Want to create a composite index on a table

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources