June 21, 2015

SQL Server - Find Physical Location of Records

0 comments


SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;


sql-physloc

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)


The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

sql-sys.fun_PhysLocCracker

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin

    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)

    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
   
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end

The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.


 
  Feedback:

May 17, 2015

Get SQL Server Hardware Information using sys.dm_os_sys_info

0 comments


If you do not have physical access to your Database but want to get the hardware information of your SQL Server, use this query:

SELECT cpu_count AS [Logical CPUs], 
hyperthread_ratio AS [Logical vs Physical cores Ratio], 
physical_memory_kb/1024 AS [Physical Memory in MB], 
committed_kb/1024 AS [Committed Memory in MB],
committed_target_kb/1024 AS [Committed Target Memory in MB],
max_workers_count AS [Max Workers Count], 
sqlserver_start_time AS [SQL Server Start Time], 
virtual_machine_type_desc AS [Virtual Machine]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

The sys.dm_os_sys_info is available since SQL Server 2005 and contains information about server resources. For eg: you can find out the following: 
  • How many CPUs are there in the server 
  • Total logical processors 
  • Amount of physical memory available (physical_memory_kb - SQL Server 2012 onwards) 
  • Amount of Virtual memory available (virtual_memory_kb - SQL Server 2012 onwards) 
  • Last Date and time SQL Server service was started 
  • Virtual Machine Type description (not reliable) (virtual_machine_type_desc – SQL 2008 R2 onwards)
The cpu_count returns the number of logical CPUs and hyperthread_ratio returns ratio between physical and logical CPUs. I find the hyperthread_ratio to be confusing as it does not tell whether these are the actual hyper threaded cores or the physical cores. So I can never find out which processor is the server using. The physical_memory_kb tells the amount of RAM in my server and how much of it is committed using committed_kb. 

The sqlserver_start_time is useful to find since what date and time SQL Server has been running.

When I run the query on my machine, this is what I get:

system-info-sqlserver


 
  Feedback:

April 26, 2015

Types of JOIN in SQL Server - Inner, Self, Outer and Cross JOIN

0 comments


When we work with relational databases, the first thing we do is normalize the data. Different forms of normalization like First Normal Form (FNF), Second Normal Form (SNF) and Third Normal Form (TNF) etc divides the data into number of tables for avoiding redundancy. These tables are related with each other using Primary Key constraint and Foreign Key Constraint.

To fetch data from these related tables, we perform various types of queries. One way is to fetch the data using SQL JOINs. There are different types of joins supported by SQL Server as mentioned below -
  • INNER JOIN
  • SELF JOIN
  • OUTER JOIN [LEFT, RIGHT and FULL OUTER JOIN]
  • CROSS JOIN
To observe the tables and their relationship, we will use a demo database Northwind provided by Microsoft. We will consider below tables -
  • Customers
  • Employees
  • Categories
  • Products
  • Suppliers
  • Orders
  • Order Details
The database diagram looks similar to the following:
database-diagram

Now we will use the tables in this diagram to fetch the data using various scenarios. Let's first query individual tables. Open SQL Server Management Studio and click on New Query. Write the following queries and test them -

USE Northwind
GO

SELECT * FROM Customers
SELECT * FROM Employees
SELECT * FROM Orders
SELECT * FROM [Order Details]
SELECT * FROM Categories
SELECT * FROM Products
SELECT * FROM Suppliers

SQL INNER JOIN

Inner Join returns the matching rows from both the tables. For example, if we want to find out all the orders placed by all the customers, we can make use of an Inner Join. The query is as follows:


SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C INNER JOIN Orders O
ON C.CustomerID=O.CustomerID

Find out all the Orders processed by each Employee. The query is as shown below -

SELECT 
    E.FirstName,
    E.LastName,
    E.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Employees E INNER JOIN Orders O
ON E.EmployeeID=O.EmployeeID

You can apply a filter on the data which is fetched by the Join. For example, we will find out all the customers and their orders and filter the result on customer's city. The query is as shown below -

--NonEqui-Join [INNER JOIN with Where Condition]
SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C INNER JOIN Orders O
ON C.CustomerID=O.CustomerID
WHERE C.City='London'

The Inner Join can also be written as shown below -

SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C JOIN Orders O
ON C.CustomerID=O.CustomerID
--OR [Equi JOIN]
SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C,Orders O
WHERE C.CustomerID=O.CustomerID

SQL SELF JOIN

Joining a table to itself is called as Self Join. We will now see why do we require this type of join. Observe the following Employees table which belongs to the Northwind database -


