|
|
You're in the
sql server programming
group:uniqueidentifier column
sql server programming:
I'm using a uniqueidentifier column as the "identity" primary key column for
a table. How can I retrieve the value of that column after an insert (using NEWID())? I tried something like: "INSERT INTO TABLE........... SELECT @@Identity" The @@Identity does not work in this case. It returns NULL Thanks, Fernando Chilvarguer
I don't think that you can use identity on
UniqueIdentifier. It must use newid() only. [quoted text, click to view] >-----Original Message-----
>I'm using a uniqueidentifier column as the "identity" primary key column for >a table. >How can I retrieve the value of that column after an insert (using NEWID())? >I tried something like: >"INSERT INTO TABLE........... SELECT @@Identity" >The @@Identity does not work in this case. It returns NULL > >Thanks, >Fernando Chilvarguer > > >.
[quoted text, click to view]
>> I'm using a uniqueidentifier column as the "identity" primary key column for a table. <<That makes no sense in relational terms. Please quit writing such low-level, non-relational, non-portable, proprietary code before you put your employer out of business. --CELKO-- =========================== Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. *** Sent via Developersdex http://www.developersdex.com ***
Thanks for all the answers. It fixed my problem.
Now another question: Which one would be the "best practice" way of doing it: 1. Using the indentity field with @@Identity or 2. Using a uniqueidentifier field and the NEWID() function? Or would it be just a matter of personal preference? Thanks again, Fernando [quoted text, click to view] "Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:uWWKB3VaEHA.1508@TK2MSFTNGP09.phx.gbl... > Hi Fernando > > You have to capture the value BEFORE inserting it, not after. It's quite the > opposite of identity in this respect. > > eg: > > declare @id uniqueidentifier > select @id = newid() > insert into mytable (udcolumn) values (@id) > ... > > Knowing the value before it's inserted is one of the key advantages of using > GUIDs over IDENTITIES. > > HTH > > Regards, > Greg Linwood > SQL Server MVP > > "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message > news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > > I'm using a uniqueidentifier column as the "identity" primary key column > for > > a table. > > How can I retrieve the value of that column after an insert (using > NEWID())? > > I tried something like: > > "INSERT INTO TABLE........... SELECT @@Identity" > > The @@Identity does not work in this case. It returns NULL > > > > Thanks, > > Fernando Chilvarguer > > > > > >
It would be a lot more helpful to suggest a better way of doing it instead
of just criticizing. Your reply did not add anything useful to this thread. [quoted text, click to view] "Joe Celko" <jcelko212@earthlink.net> wrote in message news:OD4UKQWaEHA.752@TK2MSFTNGP09.phx.gbl... > >> I'm using a uniqueidentifier column as the "identity" primary key > column for a table. << > > That makes no sense in relational terms. Please quit writing such > low-level, non-relational, non-portable, proprietary code before you put > your employer out of business. > > --CELKO-- > =========================== > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
[quoted text, click to view]
> Which one would be the "best practice" way of doing it: > > 1. Using the indentity field with @@Identity or > 2. Using a uniqueidentifier field and the NEWID() function? I'd like to add: 3. Use the real key instead of a surrogate key. Each technique has both advantages and disadvantages. #3 is often best if your database is properly normalized. However, you might choose to introduce a surrogate key for reasons such as avoiding a large composite key and/or performance. IDENTITY is more compact than uniqueidentifier and values are assigned sequentially. This reduces storage requirements and provides more efficient index maintenance during inserts. Uniqueidentifier values are assigned randomly (in Windows 2000 and above). These can be assigned on the client side and thereby eliminate a round-trip to retrieve the assigned value. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message news:e84PC5WaEHA.3752@TK2MSFTNGP12.phx.gbl... > Thanks for all the answers. It fixed my problem. > > Now another question: > > Which one would be the "best practice" way of doing it: > > 1. Using the indentity field with @@Identity or > 2. Using a uniqueidentifier field and the NEWID() function? > > Or would it be just a matter of personal preference? > > Thanks again, > Fernando > > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message > news:uWWKB3VaEHA.1508@TK2MSFTNGP09.phx.gbl... > > Hi Fernando > > > > You have to capture the value BEFORE inserting it, not after. It's quite > the > > opposite of identity in this respect. > > > > eg: > > > > declare @id uniqueidentifier > > select @id = newid() > > insert into mytable (udcolumn) values (@id) > > ... > > > > Knowing the value before it's inserted is one of the key advantages of > using > > GUIDs over IDENTITIES. > > > > HTH > > > > Regards, > > Greg Linwood > > SQL Server MVP > > > > "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message > > news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > > > I'm using a uniqueidentifier column as the "identity" primary key column > > for > > > a table. > > > How can I retrieve the value of that column after an insert (using > > NEWID())? > > > I tried something like: > > > "INSERT INTO TABLE........... SELECT @@Identity" > > > The @@Identity does not work in this case. It returns NULL > > > > > > Thanks, > > > Fernando Chilvarguer > > > > > > > > > > > >
[quoted text, click to view]
>> It would be a lot more helpful to suggest a better way of doing it instead of just criticizing. Your reply did not add anything useful tothis thread. << If this was a woodworking group and someone asked what is the best kind of rock for smashing screws into fine furniture, would you reply "Granite! Big chunks of granite!" or would you reply "Do you know about a screwdriver?" instead? --CELKO-- =========================== Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. *** Sent via Developersdex http://www.developersdex.com ***
Hi,
Unique identifier is not like an identity property. It is a 16 bit hexadecimal number. There is no functions like @@identity to get the last inserted value. The only thik before you insert the unque identifier just store it in a vatiable. DECLARE @myid uniqueidentifier SET @myid = NEWID() insert into table(myid) values(@myid) PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid) -- Thanks Hari MCDBA [quoted text, click to view] "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > I'm using a uniqueidentifier column as the "identity" primary key column for > a table. > How can I retrieve the value of that column after an insert (using NEWID())? > I tried something like: > "INSERT INTO TABLE........... SELECT @@Identity" > The @@Identity does not work in this case. It returns NULL > > Thanks, > Fernando Chilvarguer > >
Hi Fernando
You have to capture the value BEFORE inserting it, not after. It's quite the opposite of identity in this respect. eg: declare @id uniqueidentifier select @id = newid() insert into mytable (udcolumn) values (@id) .... Knowing the value before it's inserted is one of the key advantages of using GUIDs over IDENTITIES. HTH Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > I'm using a uniqueidentifier column as the "identity" primary key column for > a table. > How can I retrieve the value of that column after an insert (using NEWID())? > I tried something like: > "INSERT INTO TABLE........... SELECT @@Identity" > The @@Identity does not work in this case. It returns NULL > > Thanks, > Fernando Chilvarguer > >
Thank you Greg for pointing out. Typo error.
-- Thanks Hari MCDBA [quoted text, click to view] "Greg Linwood" <g_linwoodQhotmail.com> wrote in message news:ewCZJ5WaEHA.3804@TK2MSFTNGP10.phx.gbl... > Hi Hari. > > GUIDs use 16 bytes for storage, not 16 bits.. (^: > > Regards, > Greg Linwood > SQL Server MVP > > "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message > news:uwwfp2VaEHA.2340@TK2MSFTNGP09.phx.gbl... > > Hi, > > > > Unique identifier is not like an identity property. It is a 16 bit > > hexadecimal number. There is no functions like @@identity to get the last > > inserted value. The only thik before you insert the unque identifier just > > store it in a vatiable. > > > > > > DECLARE @myid uniqueidentifier > > SET @myid = NEWID() > > insert into table(myid) values(@myid) > > PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid) > > > > > > -- > > Thanks > > Hari > > MCDBA > > "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message > > news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > > > I'm using a uniqueidentifier column as the "identity" primary key column > > for > > > a table. > > > How can I retrieve the value of that column after an insert (using > > NEWID())? > > > I tried something like: > > > "INSERT INTO TABLE........... SELECT @@Identity" > > > The @@Identity does not work in this case. It returns NULL > > > > > > Thanks, > > > Fernando Chilvarguer > > > > > > > > > > > >
Hi,
The major advantage of using GUIDs is that they are unique across all space and time. This comes in handy if you're consolidating records from multiple SQL Servers into one table, as in a data warehousing situation. Have a look into this article which details your query. http://www.sqlteam.com/item.asp?ItemID=283 -- Thanks Hari MCDBA [quoted text, click to view] "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message news:e84PC5WaEHA.3752@TK2MSFTNGP12.phx.gbl... > Thanks for all the answers. It fixed my problem. > > Now another question: > > Which one would be the "best practice" way of doing it: > > 1. Using the indentity field with @@Identity or > 2. Using a uniqueidentifier field and the NEWID() function? > > Or would it be just a matter of personal preference? > > Thanks again, > Fernando > > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message > news:uWWKB3VaEHA.1508@TK2MSFTNGP09.phx.gbl... > > Hi Fernando > > > > You have to capture the value BEFORE inserting it, not after. It's quite > the > > opposite of identity in this respect. > > > > eg: > > > > declare @id uniqueidentifier > > select @id = newid() > > insert into mytable (udcolumn) values (@id) > > ... > > > > Knowing the value before it's inserted is one of the key advantages of > using > > GUIDs over IDENTITIES. > > > > HTH > > > > Regards, > > Greg Linwood > > SQL Server MVP > > > > "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message > > news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > > > I'm using a uniqueidentifier column as the "identity" primary key column > > for > > > a table. > > > How can I retrieve the value of that column after an insert (using > > NEWID())? > > > I tried something like: > > > "INSERT INTO TABLE........... SELECT @@Identity" > > > The @@Identity does not work in this case. It returns NULL > > > > > > Thanks, > > > Fernando Chilvarguer > > > > > > > > > > > >
Hi Hari.
GUIDs use 16 bytes for storage, not 16 bits.. (^: Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message news:uwwfp2VaEHA.2340@TK2MSFTNGP09.phx.gbl... > Hi, > > Unique identifier is not like an identity property. It is a 16 bit > hexadecimal number. There is no functions like @@identity to get the last > inserted value. The only thik before you insert the unque identifier just > store it in a vatiable. > > > DECLARE @myid uniqueidentifier > SET @myid = NEWID() > insert into table(myid) values(@myid) > PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid) > > > -- > Thanks > Hari > MCDBA > "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message > news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > > I'm using a uniqueidentifier column as the "identity" primary key column > for > > a table. > > How can I retrieve the value of that column after an insert (using > NEWID())? > > I tried something like: > > "INSERT INTO TABLE........... SELECT @@Identity" > > The @@Identity does not work in this case. It returns NULL > > > > Thanks, > > Fernando Chilvarguer > > > > > >
That's exactly my point.
You did not offer the "screwdriver" option. All you said was "Granite? Are you stupid?" [quoted text, click to view] "Joe Celko" <jcelko212@earthlink.net> wrote in message news:O$iC8ScaEHA.3524@TK2MSFTNGP12.phx.gbl... > >> It would be a lot more helpful to suggest a better way of doing it > instead of just criticizing. Your reply did not add anything useful to > this thread. << > > If this was a woodworking group and someone asked what is the best kind > of rock for smashing screws into fine furniture, would you reply > "Granite! Big chunks of granite!" or would you reply "Do you know about > a screwdriver?" instead? > > --CELKO-- > =========================== > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Another consideration I'd add is that using @@identity limits (close to
eliminates) your ability to use SQL2K partitioned views for performance in the future. Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message news:e84PC5WaEHA.3752@TK2MSFTNGP12.phx.gbl... > Thanks for all the answers. It fixed my problem. > > Now another question: > > Which one would be the "best practice" way of doing it: > > 1. Using the indentity field with @@Identity or > 2. Using a uniqueidentifier field and the NEWID() function? > > Or would it be just a matter of personal preference? > > Thanks again, > Fernando > > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message > news:uWWKB3VaEHA.1508@TK2MSFTNGP09.phx.gbl... > > Hi Fernando > > > > You have to capture the value BEFORE inserting it, not after. It's quite > the > > opposite of identity in this respect. > > > > eg: > > > > declare @id uniqueidentifier > > select @id = newid() > > insert into mytable (udcolumn) values (@id) > > ... > > > > Knowing the value before it's inserted is one of the key advantages of > using > > GUIDs over IDENTITIES. > > > > HTH > > > > Regards, > > Greg Linwood > > SQL Server MVP > > > > "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message > > news:OqYI5cVaEHA.3752@TK2MSFTNGP12.phx.gbl... > > > I'm using a uniqueidentifier column as the "identity" primary key column > > for > > > a table. > > > How can I retrieve the value of that column after an insert (using > > NEWID())? > > > I tried something like: > > > "INSERT INTO TABLE........... SELECT @@Identity" > > > The @@Identity does not work in this case. It returns NULL > > > > > > Thanks, > > > Fernando Chilvarguer > > > > > > > > > > > >
I agree!! (^:
Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "Fernando Chilvarguer" <fernando@nospam-impex.com> wrote in message news:ezLbh8WaEHA.1840@TK2MSFTNGP11.phx.gbl... > It would be a lot more helpful to suggest a better way of doing it instead > of just criticizing. > Your reply did not add anything useful to this thread. > > "Joe Celko" <jcelko212@earthlink.net> wrote in message > news:OD4UKQWaEHA.752@TK2MSFTNGP09.phx.gbl... > > >> I'm using a uniqueidentifier column as the "identity" primary key > > column for a table. << > > > > That makes no sense in relational terms. Please quit writing such > > low-level, non-relational, non-portable, proprietary code before you put > > your employer out of business. > > > > --CELKO-- > > =========================== > > Please post DDL, so that people do not have to guess what the keys, > > constraints, Declarative Referential Integrity, datatypes, etc. in your > > schema are. > > > > *** Sent via Developersdex http://www.developersdex.com *** > > Don't just participate in USENET...get rewarded for it! > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |