SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage
- by Pinal Dave
This is a follow up blog post of SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation. I wrote that blog six years ago and I had plans that I will write a follow up blog post of the same. Today, when I was going over my to-do list and I was surprised that I had an item there which was six years old and I never got to do that.
In the earlier blog post I wrote about exploitation of the Quoted Identifier and ANSI Null. In this blog post we will see a quick example of Quoted Identifier. However, before we continue this blog post, let us see a refresh what both of Quoted Identifider do.
QUOTED IDENTIFIER ON/OFF
This option specifies the setting for use of double quotes. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.
In simple words when we have QUOTED IDENTIFIER ON, anything which is wrapped in double quotes becomes an object.
E.g.
-- The following will work
SET QUOTED_IDENTIFIER ON
GO
CREATE DATABASE "Test1"
GO
-- The following will throw an error about Incorrect syntax near 'Test2'.
SET QUOTED_IDENTIFIER OFF
GO
CREATE DATABASE "Test2"
GO
This feature is particularly helpful when we are working with reserved keywords in SQL Server. For example if you have to create a database with the name VARCHAR or INT or DATABASE you may want to put double quotes around your database name and turn on quoted identifiers to create a database with the such name. Personally, I do not think so anybody will ever create a database with the reserve keywords intentionally, as it will just lead to confusion.
Here is another example to give you further clarity about how Quoted Idenifier setting works with SELECT statement.
-- The following will throw an error about Invalid column name 'Column'.
SET QUOTED_IDENTIFIER ON
GO
SELECT "Column"
GO
-- The following will work
SET QUOTED_IDENTIFIER OFF
GO
SELECT "Column"
GO
Personally,
I always use the following method to create database as it works irrespective of what is the quoted identifier’s status. It always creates objects with my desire name whenever I would like to create.
CREATE DATABASE [Test3]
I believe the future of the quoted identifier on or off is useful in the real world when we have script generated from another database where this setting was ON and we have to now execute the same script again in our environment again.
Question to you -
I personally have never used this feature as I mentioned earlier. I believe this feature is there to support the scripts which are generated in another SQL Database or generate the script for other database. Do you have a real world scenario where we need to turn on or off Quoted Identifiers.
Click to Download Scripts
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
