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 ?
>
>.
>
Showing posts with label ppl. Show all posts
Showing posts with label ppl. Show all posts
Friday, March 23, 2012
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.
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]
>
>.
>
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
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
>>
>
>.
>
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.
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.
Monday, February 20, 2012
Public key Token = b77a5c561934e089
Hi Ppl,
I have installed my program and SQL server 2K5 on the server.
But when I try to run it (from he client machine)I get the following error :
It works fine on the SERVER
The appication attempted to perform an operation not allowed by the security policy.
The operation required the security Exception. To grant this application the required permission
please contact your systems Administrator or use the Microsoft .NET security policy administration tool.
If you click Bla,bla,bla
Request for the permision of type System.Data.SqlClient.SqlClient
Permission, System.Data Version - 1.0.5000.0. Cultre = neutral.
Public key Token = b77a5c561934e089
did you ever solve this as I now have the same issue|||
I've done some checking and this seems to be related to the fact that you're apparently running your application from a network share. When you run a managed application from the network, you need to either trust the network or trust the assembly by adjusting the security policy on the local computer. Check out the Code Access Security information for more details.
The alternative to adjusting the security policy is to install the application onto the local computer via either ClickOnce or MSI. More details on the these tools can be found at:
For .NET 1.1 see
Smart Client Architecture and Design Guide
Chapter 7 — Deploying and Updating Smart Client Applications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scag-ch07.asp
For .NET 2.0 see
ClickOnce
http://msdn.microsoft.com/clickonce
Regards,
Mike Wachal
SQL Express team
Subscribe to:
Posts (Atom)