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

Using “OR” and “IN” with SQL Server’s Filtered Indexes

You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index: CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2); GO 123 CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId)     WHERE (VoteTypeId = 1 OR VoteTypeId = 2);GO If you try, you’ll get the error message:…
Read More


One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…
Read More

How to Configure Ola Hallengren’s Maintenance Scripts

If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. They’re better than yours (trust me), and they give you more flexibility than built-in maintenance plans. However, the index maintenance defaults aren’t good for everyone. Here’s how they ship: Transact-SQL CREATE PROCEDURE…
Read More

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