Sql server General T-SQL Interview questions

How to Delete duplicate Records:

here am showing the examples how to delete duplicate records in sqlserver.

First create the table with sample data

create table #test (id int identity,name varchar(100))

insert #test

select 'mals'

union all

select 'mals'

first method.using cte with row_number

;with cte as

(

select ID,name,ROW_NUMBER() over(PARTITION BY NAME ORDER BY ID) as row from #test

)

delete from CTE

where row>1

select * from #test

go

drop table #test

go

second method.using group by clause

create table #test (id int identity,name varchar(100))

insert #test

select 'mals'

union all

select 'mals'
go

delete from #test

where id in (select MAX(ID) from #test

group by name

having COUNT(*)>1)

select * from #test

go

drop table #test

Suppose there is no unique key  in table. using the below sample query you can delete the duplicate data.

create table test (name varchar(10))

insert Test

select 'malli'

union all

select 'malli'

union all

select 'ram'

union all

select 'ram'

;with cte as

(

  select row_number() over (partition by name order by name) as row,name from test

)

delete from cte where row>1

select * from Test

drop table Test

2) How to get top n records from a give table

using ranking function.

;with cte

as

(

select id,dense_rank() over ( order by id desc) as row

from systables

)

select id from cte where row between 6 and 10   ---top 6-10 records

go

using top 

declare @date datetime;

set @date=getdate()

select top 5 id

from sysobjects

where id in (select top 10 id

from sysobjects order by id desc

)

order by id asc    ---top 6-10 records

3) Suppose i have 3 tables with same data.First table have clustered index ,second table have non clustered
index and third table have no index. suppose i issue a command

select * from tablename

which will execute faster.

declare @time datetime2;

set @time=GETDATE()

select * from NOIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

set @time=GETDATE()

select * from CLIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

set @time=GETDATE()

select * from NONCLIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

go

4)  Below is the Employee table which has the four feilds that contain the data. Assume ManagerID is Dept ID. You need to get second highest salary for each department.

Table

-----------

Expected output 

-----------------

Using the Ranking functions we can get the desired output. 

1) Row_number provides the 3 entries even if tiep-up with the with same salary

2) Dense_rank and rank will give the match and returns mutiple rows.

WITH CTE AS

(

select ID,name,AnnualSalary ,ManagerID,

row_number()  over( partition by ManagerID order by AnnualSalary desc) as rnk

from Table1

)

select ID,name,AnnualSalary ,ManagerID from CTE

where rnk = 2;

WITH CTE AS

(

select ID,name,AnnualSalary ,ManagerID,

dense_rank()  over( partition by ManagerID order by AnnualSalary desc) as rnk

from Table1

)

select ID,name,AnnualSalary ,ManagerID from CTE

where rnk = 2;

create table Table1 (ID int, Name varchar(50), AnnualSalary bigint, ManagerID int)

insert into Table1 values (1,'Lisa',150000,null),(2,'Dan',110000,1),(3,'Oliver',180000,1),(4,'Dave',100000,1);

insert into Table1 values (5,'Steve',200000,null),(6,'Alice',2050000,5),(7,'Damian',100000,5),(8,'Avery',210000,5),

(9,'Mark',210000,5); 

select * from Table1

5) Returns pages from a cook book alternatevly 

CREATE TABLE [dbo].[recipes](

[page_no] [int] NULL,

[title] [varchar](50) NULL

) ON [PRIMARY]

GO

DECLARE @table TABLE (

  pageno int,

  title varchar(30)

)

INSERT INTO @table

  VALUES (1, 'pancake')

  , (2, 'pizza')

  , (3, 'pasta')

  , (5, 'cookie')

;

WITH cte_pages

AS (  -- generate page numbers

SELECT

  0 n,

  MAX(pageno) maxpgno

FROM @table

UNION ALL

SELECT

  n + 1 n,

  maxpgno

FROM cte_pages

WHERE n <= maxpgno),

cte_left

AS (   --- even

SELECT

  n,

  ROW_NUMBER() OVER (ORDER BY n) rn

FROM cte_pages

WHERE n % 2 = 0),

cte_right

AS (   --- odd

SELECT

  n,

  ROW_NUMBER() OVER (ORDER BY n) rn

FROM cte_pages

WHERE n % 2 <> 0)

SELECT

  tl.title left_title,

  tr.title right_title   

FROM cte_left l

INNER JOIN cte_right r

  ON l.rn = r.rn

LEFT OUTER JOIN @table tl

  ON tl.pageno = l.n

LEFT OUTER JOIN @table tr

  ON tr.pageno = r.n 

6)  Last person to get into lift with max weight of 1000

SELECT top 1 name

FROM liftline t1

WHERE (SELECT SUM(t2.weight) FROM liftline t2 WHERE t2.turn <= t1.turn) <= 1000

ORDER BY t1.turn DESC

transactions

set transaction isolation level repeatable read

begin tran

select * from Relationship where link_id=64099

commit tran

set transaction isolation level repeatable read

begin tran

update Relationship set start_date='2012-12-06' from Relationship where link_id=64099

commit tran

set transaction isolation level read committed

begin tran

select * from Relationship where link_id=64099

commit tran

set transaction isolation level read committed

begin tran

update Relationship set start_date='2012-12-06' from Relationship where link_id=64099

commit tran

NOLOCK VS ReadPast

select * from Relationship(nolock) where link_id=64099

select * from Relationship(readpast) where link_id=64099

Use schema-binding in functions for improve query performance

Execution context in SQL Server

When executing the modules we can specify the EXECUTE AS  clause for executing the module with the execution context.

There are different types of executing context 

EXECUTE AS CALLER|OWNER|USER

SQL Server Permission Query Details

The below queries is useful for getting the server,database and schema level permissions.

User must have securityadmin permission in respective sqlserver in order to view all permissions/

-- Server level admin acces (login level permissions)

-- Author : Malleswara Reddy Mamidi

select distinct sp.name,

sp.type_desc as LoginType,

sp.default_database_name,

ISNULL(sp1.name,'public') as ServerRole,

class_desc as PrincipalClassification,

Permission_name as ServerPermission,

state_desc AS PermissionState

from sys.server_principals sp

left join sys.server_role_members rm on sp.principal_id = rm.member_principal_id

left join sys.server_principals sp1 on sp1.principal_id = rm.role_principal_id

left join sys.server_permissions pm on pm.grantee_principal_id = sp.principal_id

order by sp.name

-- database level admin access

select distinct sp.name as DatabaseUser,

sp.type_desc as UserType,

isnull(sp.default_schema_name,'dbo') as DefaultSchema,

ISNULL(sp1.name,'public') as DatabaseRole,

class_desc as PrincipalClassification,

Permission_name as DatabasePermission,

state_desc AS PermissionState

from sys.database_principals sp

left join sys.database_role_members rm on sp.principal_id = rm.member_principal_id

left join sys.database_principals sp1 on sp1.principal_id = rm.role_principal_id

left join sys.database_permissions pm on pm.grantee_principal_id = sp.principal_id

 order by sp.name

-- Schema level permissions

SELECT pr.principal_id, pr.name, pr.type_desc,  pe.state_desc,

    pe.permission_name, s.name + '.' + o.name AS ObjectName

FROM sys.database_principals AS pr

JOIN sys.database_permissions AS pe

    ON pe.grantee_principal_id = pr.principal_id

JOIN sys.objects AS o

    ON pe.major_id = o.object_id

JOIN sys.schemas AS s

    ON o.schema_id = s.schema_id

            --where --pr.name = 'Public'

            order by pr.name