Indexing

  1. Home
  2. Indexing
  3. (Page 9)

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way – that limitation hasn’t disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they’re…
Read More

Keep it Constrained

Indexing, SQL Server
8 Comments
SQL Server has this fancy feature called constraints. Database constraints are used to restrict the domain (the range of allowed values) of a given attribute. That’s just a funny way of saying: through a set of carefully crafted rules, we control the shape of our universe. Our Test Table We’re going to be testing with…
Read More

Filtered Indexes and Dynamic SQL

Indexing, SQL Server
21 Comments
I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to…
Read More

Indexing Wide Keys in SQL Server

Indexing, SQL Server
25 Comments
Key length matters in SQL Server indexes. It’s a best practice to keep your index keys as narrow as possible, and SQL Server enforces a maximum key length of 900 bytes on most “normal” clustered and nonclustered indexes. But what happens if you want to optimize the lookup of a wide column? You’re not necessarily…
Read More

5 Things About Fillfactor

Index Maintenance
68 Comments
Are you a page half full, or a page half empty kind of person? I’ll never think “I’ve seen it all” when it comes to SQL Server– there’s just always someone waiting around the corner who’s found a weird new thing to do with it. But there are some things I really wish I could…
Read More
Menu