Indexes

States about SQL2012 release:

clustered Index per table 1
non clustered Index per table 999
xml Index per tabel 249
columns in normal table 1024
columns in wide table 30,000
rows in table : limited by memory
nested level in trigger 32
size of varchar 2^31-1
size of nvarchar 2^30-1
database size 524,272 terabytes
foriegn key per table 253
SQL instance per machine 50
partition per table/Index 1000 for 32 bit and 15000 for 64 bit
user connection 32767

If there are indexes like

Create Index IDX_Tab1_Col1Col2 on Table1 (Column1,Column2)

and if there is query using filters on both the column in a where clause, than the order of filter given does not matter.

but if there only one filer in where clause than it matter whether this filtered column is first or second in Index,

• If it is first column in index definition, query plan uses Index seek
• If it is second column in the index definition, query plan used Index scan which is more costly, hence performance is not the same.

also the Index seek operation will be applied on the Second column in Index when first column will is have equality operation(remember that order filter in where clause does not matter in this case)

 Where Column1=1 and Column2>1   (Index Seek on both the columns)
 Where Column1=1 and Column2=1   (Index Seek on both the columns)

 Where Column1>1 and Column2>1   (Index Seek on Column1 but Index Scan on Column2 )
 Where Column1>1 and Column2=1   (Index Seek on Column1 but Index Scan on Column2 )