WHERE clause in Index

Question

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

Do you mean what columns are used in the index and what columns are in the INCLUDE clause of the index?

Also using sp_help on the table the index is created on will give you the columns used by the index.

MCITP SQL 2005, MCSA SQL 2012

Richard Warr SSCertifiable Points: 6963 More actions · Answer 2

Is this the filtered index introduced in SQL Server 2008? See if this rings any bells:

mc_donnelly Valued Member Points: 70 More actions · Answer 3

I am looking for

what the WHERE clause specified

right now I know I created the index like this

CREATE INDEX IDX_indexname

ON TABLE1(StoreID,ItemsProcessed)

WHERE ItemsProcessed = 0

but If in the future, I forget what the WHERE clause

specified, How do I find out

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I don't have 2008 to test but that's the first thing I'd try.

sp_helpindex 'tblname'

If that fails I'd go to sys.indexes. It pretty much has to be in that table.

DB Dan SSC Veteran Points: 270 More actions · Answer 5

You want to look for the filter_definition column in sys.indexes.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 6

It might be in other places, but I know that it is in sys.indexes. Specifically, the column filter_definition.

EDIT: Too slow..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I preffer to say that you started too late... I can't believe it took you 20 minutes to write that message :w00t:.

mc_donnelly Valued Member Points: 70 More actions · Answer 8

Thank you, I found it

I thought that sysindexes and sys.Indexes where the same thing

I only looked in sysindexes

Mike

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

dbo.sysindexes is pre 2005. It's there only for backward compatibility along with pretty much anything that was available in 2000 and before.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 10

Ninja's_RGR'us (4/26/2011)
I preffer to say that you started too late... I can't believe it took you 20 minutes to write that message :w00t:.

I know.. pretty sad.. 😉