With SQL Server 2016 to be officially released this year, I want to make a wish list for the features I really dream of, and see when / whether this list will come true down the road.

1. Revolutionize SSMS with features like those in MS Office Word / Excel

With MS announced their intention of release SSMS independently of SQL Server database engine in June 2015, I wish SSMS, for its t-sql editor part, can be as capable as current MS office Word and Excel applications. For example, I can arbitrarily set color / fonts to any portion of codes. The business reason behind is that I can make codes easier to carry coding convention or business rules. For example, I can make all “Delete” as red and big sized font.

On the other hand, when we query a table, we get a spreadsheet-styled result set, which is similar to Excel, but in Excel, we can automatically create a graph based on a result set. Isn’t it wonderful that we can do similar things in SSMS ? i.e. when we do a query, we get both the query result and along side is the graph (like a pie or line) based on the result set?

2. Revolutionize SQL help

I always enjoy the style and simplicity of help mechanism in PowerShell, all you need to do is run:

Get-Help –name <KeyWord>

Where KeyWord can be a cmdlet name (either system or user defined) or some specific word, like about_operators.

In SQL Server world, if you create an object (a SP, trigger, function etc), you can use sp_help to find the metadata of the object, but there is no way for you to see any user-created information in the object (unless you use sp_helptext to read the object body script)

Besides this shortcoming, another one is we cannot display any syntax for regular T-SQL statement. For example, you cannot use sp_help ‘create index’ to find the syntax of “create index”

It would be very productive if we can see syntax of t-sql just within SSMS itself, and we can build a help framework to allow developers put the “help” info in the stored procedure/functions/views to facilitate the understanding of these objects.

3. Incorporate PowerShell into T-SQL

Just like SQL server 2016 supports R language, it will be great if we can mix t-sql with PowerShell, that will open a door to many innovative scripts. For example, currently, to delete any backup files older than X days before starting the backup is not too simple. (We need to use SSIS package or sql agent job to handle this.)

4. Create Conditional Foreign Keys

Currently foreign key (FK) constraint serves the referential integrity check. But as a data architect, I find traditional FK constraint implementation is pretty “ugly” in real world design.

Think about this example: 

I have a [CUSTOMER] table, and each customer may have multiple phones, and so we will have a [Phone] table, inside which, we have a Customer_ID column referencing to [CUSTOMER] tables’s PK, i.e. [Customer_ID].

Now I also have a [EMPLOYEE] table, and each employee may have multiple phones as well, so I have to create another [Phone2] table, inside which, we have a Employee_ID column referencing to [EMPLOYEE] tables’s PK, i.e. [Employee_ID].

Now let’s say I also have [ServiceProvider], [ATTORNEY], [TRUSTEE] tables, each may have multiple phones, so strictly speaking, we need to create a [phone] table for each corresponding owner table. This is a mess. But let’s say, I’ll create a brand new table [phone] as follows:

CREATE TABLE dbo.PHONE (
  phone_number varchar(20)
  phone_type varchar(10) — = mobile, home, office, fax etc
, owner_id int
, owner_type varchar(20) — = Customer, Employee, ServiceProvider, Trustee, etc
)

This table has a problem in implementing FK constraint, i.e. there is no way for me to set up a FK relationship using [owner_id] to reference a PK in [CUSTOMER] or [EMPLOYEE] table.

What I really dream of is that SQL server can create a somewhat “conditional” FK, i.e FK is set up based on a condition. In this case, when [owner_type]=’Customer’, owner_id will reference to [CUSTOMER].[Customer_ID], when [owner_type]=’Employee’, owner_id will reference to [EMPLOYEE].[Employee_ID], and so on so forth.

Using pseudo code, it should be like the following:

Alter table dbo.Phone add Constraint [fk_phone_owner] Foreign Key
with [owner_id] reference to
case [owner_type] when ‘Customer’ then [Cusomter].Customer_ID
                  when ‘Employee’ then [Employee].Employee_ID
                  when ‘Trustee’  then [Trustee].Trustee_ID
end

Currently, this type of foreign key integrity relationship can be implemented via triggers, but every DBA knows trigger is best to be avoided whenever possible. So if SQL Server engine can implement such conditional FK integrity, it is great to solve lots of real world headaches.

5. Introduce parallel maintenance for SQL Server instances.

These days, most of sql server environments contain multiple big databases and the maintenance of these databases, such as backup / restore / index maintenance can be very time-consuming if doing things in sequence. Isn’t it nice if we can do all the backups in parallel with one t-sql like the following (pseudo code)

Backup Database [A], , [C], [D] to disk=’d:\a.bak’, ‘d:\b.bak’, ‘d:\c.bak’, ‘d:\d.bak’

and then there are four sessions (or threads) that do the backup at the same time?

6. Enhance handling mechanism for blocking

Blocking is so common to every DBA, it is annoying yet unavoidable. To me, one of the most annoying case is that during index maintenance, one index reorganizing (or rebuilding) is being blocked for long time that may exceed out of the maintenance window, thus cause the minimum accomplishment and a waste of the maintenance window. Starting SQL Server 2014, “alter index” has the option to kill itself or the blocker during blocking after waiting for some user-defined time (check <low_priority_lock_wait> option).

But from a DBA perspective, I think this option should be extended to many other DML T-SQLs, like Insert / Delete / Update, and any other T-SQLs that may be a blockee (such as Truncate, Select, or partition related operations etc).

What’s more, I want to know who is the blocker and better, the whole blocking chain. My imagined pseudo option should be as follows:

<low_priority_lock_wait>::=

{ WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,  ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } ) ,

Save_Blocker_Info = <xml> }

Where <xml> can be an XML variable that records the blocking chain info. This info should be populated once MAX_DURATION is achieved.

With this blocking chain info, DBAs may come up with more creative ways to manage blocking and minimize the negative blocking impacts.

I hope some of the items will come true before 2020. God bless SQL Server.