11

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 – Martin Smith Dec 3 '14 at 21:53
  • 1
    @Martin Oh, yuck. – Aaron Bertrand Dec 3 '14 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. – Martin Smith Dec 3 '14 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... – Aaron Bertrand Dec 4 '14 at 0:05
17

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).

| improve this answer | |
  • 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. – Thomas Stringer Dec 3 '14 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 Dec 3 '14 at 21:35
1

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

or

type_desc column can also be used.

SELECT * FROM sys.indexes WHERE type_desc <> 'XML'
| improve this answer | |
  • 1
    do you have official documentation for this claim? – swasheck Dec 3 '14 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 Dec 3 '14 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. – Aaron Bertrand Dec 3 '14 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 – aasim.abdullah Dec 3 '14 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 Dec 3 '14 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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