When working with different iterations of a SQL database running on Internal, Staging and Production infrastructure it can become a pain in the ass rolling out updates at deployment time or keeping them in sync. Developers often use third party tools to help them do this job, however depending on what version of Visual Studio you have installed, there may be another option you have overlooked, and it’s baked right into the IDE.

SQL Express is pretty awesome as a light weight database server, and when Microsoft released it using the same database engine as the full version they did the world a huge favour. I have seen SQL Express in use on a lot of Virtual Private Servers & development boxes, but in most cases users don’t backup their databases regularly because of the missing scheduled jobs functionality that doesn’t ship in the Express version of Microsoft SQL Server. Like most limitations there is an easy way around this.

Having a good Continuous Integration setup is the gift that keeps on giving, but what about your database? For most web applications these days, your database is a large part of your application – so why is versioning it such an uncommon thing? Because it’s time consuming and complicated – two requirements that Continuous Integration love conquering with a one-two punch.

Working with Stored Procedures and functions on a database tier can be time consuming, hard to debug and sometimes just difficult to get clarity on what is “happening”. A rarely discussed feature of Microsoft SQL Management Studio is its inbuilt debugging features. These allow you to see exactly what is going on, and step through your logic in a similar fashion to working in Visual Studio.

I watched an interesting thing take place the other day, in the form of someone using the visual tooling in SQL Management Studio to “Edit Top 200 rows” in a table, then realising that the record they wanted was not there, running a manual SELECT query to find the record, and then running a manual UPDATE command to edit the row. This can all be done a lot quicker using the same visual tooling they used in the first action – it’s just sneakily hidden in SQL Management Studio.

MS SQL Server has a habit of sprouting enormous appendages in the form of log files. Like the infamous Tree Man from Indonesia, sometimes this can be a situation that gets out of control. The SQL error log is one of these sources of pain, but there are a few tricks to pull out of your sleave that’ll save the day.

I have been written a number of posts recently taking a look at Microsoft SQL and some items of it's more obtuse feature set – Microsoft SQL CLR support is one of these hidden gems. Whether you want to add SHA256 hashing to SQL or add managed code features to your database functions (MSMQ support?) this will get you there.

So in today's day and age stored procedures are not a new thing at all. DotNet Developers seem to put themselves in one of two camps, those that think they are awesome and use them for everything, and those that think they only have certain times when they are needed and only use them when the absolute need arises (these days, not regularly).

In the modern world we live in with Web Services, RPC calls and the like, SQL server has had a lot of work on its hands trying to keep its followers from leaving to newer ways to store data such as document databases. Microsoft SQL server is like an old friend that developers have either come to love or hate – but like old friends, they may have been keeping some secrets. One of these hidden gems appears in the shape of native XML output support for queries.

One of the niggling that often happens when your deployed to a cheap shared hosting environment is having your table owners change on you. If you have created a data access layer that references tables using their long name this is an issue. This can be caused by a deployment script not maintaining user permissions on your tables or maybe you’re using a web console to interract with you database like myLittleAdmin and it doesn’t allow you to create a table under any user but your own.

There are quite a few built-in stored procedures in Microsoft's SQL Server that can help you troubleshoot database issues and make those long nights less painful. Knowing they exist can be a life saver, so lets take a look.

It seems we live in an age where a lot of things are taken for granted on the web. Alternative spellings of things being offered to us when we search is a common example of this – simply look at Google and Bing’s “Did you mean X?” and you’ll know exactly what I'm talking about. At first though this can be seem quite daunting for the everyday web developer, but luckily Microsoft SQL server is here to save the day!

Recently I was working on a project where I had to return a simple list of products, and if they had images associated with them, return information about only one along with the product. Times like these, a simple left join or inner join just doesn’t cut it if there are more than one image/record per product. When this happens there is almost always a simple solution, so let’s take a look.

If you have ever had to setup or manage an Umbraco installation you will know both the pleasure and the occasional pain that it can bring. Umbraco is part of the growing list of “oober cool” up and coming Dot Net CMS that are getting attention in the “get it up quick” world of marketing driven sites in the market place (Ford Australia for example). I recently had to upgrade a clients installation from 3.0.5 to 4.0.2.1 and the story that follows will hopefully help someone in a similar position so that they can revel in the same relief that I do currently.

There are many times when there can be a requirement to disconnect users from your Microsoft SQL database. Sometimes you can have bad code keeping connections alive that shouldn’t be, other times your simply want to restore a backup or do something that requires no users to be using the database. Reasons aside, I’ll show you some simple SQL to make it happen.

Today i was battling in the trenches while trying to fix a deployment issue on a foreign webhost with a support team that had a lead time of 3 days on a support ticket when i came across a very annoying issue. While trying to import a large MSSQL script (140mb) i was having a repeated dual to the death with the error “701 Insufficient Memory” and his evil twin (kind of like the white dreadlock guys in the Matrix) - “System.OutOfMemoryException” in SQL management studio. But never fear, there is a solution, and its really simple.