Archive for October, 2013

CTP-2 released for SQL Server 2014 | and I’ve installed it !

October 16, 2013 1 comment

Much awaited Community Test Preview 2 (CTP-2) for SQL Server 2014 is released and you can Download it from [here].

Check out the Release Notes [here]. This lists some limitations, issues and workarounds for them.

–> As mentioned in my [previous post] for CTP-1:

– You cannot upgrade your existing installation of CTP-1 to CTP-2.

– and similar to CTP-1 restrictions you cannot install CTP-2 with pre-existing versions of SQL Server, SSDT and Visual Studio.

Sp, this should also be a clean install to be used only for learning and POCs, and should not be used on Production Environments. Installation is very simple and similar to CTP-1 and latest SQL Server previous versions.

–> What’s new with CTP-2:

1. Can create Range Indexes for Ordered Scans (along with Hash Indexes in CTP-1).

2. Configure the In-memory usage limit to provide performance and stability for the traditional disk-based workloads.

3. Memory Optimization Advisor wizard added to SSMS for converting disk-based Tables to In-memory (Hekaton) Tables, by identifying Incompatible Data Types, Identity Columns, Constraints, Partitioning, Replications, etc.

4. Similar to above a “Naive Compilation Advisor” wizard for converting Stored Procedures to Natively Compiled SPs, by identifying Incompatible SQL statements, like: SET Options, UDFs, CTE, UNION, DISTINCT, One-part names, IN Clause, Subquery, TVFs, GOTO, ERROR_NUMBER, INSERT EXEC, OBJECT_ID, CASE, SELECT INTO, @@rowcount, QUOTENAME, EXECUTE, PRINT, EXISTS, MERGE, etc.

5. and many more enhancements with Always On like: allowing to view XEvents in UTC time, triggering XEvents when replicas change synchronization state, and recording the last time and transaction LSN committed when a replica goes to resolving state, new wizard to greatly simplify adding a replica on Azure.

Enough for now, let me go back and work with CTP-2, wait for more updates !!!


Difference between NOLOCK and READPAST table hints

October 4, 2013 4 comments

AS per MS BOL:

– NOLOCK: Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. NOLOCK is equivalent to READUNCOMMITTED.

– READPAST: Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.

Thus, while using NOLOCK you get all rows back but there are chances to read Uncommitted (Dirty) data. And while using READPAST you get only Committed Data so there are chances you won’t get those records that are currently being processed and not committed.

Let’s do a simple test:

–> Open a Query Editor in SSMS and copy following code:

-- Creating a sample table with 100 records:
INTO dbo.Person
FROM [Person].[Person]

-- Initiate Transaction to verify the behavior of these hints:

UPDATE dbo.Person
SET MiddleName = NULL
WHERE BusinessEntityID >= 10 
AND BusinessEntityID < 20

–> Now open a second Query Editor in SSSM and copy following code:

-- NOLOCK: returns all 100 records
FROM dbo.Person (nolock)
-- this includes 10 records that are under update and not committed yet.

-- READPAST: returns only 90 records
FROM dbo.Person (readpast)
-- because other 10 are under update and are no committed yet in the 1st Query Editor:

–> Now go back to the 1st Query Editor window and run following query to Rollback the Transaction:

-- Issue a Rollback to rollback the Transaction:

-- Drop the Sample table:
DROP TABLE dbo.Person



– Using READPAST avoids locking contention when implementing a work queue that uses a SQL Server table.

– Using NOLOCK may lead to read uncommitted (dirty) data and/or may read a row more than once due to page-splitting.

Both of them avoids locking, but on the cost of incorrect/dirty data. So one should carefully use them depending on their business scenario.