SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 INDEXES
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hinduson
Yak Posting Veteran

Ghana
69 Posts

Posted - 10/14/2013 :  06:59:42  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/14/2013 :  07:03:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 10/14/2013 :  08:43:34  Show Profile  Reply with Quote
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

Ghana
69 Posts

Posted - 10/14/2013 :  18:47:24  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 10/14/2013 :  18:53:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Ghana
69 Posts

Posted - 10/14/2013 :  18:57:58  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 10/14/2013 :  19:14:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Ghana
69 Posts

Posted - 10/14/2013 :  19:18:30  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 10/14/2013 :  19:20:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Ghana
69 Posts

Posted - 10/14/2013 :  19:29:20  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 10/14/2013 :  19:36:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Ghana
69 Posts

Posted - 10/14/2013 :  19:52:07  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 10/14/2013 :  19:54:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Ghana
69 Posts

Posted - 10/14/2013 :  20:20:20  Show Profile  Reply with Quote
Yes Sir.

Best Regards.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 10/18/2013 :  11:04:38  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 10/18/2013 :  11:12:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 10/18/2013 :  11:27:35  Show Profile  Reply with Quote
999 non clustered. http://technet.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 10/18/2013 :  14:43:50  Show Profile  Reply with Quote
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

Nigeria
7 Posts

Posted - 05/31/2014 :  13:55:11  Show Profile  Reply with Quote
Good work guys i think i have the same project with Hinduson thank you all for your answers

Niit
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000