THE SQL Server Blog Spot on the Web

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

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Parameterization and filtered indexes (part 1)

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2014/01/parameterization-and-filtered-indexes-part-1/

Published Sunday, January 19, 2014 11:29 PM by Hugo Kornelis

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

 

gotqn said:

So, that's why you said at the beggining: "hint: if you filter on any columns not included in the index, always INCLUDE them – that solves 95% of the problems".

Making alter filter indexes covered indexes should handle the situation above.

January 26, 2014 4:06 PM
 

Vicky Thakor said:

I think you didn't understand properly. Its not understandable for human, Its for alien only.

August 12, 2014 9:42 AM
 

Daniel Adeniji said:

1)Can you please let me know which version of SQL Server introduces the ParameterizedPlanHandle attribute ( @ParameterizedPlanHandle ).

I do not see it in v2005.

2) Does  

   --Query does not target sys schema

  ( st.[text]  not like '%sys.%' )

serve intended role for

   st.[text] NOT LIKE '%ExcludeMe%'

or not quite.

3) It looks like you were going to get into the "The UseCount of the Prepared plan does not go up, even though this is the plan that is actually executed – a weird way of counting, but documented (though not entirely correct)" discussion, put to punted.

Can you please provide link to documentation.

In sincere thanks for the thoroughness in all your work,

Daniel Adeniji  

May 20, 2016 9:09 PM
 

Hugo Kornelis said:

Hi Daniel,

1) I could not find any documentation on it, but I just tested a bunch of instances. SQL2008 and up all show this attribute, SQL2005 doesn't. I did not test older instances.

2) Both serve the same intended purpose, but a filter on @sys.% will filter out ALL queries that targat any object in the sys schema. That may or may not be what you want.

3) I don't understand why but I notice that none of the links in this post worked. Luckily I always write and save my blog posts in MS Word before publishing them, so I was able to repair all links.

June 25, 2016 3:58 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement