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 Continue Reading…

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 Result ErrorMessage Conversion failed when converting the varchar value ‘Hitesh’ to data type int. When called outside the catch block ErrorMessage NULL

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. 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. All the users with this ROLE_NAME Continue Reading…

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 : Result : 3 In this example first occurrence of Continue Reading…

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, Continue Reading…

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: Result: 100 Result: (null)