In a certain tutorial I read the author is filtering sys.indexes based on the predicate index_id < 256000. What does this accomplish?

  • 2
    Maybe they copied the code from sys.sysindexkeys Commented Dec 3, 2014 at 21:53
  • 1
    @Martin Oh, yuck. Commented Dec 3, 2014 at 22:12
  • 1
    @AaronBertrand - and also in sys.selective_xml_index_paths, sys.xml_indexes, sys.sysindexes but I suppose these will just get updated if the magic number is no longer valid. Commented Dec 3, 2014 at 22:27
  • 1
    @Martin I wouldn't bet on it. Especially for the backward compatibility views. What a horrible way to demonstrate how to retrieve metadata... Commented Dec 4, 2014 at 0:05

2 Answers 2


This is based on the misconception that XML indexes are currently the only type that could ever have an id scheme that is >= 256000 (at least based on their observation; this scheme is not documented AFAIK, so not even sure if it's intentional). Probably fine in current versions, but who knows what type of index will be added next and where its id scheme will start? If you want to exclude XML indexes, you are now also excluding something else. Spatial indexes, for example, seem to start at id = 384000. If the query above is intending to include spatial indexes but not XML indexes, they're going to be in for a surprise.

A much better filter would be:

WHERE type <> 3;

...or even better, since it is self-documenting...

WHERE type_desc <> N'XML';

And now when you want to also exclude, say, spatial indexes, your query changes to...

WHERE type_desc NOT IN (N'XML', N'SPATIAL');

...instead of having to figure out what numeric range the id values for spatial indexes might occupy (or not). Good luck with that.

These are pretty clearly documented in sys.indexes (Transact-SQL). I see no reference to this magic number and I highly recommend you point your tutorial author here so they can see that this magic number is not something they should be relying on (never mind teaching others to rely on).

  • 4
    +1 that's an awful bad habit. Forget about index_id. Especially since more accurate data for determining type is sitting right next to it... literally. Commented Dec 3, 2014 at 21:31
  • 1
    It's probably a design error of SQL Server to give out index_id's with this regularity. They should be randomized so that nobody can mistakenly rely on them.
    – usr
    Commented Dec 3, 2014 at 21:35

According to book "Microsoft SQL Server 2012 Internals"By Kalen Delaney, Craig Freeman, The XML index's index_id begins numbering with 256000. So to get all type indexes information (querying sys.indexes) but skipping XML indexes you can place filter like that.

SELECT * FROM sys.indexes WHERE index_id <256000

Same result set can be achived by placing filter on type column of sys.indexes. For XML type of indexes type = 3.

SELECT * FROM sys.indexes WHERE type <> 3


type_desc column can also be used.

SELECT * FROM sys.indexes WHERE type_desc <> 'XML'
  • 1
    do you have official documentation for this claim?
    – swasheck
    Commented Dec 3, 2014 at 21:31
  • i have it right here. which page? also - i respect the heck out of those authors but i'm not sure that counts as "official documentation."
    – swasheck
    Commented Dec 3, 2014 at 21:39
  • That is a casual observation, at best, by Kalen at a specific point in time, with no knowledge that this was actually intentional, never mind no future-telling ability to determine whether some new index type will be > 256000 in the future. It is not something Microsoft intended you to rely on, which is why you won't find any reference to it in the official documentation. And agree with @swasheck, while that book is definitely a valuable resource, it is not official documentation. Commented Dec 3, 2014 at 21:41
  • 3
    @swasheck question is that, why figure 256000 is used, and not what is safe. For best practice definitely i would like to go with Aaron Commented Dec 3, 2014 at 21:50
  • 1
    "What does this accomplish?" Technically the answer would be "nothing." Circumstantially, it's a way that people have correlated filtering out XML indexes.
    – swasheck
    Commented Dec 3, 2014 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.