Can we commit inner (or nested) transactions?– SS SLUG Dec 2013 – Brain Bashers - Demo IV

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about committing nested transactions.

Here is the question related to this demo;

Can we commit the inner transaction (or nested transaction) without committing the most outer transaction?

Answer is simple. The concept of nested transaction does not exist with Microsoft SQL Server though we can have multiple BEGIN TRAN and COMMIT TRAN statements respectively.

What exactly happen with inner BEGIN TRAN and inner COMMIT TRAN?
They do nothing but increases and decreases @@TRANCOUNT. Note that @@TRANCOUNT giving a value greater than one does not mean that there are more than one transactions. It means there are more than one BEGIN TRAN statements. Committing all statements is actually done by most outer COMMIT TRAN statement.

Then what is the purpose of nested transaction in SQL Server?
It is meaningless to have multiple BEGIN TRAN statements in a same scope (example, in one stored procedure). However this allows to track the count of BEGIN TRAN in nested operations; Stored procedure executes BEGIN TRAN and then calls another stored procedure that has BEGIN TRAN. The count helps SQL Server to determine at which COMMIT TRAN actions should be committed.

Are you sure?
You may ask that question. Let’s test this and see.

Let’s execute the following code. It creates a database and a table, then inserts two records after starting a transaction with BEGIN TRAN.

CREATE DATABASE TestDB

GO

USE TestDB

GO

-- create a table 

CREATE TABLE dbo.TestTable

(

    Id int PRIMARY KEY

    , Value char(500) NOT NULL

)

GO

-- start a transaction

BEGIN TRAN

-- do some actions

INSERT INTO dbo.TestTable

    (Id, Value)

VALUES

    (1, 'Test Value 1')

INSERT INTO dbo.TestTable

    (Id, Value)

VALUES

    (2, 'Test Value 2')

Let’s check the @@TRANCOUNT and bytes used for this transaction.

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

 

As you see, the count is 1 and 1408 bytes used for the transaction. Let’s have another BEGIN TRAN and some INSERTs, and check the same.

-- add another BEGIN TRAN
BEGIN TRAN

-- do some actions
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (3, 'Test Value 3')
INSERT INTO dbo.TestTable
    (Id, Value)
VALUES
    (4, 'Test Value 4')


SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('TestDB')

It increases the count and bytes used. Now, let’s have one COMMIT TRAN statement and check again.

COMMIT TRAN

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

Now @@TRANCOUNT is again 1 but number of bytes used is same. This clearly shows that inner COMMIT TRAN statements do not do anything other than decreasing @@TRANCOUNT. Let’s execute the COMMIT TRAN again and see.

COMMIT TRAN

SELECT @@TRANCOUNT AS TransactionCount

SELECT database_transaction_log_bytes_used

FROM sys.dm_tran_database_transactions

WHERE database_id = DB_ID('TestDB')

Everything is clear now. This proves that inner BEGIN TRAN and COMMIT TRAN do not do anything other than changing the @@TRANCOUNT.

Can I rollback part of the actions performed in my transaction?
Yes, it is possible with SAVE TRAN statement. It allows to set savepoints in the transaction and rollback the transaction to the savepoint.

Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This speaks about different ways of passing datetime values to SQL Server databases.

Here is the question related to this demo;

There are many ways to pass datetime values to SQL Server. Some of ways are;

1. Value formatted as a string value
2. Value formatted as a datetime value
3. Value formatted as a datetime value and accepted via DATEFORMAT option
4. Value formatted as a ISO 8601 datetime value

What is the best way?

Majority uses the second option. It works fine as long as all applications are using correct datetime formats and all are in same country. However there could be misinterpretation if one of the application uses a different convention. Have a look on below C#.NET code (Note that the code written to show the demo only, it may not be the correct way to be written).

USE tempdb

GO

CREATE PROCEDURE dbo.ShowMonth @DateTimeValue datetime

    , @Month varchar(10) OUTPUT

AS

BEGIN

    SET @Month = DATENAME(m, @DateTimeValue)

END

GO

 

string year, month, date, datetime;

// making the datetime value from different string values

year = "2013";

month = "05";

date = "01";

datetime = month + "/" + date + "/" + year + " 00:00:00";

DateTime convertedDate = Convert.ToDateTime(datetime);

// set connection and command

SqlConnection connection = new SqlConnection(@"Server=SL-DEV001\SQL2012;Database=tempdb;uid=Dinesh;pwd=Dinesh");

SqlCommand command = new SqlCommand("dbo.ShowMonth", connection);

command.CommandType = CommandType.StoredProcedure;

SqlParameter parameterDateTime = new SqlParameter("DateTimeValue", SqlDbType.DateTime);

parameterDateTime.Value = convertedDate;

parameterDateTime.Direction = ParameterDirection.Input;

command.Parameters.Add(parameterDateTime);

SqlParameter parameterMonth = new SqlParameter("Month", SqlDbType.VarChar, 10);

parameterMonth.Direction = ParameterDirection.Output;

command.Parameters.Add(parameterMonth);

connection.Open();

command.ExecuteNonQuery();

connection.Close();

Console.WriteLine(parameterMonth.Value.ToString());

When you execute the code, it returns the “month” as follows;

Now let’s change the “Culture” of the current thread. Add the below code as the first statement of C#.NET code.

Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");

And when the code is run, result is;

As you see, “month” returns from the stored procedure is now wrong. It means, Date has been misinterpreted by either application or SQL Server.

Now let’s change the way we pass the date and time as per ISO 8601;

//datetime = month + "/" + date + "/" + year + " 00:00:00";

datetime = year + "-" + month + "-" + date +  "T00:00:00";

If you execute the code now, SQL Server identifies the elements of passed value properly regardless of Culture setting and returns the value correctly. Therefore 4th option is the best way to pass datetime values to SQL Server.

ISO 8601
This is an international standard for exchanging dates and times. This standard provides an unambiguous and well-defined method of representing dates and times and avoids misinterpretation of dates and times.

Format of this standard separates date and time using “T” (uppercase). Year needs to be set with 4 digits, month needs to be set with 2 digits and, day needs to be set with 2 digits. Time sets in 24 hours format. Hour, minute and second are separated using “:” and fraction of second is separated using “.”.

For an example, “8-Jan-2013 2:45:44” as per ISO 8601 is “2013-01-08T14:45:44.000”.

When this is used with SQL Server, setting related to SET DATEFORMAT and SET LANGUAGE is ignored.

If you need to convert stored datetime values to ISO 8601 for exchanging them with another party, here is the way;

SELECT CONVERT(char(23), DateValue, 126)

You can use 127 instead of 126 if time zone information is required.

Read more on ISO 8601: http://en.wikipedia.org/wiki/ISO_8601#Dates

How SQL Server interprets two-digit years as four-digit years – SS SLUG Dec 2013 – Brain Bashers - Demo II

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses the way SQL Server interprets two-digit years to four-digit years.

Here is the question related to this demo;

You execute the following query. Note that the “years” of input values are sent as two-digit years.

DECLARE @datetime datetime

SET @datetime = '01/01/00'

SELECT YEAR (@datetime)

SET @datetime = '01/01/30'

SELECT YEAR (@datetime)

SET @datetime = '01/01/40'

SELECT YEAR (@datetime)

SET @datetime = '01/01/50'

SELECT YEAR (@datetime)

How SQL Server interprets each year to four-digit years?

In order to answer this question, you need to know the two digit year cutoff which SQL Server uses for determining the year. The default value of two digit year cutoff is 2049. If you pass a two-digit year that is less than or equal to last two digits of cutoff year (2049), SQL Server uses the same century as the cutoff year. Here is the result of above query;

Since the 50 is above cutoff year, it has been interpreted as 1950. All other two-digit years are set with century of the cutoff year.

You can change the cutoff year if required. This option appears only if “show advanced options” is enabled via sp_configure. If “show advanced options” is not enabled, enable it. Then, set the value you need for “two digit year cutoff”. The below code sets it as 2060.

