June 10, 2010 at 6:15 am
When you create a filtered index SQL expects ansi settings to be defined a certain way on the objects that reference the index table.
Some of the ansi settings can be retrieved in the DMVs but others I can not sem to be able to query against the object. Obviously they are stored somewhere because SQL is checking them at design / runtime and throwing errors.
--tables
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
--procedures / views etc
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
For tables it is easy to find the ANSI_NULL settings
For procedures etc it is easy to find ANSI_NULLS and QUOTED_IDENTIFIER information
I assume for the table you check ANSI_PADDING by checking any char column??
I have tried to run profiler when SQL is throwing the errors to see how it checks them but I can not figure out which event to use.
Any help on how to check these settings on objects would be helpful.
the following script will generate the errors if you want to try it.
Thank you
--create the table with the right settings
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
IF EXISTS ( SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'TestFilteredIndex' )
BEGIN
DROP TABLE dbo.TestFilteredIndex
END
CREATE TABLE dbo.TestFilteredIndex ( ID int IDENTITY(1,1) PRIMARY KEY, TestType tinyint NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( 1 )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( 1 )
INSERT INTO dbo.TestFilteredIndex VALUES ( 1 )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( 2 )
INSERT INTO dbo.TestFilteredIndex VALUES ( 2 )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
INSERT INTO dbo.TestFilteredIndex VALUES ( 1 )
INSERT INTO dbo.TestFilteredIndex VALUES ( 2 )
INSERT INTO dbo.TestFilteredIndex VALUES ( NULL )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.TestFilteredIndex')
AND name = N'IX_TestFilteredIndex_Type')
BEGIN
DROP INDEX IX_TestFilteredIndex_Type ON dbo.TestFilteredIndex
END
CREATE NONCLUSTERED INDEX IX_TestFilteredIndex_Type ON dbo.TestFilteredIndex
( TestType ASC )
WHERE TestType IS NOT NULL
GO
--LETS TRY TO INSERT INTO THE TABLE WITH A PROCEDURE WITH SOME OF THE WRONG OPTIONS
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.InsertTestType') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.InsertTestType
GO
SET ANSI_NULLS ON
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT ON
GO
CREATE PROCEDURE dbo.InsertTestType AS
SET NOCOUNT ON
BEGIN
INSERT INTO dbo.TestFilteredIndex VALUES ( 5 )
END
GO
--lets raise the error
EXEC dbo.InsertTestType
GO
June 18, 2010 at 10:04 am
Any Ideas?
August 12, 2010 at 9:15 am
I still have not managed to find a way in profiler to come up with how sql is checking the ansi setting conditions.
If anyone comes across a solution to check settings let me know.
I would prefer to systematically strike offending procedures and tables than have to recreate them all so that I can use filtered indexes.
Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply