SQL Server SET options and index creation November 18, 2013Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: SET options
I have been delivering a certified course in MS SQL Server 2012 recently and I have found something that was really impressive regarding SET options in SQL Server and index creation.
I will look into the SET QUOTED_IDENTIFIER and SET ANSI_NULLS options and how their values can affect index creation.
I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.
I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)
USE tempdb GO --create a sample table CREATE TABLE Customers ( Customer_ID INT NOT NULL IDENTITY(1,1), Last_Name VARCHAR(20) NOT NULL, First_Name VARCHAR(20) NOT NULL, Email_Address VARCHAR(50) NULL, Fullname AS first_name + '' + last_name ) SET QUOTED_IDENTIFIER OFF; SET ANSI_NULLS OFF; --create a clustered index on Customer_ID and --a non-clustered composite index on the fullname --index failed CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname) SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; -successful index creation CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)
Let me explain what I am doing in this bit of code
- I make the tempdb the current database
- I create a new table called Customers
- I have a computed column in Fullname in the table Customers
- Then I set QUOTED_IDENTIFIER and ANSI_NULLS to OFF
- Then I create a non clustered index on the computed column. It failed with this error “CREATE INDEX failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”
- Then I set QUOTED_IDENTIFIER and ANSI_NULLS to ON
- Then I create a non clustered index on the computed column again. This time works just fine.
Hope it helps!!!