Archive

Archive for the ‘DB Concepts’ Category

SQL Server’s weird behavior in error handling with transactions

December 6, 2011 2 comments

In my previous post I mentioned that SQL statements inside the BEGIN & COMMIT TRANSACTION block do not auto rollback the batch if an error occurs, and SET XACT_ABORT ON is required for auto rollbacking the transaction batch in such cases.

As I mentioned earlier in my previous post that by default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

But there is an exception for some errors, like for error level 16, example: Datatype Conversion error, the transaction batch is automatically rolled back.

–> Let’s check this with an example below:

-- Create a table and insert test some records
CREATE TABLE X (NBR INT PRIMARY KEY)

INSERT INTO X VALUES(1)
INSERT INTO X VALUES(2)

-- Tran t1 : PK violation error
BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SELECT * FROM X
-- Results 4 records inserted above, the entire transaction batch was not rolled back, but only the 2nd INSERT statement.

-- To automatically rollback above transaction batch we will set XACT_ABORT to ON:
SET XACT_ABORT ON

BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SET XACT_ABORT OFF

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

--// But for some types of errors setting the XACT_ABORT to ON is not required. Like for Datatype Conversion errors (level = 16). Let's check this:

-- Tran t2: Datatype conversion error
BEGIN TRANSACTION T2
	INSERT INTO X VALUES(5)
	INSERT INTO X VALUES('a') -- Msg 245, Level 16, State 1, Line 19, Conversion failed...
	INSERT INTO X VALUES(6)
COMMIT TRANSACTION T2

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

-- Final Cleanup
DROP TABLE X

Erland Sommarskog in his tech-site lists and distinguish such error types: http://www.sommarskog.se/error-handling-I.html#statementbatch

I also discussed this topic in MSDN TSQL forum & here is the link.

SQL Basics – Working with VIEWs in SQL Server

December 2, 2011 1 comment

 
Views in SQL Server and other RDBMSs are simply Virtual Tables or Stored Queries. Views are like tables, contains rows and columns, but do not store any data within. Instead they use the Query that is defined to create the view to show data from one or more tables.
 

–> A view can be used for:

1. Simplifying a complex query, give it a simple name, and use it like a table.

2. Providing security by restricting the table access and showing only selected Columns and Rows via Views.

3. Providing compatibility with other client systems that cannot be changed.
 

–> Now let’s see how Views helps with above points:

Point #1: Consider Query below, it will be difficult and time consuming to write the same query every time. Also due to human error at times you may not be able to get same or desired results, because you can commit mistake or forget something while writing the same logic.

USE [AdventureWorks2014]
GO

SELECT 
	 P.BusinessEntityID AS EmpID
	,P.Title
	,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName
	,P.Suffix
	,E.BirthDate
	,CASE 
		WHEN E.Gender = 'M' THEN 'Male' 
		ELSE 'Female' 
	 END as Gender
	,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus
FROM Person.Person P
JOIN [HumanResources].[Employee] E
ON E.BusinessEntityID = P.BusinessEntityID

views-01

… here we have Joined 2 tables and selected only required columns. You will also notice that we have changed the column names (alias), and created calculated columns, like EmployeeName, Gender & MaritalStatus.

So rather than writing this query every time, its better to store this query as a View, like below:

CREATE VIEW dbo.vwPersonEmployee
AS
SELECT 
	 P.BusinessEntityID AS EmpID
	,P.Title
	,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName
	,P.Suffix
	,E.BirthDate
	,CASE 
		WHEN E.Gender = 'M' THEN 'Male' 
		ELSE 'Female' 
	 END as Gender
	,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus
FROM Person.Person P
JOIN [HumanResources].[Employee] E
ON E.BusinessEntityID = P.BusinessEntityID
GO

… and simply execute the view instead of the query now onwards, like:

SELECT * FROM dbo.vwPersonEmployee

 

Point #2: The above View uses 2 tables Person.Person & HumanResources.Employee. Now if you want a user to have restricted access to these 2 tables, but also want the user to query View to get desired and restricted data, then you can GRANT access only to the View, like:

CREATE USER userView WITHOUT LOGIN;

GRANT SELECT ON dbo.vwPersonEmployee TO userView;
GO

