Is there a difference between id, date or date, id index in sql server

Question

I am creating an index in sql server 2005, and a discussion with a colleague is if the difference between the columns of the index key is id and date vs date then id.

Is there a fundamental difference between the way an index is created in any scenario?

Could this affect other versions of SQL Server?

thanks

Answer 1

Yes, definitely. Does anyone query a table for JUST date or JUST id? Date index, id can be used to search only the date, but not only id, and vice versa

Using date id:

Jan 1     4  
Jan 1     7  
Jan 2     6  
Jan 2     9  
Jan 2     33  
Jan 3     23  
Jan 4     1

Using id, date:

1     Jan 4  
4     Jan 1  
6     Jan 2  
7     Jan 1  
9     Jan 2  
23    Jan 3  
33    Jan 2 

WHERE JOIN , , . , , , .

A, B, C, D A, B, C, D, OR A, B, C OR A, B A.

Answer 2

, . , , .

.

, , , .

, :

FirstName, MiddleName, LastName

, , WHERE . .

, , , , , , , WHERE, , , . : .

, , :

FirstName, LastName, MiddleName

, , .

, -.

, . , , , , , .

, , "Lasse Vågsæther Karlsen", , , , , , Vågsæther.

, , , , . , - .

, id, , , , , , , , .

Answer 3

Yes, that matters. Suppose you create an index for columns (A, B). You can do SELECT with a WHERE clause that includes both columns, and the index can be used. The index will also be used if you perform SELECT with WHERE, which includes only column A. But if you perform SELECT with WHERE, which includes only column B, the index cannot be used.

See here for more details .