Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Want to create a composite index on a table

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-04-12 : 10:23:18
We mostly use the below 3 conditions from various stored proc queries:

condition1: progid, projid, contractid

condition2: progid, projid, contractid, username (username can be used in the front or back of where condition

condition3: progid, projid, contractid, userid

Can i create an index this way that can accomodate all above three conditions:

create nonclustered index USR_ACCESS_HIER
On dbo.Tab_useraccess(progid,projid,contractid,username)
Include(Userid)


Thanks a lot for the helpful info

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-04-12 : 14:36:39
create nonclustered index USR_ACCESS_HIER
On dbo.Tab_useraccess(progid,projid,contractid)
Include(Userid,username)



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-14 : 13:45:11
The clustered index is what is critical.

Determine which columns you (almost) always query by, then cluster the table by those. You won't need to (and can't) INCLUDE username or userid in the index.

You may need only two columns. Determine which columns and the order based on your usage.
Go to Top of Page
   

- Advertisement -