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 2005 Forums
 Transact-SQL (2005)
 Simple index question

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-07-20 : 12:59:53
Given the following query...


SELECT CourseCatID, CategoryName FROM dbo.CourseCategories
WHERE ParentCatID = 1 AND CourseCatID <> 1
AND ClientID = 1
ORDER BY CategoryName ASC


Should I have three separate indexes for each item in the where clause (ParentCatID, CourseCatID, ClientID) or should I have one index comprised of all three columns? And if they are separate do I include CourseCatID and CategoryName as include columns?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 13:18:19
Please convert your query to a string for us, so that we can copy/paste into Management Studio. I don't have the time to parse it for you, so please help us help you.

Your code is vulnerable to SQL injection. You should not be concatenating like that. Instead you should use parameterized queries. Google "sql injection" for more information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 16:35:45
How many distinct values of each are there: ParentCatID, CourseCatID, and ClientID? We need to determine the selectivity of them before determining what indexes to create.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -