Developer Friendly T-SQL Enhancements in SQL Server 2016 and vNext

Both SQL Server 2016 and vNext versions have introduced many new features. T-SQL language too gets new features in each SQL Serer versions. Below two enhancements to T-SQL language is very important for DBAs and Database Developers who writes T-SQL heavily.

1. CREATE OR ALTER

You can create SQL Server objects like below by using CREATE OR ALTER statement.

    CREATE OR ALTER PROC dbo.TestProc

    AS

    BEGIN

       SELECT @@VERSION

END

This way, you can create or alter stored procedure, function, view, and trigger.  However, this does not apply for table object.  This saves developers time tremendously   because it avoids numerous validation steps.

2. DROP IF EXISTS

The new DROP EXISTS statements also avoids some conditional logic that you need to include to check the existence of the object before it drops.

DROP PROC IF EXISTS dbo.TestProc

This statement works with many SQL Server objects. See the below figure;

Source: MSDN

So if you’ve SQL Server 2016 or vNext in your environments, you can start using these new features right away.

Cheers!

How to check the isolation level

SQL Server supports all four ANSI standards isolation levels. They are;

1. Read Uncommitted (the lowest level, high concurrency, less blocking, more currency related issues) 
2. Read Committed (default)
3. Repeatable Read
4. Serializable (the highest level, less concurrency, higher blocking, no concurrency related issues) 

Other than the above four, SQL Server introduced additional two optimistic concurrency control isolation levels based on row versioning technique;

1. Read Committed Snapshot
2. Snapshot isolation level

DBCC USEROPTIONS command can be used to see the current isolation level in SQL Server.

ALTER PARTITION FUNCTION Causes Blocking?

Recently I’ve experienced a situation where one of the partition maintenance jobs created a blocking. At this point the database had high t-log usage and backup log was also executing. It was clear some kind of large transaction has occurred during a partition maintenance operation. All inserts into the table which was being partitioning, got blocked too. 

MSDN states:

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

ALTER PARTITION FUNCTION (Transact-SQL)

So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.

Cheers.

How To Find Object Name For “wait_resource”

When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such values are shown in Figure 1 below.

Figure 1 – List of wait resources

How do you interpret these values? It has the following format in this case: (It is not always the same format. Depending on the values you see in wait_resource the interpretation would be different)

[database id] : [file id] : [page id]

However still the information is not sufficient because knowing the page id does not give much details related to the issue your troubleshooting. So we need to figure it out the table related to the above page. For that you need to use some undocumented DBCC commands. (Please be careful when running them in production system)

DBCC TRACEON (3604)
GO
DBCC PAGE (5, 20, 56792898)
GO
DBCC TRACEOFF (3604)

You get an output similar to below;

Figure 2 – Output of DBCC PAGE command

Refer the objectId value. That is the object Id of the page belongs to. After that it is simple to find the object name using OBEJCT_NAME() T-SQL function.

Cheers.

The FORMAT function

Recently one of colleagues asked, how to format a date value according to the style he wants. His date style is as below;

10/15/13 - 04:11 pm

The immediate solution that came into my mind was the CONVERT function which you can specify the conversion style. He had already worked out a solution which using that function and the code is as below;

SELECT LEFT(Lower(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 1) + ' - ' + RIGHT( 
       CONVERT ( 
       VARCHAR, CURRENT_TIMESTAMP, 100), 8)), 17) 
       + ' ' 
       + Lower(RIGHT(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 100), 2)) AS 
       "Date/Time" 

However it is bit of complex string concatenation. Wouldn’t there be any simple way of doing it? So after bit of research I found the following code which does the exact same thing that my colleague wants.

DECLARE @d DATETIME = CURRENT_TIMESTAMP; 

SELECT Lower(Format(@d, 'MM/dd/yy - hh:mm tt')) AS 'Date/Time' 

Using FORMAT function, you can format numeric and date values. It also supports the culture. The syntax is as below;

FORMAT ( value, format [, culture ] )

SSMS Limitations

When performing admin tasks or development tasks, some people like to use SQL Server Management Studio (SSMS) while some are like to use scripts. As many of you know, SSMS has limitations when it comes to perform some tasks. I found such instance while setting up replication for a testing purpose.

The requirement is simple, I wanted to setup a replication for a table. The uncommon factor here is, the publisher and subscriber databases are same. I used a AdventureWorks2008R2 as the database.