EXECUTE AS USER = 'userView';

SELECT * FROM dbo.vwPersonEmployee 	-- Displays View data

SELECT * FROM Person.Person				-- ERROR: The SELECT permission was denied on the object
SELECT * FROM HumanResources.Employee	-- ERROR: The SELECT permission was denied on the object

REVERT;
GO

DROP USER userView
GO

… here when the user executes the View he can see the data, but with only selected columns. But if he tries to use tables, he will get error.
 

Point #3: Now let’s say a client application which was getting data from an old table, let’s say dbo.Person, with following columns: PersonID, PersonFirstName, PersonLastName. Now on the new DB system the table is replaced by a new table Person.Person with different column names. This will make the system unusable and unstable.

But with the use of Views we can fill the gap, by creating a new View with name dbo.Person on top of Person.Person, and aliasing new columns with old column names, like:

CREATE VIEW dbo.Person
AS
SELECT
	 BusinessEntityID as PersonID
	,FirstName as PersonFirstName
	,LastName as PersonLastName
FROM Person.Person
GO

… so by this way the client application can talk to the new table by hitting the View instead of the Table.
 

–> Dropping/Deleting Views:

DROP VIEW dbo.vwPersonEmployee
DROP VIEW dbo.Person

 


What are Records, Pages, Extents, Heap in SQL Server?

October 12, 2011 1 comment

 
I found this very interesting series by Paul Randal on some basic terms of SQL Server Storage Engine at MSDN SQL Server blog. This is a 4 part series and provides information on records, pages, extents and heap structures.

I’ve seen many people asking questions related to these topics on MSDN & other SQL Server forums. And there are very few tech articles on internet and MS BOL regarding these. Paul has very breifly and in layman terms explained these topics.
 

I’d like to share the following topics for my future reference and for my blog viewers:

Part 1. What are Rows/Records?

Part 2. What are Pages?

Part 3. What are Extents?

Part 4. What is a Heap?
 

–> PAGE: A page in SQL Server is an 8 KB data storage area. There are 8 types of pages:

1. Data page
2. Index page
3. IAM (Index Allocation Map) page
4. GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map) page
5. PFS (Page Free Space) page
6. Text/Image page
7. Sort page
8. Differential Bitmap page
9. Bulk-Changed Map page
10. Boot page
11. FileHeader page


Categories: DB Concepts Tags: , , ,

DB Basics – Is SELECT a DML (Data Manipulation Language)?

July 26, 2011 1 comment

DML or Data Manipulation Langauge as the term suggest represents those SQL statements that manipulates the data in a database. Thus these langauges allows users to INSERT, UPDATE & DELETE the data in a particular database. Other than this the much debatable SELECT statement may or may not be considered as DML upon its usage.

A simple SELECT statement which fetches data from a table is a read-only language and cannot be called as DML.

But a modified version of SELECT i.e. ‘SELECT INTO’ can fall into the DML segment. The ‘SELECT INTO’ can be used to create a Table and insert records fetched from the SELECT statement.

Also, we can manipulate the data for reporting purpose while retriving by using the SELECT statement. The data in underlying tables is unchanged but on the frontend you get a view of modified data, like:

USE [AdventureWorks]
GO

SELECT ContactID, Title,
	FirstName, MiddleName, LastName,
	FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as [FullName] -- Name manipulated by joining 3 part names.
FROM Person.Contact

SELECT SalesOrderID, SalesOrderDetailID,
	OrderQty, UnitPrice,
	OrderQty * UnitPrice as [TotalPrice] -- Manipulated cost by calculating it for total items purchased.
FROM Sales.SalesOrderDetail

Thus SELECT also comes under DML and following is the list of all DMLs:
1. SELECT {COLUMN LIST} [INTO {TABLE_NAME}] [WHERE {WHERE condition}]
2. INSERT INTO {TABLE_NAME} VALUES (SET of Values)
3. UPDATE {TABLE_NAME} SET [WHERE {WHERE condition}]
4. DELETE FROM {TABLE_NAME} [WHERE {WHERE condition}]

DIRTY reads and PHANTOM reads – SQL Server

July 20, 2011 15 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.