| Author |
Topic |
|
Hinduson
Yak Posting Veteran
69 Posts |
 Posted - 2013-10-14 : 06:59:42
|
| Create the appropriate Indexes to speed up the execution of the following tasks.*Extract the order details for all the purchase orders in the current monthExtract the details of all the orders placed more than two weeks.I got this syntax but am not sure its meant for this questions above. I would like someone to please help me out with the right once for the above.CREATE INDEX A.index,ON TRANSACTIONS_ORDERDETAILS (PurchaseOrderID).Thank you.Best Regards. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-14 : 07:03:22
|
| Nope..it depends on the query you use for the above tasks.Can you post your current query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-14 : 08:43:34
|
| Usually an index on the column(s) that you want to filter on helps. In your case, it is not PurchaseOrderID that you want to filter on, it is the order date, isn't it? |
|
|
|
Hinduson
Yak Posting Veteran
69 Posts |
Posted - 2013-10-14 : 18:47:24
|
| CREATE CLUSTERED INDEX Index_Name_Clstd ON TRANSACTIONS_ORDERDETAILS (PurchaseOrderID) WITH FILLFACTOR=30;But I don't really know if this is the real answer to the Question Visakh16*Extract the order details for all the purchase orders in the current month*Extract the details of all the orders placed more than two weeks.Best Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-14 : 18:53:12
|
| The index needs to be on the date/time column. And why did you add FILLFACTOR=30?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Hinduson
Yak Posting Veteran
69 Posts |
Posted - 2013-10-14 : 18:57:58
|
| Bro Tkizer, am new to this topic and I don't really know much about it. So am following the formula used and it my instincts tells me its not right, so thats why I need further clearance. So please can you help me out with how to do it right?Best Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-14 : 19:14:38
|
| Replace PurchaseOrderID with the date/time column and remove the FILLFACTOR part. I am not sure what formula you used, but FILLFACTOR=30 would be very rarely used, if ever.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Hinduson
Yak Posting Veteran
69 Posts |
Posted - 2013-10-14 : 19:18:30
|
| I did as you said and it gave me this error.CREATE CLUSTERED INDEX Index_Name_Clstd ON TRANSACTIONS_ORDERDETAILS (Orderdate) Msg 1902, Level 16, State 3, Line 1Cannot create more than one clustered index on table 'TRANSACTIONS_ORDERDETAILS'. Drop the existing clustered index 'PK__TRANSACTIONS_ORD__17036CC0' before creating another.Best Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-14 : 19:20:45
|
| You can only have one CLUSTERED index on each table. It seems your primary key was created as clustered, so you'll need to instead create a NONCLUSTERED index (or change the PK to nonclustered).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Hinduson
Yak Posting Veteran
69 Posts |
Posted - 2013-10-14 : 19:29:20
|
| I made it a non clustered index and it worked.But i would like to know how do I test the Index created? I mean like a query to try the index created?Best Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-14 : 19:36:55
|
These would test it:quote:
Extract the order details for all the purchase orders in the current monthExtract the details of all the orders placed more than two weeks.
You would view the execution plans of the queries to see if the index was selected by the query optimizer or not.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Hinduson
Yak Posting Veteran
69 Posts |
Posted - 2013-10-14 : 19:52:07
|
| I tried the first one and it gave me this errorExtract the order details for all the purchase orders in the current monthMsg 102, Level 15, State 1, Line 1Incorrect syntax near 'details'.Best Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-14 : 19:54:27
|
| You have to write the queries. Your questions are clearly homework questions. We are not here to do your homework for you. You must show some effort. Write SELECT queries for the two "extracts" mentioned in the homework.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Hinduson
Yak Posting Veteran
69 Posts |
Posted - 2013-10-14 : 20:20:20
|
| Yes Sir.Best Regards. |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-10-18 : 11:12:56
|
quote:
Originally posted by tkizer
The index needs to be on the date/time column. And why did you add FILLFACTOR=30?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Maximum how many indexes can we create on a table in SQL Server 2008?-- Thanks and RegardsSrikar Reddy Gondesi,Trainee SQL Server Database AdministratorMiracle Software systems ,Inc. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-18 : 11:27:35
|
| 999 non clustered. http://technet.microsoft.com/en-us/library/ms143432(v=sql.100).aspx |
|
|
|
sgondesi
Posting Yak Master
200 Posts |
Posted - 2013-10-18 : 14:43:50
|
quote:
Originally posted by James K
999 non clustered. http://technet.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
Thanks for the link.-- Thanks and RegardsSrikar Reddy Gondesi,Trainee SQL Server Database AdministratorMiracle Software systems ,Inc. |
|
|
|
sfalalu
Starting Member
7 Posts |
Posted - 2014-05-31 : 13:55:11
|
| Good work guys i think i have the same project with Hinduson thank you all for your answersNiit |
|
|
|
|
INDEXES