How to eliminate duplicate rows in a table



/*Create table DupRecords*/
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME='DUPRECORDS' AND t.TABLE_SCHEMA='dbo')
BEGIN
Create Table dbo.DupRecords
(
id INT not null
,Name varchar(40)
,Salary money
)
END

/*Insert duplicate data*/
insert into DupRecords values
(1,'A', 1000)
,(1,'A', 1000)
,(2,'B',2000)
,(3,'C',3000)
,(2,'B',2000)
,(2,'B',2000)
,(3,'C',3000)
,(4,'D',4000)


If we want to eliminate duplicate rows in a table use the following syntax


/*Display Non duplicate data*/
SELECT distinct * FROM dbo.duprecords


The following code is used to eliminate duplicate rows based on selected columns by displaying all the columns. The columns on which duplicate is decided need to be mentioned after Partition by in the Over() function


SELECT Id,Name,Salary FROM
(
select Id,Name,Salary, ROW_NUMBER()over(partition by id,name,salary order by id) as [RowNumber] from dbo.DupRecords
) t
WHERE RowNumber=1


If we want to display what are the records we need to delete to eliminate duplicate rows in a table we can use the following sql statement.

SELECT Id,Name,Salary FROM
(
select *, ROW_NUMBER()over(partition by id,name,salary order by id) as [RowNumber] from dbo.DupRecords
) t
WHERE RowNumber>1


How to calculate running total using t-sql

/*Using correlated subqueries*/
select e.empno,e.ename, e.sal,(select sum(d.sal) from emp d where d.empno <= e.empno) as running_total
from emp e
order by e.empno

/*using joins*/
select e.empno,e.ename, e.sal, sum(d.Sal) as running_total
from emp e
inner join emp d
on e.empno >= d.EmpNo
group by e.empno,e.ename, e.sal
order by e.empno

String operations using C#.Net

/*
Following are the methods in C#.Net on String for some of the interviews
1. Reverse a string
2. Remove duplicate words in a give string
3. Remove duplicate chars
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
namespace Interview
{
  class StringOperations
  {
    /*Reverse the string -- Method 1*/
    public string ReverseString(string inputString)
    {
      StringBuilder sbTemp = new StringBuilder("");
      /*Check either the string is empty or null*/
      if (String.IsNullOrEmpty(inputString))
      {
         return sbTemp.ToString();
      }
      /* If it is a valid string take a single charcter from last till start of the
         string*/
      for (int i = inputString.Length - 1; i >= 0; i--)
      {
        sbTemp.Append(inputString.Substring(i, 1));
      }
      return sbTemp.ToString();
    }

    /*Remove duplicate words in a give string.*/
    public string RemoveDuplicateWords(string stringToRemoveDuplicates)
    {
      if (String.IsNullOrEmpty(stringToRemoveDuplicates))
      {
        return "";
      }
      ArrayList alist = new ArrayList();
      foreach (string word in stringToRemoveDuplicates.Split(' '))
      {
        if (!alist.Contains(word))
        {
          alist.Add(word);
        }
      }
      return string.Join(" ", (string[])alist.ToArray(typeof(string)));
    }

     /*Remove duplicate chars*/
     public string RemoveDuplicateChars(string removeDups)
     {
       StringBuilder sb = new StringBuilder(string.Empty);
       foreach (char c in removeDups.ToCharArray())
       {
        if (sb.ToString().IndexOf(c) == -1)
        {
          sb.Append(c);
        }
       }
       return sb.ToString();
      }
  }
}

How to restore master (system) database

• How to restore master database backup.
o Stop the Sql Server
o Start the sql server in Single user mode using the command sqlservr.exe – m from command prompt.
o Restore the master database

• How to restore master database when the master database is corrupted.
o Execute the following command by placing setup disk with the following options by navigating the folder where setup.exe is present.
start /wait \setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=
o After executing the above command, stop the sql server.
o Start the SQL Server in single user mode using the command sqlservr.exe –m
o Restore the master database from the backup.

SQL Server 2005: Features

SQL Server 2005 Features
• XML data Type
• Recursive Queries with CTE’s (Common Table Expressions)
• PIVOT and UNPIVOT
• APPLY
• TRY….CATCH error handling
• Database Mirroring
• Common Language Runtime (CLR) .Net Framework Integration
• Dynamic Management Views – Provides information on database performance.
• System Catalog Views
• SQL Server Management Objects (SMO)
• Dedicated Administrator Connection (DAC).
• Database Mail
• Online Index and Restore Options
• Table and Index Partitioning
• Multiple Active Result Sets (MAR)
• Indexes can be modified using Alter Index statement.
• Non-key columns can be added into Covered Index to improve performance.
• T-SQL Enhancements
o Common Table Expressions (CTE)
• Security Enhancements
o Execute As
• Tools
o SQLCMD – command tool to execute sql statements in batch mode.
o TableDiff – To compare data in two tables.
o Dta—Database engine Tuning Advisor – Tool to improve sql statements performance.
Tools introduced with SQL Server 2005
• SQL SERVER Configuration Manager
• SQL SERVER Surface Area Configuration
• SQL Server Management Studio