|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How start identity field at 100I have a table with the PK field defined as an identity field with the
increment of 1. How do I START the calculations at 100, rather than 1? When the table is first created, use an IdentitySeed value of 100.
-- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Cirene" <t***@test.com> wrote in message news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >I have a table with the PK field defined as an identity field with the >increment of 1. How do I START the calculations at 100, rather than 1? > What if the rows already exist? There are 3 rows that i don't want to
recreate, but it won't let me manually change the PK field. Any ideas? Thanks! Show quote "William (Bill) Vaughn" <billvaRemoveT***@betav.com> wrote in message news:2DBCD4E7-75BD-4BAD-930B-574B3BD46A3F@microsoft.com... > When the table is first created, use an IdentitySeed value of 100. > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > "Cirene" <t***@test.com> wrote in message > news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>I have a table with the PK field defined as an identity field with the >>increment of 1. How do I START the calculations at 100, rather than 1? >> > You can try to remove temporaly the autoident setting of the key.
Cor Setting the Identity column properties like Seed and AutoIncrement should
only be done on an empty table--not when tables already exist. It's rarely a good idea to change the PK anyway. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Cirene" <t***@test.com> wrote in message news:OlvnQTJIIHA.4476@TK2MSFTNGP06.phx.gbl... > What if the rows already exist? There are 3 rows that i don't want to > recreate, but it won't let me manually change the PK field. Any ideas? > Thanks! > > "William (Bill) Vaughn" <billvaRemoveT***@betav.com> wrote in message > news:2DBCD4E7-75BD-4BAD-930B-574B3BD46A3F@microsoft.com... >> When the table is first created, use an IdentitySeed value of 100. >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> Visit www.hitchhikerguides.net to get more information on my latest book: >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >> ----------------------------------------------------------------------------------------------------------------------- >> "Cirene" <t***@test.com> wrote in message >> news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>>I have a table with the PK field defined as an identity field with the >>>increment of 1. How do I START the calculations at 100, rather than 1? >>> >> > > Bill,
> Setting the Identity column properties like Seed and AutoIncrement should I did it yesterday with a corrupted datatable from an SQL server 2000, which > only be done on an empty table--not when tables already exist. It's rarely > a good idea to change the PK anyway. was corrupt because that there was no primary key and was handled using SQL Management Studio (it was not me who did it, so I am not sure what exactly was the reason). I could only do it with the SQL Enterprise tools. However it did help me a lot. Therefore never say never. :-) CorShow quote "William Vaughn" <billvaNoSPAM@betav.com> schreef in bericht news:elJU7ZjIIHA.4592@TK2MSFTNGP02.phx.gbl... > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant, Dad, Grandpa > Microsoft MVP > INETA Speaker > www.betav.com > www.betav.com/blog/billva > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > > "Cirene" <t***@test.com> wrote in message > news:OlvnQTJIIHA.4476@TK2MSFTNGP06.phx.gbl... >> What if the rows already exist? There are 3 rows that i don't want to >> recreate, but it won't let me manually change the PK field. Any ideas? >> Thanks! >> >> "William (Bill) Vaughn" <billvaRemoveT***@betav.com> wrote in message >> news:2DBCD4E7-75BD-4BAD-930B-574B3BD46A3F@microsoft.com... >>> When the table is first created, use an IdentitySeed value of 100. >>> >>> -- >>> ____________________________________ >>> William (Bill) Vaughn >>> Author, Mentor, Consultant >>> Microsoft MVP >>> INETA Speaker >>> www.betav.com/blog/billva >>> www.betav.com >>> Please reply only to the newsgroup so that others can benefit. >>> This posting is provided "AS IS" with no warranties, and confers no >>> rights. >>> __________________________________ >>> Visit www.hitchhikerguides.net to get more information on my latest >>> book: >>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >>> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >>> ----------------------------------------------------------------------------------------------------------------------- >>> "Cirene" <t***@test.com> wrote in message >>> news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>>>I have a table with the PK field defined as an identity field with the >>>>increment of 1. How do I START the calculations at 100, rather than 1? >>>> >>> >> >> > Set the seed to 100. If you are using the SQL Manager, you can alter there,
but be careful with live databases using this method. With scripting, the method is: CREATE TABLE TableWithIdentityColumn ( MyID int IDENTITY(100,1) PRIMARY KEY , SomeData varchar(2000) NOT NULL ) -- Show quoteGregory A. Beamer MVP, MCP: +I, SE, SD, DBA ************************************************* | Think outside the box! | ************************************************* "Cirene" <t***@test.com> wrote in message news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >I have a table with the PK field defined as an identity field with the >increment of 1. How do I START the calculations at 100, rather than 1? > If it is Identity column, why do you care what its value is?
Show quote "Cirene" <t***@test.com> wrote in message news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >I have a table with the PK field defined as an identity field with the >increment of 1. How do I START the calculations at 100, rather than 1? > i display it to the user as the id for the home listing
Show quote "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message news:uyJTJSJIIHA.868@TK2MSFTNGP05.phx.gbl... > If it is Identity column, why do you care what its value is? > > > "Cirene" <t***@test.com> wrote in message > news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>I have a table with the PK field defined as an identity field with the >>increment of 1. How do I START the calculations at 100, rather than 1? >> > The only purpose for Identity is to give a unique number. Even you can
managed to make it starts from 100, is it still OK if next number is 103, not 101 (if 2 records have been added mistakenly and then deleted)? If it is OK, then I do not see why you have to waste time to get the SQL Server to start an ID from 100 Or any other arbitatry number). If it is not OK, then you should not use Identity column at all. Show quote "Cirene" <t***@test.com> wrote in message news:esV6EfJIIHA.4196@TK2MSFTNGP04.phx.gbl... >i display it to the user as the id for the home listing > > "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message > news:uyJTJSJIIHA.868@TK2MSFTNGP05.phx.gbl... >> If it is Identity column, why do you care what its value is? >> >> >> "Cirene" <t***@test.com> wrote in message >> news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>>I have a table with the PK field defined as an identity field with the >>>increment of 1. How do I START the calculations at 100, rather than 1? >>> >> > > It is ok as long as it starts at 100 and above. I guess I need to tweak the
design. Thanks! Show quote "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message news:eeNm2tMIIHA.4880@TK2MSFTNGP03.phx.gbl... > The only purpose for Identity is to give a unique number. Even you can > managed to make it starts from 100, is it still OK if next number is 103, > not 101 (if 2 records have been added mistakenly and then deleted)? If it > is OK, then I do not see why you have to waste time to get the SQL Server > to start an ID from 100 Or any other arbitatry number). If it is not OK, > then you should not use Identity column at all. > > > "Cirene" <t***@test.com> wrote in message > news:esV6EfJIIHA.4196@TK2MSFTNGP04.phx.gbl... >>i display it to the user as the id for the home listing >> >> "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message >> news:uyJTJSJIIHA.868@TK2MSFTNGP05.phx.gbl... >>> If it is Identity column, why do you care what its value is? >>> >>> >>> "Cirene" <t***@test.com> wrote in message >>> news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>>>I have a table with the PK field defined as an identity field with the >>>>increment of 1. How do I START the calculations at 100, rather than 1? >>>> >>> >> >> > Don't worry about what Cirene is saying. It's OK to display an identity
column to your users. Some people choose not to do so, but it's more about design differences than anything else. I don't agree with pushing this functionality up to the app level merely to "not waste the DB's time". Odds are you are going to use an identity column to link tables anyway. You may as well use it for an ID in the app rather than waste your time building more code. Show quote "Cirene" <t***@test.com> wrote in message news:eY8$c5MIIHA.6068@TK2MSFTNGP05.phx.gbl... > It is ok as long as it starts at 100 and above. I guess I need to tweak > the design. Thanks! > > "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message > news:eeNm2tMIIHA.4880@TK2MSFTNGP03.phx.gbl... >> The only purpose for Identity is to give a unique number. Even you can >> managed to make it starts from 100, is it still OK if next number is >> 103, not 101 (if 2 records have been added mistakenly and then deleted)? >> If it is OK, then I do not see why you have to waste time to get the SQL >> Server to start an ID from 100 Or any other arbitatry number). If it is >> not OK, then you should not use Identity column at all. >> >> >> "Cirene" <t***@test.com> wrote in message >> news:esV6EfJIIHA.4196@TK2MSFTNGP04.phx.gbl... >>>i display it to the user as the id for the home listing >>> >>> "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message >>> news:uyJTJSJIIHA.868@TK2MSFTNGP05.phx.gbl... >>>> If it is Identity column, why do you care what its value is? >>>> >>>> >>>> "Cirene" <t***@test.com> wrote in message >>>> news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>>>>I have a table with the PK field defined as an identity field with the >>>>>increment of 1. How do I START the calculations at 100, rather than >>>>>1? >>>>> >>>> >>> >>> >> > > Thanks!
Show quote "Andrew Faust" <and***@andrewfaust.com> wrote in message news:A63D2FAE-21EF-4704-90B3-554F3309B9C6@microsoft.com... > Don't worry about what Cirene is saying. It's OK to display an identity > column to your users. Some people choose not to do so, but it's more about > design differences than anything else. I don't agree with pushing this > functionality up to the app level merely to "not waste the DB's time". > Odds are you are going to use an identity column to link tables anyway. > You may as well use it for an ID in the app rather than waste your time > building more code. > > -- > Andrew Faust > andrew[at]andrewfaust.com > http://www.andrewfaust.com > > > "Cirene" <t***@test.com> wrote in message > news:eY8$c5MIIHA.6068@TK2MSFTNGP05.phx.gbl... >> It is ok as long as it starts at 100 and above. I guess I need to tweak >> the design. Thanks! >> >> "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message >> news:eeNm2tMIIHA.4880@TK2MSFTNGP03.phx.gbl... >>> The only purpose for Identity is to give a unique number. Even you can >>> managed to make it starts from 100, is it still OK if next number is >>> 103, not 101 (if 2 records have been added mistakenly and then deleted)? >>> If it is OK, then I do not see why you have to waste time to get the SQL >>> Server to start an ID from 100 Or any other arbitatry number). If it is >>> not OK, then you should not use Identity column at all. >>> >>> >>> "Cirene" <t***@test.com> wrote in message >>> news:esV6EfJIIHA.4196@TK2MSFTNGP04.phx.gbl... >>>>i display it to the user as the id for the home listing >>>> >>>> "Norman Yuan" <NoAddr***@NoEmail.fake> wrote in message >>>> news:uyJTJSJIIHA.868@TK2MSFTNGP05.phx.gbl... >>>>> If it is Identity column, why do you care what its value is? >>>>> >>>>> >>>>> "Cirene" <t***@test.com> wrote in message >>>>> news:e3mKixIIIHA.5328@TK2MSFTNGP05.phx.gbl... >>>>>>I have a table with the PK field defined as an identity field with the >>>>>>increment of 1. How do I START the calculations at 100, rather than >>>>>>1? >>>>>> >>>>> >>>> >>>> >>> >> >> >  
Other interesting topics
|
|||||||||||||||||||||||
