Check Your SQL Server Identity
-
I've created some SP's similar to yours - where you are basically checking for the existence of a value in a column to avoid duplicates. This could also be achieved via a UNIQUE constraint.
Cursors are horrible. How do you tell if you need one? Basically 99.999% of the time you don't need them. As Joe Celko points out, "I've been working with SQL for 30 years and I've only needed to use a cursor 3 times, and two of those times I found a better set-based solution after the fact." (or something to that effect.)
Keep in mind that SQL is optimized for set-based logic, and always look for the set-based solution first. I posted a blog entry about this a while ago at http://sqlservercentral.com/cs/blogs/michael_coles/archive/2005/07/15/47.aspx.
Basically when you use a cursor you are telling SQL Server step-by-step how to do a task, much like any other 3GL/4GL language. SQL is not optimized for this.
For an administrative task, like copying tables, I would look to either another language (like VB.NET, etc.), or DTS, bulk copy, or some other tool to do the job. Those are just my thoughts though.
-
I agree. The primary reasons I use GUIDs are for replication and/or for generating unique alphanumeric transaction ID's, etc. It's just too bad that there is no built-in support for a Base 36 (all alpha + all numeric) type of GUID without some sort of conversion on the binary GUID values. That would definitely come in handy in customer-service and other scenarios.
Perhaps a Base 36 GUID is a good candidate for SQL 2005's new UDTs?
-
Speaking of middle tier, ADO.NET's SqlDataAdapter's wizard INSERT statement by default retrieves @@IDENTITY. (At least with my version.) That makes sense, because it's disconnected with the version of SQL Server it will be running on. So if your developers will be directly interfacing with IDENTITY tables, they need to be aware of this and may want to change it.
Would SCOPE_IDENTITY() be available on OleDBDataAdapter's insert? If you are using this adapter, would you WANT to use SCOPE_IDENTITY?
-
If you're using SP's to perform the actual INSERT (as I usually do), you probably would want to use SCOPE_IDENTITY inside the SP itself. As for using a direct INSERT statement, not sure...
-
I usually do one interview every week or two for developers who need to know about SQL, but are not necessarily DBA's. I get close to 100% failure rate on this question and I ask it every time. I'm interviewing mid to senior level developers with 5 to 10 or more years experience that usually includes at least some SQL Server experience. It doesn't necessarily disqualify a developer but it's one indicator about how deep they've been in the data layer. It usually means I've got somebody who can probably code C# of VB.NET and put together some ASP.NET pages but maybe I can't leave them alone in the database for very long.
-
Scope_Identity() doesn't always work either; when you have an INSTEAD OF INSERT trigger, it will return NULL (in the example below). You then have to use Ident_Current() or @@Identity or some other method. In short, I think it's best to re-assess the use of these functions and global variables each time you might make a schema change.
create table TEST2 (ContactID int not null identity (1, 1), firstname varchar(100) null, lastname varchar(100) null)
go
insert into TEST2 Default Values
select @@Identity
insert into TEST2 Default Values
select Scope_Identity()
go
create table TEST2HISTORY (HistoryID int not null identity (1, 1), ContactID int not null, firstname varchar(100) null, lastname varchar(100) null)
go
create trigger i_TEST2 on dbo.TEST2 instead of insert as
set nocount on
insert TEST2 Default Values
go
insert into TEST2 Default Values
select @@Identity
insert into TEST2 Default Values
select Scope_Identity(), Ident_Current('TEST2')
go
-
Beware that if you call scope_identity before the insert you get an error.
"Select @@Scope_Identity" from a new connection in the Query Analyzer generates:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@@Scope_Identity'.
@@Identity, conversely, returns NULL.
So scope_identity is not bulletproof either. Each scenario requires careful evaluation.
-
I don't believe SCOPE_IDENTITY() solves the problem, as the initial insert and the audit trigger insert would both be within the context of the same connection.
The only way that I've been comfortable is logically solid is to write-lock the table and use IDENT_CURRENT(<tablename>
. -
Gernerally speaking: don't use cursors.
However, there are occasions where cursors *may* be faster (or of negligible difference) - these include STATIC cursors or a small number of iterations.
But if you can do it via set-based logic, do it.
S.
-
I´m a developer in a no dba environment. I fell like a passenger in a plane where the pilot is missing and try to avoid a crash.
Yesterday I helped a coworker to build a sp to insert and retrieve the generated id using scope_identity.
Since I avoid to use triggers and the system is heavly concurrent hope I did the correct thing.
Great article, great posts.
Jean
-
All of these arguments go out the window when you begin encrypting data. Scope_Identity loses scope in an INSTEAD OF INSERT (or UPDATE) trigger returning NULL.
And for historical accuracy, SQL had not been invented in 1976 (30 years ago); it was still incubating as a loose collection of theories and routines that led to System R in 1978 at IBM labs. Ted Codd was still using the word FIND and not SELECT as in his favourite query, "Find all employees who earn more than their managers." Ted was a weird cat with a strange sense of humour.
Structured Query Language wasn't released until 1982 in the form of SQL/DS. The main action words were renamed to avoid legal conflict with NATURAL, which was released on the ADABAS database by Peter Page' in 1979. FIND became SELECT, ADD became INSERT, and so on.
Last note on this thread: Jeff Garbus said it best, "Cursors are the final refuge of those who refuse to accept set processing."
My favourite interview question is: "Who invented SQL?" All respondents under 40 reply, "Microsoft." How very sad.
-
Matt, "@@Scope_Identity" is not valid, try:
SELECT scope_identity()
instead. Like @@IDENTITY, it returns NULL on a new connection (or at least it does for me).
-
I use IDENT_SCOPE("Table_name") which is recommended in the 2K help file
John Linville
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply