Katmai's 3 Blunders

I like all three suggestions very much; this is well thought out and I share the priorities you outline. But I have one minor peeve -- not directed at you, Paul, but at this conversation generally. I keep on hearing in discussions like this how "DBAs are insulted by being expected to use Visual Studio" or "it's a slap in the face to DBAs everywhere." I don't buy that argument for a minute. IMHO not having a T-SQL debugger in management studio is an inconvenience, and a bad design decision, but to those who get all worked up and personally insulted I have to recommend growing up a little. This technology changes rapidly, there are ups and downs, and if you actually get personally offended because of a situation like that, then I guess you'd spend a lot of time feeling angry and personally offended. Personally, I use all the tools, and I think the dramatic chasm that some people see (or do they create it, I wonder) between developers and DBAs can and will and should be erased, and we will all be expected to cross over. So to the grumblers: get Visual Studio, and learn it. It's a great tool. That way when you need it, you can fire it up and get work done. </rant> Meanwhile we can still hope to have a more convenient debugging process...

<blockquote> But, T-SQL is no UI and busy DBAs want a UI option to see these settings</blockquote>

Speak for yourself.  Busy DBAs should script it out once and then run that script against any server that they're setting up.  Any process worth doing is automatable, in my opinion.

Completely agree with #1.  No excuse for it.  Totally.

#2.  I'm sorry, but maybe I'm the last person in the world to understand what in the world a T-SQL debugger would possibly be good for.  I tried it out once, and didn't see the point.  For me, most of the time I spend "debugging" is in analyzing a query plan for performance.  And SSMS is damned good at that.  I have very, very little sequential T-SQL of any consequence, and it's been that way for years.  I'm not totally shutting you down here -- I just wish someone could relate to me a story where they felt that the T-SQL debugger SIGNIFICANTLY helped them in some way.  Other than, "there was this 10,000 line monster proc that was written like crap that I inherited...", I just don't see a use case there for me.

Agreed 100% with David regarding the debugger.  I tried it a few times on 2000, and found that I was much less productive with it than with dropping a few PRINTs/RAISERRORs/SELECTs here and there.  Step debugging T-SQL is just not a good experience compared with step debugging other languages (e.g., the C# debugger in VS2005 is amazing, and I certainly wouldn't trade it for a Console.WriteLine).

Agreed on #2.  RAISERROR is much more convenient for me than any debugger could ever be.

#2: I still have to say "meh" on the T-SQL debugger question.  If you write set-based SQL, there won't typically be a ton of use for debugging sequential logic.  

#1: totally agree

#3: I don't wear the DBA hat too much these days, so I can't really speak to this one :)

I think if we're going to talk about blunders for the next release, we should really talk about missed opportunities.  What are the big segments of the market that MS is missing?  Where could they be leaders?  What's the **unfulfilled need**?

For me, you need to look no further than the exciting stuff that people writing Ruby on Rails programs are doing.  I guess this speaks to the whole development platform more than just SQL Server, but I think that MS has a unique opportunity by controlling the application and the data layer to make object-based data access a heck of a lot easier for everyone on their platform.  Maybe the implementation of such a thing would not involve SQL at all, but some sort of active record implementation on the SQL Server level, and an easy to use, WCF based object marshaller on SQL Server itself.  Either way, the (admittedly large) problem or O-R persistence exists and is being handled a ton of ways by a ton of vendors.  MS could help this situation.  I think they've primarily failed up til now because they've tried to make OR mappers that are everything to everyone.  Perhaps it's time to back off a bit on that philosophy, like Rails does, and implement a method that admittedly doesn't cover ALL use cases...  Perhaps maybe it's even time to look at a proprietary interface to SQL Server from the .NET side that would somehoe accomplish this...   I don't know...  

When most folks talk MVC to me re: database applications, I keep thinking, "but isn't the database itself the model?"  And if so, why is it so darned hard to access that model in an adequate way through code?

Paul,

You seem to imply the sql server group is autonomous:) First of all how much do you think MS will talk up the sql standard with katami? Nada. The only way a major functional addition will be added to the language is for a case to be made that it adds value to some other component. Enhancing partition views adds value to LINQ. My guess is grouping sets adds value to report. I really don't think keeping up with standards plays much of a role anymore so getting OVER would have to be justified with adding value to something else. What?

I'm sure when MS implemented the ranking functions they considered the full window specification. Why didn't they implement the whole thing? My guess is only the ranking, specifically row_number(), was crucial to LINK. I'm afraid this also says something of the independence of sql. The same independence of the debugger:)

