SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE  ‘%searchstring%’

Char:

1. It is used for fixed length.
2. Char is not memory efficient I’d data in column varying in size.

Varchar:

1. Takes 1 byte per char.
2. VARCHAR is used for Variable Length Size Variable.
3. It is memory efficient.
4. It support non-unicode data.

Nvarchar:

1. Takes 2 byte per char.
2. NVARCHAR is used for Variable Length Size Variable.
3. A nvarchar column can store any Unicode data

This is one of common interview question,well  we can find our answer using below Methods…

Method 1 :  Using cte as below supoose we need 3rd highest salary

WITH CTE AS
(
SELECT Salary,
RNo = DENSE_RANK() OVER (ORDER BY Salary DESC)
FROM dbo.Employee
)
SELECT Salary
FROM CTE WHERE RNo = 3

Method 2 : Using SubQuery  finfing nth salary here (n-1 will be 2)

SELECT distinct Salary FROM Empoyee e1 where 
(n-1) = (SELECT count(distinct salary) from Employee e2 
WHERE e1.salary <= e2.salary);

Method 3 : 

 SELECT TOP 1 Salary FROM 
 (SELECT DISTINCT TOP 3 Salary FROM Employee ORDER BY Salary DESC )
 AS temp ORDER BY Salary

In most of the interviews this question is asked,we have many ways to find the nth highest salary.

Suppose we have below data. And finding 3rd highest salary

Salary                                                           90000                                                     80000                                                     60000                                                     60000

Method 1 :

Select Top 1 salary from
(
Select distinct top 3 salary from employee
order by salary desc
) Result
order by salary

Output : 60000

Method 2 : 

With result as
(
Select salary,dense_rank() over (order by salary desc) drank from employee
)
Select salary from result
Where result.drank=3
Output : 6000

Method 3 : 

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary
)

ALTER TABLE TABLENAME
ADD ColumnName int NOT NULL DEFAULT(1)
GO

Magic table hold the recently inserted values in the case of insert and to hold recently deleted values in the case of delete

Use: Often used in Trigger

e.

INSERT INTO emp

SELECT i.id, i.names

FROM INSERTED i   // Magic Table

Case expression in SQL Server allows for values to be replaced in the result set based on comparison values.

e.g.

SELECT ID, Title= CASE Gender WHEN ‘M’ THEN ‘Mr’ WHEN ‘F’ THEN ‘Mrs’ END FROM EMPLOYEE.

COALESCE() function will return first non null value passed in it.

e.g.

Select COALESCE(NULL,NULL,’HERRY’,’THOMAS’)

OUTPUT : HERRY

 

Scenerio : we have coma sperated data and want it in tabular form

Input: @strinput Varchar=’1,2,3,4′

Output :

Solution

Create a function as below

CREATE FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT

SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) – 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex – 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

RETURN
END

Now use below statement to get desired output –

Select * from [dbo].[SplitString](‘1,2,3′,’,’)

 

Convert(varchar(10),createdon,103)=’29/07/2016′
AND DATEPART(hh,createdon) > =0 and DATEPART(hh,createdon) < =16

0 == 12 Midnight
16 == 4 PM