Category Archives: SQL Server

Difference between Stored Procedure and View in SQL Server.

Stored Procedure

Stored Procedure can have multiple select statement
Stored Procedure can have parameters
Stored Procedure exists in database
Stored procedure return predefined result

View

View can have only one select statement
View does not have any parameter
View are virtual and does not exists in database
Views can return different result based on where condition.

What are Different Types of Joins?

Joins are used to get data from two or more tables. We can combine the tables data with the common fields between tables.

There are four types of Joins in SQL Server

Inner Join
Outer Join
- Left outer join (Left Join)
- Right outer join (Right Join)
- Full Outer Join (Full Join)
Cross Join
Self Join

Inner Join – It is used to get data from two or more tables when common column values matches between tables. If no column values matches between tables then no data will be return.
Example: Lets create an Employee table and Department table.

CREATE TABLE #Employee
(
  EmpId int,
  EmpName Varchar(100),
  DepartmentId int,
  ManagerId int
)

Insert into #Employee(EmpId,EmpName,DepartmentId,ManagerId)
Select 1,'Hitesh',4,Null Union
Select 2,'Priyanka',1,Null Union
Select 3,'Rahul',Null,Null Union
Select 4,'Dev',4,1 Union
Select 5,'Neha',4,1 Union
Select 6,'Vikas',Null,1 Union
Select 7,'Abhimanyu',3,Null 


CREATE TABLE #Department
(
  DepartmentId int,
  DepartName Varchar(100) 
)

Insert into #Department(DepartmentId,DepartName)
Select 1,'English' Union
Select 2,'Java' Union
Select 3,'PHP' Union
Select 4,'Dot Net' Union
Select 5,'SQL' Union
Select 6,'Physics'

Inner Join Query

Select E.EmpName,D.DepartName 
From #Employee E(Nolock) inner join #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Inner Join

Outer Join – There are three types of Outer Join in SQL Server.

Left outer join (Left Join) – Left outer join is also known as Left Join. It is used to get data from two or more tables. It will return all the rows from left table and matching rows from right table.

Select E.EmpName,D.DepartName 
From #Employee E(Nolock) left join #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Left Join

Right outer join (Right Join) – Right outer join is also known as Right Join. It is used to get data from two or more tables. It will return all the rows from right table and matching rows from left table.

Select E.EmpName,D.DepartName 
From #Employee E(Nolock) right join #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Right Join

Full Outer Join (Full Join) – Full outer join is also known as Full Join. It is used to get data from two or more tables. it returns all the rows from left table and right table. It combines the result of both left and right joins.

Select E.EmpName,D.DepartName 
From #Employee E(Nolock) FULL OUTER JOIN #Department D(Nolock) on E.DepartmentId=D.DepartmentId

Full Outer Join

Cross Join – It is used to get data from two or more tables. It will return Cartesian product of two tables.

Select E.EmpName,D.DepartName 
From #Employee E(Nolock) cross join #Department D(Nolock)

Cross Join

Self Join – It is used when a table references data in itself.

Select E.EmpName ,M.EmpName as ManagerName
From #Employee E(Nolock) , #Employee M(Nolock)  where E.ManagerId=M.EmpId

Self Join

What is ERROR_MESSAGE() in SQL Server?

ERROR_MESSAGE returns the error message cause in SQL Server statement. The Scope of ERROR_MESSAGE is within catch block of try catch. If called outside the catch block it will return NULL.

Example

BEGIN TRY   
    SELECT 'Hitesh'/5 as Value;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
 SELECT ERROR_MESSAGE() AS ErrorMessage;

Result
ErrorMessage
Conversion failed when converting the varchar value ‘Hitesh’ to data type int.

When called outside the catch block
ErrorMessage
NULL

What is charindex in sql?

Charindex is used to search an expression in another expression and return its starting position if found else will return 0.
Syntax :
CHARINDEX ( expression_ToFind ,expression_ToSearch)
Example :

 
DECLARE @expression_ToSearch varchar(100);
SELECT  @expression_ToSearch = 'I am proud to be an Indian.';
SELECT CHARINDEX('be', @expression_ToSearch);

Result : 15

What are GRANT, DENY and REVOKE in SQL Server.

These are related to security in SQL Server.

GRANT – It is used to give access permission for any object in SQL Server.

GRANT SELECT ON OBJECT::SCHEMA.TABLE_NAME TO ROLE_NAME;

All the users with this ROLE_NAME can only select data from TABLE_NAME Table.
DENY –It is used to block access permission for any object in SQL Server.

DENY SELECT ON OBJECT::SCHEMA.TABLE_NAME TO ROLE_NAME;

All the users with this ROLE_NAME will not be able to select data from TABLE_NAME Table.
REVOKE –It is used to undo a permission whether it is GRANT or DENY in SQL Server.

REVOKE SELECT ON OBJECT::SCHEMA.TABLE_NAME FROM ROLE_NAME;

All the users with this ROLE_NAME will GRANT permission if they were previously DENY. And those GRANT permission previously will DENY.

What is PATINDEX in SQL Server?

It returns the starting position of the first occurrence of a pattern in a specified String.
If the pattern is not found then it will return zero. It applies on all valid text and character data types.
Syntax : PATINDEX ( ‘%pattern%’ , String_Exp)
Example :

DECLARE @Pattern VARCHAR(50), @String_Exp VARCHAR(50)
Set @Pattern='%[0-9]%'
Set @String_Exp='Hi123t4e5s6h'
Select PATINDEX(@Pattern, @String_Exp)

Result : 3
In this example first occurrence of integer value is at position 3.

Explain WaitFor Delay And WaitFor Time in SQL Server?

WaitFor Delay : It is used for a specified duration relative to the current time.

SELECT GETDATE();
WAITFOR DELAY '00:00:05'; —- Wait 5 seconds
SELECT GETDATE();

WaitFor Time : It is used for actual time of day.

SELECT GETDATE();
WAITFOR TIME '20:00'; -- Wait for 8pm
SELECT GETDATE();

Len() vs Datalength() in SQL Server.

Len() function returns number of characters in the input string.
Datalength() function returns number of bytes in the input string.

Example :

Declare @Str Nvarchar(50)
Set @Str = 'Hitesh Kumar' 
Select Len(@Str) As [Len]
Select DATALENGTH(@Str) As [DataLength]

Output :
12 and 24

What are @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(‘table_name’) in SQl Server?

@@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(‘tablename’) are used to get last inserted identity of record.

@@IDENTITY : It is used to get last inserted identity of record. It is limited to current Session but it is not limited to current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SCOPE_IDENTITY() : It is used to get last inserted identity of record. It is limited to your current Session and also limited to your current scope. it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
IDENT_CURRENT(‘table_name’) : It is used to get last inserted identity of record. It is not limited to your current Session and current scope. It is limited to your table. It returns the identity value generated for a specific table in any session and any scope.

What is TRY_CONVERT Function in SQL Server?

TRY_CONVERT Function was introduced with SQL Server 2012. This function is similar to previous CONVERT Function but this Function will return null if the value provided is not convertible.
Syntax: TRY_CONVERT(datatype,Value)
Example:

Select TRY_CONVERT(int,'100')

Result:
100

Select TRY_CONVERT(int,'Hitesh')

Result:
(null)

CodeSolution.org

Your Problems Our Solutions