Of course many people familiar with OVER would like it. But as times goes by it becomes more and more of an embarrassment . I think MS would like to just drop OVER:) Besides there are numerous workarounds. The mixing of variables/columns in Update can be resurrected and used to simulate it. That should provide quite a few articles for some folks. And there's always RAC :)

best,

www.beyondsql.blogspot.com

Hello Paul,

On behalf of all us dummies out there what is 'composable sql'? Never heard that before:)

Steve:

INSERT INTO Tbl (Column)

SELECT *

FROM 

(

 UPDATE SomeOtherTable

 SET SomeCol = 'X'

 OUTPUT DELETED.SomeCol

) n

Result: The original value of "SomeCol" for all rows of SomeOtherTable will be inserted into Tbl, along with the update being done.  Why was this feature added to T-SQL, you might ask?  Simple... It helps immensely with TPC scores.

Adam,

That's very keen stuff:)

Update

@variable = column = expression

Saves a round trip, also shaves tpc scores:) Sometimes an orange really is an orange. Sometimes it's a banana:) Sometimes it's hard to tell where the intellectual pursuit ends and politics begins. Sometimes because of politics you get something you can twist to your purposes. Sometimes it's hard to pin down the real intent. Sometimes I really do wish it wasn't such a game.

My 2 cents

>>The Surface Area Configuration tool is gone

This was a great way to create the scripts you needed to enable OPENROWSET etc etc, no need to look it up in BOL, you create it once and save it

>> the debugger

It was in query analyzer and it didn't cost a dime, why take it away? I wouldn't mind paying if it works, but it doesn't work

Denis,

OPENROWSET (ad-hoc remote queries) and other server-level features that can be turned on and off (XP_CMDShell, SQL Mail, Database Mail, CLR, Remote DAC, Service Broker Endpoints, Soap Endpoints, OLE Automation, and Web Assistant) can be checked and configured via DMF in one shot, for all your servers.  You simply seet up the group of servers/instances that you are managing, and I promise you, this is going to be easier to do via DMF, than it could ever possibly be with SAC (you have to repeat the same set of checks and clicks for every server you have to manage, after changing the computer that SAC is connected to).  I plan to write an article about how easy this is going to be, once CTP6 is public and I can actually share screen shots etc.

Now, there are a few omissions, for example turning TCP/IP etc. on and off is not in the current set of options available in DMF.  I am not sure if that is coming in CTP6, RTM, or if they missed it entirely.

Aaron,

Thanks, I tried it out

Policy Managment-->Conditions, right click-->New Condition

Facet Surface Area Configuration, from the first browse button I selected @AdHocRemoteQueriesEnabled, value = 1, give the condition a name

Then from Policy Managment-->Policies, right click-->New Policy, scroll down to the one you created. Execution Mode, select On Schedule, pick starts automatically when SQL Server Agent starts

Give it a name, pick server restrictions, hit OK

Go back to the policy, right click and select Export Policy, this will export it to a XML file. You can use this file to import the policy if you ever need to

Denis,

It can be even easier than that.  You can group managed servers together in Registered Servers (e.g. under local server groups) and then you can right-click the group as a whole, and evaluate policies that come from any server on demand (instead of on schedule).  That way the DBA can quickly assess certain aspects of his/her entire data center, or even enterprise, with a few clicks of the mouse...

Paul: I'm surprised by the statement that the debugger is not back. Do you have any verifiable (i.e., on the internet) source to confirm this? Until I see an official announcement, I'm going to believe what Microsoft has promised two years ago:

* "We will implement this feature for the Katmai release of SQL Server." (May 8, 2006, in response to https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550 - 199 votes, average 4.89)

* "This functionality is scheduled to go into the next major release of SQL Server." (March 30 2006, in response to https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124613 - 48 votes, average 4.75)

David: "I just wish someone could relate to me a story where they felt that the T-SQL debugger SIGNIFICANTLY helped them in some way"

I have used the debugger many times when trying to find errors in a database that had triggers on many tables, some of which called various stored procedures that changed data in tables (which of course caused other triggers to fire). I'll readily admit that this was an extreme case, but even in situations with just a few triggers, a debugger can help you quickly find out what's going on.

I'll immediately agree that the old debugger was limited. I missed the opportunity to check, or even change, table contents from within the debugger. A C# or VB.Net debugger allows me to check and set variable contents, a T-SQL debugger should expose similar functionality for tables (permanent, temporary, and table variables). But for now, I'll take the "old" debugger over the current "no" debugger!

> Simple... It helps immensely with TPC scores.

What TPC scores are you referring to, Adam?