Order By Primary Key

I've got a table with an a primary key of type 'int' (auto incrementing).
Were talking about millions of rows.
The name of this field is TempID.
I always want to sort by the TempID ascending...is that something that I
even need to specify?
When I query it in Query Analyzer it seems to always be sorted by the
primary key ascending.
Although this is what I want, will this ALWAYS be the case, or should I just
throw an order by in there for good measure?
Stored Proc:
--
@.numberOfRows int,
@.startingID int
set rowcount @.numberOfRows
Select * From tblList Where TempID > @.startingID
set rowcount 0
--
Will this always work how I expect or should I add the "Order By TempID"?
I am asking because I don't know if my Order By clause will slow it down.
I'm sure I wouldn't notice if it was .0000000002 ms slower, but this would
be good to know, especially if @.startingID = 4,000,000 or the @.numberOfRows
is very large.
I hope that all made sense.Read the BOL for more information under "Clustered Indexes":
"An index on the columns specified in the ORDER BY or GROUP BY clause
may remove the need for the Database Engine to sort the data, because
the rows are already sorted. This improves query performance."
Didi you create them as Clustered keys ?
HTH, Jens Suessmeyer.|||INeedADip wrote:
> I've got a table with an a primary key of type 'int' (auto
> incrementing). Were talking about millions of rows.
> The name of this field is TempID.
> I always want to sort by the TempID ascending...is that something
> that I even need to specify?
> When I query it in Query Analyzer it seems to always be sorted by the
> primary key ascending.
> Although this is what I want, will this ALWAYS be the case, or should
> I just throw an order by in there for good measure?
> Stored Proc:
> --
> @.numberOfRows int,
> @.startingID int
> set rowcount @.numberOfRows
> Select * From tblList Where TempID > @.startingID
> set rowcount 0
> --
> Will this always work how I expect or should I add the "Order By
> TempID"? I am asking because I don't know if my Order By clause will
> slow it
> down. I'm sure I wouldn't notice if it was .0000000002 ms slower, but
> this would be good to know, especially if @.startingID = 4,000,000 or
> the @.numberOfRows is very large.
> I hope that all made sense.
The only way to guarantee the order of results is with an ORDER BY
clause. If that column is your PK, you should already have a unique
index on the column. If it's clustered, it will help with sorting. Keep
in mind that sort operations are very costly. While the unique,
clustered index on the PK column might eliminate the need for the sort,
it is no guarantee. You still need the ORDER BY clause.
David Gugick
Quest Software
www.quest.com|||You have a ton of basic mistakes and need to get an intro book on SQL.
Columns are not fields. An auto-increment can never be a relational
key by definition because it is not an attribute of the entities
modeled in the table. Tables by definition have no ordering.
You are confusing an implementation with SQL and proper data modeling.
If you want to be sure that you get your output in order, you need an
ORDER BY clause.
Years ago, SQL Server programmers got screwed up becuase they wrote
code that assumed a GROUP BY clause would be done with a sort under the
covers, so they did not write the ORDER clause. It blew up a ton of
programs on the next release.|||Wow...guess I hit a nerve.
I'm sorry, I should have picked my words a little more carefully.
I figured you would get the jist of it....
Why would auto-increment fields not be an attribute of the entities in the
table?
Isn't that one of the easiest ways to get a unique identifier for that set
of data, making it a key attribute?
"can never be a relational key"...I've never heard that argument.|||There are different schools of thought on using auto-increment fields as
keys. Personally, I tend to agree with Celko on this one. Auto-increment
fields have nothing to do with the data in the table, they are an artificial
way of creating uniqueness. A more meaningful key would suit my tastes.
Using meaningful keys allows you to
1. Reuse the column name and data type in all tables, which makes joins more
intuitive.
Job.deptid = department.deptid
is easier and more intuitive than
job.Department = department.id
2. Use a key value that makes some sort of sense when you look at it.
3. Avoid redundancy in your table and insure normalization.
Often when we have an auto-incremeneting key, there is another column or
columns that also uniquely identify the row.
However, there are many in the field (with as much experience as myself or
Celko) that consider auto-increment keys a requirement in their tables.
As for the terminology, do a search on celko and punch cards to see why he
makes such an issue of it. As long as everone knows what you mean by
"field" and "record" (and we do) I wouldn't lose any sleep over it.
"INeedADip" <INeedADip@.gmail.com> wrote in message
news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Wow...guess I hit a nerve.
> I'm sorry, I should have picked my words a little more carefully.
> I figured you would get the jist of it....
> Why would auto-increment fields not be an attribute of the entities in the
> table?
> Isn't that one of the easiest ways to get a unique identifier for that set
> of data, making it a key attribute?
> "can never be a relational key"...I've never heard that argument.
>|||So you guys are just refering to the name of the column?
I do have incrementing fields in 99% of my tables like AccountID, QueueID,
CampaignID, ect...
If that is what you guys are talking about, I agree....anyways...
Thanks for educating me on the Order By, I was under the impression that if
I used an index on the field, I could save some time by not using the Order
By. And just to note...I'm wasn't asking this question as a general rule,
but in this one case (this specific table) I was trying to save some time
because the table is so large and I will only be getting chunks in ascending
order EVERY TIME...but if it is not guaranteed, then I will add the "Order
By".
- Thanks|||Hi Jim,
I've got to take the bite and put the surrogate key side forward.
If a natural key exists on the table (that isn't in reality a generated
number in itself - for instance deptid would indicate its auto-generated
some how), most keys in the natural world are actually a surrogate of some
form or another.
There are problems duplicating the natural key everywhere, especially if its
a composite key; there is no difference in joining whether you use a
surrogate key or a natural one to join.
INeedADip - do a search on 'surrogate key' and 'identity' for some really
good and extensive discussions on the benefits of the surrogate key
approach.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
> There are different schools of thought on using auto-increment fields as
> keys. Personally, I tend to agree with Celko on this one. Auto-increment
> fields have nothing to do with the data in the table, they are an
> artificial
> way of creating uniqueness. A more meaningful key would suit my tastes.
> Using meaningful keys allows you to
> 1. Reuse the column name and data type in all tables, which makes joins
> more
> intuitive.
> Job.deptid = department.deptid
> is easier and more intuitive than
> job.Department = department.id
> 2. Use a key value that makes some sort of sense when you look at it.
> 3. Avoid redundancy in your table and insure normalization.
> Often when we have an auto-incremeneting key, there is another column
> or
> columns that also uniquely identify the row.
> However, there are many in the field (with as much experience as myself or
> Celko) that consider auto-increment keys a requirement in their tables.
> As for the terminology, do a search on celko and punch cards to see why he
> makes such an issue of it. As long as everone knows what you mean by
> "field" and "record" (and we do) I wouldn't lose any sleep over it.
>
> "INeedADip" <INeedADip@.gmail.com> wrote in message
> news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Tony.
I believe there was a fairly in depth discussion about this last w,
although I purge my postings frequently and can't locate it. If I recall
correctly, you were one of the folks involved in the discussion. Would you
mind posting a link to it?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I've got to take the bite and put the surrogate key side forward.
> If a natural key exists on the table (that isn't in reality a generated
> number in itself - for instance deptid would indicate its auto-generated
> some how), most keys in the natural world are actually a surrogate of some
> form or another.
> There are problems duplicating the natural key everywhere, especially if
its
> a composite key; there is no difference in joining whether you use a
> surrogate key or a natural one to join.
> INeedADip - do a search on 'surrogate key' and 'identity' for some really
> good and extensive discussions on the benefits of the surrogate key
> approach.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
Auto-increment
or
he
>|||I think this is one of them...
6d2a8ccf940fe2" target="_blank">http://groups.google.co.uk/group/co...
6d2a8ccf940fe2
but there are many, search on 'surrogate key' rogerson --celko--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eIiy5BGIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Thanks Tony.
> I believe there was a fairly in depth discussion about this last w,
> although I purge my postings frequently and can't locate it. If I recall
> correctly, you were one of the folks involved in the discussion. Would
> you
> mind posting a link to it?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> its
> Auto-increment
> or
> he
>