ROWVERSION Walkthrough

ROWVERSION is used for versioning rows (did the name not give it away?)

Equivalent to VARBINARY(8), here is a demonstration of how it works (using the bestselling albums of 1983!)
First we create a table with a ROWVERSION column and populate it.

CREATE TABLE TopAlbums
( ID INT IDENTITY(1,1)
 ,Artist NVARCHAR(50)
 ,Title NVARCHAR(50)
 ,Version ROWVERSION)

GO

INSERT INTO TopAlbums (Artist,Title) VALUES ('Michael Jackson','Thriller')
INSERT INTO TopAlbums (Artist,Title) VALUES ('David Bowie','Let''s Dance')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Culture Club','Colour By Numbers')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Paul Young','No Parlez')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Spandau Ballet','1')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Wham!','Fantastic')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Men At Work','Business As Usual')
INSERT INTO TopAlbums (Artist,Title) VALUES ('The Police','Synchronicity')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Genesis','Genesis')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Eurythmics','Sweet Dreams')

GO

Here we can see the value of the Version column that ROWVERSION has created.

SELECT * FROM TopAlbums WHERE ID = 10

10 Eurythmics Sweet Dreams 0x00000000000007F8 

Now we give album 10, it's full title -

UPDATE TopAlbums  
SET Title = Title + ' (Are Made Of This)'
WHERE ID = 10

and we can see that the amendment has updated the Version column.

SELECT * FROM TopAlbums WHERE ID = 10

10 Eurythmics Sweet Dreams (Are Made Of This) 0x00000000000007F9 

Practical uses of this are data warehousing systems, i.e. only reloading data that has changed.

Policy Based Management Basics

Introduced in SQL 2008 , PBM allows you to standardise your SQL servers.

PBM is found under Management \ Policy Management in SQL Management Studio,
PBM relies on Facets (which in turn have properties), Conditions & Policies.

FacetsThese are the area of SQL we wish to control; e.g. 'Log File'
(There are lots of these, even more in SQL 2012).

PropertiesThe 'Log File' Facet has 15 properties, 'Growth' being a potentially important one.

ConditionsThe condition compares the Facet and it's property. For example we might want Log file Growth to always be set to 10%.

PoliciesThe policy itself defines the target servers and the evaluation modes.

Evaluation Modes are

• On Demand
• On Schedule
• On Change Prevent
• On Change Log Only

So to sum up, to implement a policy -

1. Create condition based on a facet

2. Create a policy

1. Based on the named condition
2. Set the evaluation mode

3. Enable the Policy


Simple Talk : Introduction to Policy Based Management

Revision : I/O Affinity Mask

I/O Affinity is a server option typically for datawarehouse implementations.
It helps when a system is reading & writing large amounts of data.

Solace : Affinity & Affinity I/O

Minimal Configuration & Single User Modes

In the event of a server failure, you might need to start SQL Server in either the Minimal Configuration or Single User Mode.

They both

• Only allow 1 user to connect
• Disable the CHECKPOINT process

Minimal Configuration Mode also prevents startup procedures from being run however.

Links:

Starting SQL Server in Single-User Mode (Startup option -m)
Starting SQL Server with Minimal Configuration (Startup option -f)

Revision : Backups

Recovery Models

FULL Recovery Models

• Supports FULL , DIFF & LOG backups
• The only mode that supports PAGE restores - Page Restore requires FULL recovery model

SIMPLE Recovery Models

• Supports FULL & DIFF backups

Backup Options

COPY_ONLY Use this to take a copy of a production system for a development environment.
It won't mess up the backup chain.

CONTINUE_AFTER_ERROR - Does what it says!

BACKUP DATABASE database_name TO DISK='c:\database_name.BAK'
WITH CHECKSUM, CONTINUE_AFTER_ERROR;

It can also be used on a restore (last resort)

RESTORE DATABASE database_name FROM backup_device
WITH CONTINUE_AFTER_ERROR;

SQL 2012 : Enabling Filestream

Filestream launched in SQL 2012 and introduces the concept of a FileTable.
This is a directory structure (folder) viewable from SQL.

