jump to navigation

SQL Server SET options and index creation November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
trackback

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!!!

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: