CODEWITHSUNDEEP
sqlt-sql
Jaiesh_bhai
Jaiesh_bhai

Reputation: 1814

Optimal way to check if value is unique in t-sql

Is there a function for a quicker way to check if a given value is unique in Microsoft SQL Server Management Studio? Excluding the following:

DECLARE @username VARCHAR(45)
SET @username = 'foo'

DECLARE @checkTable INT

-- finds how many rows contain 'foo'
SET @checkTable = (SELECT COUNT(1) 
                   FROM table1 AS t
                   WHERE t.username = @username)
IF @checkTable = 0
-- Unique!

If you have a large table this seems like it would be expensive. I need to allow an user to change his/her existing username while remaining unique.

Thanks

Upvotes: 0

Views: 1134

Answers (2)

jhilden
jhilden

Reputation: 12439

IF EXISTS (SELECT 1 FROM table1 WHERE username = @Username)
BEGIN
     RETURN 'exists'
END
ELSE
BEGIN
     RETURN 'not exists'
END;

Upvotes: 1

David
David

Reputation: 34563

This saves several characters...

DECLARE @username VARCHAR(45)
SET @username = 'foo'

IF NOT EXISTS(SELECT 1 FROM table1 WHERE username = @username)
BEGIN
    -- Unique!
END

Upvotes: 3

Related Questions