Here's a quick walkthrough to see Filestream in action.

1) Enable Filestream at the INSTANCE Level by -

Launching SQL Server configuration manager
Navigate to the properies of the instance service and enable via the Filestream tab
Chose the appropriate options -

• Enable FILESTREAM for Transact-SQL access
• Enable FILESTREAM for file I/O streaming access (Also provide a share name)
• Allow remote clients to have streaming access to FILESTREAM data.

Launch a query window in Management Studio and run

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

2) Add a Filestream filegroup at the database level 

ALTER DATABASE SandPit
ADD FILEGROUP fsTestFileGroup
CONTAINS FILESTREAM;
GO

3) Add a file to the filegroup 

ALTER DATABASE SandPit
ADD FILE
(
NAME='fsTestFile',
FILENAME='C:\fsTestFile'
)
TO FILEGROUP fsTestFileGroup;
GO

4) Enable Filestream on the database

ALTER DATABASE SandPit
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME ='fsTestFile')

5) Create a table 

CREATE TABLE fsTestTable AS FILETABLE
WITH
(
FILETABLE_DIRECTORY ='fsTestFile',
FILETABLE_COLLATE_FILENAME = database_default
);
GO

6) Test !!! 

SELECT * FROM fsTestTable;

Creating Views - WITH CHECK OPTION

Something I hadn't appreciated today, the WITH CHECK OPTION when creating views.

The WITH CHECK OPTION is implemented at the end of a view definition.

CREATE VIEW redCars AS
    (
        SELECT type, model, registration, colour
    FROM Vehicles
    WHERE type = 'Car'
    AND Colour = 'Red'
    )
    WITH CHECK OPTION

It ensures changes that would alter the returned rows cannot occur.

UPDATE redCars SET Registration = 'ABC123' will therefore succeed.
UPDATE redcars SET Colour = 'Blue' will not

Links :
DevX.com - The WITH CHECK OPTION option with views
Tony Rogerson - WITH CHECK on CREATE VIEW

Revision :Schemabinding

Schemabinding can be specified on VIEWS and UDFs (user defined functions).

For example ;

 
CREATE VIEW [dbo].[vw_myView] WITH SCHEMABINDING AS
SELECT columnlist
FROM dbo.table
GO

Creating an object with SCHEMABINDING prevents the underlying structure of the tables being reference from being changed.

It's advantage is the increased performance of objects that use it.

It's disadvantage is maintainability in that it complicates scripting when underlying objects need to change i.e. the schema bound ones must first be removed.

It is necessary in order to create INDEXED VIEWS in editions of SQL Server that support it.

Solace : Schemabinding & UDFs

TSQL : Processes being blocked

How to determine the Processes being blocked via TSQL

Old way : exec sp_who 80
New way (sql 2005+): SELECT * FROM sys.dm_exec_requests WHERE Session_id = 80

With sp_who the column blk holds the session id of the blocking process.

On the sys.dm_exec_requests view there is the blocking_session_id column.


To stop a blocking process, use KILL spid e.g. KILL 71

Other uses of sys.dm_exec_requests -

Solace : Long Running Queries
Solace : % Percentage complete of running requests

MSDN : sys.dm_exec_requests
MSDN : sp_who

Table hint basics

SELECT columnlist FROM myTable WITH (TABLOCK);

Generally avoid table hints and let SQL Server manage locking.
Exact behaviour depends on the isolation level.

• TABLOCK - Shared table lock
• TABLOCKX - Exclusive table lock
• NOLOCK - Like the READUNCOMMITTED isolation level.
• UPDLOCK - Use update locks
• XLOCK - Take exclusive locks

Full list at MSDN

Trace Flag 1222 - DBCC TRACEON (1222, -1)

Trace Flag 1222 allows you to diagnose deadlocks.

DBCC TRACEON 1222 - (from MSDN)

"Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema "

Setting as a server startup option is done via the SQL Server Configuration Manager.
In SQL 2008 they are done from the Advanced tab of the Service properties as shown here.
From SQL 2012 they have their own tab ...

Turning it on & off via TSQL is as easy as these commands -

At session level - 

• DBCC TRACEON (1222,)
• DBCC TRACEOFF (1222)
  
  At server level - 

• DBCC TRACEON (1222, -1)
• DBCC TRACEOFF (1222, -1)

My development background means I like the idea of placing commands in a stored procedure that is executed on startup. Much nicer and more maintainable (for me) than messing service properties.
@MsSqlGirl Julie Koesmarno has done exactly that, here.

You can check which flags are enabled by using DBCC TRACESTATUS .

Links :

Finding SQL Server Deadlocks Using Trace Flag 1222
MSDN : trace Flags
Technet : DBCC TraceOn
Turn On Deadlock Trace Flag
How do I work with Trace Flags
SQL Server 2012 Startup Parameters

Enabling the Dedicated Admin Connection (DAC)

The Dedicated Admin Connection (DAC) or Remote Admin Connection allows one administrator connection to a server. A good thing to have enabled for an emergency.

To enable the DAC ;

1. Open port 1434 on Windows firewall / ISA / Any hardware firewalls present.
   
2. sp_configure 'remote admin connections', 1;
   GO
   
3. RECONFIGURE;
   GO

Links :

Enabling DAC via TSQL 
The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It
MSDN : remote admin connections Server Configuration Option

Database Mirroring Basics

Prerequisite : Source database must be in FULL RECOVERY model

Initialization Steps

1. Perform FULL backup of source database
2. Perform LOG backup of source database
3. Restore FULL backup with NORECOVERY on target server
4. Restore LOG backup with NORECOVERY on target server

Configure Mirroring

1. Right-Click Database, select Mirroring page.
2. Use Wizard to configure Principal & Mirror Servers
   * You may need to open port 5022 on the firewall (or whichever alternative you choose).
3. Choose between High Performance (asyncronous) or High Saftey (syncronous) modes
4. Start Mirroring

Adding a witness

1. Create an endpoint on the new server
2. Ensure the same Windows logins exist on each server & grant connect permissions
3. On the Principle Server, alter the pricinipal database to use the endpoint on the new server as a witness.

NB ;

• High Safety (syncronous) could slow a system if bandwidth is limited as commits need to occur on both servers.
• High Performance (asyncronous) allows the changes to be commited at the principle first.

Links :
Configure SQL Server Database Mirroring Using SSMS
Database Mirroring Tips

Debugging connectivity to a named instance

Start > Run > services.msc

Find the services "SQL Server Browser"

1. Ensure it is running
2. Ensure it starts automatically
3. Ensure the Firewall allows UDP 1434 through

Find the service "SQL Server (instance name)"

1. Ensure it is running
2. Ensure it starts automatically

SSIS - SQL Server Agent Permissions

Creating SSIS Jobs that can access network resources

1. Create domain user
2. Grant access to network share
3. Create Credential that references the domian user
4. Add Proxy that references credential
5. Assign the proxy to ssis execution system

sqlsolace : SSIS Credential & Proxy

SQL 2012 - Compatibility Level 110

In SQL Server 2012, the default compatibility level is 110
For a database to include 2012 functionality it needs to be on a SQL 2012 Server in a 2012 Compatibility Level of 110.

ALTER DATABASE MigratedDb
SET COMPATIBILITY_LEVEL = 110;
GO

Compatibility Levels

• 70 - SQL Server 7
• 80 - SQL Server 2000
• 90 - SQL Server 2005
• 100 - SQL Server 2008 / 2008 R2
• 110 - SQL Server 2012

MsDN Compatibility Modes

SQL Server : FOR XML Basics

FOR XML clause supports 4 modes

• RAW 
• AUTO 
• PATH 
• EXPLICIT 

RAW produces 1 line per record.

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML RAW;

AUTO on it's own lists columns as attributes

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO;

AUTO, ELEMENTS makes each column an element.

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO, ELEMENTS;
 

 

  1
  Ken
  J
  Sánchez


  2
  Terri
  Lee
  Duffy


  3
  Roberto
  Tamburello

