| Author |
Topic |
|
wshtrue
Yak Posting Veteran
74 Posts |
 Posted - 2004-11-09 : 18:27:37
|
| I am new to programming.Never did indexing on few columns.Can anyone help me do indexing.For an example if i have 2 tables like A and BTable ANamePhone NumberTable BIdHow to do indexing and how to decide whether i should go for Non Clustered IndexComposite IndexCovering Index etc Once indexing is done what would be the steps to see that my indexing is working.Thank You in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 18:28:52
|
| Creating indexes is done with CREATE INDEX statement. Deciding on which indexes and what type to add is determined by looking at the queries. Please post them if you want us to help you decide.To see if an index is being used in a query, view the execution plan in Query Analyzer.Tara |
 |
|
|
wshtrue
Yak Posting Veteran
74 Posts |
Posted - 2004-11-09 : 18:35:35
|
| Hi Tara,Thank You so much for your prompt reply but to be honest i am just learning it so that i can do it in the future.Can you please give me syntax and steps doing on the column names i have mentioned.Can you think that these are the column names for the time being.Thank You |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 18:39:43
|
| Here's one example:CREATE NONCLUSTERD INDEX idx_Table1_Column1 ON (Column1)For more detailed examples, please see the CREATE INDEX topic in SQL Server Books Online.Tara |
|
|
|
wshtrue
Yak Posting Veteran
74 Posts |
Posted - 2004-11-09 : 18:48:32
|
| Tara,Thanks but it gave the following error messageIncorrect syntax near 'NONCLUSTERD' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 18:50:15
|
| Spelling mistake...NONCLUSTEREDI wouldn't start adding these on your tables unless you know you need them. Adding unnecessary indexes will cause inserts and updates to slow down.Tara |
|
|
|
wshtrue
Yak Posting Veteran
74 Posts |
Posted - 2004-11-09 : 18:57:15
|
| Sorry Tara,Now it gave this messageIncorrect syntax near '('I appreciate your advice.I really want to know about indexing. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 18:59:33
|
| I neglected to add the table in the script. CREATE NONCLUSTERED INDEX idx_Table1_Column1 ON Table1(Column1)Please look at the examples and syntax in SQL Server Books Online for more details.Tara |
|
|
|
wshtrue
Yak Posting Veteran
74 Posts |
Posted - 2004-11-09 : 19:06:28
|
| Thank You Tara! It did work now and gave me the messageThe command(s) completed successfully.As you said i should go to QA and see execution plan. What should i look for? Please tell me how do i know that it did index though it gave me the successful message.Please help me in verifying that my indexing is working. Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 19:08:20
|
| You should look for the index in the execution plan. I'm thinking you're going to need to read up on how to read execution plans though.Tara |
|
|
|
wshtrue
Yak Posting Veteran
74 Posts |
Posted - 2004-11-09 : 19:18:06
|
| I could read indcreate cost:0% but didn't know what does that mean. I have been trying to study on indexes but it is so confusing. Thanks I appreciate your help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-09 : 19:20:59
|
| Don't look at the execution plan for the CREATE INDEX statement. Once you have your queries created, run them in Query Analyzer and view the execution plan for them.Tara |
|
|
|
wshtrue
Yak Posting Veteran
74 Posts |
Posted - 2004-11-09 : 19:25:04
|
| OK Tara,You are so good! I am totally new to programming but want to become a programmer overnight.Please Please let me know once i run my queries then i should look for what? in execution plan and what would that mean.Thanks |
|
|
|
|
steps on how to do indexing