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