XML PATH

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML PATH('Donkey');

 

  1
  Ken
  J
  Sánchez


  2
  Terri
  Lee
  Duffy


  3
  Roberto
  Tamburello

For XML EXPLICIT to work, the first 2 columns must be Tag and Parent.
Need to UNION ALL a header row to the data.

 
SELECT    1 AS Tag
         ,NULL AS Parent
         ,ProductCategoryID as [Category!1!ID]
         ,Name AS [Category!1!Name]
         ,NULL AS [Subcategory!2!Name] -- Placeholder for Subcategory.Name
FROM Production.ProductCategory 
 
UNION ALL 
 
SELECT   2 AS Tag
        ,1 AS Parent
        ,s.ProductSubcategoryID
        ,c.Name -- (1) Placeholder for Category.Name.  (2) Category.Name is needed for ORDER BY 
                ,s.Name 
FROM Production.ProductSubcategory s,
Production.ProductCategory c
WHERE c.ProductCategoryID = s.ProductCategoryID 
ORDER BY [Category!1!Name], [Subcategory!2!Name] 
FOR XML EXPLICIT 

Links 
Simple Talk : Using the FOR XML Clause

Malware Terminology

Following a 'scare' on a relatives laptop, I found this article which serves to explain security threats simply.

Malware and Viruses - What is the Difference

Terms explained :

• Virus
• Trojan
• Worm
• Key loggers
• Dialers
• Backdoor
• Exploit
• Spyware
• Adware
• Rootkit
• Rogues/Scareware
• Ransomware

Numeric Data type for 1 decimal point.

I wanted to store a number with 1 decimal point and found myself looking how to do it today.

DECIMAL allows you to specify first the number of digits in total (precision) and secondly how many will be after the decimal point.

DECIMAL(2,1) is what I need therefore, to store a single digit before & after the point
e.g. 1.0, 2.0, 2.1, 2.2 etc.

NB : DECIMAL and NUMERIC are interchangable in SQL Server, the latter being a hangover from Sybase days.

Links :
Number Data Types in T-SQL

SQL Server 2012 : New Date & Time Functions

SQL Server 2012 introduces several new Date & Time Functions. To create a SQL formatted date from component parts you would previously have to use some code similar to this.

DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY  INT

SET @YEAR = 2013
SET @MONTH = 1
SET @DAY = 17

-- Convert Version
SELECT CONVERT(DATE,CAST (@YEAR AS VARCHAR)+ RIGHT ('0'+ CAST (@MONTH AS VARCHAR),2) + RIGHT ('0'+ CAST (@DAY AS VARCHAR),2))

-- DateAdd version
SELECT DATEADD(MONTH,(@YEAR-1900)* 12 + @MONTH - 1,0) + (@DAY-1)

SQL Server 2012 provides a series of 'FROMPARTS' functions however.
The Syntax is like this ,

• DATEFROMPARTS (YEAR, MONTH, DAY)
  
• TIMEFROMPARTS (HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)
  
• DATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, MILLISECONDS)
  
• DATETIME2FROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)
  
• SMALLDATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE)
  
• DATETIMEOFFSETFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, HOUR_OFFSET, MINUTE_OFFSET, PRECISION)

In Addition an end of month function is available too.

• EOMONTH (START_DATE, [, MONTH_TO_ADD ])

Their use is demonstrated below.

DECLARE 
  @YEAR INT = 2013
, @MONTH INT = 01 
, @DAY INT = 09 
, @HOUR INT = 11
, @MINUTE INT = 59 
, @SECONDS INT = 59
, @MILLISECONDS INT = 0 
, @TODAY DATETIME = GETDATE()

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [DATEFROMPARTS]

SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [TIMEFROMPARTS]

SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [DATETIMEFROMPARTS]

SELECT DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [DATETIME2FROMPARTS]

SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [SMALLDATETIMEFROMPARTS]

SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE,@SECONDS,500, 5, 30, 3) AS [DATETIMEOFFSETFROMPARTS]

