Hi,
how we can declare an Identity Field for MS SQL Server 2005 datatabse.
thanks
Arnaud.
Hello Arnaud,
Can you tell me the exact datatype you meaning?
Best regards,
Rain Wong
Hi,
We want to have a Primary key (int) as Identity under SQL Server 2005 when we generate the database script.
Thanks
Arnaud.
Hello Arnaud,
It seems there is no “Identity” type in SQL Server 2005 (the closest is uniqueidentifier). By specifying the column is primary key with type int and select “native” as ID Generator, it will then become int column with auto increment.
Best regards,
Rain
Well,
actually it doesn’t work.
When we add this options:
type : int
ID generator : native
set as primary key
the script generated doesn’t add the identity (auto-generated like an Oracle Sequence) “property” with good parameters to the primary key
the script generated:
create table fwk_shipping_edd (EddID int identity not null, StartDate datetime not null, ShippingCodeDelay int null unique, DeliveryMode char(3) null unique, ProviderCode nvarchar(20) null unique, PreOrderDate datetime null, ZoneID int not null, MinShippingExpectedDate datetime null, MaxShippingExpectedDate datetime null, MinDeliveryExpectedDate datetime null, MaxDeliveryExpectedDate datetime null, primary key (EddID), constraint unq_startdate_shippingcodedelay_deliverymode_providercode_preorderdate_zoneid_constraint unique (StartDate, ZoneID, ShippingCodeDelay, DeliveryMode, ProviderCode, PreOrderDate));
the script we are expecting:
create table fwk_shipping_edd (EddID int identity (1,1) not null, StartDate datetime not null, ShippingCodeDelay int null unique, DeliveryMode char(3) null unique, ProviderCode nvarchar(20) null unique, PreOrderDate datetime null, ZoneID int not null, MinShippingExpectedDate datetime null, MaxShippingExpectedDate datetime null, MinDeliveryExpectedDate datetime null, MaxDeliveryExpectedDate datetime null, primary key (EddID), constraint unq_startdate_shippingcodedelay_deliverymode_providercode_preorderdate_zoneid_constraint unique (StartDate, ZoneID, ShippingCodeDelay, DeliveryMode, ProviderCode, PreOrderDate));
Both of your scripts having “EddID int identity”, do you mean the identity (1,1)?
Yes,
otherwise the ID is not autogenerated in SQL Server.
Best regards,
Arnaud.
But actually “EddID int identity not null” and “EddID int identity (1,1) not null” are just the same (even you not specify (1,1) identity default is starting with 1 and increment by 1 at a time), so what is the problem?
Well,
we have tested it. It works fine on SQL Server 2000 but fails on SQL Server 2005.
Best regards
Arnaud.
I’ve created a simple example to test the identity and identity (1,1) and both are working fine on SQL Server 2005 (insert data with our generated ORM code). Would you mind tell me more details on how it not working? Can you send me the exact error message and the code you used to insert data?
With SQLite V 3.5.6, the table creation snytax is:CREATE TABLE “stories” (“id” int(11) NOT NULL,“name” varchar(255) default NULL,“link” varchar(255) default NULL,“created_at” datetime DEFAULT NULL,“updated_at” datetime DEFAULT NULL,PRIMARY KEY (“id”));The PRIMARY KEY (“id”) automatically makes it autoincrement
it’s very impotent to be aware of the resreved words, I built a cms that has a filed in each table called (CONDITION) and now its resreved in mysql 5… it took me a lot of time to alter all the tables and the php code in all the sites that uses this cms Thanks Steve
Thanks for the post.a0 It helped confrim a problem we are having.a0 We have some old classic ASP code that is using ADO.a0 The programmer who wrote the code was using SCOPE_IDENTITY to get the IDENTITY used on a previous insert.a0 He was basically issuing a dynamic SQL statement and putting the result back into a ADO recordset.a0 This method worked for years on SQL 2000.a0 We just recently migrated this app to SQL 2005 and this broke.a0 The problem was the recordset was now getting a NULL value.a0 It is our thinking that the change in SCOPE_IDENTITY in 2005 doesn’t mashup with old ADO that well.a0 So, rather than doinga0rsSave.Open "SELECT SCOPE_IDENTITY() AS ‘EntryID’ ",cnTime,adOpenStatic,adLockReadOnlya0We are now doinga0rsSave.Open "DECLARE @RecID int; SET @RecID = SCOPE_IDENTITY(); SELECT @RecID AS ‘EntryID’ ",cnTime,adOpenStatic,adLockReadOnlya0This is now getting the recordset populated with the IDENTITY.a0 Interestingly enough, if we did a CAST to INT on the original statement, that did not help.a0a0
If you set an autoincrement of 1 but do not set a seed, the generated code says identity(1), which is illegal. Adding a seed of 1 gives me identity(1,1) which works. You may wish to provide defaults to keep the code from failing.
