?

Log in

Previous Entry | Next Entry

Below are two common ways

       1)      When it has identity column called ID
                     a.
                             Delete Table1
                             from MyTable Table1
                             join MyTable Table2 on Table1.Column1= Table2. Column1
                            And Table1.Column2= Table2. Column2
                            and Table1.ID> Table2. ID

                     b.    Delete from MyTablewhere
                            ID in (select ID from MyTablegroup by Column1,Colum2 having count(*) >1)

                     c.    Delete From MyTable where Id not in (Select Min(ID) from MyTable group by Column1,Column2)

                     d.    Delete From MyTable where Id not in (Select Max(ID) from MyTable group by Column1,Column2)

      2)      When it won’t contain Identity column
                    a.      
                                  i.    ALTER TABLE MyTableADD ID INT IDENTITY(1,1)
                                 ii.   (One of the query from above)
                                iii.    ALTER TABLE MyTable  Drop ColumnID

                    b.    SELECT DISTINCT * INTO #temp FROM MyTable
                           TRUNCATE TABLE MyTable
                           INSERT INTO MyTable
                           SELECT * FROM #temp


We are thankful to questpond.com to provide this SQL Server interview question with answers

You can also see the below SQL Server interview question video on finding the second highest from a given table.

Copy right www.questpond.com

Buy 600+ Real Time Interview Videos

Call +91-22-66752917 or mail us on questpond@questpond.com for more details on C#, .NET, ASP.NET, SQL Server interview question videos.

SQL Server interview question videos

Latest Month

January 2017
S M T W T F S
1234567
891011121314
15161718192021
22232425262728
293031    

Tags

Powered by LiveJournal.com