Category Archives: SQL

SET vs SELECT

SET and SELECT both key words are used to Assign Variables in SQL Server.

SET and SELECT both specifies the columns to be changed and the new values for the columns.
The values in the specified columns are updated with the values specified in the SET and SELECT in all rows that match the WHERE clause search condition.
If no WHERE clause is specified, all rows are updated.

There are some difference based on the Performance, Process like Follows :

1. SET is the ANSI standard for variable assignment, SELECT is not. 

2. SELECT can be used to assign values to more than one variable at a time, Whereas SET allows to assign data to only one variable at a time.

Example :

/* Declaring variables */
DECLARE @Var1 AS int, @Var2 AS int

/* The same can be done using SET, but two SET statements are needed */
SET @Var1 = 1
SET @Var2 = 2  

/* Initializing two variables at once */
SELECT @Var1 = 1, @Var2 = 2

But use SET instead SELECT, for variable initialization, It will throw the following error

SET @Var1 = 1, @Var2 = 2  

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘,’.

3. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all .so the variable will not be changed from it’s previous value.  

Example :

Run it in master Database in SQL Server.

/* Returns NULL */
DECLARE @Title varchar(80)
–SET @Title = ‘Not Found’

SET @Title =
(
SELECT error
FROM SysMessages
WHERE Description = ‘Invalid Description’
)

SELECT @Title
GO

/* Returns the string literal ‘Not Found’ */
DECLARE @Title varchar(80)
SET @Title = ‘Not Found’

SELECT @Title = error
FROM SysMessages
WHERE Description = ‘Invalid Description’

SELECT @Title
GO  

4. Let using a query needs to populate a variable and the Query returns more than one value.
SET will fail with an error in this scenario.
But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

As a result, bugs in your the could go unnoticed with SELECT, and this type of bugs is hard to track down too.

Example :

/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Table (i int, j varchar(10))
INSERT INTO #Table (i, j) VALUES (1, ‘Sunday’)
INSERT INTO #Table (i, j) VALUES (1, ‘Monday’)
GO

/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Table WHERE i = 1
SELECT @j
GO

/* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Table WHERE i = 1)
SELECT @j

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Based on the above results, when using a query to populate variables, we should always use SET.
If you want to be sure that only one row is returned then only use SELECT, as shown below:

DECLARE @j varchar(10)
SELECT @j = (SELECT j FROM #Table WHERE i = 1)
SELECT @j

5. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables.
In this scenario, using a SELECT is at least twice as fast, compared to SET.

Leave a comment

Filed under SQL

Temporary Tables VS Table Variables

Temporary Tables VS Table Variables

Temporary tables are like regular tables but stored in the tempdb database. These tables get dropped after they have been used. Temporary tables are of two types,

Local Temporary table – defined using a pound sign (#) and accessible only within the session that created it

Global Temporary table – defined using double pound signs (##) and visible to all users.

Table Variables is a data type that looks similar to a table but has a tighter lifetime scope (i.e within a function, stored procedure or batch that it is declared in) and should be used with small datasets.

Temporary Tables Table Variables
Created and stored in tempdb database Created in Memory (although it can write to tempdb)
They are bound to transactions i.e transaction logs are recorded. They are not bound to any transactions. So no effect of transaction rollbacks
Stored procedure containing temporary tables cannot be pre-compiled Stored procedures containing table variables can be pre-compiled
You can drop a Temporary Table You cannot manually drop a table variable
You can create indexes on them You cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)

Leave a comment

Filed under SQL

Try Catch in SQL

–After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.
–A TRY block must be followed immediately by a CATCH block.
–TRY…CATCH constructs can be nested.
–Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block.
–Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.

Error Functions
TRY…CATCH uses the following error functions to capture error information:
ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

–The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed in the CATCH block.

CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO

BEGIN TRY
— Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
— Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;

Leave a comment

Filed under SQL