January 19, 2005 at 1:33 pm
Currently, i'm developing an e-store with categories and products identified by their autonumeric id assigned by sql when they are add it to the table. A work partner has told me that this could be wrong because sql server can suddenly reassign those autonumeric ID's. So, in your experience have you ever know that this happened? in that case, should we create a manual Product ID, instead of work with the sql autonumeric id?
thanks
Maria
January 19, 2005 at 3:09 pm
I've only heard of problems like this in replication, but then again it's real easy to fix with a single commande. So I would just go ahead and push with your current design. I would also ask where and when your partner has seen this... my guess is that it hapenned in access... or access + sql server because I've never heard of this with sql server alone.
(Please don't restart the identity vs natural key debate in here).
January 19, 2005 at 9:33 pm
We use identity fields all the time and have never had a problem with them. It is just a lot easier to let SQL take care of them. We have had issues however, when we didn't use SQL and relied on an applications code to take care of it.
My two cents says to carry on with your plan.
January 20, 2005 at 1:08 am
Let your work partner give you some more details.
SQL Server alone won't do this automatically. However, if someone has fired a DBCC CHECKIDENT to reseed the IDENTITY property, SQL Server *will* start again at the new seed value and increment this. Usually this will result in an error, since you hopefully have declared a PRIMARY KEY or UNIQUE constraint on that column. If not, you're likely you get duplicates. Consider this:
SET NOCOUNT ON
CREATE TABLE MyTable
(
MyID int IDENTITY (1, 1) NOT NULL
, MyNote varchar (12) DEFAULT('ABC')
, DateTimeInserted datetime DEFAULT getdate()
)
GO
INSERT MyTable (MyNote) VALUES ('Note 1')
INSERT MyTable (MyNote) VALUES ('Note 2')
INSERT MyTable (MyNote) VALUES ('Note 3')
SELECT * FROM MyTable ORDER BY DateTimeInserted, MyID
DBCC CHECKIDENT (MyTable,RESEED,0) -- Set the identity column to begin from 1 again
INSERT MyTable (MyNote) VALUES ('Note 4')
INSERT MyTable (MyNote) VALUES ('Note 5')
INSERT MyTable (MyNote) VALUES ('Note 6')
INSERT MyTable (MyNote) VALUES ('Note 7')
SELECT * FROM MyTable ORDER BY DateTimeInserted, MyID
DROP TABLE MyTable
SET NOCOUNT OFF
MyID MyNote DateTimeInserted
----------- ------------ ------------------------------------------------------
1 Note 1 2005-01-20 08:58:28.830
2 Note 2 2005-01-20 08:58:28.830
3 Note 3 2005-01-20 08:58:28.830
Checking identity information: current identity value '3', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
MyID MyNote DateTimeInserted
----------- ------------ ------------------------------------------------------
1 Note 1 2005-01-20 08:58:28.830
1 Note 4 2005-01-20 08:58:28.830
2 Note 2 2005-01-20 08:58:28.830
2 Note 5 2005-01-20 08:58:28.830
3 Note 6 2005-01-20 08:58:28.830
3 Note 3 2005-01-20 08:58:28.830
4 Note 7 2005-01-20 08:58:28.830
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply