SQL: Nonclustered index group multiple columns vs single columns

Question

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:

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

There's no LEFT INNER JOIN - either it's an INNER JOIN or an LEFT (OUTER) JOIN ...... — marc_s
– marc_s, Commented Aug 25, 2014 at 7:55
This material has been covered many times. The question is too broad. A basic tutorial about indexing should answer this better than SO can. — usr
– usr, Commented Aug 25, 2014 at 8:10

mehdi lotfi · Accepted Answer · 2014-08-25 08:19:31Z

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 = ?

Collectives™ on Stack Overflow

SQL: Nonclustered index group multiple columns vs single columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related