The fastest way to return SQL Server 2005 primary key value

Question

I have a table with two columns with primary key (int) and unique value (nvarchar (255))

When I insert a value into this table, I can use Scope_identity () to return the primary key for the value I just inserted. However, if the value already exists, I need to make an additional selection to return the primary key for the next operation (inserting this primary key into the second table).

I think there should be a better way to do this - I looked at using private indexes, but there are only two columns in the table, most of what I read on the covered indexes says that they only help where the table is much larger than index.

Is there a faster way to do this? Will the covered index be faster even if it is the same size as the table?

Answer 1

Building an index will not bring you anything, because you already created your column of values ​​as unique (which builds the index in the background). Effectively a full table scan is no different from an index scan in your scenario.

I assume that you want to have some kind of insert-if-not-already-existsts behavior. There is no way around the second choice.

if not exists (select ID from where name = @...) insert into ... select SCOPE_IDENTITY() else (select ID from where name = @...) 

If the value exists, the request is usually cached, so there should be no performance for the second choice of identifier.

Answer 2

Create a unique index for the second record, then:

 if not exists (select null from ...) insert into ... else select x from ... 

You cannot get away from the index, and in fact it is not so much overhead - the SQL server supports indexing columns up to 900 bytes and does not distinguish.

The needs of your model are more important than any perceived performance problems that symbolize the string (this is what you are doing) - this is a general method of reducing the size of the database, and this indirectly (and generally) means better performance.

- change -

To calm timothy:

 declare @x int = select x from ... if (@x is not null) return x else ... 

Answer 3

 [Update statment here] IF (@@ROWCOUNT = 0) BEGIN [Insert statment here] SELECT Scope_Identity() END ELSE BEGIN [SELECT id statment here] END 

I do not know about performance, but it does not have big overhead

Answer 4

As already mentioned, this really should not be a slow operation, especially if you are indexing both columns. However, if you decide to reduce the cost of this operation, I see no reason why you could not completely delete the table and just use the unique value directly, and not look for it in this table. A mapping 1-1 like this is (theoretically) redundant. I speak theoretically because there may be performance implications for using nvarchar instead of int.

Answer 5

I will post this answer as everyone else seems to say that you need to query the table twice if the record exists ... it is not.

Step 1) Create a unique index in another column:

I recommend this as an index:

- We include the "ID" column so that SQL does not have to look far as soon as the "WHERE" clause is complete.

 CREATE INDEX MyLilIndex ON dbo.MyTable (Column2) INCLUDE (ID) 

Step 2)

 DECLARE @TheID INT SELECT @TheID = ID from MyTable WHERE Column2 = 'blah blah' IF (@TheID IS NOT NULL) BEGIN -- See, you don't have to query the table twice! SELECT @TheID AS TheIDYouWanted END ELSE INSERT... SELECT SCOPE_IDENTITY() AS TheIDYouWanted 

Answer 6

You can use the OUTPUT clause to return a value in the same expression. Here is an example.

DDL:

 CREATE TABLE ##t ( id int PRIMARY KEY IDENTITY(1,1), val varchar(255) NOT NULL ) GO -- no need for INCLUDE as PK column is always included in the index CREATE UNIQUE INDEX AK_t_val ON ##t (val) 

DML:

 DECLARE @id int, @val varchar(255) SET @val = 'test' -- or whatever you need here SELECT @id = id FROM ##t WHERE val = @val IF (@id IS NULL) BEGIN DECLARE @new TABLE (id int) INSERT INTO ##t (val) OUTPUT inserted.id INTO @new -- put new ID into table variable immediately VALUES (@val) SELECT @id = id FROM @new END PRINT @id