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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 INDEXES

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 month
Extract 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 1
Cannot create more than one clustered index on table 'TRANSACTIONS_ORDERDETAILS'. Drop the existing clustered index 'PK__TRANSACTIONS_ORD__17036CC0' before creating another.


Best Regards.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 month
Extract 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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-14 : 19:52:07
I tried the first one and it gave me this error

Extract the order details for all the purchase orders in the current month


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'details'.


Best Regards.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-10-14 : 20:20:20
Yes Sir.

Best Regards.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-18 : 11:04:38
quote:
Originally posted by Hinduson

Yes Sir.

Best Regards.


Hai Hinduson..
i am also a beginner like you to SQL Server.
I am learning SQL Server concepts from the you tube videos, SQL Server PDF's from wrox publications and the videos provided in the below mentioned sites.
1. http://technet.microsoft.com/en-us/sqlserver/ff977043
2. http://www.sqlservervideos.com/series/
3. http://www.lynda.com/SQL-Server-tutorials/SQL-Server-Reporting-Services-Depth/110282-2.html
4. http://www.creativecommit.com/courses/SQL2008

Utilize these if you want to gain some knowledge in SQL Server Database.


--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems ,Inc.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Maximum how many indexes can we create on a table in SQL Server 2008?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems ,Inc.
Go to Top of Page

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
Go to Top of Page

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 Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems ,Inc.
Go to Top of Page

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 answers

Niit
Go to Top of Page
   

- Advertisement -