THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

On filtered indexes and defensive coding

When one and the same constant is copied and pasted in more than one place, there is always a chance that we can change it in one place and fail to change in another, resulting in a discrepancy.

For example, recently I read a very interesting post by Michelle Ufford, Filtered Indexes Work-Around.

I will not repeat the whole post here, I encourage you to read it in full, but here are the relevant parts. There is a filtered index:

CREATE NONCLUSTERED INDEX MyLatestData
            
ON dbo.myTable(myDate)
            
Include (myData)
    
WHERE myDate >= '20100127'

The following stored procedure does not use it, because the optimizer does not know if the parameter is before the cutoff date for the filtered index:

CREATE PROCEDURE SelectLatestData
    
@DateFrom DATETIME
            
AS
SELECT DISTINCT 
myData
    
FROM dbo.filteredIndexTest
    
WHERE myDate >= @DateFrom

This is where Michelle analyzed several approaches. The following is my suggestion, which has problems, yet Michelle was kind enough to incorporate in her post:

ALTER PROCEDURE SelectLatestData
    
@DateFrom DATETIME
            
AS
SELECT DISTINCT 
myData
    
FROM dbo.filteredIndexTest
    
WHERE myDate >= @DateFrom
    
-- this ensures that the index is used
        
AND MyDate >= '20100127' 

By repeating the cutoff date from the definition of the filter index in the WHERE clause of this procedure, we explicitly tell the optimizer that that filtered index can always satisfy it regardless of the value of the parameter. However, there is a problem with my suggestion: what happens when later we change the filter of the index, as follows:

CREATE NONCLUSTERED INDEX MyLatestData
            
ON dbo.myTable(myDate)
            
Include (myData)
    
WHERE myDate >= '20100220'

Our stored procedure will still work, but it will not use that new index any more.Suppose that we still want to use the stored procedure to get the latest data, and to use the lean filtered index for that purpose.

Clearly we should change the stored procedure at the same time when we replace the index. How can we manage that? If the system is large, it is impossible to remember all these details.

The way I deal with such problems is this:

I have macros, and I apply C++ preprocessor against my sql source code when I build. So, in my definitions.h file I will have this line:

#define LATEST_DATA_CUTOFF '20100127' 

In my sql code, I will include definitions.h, just like I would do in C++ code. Both the index definition and the stored procedure will use the macro LATEST_DATA_CUTOFF. When I change the macro definition, the preprocessor will take care of all the changes in all the places the macro is used. SQL Compare will take care of the deployment.

How do you solve such problems?

 

 

Published Friday, February 05, 2010 10:16 AM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Zack Jones said:

I'm not sure how I would solve the problem but I am interested to hear more about how you can use an include file with SQL Server, specifically stored procedures. I could see where that would be very handy.

February 5, 2010 12:28 PM
 

Alexander Kuznetsov said:

Zack,

All the work with macros proceeds outside SQL Server and outside SSMS.

I have a Visual Studio solution with my unit tests. All *.sql files are added to that solution, and in my Post Build event I invoke an Iron Python script that grabs those *.sql files (after the proprocessor worked against them and expanded all those macros), and it merges those *.sql files into a single sql script. That sql script creates the database from scratch, adds all objects to it.

February 5, 2010 2:31 PM
 

noeldr said:

Might not be cheaper but "OPTION (RECOMPILE);" is safer!

February 9, 2010 11:19 AM
 

Alexander Kuznetsov said:

noeldr,

I agree that in uncontrolled environments OPTION (RECOMPILE) is definitely safer. If, however, deployments are tightly controlled, there are other safe alternatives.

February 9, 2010 12:58 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement