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]
>
>.
>