Stored procedures are remains one of a DBA’s main weaponry, there is no doubt in that, but in my opinion there are a few cases where stored procedures are a bad thing.

The first reason is that they lock you down into vendor-specific syntax which ultimately means that your system can only run on a vendor-specific RDBMS. This is fine if you are never planning on creating a shippable product, but if you do intend to create a shippable product the data layer in your product should not be dependent on a specific RDBMS. I am sure there are nuances that separate SQL Server Stored Procedure syntax from Oracle syntax, which means that if you intend to make a product work on say both SQL Server and Oracle you might have to rewrite parts of your stored procedures to be compatible with the specific vendor.

The second reason stored procedures are bad is because DBA’s try to control too much, they tend to sometimes dabble too much and exert too much control, which leads to application inflexibility. I have seen instances where the DBA returned HTML from a stored procedure, which is a total WTF moment. What happens if your data needs to be consumed in a non-web environment, like Windows Forms or WPF? What do you do with the HTML? The point is that the RDBMS is responsible for data integrity and data integrity alone, and not for returning HTML (its the role of the client consuming the data). I always had the impression that some DBAs would even run the web server using SQL Server. I have also seen DBAs place domain specific code in stored procedures. In other words they let the SQL handle business rules, which is inflexible again and it muddies the water in terms of what the code using the data must do.

The third reason stored procedures are bad is that they can become really cumbersome to maintain, in which case it makes better sense to rather have your programming language deal with data access and not have your business rules in stored procedures.

The fourth reason stored procedures are bad is because there is a preconceived notion or idea that they are more secure, which again is a load of crap. The only thing that makes it seem more secure is because the roles for a database have not been properly assigned and that you are trying to be “more secure” by adding T-SQL to apply user-specific rules. I have seen this approach fail where data gets leaked to users that are not suppose to see it because the code in the stored procedure was dodgy, not because the security was inherently bad. You can lockdown roles and their permissions in your database to a reasonable extent.

The fifth reason stored procedures are a bad thing is because they don’t really outperform ADO.NET code.

One of the other things I felt the esteemed devs at the previous company had problems with was fundamentally understanding SQL Server from a technical and ground-level understanding. I don’t think they understood things like indexes very well, because in the database design they only ever used primary keys, and when they used foreign keys, they referred to doing things the ‘relational way’. SQL Server 2008 has certain types of indexes and understanding them, in my view, is central to understanding how your database should be designed. What is a btree?

In computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic amortized time. The B-tree is a generalization of a binary search tree in that a node can have more than two children. (Comer, p. 123) Unlike self-balancing binary search trees, the B-tree is optimized for systems that read and write large blocks of data. It is commonly used in databases and filesystems.

I also recall these ‘DBA’s’ categorically stating that they didn’t like views – without a reasonable argument, which they expected everyone just to accept. Its the same argument style they used when comparing Dell to HP – they expected everyone to accept their argument (or lack thereof) that HP was worse than Dell. Back to the point of why they didn’t like views, which was based solely on some over hyped personal opinion and not some real proof. I just did a quick Google for Views vs Stored Procedures and one of the first links I cam across was an article by Frans Bouma where he lists quite a few points on why stored procedures are bad, and why using dynamic SQL is a better approach. I also found an article by Jeff Attwood where he clearly states that stored procedures should not be used for simplistic needs. Within the previous environment I worked in the devs wrote stored procedures for everything, including basic scaffolding.

Recently I have been dabbling a bit with Ubuntu and I really liked the command line approach because it feels like you got this powerful sword that you can wield around. Linux is great, but I am still a Windows person (bad I know) but its the truth. So I was looking around and reading and at one point today I wondered if you can connect to a Linux shell through Windows Powershell, but it didnt seem to evident. All the thinking and reading got me to search for a Powershell 2.0 “download page”, which doesnt really exist. You have to download an update for Windows XP (if you are running Windows XP like me). Windows 7 has Powershell installed by default. Once you have downloaded and installed it you are ready to use Windows Powershell. I found two items under my Accessories –> Windows Powershell menu:

The first item takes you directly to the command line and the second one takes you to an IDE for debugging and testing your Powershell scripts, which I found useful for syntax checking. My first script was just a test to print some text on the commandline:

To execute you run this in the commandline (if you have saved the script above to a folder called powershellscripts):

The code above was just for myself and so that I could get some of basic mechanics right. My goal was to create a script that could connect to a SQL Server database and retrieve data, and store it. I also found two places useful as reference for examples:

• Hey, Scripting Guy! Blog
• The Script Center

I looked at a few samples and came across a sample that connected to an OLE DB (Microsoft Access) and what struck me was that the code looked a lot like standard ADO.NET code, although there are a few differences. It seems variables are declared with a dollar ($) sign and there are no semicolons (being a C# person that was a surprise). With the sample I started creating my own sample that connected to a SQL Server 2008 Express database:

As you can see from the code parameters are passed with a param(…) and class instances are created with the new-object keyword. If you type a variable it will be printed as is the case with the $tbl variable.

I know there is a whole lot more that can be done, but I was surprised with how easily I could connect to a database.