Search results matching tags 'SQL Server 2012' and 'management studio'

Why people think some SQL Server 2000 behaviors live on... 12 years later

AaronBertrand — Tue, 28 Feb 2012 16:57:00 GMT

A long, long time ago in a galaxy you can reach out and touch right now, ORDER BY in a VIEW meant something. If you had a view like the following:

CREATE VIEW dbo.Rubbish
AS
    SELECT TOP 100 PERCENT Trash
        FROM dbo.Garbage
        ORDER BY Trash;

And then you ran a query such as:

SELECT Trash FROM dbo.Rubbish;

You could reliably expect the rows to come back ordered by Trash in ascending order, even though it is relatively clear that the ORDER BY here serves a completely different and overloaded purpose - to dictate which rows should be filtered by TOP.

When SQL Server 2005 came around, changes to the optimizer meant that this behavior - which was never documented, by the way, hence never guaranteed - ceased to work as reliably as it had in previous versions. In fact in this case the optimizer sees TOP 100 PERCENT and simply throws out both TOP and ORDER BY. Compare the differences in the plan from the view with and without the ORDER BY:'

Notice how there is no sort operator in the query without ORDER BY? Because we didn't tell SQL Server what order we want, it's free to make its own choice. In this case it is choosing a clustered index scan which is not based on the Trash column. You can see the end result of the difference in these plans by comparing the results from the select:

Obviously only the latter result honors the outer ORDER BY, and in fact both cases have discarded the inner TOP/ORDER BY combination.

