Filtered index in SQL Server 2008

Question

Hi

I have created filtered unique index in one my database table. According to my research if i want to do any operation on that table from asp.net i need to turn on the following set operation to my connection

SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

If i turn the above features, what will be the effect? Is it cause any problem? is there any other better option?

Thanks in advance

Answer 1

None of the three items you are required for you to properly use your index. The single largest factor that determines if an index will be used or not is the construction of the query, and it's evaluation by the query optimizer engine.

But, to go on with what you brought up, let's check the use of the three:

SET CONCAT_NULL_YIELDS_NULL ON

Let's take this statement SET @Name = @FirstName + ' ' + @LastName

If @FirstName is NULL or @LastName is NULL, then your @Name will be NULL. If you do not want this behavior - you want NULL to be treated like an empty string, then you SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_PADDING ON

This will be deprecated in the future. On SQL Server, this is set to ON by default. As a matter of fact, on newer versions of SQL Server, you cannot even turn it to OFF.

This affects data storage in a varchar column - for example, if you turn it ON then a value of TEST will be stored along with with a trailing blank space if you turn it to OFF, trailing spaces are removed. Same thing applies to trailing zeroes into a VarBinary column.

SET ANSI_WARNINGS ON

This is for grouping and totaling. If you have a NULL value in any aggregate function, and you want to know that there is a NULL value then you set this to ON. If you do not care to know that a NULL was eliminated from the aggregate, then you turn this to OFF.