SQL Server – How to insert a record and make sure it is unique

by

Your solution:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)

…is about as good as it gets. You could simplify it to this:

INSERT INTO Words (Word)
    SELECT @Word
WHERE NOT EXISTS (SELECT * FROM Words WHERE Word = @Word)

…because EXISTS doesn’t actually need to return any records, so the query optimiser won’t bother looking at which fields you asked for.

As you mention, however, this isn’t particularly performant, because it’ll lock the whole table during the INSERT. Except that, if you add a unique index (it doesn’t need to be the primary key) to Word, then it’ll only need to lock the relevant pages.

Your best option is to simulate the expected load and look at the performance with SQL Server Profiler. As with any other field, premature optimisation is a bad thing. Define acceptable performance metrics, and then measure before doing anything else.

If that’s still not giving you adequate performance, then there’s a bunch of techniques from the data warehousing field that could help.

More Related Contents:

  1. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  2. SELECT INTO a table variable in T-SQL
  3. incorrect syntax near keyword where [closed]
  4. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  5. How do I create a unique constraint that also allows nulls?
  6. How do I get list of all tables in a database using TSQL?
  7. Storing images in SQL Server?
  8. How to convert SQL Server’s timestamp column to datetime format
  9. Escape a string in SQL Server so that it is safe to use in LIKE expression
  10. SQL time difference between two dates result in hh:mm:ss
  11. Storing files in SQL Server
  12. Dapper.NET and stored proc with multiple result sets
  13. “order by newid()” – how does it work?
  14. Import ‘xml’ into Sql Server
  15. Query times out when executed from web, but super-fast when executed from SSMS
  16. T-SQL Dynamic SQL and Temp Tables
  17. Efficient ISNUMERIC() replacements on SQL Server?
  18. How to determine total number of open/active connections in ms sql server 2005
  19. Login to Microsoft SQL Server Error: 18456
  20. Adding a query hint when calling Table-Valued Function
  21. Find non-ASCII characters in varchar columns using SQL Server
  22. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  23. How can I determine in SQL Server if a dateTime range overlaps another
  24. Intermittent ODBC connection failures
  25. SQL Server Query – groupwise multiplication
  26. SQL Server Configuration Manager cannot be found
  27. SQL Server recursive query
  28. Getting week number off a date in MS SQL Server 2005?
  29. Set database collation in Entity Framework Code-First Initializer
  30. How to get a list of all the MS SQL Server instances on the local machine?

Leave a Comment