-- enable advanced options

sp_configure 'show advanced options', 1

GO

reconfigure

-- set two digit year cutoff as 2060

sp_configure 'two digit year cutoff', 2060

GO

reconfigure

Let’s run the query again and see;

As you see, 50 is less than current cutoff year. Therefore even last value is taken as 2050. Change it back to 2049 to maintain the backward compatibility.

You need to know one more thing on this setting. If you are accepting year values via an OLE Automation object, you need to be aware that its cutoff year is 2030, not 2040. For an example, if a .NET application is used for sending values for a stored procedure and values are set as two-digit years, you may notice unacceptable years. Look at below code and see. If two-digit values are less than last two digit of cutoff year, it uses century of cutoff year.

DateTime dt;

dt = Convert.ToDateTime("01-01-00");

Console.WriteLine(dt.ToString());

dt = Convert.ToDateTime("01-01-30");

Console.WriteLine(dt.ToString());

dt = Convert.ToDateTime("01-01-40");

Console.WriteLine(dt.ToString());

dt = Convert.ToDateTime("01-01-50");

Console.WriteLine(dt.ToString());

Filtering DateTime values – SS SLUG Dec 2013 – SQL Brain Bashers - Demo I

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses a tricky (or error-prone) filtering issue with datetime values.

Here is the question related to this demo;

You have a table that contains two columns as below. First column type is int and second is datetime.

Id (int)	Date (datetime)
1	2010-06-12 09:17:19.320
2	2010-06-12 23:59:59.997
3	2010-06-13 00:00:00.000
4	2010-06-13 13:30:00.347
5	2010-06-13 23:59:59.997
6	2010-06-14 00:00:00.000
7	2010-06-14 00:00:00.420

You write a query for getting records related only to 2010-06-13;

SELECT Id, Date

FROM dbo.DateTimeValues

WHERE Date BETWEEN '06/13/2010 00:00:00:000' 

AND '06/13/2010 23:59:59:999'

Does this produce the required resultset?

Answer is “No”. Result includes 6th Id too, which is related to 2010-06-14.

The reason for this is, accuracy level of datetime data type and storage mechanism of datetime type. SQL Server only stores time to approximately 1/300th of a second (.00333 seconds), meaning time is stored as;

00:00:00:000
00:00:00:003
00:00:00:007

Because of this, when a value like “2013-12-25 10:30:50:995” is used, it gets rounded up to “2013-12-25 10:30:50:997”. In the query given with the example, “06/13/2010 23:59:59:999” is rounded up to “06/14/2010 00:00:00:000”, resulting 2010-06-14 record in the result.

However, this query can be written in a different way to get the required result;

SELECT Id, Date

FROM dbo.DateTimeValues

WHERE Date >=  '06/13/2010 00:00:00:000' 

    AND Date < '06/14/2010 00:00:00:000'

If you need to accuracy level of datetime for more than 0.00333 seconds, solution is, using datetime2 data type instead datetime. The accuracy of datetime2 data type goes up to 100 nanoseconds.

Finding pages (or physical location) of records: %%PHYSLOC%% and fn_PhysLocCracker

There are many instances which we need to find the physical location, specifically the page, related to a record. Good example could be finding records in a corrupted page, or finding records’ distribution in pages. In most cases, I have use DBCC IND and DBCC PAGE but there two more great functions that can be used for finding the same.

The first function is %%PHYSLOC%%. This returns the RID (Record Identifier) as a hexadecimal value. The RID consists file number, page number and, record number.

SELECT p.BusinessEntityID, p.Title

    , p.FirstName, p.LastName

    , %%PHYSLOC%%

FROM [Person].[Person] p

The second function is a table-valued function which is fn_PhysLocCracker. It accepts the RID returning from %%PHYSLOC%% and returns three-columned table for file number, page number and, record number. Here is the way for using it;

SELECT p.BusinessEntityID, p.Title

    , p.FirstName, p.LastName

    , ph.*

FROM [Person].[Person] p

CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph