Fastest way to return a primary key value SQL Server 2005

Question

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

When I insert a value to 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 have to perform an additional select to return the primary key for a follow up operation (inserting that primary key into a second table)

I'm thinking there must be a better way to do this - I considered using covered indexes but the table only has two columns, most of what I've read on covered indexes suggests they only help where the table is significantly larger than the index.

Is there any faster way to do this? Would a covered index be faster even if its the same size as the table?

Answer 1

Create a unique index for the second entry, then:

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

You can't get away from the index, and it isn't really much overhead -- SQL server supports index columns upto 900-bytes, and does not discriminate.

The needs of your model are more important than any perceived performance issues, symbolising a string (which is what you are doing) is a common method to reduce database size, and this indirectly (and generally) means better performance.

-- edit --

To appease timothy :

declare @x int = select x from ...

if (@x is not null)
   return x 
else
   ...

Answer 2

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

I don't know about performance but it has no big overhead

Answer 3

Building an index won't gain you anything since you have already created your value column as unique (which builds a index in the background). Effectively a full table scan is no different from an index scan in your scenario.

I assume you want to have a sort of insert-if-not-already-existsts behaviour. There is no way getting around a second select

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

If the value happens to exist, the query will usually have been cached, so there should be no performance hit for the second ID select.

Answer 4

As has already been mentioned this really shouldn't be a slow operation, especially if you index both columns. However if you are determined to reduce the expense of this operation then I see no reason why you couldn't remove the table entirely and just use the unique value directly rather than looking it up in this table. A 1-1 mapping like this is (theoretically) redundant. I say theoretically because there may be performance implications to using an nvarchar instead of an int.

Answer 5

I'll post this answer since everyone else seems to say you have to query the table twice in the event that the record exists... that's not true.

Step 1) Create a unique-index on the other column:

I recommend this as the index:

-- We're including the "ID" column so that SQL will not have to look far once the "WHERE" clause is finished.

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 could use OUTPUT clause to return the value in the same statement. 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