Is there any difference when I create a table index for more columns if I use the columns in different order?
Exactly is difference between ID, isValid, Created and ID, Created, isValid indices?
And is there any difference in querying order?
where ID = 123
and isValid = 1
and Created < getdate()
vs.
where ID = 123
and Created < getdate()
and isValid = 1
Column types: ID [int], isValid [bit], Created [datetime])
Exactly is difference between
ID, isValid, CreatedandID, Created, isValidindices?
If you always use all three columns in your WHERE clause - there's no difference.
(as Martin Smith points out in his comment - since of the criteria is not an equality check, the sequence of the columns in the index does matter)
However: an index can only ever used if the n left-most columns (here: n between 1 and 3) are used.
So if you have a query that might only use ID and isValid for querying, then the first index can be used - but the second one will never be used for sure.
And if you have queries that use ID and Created as their WHERE parameters, then your second index might be used, but the first one can never be used.
Created at the end?< GetDate and scan a load of rows that don't necessarily match the other two predicates. If at the end you can do an equality seek on those then just do the range seek on matching rows.AND is commutative, so the order of ANDed expressions in WHERE doesn't matter.
Order of columns in an index does matter, it should match your queries.
If ID is your table's clustered primary key and your queries ask for specific ID, don't bother creating an index. That would be like giving an index to a book saying "page 123 is on page 123" etc.
The order in the query makes no difference. The order in the index makes a difference. I'm not sure how good this will look in text but here goes:
where ID = 123 and isValid = 1 and Created < Date 'Jan 3'
Here are a couple of possible indexes:
ID IsValid Created
=== ======= =========
122 0 Jan 4
122 0 Jan 3
... ... ...
123 0 Jan 4
123 0 Jan 3
123 0 Jan 2
123 0 Jan 1
123 1 Jan 4
123 1 Jan 3
123 1 Jan 2 <-- Your data is here...
123 1 Jan 1 <-- ... and here
... ... ...
ID Created IsValid
=== ======= ========
122 Jan 4 0
122 Jan 4 1
... ... ...
123 Jan 4 0
123 Jan 4 1
123 Jan 3 0
123 Jan 3 1
123 Jan 2 0
123 Jan 2 1 <-- Your data is here...
123 Jan 1 0
123 Jan 1 1 <-- ... and here
... ... ...
As you can probably tell, creating an index(IsValid, Created, ID) or any other order, will separate your data even more. In general, you want to design the indexes to make your data as "clumpy" as possible for the queries executed most often.
ID, isValid , Createdthere. So seek on the first two columns.