Advertisements

Archive

Archive for the ‘Differences’ Category

New THROW statement in SQL Server 2012 (vs RAISERROR)

December 20, 2012 7 comments

Newly introduced THROW keyword in SQL server 2012 is an improvement over the existing RAISERROR() statement. Yes, it’s single ‘E’ in RAISERROR.

Both RAISERROR & THROW can be used in T-SQL code/script to raise and throw error within a TRY-CATCH block. Check my previous post for TRY-CATCH block, [link].

–> With RAISERROR developers had to use different ERROR_xxxx() system functions to get the error details to pass through the RAISERROR() statement, like:
– ERROR_NUMBER()
– ERROR_MESSAGE()
– ERROR_SEVERITY()
– ERROR_STATE()

let’s see an example:

-- Using RAISERROR()
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)
	,@ERR_SEV AS SMALLINT
	,@ERR_STA AS SMALLINT

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_SEV =ERROR_SEVERITY(),
		@ERR_STA = ERROR_STATE()
	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG

	RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA)  WITH NOWAIT
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 15
Error occurred while retrieving the data from database: Divide by zero error encountered.

The RAISERROR() can take first argument as message_id also instead of the message. But if you want to pass the message_id then it has to be in sys.messages

–> With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception.
Just using the THROW; statement will get the error details and raise it, as shown below:

-- Using THROW - 1
BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	THROW;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

As you see in the Output above, the error message thrown is the default one. But you can also add your customized message, we will see below.

IMP NOTE: THROW will show the exact line where the exception was occurred, here the line number is 2. But RAISERROR will show the line number where the RAISERROR statement was executed i.e. Line 15, but not the actual exception position.

Also passing the message_id won’t require it to be stored in sys.messages, let’s check this:

-- Using THROW - 2
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)      
	,@ERR_STA AS SMALLINT      

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_STA = ERROR_STATE()

	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG;

	THROW 50001, @ERR_MSG, @ERR_STA;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 14
Error occurred while retrieving the data from database: Divide by zero error encountered.

But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you’ve to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.

As per MSBOL following are the difference between RAISERROR & THROW:

RAISERROR statement

THROW statement

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The error_number parameter does not have to be defined in sys.messages.

The msg_str parameter can contain printf formatting styles.

The message parameter does not accept printf style formatting.

The severity parameter specifies the severity of the exception.

There is no severity parameter. The exception severity is always set to 16.

NOTE: As per MS BOL for exception handling in new development work THROW must be used instead of RAISERROR.

Advertisements

Reorganize Index vs Rebuild Index in SQL Server

July 25, 2012 Leave a comment

Well-designed indexes on tables/views improves the performance of queries run against a database by reducing disk I/O operations and consume fewer system resources. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements.

SQL Server Database Engine automatically maintains indexes whenever INSERT, UPDATE, or DELETE operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered/fragmented in the database. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

SQL Server has provided ways to reduce/remedy fragmentation by Reorganizing or Rebuilding an Index.

1. Reorganize Index: uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

2. Rebuild Index: drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

So, which approach to go with, Reorganize or Rebuild?

First of all we’ll check the fragmentation status of a particular table or an index, by using sys.dm_db_index_physical_stats function.

Here we will check the status of all indexes in [HumanResources].[Employee] table of [AdventureWorks2012] database.

select 
	x.name as Index_Name,
	s.database_id, s.object_id, s.index_id, s.index_type_desc, -- General info columns
	s.avg_fragmentation_in_percent, s.fragment_count, s.avg_fragment_size_in_pages -- stats we need
from sys.indexes x
cross apply sys.dm_db_index_physical_stats (
	DB_ID(N'AdventureWorks2012'), -- database_id
	x.object_id, -- object_id
	x.index_id, -- index_id
	NULL, -- partition_number
	NULL) as s -- mode
where s.object_id = object_id('HumanResources.Employee')

Output:

