Archive

Archive for December, 2011

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

 


XACT_ABORT with TRANSACTIONS in SQL Server

December 1, 2011 2 comments

SQL Server by default does not ROLLBACK a Transaction if there is error anywhere in the code inside a BEGIN TRANSACTION & COMMIT TRANSACTION block. It only skips the statement that causes the error and moves ahead.

To allow a transaction to complete entirely or fail entirely you can use the TRY-CATCH feature with ROLLBACK statement or the SET XACT_ABORT statement.

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.

–> Here let’s check how we can use XACT_ABORT with an example:


-- Create a test tabel and insert some records:
CREATE TABLE Tab (x INT NOT NULL PRIMARY KEY)

INSERT INTO Tab VALUES (1)
INSERT INTO Tab VALUES (2)
INSERT INTO Tab VALUES (3)
GO

-- =================================
-- Check with XACT_ABORT OFF option:
-- =================================
SET XACT_ABORT OFF; -- By default it is OFF

BEGIN TRANSACTION
	INSERT INTO Tab VALUES (4)
	INSERT INTO Tab VALUES (4) -- Statement fails but transaction is committed.
	INSERT INTO Tab VALUES (5)
COMMIT TRANSACTION
GO
/*
-- On message pane:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__Tab__3BD019E56991A7CB'. Cannot insert duplicate key in object 'dbo.Tab'. The duplicate key value is (4).
The statement has been terminated.

(1 row(s) affected)
*/

SELECT * FROM Tab
-- List records: 1,2,3,4,5
GO

-- ================================
-- Check with XACT_ABORT ON option:
-- ================================
SET XACT_ABORT ON

BEGIN TRANSACTION
	INSERT INTO Tab VALUES (6) -- Inserts 6, but the transaction is rollbacked in next step.
	INSERT INTO Tab VALUES (6) -- Statement fails and rollbacks the entire transaction.
	INSERT INTO Tab VALUES (7) -- Statement skipped as transaction is rollbacked at previous statement.
COMMIT TRANSACTION

SET XACT_ABORT OFF
GO
/*
-- On message pane:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__Tab__3BD019E56991A7CB'. Cannot insert duplicate key in object 'dbo.Tab'. The duplicate key value is (6).
*/

SELECT * FROM Tab
-- Still list records: 1,2,3,4,5
GO

-- Final cleanup
DROP TABLE Tab
GO

We can also use TRY-CATCH constructs that helps in handling transaction and can be used instead of XACT_ABORT statement. Check here how we can use them.