Yes all that is true, but the index would appear in the execution plan and be counted in usage stats. The question was if an index could...

Yes, the mere existence of a *unique* index can be used by the optimizer to simplify an execution plan based on the guarantees that uniqueness provides. The index will...

In addition to any search engine results:

http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx

I definitely recommend them - particularly Inside Microsoft SQL Server 2008: T-SQL Querying.

...so I have to build the query from...

GilaMonster (1/26/2012)

Only...

I thought the same thing! We must have been working on the opposite solutions at the same time. I must confess I still...

Sample data copied from the first post:

DECLARE @data AS TABLE

(

MID integer NOT NULL,

[Date] ...

The rewrite to >= @date AND timestamp < DATEADD(dd,1, @date) is a sound practice, but it is not actually required to make the predicate SARGable. A simple CONVERT of...

Whatever else, always store dates and times using one of the built-in types, never as a string.

As an aside, this sort of formatting is made easier and faster in SQL...

DECLARE @Example AS TABLE

(

CardType nvarchar(50) NOT NULL,

Completed bit ...

Backwards compatibility has a lot to answer for. I cringe slightly every time I see a variable-length data type used without an explicit maximum length value.

See also http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

Just for the record, SQL Server 2012 (only) gives us a neater T-SQL solution (though not as fast):

DECLARE @test-2 TABLE

(

Id integer...

Itzik Ben-Gan ran a series of articles exploring T-SQL solutions to this problem:

http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3

The link above gives a very neat solution provided by several people including a past regular contributor to...

Been reading Douglas Adams recently, Brandie?