By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,498 Members | 1,558 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,498 IT Pros & Developers. It's quick & easy.

Adding an auto-increment column to existing table with a particular order

P: n/a
Hello all,

I'm using SS2K on W2k.

I'v got a table say, humm, "Orders" with two fields in the PK:
OrderDate and CustomerID. I would like to add an "ID" column which
would be auto-increment (and would be the new PK). But, I would really
like to have orders with the oldest OrderDate having the smallest ID
number and, for a same OrderDate, I'd to have the smallest CustomerID
first. So my question is:

How could I add an auto-increment column to a table and make it create
its values in a particular order (sort by OrderDate then CustomerID
here)?

In the real situation, the table I want to modify has around 500k
records and the PK has 5 fields and I want to sort on three of them.

Thanks for you help

Yannick

Oct 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
IDENTITY is the proper name for an auto-incrementing surrogate key
column in SQL Server.

In principle you should avoid using IDENTITY in any way that is
meaningful outside the database. In fact, don't show the IDENTITY
column to your users AT ALL in your applications. The main reason why
we use IDENTITY columns is to provide an immutable surrogate key. We
know from experience that once we expose data to users it may become
significant in some way, at which point its notional immutability may
be at risk because it is no longer just an attribute of the system - it
belongs to the outside world as well. Then we may have a problem
because an IDENTITY column is not updateable and because sometimes (for
example when you integrate data or perform multiple row INSERTs) you
can't always dictate its value directly on INSERT either.

So for those reasons I'd say that you shouldn't concern yourself about
the order in which the IDENTITY values are assigned. The order doesn't
matter much if only the developers and admins can see it.

If you want the cosmetic reassurance of seeing the values in the
"right" order you can try creating a new table and using INSERT SELECT
with an ORDER BY clause to populate it. Be aware that that method is
definitely NOT always guaranteed to generate the values in the correct
order - so don't count on it. You may also find that the effort of
creating and populating a new table negates any slight advantage of
"fixing" the IDENTITY sequence.

--
David Portas
SQL Server MVP
--

Oct 19 '05 #2

P: n/a
David Portas (RE****************************@acm.org) writes:
If you want the cosmetic reassurance of seeing the values in the
"right" order you can try creating a new table and using INSERT SELECT
with an ORDER BY clause to populate it. Be aware that that method is
definitely NOT always guaranteed to generate the values in the correct
order - so don't count on it. You may also find that the effort of
creating and populating a new table negates any slight advantage of
"fixing" the IDENTITY sequence.


In fact, the word I have from Microsoft people is that INSERT SELECT
ORDER BY is guaranteed to produce the expected result.

This however does not apply to SELECT INTO.

I would recommend, nevertheless, to add an OPTION (MAXDOP 1) as an
extra precaution.

It is also worth pointing that the main reason for having IDENTITY
in opposition ro rolling your own, is that IDENTITY scales better in
an environment where there is a heavy insertion rate. When you roll
your own, inserts tend to get serialized.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 19 '05 #3

P: n/a
Hi Erland,

You don't believe everything MS tell you, do you? :-) Thanks due to Gert-Jan
for the following repro. Tested on SP4 (with or without MAXDOP) it doesn't
order the INSERT (YMMV). Admittedly it might sometimes be possible to order
*some* INSERTs but how are we supposed to know which will work, which won't
and which will fall apart after then next hotfix?

Hopefully the ORDER BY on INSERT nonsense will go away one day. It's plainly
a limitation rather than a feature.