SELECT EOMONTH (@TODAY)  AS LASTDAYTHISMONTH
SELECT EOMONTH (@TODAY, -1) AS LASTDAYLASTMONTH
SELECT EOMONTH (@TODAY, 1)  AS LASTDAYNEXTMONTH

Links :
SQL Server 2012 New Date Functions

New Date,Logical Functions in SQL Server 2012

The ultimate guide to the datetime datatypes

SQL Server 2012 : New Windowing Functions

SQL Server 2012 Introduces 8 New Windowing Functions

 • FIRST_VALUE
 • LAST_VALUE
 • LAG
 • LEAD
 • PERCENT_RANK
 • CUME_DIST
 • PERCENTILE_CONT
 • PERCENTILE_DISC

SQL 2005 Ranking Functions

 • ROW_NUMBER
 • RANK
 • DENSE_RANK
 • NTILE

Traditional Aggregate Functions

 • SUM
 • COUNT
 • MIN
 • MAX
 • AVG

Windowing Functions work with OVER and the optional ORDER BY, PARTITION BY and BETWEEN functions.

SQL Server 2012 lets the aggregate functions work with OVER (ORDER BY ...). Previously only the Ranking Functions worked.


Links

TSQL : Using NTILE to divide a results set up.

SQL 2005 : RANK() vs DENSE_RANK()

Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

SQL Server 2012 Windowing Functions Part 1 of 2: Running and Sliding Aggregates

SQL Server 2012 Windowing Functions Part 2 of 2: New Analytic Functions

SQL Window Functions and You

Tip: OVER and PARTITION BY

SQL 2012 : LEAD & LAG (Transact-SQL)

LEAD and LAG are Windowing functions new to SQL Server 2012 returning the next and previous rows in a set respectively.

This eliminates the need for a self join (joining back to the same table)

Here are a couple of examples using AdventureWorks2012

Fistly, alphabetical currency names...

SELECT 
  [Name]
, LEAD(Name,1) OVER (ORDER BY Name ) LeadValue
, LAG(Name,1) OVER (ORDER BY Name ) LagValue
FROM [Sales].[Currency]

More usefully, LEAD and LAG used to compare the totals of shipped products with the next and previous days.

SELECT 
   ShipDate
  ,LAG(ValueShipped,1) OVER (ORDER BY ShipDate) AS DayBefore 
  ,ValueShipped
  ,LEAD(ValueShipped,1) OVER (ORDER BY ShipDate) AS DayAfter
FROM

 (
  SELECT ShipDate, SUM(TotalDue) AS ValueShipped
  FROM [Purchasing].[PurchaseOrderHeader]
  GROUP BY [ShipDate]
 ) ShippingTotals

ORDER BY [ShipDate]

Links :

Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 Functions - Lead and Lag

LAG (Transact-SQL)

LEAD  (Transact-SQL)

Trigger Happy - New SQL 2012 Functions

SQL 2012 introduces new functions to test for changes inside a trigger

UPDATE() tests for UPDATE or INSERT attempts on one column.

COLUMNS_UPDATED() tests for UPDATE or INSERT actions performed on multiple columns.

CREATE TRIGGER trgColumnChange
ON TABLE FOR UPDATE
AS
IF UPDATE(columnname)
BEGIN
-- Do Audit stuff here
END

COLUMNS_UPDATED (Transact-SQL)

Error Catching with @@ERROR and @@ROWCOUNT

Error Catching with @@ERROR and @@ROWCOUNT

@@ERROR  - Error Number
@@ROWCOUNT - Rows affected

Retrieve both values directly after a modification statement

The following code fails as the CostRate cannot be negative.
This is due to a Column Constraint on the location table (SQL 2012 Adventureworks)
It does however demonstrate catching the error number

DECLARE @myError INTEGER
DECLARE @myRowcount INTEGER
INSERT INTO [Production].[Location] ([Name],[CostRate],[Availability],[ModifiedDate])
VALUES('Tool ',-2,    0.00, GETDATE())
SELECT @myError = @@ERROR , @myRowcount = @@ROWCOUNT

SELECT 'Error Number ' + CAST(@myError AS VARCHAR(10))
SELECT 'Row Count ' + CAST(@myRowcount AS VARCHAR(10))