employees-table

In the employees table, we have a primary key EmployeeID which is mapped to the employees table's ReportTo column. Assume that ReportsTo column acts as Manager ID column who tells which employees works under whom. In that situation, we will have to make use of Self Join. The query is as shown below -

-- Self JOIN - Joining a Table to itself.
SELECT 
    Mgr.FirstName + ' ' + Mgr.LastName AS 'Manager',
    Emp.FirstName + ' ' + Emp.LastName 
    as 'Employee Name'
    FROM Employees AS Emp INNER JOIN Employees AS Mgr
    ON Mgr.EmployeeID=Emp.ReportsTo

SQL Outer JOIN

An outer join is divided into three different parts.

LEFT OUTER JOIN - This join returns all the rows from the first [Left] table and matching rows from the second [Right] table. For example, If we want to see all the customers who have placed orders and who have not placed any orders. Let’s say we want to display orders which are placed by customers, we can make use of Left Outer Join. The query is as shown below -

--LEFT OUTER JOIN - 
SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C LEFT OUTER JOIN Orders O
ON C.CustomerID=O.CustomerID

RIGHT OUTER JOIN - This join returns only matching rows from the first [Left] table and all the rows from the second [Right] table. For example, If we want to see all the orders who have been placed by customers and the orders which do not belong to any customer (although unrealistic), but want to display only those customers who have are placed orders, we can make use of Right Outer Join. The query is as shown below -

--RIGHT OUTER JOIN - 
SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C RIGHT OUTER JOIN Orders O
ON C.CustomerID=O.CustomerID

FULL OUTER JOIN - This join returns matching as well as non matching rows from both tables [Left table as well as Right table]. For example, If we want to see all the customers as well as all the orders, we can make use of FULL Outer Join. The query is as shown below -

--FULL OUTER JOIN - 
SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM Customers C FULL OUTER JOIN Orders O
ON C.CustomerID=O.CustomerID

You can also perform multi-table join query. Let's look at an example of multi-table join query -

--JOINS with Multiple Tables
SELECT 
    C.ContactName,
    C.CompanyName,
    C.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate,
    OD.Quantity,
    OD.UnitPrice,P.ProductName
FROM Customers C INNER JOIN Orders O
ON C.CustomerID=O.CustomerID
INNER JOIN [Order Details] OD
ON O.OrderID=OD.OrderID INNER JOIN Products P
ON OD.ProductID=P.ProductID

Join Between Table and View

A Join can be performed in between Views and tables as well. We will first create a view and then perform a join in between a view and a table -

--JOIN Between Table and View
CREATE VIEW LondonCustomers
AS SELECT * FROM Customers WHERE City='London'
GO

SELECT 
    CV.ContactName,
    CV.CompanyName,
    CV.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM LondonCustomers CV INNER JOIN Orders O
ON CV.CustomerID=O.CustomerID

Join between Table Valued Function as well as Table

We can also perform a join in between Table Valued Function as well as table. Let's create a Table Valued Function and perform a join in between TVF and a table. The query is as shown below -

--JOIN Between Table and TVF [Table Valued Function]
CREATE FUNCTION BerlinCustomersFunction() RETURNS TABLE
AS
    RETURN (SELECT * FROM Customers WHERE City='Berlin')

GO
SELECT 
    TVF.ContactName,
    TVF.CompanyName,
    TVF.City,
    O.OrderID,
    O.OrderDate,
    O.RequiredDate 
FROM dbo.BerlinCustomersFunction() TVF INNER JOIN Orders O
ON TVF.CustomerID=O.CustomerID

CROSS JOIN

This join returns a Cartesian Product of two or more tables. It joins each row of the first table with all the rows of the second table. Let's take a look at the query as shown below -

--CROSS JOIN
SELECT 
    ContactName,
    CompanyName,
    City,
    OrderID,
    OrderDate,
    RequiredDate 
FROM Customers CROSS JOIN Orders

Summary

In this article, we have seen different types of joins you can perform in SQL Server. We have also seen how to write complex joins (multi-table join). We have seen INNER JOIN, SELF JOIN, OUTER JOIN (LEFT, RIGHT and FULL) and CROSS JOIN. Hope this article has helped in getting up to speed with SQL Joins.


 
  Feedback:

March 15, 2015

SQL Server Stored Procedure Recursion Limit

2 comments


