New Year Wish List for SQL Server Features

 

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], [B], [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. 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

10 Responses to New Year Wish List for SQL Server Features

  1. S says:

    For #4, maybe a person table is needed. It could link to both the employees and the customers. From the person table you could link to the phone table.

  2. Art says:

    4. Conditional FK’s: Combine Customer, Employee & Trustee, etc. into a Person table to which the Phone FK refers. You can then (or also) create separate tables for each entity type. Using Entity as a table name is not suggested as it is not definitive.

    6. MAX_DURATION probably should be . A minute is a long time to wait but still can be expressed in seconds.

  3. Julie says:

    How about return the functionality for printing script in color? No longer available after SSMS 2008.

  4. Bob Stauffer says:

    Hi,
    Regarding #4. Create Conditional Foreign Keys, you might want to rethink creating so many different subtype tables for all of the different “people”. Instead of customer, employee, attorney, etc., tables, have a single “person” table. That would avoid the need for conditional foreign keys.
    Bob

    • jxhyao says:

      Well, different “people” do have different attributes, for example [Company] does not have “first name” / “last name” and “gender”, while [Customer] does not need to have “DOB” or “gender”, while [Employee] may not have same “Type” as [Company] (such as Non-Profit / Public / Private vs Contract / FTE / TTE, temporary time employee) etc. Long story short, there are times, we simply cannot merge tables.

      • Bob says:

        If they’re existing tables, probably. Those can be tough to re-engineer. But with a new design, you might be better off with “person”, “organization”, and “phone number” entity tables, each with associated subentity tables if needed. And then create foreign keys between them as required. Multiple people can share phone numbers, as can people and organizations.

      • jxhyao says:

        I am not denying that we can live with the current rules (which I currently follow as I have no choice). I am just saying the current rules (in this case FK) can be “advanced” to make data model more elegant. 🙂

  5. Bob says:

    Elegant? I’d say more complex than needed. 😉 Although I do like the rest of your wish list. Have a good weekend.

  6. Everyone seems to have jumped on #4 so I’ll leave that one alone. (Can you say Associative table?) #1 isn’t needed and not what SSMS is intended to be. It’s bloated enough without adding that additional functionality. I would rather see true multi-line text editing for scripts being written with it. Something along the lines of what the UltraEdit text editor does.

    I agree with #2 and #3. #5 can be handled with a little bit of design and engineering. We use a table and stored procedure driven system to handle this.

    #6 is one of those things that make you go, “hmmmm”. I currently keep track of blocking but it has long been an issue that comes up because the data engine has to maintain integrity across all objects all of the time. Not just on the ones that you’re not using at the moment. That said I get just about all of that using an alert on the permfmon counter SQLServer:General Statistics – Process Blocked counter. When ever it is > 0 a job that gathers the blocking info from sys.dm_exec_requests (among others) is fired to send an email to appropriate personnel with the pertainant information. It is setup to send another email every so often (configured in a table) to let us know what is involved and also when the system is “All Clear”. It’s not perfect, but it works for us.

    It may seem like I’m being critical but I do like your list in general. It was a reminder to me that I always need to be looking for better ways to do things. Thanks.

    Richard

  7. jxhyao says:

    Hi Richard,
    Thanks for your comment. It will be very interesting if you can blog about your way of doing #5, i.e.parallel work on DBA stuff. I actually implemented some “pseudo” parallel work via SQL jobs with some centralized tables. Nowdays, I start to use use PowerShell to implement parallelism.

    Regards,
    Jeffrey.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s