How many types of backup in sql server? Example.

Where to Backup : Shared Drive , Storage Server,Cloud, Tapes, Dat Drives ,Logical

1: Normal Backup (Whole Backup) Full Backup once in week or month
2: Incremental Backup  (After Changes)
3: Differential Backup
4: Copy Backup (Full Backup)
5: Daily Backup (Creation or modification date of current date)

Files : Archive Bit (Off)
Change :  On after changes

Share:

Read More

How to delete duplicate rows using CTC in SQL? Example.

SELECT name ,COUNT(*) AS NumberOfTime FROM emp55 GROUP BY name HAVING COUNT(*)>1

WITH CTE AS 
( 
 SELECT name,salary,ROW_NUMBER() OVER (PARTITION BY name ORDER BY salary) AS rn FROM Emp55
) 
DELETE FROM CTE WHERE rn >1 

Share:

Read More

What is pivot in sql server? Example.

SELECT [Year], Kul,Amit,Raj FROM 
(SELECT Name, [Year] , Sales FROM Emp44 )Tab1 
PIVOT 
( 
SUM(Sales) FOR Name IN (Kul,Amit,Raj)) AS Tab2 
ORDER BY [Tab2].[Year] 

Share:

Read More

How to get running total in sql server? Example.

create table #temp (id int identity,name varchar(50),department varchar(50),salary decimal,TotalRunningSalary decimal)
insert into #temp(name,department,salary) select e1.Name,e2.Department,e2.Salary from employee e1
inner join  EmployeeDetails e2 on e1.id=e2.did
select * from #temp

declare @total int
set @total = 0
update #temp set TotalRunningSalary = @total, @total = @total + salary
select * from #temp
order by id
drop table #temp

Share:

Read More

How to find second highest salary in each department in sql server? Example.

select e1.Name,e2.Department,e2.Salary from employee e1
inner join  EmployeeDetails e2 on e1.id=e2.did

select Name,Department,Salary from (
select e1.Name,e2.Department,e2.Salary, dense_rank() over(PARTITION by e2.Department order by e2.Salary desc) as Rank from employee e1
inner join  EmployeeDetails e2 on e1.id=e2.did
) T
where T.Rank=2;

Share:

Read More