November 24, 2003 at 9:53 am
quote:
...Now, your (sic) telling me that you want to change the clustered index (primary key) from customer_id to last_order_dt to improve performance???
I've seen a few comments that suggest that people believe that clustered indices and primary keys are the same thing; they are not. You can place a clustered index on any column, regardless of whether or not it is a primary key. The column does not even have to be unique; hence, there is no technical reason that you cannot place a clustered index on Last_Order_Dt column, and doing so does not mean that that column suddenly becomes a primary key, nor does it mean that the Customer_ID column now cannot serve as the primary key. Clustered indices do require (as do all indices) the ability to uniquely identify a single row in the table; so, if the Last_Order_dt column is not unique (which I assume it is not), the clustered index will add an "invisible" column to the index that will maintain uniqueness, but this detail is hidden from the user. The important point is that primary keys and clustered indices are two separate things.
November 24, 2003 at 10:38 am
quote:
I think i misunderstand the clustered index!I have a table with the following structure
CREATE TABLE [Tb_Amz_UnitChange81] (
[Fd_Ucg_Term] [char] (8) ,
[Fd_Ucg_Regno] [int] NOT NULL ,
[Fd_Ucg_ClassId] [int] NOT NULL ,
[Fd_Ucg_CourseId] [int] NOT NULL ,
[Fd_Ucg_Action] [smallint] NOT NULL ,
[Fd_Ucg_FormType] [char] (8) ,
[Fd_Ucg_PermitNumber] [char] (15) ,
[Fd_Ucg_PermitDate] [char] (10) ,
[Fd_Ucg_SaveUserName] [char] (50) ,
[Fd_Ucg_FormNumber] [smallint] NOT NULL ,
[Fd_Ucg_Year] [smallint] NOT NULL ,
CONSTRAINT [PK_Tb_Amz_UnitChange81] PRIMARY KEY CLUSTERED
(
[Fd_Ucg_Term],[Fd_Ucg_Regno],[Fd_Ucg_ClassId],[Fd_Ucg_CourseId],
[Fd_Ucg_Action],[Fd_Ucg_FormType],[Fd_Ucg_FormNumber],[Fd_Ucg_Year]
) )
we have many insert and delete against the table but no update .and
most of our query like the following :
select * from Tb_Amz_UnitChange81
where Fd_Ucg_Year=xxxx
and Fd_Ucg_Term='xxxxxxxx'
and Fd_Ucg_Regno=xxxxxx
I have two questions:
1-should I change primary key from clustered to non clustered?since we don't need any presort on table .
2-On a table we have a varchar (30) column for family .most of searching and sorting is based on this column ,
is it a good idea to create clustered index on this column?
I read somewhere when we use clustered index ,all nonclustered indexes carry value of clustered index as the key
so it is not recommended clustered index on large column.
any idea would be appreciate.
For the query you show, SQL Server will not use your primary key as an index, because of the differences in structure between your primary key and your Where clause. For best performance of your query, you need a non-clustered composite index on FD_Ucg_Year, Fd_Ucg_Term, and Fd_Ucg_Regno using these columns in this SAME order. This and all other non-clustered indexes will reference the clustered index (currently your primary key) during all seek operations.
Your clustered index is currently a composite of 8 columns and is 34 bytes wide. If your table isn’t very large, this doesn’t really matter, but if your table is large, the wide clustered key will cause more node levels in your indexes than necessary. For this reason, the answer to your first question is probably “Yes, your primary key and clustered key should be different.” I think this is a good candidate for a clustered key that is an integer column (only 4 bytes) with the identity property.
For the reason already stated (key width), making your clustered key on a column that is a varchar(30) may lead to poor performance. However, if the average value for this column is relatively short (say only 8 characters), this would not be too bad, especially if the values are unique. If the values are not unique, SQL Server will add an integer (4 byte) uniqueafier value. This decision needs be made with knowledge of the data.
Having said all that, you must remember that these are guidelines based on experience and knowledge of internal SQL Server structures. The size of your table and uniqueness of your column values influence these and other decisions. You can only be sure by testing both ways.
Hope this helps
Larry Ansley
Atlanta, GA
Larry Ansley
Atlanta, GA
November 24, 2003 at 10:52 am
This is the most un-proper 'right' anwser I have ever seen. If customerID's integraity could be negotiable for the order_date performance, the developer and/or DBA could be in the unemployment office already. Please select some realistice answer instead of accadamic discussion, please....
November 24, 2003 at 11:06 am
dlu: Do consider that this discussion forum for the QoD is NOT an official statement from the people that formulated the QoD for 21-NOV. Please separate our conversations about the impacts of the answers from the answers themselves. You want a detailed discussion come to the forum, if you think it's to academic just skip it
Ok, maybe you are right, the answers sometimes can be a little to short or unclear and maybe they should include a link to BOL and tell people they should either come up with their own QoDs (and send them in of course) so some of us can apply constructive criticism on them, or just stop whining
Regards, Hans!
November 24, 2003 at 11:27 am
I enjoy the daily quitz very much, until this one. Hope the web sit quitz editors could be a little careful about the published quitz from now on. If this kind un-realistic one happen 2 more times, the editor should be 'striked' out.... Please don't lower your standard.....
November 24, 2003 at 11:30 am
Wow! 7 topics & nearly 100 posts on a single QOD!
I put in my 2 cents a while back in TomBrattin's topic, but a few things stand out -
1. There's confusion about clustered index - that it must be the PK. If there's one thing to get out of this QOD, remember that in the right circumstances, performance can be greatly improved by moving the clustered index away from the PK.
2. The PK must be unique, but the clustered index need not. There can be only one of each per table.
I think it was a great QOD from Brian Knight, from both the response generated & the existing misconceptions about PK & clustered indexes. Yes, I still think it was contrived (who would ever run that simple query every second on one table?), but excellent for the concept.
Data: Easy to spill, hard to clean up!
November 24, 2003 at 1:18 pm
I think it is important to remember that the QOD has to be very simple in nature.
I can not imagine how difficult it would be to determine a new "real-world" scenario with exhausting detail every day. Not only that, but answering such a question could take days or weeks, as such activities do in the "real-world".
Instead, these questions are greatly simplified what-ifs that generally focus on one or two facets of a problem. To answer them it is necessary for us to limit our own thinking to the boundaries of these same qestions.
In many ways these are similar to certification type questions where you have to look for key words and phrases like 'Least admin effort' or 'Greatest read performance'...
anyway... another log on the fire.
-Dan B
November 24, 2003 at 2:03 pm
quote:
I think it is important to remember that the QOD has to be very simple in nature.I can not imagine how difficult it would be to determine a new "real-world" scenario with exhausting detail every day. Not only that, but answering such a question could take days or weeks, as such activities do in the "real-world".
Instead, these questions are greatly simplified what-ifs that generally focus on one or two facets of a problem. To answer them it is necessary for us to limit our own thinking to the boundaries of these same qestions.
In many ways these are similar to certification type questions where you have to look for key words and phrases like 'Least admin effort' or 'Greatest read performance'...
anyway... another log on the fire.
-Dan B
Please don't confuse us with the facts. We are having too much fun.
Larry Ansley
Atlanta, GA
Larry Ansley
Atlanta, GA
November 24, 2003 at 2:05 pm
Sorry! Carry on!
-Dan B
November 25, 2003 at 12:35 am
Well, here in Germany we have a phrase which translates to something like:
'Nothing is as old as yesterday's newspaper!'
...but this thread is really interesting!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy