Creating and Editing Stored Procedures

A pesky problem with the Server Explorer's stored procedure-management interface is that it has not really evolved very much since the Visual Basic days. There still is no way to categorize procedures into logical groups, and it does not differentiate between the stored procedures you write or are installed by Visual Studio or other "systems." This means the list of stored procedures you're forced to work with can be a seemingly endless list that scrolls off the screenmany of which don't really apply to your application or your current focus. Yes, I've seen how some application databases have dealt with this shortcoming by renaming stored procedures to facilitate their management. For example, "Accounting_FindRows", "Inventory_FindRows", etc. procedure names make it easier to locate application-specific or function-specific procedures in the list of stored procedures visible in the Server Explorer, but you still have to scroll through these to find the right stored procedure. Before long, you'll find that not all of the stored procedures are "visible" to your login credentials. That's because I've been encouraging DBAs to hide everything and expose only specific objects to developerson a "need to know" basis only. If your login credentials do not have rights to an object, SQL Server hides it from you. This problem has been handled quite elegantly in SQL Server Management Studio by exposing a "Filter" on the stored procedure list so you can filter (in) just those stored procedures you're working with.

Comparing SQL Server 2005 and Visual Studio 2005

Again, the SQL Server and Visual Studio teams have fallen (well) short in their attempt to integrate their UIs, so the SQL Enterprise Studio's Object Explorer and Visual Studio Server Explorer work and look about the same. As it is, they use different (albeit similar) metaphors to organize and manage the objects in SQL Server databases. I think, over time, you'll agree with us that the SQL Server Object Explorer is better suited to locate and manage stored procedures. I expect that the Visual Studio UI has been genericized and "dumbed down" to provide a level of compatibility between the various "foreign" (third-party) data sources it has to support. Consider that the Visual Studio tools had to adapt to non-SQL Server DBMS engines like Oracle and JET, as well as the OSFA^[1] interfaces like ODBC and OLE DB. Not being a fan of OSFA UI, I'm not thrilled with this approach. Check out the side-by-side comparison of the two UIs shown in Figure 5.1. It's a shame these teams could not have come to agree on a solution that better mapped the SQL Server objects.

^[1] OSFA: One size fits all.

Figure 5.1. SQL Server 2005 Object Explorer and Visual Studio 2005 Server Explorer.

The serious problem (that I've mentioned before) is that the Server Explorer does not expose the Security tab, so you won't be able to use the Server Explorer to manage logins, users, schemas, or rights without switching to an entirely separate program. The Server Explorer does permit you to alter CHECK Constraints (via the database diagram), but it does not support Rules (which Microsoft no longer wants to support) or Defaults (which it does support). No, it just looks like they ran out of time and could not get all of it doneI guess that almost four years wasn't enough. (Or is it eight years since the Server Explorer was first introduced in Visual Studio?)

Enter the SQL Server Management Studio Express

The newest member of the SQL Server tool suite is SQL Server Management Studio Express. When Microsoft first announced its new tool for the SQL Server Express edition, they were panned by me and the rest of the pundit community. Generally, I wanted something that was virtually identical to the SQL Server Enterprise Studio toolset but gated to the Express featuresand worked. The prototype tools were pretty shabby. I was concerned that a developer would have to learn two entirely different sets of tools to do basically the same thing.

Basically, we (all) got what we asked forthe SQL Server Management Studio Express is a clone of the SQL Server Management Studio that ships with the other versions.