SQL ADDA

Q - Delete duplicate data from a table. A - First of all create a table -  create table del_duplicate  (  id int ,  name varchar (15),  class varchar (15)  ) insert records :  insert into del_duplicate values ('1','Abhishek','IT')  insert into del_duplicate values ('1','Abhishek','IT')  insert into del_duplicate values ('2','Amit','Civil')  insert into del_duplicate values ('2','Amit','Civil') Query for delete duplicate data -: Method 1: SELECT DISTINCT id,name,class INTO    tempTable FROM    del_duplicate GO TRUNCATE TABLE del_duplicate DROP TABLE del_duplicate exec sp_rename 'tempTable', 'del_duplicate' Method 2:       SET NOCOUNT ON       SET ROWCOUNT 1       WHILE 1 = 1        BEGIN        DELETE   FROM del_duplicate       WHERE    id IN (SELECT  id   ...

Q- What is DBCC command in SQL sever. A - DBCC  (Database consistency checker ) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises. I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples. These are four types: Maintenance Informational Validation Miscellaneous                Maintenance Commands Performs maintenance tasks on a database, index, or file group. Syntax: DBCC  CLEANTABLE ( ‘DatabaseName’,’TableName.ColumnName’ ,0) 2.  DBREINDEX  – Builds one or more indexes for the table in the specified database. (Will be  removed  in the future version, use  ALTER INDEX  instead) Syntax: USE DatabaseName DBCC  DBREINDEX ( ‘TableName.ColumnName’,’PK_ID’ ,80) 3.  DROPCLEANBUFFERS  – Removes all clean buffers from buffer pool. Synta...