In order to save you from a performance penalty, SQL Server has a hard limit set for recursion levels for Stored Procedures, Functions, Triggers  and Views. The limit is 32 levels and you can’t change it programmatically.

Here is a simple example demonstrating this limit:

CREATE PROCEDURE recurseProc (@cnt int)
AS
BEGIN
PRINT @cnt;
SET @cnt += 1;
EXEC recurseProc @cnt; 
END; 
GO

EXEC recurseProc 1;


stored-procedure-recursive


When you execute this code, you get an error after 32 recursions are over Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Note: You can work around this limitation by using a Common Table Expression. A CTE allows up to 100 levels of recursion.


 
  Feedback:

February 15, 2015

SQL Server 2014: How Old is your Build?

0 comments


The @@Version is a very handy T-SQL function which gives you the Major SQL Server version, the Edition (Standard, Business Intelligence or Enterprise), Build Number (RTM, Service Packs, Cumulative Update Level), Build Release Date and Windows Version.

Let’s run this query along with @@ServerName which gives us the SQL Server Instance Name too.

SELECT @@SERVERNAME as [SQL Server Name],
@@VERSION as [SQL Version]

 
Running this query on my SQL Server brings up the following:

sqlserver-version

When you expand the SQL Version column, you get the following.

Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Jul 25 2014 18:52:51  Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

where :

Major SQL Server Version = Microsoft SQL Server 2014
Build Number = 12.0.2254.0 (X64)
Build Release Date = Jul 25 2014 18:52:51
Edition = Enterprise Edition (64-bit)
Windows Version = Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
  
Some SQL Server 2014 RTM Branch Builds are as follows (ordered by Release Date):

Build Short Description Release Date (mm/dd)
11.00.9120 SQL Server 2014 CTP1 6/25/2013
12.00.1524 SQL Server 2014 CTP2 10/15/2013
12.00.2000 SQL Server 2014 RTM 4/1/2014
12.00.2342 Cumulative Update Package 1 4/21/2014
12.00.2370 Cumulative Update Package 2 6/27/2014
12.00.2381 KB 2977316 Security Update (QFE) 8/12/2014
12.00.2254 KB 2977315 Security Update (GDR) 8/12/2014
12.00.2402 Cumulative Update Package 3 8/18/2014
12.00.2405 KB 2999809 FIX 9/25/2014
12.00.2430 Cumulative Update Package 4 10/21/2014
12.00.2423 KB 3007050 FIX 10/22/2014
12.00.2436 KB 3014867 FIX 11/27/2014
12.00.2456 Cumulative Update Package 5 12/18/2014
12.00.2464 KB 3024815 FIX 1/5/2015
12.00.2472 KB 3032087 FIX 1/28/2015
 
The one in bold is installed on my PC and using this table with my Build number gives me an idea of how old is my build. There have been about 8 different builds released after I last installed SQL Server 2014 and I should update my latest build for better performance and security.


 
  Feedback:

December 16, 2014

SQL Tribal Awards

0 comments


Simple-Talk and SQLServerCentral.com are once again hosting the Tribal Awards. Inspired by the book Tribal SQL, the awards seek to recognize outstanding members of the SQL community in 11 categories.



Voting is open now and will close on January 2nd 2015. Winners will be announced on January 20th. In the spirit of Tribal SQL, the awards will also donate £250 to the charity Computers4Africa.org at the close of the event.

Link to Nomination form: https://www.surveymonkey.com/s/HWB5BWG
Link to more information: https://www.simple-talk.com/blogs/2014/11/21/the-tribal-awards-are-here-again/
Categories
Article that Saved My Bacon in 2014
Blog of the Year
Person You’d Most Like to Have as a Mentor
Best Presentation at any Tech Conference
Best Tech User Group
Best New Community Voice
Beyond the call of duty / Outstanding Contribution
Best Free PowerShell Script
Best Twitter Account To Follow
Best New Book About SQL
Best Outfit or Costume at a Tech Conference

These awards are inspired by the book Tribal SQL, published last year. Tribal SQL was created by 15 authors each contributing one chapter, and donating their royalties to a UK-based charity Computers4Africa.org


 
  Feedback:

December 08, 2014

Error Handling in SQL Server with THROW

1 comments


Continuing our series on SQL Server 2012, today we will talk about THROW. In versions prior to SQL Sever 2012, we used @@RAISE_ERROR to generate error messages dynamically or using the sys.messages catalog.

Consider the following example

SELECT ROUND(800.0, -3)

On executing this statement, you get the following error:

image

because the value does not fit into the decimal data type.

You can use @@RAISERROR to raise a message

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
DECLARE @ErrorMsg nvarchar(1000), @Severity int
SELECT @ErrorMsg = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY()
RAISERROR (@ErrorMsg, @Severity, 1)
END CATCH


Note: The old syntax of RAISERROR syntax specifying the error number and message number got deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead the new syntax RAISEERROR(50005, 10, 1) allowed you to specify the messageid, severity and state). For new applications use THROW.

However in SQL Server 2012, there’s a better way to this without much efforts – THROW. Consider the following code

BEGIN TRY
SELECT ROUND(800.0, -3)
END TRY
BEGIN CATCH
THROW
END CATCH


image

As you can see, with just one word THROW, we were able to handle the error with grace and get a result too.


 
  Feedback:

November 20, 2014

User Defined Functions in SQL Server

0 comments


User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role. For example, we can call user defined function in a where clause or use a user defined function in a JOIN [Where UDF returns a result set].

SQL Server supports two types of User Defined Functions as mentioned below –

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –
  • Inline Table
  • Multi-statement Table
We will explore these functions today. I am using SQL Server 2012 for this demonstration, although you can use SQL Server 2005, 2008, 2008 R2 as well.

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –
  • Customers
  • Employees
  • Orders
  • Order Details
  • Products
Let’s start querying the above table. Open a new Query window and write the following commands –

tablequeries

Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad –

scalar1

The above function returns an integer value. To test this function, we will write some code as shown below –

scalartest1

Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad –

scalar2

We will test this function with different years for an employee as shown below –

scalartest2

Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below –

tvf1

To test this example we will use a select statement as shown below –

tvftest1

Another example of the Inline Table Valued Function is as shown below –

tvf2

To test this function, we will use different years as shown below –

tvftest2
clip_image001

We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below –

clip_image003

To use the Multi-Statement Table Valued function, use this code –

tvfmultistatementtest

There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below –
  • You cannot modify the state of the database using UDFs
  • Unlike Stored Procedures, UDF can return only one single result set
  • UDF does not support Try-Catch, @ERROR or RAISERROR function
Summary – User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].

Download the source code of this article (Github)


 
  Feedback:

October 30, 2014

Using SET Operators in SQL Server

1 comments


In this article, we will see how to use SET Operators in SQL Server. We will look at UNION, UNION ALL, INTERSECT and EXCEPT operators.

To start with SET Operators, we will first create two sample tables in our Northwind database. The script is shown as below -

USE Northwind
GO

CREATE TABLE Example1
(
    Name CHAR(1)
)

CREATE TABLE Example2
(
    Name CHAR(1)
)


--INSERT FOR Example1 Table
INSERT INTO Example1 VALUES('A')
INSERT INTO Example1 VALUES('B')
INSERT INTO Example1 VALUES('A')
INSERT INTO Example1 VALUES('C')
INSERT INTO Example1 VALUES('D')
INSERT INTO Example1 VALUES('E')


--INSERT FOR Example2 Table
INSERT INTO Example2 VALUES('X')
INSERT INTO Example2 VALUES('A')
INSERT INTO Example2 VALUES('B')
INSERT INTO Example2 VALUES('B')
INSERT INTO Example2 VALUES('C')
INSERT INTO Example2 VALUES('Z')
INSERT INTO Example2 VALUES('Y')


--SELECT ROWS FROM BOTH THE TABLES [Example1, Example2]
SELECT * FROM Example1
SELECT * FROM Example2


In the above script, we have created two tables, Example1 and Example2 and inserted some dummy data. We will now take a look at how to select the data from both the tables using the SET Operators.

UNION Operator

Let’s take an overview of our very first operator - UNION. The UNION operator combines the result of two or more queries and returns a single result set excluding the duplicate values. The following figure shows the same output -

sqlserver-set-operators
Write the following query in SQL Server Management Studio and observe the result -

union

The output of the above query is as shown here -

unionoutput

Now let’s use the two tables in the Northwind database with the name Dept and Emp.

empdept

We will write a query where we want to find all the unique cities and countries from Customers and Employees table -

union1

The output is shown below -

unionoutput1

UNIONALL Operator

However if you want to include the duplicate values of City and Country from Customers, as well as Employees, include ALL with UNION. The query is shown below -

unionall

Some rules to remember while working with UNION operator -

1. The number of columns must be same.
2. The data type of the columns must be same or implicitly convertible by database.

INTERSECT Operator

