database collation: seed

Showing posts with label seed. Show all posts

Sunday, March 25, 2012

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Sunday, February 19, 2012

Changing identity seed and increment after the fact

Hi all,

I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ...). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.

My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?

Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?

Thanks!You can use DBCC Checkident to reseed the seeding of the identity column.|||Thanks,

I'm Looking at the help files on DBCC checkident now. It doesn't look like it will convert any existing records in the table. If there are 100 records in the table with identity values 1 through 100 and I change the seed/increment valuse to 10000/+1, I'm asuming that the existing 100 records will maintain their current ident values?|||Yes, the value will be the same even you execute the DBCC. You can save the table to a temp, delete all rows out of the existing table, change the identity seeding, and then reload the data. That will do it.|||I think that will work. Thanks again for the help!

Changing Identity Seed

Hi All,
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the following
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... do
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:
> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> > Hi All,
> >
> > I am using SQL Server 2000 and I am trying to change the identity seed in
> > a
> > table. I want to change this via a script. I have used the following
> > command:
> >
> > DBCC CHECKIDENT (dbo, RESEED, value)
> > where
> > dbo = the table in the database whose seed I want to change
> > value = the value I would like to change the value in Identity seed too.
> >
> > I get the following result:
> > "Checking identity information: current identity value '100017140',
> > current
> > column value '100017148'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator."
> >
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> >
> > Can anyone help. I am looking to have the value to be changed in the
> > design
> > view for Identity seed.
> >
> > Thanks.
> > A
> >
> > Thanks
> > Aaaaa
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
--
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Then when I go to the table --> right click --> desigh --> the identity
> > seed
> > has not changed to what the message above has said it has changed too.
> >
> > I have tried refreshing the table but still no luck.
> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:
>I inserted a new table, shut down the application and opened it up. When it
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
--
Thanks
Aaaaa
"Roy Harvey" wrote:
> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
> >I inserted a new table, shut down the application and opened it up. When it
> >opened the table had been updated (test --> right click --> open table -->
> >return all rows)
> >But then when I check design --> column --> identity seed the value has not
> >changed.
> >
> >This is where the problem lies.
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
2005 and even in Katmai. There doesn't seem to be a way to make the table
designer reflect numerous changes to the identity seed value...
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(The question, of course, is where on earth does SQL Server store the 1,1
from initial creation? They must be coming from somewhere. Yes, I'm too
lazy this morning to fire up profiler.)
--
Aaron Bertrand
SQL Server MVP|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Be sure to right-click on the table in the tree and choose REFRESH.
> No, this really is a bug in the table designer. I can reproduce it in 2000,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Be sure to right-click on the table in the tree and choose REFRESH.
>> No, this really is a bug in the table designer. I can reproduce it in
>> 2000,
>> 2005 and even in Katmai. There doesn't seem to be a way to make the
>> table
>> designer reflect numerous changes to the identity seed value...
>> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
>> (The question, of course, is where on earth does SQL Server store the 1,1
>> from initial creation? They must be coming from somewhere. Yes, I'm too
>> lazy this morning to fire up profiler.)
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id => col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_value
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
> > Thanks Aaron
> >
> > I have tried to run a trace via SQL Profiler though I am having some
> > issues
> > with the parameters returning more indepth information on where (1,1) is
> > stored. Could you advise on what I could include with this for more
> > information?
> >
> > Morning by the way (evening for me)
> >
> > Thanks
> > Ads
> > Aaaaa
> > --
> > Thanks
> > Aaaaa
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Be sure to right-click on the table in the tree and choose REFRESH.
> >>
> >> No, this really is a bug in the table designer. I can reproduce it in
> >> 2000,
> >> 2005 and even in Katmai. There doesn't seem to be a way to make the
> >> table
> >> designer reflect numerous changes to the identity seed value...
> >>
> >> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> >>
> >> (The question, of course, is where on earth does SQL Server store the 1,1
> >> from initial creation? They must be coming from somewhere. Yes, I'm too
> >> lazy this morning to fire up profiler.)
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >>
> >>
> >>
> >>
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
--
Aaron Bertrand
SQL Server MVP|||Hi
I tried turning on profiler and then checking the table in design view.
Unfortunately was not able to find any relating table to find where the
identity seed in design view is entered. Any other ideas?
Thank you so much for all your help.
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:
> > That was very well shrunk BUT i still failed miserably and this code did
> > not
> > work. Too many errors,
> I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
> have to turn Profiler on, including TSQL:StmtCompleted, and then open the
> table in design view. There will be a bunch of statements there, and one of
> them will include something about identity / seed.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||Does it really matter where it's stored? It's WRONG! I'd fire up profiler
and look at the same queries for you, but I don't think it's really worth
it. They're not likely to fix this for SQL Server 2000 anyway. But there
is a chance they will fix it for 2005, and an even better chance they will
fix it in 2008.
--
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:83E7C60B-C39B-4B64-B7A6-60AA1DD09DB2@.microsoft.com...
> Hi
> I tried turning on profiler and then checking the table in design view.
> Unfortunately was not able to find any relating table to find where the
> identity seed in design view is entered. Any other ideas?
> Thank you so much for all your help.

Sunday, March 25, 2012

Changing the identity seed & increment programatically

Changing the identity seed & increment programatically

Sunday, February 19, 2012

Changing identity seed and increment after the fact

Changing Identity Seed

Changing Identity Seed

Changing Identity Seed

database collation

Blog Archive

About Me