In a certain tutorial I read the author is filtering
sys.indexes based on the predicate
index_id < 256000. What does this accomplish?
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).
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'