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!

Why use SELECT * With DMVs?

Dynamic Management Views and Functions are used to retrieve information about SQL Server internals. The Dynamic Management Objects first, introduced in SQL Server 2005. The subsequent releases added many new DMVs while some got changed.  Sometime new columns can be added and column names can be changed in new versions. So it is recommended to use SELECT * whenever you want to get internal information using DMVs. This is especially important when developing production monitoring code. So that the possibility of breaking your code is less with new SQL Server versions.

SQLSaturday584 - Colorado Springs

Another great SQLSaturday conference ended on 25^th March, 2017 at Colorado Springs. I presented SQL Server Statistics and New CE model. You can visit the below link to download the presentations and demos which you’re interested. There are many valuable topics.

 

Schedule Page

 

Thanks everyone who participated for the event!

 

SQL Server errors are too generic

Below is an actual error captured in SQL Server Error log during an incident. 

SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. 

Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

This error is not quite accurate because it says SQL Server must shut down in order to recover a database which is database id 1. In SQL Server, database id 1 means "master" database. The next statement says the database is either a user database or system database. In this particular case, it can not be a user database. 

The error message is quite generic. It appears that SQL Server does not change the error messages depending on the database id in this case, maybe it could be due to a performance impact.