--drop table Item
create table Item
(ItemID int not null primary key
,ItemGUID uniqueidentifier not null -- newid()
,ParentTopicID int null
,OwningUserID int null
,Name varchar(100) null
,SEName varchar(100) null
,ShortName varchar(50) null
,AboutDisabled bit null
,DisplayOrder int null
,Description varchar(200) null
,ShopDisabled tinyint null
,ShopNoCaption varchar(20) null
,GeneralShopDisabled tinyint null
,RelatedSitesDisabled tinyint null
,IsDisabled tinyint null
,CreatedOn datetime null
,VisitURL varchar(256) null
,VisitPrompt varchar(200) null
,ShopURL varchar(256) null
,Address1 varchar(100) null
,Address2 varchar(100) null
,Suite int null
,City varchar(30) null
,State char(2) null
,ZipCode char(5) null
,Country varchar(50)
,Phone varchar(20)
,FAX varchar(20)
,URL varchar(256)
,Email varchar(100)
,OvertureKeywords varchar(200)
,SE_Title varchar(100)
,SE_Description varchar(50)
,SE_Keywords varchar(50)
,ExtendedData varchar(100)
,SubmittedByUserID int
)
insert into Item (ItemID,IsDisabled,ZipCode,Name,itemguid,CreatedOn
,RelatedSitesDisabled,GeneralShopDisabled,ShopDisa bled,DisplayOrder,AboutDisabled
,ShortName
) select id,0,'abcd',name,newid(),dateadd(ms,-(id%2000000),getdate())
,id%2,id%3,id%4,1,0, left(name,15)
from sysobjects
--drop table ItemRatings
create table ItemRatings
(ItemID int not null primary key
,rating int not null
,HasComment tinyint not null
,TopicID int null
,userID int null
,userAge int null
,userGender char(1) null
,f7 varchar(20) null
,f8 varchar(20) null
,f9 varchar(20) null
,f10 varchar(20) null
,f11 varchar(20) null
,f12 varchar(20) null
,f13 varchar(20) null
,f14 varchar(20) null
,f15 varchar(20) null
,f16 varchar(20) null
,f17 varchar(20) null
,f18 varchar(20) null
,f19 varchar(20) null
,f20 varchar(20) null
)
insert into ItemRatings(ItemID,rating,HasComment,TopicID,userI D
,userAge,userGender,f7,f8,f9,f10,f11,f12)
select id,id%10,id%2,90,20069715,id%80,'M'
,'aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaa a','aaaaaaaaaa','aaaaaaaaaa'
from sysobjects
insert into ItemRatings(ItemID,rating,HasComment,TopicID,userI D
,userAge,userGender,f7,f8,f9,f10,f11,f12)
select id%2000000000+1000000,id%10,id%2,90,id%19,id%80,'M '
,'bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bb bbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bbbbbb bbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb'
from sysobjects
--drop table Alerts
create table Alerts
(AlertID int not null primary key
,ItemID int not null
,alertOwnerUserID int not null
,alertType int not null
,f4 char(200) null
)
insert into Alerts
select id,id,20069715,1,' '
from sysobjects
--drop TABLE #tmp
CREATE TABLE #tmp
(rownum int not null identity(1,1) primary key
,[ItemID] [int] NOT NULL
,[ItemGUID] [uniqueidentifier] NOT NULL
,[ParentTopicID] [int] NULL
,[OwningUserID] [int] NULL
,[Name] [varchar] (250) NOT NULL
,[SEName] [varchar] (250) NULL
,[ShortName] [varchar] (35) NOT NULL
,[AboutDisabled] [bit] NOT NULL
,[DisplayOrder] [int] NOT NULL
,[Description] [ntext] NULL
,[ShopDisabled] [tinyint] NOT NULL
,[ShopNoCaption] [tinyint] NULL
,[GeneralShopDisabled] [tinyint] NOT NULL
,[RelatedSitesDisabled] [tinyint] NOT NULL
,[IsDisabled] [tinyint] NOT NULL
,[CreatedOn] [datetime] NOT NULL
,[VisitURL] [ntext] NULL
,[VisitPrompt] [varchar] (50) NULL
,[ShopURL] [ntext] NULL
,[Address1] [varchar] (255) NULL
,[Address2] [varchar] (255) NULL
,[Suite] [varchar] (100) NULL
,[City] [varchar] (255) NULL
,[State] [varchar] (100) NULL
,[ZipCode] [varchar] (10) NULL
,[Country] [varchar] (100) NULL
,[Phone] [varchar] (50) NULL
,[FAX] [varchar] (50) NULL
,[url] [ntext] NULL
,[email] [varchar] (100) NULL
,[OvertureKeywords] [ntext] NULL
,[SE_Title] [ntext] NULL
,[SE_Description] [ntext] NULL
,[SE_Keywords] [ntext] NULL
,[ExtendedData] [ntext] NULL
,[SubmittedByUserID] [int] NULL
,[NumRatings] [int] not null
,[AvgRating] [decimal](6, 5) not null
,[NumComments] [int] not null
,[UserRating] [decimal](3,2) not null
,[AlertUser] [int] not null
)
-- set showplan_text on
INSERT #tmp (ItemID, ItemGUID, ParentTopicID, OwningUserID, Name,
SEName, ShortName, AboutDisabled, DisplayOrder,
Description, ShopDisabled, ShopNoCaption, GeneralShopDisabled,
RelatedSitesDisabled, IsDisabled, CreatedOn, VisitURL,
VisitPrompt, ShopURL, Address1, Address2, Suite, City, State, ZipCode,
Country, Phone, FAX, URL, Email,
OvertureKeywords, SE_Title, SE_Description, SE_Keywords, ExtendedData,
SubmittedByUserID, NumRatings, AvgRating,
NumComments, UserRating, AlertUser)
SELECT
i.*
, isnull(a.NumRatings,0) NumRatings
, isnull(a.AvgRating, 0) AvgRating
, isnull(a.NumComments, 0) NumComments
, isnull(r.rating, 0)
, isnull(al.alertOwnerUserID, -1)
FROM Item i (nolock)
join (
select
ir.itemid
, count(ir.rating) NumRatings
, avg(ir.rating*1.0) AvgRating
, sum(ir.HasComment) NumComments
from ItemRatings ir with (nolock)
where ir.TopicID = 90
and ir.userAge between 0 and 255
and ir.userGender = coalesce(null, ir.userGender)
group by ir.itemid
) a on i.itemid = a.itemid
left join ItemRatings r
on i.itemid = r.itemid and r.userid = 20069715
left join Alerts al
on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and
alertType = 1
WHERE i.IsDisabled = 0
and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, ''))
ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc
-- set showplan_text off
select top 100 rownum,avgrating,numratings,name
from #tmp
order by rownum

