2

Which is better distinct or unique constraint for table in SQL Server Database ?

Should I use

distinct

for getting records from the large table or put

unique constraint

to the field so no duplicate entry happened ?

My ultimate goal is only that , get unique data, and i know both will give me this, But if i use unique constraint to field , then It will give me sql error at a time i insert duplicate data. Is it ok ? Is it affect to server or Databases ? I am using SQL Server for this process.

Improve this question
2
  • Is the data meant to be distinct or not? If so, create a unique constraint in your database and do not allow your data to be invalid Commented Nov 12, 2014 at 7:17
  • 1
    Use unique constraint and handle insertion errors (or check data before insert). Duplicate data in database has no sense. Commented Nov 12, 2014 at 7:23

3 Answers 3

Reset to default
2

They're totally different use cases.

A unique constraint is what you use if the column itself (or set of columns) must be unique according to the schema details (the data). In other words, if the data is required to be unique on that column (or column set), use a unique constraint.

For example, if you're maintaining a membership table, the member ID should be unique.

The database must protect itself from dodgy data, this is not something that should be left to well-behaved applications, since the first non-well-behaved application that comes along is going to destroy your universe.

If the data is not required to be unique (such as the town each member lives in), then you can decide to "uniquify" it in a select statement, depending on your needs:

-- Get all towns.
select distinct town from members

So, here's your solution matrix, in decreasing priority:

  1. Does the actual data need to be unique on that column? If so, a unique constraint must be used. Otherwise, a unique constraint should not be used.

  2. If the data does not need to be uniques, do you need to only get one row for each possible value for that data? If so, use select distinct. If not, use select on its own.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You are right,But if i use distinct , it means Table may have duplicate entry and make table size large, If i use unique constraint No duplicate entry but it will generate error. IS error affect to database ?
@bharatpatel: I'm not sure I made it clear so I've updated. The generation of errors when you try to insert a duplicated unique piece of data is the correct behaviour for the DBMS and it's something you should allow for, since the alternative is dodgy data.
1

Depends.

With distinct you pay at query time, but it's simpler for the user.

With unique constraint, you pay at insert time, and the app now has to handle exceptions on duplicates, but the query is faster.

Without more info, I would go with distinct, because life is simpler and you don't lock in behaviour (next week you may need the duplicates).

Improve this answer

1 Comment

You are right,But if i use distinct , it means Table may have duplicate entry and make table size large, If i use unique constraint No duplicate entry but it will generate error. IS error affect to database ?
0
  • UNIQUE: always take part in data INSERTION (in brief)
  • DISTINCT: always concern on data retrieval (in brief)

Maybe this will help you.

Improve this answer

1 Comment

You are right,But if i use distinct , it means Table may have duplicate entry and make table size large, If i use unique constraint No duplicate entry but it will generate error. IS error affect to database ?

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.