SET XACT_ABORT Option and Transaction Behavior

One of my colleagues asked what if a transaction has only BEGIN TRAN and COMMIT TRAN without a ROLLBACK TRAN. I did not have a proper answer on top of my head however I could remember the behavior is depending on the SET OPTION known as XACT_ABORT.
I initially tried the below query to test the above mentioned situation. Note that I did not change the default setting of XACT_ABORT option. The default is set to OFF.

“Table Alias”, How it behaves?

One of my colleagues asked me a question about a simple T-SQL query which uses table alias. See the below T-SQL code;

USE AdventureWorks2012
GO

SELECT E.loginID,HumanResources.Employee.JobTitle FROM HumanResources.Employee E

The above query used a table alias “E” and in SELECT list one column refers with table alias while the other column referring  full table name.  Seems like technically correct query. However the query returned the following error.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "HumanResources.Employee.JobTitle" could not be bound.

As per the error message, use of table name, “HumanResources.Employee” to refer JobTitle column is incorrect. When you remove HumanResources.Employee in the SELECT list then the query works fine.

The theory behind this is when you have a table alias, it logical rename the table to table alias. So if you want to refer the table in the query, it needs to use table alias instead of the actual table name.

Relational algebra explains this more clearly.

The relevant operator for table alias in relational algebra (RA) is RENAME. As SQL derived from RA it is always better to learn RA before learning SQL.

How to get database sizes

There are several ways to get database sizes in a server. Following three system tables has the information to get database sizes.

sys.sysfiles

sys.database_files

sys.dm_db_file_space_usage

You also can use following system stored procedure.

exec sp_spaceused

However the easiest way is to use SP_HELPDB system stored procedure. Below script used that SP to get the database sizes in a server.

create table #spdbdesc

(
    dbname sysname,
    dbsize        nvarchar(13) null,
    owner sysname null,
    dbid    smallint primary key,
    created nvarchar(11),
    dbdesc    nvarchar(600)    null,
    cmptlevel    tinyint
)


INSERT INTO #spdbdesc
exec SP_HELPDB

SELECT *
FROM #spdbdesc

Meaning of auto generated STATISTIC name

Use sys.stats catalog to see all the STATISTICS available for the database. To see the statistics in AdventureWorks2012 database, use the following T-SQL statement.

USE AdventureWorks2012
GO
SELECT * FROM sys.stats

You would see the output something like below; (only the values of name column appeared)

_WA_Sys_00000009_00000005
_WA_Sys_00000005_00000005
_WA_Sys_00000003_00000005
_WA_Sys_00000004_00000005

How do you understand above names.

_WA - Washington, the state of the US where SQL Server development team is located.
All automatically generated statistics have the name starting with _WA_Sys. The first number is the column id of the column which these statistics are based on. The next number is the hexadecimal number of the object id of the table.
Source: Inside the SQL Server Query Optimaztion by Benjamin Nevarez

Parameter Sniffing

What is it? First we will go through the scripts below;

Create the following index;

USE [AdventureWorks2008R2]

GO

/****** Object:  Index [NNI_SalesOrderDetail_ModifiedDate]    Script Date: 03/06/2012 15:09:41 ******/

CREATE NONCLUSTERED INDEX [NNI_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]

(

     [ModifiedDate] ASC

)

Then create the below stored procedure. It is a very simple SP which has single SELECT statement for Sales.SalesOrderDetail table. There are two parameters for two date values.

How SQL Server handles multi byte characters?

To store multi byte characters you need to use Unicode data type in SQL Server. SQL Server has four data types for Unicode characters. (nchar, nvarchar, nvarchar(max),ntext)

See below for the MSDN definitions for the above data types; (except ntext)

Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.


nchar, nvarchar

What should you know more about NOLOCK?

As you may know already, NOLOCK is used in some queries to avoid, SQL Server to acquire shared locks on the records being red. The main reason to use nolock is to eliminate the performance overhead, especially for large data sets, due to shared locks. Though you have the gain in performance wise, it also gives you dirty records (uncommitted data).

This article explains another behavior of nolock.

Let’s create a sample table with test data to illustrate the behavior.

Shortcut to view tables

As a DBA I need to see the list of tables available in a particular database frequently. Traditionally, this can be done by traversing through the object explorer. This may be the popular method among GUI lovers. But for me it is a pain. I hope it is the same for most of the DBAs.