Purpose od IDENTITY column

Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgs
Best shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs
|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>
|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message[vbcol=seagreen]
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
(1,1))[vbcol=seagreen]
increment each row by[vbcol=seagreen]
>1.
unique, sequential[vbcol=seagreen]
>ID.
the 'C' row 3. This ID[vbcol=seagreen]
commonly as a[vbcol=seagreen]
>surrogate
IDENTITY being[vbcol=seagreen]
>consecutive!
and the identity can[vbcol=seagreen]
there won't[vbcol=seagreen]
IDENTITY as a primary key[vbcol=seagreen]
uniqueness of your[vbcol=seagreen]
>data.
common source of[vbcol=seagreen]
>data
in message[vbcol=seagreen]
COLUMN ?
>
>.
>

Purpose od IDENTITY column

Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgs
An identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Purpose od IDENTITY column

Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsBest shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
(1,1))[vbcol=seagreen]
increment each row by[vbcol=seagreen]
>1.
unique, sequential[vbcol=seagreen]
>ID.
the 'C' row 3. This ID[vbcol=seagreen]
commonly as a[vbcol=seagreen]
>surrogate
IDENTITY being[vbcol=seagreen]
>consecutive!
and the identity can[vbcol=seagreen]
there won't[vbcol=seagreen]
IDENTITY as a primary key[vbcol=seagreen]
uniqueness of your[vbcol=seagreen]
>data.
common source of[vbcol=seagreen]
>data
in message[vbcol=seagreen]
COLUMN ?[vbcol=seagreen]
>
>.
>

Purpose od IDENTITY column

Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsAn identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

Purpose od IDENTITY column

Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsHi ,
i think i knowit. it's like that AutoNumber in MS
Access where a row number will be created for each row
rdgs
>--Original Message--
>Hi Ppl,
> Could i know what's the purpose of the IDENTITY
COLUMN ?
>thks ^ rdgs
>.
>|||Best shown through an example:
CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
GO
INSERT #IDTest (AChar) VALUES ('A')
INSERT #IDTest (AChar) VALUES ('B')
INSERT #IDTest (AChar) VALUES ('C')
GO
SELECT *
FROM #IDTest
ORDER BY IDCol
--
We seeded the identity with a start value of 1, and increment each row by 1.
(IDENTITY(1,1)). Every row we insert is now given a unique, sequential ID.
So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
column can be used to track order of INSERTs or more commonly as a surrogate
key for joining to other tables.
Number one thing to remember: Never rely on the IDENTITY being consecutive!
Rows can be deleted, transactions can be rolled back, and the identity can
be re-seeded. So there is certainly no guarantee that there won't
eventually be gaps.
You should also always make sure when using the IDENTITY as a primary key
that you have other constraints in place to ensure uniqueness of your data.
Mis-use of IDENTITY columns for primary keys is a very common source of data
integrity problems. So use them wearily.
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> Hi Ppl,
> Could i know what's the purpose of the IDENTITY COLUMN ?
> thks ^ rdgs|||That's solid advice from Adam.
Just adding my 20c though: If you design the use of identities into a
database / application, you throw away the ability to partition tables in
the database later which might be important if the database grows
substantially. This is due to a limitation of the SQL 2000 partitioning
design which has been fixed in SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Best shown through an example:
> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY(1,1))
> GO
> INSERT #IDTest (AChar) VALUES ('A')
> INSERT #IDTest (AChar) VALUES ('B')
> INSERT #IDTest (AChar) VALUES ('C')
> GO
> SELECT *
> FROM #IDTest
> ORDER BY IDCol
> --
> We seeded the identity with a start value of 1, and increment each row by
1.
> (IDENTITY(1,1)). Every row we insert is now given a unique, sequential
ID.
> So the 'A' row has an ID of 1, the 'B' row 2, and the 'C' row 3. This ID
> column can be used to track order of INSERTs or more commonly as a
surrogate
> key for joining to other tables.
> Number one thing to remember: Never rely on the IDENTITY being
consecutive!
> Rows can be deleted, transactions can be rolled back, and the identity can
> be re-seeded. So there is certainly no guarantee that there won't
> eventually be gaps.
> You should also always make sure when using the IDENTITY as a primary key
> that you have other constraints in place to ensure uniqueness of your
data.
> Mis-use of IDENTITY columns for primary keys is a very common source of
data
> integrity problems. So use them wearily.
>
> "maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
> > Hi Ppl,
> >
> > Could i know what's the purpose of the IDENTITY COLUMN ?
> >
> > thks ^ rdgs
>|||thks Adam & Greg !! Cheers
>--Original Message--
>That's solid advice from Adam.
>Just adding my 20c though: If you design the use of
identities into a
>database / application, you throw away the ability to
partition tables in
>the database later which might be important if the
database grows
>substantially. This is due to a limitation of the SQL
2000 partitioning
>design which has been fixed in SQL 2005.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com>
wrote in message
>news:elUAjERdEHA.2352@.TK2MSFTNGP09.phx.gbl...
>> Best shown through an example:
>> CREATE TABLE #IDTest(AChar CHAR(1), IDCol INT IDENTITY
(1,1))
>> GO
>> INSERT #IDTest (AChar) VALUES ('A')
>> INSERT #IDTest (AChar) VALUES ('B')
>> INSERT #IDTest (AChar) VALUES ('C')
>> GO
>> SELECT *
>> FROM #IDTest
>> ORDER BY IDCol
>> --
>> We seeded the identity with a start value of 1, and
increment each row by
>1.
>> (IDENTITY(1,1)). Every row we insert is now given a
unique, sequential
>ID.
>> So the 'A' row has an ID of 1, the 'B' row 2, and
the 'C' row 3. This ID
>> column can be used to track order of INSERTs or more
commonly as a
>surrogate
>> key for joining to other tables.
>> Number one thing to remember: Never rely on the
IDENTITY being
>consecutive!
>> Rows can be deleted, transactions can be rolled back,
and the identity can
>> be re-seeded. So there is certainly no guarantee that
there won't
>> eventually be gaps.
>> You should also always make sure when using the
IDENTITY as a primary key
>> that you have other constraints in place to ensure
uniqueness of your
>data.
>> Mis-use of IDENTITY columns for primary keys is a very
common source of
>data
>> integrity problems. So use them wearily.
>>
>> "maxzsim" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:628c01c4750f$50c5dc40$a401280a@.phx.gbl...
>> > Hi Ppl,
>> >
>> > Could i know what's the purpose of the IDENTITY
COLUMN ?
>> >
>> > thks ^ rdgs
>>
>
>.
>

