Wise Owl suggestions for new features for SQL Server Management Studio
Writing SQL in Management Studio could, Wise Owl feel, be made a little easier: here are 10 practical suggestions for how!

Posted by Andy Brown on 09 September 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

10 suggested new features for the next version of SQL

Here at Wise Owl we think we're uniquely well placed to see what works and what doesn't work in software.  There's nothing like watching someone struggle with inner joins on a training course to realise that they could be made easier!  So here are 10 humble suggestions for improvements to make for the next version of SQL Server Management Studio (SSMS).

1 - Auto-indentation of queries

Wouldn't it be nice if SQL Server could automatically indent your SQL?  So you type  in this:

Badly formatted query

This query will execute, but it doesn't look pretty!

and SSMS turns it into this:

-- valid query, badly formatted

SELECT

FilmName AS 'Name of film',

FilmOscarWins AS Oscars,

FilmReleaseDate AS 'When released'

FROM

tblFilm

ORDER BY

FilmName ASC

I've written code to do this; can't Microsoft?  It works when writing Visual C# and VB in Visual Studio, so why not in SSMS?

2 - Auto-Capitalisation of queries

As part of the same feature, maybe SQL could capitalise:

  • columns and table names according to their capitalisation in the underlying database; and
  • keywords according to preferences set by the user in Tools / Options.

So I'm thinking that this:

A badly capitalised query

This query doesn't use consistent capitalisation.

 

could morph into this at the touch of a button:

-- right capitals

SELECT

FilmName AS 'Name of film',

FilmOscarWins AS Oscars,

FilmReleaseDate AS 'When released'

FROM

tblFilm

ORDER BY

FilmName ASC

3 - Better and consistent colours

I realise that I'm still talking about the user interface, but that is, after all, where most people type in their SQL.  So ... how about more consistent and useful colours?

Odd colours

In the query above, the word Description is blue (it shouldn't be), the text in quotes is red but the text in square brackets isn't, and the colour for the keyword and and the function CONVERT are barely distinguishable from the surrounding text.

4 - Visual joins

Everybody finds the syntax for inner and outer joins hard to remember initially when learning SQL.  You can use a view or the query designer to draw joins (and so get at the underlying query syntax), but how about doing this in situ?

Query and join together

My thinking is that as you type in the query, the "join window" below (or to the right of) it will show what you're creating.

 

I'll leave it to the boffins at Microsoft to work out the fine detail of how this should work ...

You could even have an auto-detect feature: right-click on a table to have SQL Server suggest other tables that it could link to. 

5 - Sensible wizards

When you choose to add a stored procedure, you get this (I've only shown a small part of it):

Stored procedure wizard

A small part of the stored procedure wizard!

You can then press the key combination SHIFT + CTRL + M to get this:

Stored procedure parameters

You can fill in the various parts of the procedure - if you have any idea what they mean!

 

So there's scope for a simpler stored procedure wizard, which could ask you to name the procedure and build up lists of parameters - version 2 please Microsoft!

The same applies for new tables, views, functions, etc.  The wizards need to be simpler, less ambitious, and should create well-indented queries without additional comments.

6 - Include a RESULTS keyword in SQL queries

You can send the results of any query to one of 3 standard places:

Query results options

You can show query results as text, as a grid or in a file.

How about being able to do this within a query easily?

SELECT

FilmName,

FilmOscarWins

FROM

tblFilm

ORDER BY

FilmName

RESULTS

'C:\films.xlsx'

Again, I'll leave the fine details to Microsoft!

7 - Sorting query results

I realise that SQL Server Management Studio is only designed for writing queries, and it doesn't attempt to be a user interface, but isn't there room for one small concession?

Sorted results?

Not even the teensiest little downward pointing arrow on each column, allowing you to click on the column header to change the sort order?

 

8 - Making variables easier to write and use

SQL variables look ... clunky!  Here's an example:

-- declare a variable

DECLARE @WiseOwlSuggestions int

 

-- set its value

SET @WiseOwlSuggestions = 10

 

-- add one more

SET @WiseOwlSuggestions = @WiseOwlSuggestions + 1

My suggestion would be to make it possible to remove the @ symbol and the SET keyword and to allow C-style increments - like this:

-- declare a variable

DECLARE WiseOwlSuggestions int

 

-- set its value

WiseOwlSuggestions = 10

 

-- add one more

WiseOwlSuggestions += 1

There may be practical reasons why some of this can't be done for an SQL compiler, but we can but ask.

9 - Better error messages

Missing out a comma must be the reason for about 50% of all queries failing.  Here's what you see if you do make this mistake:

Missing comma error

Think how much time the world's developers could save if the error message added a suggestion Have you missed out a comma? after this error!

 

Other common error messages could be:

  • Have you got the SQL keywords in the right order?
  • Have you tried referring to a table without using its alias?
  • Have you tried using a column alias in a WHERE clause?
  • Should you be using Excel instead?

10 - A VB developer option

If you're used to writing code in VBA or VB, you'll continually use:

  • the wrong inverted commas (" instead of ');
  • ' instead of -- as a comment prefix; and
  • the wrong concatenation symbol (& instead of +).

Could there not be an option to say that you want to write queries in the style of a Visual Basic programmer?  Just a thought.

Finally, one new suggestion which Microsoft have taken up - there's now a FORMAT function in SQL Server 2012.  Hallelujah!

This blog has 0 threads Add post