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.
| Author |
Topic |
|
patrick.davey
Starting Member
1 Post |
 Posted - 2009-04-27 : 17:30:25
|
| Hi,I have been reading up on indexing - how to create, when to use etc... but before I jump into it (on a dev box and then production) I just have a couple of questions.0) Is there any risk to the underlying data in creating and deleting an index? Could that end up making things slower than they currently are? Or are they (as I understand it) created quite separately and safe to play with?1) Is there a brilliant introduction to indexes that anyone knows about? (book or online?)2) If a usual search is a date range - is it worth creating an index on a date field - especially when that field has a default getDate() upon insertion which never changes? Is SQL Server clever enough to know that it's basically indexed already? (or is it!?)3) Any columns which are usually filtered on are candidates for an index? Is it best to create one index for each of these columns?4) Best practice / scripts to test the efficacy of these new filters?Apologies if this is too basic / wrong place to post.. I have looked honestly ;) But I'm just a little nervous about getting much further into this! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-27 : 17:56:46
|
| 0) Adding too many indexes could hinder write performance. There is no risk to the underlyding data though. 1) I don't know of one, but it takes lots of experience to be very comfortable with it. It's from all of this experience that you get comfortable with execution plans, index reports, duplicate indexes, statistics, etc...2) Yes it is worth creating an index on the datetime column if you are always using that in your WHERE clause. A default of GETDATE() has nothing to do with an index. That is known as a default constraint and is not related to indexes. You definitely need to index that column.3) a. Yes but remember you can also use composite indexes. b. It depends. You don't want to overly index and you should try to use composite indexes where possible. If you have this WHERE Column1 = @v1 AND Column2 = @v2 then I'd add an index on Column1,Column2 and would not individually index them. Do not add an index to Column1 in this scenario since that would be considered a duplicate. An index on Column2 may be needed for other queries though. 4) I use this a lot, but you need to understand the output: http://sqlserverpedia.com/wiki/Index_Related_DMV_QueriesTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-05-09 : 23:03:20
|
| Also Use Execution plan and SET Statistics..... to analyze indexes. |
|
|
|
|
|
|
Basic Indexing Question (SQL2005)