July 12, 2010 at 5:49 am
Hi,
I have a table with these fields:
ID (PK), ProjectName, and some other non important fields (about 20)
Most of my queries i'm doing SELECT * from this table.
there is one query where i need the data to be ordered by ProjectName.
Should i put a non-clustered index on the ProjectName?
or i can just leave the default index from the PK on the ID field?
Thanks in advanced.
July 12, 2010 at 6:32 am
shayy (7/12/2010)
Hi,I have a table with these fields:
ID (PK), ProjectName, and some other non important fields (about 20)
Most of my queries i'm doing SELECT * from this table.
there is one query where i need the data to be ordered by ProjectName.
Should i put a non-clustered index on the ProjectName?
or i can just leave the default index from the PK on the ID field?
Thanks in advanced.
I assume the queries are
SELECT *
FROM Table
WHERE Id = 42
If so, I tend to doubt you'll see much benefit from an index on the ProjectName column. If your queries are literally bringing back all data every time, with no WHERE clause, that's a problem in and of itself, but adding the nonclustered index on ProjectName is likely to lead to an additional issue, you'll be introducing a key lookup operation in order to get the data which is stored on the clustered index. It'll hit the nonclustered for the ordering, but then have to go and read the clustered index anyway to get the data.
The thing to do is simply test it. Check the execution plan and the performance metrics, I/O and time.
If you are not passing WHERE clause values, why not. You're going to be seeing lots and lots of scans. As the table grows this will just make for worse and worse performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 6:35 am
shayy (7/12/2010)
Should i put a non-clustered index on the ProjectName?or i can just leave the default index from the PK on the ID field?
Do you have any queries that filter on the ProjectName column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2010 at 6:41 am
Thank you both for the fast replies.
1. As i mentioned, i only have one query (maybe 2) which takes the data ordered by ProjectName
2. about using the WHERE, what if i need let's say 10 fields, should i use the WHERE or just *?
3. can you direct me to an article on how to test time an I/O as you mentioned?
4. let's say i do use WHERE, should i then use the index on ProjectName?
Thanks a lot.
July 12, 2010 at 6:56 am
shayy (7/12/2010)
Thank you both for the fast replies.1. As i mentioned, i only have one query (maybe 2) which takes the data ordered by ProjectName
2. about using the WHERE, what if i need let's say 10 fields, should i use the WHERE or just *?
The WHERE clause has nothing to do with the columns selected. If you need to select 10 columns from a 10 column table, you can use *. Just understand that when column 11 or 12 is added, you're getting those too. If you need 10 columns from a 20 column table, no, don't use *. Only move the data you need to move, when you need to move it. That's a very good rule to live by.
3. can you direct me to an article on how to test time an I/O as you mentioned?
The easy way, for a single query, is to right click inside the TSQL editing window,select Query Options, Advanced, and click on the Statisicts IO and Statistics Time check boxes. There are lots of other ways to collect this data as well. You may want to pick up a book on the topic.
4. let's say i do use WHERE, should i then use the index on ProjectName?
Thanks a lot.
It depends on what is in the WHERE clause. The WHERE clause is a filtering clause to limit the number of rows returned. If your query returns data based on the primary key of the table, it's only ever going to return one row, so, no, putting an index on for the ordering of the data is a waste of time. You'll need to determine first, what is the filtering that you'll be doing. Depending on that filtering, if you also have to order the data returned, then an index may help for the ordering. It all really depends on the queries that you're writing.
Again, all this is beside the point if you are only ever running
SELECT * FROM Table
Because at that point, SQL Server can only scan the table to return your data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 7:06 am
shayy (7/12/2010)
2. about using the WHERE, what if i need let's say 10 fields, should i use the WHERE or just *?
??
The use of a WHERE clause limits the rows you return. The use or not of * affects the columns you return. Different things totally.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2010 at 7:13 am
From what i've understood so far, when using * the index doesn't matter but if i'll use exact
column, then an index on that column can help.
Thanks a lot for all your help.
Cheers 😀
July 12, 2010 at 7:15 am
shayy (7/12/2010)
From what i've understood so far, when using * the index doesn't matter but if i'll use exactcolumn, then an index on that column can help.
Thanks a lot for all your help.
Cheers 😀
No, no, no, that's wrong.
* or not simply determines which columns you're selected, as Gail said.
It's the WHERE clause, the filter, the search criteria, the thing that limits the number of rows returned, that will help determine index use.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 7:17 am
I see now, thanks 🙂
July 12, 2010 at 7:54 am
shayy (7/12/2010)
From what i've understood so far, when using * the index doesn't matter but if i'll use exactcolumn, then an index on that column can help.
Um....
I really think you need to read these, then the Books Online entries on indexes.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply