When/ why would you use QUOTENAME in SQL?

Question

I understand that the QUOTENAME function can be used to add square brackets ([], the default behaviour) or some other character wrapper to strings. It doesn't work for longer strings (over 128 characters).

So... Why/ when would you use it instead of the more conventional and far more easily readable string concatenation?

Why would you not just concatenate a single quote or a square bracket at the beginning and end of a term and use this function instead?

Answer 1

It is/was specifically designed for quoting column/table/database names – sysnames. For example, this: SELECT QUOTENAME('abc[]def') returns [abc[]]def] while SELECT '[' + 'abc[]def' + ']' returns [abc[]def] which is invalid to use as a column/table/database name.

Additionally, the SQL-99 standard is to quote by using a single quote character, and while current versions of Sql Server continue to use brackets, it may in the future (or be configurable to be) use the SQL-99 standard. In which case, all code using QUOTENAME will continue to function correctly, while code that tries to do its own escaping will fail.

There is more subtle implications as well. Since QUOTENAME has the exact same limitations as sysname, should Microsoft ever decide to change sysname to be longer than 128 characters (256 maybe? 32767 maybe?), it would be assumed that QUOTENAME would then also be able to handle these increased sizes. Using QUOTENAME is a safe(r) way of taking a column name from a possibly untrusted source and using it as a sysname – no matter the current/future database settings without having to worry about the edge cases (like ] or ' inside the input) and whether it will allow the string to break out of the column name to create SQL injection attacks. I probably wouldn't depend solely on this feature for security, but to be used in one of many layers of protection.