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?