My Biggest SQL Server Pet Peeves

Both of these facts bother me because together they point to a trend to move database development from the database professional to the application developer.

The primary tool of the SQL Server database professional is Management Studio and the primary language of SQL Server database development is T-SQL. For these two reasons, both stored procedure debugging and Data Dude belong in Management Studio first and Visual Studio only as a secondary option.

When I teach a SQL Server upgrade class, the students are shocked to learn that store procedure debugging was moved from the database tool to Visual Studio. Most SQL Server professionals I know don’t even have Visual Studio installed on their workstation.

Microsoft, please respect the database professional and make the common sense move to place database tools inside the primary environment first.

Comment Notification

Comments

I agree. I now must keep a copy of Visual Studio open constantly - both for SQL debugging and to access the Team Foundation source control (some of the TFS SCC functions are not implemented conveniently via the limited command line supplied).

Or perhaps I should say "attempt" to keep a copy open - it tends to crash semi-regularly on two different workstations. VS also consumes more resources on my systems other than SQL Server itself (I expect the DB instance to use a signifcant amount of memory, but not my IDE).

Visual Studio does _not_ make a good interface for the DBA to develop in. Perhaps if you also work in .NET or ASP it is a handy to have everything in one environment, but I am keeping my eye on the development of 3rd party tools (ApexSQL for example).

The SQL debugger in Vs is written by the VS team using APIs that are jointly developed between the SQL and VS-Debugger teams. When QA gained a debugger in SQL 2000 this was all developed by the Tools team in SQL, it was a LOT of work to do and stablise.

When the switch was made to using elements of VS Shell as the core for SSMS one of the reasons was to make it easier to rehost elements rather than developing them from scratch, the team simply ran out of time to implement all the features (intellisense, smart editing, debugger were just some of these), they were always in the plan to be done. In the case of the debugger it was supposed to come back in what became SP2 but it looks like that plan was changed after I left the team.

My favourite "free" feature was the bracket matching in the T-SQL editor, the team had to build something called a T-SQL Language Service to enable a basic editor in T-SQL, when the core VS editor (that the T-SQL one inherits from) added bracket matching the T-SQL one got it without any work, in fact I was demoing something else live on stage when we noticed it was working, no-one knew up to that point.

Moving to the VS based editor was an attempt to move T-SQL editing onto a rich modern platform to make it a peer of C#, VB etc, the SQL tools team at the time did not have the resources to build all of that into QA or any other custom editor.

I know it blows that the debugger is missing in SSMS and I know some people are still frustrated by what SSMS delivers today but the real payback comes in the future, as long as the team continues on the strategy that we set down at the beginning of Yukon, as new features are added in VS, SSMS should just be able to rehost or better yet, reskin to make them apply more to DBAs than Developers.

I also agree. So much, in fact, that I made a suggestion to bring back the stored procedure debugger on Connect. There are already 156 votes in facor of this suggestion, with an average rating of 4.87, but there's always room for more.

Thanks Euan for the inside story. To clarify - I like SSMS and the move from a proprietary editor like QA to a VS-based tool was the right move. My point is that if it's possible to get stored procedure debugging to work in VS, it should be possible to get it to work in SSMS.

Maybe stored procedure debugging is not in Yukon simply because of time constraints, and that's understandable. If that's the case hopefully we'll see it in Katmai.

I can say for a fact that at the point in the release where I switched roles out of the tools team that debugging was not in because of time constraints, not some grand plan to make you buy VS. Now that was 2 yrs ago and plans change so I can't speak for the team now.

The other subject that comes up in a similar vein is extensibility, extensibility of SSMS is switched off for a similar reason. That is the time it would have taken to do the extra work to enable the right extensibility at the right quality level was not there, it was always planned to add back in via a future release, but again times and plans change so I have no idea if thats on the radar.

I like to use NUnit for unit testing my T-SQL code, so I extensively use Visual Studio anyway. Also I love to utilize C++ style macros in my T-SQL code, such as

#define IM(A, B) ((B IS NULL AND A IS NULL) OR (A = B))

so I extensively use Visual Studio to utilize macros. Also I think Management Studio is _very_ ureliable, so I avoid using it as much as possible. Because of all that, I am quite satisfied with existing functionality. What am I missing?

I'm surprised you think that Management Studio is _very_ unstable. My experience has been that it's extremely stable and very fast. It opens nearly instantly, even when opening it from double clicking a .sql file. Object Explorer is always instantly responsive. I can't remember the last time it generated an error for me. And, maybe I've adapted to using it, but I find Query Editor a pleasant and productive environment. I'm not questioning your experience, but I wonder what the difference is?

I am interested in hearing more about your C++ style macro within T-SQL. Are these simply text replacement macros?

While I agree it makes sense to have these items in SSMS, the same could be said for the ease of using Data Dude with multiple projects of different types, but all related. For example, Report projects, WinForms apps using the report control, CLR projects, and the DB project can all be contained in one solution using the VS IDE. This would not be possible in SSMS without, I'm sure, quite a bit of work.

I am a database developer to the core, but I will say I like the integration with VS. I was disappointed to not see this capability in SSMS, but so much more can be done and I am more productive in VS due to the complexities of what I develop that is integrated.

There are disadvantages, too, though, like the 128 MB or RAM it uses just to open and the fact that in order to even use Data Dude you have to have VS licensing. Too bad it's not free with SQL Server.

Euan, one thing I would like to see back in Data Dude is the auto generation of CLR binary code when doing a db build using the path.dll form of CREATE ASSEMBLY. It was in there in CTP5 and then got dropped. It now requires extra steps when the underlying CLR code changes to get the binary represenation into the data dude script. I'd also like to see more support for SQL Mobile projects and building it to an SDF directly.

Having said that, why is there such a move to create a distinction between a DB developer and a developer in any other declarative programming language? From my point of view (and 22+ years of experience) T-SQL is simply another language in the developer's arsenal. To distinguish a T-SQL developer as a separate "class" of developer is an artificial (and somewhat narcissistic) point of view.

To develop applications that are effective, the developer should understand ALL layers of his/her application and their strengths/weaknesses. To develop an application without that knowledge is inviting disaster (or at the very least a poorly performing app).

Leave a Comment

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.