Dear all,
The last value I see for a identity field is 174. That's fine.
But the next value after insert which appears is 217 instead of 175. How do
I force the sequence 'natural' again'
I suppose that it happen due to I deleted some rows...
I would need in order to add a new row into a another table.
Thanks in advance,
EnricEnric
SET IDENTITY_INSERT
Be aware that an IDENTITY property may have gaps as well , and if it is
important , you can change to the natural key and add value to maximal key
SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How
> do
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||Thanks for your post, anyway I will not know which will be the next value in
case I delete some rows.
"Uri Dimant" wrote:
> Enric
> SET IDENTITY_INSERT
> Be aware that an IDENTITY property may have gaps as well , and if it is
> important , you can change to the natural key and add value to maximal k
ey
> SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
>
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
>
>|||Try this ...
DBCC CHECKIDENT(tablename, RESEED, 0)
DBCC CHECKIDENT(tablename, RESEED)
"Enric" wrote:
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How d
o
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||"Enric" wrote:
> Thanks for your post, anyway I will not know which will be the next value
in
> case I delete some rows.
> "Uri Dimant" wrote:
If you want a gapless sequence then IDENTITY is the wrong solution. Don't
use IDENTITY in a way that has meaning for your users precisely because you
can't always control the IDENTITY value. IDENTITY is intended to be used as
an artificial surrogate key only.
Why do you need an IDENTITY column and why do you care if the sequence has
gaps?
David Portas
SQL Server MVP
--
Showing posts with label instead. Show all posts
Showing posts with label instead. Show all posts
Monday, March 19, 2012
Identity Seed : Implications of making it zero
Hi,
The default for the seed is 1. HAs anyone tried creating a
seed starting at 0 instead. What are the problems/comments
on this?
Any feedback is appreciated.
Thanks
AnnaImplication will be that you'll get one extra possible value :)
Seriously, no problem at all. You can use negative numbers if you want.
Everyone just pretty much uses 1 due to convention.
"Anna" <anonymous@.discussions.microsoft.com> wrote in message
news:0c0601c49a7c$4d843a30$a601280a@.phx.gbl...
> Hi,
> The default for the seed is 1. HAs anyone tried creating a
> seed starting at 0 instead. What are the problems/comments
> on this?
> Any feedback is appreciated.
> Thanks
> Anna|||No problems, your new identity values will start from 0 instead of 1.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Anna" <anonymous@.discussions.microsoft.com> wrote in message
news:0c0601c49a7c$4d843a30$a601280a@.phx.gbl...
> Hi,
> The default for the seed is 1. HAs anyone tried creating a
> seed starting at 0 instead. What are the problems/comments
> on this?
> Any feedback is appreciated.
> Thanks
> Anna|||> Everyone just pretty much uses 1 due to convention.
And then get an overflow when the value reaches maxint. ;-)
Seriously, Anna, it even makes more sense to start with the lowest int in
potentially big tables.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ulUM8znmEHA.3396@.tk2msftngp13.phx.gbl...
> Implication will be that you'll get one extra possible value :)
> Seriously, no problem at all. You can use negative numbers if you want.
> Everyone just pretty much uses 1 due to convention.
>
> "Anna" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c0601c49a7c$4d843a30$a601280a@.phx.gbl...
>> Hi,
>> The default for the seed is 1. HAs anyone tried creating a
>> seed starting at 0 instead. What are the problems/comments
>> on this?
>> Any feedback is appreciated.
>> Thanks
>> Anna
>|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uI0F4GomEHA.3356@.TK2MSFTNGP14.phx.gbl...
> And then get an overflow when the value reaches maxint. ;-)
>
The proactive DBA would have re-seeded before hitting that!
The default for the seed is 1. HAs anyone tried creating a
seed starting at 0 instead. What are the problems/comments
on this?
Any feedback is appreciated.
Thanks
AnnaImplication will be that you'll get one extra possible value :)
Seriously, no problem at all. You can use negative numbers if you want.
Everyone just pretty much uses 1 due to convention.
"Anna" <anonymous@.discussions.microsoft.com> wrote in message
news:0c0601c49a7c$4d843a30$a601280a@.phx.gbl...
> Hi,
> The default for the seed is 1. HAs anyone tried creating a
> seed starting at 0 instead. What are the problems/comments
> on this?
> Any feedback is appreciated.
> Thanks
> Anna|||No problems, your new identity values will start from 0 instead of 1.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Anna" <anonymous@.discussions.microsoft.com> wrote in message
news:0c0601c49a7c$4d843a30$a601280a@.phx.gbl...
> Hi,
> The default for the seed is 1. HAs anyone tried creating a
> seed starting at 0 instead. What are the problems/comments
> on this?
> Any feedback is appreciated.
> Thanks
> Anna|||> Everyone just pretty much uses 1 due to convention.
And then get an overflow when the value reaches maxint. ;-)
Seriously, Anna, it even makes more sense to start with the lowest int in
potentially big tables.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ulUM8znmEHA.3396@.tk2msftngp13.phx.gbl...
> Implication will be that you'll get one extra possible value :)
> Seriously, no problem at all. You can use negative numbers if you want.
> Everyone just pretty much uses 1 due to convention.
>
> "Anna" <anonymous@.discussions.microsoft.com> wrote in message
> news:0c0601c49a7c$4d843a30$a601280a@.phx.gbl...
>> Hi,
>> The default for the seed is 1. HAs anyone tried creating a
>> seed starting at 0 instead. What are the problems/comments
>> on this?
>> Any feedback is appreciated.
>> Thanks
>> Anna
>|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uI0F4GomEHA.3356@.TK2MSFTNGP14.phx.gbl...
> And then get an overflow when the value reaches maxint. ;-)
>
The proactive DBA would have re-seeded before hitting that!
Identity Seed
Can you run an alter table script to change the seed on a table or do you
have to redefine the table DDL?
I want the seed to be 0 instead of 1Check for the DBCC CHECKIDENT in BOL and see if it helps.
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E7B08050-BD84-4AD1-8E4C-D3C6192C39B7@.microsoft.com...
> Can you run an alter table script to change the seed on a table or do you
> have to redefine the table DDL?
> I want the seed to be 0 instead of 1|||thanks MC but I want to cheange the seed not the identity
Can I update the INFORMATION_SCHEMA.TABLES Seed value'|||Itried this but that aint gunna work.
Any other ideas?
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
and TABLE_NAME in ('Pot_lu_rd_county')
update INFORMATION_SCHEMA.TABLES
set IDENT_SEED(TABLE_NAME) = 0
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
('Pot_lu_rd_county')|||You can change the seed value by using DBCC CHECKIDENT (table, reseed, new
seed value) options. Perhaps I misunderstood something?
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8D21B11D-46D9-46B0-B397-843015B31F86@.microsoft.com...
> Itried this but that aint gunna work.
> Any other ideas?
> SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
> and TABLE_NAME in ('Pot_lu_rd_county')
> update INFORMATION_SCHEMA.TABLES
> set IDENT_SEED(TABLE_NAME) = 0
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
> ('Pot_lu_rd_county')|||marcmc wrote on Thu, 13 Apr 2006 04:52:01 -0700:
> thanks MC but I want to cheange the seed not the identity
DBCC CHECKINDENT is for changing the seed. Read the description again. The
seed is the value to be used for the first row if the table is empty, or the
next value to use -1 if the table has rows (ie. if you set the seed to 10
and the table has a row in it already, the next new row will be given the
value 11 for the identity)
Or do you mean you want to change the increment?
Dan|||Hi,
I copied the contents of the table to a temp table and ran
DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
sp_help still says the seed is 1
and populated the table again. The first id is still 1.
I want the tables first id to be 0|||There doesn't seem to be a way apart from dropping the table & redefining th
e
ddl with the following'
IDENTITY (0, 1)|||Did you try setting the seed to -1
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6BDD2FF5-0619-4EE4-86AB-BB8C3F80A4E6@.microsoft.com...
> Hi,
> I copied the contents of the table to a temp table and ran
> DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
> sp_help still says the seed is 1
> and populated the table again. The first id is still 1.
> I want the tables first id to be 0
have to redefine the table DDL?
I want the seed to be 0 instead of 1Check for the DBCC CHECKIDENT in BOL and see if it helps.
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E7B08050-BD84-4AD1-8E4C-D3C6192C39B7@.microsoft.com...
> Can you run an alter table script to change the seed on a table or do you
> have to redefine the table DDL?
> I want the seed to be 0 instead of 1|||thanks MC but I want to cheange the seed not the identity
Can I update the INFORMATION_SCHEMA.TABLES Seed value'|||Itried this but that aint gunna work.
Any other ideas?
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
and TABLE_NAME in ('Pot_lu_rd_county')
update INFORMATION_SCHEMA.TABLES
set IDENT_SEED(TABLE_NAME) = 0
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
('Pot_lu_rd_county')|||You can change the seed value by using DBCC CHECKIDENT (table, reseed, new
seed value) options. Perhaps I misunderstood something?
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8D21B11D-46D9-46B0-B397-843015B31F86@.microsoft.com...
> Itried this but that aint gunna work.
> Any other ideas?
> SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
> and TABLE_NAME in ('Pot_lu_rd_county')
> update INFORMATION_SCHEMA.TABLES
> set IDENT_SEED(TABLE_NAME) = 0
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
> ('Pot_lu_rd_county')|||marcmc wrote on Thu, 13 Apr 2006 04:52:01 -0700:
> thanks MC but I want to cheange the seed not the identity
DBCC CHECKINDENT is for changing the seed. Read the description again. The
seed is the value to be used for the first row if the table is empty, or the
next value to use -1 if the table has rows (ie. if you set the seed to 10
and the table has a row in it already, the next new row will be given the
value 11 for the identity)
Or do you mean you want to change the increment?
Dan|||Hi,
I copied the contents of the table to a temp table and ran
DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
sp_help still says the seed is 1
and populated the table again. The first id is still 1.
I want the tables first id to be 0|||There doesn't seem to be a way apart from dropping the table & redefining th
e
ddl with the following'
IDENTITY (0, 1)|||Did you try setting the seed to -1
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6BDD2FF5-0619-4EE4-86AB-BB8C3F80A4E6@.microsoft.com...
> Hi,
> I copied the contents of the table to a temp table and ran
> DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
> sp_help still says the seed is 1
> and populated the table again. The first id is still 1.
> I want the tables first id to be 0
identity seed
Hello,
How can i set a identity column to start with 01 instead of 1?
Thnx
Identity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>
|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
>
|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n>) + YourCol, <n>) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.
How can i set a identity column to start with 01 instead of 1?
Thnx
Identity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>
|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
>
|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n>) + YourCol, <n>) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.
identity seed
Hello,
How can i set a identity column to start with 01 instead of 1?
ThnxIdentity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n> ) + YourCol, <n> ) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.
How can i set a identity column to start with 01 instead of 1?
ThnxIdentity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n> ) + YourCol, <n> ) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.
identity seed
Hello,
How can i set a identity column to start with 01 instead of 1?
ThnxIdentity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > How can i set a identity column to start with 01 instead of 1?
> >
> > Thnx
> >
> >
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n>) + YourCol, <n>) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.
How can i set a identity column to start with 01 instead of 1?
ThnxIdentity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > How can i set a identity column to start with 01 instead of 1?
> >
> > Thnx
> >
> >
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n>) + YourCol, <n>) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.
Wednesday, March 7, 2012
Identity columns and date columns on transactional replication
Hi,
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Any help would be appreciated.
Thanks,
-Niraj
By default, the data should be replicated as it is, regardless the default values. You can look at the article properties to see your options, you also have option to not replicate identity value if you don't want to.
Subscribe to:
Posts (Atom)