Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Advice: Where to check for duplicates

    Lets say I have a proc
    Code:
    PROC addAuthor( @name varchar ) 
    BEGIN 
        IF EXISTS (SELECT * FROM authors WHERE author_name = @name )
            BEGIN
                RAISEERROR('not allowed two authors with same name')
                RETURN
            END
        INSERT INTO authors(author_name) values(@name)
    END
    Now... As you can see, I ensure that no two authors have the same name.

    Would it be better if I did that check within a TRIGGER for that table?? Or is it fine where I have it?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so it looks like you run a SELECT, followed (in most cases) by an INSERT

    let's say the odds of a dupe are 3%

    so out of every 100 attempts, 97 successfully insert, and, in total, you are doing 197 database operations

    the alternate strategy is to let the database detect the dupes

    in total, this method does 100 database operations

    this is almost twice as efficient!!

    all you need to do is declare a UNIQUE constraint, do not to the initial SELECT (the database will do it for you) and trap the error if a dupe is detected

    neat, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Thanks! I've put that on my tables and was able to remove that check condition from my stored procedures

  4. #4
    Join Date
    Feb 2007
    Posts
    62
    You also have to consider that in a multi-user environment, what is true at one point in time is not necessarily true at another. Doing it your way there is always an outside chance of the uniqueness changing between the check & the insert. No such problems with a UC.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •