SQL Server SET options and index creation
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)
[sourcecode language="sql"]
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)
[/sourcecode]
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!!!