When are square brackets not used?

Question

I understand that square brackets allow you to use reserved names or previously forbidden characters, such as spaces in your identifiers. I thought adding them everywhere was good practice. (See What are square brackets [] in sql statements? )

However, I notice that when I use them in the COL_LENGTH function, I get unexpected results:

SELECT COL_LENGTH(N'[TestTable]', N'[RatingID]') -- Returns NULL SELECT COL_LENGTH(N'TestTable', N'[RatingID]') -- Returns NULL SELECT COL_LENGTH(N'[TestTable]', N'RatingID') -- Returns 10 SELECT COL_LENGTH(N'TestTable', N'RatingID') -- Returns 10 

I see that by specifying the column name in single quotes, the square brackets become redundant, but I do not understand why they break the call. These square brackets work for the table argument, which increases my confusion.

Is there a rule when square brackets should not be used?

Answer 1

Do not pass square brackets to functions that use strings only in valid SQL statements. The function searches for a table with a name, including square brackets.