We will use the following criteria setup by Microsoft to detirmine the best method to correct the fragmentation:

avg_fragmentation_in_percent value | Corrective statement
> 5% and <= 30%			     ALTER INDEX REORGANIZE
> 30%				     ALTER INDEX REBUILD WITH (ONLINE = ON)*

The above results shows that the Indexes [AK_Employee_LoginID] and [AK_Employee_NationalIDNumber] requires Rebuild and rest of them are good.

–> TO REBUILD:

--// To Rebuild [AK_Employee_LoginID] Index, run the following query:

USE AdventureWorks2012;
GO

ALTER INDEX AK_Employee_LoginID
	ON HumanResources.Employee
REBUILD;
GO

--// To Rebuild All indexes, use following query:

USE AdventureWorks2012;
GO

ALTER INDEX ALL
	ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

–> TO REORGANIZE:

--// To Reorganize [AK_Employee_NationalIDNumber] Index, run the following query:

USE AdventureWorks2012;
GO

ALTER INDEX AK_Employee_NationalIDNumber
	ON HumanResources.Employee
REORGANIZE;
GO

--// To Reorganize All indexes on [HumanResources].[Employee], use following query:

USE AdventureWorks2012;
GO

ALTER INDEX ALL
	ON HumanResources.Employee
REORGANIZE;
GO

So, check the fragmentation status by using the DM function sys.dm_db_index_physical_stats and then decide to do either REORGANIZE or a REBUILD on an Index.

Difference between Decimal and Numeric datatypes

June 8, 2012 4 comments

Decimal vs Numeric: None… both are same!!!

This is a misconception among many developers that Decimal and Numeric are different data types. And lot of people do not agree to understand that they are same unless I redirect them to this MSDN BOL [link].

Few days back while designing a data model for one our support enhancement project this question was raised and following was the conversation:

Person A: Ok, this column is defined as Numeric, can’t we make it a Decimal?
Me: What difference will it make?
Person A: Decimal is more precise than Numeric (according to him).
Me: No, both are same.
Person B, C and some others: There is slight difference, Decimal holds more accurate precision values than Numeric.

To my surprise none of them were aware that both are same.

So, when I redirected them to MSDN decimal and numeric (Transact-SQL) and show them the line which says: “numeric is functionally equivalent to decimal.” they agreed.

So, not to confuse between Decimal & Numeric, they both are synonyms to each other.

Stored Procedures vs Functions (Difference between SP & UDF) – SQL Server

September 21, 2011 10 comments

Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.

On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.
 

Following are some common differences between an SP & a UDF:
 

–> Stored Procedures (SP):
– Can be used to read and modify data.
– To run an SP Execute or Exec is used, cannot be used with SELECT statement.
– Cannot JOIN a SP in a SELECT statement.
– Can use Table Variables as well as Temporary Tables inside an SP.
– Can create and use Dynamic SQL.
– Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
– Can use used with XML FOR clause.
– Can use a UDF inside a SP in SELECT statement.
– Cannot be used to create constraints while creating a table.
– Can execute all kinds of functions, be it deterministic or non-deterministic.
 

–> Functions (UDF):
– Can only read data, cannot modify the database.
– Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
– Can JOIN a UDF in a SELECT statement.
– Cannot use a Temporary Table, only Table Variables can be used.
– Cannot use a Dynamic SQL inside a UDF.
– Cannot use transactions inside a UDF.
– Cannot be used with XML FOR clause.
– Cannot execute an SP inside a UDF.
– Can be used to create Constraints while creating a table.
– Cannot execute some non-deterministic built-in functions, like GETDATE().
 

More about “User Defined Functions” (UDFs) [check here].
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


DIRTY reads and PHANTOM reads – SQL Server

July 20, 2011 14 comments

–> DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.

This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.

To prevent Dirty Reads, READ COMMITTED or SNAPSHOT isolation level should be used.
 

–> PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.

PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.