People are surprised by this change, and in fact variations on the symptom come up in forum discussions all the time. Further to the confusion, that still exists today, early on there were some very vocal customers who screamed bloody murder and were very stubborn about changing their code to put the ORDER BY on the outermost query where it belongs. After upgrading to SQL Server 2005 they cried foul until Microsoft finally submitted - creating a trace flag that forced the optimizer to behave the old way. Note that this trace flag is only valid in SQL Server 2005 and SQL Server 2008 - in SQL Server 2008 R2 and SQL Server 2012, the trace flag seems to be a no-op (at least in every case that I've tried). Even if you are still on 2005/2008, I highly recommend using this option as a last resort only, because it changes the way the optimizer works and this can have other undesirable impacts on your entire workload.

Hopefully none of this is news, as it has been discussed at length both online in general, and more specifically on Connect. Where it gets more interesting is that there have been several bugs reported that tools within Management Studio, such as the view designer, actually encourage this behavior and help perpetuate the myth. The issue I noticed this weekend is that, while most bugs have been closed as won't fix, one (#249248) has been closed as fixed. So I immediately fired up the latest build of Management Studio I have, right-clicked the views node, and selected New View. Once I added a table, what I saw was not very promising, as I was able to choose Sort Order and Sort Type:

So Management Studio for SQL Server 2012 still allows me to specify a sort order and sort direction for any column in the view! And what does it do to the SQL? It injects TOP 100 PERCENT / ORDER BY! Bad, bad, bad!

On the plus side, if I try to *save* this view, I at least get some kind of warning:

For Google/Bing-fu:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
Click CANCEL to discard your modifications.
Click OK to save the view.

It's great that they're finally trying to stop people from believing that ORDER BY in a view means anything about ordering the result. But the problem that's being overlooked here is that not everyone uses the view designer to create and save a view in a single session. They may be generating code for a view that they're going to cut & paste into a CREATE/ALTER view script elsewhere, to use in a CTE or derived query in a more complicated script, or to send to a colleague. In a lot of cases they are never going to see this warning, because this won't magically pop up when they try to use the syntax in other scenarios.

So I left a comment on the Connect item with a little detail about that, because I'm unfortunately going to have to disagree that this issue has been "fixed." I'm not sure why the Sort options in the view designer can't just be hidden without changing the behavior of the tool in general - can't it just always behave as if I hadn't changed those options at all?

I do acknowledge that there are some edge cases here, where someone really does want to create a view that selects the top (n) rows ordered by some criteria. So perhaps my suggestion to remove the columns altogether is a bit harsh. But when I choose a Sort Order for one or more columns, it should not just throw TOP 100 PERCENT into my query; the query that's produced at this point shouldn't be valid. Instead it should prompt me with a similar warning to what I get now when I click Save, with additional wording that implies, "If you really do mean to take some subset of the rows in the table, please add a TOP (n) clause to indicate the number or percentage of rows you want to return."

Agree with me? Please go comment and vote!

T-SQL Tuesday #25 : T-SQL Tips 'n' Tricks

AaronBertrand — Tue, 13 Dec 2011 18:50:00 GMT

This month's T-SQL Tuesday is being hosted by Allen White (@SQLRunr) and is about sharing your T-SQL tips and tricks. Since I know many people will share their T-SQL magic and wizardry, I thought I would share a couple of productivity tips - so still T-SQL related, but more how to take advantage of some features in SSMS.

Drag column list onto your query window

Whenever I give my Bad Habits to Kick presentation, I remind people that - even if they're not using 3rd party code completion tools (many of which offer SELECT * expansion) - there's little excuse to use SELECT *. This is because Management Studio makes it very easy to list out all of your columns in one motion. I'm always amazed when I ask how many people didn't already know about this - and most times it's more than half the room.

The process is quite simple - expand your table, click on the Columns node, drag it onto your query window and let go:

Now, it's probably not in the exact format you want, it won't properly delimit any [keyword] or [reserved word] you use for a column name, and you'll have to do this multiple times when you're performing a join (and you'll have to add your own table/alias prefixes to each column as well). But I'd rather spend time using TAB and ENTER to correct formatting than to try and type out all the column names individually.

Use snippets

If you find yourself writing the same type of code over and over again, the new snippets feature in SQL Server 2012 will likely be very useful for you. What does this have over templates? For one, I find it much easier to use once you have your snippets created. There is no hokey VB6-style dialog to perform token replacements, and you can actually store all your custom snippets in a network location - so if you want to enforce coding standards you have a fighting chance by pointing everyone's SSMS at the same snippet location. I'll be honest, though, setting up the template is a bit of a pain - you need to hand-massage XML manually because as of yet there is no easy way to generate these from within Management Studio. But once they're in place, they're very easy to work with. Just right-click within the query window, choose "Insert Snippet", and follow the rabbit:

One of the pieces of code I write a lot for administrative tasks (or for helping folks out in the wild) is a cursor. I tend to have a hard time memorizing the options I always try to use to make a cursor as efficient as possible (something else I cover in my Bad Habits to Kick presentation). The snippet eliminates this problem. Once I've selected the "Declare Cursor" snippet I've saved, I get the following in my query window:

You'll notice in the diagram that "c" is currently highlighted and the cursor is there; all other instances of "c" are lightly outlined. I can type any name I want to overwrite "c" and, once I hit tab (which moves me to the next token), it will replace all instances of that token with the new name I've typed. So this is a little easier to work with than the floating window in templates, though you lose this easy editing capability if you start doing other things with the script first (need to remind myself to file a bug or suggestion about that). Changing "c" to "x" throughout this code sample took two keystrokes, and I'm ready to add the actual query:

I plan to write a much more thorough blog post on the snippets feature, because I think it will become a pretty popular time saver once the latest version of Management Studio becomes more common on the desktop. In the meantime, if you're already playing with SQL Server 2012 RC0, I highly recommend you check this feature out!

Avoid dynamic SQL in generated scripts

I see a lot of people complain that when they script objects from Management Studio the object text ends up in dynamic SQL. I don't blame them - this makes it very cumbersome to read, never mind modify, the object text before copying, running or saving the script.

In SQL Server 2008 R2, this is controlled by the option Tools > Options > SQL Server Object Explorer> Scripting > Include IF NOT EXISTS clause:

In SQL Server 2012, the option has changed slightly to Tools > Options > SQL Server Object Explorer > Scripting > Check for Object Existence:

Why did it change? Because, depending on the action, it actually performs an IF EXISTS check (e.g. for a DROP or ALTER), not always an IF NOT EXISTS check (for a CREATE). Some background in the following Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/242799

http://connect.microsoft.com/SQLServer/feedback/details/264310

http://connect.microsoft.com/SQLServer/feedback/details/624075

(As an aside, I think it's funny that they call the options category "SQL Server Object Explorer" - good thing they're specific, because you might get it confused with the Lego Object Explorer, Oracle Object Explorer or Windows Explorer Object Explorer.)

In any case, when these options are set to True, the scripts you generate often yield dynamic SQL, and even when they're set to false they still sometimes perform the check for existence (specifically in 2008 R2). Here is a brief wireframe of the options and how they affect each scripting method:

Note that with the "DROP and CREATE" option, the existence check for the CREATE is unnecessary - after all, if the object already exists, the script should have just dropped the object (though both checks may have failed if there is an object with the same name that is *not* a procedure). In general, most people won't want to perform a DROP and CREATE anyway - they lose dependencies and permissions when they do this (though permissions can be scripted with a different option in the same category). Until they develop "CREATE or REPLACE" functionality (please vote and comment!), ideally you should have a script that creates a stub for the object only if it doesn't already exist, then runs an ALTER - then you don't have to re-apply permissions, you don't have to worry about whether the object already exists, and as an added bonus you retain dependency chains. So your scripts can look like this (still using dynamic SQL, but it's very simple and doesn't present parsing or reading issues):

IF OBJECT_ID('dbo.proc_name') IS NULL
BEGIN
    EXEC sp_executesql N'CREATE PROCEDURE dbo.proc_name AS SELECT 1;';
END
GO

ALTER PROCEDURE dbo.proc_name
AS
... -- actual script body from source control or elsewhere goes here

Unfortunately there is no way to train SSMS to do this today, unless you write a scripting add-in.

Wrapping up

I hope that at least one of these little tricks helps you in some way. It's always fun participating in T-SQL Tuesday, even if I'm often trying to bend the rules a little bit.