How to recover identity ID after insert in MSSQL 2008?
-
I've been searching the net for this but I couldn't find concrete answer what to use to recover identity ID from INSERT stored procedure. What is the correct way to get the ID as output in stored procedure (should I use @@IDENTITY, SCOPE_IDENTITY() or something else). CREATE TABLE [dbo].[tbl_Table]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) Thanks,
-
I've been searching the net for this but I couldn't find concrete answer what to use to recover identity ID from INSERT stored procedure. What is the correct way to get the ID as output in stored procedure (should I use @@IDENTITY, SCOPE_IDENTITY() or something else). CREATE TABLE [dbo].[tbl_Table]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) Thanks,
-
http://www.davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx[^] Answer contained within
Dave Find Me On: Web|Facebook|Twitter|LinkedIn
Folding Stats: Team CodeProject
-
I've been searching the net for this but I couldn't find concrete answer what to use to recover identity ID from INSERT stored procedure. What is the correct way to get the ID as output in stored procedure (should I use @@IDENTITY, SCOPE_IDENTITY() or something else). CREATE TABLE [dbo].[tbl_Table]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) Thanks,
No, don't use identities, they're too limiting.
-
No, don't use identities, they're too limiting.
-
No, don't use identities, they're too limiting.
If you start on about guids again I'll scream.
Never underestimate the power of human stupidity RAH
-
If you start on about guids again I'll scream.
Never underestimate the power of human stupidity RAH
I'll need a heads up before you scream. Just to plug my ears. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
If you start on about guids again I'll scream.
Never underestimate the power of human stupidity RAH
I figured that might be that persons point. One problem with guids is that they have no implicit ordering. And some times ordering for implementation needs rather than business needs is a good idea. Not to mention size and convenience. And of course guids are likely unique but not guaranteed to be so.
-
I figured that might be that persons point. One problem with guids is that they have no implicit ordering. And some times ordering for implementation needs rather than business needs is a good idea. Not to mention size and convenience. And of course guids are likely unique but not guaranteed to be so.
From my personal experience guids may affect the performance significantly if they are used for primary key and are randomly generated. To solve this i've read somewhere that is recommended to use NEWSEQUENTIALID() as default, instead NEWID() to generate keys and with clustered indexes the performances will be much better. However if I use these keys as foreign key in other table this will not help as they will not be ordered, so I choose to use integers for primary key. The idea is MSSQL to generate new id and to return it to object in code, but also I've read there might be some problems with using @@IDENTITY or SCOPE_IDENTITY()
-
I figured that might be that persons point. One problem with guids is that they have no implicit ordering. And some times ordering for implementation needs rather than business needs is a good idea. Not to mention size and convenience. And of course guids are likely unique but not guaranteed to be so.
jschell wrote:
One problem with guids is that they have no implicit ordering
This is 100% incorrect, and one of the arguments FOR guids. Primary key should have absolutely NO intelligence other than to identify the record.
Never underestimate the power of human stupidity RAH
-
From my personal experience guids may affect the performance significantly if they are used for primary key and are randomly generated. To solve this i've read somewhere that is recommended to use NEWSEQUENTIALID() as default, instead NEWID() to generate keys and with clustered indexes the performances will be much better. However if I use these keys as foreign key in other table this will not help as they will not be ordered, so I choose to use integers for primary key. The idea is MSSQL to generate new id and to return it to object in code, but also I've read there might be some problems with using @@IDENTITY or SCOPE_IDENTITY()
Igor Jas wrote:
I've read there might be some problems with using @@IDENTITY or SCOPE_IDENTITY()
Reference required, a shot like that requires a reference so we can investigate the veracity of the statement.
Never underestimate the power of human stupidity RAH
-
If you start on about guids again I'll scream.
Never underestimate the power of human stupidity RAH
Nope nope, not this time, nuh uh. But there are better ways of generating integer Ids.
-
jschell wrote:
One problem with guids is that they have no implicit ordering
This is 100% incorrect, and one of the arguments FOR guids. Primary key should have absolutely NO intelligence other than to identify the record.
Never underestimate the power of human stupidity RAH
That is one area where you and I seem to agree completely. :jig:
-
That is one area where you and I seem to agree completely. :jig:
PIEBALDconsult wrote:
agree completely
Yeah we have the fundamentals right, just disagree on the details but the thats where the art comes in and art is such a personal thing.
Never underestimate the power of human stupidity RAH
-
PIEBALDconsult wrote:
agree completely
Yeah we have the fundamentals right, just disagree on the details but the thats where the art comes in and art is such a personal thing.
Never underestimate the power of human stupidity RAH
-
I've been searching the net for this but I couldn't find concrete answer what to use to recover identity ID from INSERT stored procedure. What is the correct way to get the ID as output in stored procedure (should I use @@IDENTITY, SCOPE_IDENTITY() or something else). CREATE TABLE [dbo].[tbl_Table]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) Thanks,
go for @@IDENTITY .
-
From my personal experience guids may affect the performance significantly if they are used for primary key and are randomly generated. To solve this i've read somewhere that is recommended to use NEWSEQUENTIALID() as default, instead NEWID() to generate keys and with clustered indexes the performances will be much better. However if I use these keys as foreign key in other table this will not help as they will not be ordered, so I choose to use integers for primary key. The idea is MSSQL to generate new id and to return it to object in code, but also I've read there might be some problems with using @@IDENTITY or SCOPE_IDENTITY()
Igor Jas wrote:
However if I use these keys as foreign key in other table this will not help as they will not be ordered
That statement is either incomplete or wrong. You might want an implementation (not business way) to order data and as such you could use a integer key to do so. But whether it is a foreign key or not has nothing to do with that.
-
jschell wrote:
One problem with guids is that they have no implicit ordering
This is 100% incorrect, and one of the arguments FOR guids. Primary key should have absolutely NO intelligence other than to identify the record.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
This is 100% incorrect, and one of the arguments FOR guids. Primary key should have absolutely NO intelligence other than to identify the record.
Err...obviously what you quoted is 100% correct. Guids do not have an ordering. Integer values do. That said you might disagree with my assertion that one might need an implicit ordering in a table but that has NOTHING do with what you quoted. Presumably you are claiming that the need is never possible and can never occur. I am claiming that sometimes, rarely, it does provide a solution.
-
Mycroft Holmes wrote:
This is 100% incorrect, and one of the arguments FOR guids. Primary key should have absolutely NO intelligence other than to identify the record.
Err...obviously what you quoted is 100% correct. Guids do not have an ordering. Integer values do. That said you might disagree with my assertion that one might need an implicit ordering in a table but that has NOTHING do with what you quoted. Presumably you are claiming that the need is never possible and can never occur. I am claiming that sometimes, rarely, it does provide a solution.
jschell wrote:
Err...obviously what you quoted is 100% correct.
No you read correctly, this is a fundamental rule of database design and if you ignore it you will get bitten somewhere down the track. A PK has only 1 job, identify the record. Ordering the data is NOT part of the PKs job, thats up to the designer to implement a specific solution. This is one of the strongest arguments FOR guids, it stops developers relying on the PK for anthing but record identification, they can't use them for ordering or to identify part of the the record (the old concatenated string from bits of the record trick). The reson I don't like guids is I can never remember the bloody things when debugging a procedure X| .
Never underestimate the power of human stupidity RAH
-
go for @@IDENTITY .
Only if you don't use functions or nested procedures or triggers. Scope_Identity() is a more robust solution. Learn to use that instead (reteach the fingers).
Never underestimate the power of human stupidity RAH