Purpose od IDENTITY column

Hi Ppl,
Could i know what's the purpose of the IDENTITY COLUMN ?
thks ^ rdgsAn identity column is a self generated column that you can use in a table.
It contains a starting value and an increment. So that means if that if you
insert a row which has an identity column called "myID", then the first row
will automatically insert a value of 1 to myID (assuming the starting value
is 1). If you've defined the increment to be 2, then the 2nd row's myID
will now be 1+2=3. The 3rd row will have a myID=5.
The only caveat is that, if you were to rollback a statement, the Identify
value doesnt get rolled back.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this da
y.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:
> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>sql

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.
Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

pull subscription chaning identity seed/increment

I have an anynonomous pull merge set and working. But, I create a new
table on the client machine with increment and seed as -1000 and -20.
The table is created fine. But when I execture the first synchrnoize
data from the client, the table is changed to be 1, 2, which is what the
server has. I am using SQL-DMO on the client with the following code:
With myMergeObj
.Distributor = inxRS.Globals.gServer
.DistributorLogin = "inxsql"
.DistributorPassword = "inxsql4u"
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.DistributorNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.DistributorAddress = inxRS.Globals.gServerIP
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = in_comp
.Publication = Trim(in_comp) & "RS"
.PublisherLogin = "inxsql"
.PublisherPassword = "inxsql4u"
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.PublisherNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.PublisherAddress = inxRS.Globals.gServerIP
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = in_comp
.SubscriberSecurityMode = SECURITY_TYPE.NT_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.ANONYMOUS
.SubscriptionName = Trim(in_comp) & "RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
.ReinitializeSubscription(false)
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
I can't tell if it is a problem in the pull or in how the publication
was/is setup.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I removed the reinit statement and I still have the same problem.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||FIXED IT.
Darin
*** Sent via Developersdex http://www.codecomments.com ***