I am using SQL Server 2008-R2, but I'd be interested in a more general answer too ... I have a table with hundreds of millions of rows, each with a "DateModified" field (datetime2(7)) Now I very ...

I am currently doing some migration from MS Access to SQL Server. Access allows multiple Nulls in unique indexes where as SQL Server does not... I've been handling the migration by removing the ind...

Similar to SQLServer where I can do the following create index TimeSeriesPeriodSs1 on TimeSeriesPeriod (validationStatus, completionStatus) where completionStatus= N'Complete' and validationStatu...

There are plenty of questions here on SO and articles online about how to handle this mess, but my question is more fundamental: WHY? Quoted identifier seems to be a property of how the engine inte...

I created a unique index (case description should be unique if IsDelete != 1) CREATE UNIQUE NONCLUSTERED INDEX [UniqueCaseDescription] ON [tblCases] ([fldCaseDescription] ASC) WHERE [IsDeleted] =...

The latest version of MongoDB (v3.2) adds support for partial (filtered) indexes. You supply a filter when you create the index and that filter determines which documents will be referenced in the ...

When I read this book, I found that I can create an index that has a where condition. I found that we can create index like this Create index `<index_name>` on `<table_name>` Or ...

So I've discovered a strange SQL Server behavior today. Suppose I have a table like this, id is primary key ╔════╦══════╦════════╗ ║ id ║ name ║ active ║ ╠════╬══════╬════════╣ ║ 1 ║ a ║ 0 ║ ║ 2 ...

Assume I'm running a website that shows funny cat pictures. I have a table called CatPictures with the columns Filename, Awesomeness, and DeletionDate, and the following index: create nonclustered...

For 'large' tables, is there any reason not to put a filter on indexes for optional columns? So for an index on column AAA (because people can search on AAA), I can set the filter to ([AAA] IS NOT...