December 27, 2013 at 10:19 pm
Hello everyone,
So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.
then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert
So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.
So now I'm wondering 2 things...
1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or
2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...
after days and hours of google searching on this problem, I finally decided to come and talk to you all...
I'd like to solve this before this product gets too far into it's production lifespan.
cheers.
December 28, 2013 at 2:25 am
Uniqueidentifiers are a rather bad choice for the clustered index (which is by default on the primary key). NewSequentialID is better than NewID, but still not great.
http://www.sqlservercentral.com/articles/Indexing/68563/
Look up the OUTPUT clause to get the pk value back. Scope_identity is specifically for numeric columns that are marked as identity. Non-numeric data types can't be identity
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2013 at 3:25 am
$conn = MSSQL_CONN; // constants connectionstring
$tsql= "INSERT INTO [dbo].
(column1,column2,etc) VALUES (?,?,?);";
$params = array( $item1, $item2, etc);
$stmt = sqlsrv_query($conn, $tsql, $params);
if($stmt === false)
die(print_r(sqlsrv_errors(), true));
The OUTPUT methods I've been reading don't have any examples within this method, using sqlsrv. I've also attempted:
$stmt = sqlsrv_prepare($conn, $tsql, $params);
if($stmt === false)
die(print_r(sqlsrv_errors(), true));
// execute here...
I realize I need to put the OUTPUT after
INSERT INTO [dbo].
(column1,column2,etc) <OUTPUT something> VALUES ()
but nothing that I've found to really guide me through it
December 28, 2013 at 3:33 am
I strongly vote for using a stored procedure instead of the direct Insert.
There are numerous reasons, one of them is to separate the database structure from the application layer. A few more are security and maintainability.
To get an example how to use the output clause, please have a look at Example A.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply