How to handle erros in T-SQL code ?

From SQL Server 2005 introduced a TRY ... CATCH statements that you can use to provide error handling.

• A TRAY block must be followed immediately by a single CATCH block
• When an error occurs in a statement within a TRY block, control is passed to the CATCH block where the error can be processed. If no error occurs inside the TRY block, the CATCH  block is skipped.
• Within a CATCH  block, you can use the functions shown in this figure to return data about the error that caused the CATCH block to be executed.

Syntax:

BEGIN TRY
{sql_statement / statement block}
END TRY
BEGIN CATCH
{sql_statement / statement block}
END CATCH

How to test the existence of a database object ?

OBJECT_ID  is used to check the existence of the table and DB_ID is used to check the existence of the Database.

Syntax of the OBJECT_ID function

<pre class="brush:sql;">

OBJECT_ID(‘object/table’)

</pre> 


Syntax of the DB_ID function

<pre class="brush:sql;">

DB_ID(‘database’)

</pre> 

EXAMPLES:

• Code that tests whether a database exists before it deletes it

<pre class="brush:sql;">

IF DB_ID(‘Test_DB’) IS NOT NULL

DROP DATABASE Test_DB

CREATE DATABASE Test_DB

</pre>

• Code that tests for the existence of the talbe

<pre class="brush:sql;">

IF OBJECT_ID(‘Test_Table’) IS NOT NULL

DROP TABLE Test_Table

</pre>

• Another way to test the existence of a table

<pre class="brush:sql;">

IF EXISTS ( SELECT * FROM sys.tables where name = ‘Test_Table’)

DROP TABLE Test_Table

</pre>

• Code that tests for the existence of the temp table

<pre class="brush:sql;">

IF OBJECT_ID(‘tempdb..#temptable_name’) IS NOT NULL

DROP TABLE #temptable_name

</pre>

 

Types of Transact-SQL Table Objects

There are Five Types of Transact-SQL Table Objects

#	Type	Scope
1	Standard Table	Available within the system until explicitly deleted
2	Temporary Table	Available within the system while the current database session is open
3	Table Variable	Available within a script while the current batch is executing
4	Derived Table	Available within a statement while the current statement is executing
5	View	Available within the system until explicitly deleted

Note: 

• Derived Tables are select Statements in from clause, referred by an alias name.
• CTE (Common table expression) is a type of derived table.

Diffence between Table Variable and Temp Table

#.	Table Variable	Temp Table
1	It stored in primary memory(ie. in RAM)	It stored in system database(ie. In msdb)
2	It has batch scope only, ie. Till the current t-sql stmt to be execute	It has the scope till the query window close, In case of local temp table and has the scope till all the SSMS query windows close in case of global temp table
3	Table variables can’t use within select into clause	Temp tables can be used within select into clause
4	Table variables gives better performance than temp table, because they stores in primary memory (ie. In RAM)	Temp tables not gives better performance than table variables, because it stores in hard disk
5	We can’t explicitly drop the table variables	We can explicitly drop the temp tables

Difference between UNION and UNION ALL ?

UNION and UNION ALL both are used to combine the results sets into one single results set, But following are the difference between the both operators.

#.	UNION	UNION ALL
1	UNION removes duplicate records from the two or more results sets	UNION ALL doen’t remove duplicate records from the two or more results sets
2	UNION  Sorts the data from the results sets	UNION ALL doesn’t Sorts the data from the results sets
3	By compare with union all, union is much less performance. Because of it must scan the result for duplicates	UNION ALL performance is more comparing with UNION

SET Operators in T-SQL

Set Operators :

It is used to combine multiple results sets into one single result set.

There are 3 set operators in T-SQL
1) UNION/UNION ALL
2) EXCEPT
3) INTERSECT

UNION/UNION ALL :
----------------------------
It is used to combine two or more result sets into one single result set by with or without duplicates
(ie, UNION doesn't allow null values, but UNION ALL allows null values)

Example:
<<ResultSet>>
UNION or UNION ALL
<<ResultSet>>


 EXCEPT:
------------
It takes the data from one result set where there is no matching in another result set.

Example:
<<ResultSet>>
EXCEPT
<<ResultSet>>

 INTERSECT:
----------------
It takes the data from the result sets which are in common

Example:
<<ResultSet>>
INTERSECT
<<ResultSet>>

How to change the Identity Column value ?

Before Change the Identity value(ProductID column) from the Product Table

 

 

-- To set the new identity value on the Product Table

DBCC CHECKIDENT ('Product', RESEED, 10)

Execution Results as follows

Checking identity information: current identity value '10', current column value '10'.
DBCC execution completed.

To insert a new record in the product table, to test the next identity value

insert into Product values ('Mat',200)

After Change the Identity value