Using @@ROWCOUNT to test data existance

THROW (SQL 2012 Error Handling)

THROW (SQL 2012 Error Handling)

TRY - CATCH error testing was added back in SQL 2005.
THROW has finally arrived in SQL 2012
It should be used in place of RAISERROR

Logging errors the old way -
RAISERROR ('An error occurred querying the table.', 16, 1);

And the new way -
THROW 50000, 'An error occurred querying the table.', 1;

Disadvantages to using THROW

• Error messages are buffered, and don’t appear in real-time (No WITH NOWAIT equivilent)
• Doesn’t support token substitutions
• Always uses severity level 16

A full comparison is in  Lenni Lobel's article linked below.
Throwing Errors in SQL Server 2012
THROW (Transact-SQL)

UPDATE .WRITE (SQL 2012)

UPDATE .WRITE (SQL 2012)

Syntax: .WRITE ( expression, @Offset , @Length )

UPDATE table
SET column.WRITE('Inserted Text', 18, 3) 
WHERE id = 1

As per MS BOL the new .WRITE clause of the UPDATE DML statement enables partial or full updates and high performance appends to varchar(max), nvarchar(max) and varbinary(max) data type columns.

A traditional UPDATE statement is logged
Using the .WRITE clause is minimally logged (faster)

If @Offset is NULL, the update operation appends expression at the end of the column.


NB : only columns of varchar(max), nvarchar(max), or varbinary(max)  work with UPDATE.WRITE
 
Links :
UPDATE statement with new .WRITE Clause
0201-SQL Server-UPDATE statement-use .WRITE for partial updates

Transaction error control - XACT_ABORT & XACT_STATE (SQL 2012)

XACT_ABORT can be used to control the behaviour of SQL when an error in a transaction occurs.
(it has been around since SQL 2000)

SET XACT_ABORT ON
If a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

SET XACT_ABORT OFF
Only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.


XACT_STATE  is new to SQL 2012.

It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

XACT_STATE() = 1 - Committing the transaction will suceed
XACT_STATE() = -1 - Committing the transaction will fail
XACT_STATE() = 0  - No user transaction for request.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
        -- Do a DML operation - Insert, Delete or Update
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'Failed, Rollback occuring'
        ROLLBACK TRANSACTION;
    END;
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'Succeeded, Transaction committed'
        COMMIT TRANSACTION;  
    END;
END CATCH;
GO

Links :
XACT_STATE (Transact-SQL)
XACT_ABORT (Transact-SQL)

SQL Server PERSISTED Columns

SQL Server PERSISTED Columns (SQL 2005+)

Normal calculated column values are calculated each time a query references them.

Implementing a calculated column as PERSISTED physically stores results in the table.

PERSISTED Calculated Columns
+ Give Better Performance
+ You can create indexes on them
- Take up storage space

Calculated Column
ALTER TABLE invoice ADD invoicetotal AS itemtotal + itemtax



PERSISTED Calculated Column
ALTER TABLE invoice ADD invoicetotal AS itemtotal + itemtax PERSISTED



Links :

Using Computed Columns in SQL Server with Persisted Values
Top 10 Hidden Gems in SQL Server 2005
Persisted Columns Problem : Persisting a time difference

What's new in SQL Server 2012?

What's new in SQL Server 2012?

Rather than regurgitate a press release or add another list, here are some others who have already formed their opinions.

Top 12 Features of SQL Server 2012 (Joey D'Antoni)

My Top 5 SQL Server 2012 Features (Aaron Bertrand)

Top 10 New Features in SQL 2012 (John Weston)

Using SQL Server 2012 T-SQL New Features (Henry He)

T-SQL Improvements in SQL Server 2012 (Jonathan Allen)

Cool New SQL Server 2012 Metadata (Kalen Delaney)

Differences between SQL Server 2008 and SQL Server 2012 (Virendra Yaduvanshi)

SQL Server 2012 - Why it Rocks (Michael Corey)

Upgrading to SQL 2012 - Ten things you don't want to miss