MS SQL table hints in FROM clause (e.g. NOLOCK)

[Prometheus77]

As an MS SQL user, I want to be able to add table hints to FROM clauses natively in HoneySQL.

For example, the query:

SELECT * FROM mytable WITH (NOLOCK)

can be approximated by HoneySQL

(sql/format {:select :*
             :from [[:mytable [:t [:raw "WITH (NOLOCK)"]]]]})

which yields

[SELECT * FROM mytable AS t WITH (NOLOCK)]

however, this suffers from two issues:

1. It requires the creation of a table alias (t)
2. It requires using the :raw tag rather than supporting it natively

A more elegant solution would look something like

(sql/format {:select :*
             :from [[:table [:hints :nolock]]]})

For reference:

• Microsoft's official documentation for Table Hints: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
• Link to discussion in Clojurians Slack discussing this issue: https://clojurians.slack.com/archives/C66EM8D5H/p1704733657926419

[seancorfield]

I went with metadata for this:

(sql/format {:select :*
             :from [^:nolock [:table]]})

Works with all the :join clauses too.

Right now, it's hardcoded to SQL Server's WITH (...) format. If other dialects need something different -- and people ask for it -- I'll make it more flexible.