CODEWITHSUNDEEP
sqlsql-server
Steve Lam
Steve Lam

Reputation: 1059

SQL: Nonclustered index group multiple columns vs single columns

I have table GLTrans, it has a nonclustered index:

enter image description here

My query:

SELECT 
   _glAccount.[Code] as [AccountCode]
   ,_glTrans.[CommentBooking] as [CommentBooking]
FROM 
   [GLTransHeader] _this 
INNER JOIN 
   [GLTrans] _glTrans ON _glTrans.[GLTransHeader_Id] = _this.[Id] 
LEFT INNER JOIN 
   [GLAccount] _glAccount ON _glAccount.[Id] = _glTrans.[GLAccount_Id]
WHERE 
   _glTrans.Folder_Id = '3AFE5BC5-1CC7-4198-9D89-B65591624C6E'

If I add Folder_Id in GLTrans table, query is show up. In the other hand, query is timeout.

My questions:

  1. If I group key with multiple columns, I must using them in where keyword?
  2. If I ungroup and create nonclustered index for each single column, is it different from above?

Upvotes: 1

Views: 835

Answers (1)

mehdi lotfi
mehdi lotfi

Reputation: 11601

Index ON (Col1, Col2) is different to 'Index ON (Col2, Col1)`

Index ON (Col1, Col2) is useful for following queries:

Select * 
from YourTable
Where Col1 = ? and Col2 = ?

and following query:

Select *
From YourTable
Where col1 = ?

but not useful for this query:

Select *
from YourTable
Where Col2 = ?

Priority of columns in index is very important.

If you select column in select statement, can use include for index in other word if you not use include in your index, sql server use key lockup to get data.

for example following query need Index on (Col1, Col2) include(Col3).

Select Col3
from your table
where col1 = ? and col2 = ?

Upvotes: 2

Related Questions