Scope parameter: increment

Showing posts with label increment. Show all posts

Wednesday, March 28, 2012

Script to reset the "Identity Seed" and "Identity Increment" values

hi,

Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.

I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.

The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?

Any help whould be greatly appreciated.

Thanks,
Prashanth ReddyOriginally posted by Prashanth
hi,

Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.

I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.

The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?

Any help whould be greatly appreciated.

Thanks,
Prashanth Reddy

You have 2 options that spring to mind:

1. You can use TRUNCATE TABLE to accomplish this, but you will lose all data and as its a non-logged operation nothing will be written to the transaction log. I should add that if the table is referenced by a foriegn key constraint then you will not be able to use this method.

Usage: TRUNCATE TABLE tablename

2. The other option is to use DBCC CHECKIDENT to reset the seed:

Usage: DBCC CHECKIDENT (tablename, RESEED, 0)

macka.|||thanks for the response.|||thanks for the response.

I have patient table with PatientID as auto increment field
with
"Identity Increment" as 1 and
"Identity seed" as 1
I can chane Identity Seed using
DBCC checkident (patient,reseed,4)
How do i change the "Identity Increment" value to 10?

Prashanth|||The following script should work. In this example it resets the seed to 4 and increment to 10.

If the table is referenced by foreign key constraints then you will need to drop them before this script is run and add them back after it completes.

ALTER TABLE Demo
DROP CONSTRAINT PK_Demo

ALTER TABLE Demo
DROP COLUMN DemoID

ALTER TABLE Demo
ADD DemoID INT IDENTITY(4,10)

ALTER TABLE Demo ADD CONSTRAINT
PK_Demo PRIMARY KEY CLUSTERED
(
DemoID
) ON [PRIMARY]

macka.

Friday, March 23, 2012

Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
>> Because I have to do this more then once for several Tables, I do not
>> want to do this manually but through a Script.
>> The thing is: do not have a clue how to do it.
>> Cheers
>> Marcel
>>
>> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
>> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>>
>

Script to alter Identity, Identity Seed, Identity Increment

Tuesday, February 21, 2012

Scope_Identity vs manually increment + 1 clarification needed

Hi
I was wondering if somebody could help clarifying the issue of

Scope_Identity vs manually increment + 1 .

Where I work since the days of sql server 7 we use our own way to get the next Identifier.
By having a table with (name and value column) EG CustomerID 234 and when we do an insert we update this Table by incrementing + 1 EG CustomerID 235.

I beleive that this way of doing things is not good and should be replaced by scope_Identity.

The reason I was given was to avoid Locking Tables whilst inserts executes whether in transactional operation or not.

I totally disagree with the above but cannot demostrate it.

My understanding of Scope_Identity is that is the most secure way to get the next Identifier by getting things
that are only in scope (EG A stored Procedure,statements etcc)

Now my question is:
Does scope_Indentity performs any locks of some sort ,or slows the process down?

Could somebody give me a example why incrementing by 1 manually is inefficient or what problem it can cause.

Thanks a lot in advance

No there is no lock for IDENTITY columns.

There are lot of points there to say manyally +1 is inefficeint.

1. You have to lock the record - identity never use any lock

2. You have to fetch the last value from your table - identity uses the LOG no query

3. You have to manullay increment the value - idenity does the same

4. You have to wirte the code manually on triggers - identity is internal opertion, you need not to write any code

5. Fetching the last inserted value , again a overhead to write a few line of code - idenity values can be fetched easily

Last but not least Idenity never cause any performance issue, but it may cause GAPs on your sequential value.

Want to know more see this article.. http://blogs.msdn.com/sqlprogrammability/archive/2006/04/04/567724.aspx

|||

Fantastic reply.Thanks!!!. If there are anymore points please add it here. I will wait a bit in case somebody else adds something else and then pack in a email and send it to my collegues who were not conviced.

Thanks again for such a clean answer

SCOPE_IDENITY() - Incorrect Value (Returned)

There are two tables

Table_1:

ProductID (Identity Increment)

ProductDescription (nvarchar)

Table_2:

ProductID (int)

I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

DECLARE@.getTheNewProductIDintINSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;SET@.getTheNewProductID =SELECT SCOPE_IDENTITY()
INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2

The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.

How do you do this?

I did try using @.@.identity which may have stuffed things up??

Thanks for your help Smile

Try with this code.

DECLARE@.getTheNewProductIDint

INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;

SET@.getTheNewProductID = (SELECTProductID from Table_1 where ProductID= SCOPE_IDENTITY())

INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)

Wednesday, March 28, 2012

Script to reset the "Identity Seed" and "Identity Increment" values

Friday, March 23, 2012

Script to alter Identity, Identity Seed, Identity Increment

Script to alter Identity, Identity Seed, Identity Increment

Tuesday, February 21, 2012

Scope_Identity vs manually increment + 1 clarification needed

SCOPE_IDENITY() - Incorrect Value (Returned)

Scope parameter

Blog Archive

About Me