What significance does an index_id < 256000 have? In a certain tutorial I read the author is filtering sys.indexes based on the predicate index_id &lt; 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 &lt;&gt; 3;


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

WHERE type_desc &lt;&gt; 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).