What’s QUOTED_IDENTIFIERS All About?
Yesterday I wrote about having to use brackets to get SQL Server to accept my DML commands to CREATE and DROP objects using SQL Server Management Studio (SSMS). Others wrote they had not experienced this and could not reproduce the behavior. Today, I also was unable to reproduce the behavior.
I can’t say why SSMS was working in this fashion. I was in the middle of Rambo coding (writing code as fast as you can go) trying to solve a problem, and probably came across an interesting key combination setting this mode on.
Today I tried to see if there was more documentation on the issue. I came across an MSDN document about QUOTED_IDENTIFIERS, which are the ANSI standard technique for using non-standard identifiers for objects and columns. Microsoft allows you to use square brackets instead.
[Bad Table Name] = “Bad Table Name” when QUOTED_IDENTIFIERS is turned on.
When, and how, does the QUOTED_IDENTIFIERS get set? That’s kind of confusing. The setting can be set explicitly. It can be set inside a stored procedure. It can be set at the Depending on the protocol us use to connect to SQL Server, or based on the default value of a database to which you are connected. It can also be set by turning on the higher level setting ANSI_DEFAULTS which modifies QUOTED_IDENTIFIERS as well as other ANSI behaviors.
What is confusing to me is why DBLibrary (the original network protocol to connect to SQL Server), the SQL Server Native Client, and ODBC all have different behaviors. All three protocols are in Microsoft’s control when it comes to SQL Server; but they each have different behaviors.
If you’re new to SQL Server or simply want to brush up on these settings you’ll find help at http://msdn.microsoft.com/en-us/library/ms174393.aspx.
Thanks to all who view our editorials online and take the time to enter feedback. What do you think? Should we use the ANSI techniques to start with, and ignore the Microsoft enabled methods? Drop a note with your experience to btaylor@sswug.org, or feel free to leave a comment below.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
How to Handle Gaps in Your Employment History
Steve WynKoop and I talk a lot about designing and managing our professional careers on a weekly interview on SSWUG.org. This episode was about how to handle interviews when you have a large employment gap in your resume. This article goes into more detail.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)