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.

I am still frustrated and angry with my previous employer, simply because they did not offer me anything new to learn at all. I don’t know why I still carry this frustration, but I do, so maybe I need to use this energy and write about the things that bothered me so much and propose a better way.

Their programming techniques were questionable to say the least, from having upper case method naming conventions right through to having no real clue about things, and claiming they did. The thing that was the most surprising was that through their cluelessness, they actually thought that what they were doing was right. What gives me the right to say I am right? Not much really, apart from the fact that I did study some and I feel that what I was taught was completely ignored by these ‘experts’. Some are claiming to be Senior Web Developers with 6 years experience – yet they have only worked with Microsoft Technologies, and they have only worked for one organisation their entire career, so who really measures their seniority, apart from them? Claiming to be a Senior Web Developer using VB.NET and not knowing a quarter of the language’s features does not entitle you to seniority either. The DBA’s also know little outside of SQL Server and when asked about MySQL they tend to be smart about it as if SQL Server is the only piece of RDBMS software written. I have written before that these people talked about “doing things the relational way” when they used SQL Server, and if that is not a red light to anyone who has done some computer science, then not much will be. The fact is they pretty much did self appointed ‘DBA-mastery’ when what they really did was write little more than messy T-SQL Scripts, which included generating HTML in some cases (shocking!). The T-SQL often used concatenation to generate results and was difficult to read, to say the least, and because they were able to write and execute these scripts, they thought they were awesome and wise. Yet data often leaked to clients that were not suppose to see it, which makes me wonder.

In my first attempt at dealing with the frustration I want to list the things a SQL Server DBA should now, which I know they did not. First and foremost they relied almost entirely on learning by Google, in other words whenever an error occurred, Google was their first source. They had no real system for breaking the errors down, which ultimately meant they did not have enough knowledge or expertise of SQL Server, yet they called themselves DBA’s. Asked what CLR stood for none knew either. I know normalization can be both good and bad – but throughout their database design(s) they did not do any normalization and I am willing to bet that if you asked them to normalize they would a) not be able to recite the normalization theory b) apply normalization, primarily because they tend to think that a certain way of feeling or thinking is going to result in a better ERD design. I saw in at least one instance where their ‘way of feeling’ failed, and where normalization would have helped a great deal. Other things that seemed strange to me was that they used the ‘sa’ user as login, as well as “views” simply being deemed “bad” with no real good reason.

So what are some of the better practices? I did a Google for some SQL Server best practices and came across this MSDN article and relating back to the point I made about the ‘sa’ user, on Page 18, of this document:

• Have distinct owners for databases; not all databases should be owned by sa.

Why is the sa login such a vulnerability though? I bet none of the guys at the previous job can answer that since they use sa as a login for everything.

Ok so I have written my first CLR project, but there are a few things I didnt do right and which I know requires a bit more knowledge and effort. Firstly, the context-connection seems to be only applicable to the SqlPipe class. I tried using an SqlConnection that used a regular connection string, but I got an error stating that SqlPipe.Context can only be used with context-connection = true. Allrighty then. Coming to think of it I did not do much wrong in terms of data access, because I used stored procedures that already existed. Anyway…

My main problem was that I needed a connection string and I didnt know how to get it. To get a connection string into a CLR project you have these choices:

• Use an App.config file. This is not really recommended in a production environment because you will have to restart SQL Services for SQL Server to read the values in the config file. If you plan on using a config file, you will have to place the config file in the root folder of the SQL server instance. You get this by looking at the properties of the database (look for Root folder), and naming the file something like sqlsrv.exe.config.
• Hardcode the connection strings. This is hardly a good thing to do
• Use context connection = true. Again though you need to be aware that the usage for this connection type differs from a regular connection string. Read the MSDN article here
• Use some external file, plain text, xml, etc. This means however knowing a part of the directory structure the machine/server resides on.
• Could you use a web service? Haven’t really thought this one through yet. But could you hypothetically speaking use a web service to initialise a connection string? I’m not sure
• Use a scalar user defined function and pass the database details as parameters, and run the function as a Job. This is the method I chose.

In one of my recent projects I learnt how to use CLR stored procedures. So what exactly are CLR stored procedures? Well they allow you to run code (C#) inside SQL Server 2005. So instead of creating stored procedures or UDF (User Defined Functions) with SQL code you use C# code to achieve this. There are various scenarios where you would want to do this, and here are a few reasons why:

• Complex code – C# (or any CLR language) has much better support for doing complex string manipulations or hectic calculations than SQL Server does. SQL server does not have support for generics and collections, for example, and to be honest Lists and Dictionaries are very convenient structures
• Functionality that extends beyond the scope of SQL Server 2005. I had to, for instance, check a POP3 email account and update DB values based on the results from checking the POP 3 account. The POP 3 functionality does not exist, and cannot be replicated with plain SQL Code.

There are however a few instances where I think you should not use CLR stored procedures:

• When the assemblies you are adding are not tested and supported. What I mean by this is that for example System.Drawing is considered ‘unsafe’, and you have to wonder why MS do not make it easy to integrate this. Personally I think you should stick to things that are supported, and tested.
• If you’re CLR sproc does data access (selects, updates, inserts) or simple queries. SQL Server 2005 is a very powerful piece of software

I also found that by assigning my CLR sprocs to Jobs I could leverage off the power that SQL Server 2005 provides, and let my sprocs run at intervals without having to write a windows service.

So during the week I had a situation where I had to take an existing web application, that used SQL Server Express, and had to make it work with SQL Server 2005 Standard Edition. Both versions of SQL Server form part of the SQL Server SKU line of products, but their implementation in certain scenarios differ. The providers that are used to connect through your applications are the same though.

I used Visual Studio 2005 to develop the initial application, and in many ways Visual Studio is awesome but in other ways its not, and specifically with regards to SQL Server databases its not that great. Visual Studio allows you to simply drag and drop databases into your web application project, which is great for productivity, but it may cause you some headaches later on. If you have SQL Server Express running on a local machine you will note that a database created in Visual Studio will not show up in SQL Server Management Studio Express. Visual Studio creates the database for you, but it does not attach it to the SQL Server instance that you are running in SQL Server Management Studio Express. SQL Server Express uses a special mode of operation called user instancing, which means it uses accounts such as NETWORK SERVICE and ASPNET to give it rights to connect to the SQL Server Express Instance. SQL Server Express Instancing also adds a 45-75MB memory overhead.

During the week I have been working with SQL Server 2005 Standard Edition and 2005 Express Edition for a website. I have specifically been looking at using SQL Server 2005 for Authentication as opposed to using the standard authentication that comes with ASP.NET when you configure an ASP.NET application in Visual Studio 2005. When you enable authentication for your website an SQL Server Express database (MDF file) is automatically created and the connection string in your web.config file is also changed to point to that SQL Server Express database.

Why is the standard authentication model not appropriate for all uses?

A couple of reasons I can think of:

1. If you wanted your development environment and your application environment to work together seamlessly then you might want to consider using SQL Server Authentication, or at least get both environments to use connection strings that are similiar or the same.
2. If you wanted to change or add to the fields provided by the regular membership model provided in ASP.NET and in particular have those fields stored in a proper database table.
3. If you wanted to use a Database Management System other than SQL Server. Perhaps you want to use MySQL or MS Access. You can also use Active Directory and XML as an authentication model.