3

We have a large number of tables for setup forms. In each form, there is a need to fill in the Code and the Description.

The Code consists of a varchar(10) datatype.

Now, the thing is, every time a user creates new data or updates the data, there is a need to check whether the Code conflicts with the others or not.

In LINQ, this code checking is inevitable:

string ExistingCode = "ITEM01";
int ExistingKey = 1;

var recordWithConflictedCode = (from a in db.MyTable
                               where a.Code == ExistingCode && 
                               a.Key != ExistingKey).ToList();

if(recordWithConflictedCode.Count >= 1)
    return BadRequest("Duplicated Code found");

Now, since this involves a string comparison on every process, I think it makes sense to index the column Code to make the query faster.

Is this a common practice for you guys?

Improve this question

1 Answer 1

Reset to default
5

Use the DBMS for its intended purpose; that is create a constraint on the table so that it fits the business rules.

Designing your table so it looks like:

USE tempdb;
CREATE TABLE dbo.MyTable
(
    [Key] int NOT NULL
    , Code varchar(10) NOT NULL
         CONSTRAINT uq_MyTable_Code
         UNIQUE 
);

This will ensure every row inserted into MyTable has a unique Code column. Now you can add a try ... catch statement around your inserts in .Net code to inform the user when a duplicate is detected.

The SQL Server engine will automatically create a unique index on the table to support the constraint. You could just manually create a unique index on the table to satisfy the same requirements; however adding it as a unique constraint in the way detailed above self-documents the business rule.

Improve this answer
8
  • Thank you! So the answer is, it is fine to index that column with varchar(10). Great to know this approach!
    – zeroflaw
    Jan 20, 2017 at 3:17
  • 1
    Yes, but don't search the table each time to see if the Code already exists; simply attempt to do the insert - if it fails, inspect the error code to see if it indicates that you attempted to insert a duplicate key into the index.
    – Hannah Vernon ♦
    Jan 20, 2017 at 3:21
  • 2
    I don't know @Max, I've found that unless you have a very high success rate (e.g. very few collisions ever), your batches can take longer by letting SQL Server invoke error handling instead of preventing the error by checking yourself. See here and here. This is especially true if the app has to check the error and then do something different, causing an additional round trip.
    – Aaron Bertrand
    Jan 20, 2017 at 3:36
  • If you're talking about a different case, like trying to do an update and if it fails (rowcount = 0) then do an insert, I absolutely agree, the check for existence is wasteful and can actually lead to other problems (like deadlocks). But the threshold where letting constraint violations happen outweighs the cost of preventing them is pretty low.
    – Aaron Bertrand
    Jan 20, 2017 at 3:38
  • @Aaron Thanks for the articles. Sorry but can you explain a little bit more on the sentence "But the threshold where letting constraint violations happen outweighs the cost of preventing them is pretty low"? Do you mean my method of checking is fine?
    – zeroflaw
    Jan 20, 2017 at 4:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.