List Tables That Contain Special Indexes

Find out if any of your tables contain special columnstore or spatial indexes. Columnstore indexes organizes the data in columns instead of rows like traditional indexes and can increase performance on large data sets as found in data warehouses. Spatial indexes are a special type of index on a spatial column such as geometry or geography.

— Tables with columnstore indexes — SQL 2012 +

select t.name as TablesWithColumnstoreInx

from sys.indexes i

inner join sys.tables t

on i.object_id = t.object_id

where i.type = 5 or i.type = 6

— Tables with spatial indexes — SQL 2014 +

select t.name as TablesWithSpatialInx

from sys.indexes i

inner join sys.tables t

on i.object_id = t.object_id

where i.type = 4

 

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview

https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-indexes-overview

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Post a comment or leave a trackback: Trackback URL.

Comments

  • Sql_unicorn  On September 7, 2017 at 4:38 PM

    What would be more useful is a post about the index types and what they mean. You know, like explaining the ‘type’ column in sys.indexes ? There’s at least 6, and you only described 2 of them obliquely…

Trackbacks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: