 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008More about indexes
After having avoided the topic for some time (previous attempts to grasp the concepts resulted in near brain meltdown!), I have decided now is the time to completely come to terms with indexing and query optimisation. After a couple of posts here and a fair amount of reading I have some questions. I'll try and save the how's and where's for BOL etc but have some "best practises" and "have I read this right" stuff that I will probably post here. Not least because I realise the expertise levels of some of the residents here. So. In every table I have ever created (Access and SQL Server) I have always made an identity column. Even if that column had no relation to the data stored in the table. If I had a PartID then I'd use that. If no unique ID existed in the data then I would create an ID column for the sake of it. This was mainly due to my data being used for web front ends. I might populate a drop down and have the 'name' as the list items and the 'ID' as the value. Therefore I would use WHERE ID = ID in subsequent queries based on drop down selection. Based on the above scenario, am I correct in thinking that no other index would be required on that table (assuming it was not queried in other ways from other places)? Also, does the amount of rows come into consideration when deciding whether to create an index? If I have a table of 200 rows and a column called lastname which features in my WHERE criteria (e.g. where lastname = 'smith'), is an index on the lastname column necessary or, given the low number of rows, would a table scan be more efficient overall than creating and maintining an index on that column? Thanks for baring with an indexing noob who is trying to get his head around this heavy topic! Jay > Based on the above scenario, am I correct in thinking that no other index > would be required on that table (assuming it was not queried in other ways > from other places)? Actuially NO. You are probbaly running SELECT statements with WHERE condition, so in you example (200 rows) SQL Server decides what is the best goring to scan table or using an index on last name column, You are right , if the table is small , SQL Server will scan table. > After having avoided the topic for some time (previous attempts to grasp > the concepts resulted in near brain meltdown!), I have decided now is the [quoted text clipped - 32 lines] > Thanks for baring with an indexing noob who is trying to get his head > around this heavy topic! > Jay >> Based on the above scenario, am I correct in thinking that no other index [quoted text clipped - 5 lines] > best goring to scan table or using an index on last name column, You are > right , if the table is small , SQL Server will scan table. My WHERE condition involves only the PK. Can you tell me how it is beneficial to index other columns in this scenario please. >> After having avoided the topic for some time (previous attempts to grasp >> the concepts resulted in near brain meltdown!), I have decided now is the [quoted text clipped - 32 lines] >> Thanks for baring with an indexing noob who is trying to get his head >> around this heavy topic! Jay > My WHERE condition involves only the PK. > Can you tell me how it is beneficial to index other columns in this > scenario please. OK, but what is about columns to want to return? Do not use '*' in productuion . Take a look at covering indexes , morever if you are in SQL Server 2005 there is a new feature called INCLUDED where you are able to add non-key columns to the leaf level >> Jay >>> Based on the above scenario, am I correct in thinking that no other [quoted text clipped - 47 lines] >>> Thanks for baring with an indexing noob who is trying to get his head >>> around this heavy topic! It's not only the Primary Key the important thing in indexing. Other fields that you query are also important. For example, if your query returns a large number of rows then using covered index could be a good idea. I think you should also learn how to evaluate query performance. I don't know if you have any idea about this or not, but I'll try to tell you anyway. If you already know, jump to the last paragraph =) When you open SSMS and a New Query, you'll see "Include Actual Execution Plan". Click on it or press CTRL + M to select it. In your test environment, create indexes to test your queries. When this button is selected, you'll see another tab called "Execution Plan" near the "Results" and "Messages" tab. When you click the "Execution Plan", you'll see the SELECT in front of the graph. Point your mouse cursor on it and a hint pops up. "Estimated Sub Query Cost" is the cost of your query. There are some exceptions in this cost's calculation (for example CLR routines, user-defined functions aren't calculated), however this is a good metric to count on. There are also other metrics to calculate your query's performance however this is not the place to talk about all of them. I hope you'll now have more idea about the query costs and you can evaluate your queries' performance. Also, you can use Database Tuning Advisor to see if your indexes are useful or not, or if you need new ones etc. (This tool is shipped only with Standard and Enterprise Editions of SQL Server.)  SignatureEkrem Önsoy >> Jay >>> Based on the above scenario, am I correct in thinking that no other [quoted text clipped - 47 lines] >>> Thanks for baring with an indexing noob who is trying to get his head >>> around this heavy topic! > Based on the above scenario, am I correct in thinking that no other index > would be required on that table (assuming it was not queried in other ways > from other places)? If your only query selects a single row by primary key, then you need no other index. Your only indexing considerations are whether the primary key index should be clustered and, if not, what other columns should be included. > If I have a table of 200 rows and a column called lastname which features > in my WHERE criteria (e.g. where lastname = 'smith'), is an index on the > lastname column necessary or, given the low number of rows, would a table > scan be more efficient overall than creating and maintining an index on > that column? If the table is small, the cost of creating and maintaining the index is negligible anyway so I usually err on the side of index creation. Much depends on how often the query is run. If such a query is executed frequently a busy web site, not only is there a cumulative CPU cost, the exposure to blocking is much higher because every row in the table needs to be touched each time the query runs. This can have a big impact on OLTP concurrency and performance. SignatureHope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ > After having avoided the topic for some time (previous attempts to grasp > the concepts resulted in near brain meltdown!), I have decided now is the [quoted text clipped - 32 lines] > Thanks for baring with an indexing noob who is trying to get his head > around this heavy topic! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.