--
David Portas
SQL Server MVP
--
Oct 19 '05 #4

P: n/a
David Portas (RE****************************@acm.org) writes:
You don't believe everything MS tell you, do you? :-)
If MS says "we guarantee this", and I get an unexpected result, that's
a bug, for which I can require a hotfix, if there are business reasons
enough for it. If they say "this is undefined", I am left on my own in
the wilderness.

It might be that this was only for SQL 2005, but I'm quite sure that
it applied to SQL 2000 as well. I need to research this a little more.
(The repro does produce an ordered output when I test on SQL 2005.
Thanks due to Gert-Jan for the following repro. Tested on SP4 (with or
without MAXDOP) it doesn't order the INSERT (YMMV). Admittedly it might
sometimes be possible to order *some* INSERTs but how are we supposed to
know which will work, which won't and which will fall apart after then
next hotfix?


With that reasoning, how could you trust SQL Server at all? After the
next hotfix, it may say that 2 + 2 = 5.

I recall that during the beta 2 phase of SQL 2000, someone in the beta
groups asked about this, because he had a case where he did not get the
expected results. But he also reported that with the RTM version, he did
get the correct results. I also recall that I tried to produce a repro
where the IDENTITY values did not follow the order. My plan was then
to open a case, in order to get a statement whether I had a bug or
not. However, I was never able to produce such a repro.

So saying that it "works sometimes" is quite an understatement. It works
most of the time, and in this particular case the question was about a
one-off thing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 20 '05 #5

P: n/a
Take a look at this KB:
http://support.microsoft.com/default...b;en-us;273586

It gives an example and does indeed say that IDENTITY order is guraranteed.
What it doesn't do is explain whether this is true in the case of every
INSERT. The reason for my doubt is that this KB was changed - it used to
state clearly that the IDENTITY behaviour was undefined!

I think the question will be pretty academic soon. In 2005 the ROW_NUMBER
function is surely a better way (better defined at least) to generate a
numbered sequence.

--
David Portas
SQL Server MVP
--

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
David Portas (RE****************************@acm.org) writes:
You don't believe everything MS tell you, do you? :-)


If MS says "we guarantee this", and I get an unexpected result, that's
a bug, for which I can require a hotfix, if there are business reasons
enough for it. If they say "this is undefined", I am left on my own in
the wilderness.

It might be that this was only for SQL 2005, but I'm quite sure that
it applied to SQL 2000 as well. I need to research this a little more.
(The repro does produce an ordered output when I test on SQL 2005.
Thanks due to Gert-Jan for the following repro. Tested on SP4 (with or
without MAXDOP) it doesn't order the INSERT (YMMV). Admittedly it might
sometimes be possible to order *some* INSERTs but how are we supposed to
know which will work, which won't and which will fall apart after then
next hotfix?


With that reasoning, how could you trust SQL Server at all? After the
next hotfix, it may say that 2 + 2 = 5.

I recall that during the beta 2 phase of SQL 2000, someone in the beta
groups asked about this, because he had a case where he did not get the
expected results. But he also reported that with the RTM version, he did
get the correct results. I also recall that I tried to produce a repro
where the IDENTITY values did not follow the order. My plan was then
to open a case, in order to get a statement whether I had a bug or
not. However, I was never able to produce such a repro.

So saying that it "works sometimes" is quite an understatement. It works
most of the time, and in this particular case the question was about a
one-off thing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 21 '05 #6

P: n/a
David Portas (RE****************************@acm.org) writes:
Take a look at this KB:
http://support.microsoft.com/default...b;en-us;273586

It gives an example and does indeed say that IDENTITY order is
guraranteed.
What it doesn't do is explain whether this is true in the case of every
INSERT. The reason for my doubt is that this KB was changed - it used to
state clearly that the IDENTITY behaviour was undefined!
Since it does insert any waivers, disclaimers etc, it is a natural
interpretation that you can expect INSERT SELECT to work under all
circumstances, and thus Gert-Jan's repro is a bug. Or the article is
in error.
I think the question will be pretty academic soon. In 2005 the ROW_NUMBER
function is surely a better way (better defined at least) to generate a
numbered sequence.


I've checked my references for this statement, and the statement for
SQL 2005 is from a more reliable source. So I would not say that
row_number() is any better defined than IDENTITY. But I do agree for
many situations, row_number() is preferable. However, in this particular
case, Yannick wanted to populate an IDENTITY column, so a plain ORDER BY
is certainly simpler, as it relieves him from SET IDENTITY_INSERT ON.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 21 '05 #7

P: n/a
Thanks all for you time on this question.

I clearly understand that IDENTITY shouldn't mean anything and it is
not that important that it follows exactly my "sort wishes". It was
simply to make table hacking more consistant with other tables where I
sort by IDENTITY to see last insertions. Using the INSERT INTO...
SELECT... ORDER BY will do the job... I hope so! ;o)

Yannick

Oct 24 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.