We will now take a look at INTERSECT operator. The INTERSECT operator takes the result of two queries and returns common rows which appears in both the result sets excluding the duplicate values. The following figure explains the same -

sqlserver-intersect
Let's try the query on our Example1 and Example2 table. The query is shown below -

intersect1

The output of the above INTERSECT query is shown below -

intersectoutput1

We will try the INTERSECT on Northwind database and find out all the common Cities and countries from Customers and Employees tables excluding duplicates. The query is as shown below -

intersect2

The output of the above query is as shown below -

intersectoutput2

EXCEPT Operator

We will now explore the last operator, the EXCEPT operator. The EXCEPT operator returns distinct rows from the first query which do not appear into the second result set. The following figure explains the same -

except1

The output of the above query is shown below -

exceptoutput1

We will now try the same query with our Northwind database. We will find out all the distinct cities from Employees tables which does not appear in Customers table. The query is shown below -

except2

The output of the above query is shown below -

exceptoutput2

And that’s it. In this article, we have seen how to use SET Operators in SQL Server. We have seen UNION, UNION ALL, INTERSECT and EXCEPT operator with some simple examples.


 
  Feedback:

September 15, 2014

Memory Optimized Tables and Transactions in SQL Server 2014

0 comments


In one of our previous articles “SQL Server 2014 – InMemory Table, Indexes and Stored Procedures”, we saw how to create in-memory tables and indexes. We also saw how to use stored procedures to interact with these tables.

In-memory tables are capable of storing multi-versions of a rows. When you perform transactions on these tables, internally it uses snapshot – based transaction isolation. In memory optimized tables, we always assume that the transactions will optimistically commit the operations.

As we don’t have locks, we don’t have blocking mechanism in memory optimized tables. Given these points, a question always arises that what if there are conflicts. For example, we have two transactions. The first transaction tries to write a row and the same row is getting written by another transaction as well. Then these conflicts are detected and the second transaction will always fail.

In the scenario of two transactions, you can read the old version of the row in case a transaction is occurring on the row. But if the row is not available, then the transaction simply fails.

There are number of Isolation Levels supported by transactions as described below –

· Snapshot Isolation – In this level of isolation, the starting reads are consistent and writes are always consistent.

· Repeatable Read – In this level of isolation, we have guarantee that the rows which we read during the start of the transaction, are the same at the end of the transaction. Other transaction will not be able to change them.

· Serializable – In this isolation level, if you scan the entire table at the end of the transaction before commit to check, if the new row is inserted, then the transaction will fail.

Consider few guide lines while working with Memory Optimized Table Transactions –
  • Always declare which Isolation Level you want you as described above.
  • In case of transaction failure, try writing logic for handling conflicts and validations in your transactions.
  • Try avoiding long running transactions.
In my previous article I have shown how to create Memory optimized table, indexes and stored procedure. I am using the same table “Employees” for demoing Memory Optimized Tables and Transactions.

Let’s open SQL Server Management Studio and write the below script.

emlployee-script

In the script, the query pad 1 contains a T-SQL command which is creating a table with the name Test. We have inserted two rows in the table. We then start a transaction which will update the row from the Test table which has ID=1. We are updating the row without committing it. In the second query pad, we try to access the row which is under the transaction and hence you will not see the row unless the first transaction is either committed or rollback.

Try committing the transaction and you will see the updated row in your second query pad as shown below –
sql-result1

Now let’s compare the same with Memory Optimized table which we have created in the previous article. Let’s write the following script in Query pad 1 –

recordbeforeupd

Now update the above row and change the city from London to New York but do not commit it as shown below –

BEGIN TRAN
UPDATE Employees WITH (SNAPSHOT)
    SET City='New York' 
    WHERE EmployeeID = 88569
COMMIT TRAN


Now let’s try accessing the updated row from our second query pad and see the output. You will see the older version of row as shown below –

recordbeforeupd

Commit the transaction and you will see the updated row as shown below –

afterupdate

Let’s update the city from our first query pad without committing it. And then try to update the same row from our second query pad and then we will see the difference –

script2

The output of the second query pad transaction execution is as shown below. The error says – The current transaction attempted to update a record that has been updated since this transaction…

error1

The above error clearly says that the row which the current transaction attempted to update cannot commit the transaction as the row has been modified outside since this transaction started.

So, in this situation, the second transaction will always fail. We can also write a retry logic which can validate the row and then modify it.

Summary – In this article we have seen how to implement a transaction with memory optimized tables in SQL Server 2014.


 
  Feedback:
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions