CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2012
Sudhir Panda
Sudhir Panda

Reputation: 784

How to deal with duplicate records in SQL Server database

I have a table with records as below image

Student Table

I have to maintain the replica of student ABC as shown below, for each student in each process can contain only one active row. What is the best way to stop duplicates for every student entry from db end. how i manage uniqueness of active row.

How to check new INSERTS to prevent future duplicates.

Upvotes: 2

Views: 1948

Answers (5)

michel
michel

Reputation: 1

Unique index with filter can solve the issue

it is also known as filtered index

create unique index unq_table_filtered_IX
    on table(columns) where condition;

Upvotes: 0

user5620749
user5620749

Reputation: 

create unique index unq_table_name_processid_filtered on table(name, processid) where IsActive = 1;

1 for bit type true

Upvotes: 1

code save
code save

Reputation: 1106

try it

 WITH CTE 
 AS
    (
    SELECT *,RN=ROW_NUMBER() OVER (PARTITION BY orgName ORDER BY orgName DESC)
    FROM organizations 
    )
    select * 
    from CTE 
    where RN>1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You can maintain the uniqueness by using a unique filtered index:

create unique index unq_table_name_processid_filtered
    on table(name, processid) where IsActive = 'true';

Here is the documentation on the topic.

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31795

I would do this with a CHECK constraint that calls a UDF.

You could also manage this with a TRIGGER.

Upvotes: 0

Related Questions