On some tables when I execute the following statement the identity value will start at 1 other times 0. Has anyone experienced this inconsistency before?
DBCC CHECKIDENT ('TABLE_NAME', RESEED, 0)Is the miracle effect?
Can you post some code where this happens?
Please make sure to post the DDL, any DML and sample data.
Thanks|||Here ya go. Most of the tables are very simplistic. I start by deleting all of the records then executing the DBCC CHECKIDENT ('table_name', RESEED, 0) in query analyzer against the table. Here's the ddl for one of the tables. 90% of the time the identity value starts at 1 the other 10% start at 0.
t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)
/*================================================= =============*/
/* Table: producer_type */
/*================================================= =============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go
alter table dbo.producer_type
add constraint pk_producer_type primary key (producer_type_id)
with
fillfactor= 90
go
/*================================================= =============*/
/* Index: ux_producer_type_01 */
/*================================================= =============*/
create unique index ux_producer_type_01 on dbo.producer_type (
producer_type_name
)
with
fillfactor= 90
go|||I'll take a look, but why don't you specify the seed an increment in the DDL?
I'm sure there's a default...but...
I've always set IDENTITY(1,1) (that is when I have to use it...I try to avoid it)|||Just out of curiosity...how are you building this table?
You can't do what you're doing unless you use dynamic sql...unless you're building from the application layer, and if you are, what's with the GO?
Also out of curiosity, it sounds like whatever process your doing is placing an undue amount of meaning to the identity column.
Why does it need to be reset to 1 (or 0)|||Sorry, these are in no particular order:
First, I'm building the tables with ddl and executing with query builder. I didn't want to post all of the ddl for the user defined data types, fks, etc... The 'GO' is used as a terminator for the SQL. I'm building over 150 tables, pk's, fk's, indexes, etc.. in one script. It helps me if I encounter an error. The script will stop at that point and allow me to correct the problem and continue running.
Second, the default on the identity is (1,1)
Third, we are placing extra burden on the identity value because we are trying to migrate data into the new system from an existing system and are heavily coding at the same time. The developers need to know the primary key values for reference or metadata for codiing purposes. I know the order of the values going into the tables and as a standard told all of the developers the pk would start at 1. This hurts if it periodically starts at 0 because we are constantly rebuilding the database from the ddl.
Finally, I'm regretting the decision not to turn the identity insert on and specify the values on insert. I could change my scripts but it would take some time to do. I didn't know if there was something simple I was missing with the DBCC CHECKIDENT and RESEED.
Hope this helps, sorry for the long reply.|||Are you not using Query Analyzer?
And if you are, then how does this work?
t_unique_id = int
t_varchar = varchar(50)
t_flag = int with rule limiting values to 1 or 0
t_guid = char(36)
/ *=================================================
=============*/
/* Table: producer_type */
/ *=================================================
=============*/
create table dbo.producer_type (
producer_type_id t_unique_id identity,
producer_type_name t_varchar not null,
org_flag t_flag not null,
description t_long_varchar not null,
comments t_long_varchar not null,
created_by t_unique_id not null,
created_dt datetime not null,
changed_by t_unique_id not null,
changed_dt datetime not null,
logical_unit_of_work_id t_guid not null
)
go
You can't assign the datatype to a variable like that...unless it's a different interface/language...
I'm amazed at a 10% occurance...it's not often that the same process behave differently.
Since you're blowing the data away anyway, can you just do a drop, then recreate the table?
It will save on logging.
Why not create a shell db and recreate it when you need. Then have load scripts...or better just dump a "clean slate" db, and restore over...
and I'm sure NONE of these suggestion will work for you because of either RI, developer data, yada yada yada...
And in lieu of the fact that you're having these problems...why not give it a try and build the table with IDENTITY(1,1).
My Own Opinion (MOO)
Good Luck
