March 3, 2012

Find and Delete duplicate records in a table

Many times you can face problem of duplicate records in table.So How would you identify and delete duplicate records in a table?

For that Firstly check if table has duplicate records or not using below query.

    SELECT [FirstName] FROM tblTest GROUP BY [FirstName] HAVING COUNT(*) > 1

Then Delete duplicate records.

DELETE FROM tblTest WHERE ID NOT IN (SELECT MAX(ID) FROM tblTest GROUP BY [FirstName])

Difference between Primary Key and Unique Key

  •     Both Primary Key and Unique Key gives uniqueness of the column on which they are defined.
  •     By default Unique Key creates a nonclustered index whereas Primary Key creates a clustered index on the column.
  •     A Primary key value cannot be NULL whereas Unique Key allows only one NULL.

Difference between Truncate and Delete in SQL


Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.
  • TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

  • You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .

  • You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

  • A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.

  • If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.

  • TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

  • Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.

  • TRUNCATE is faster than DELETE.

Difference between Stored procedure and User Functions


Fundamental difference between Stored procedure vs User Functions:
  • Procedure may return none or more values.Function must always return one value either a scalar value or a table.
  • Procedure have input,output parameters.Functions have only input parameters.
  • Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
  • Functions can be called from procedure.Procedures cannot be called from function.
  • Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
  • Transaction management possible in procedure but not in function.