I recently came up against a SQL Server instance that wouldnt start, after going through the event log the reason it was having problems was that it had been set to a max memory of 10mb.

At this point I was unable to change the max memory setting because I couldnt bring the instance online. SQL Server has a minimal configuration argument you can supply whereby it ignores most configuration settings and comes up in default mode. I was able to get the instance up in single user mode using this setting, at which point I changed the max memory back to a sensible value and restarted the server in normal configuration mode and all was good again.

If for any reason you can’t start a SQL Server instance due to a configuration change you can start it in minimal configuration mode so you can fix the offending settings by doing the following…

1. Log on to the computer running the SQL instance
2. Open the SQL Server configuration manager
3. In start up arguments add a new one of -f
4. Start the instance
5. Change any settings that need changing
6. Remove the -f argument from startup parameters
7. Restart the instance

Imagine we have the following table

Users

Then imagine we want Username to be unique for non deleted users. Normally we would make a field unique by doing something like this…

ALTER TABLE dbo.Users ADD CONSTRAINT uq_username UNIQUE(Username)

This will fail as multiple deleted users can have the same username in our system. To make a constraint that only constrains a subset of data we need to use a filtered index and make that unique, in the example above that looks like this

CREATE UNIQUE INDEX ndx_non_deleted_username ON dbo.Users(username) WHERE Deleted = 0

We can then do this without any errors

INSERT INTO dbo.users(username,deleted)
VALUES('gavin',1),
        ('gavin',1),
        ('gavin',0)
        

It will also successfully error if we try to create a second non deleted user with the same name

INSERT INTO dbo.users(username,deleted)
VALUES('gavin',0)

SQL Server 2012 introduced IIF and CHOOSE functions and I completely missed they even existed until recently. They make some quite messy CASE statements go away. Lets have a look…

Let’s imagine we have a need to display something different in our select depending on the value of a field in a table. In the case of this example I’ll use a variable rather than a table. If my variable is equal to ‘Test’ then I want to return ‘Woop’ if it’s not I’ll return ‘Oh No’. I’ve always used CASE statements for this previously and that looks a bit like this…

DECLARE @Testing NVARCHAR(4) = 'Test'
SELECT CASE WHEN @Testing = 'Test' THEN 'Woop' ELSE 'Oh No' END

This is fine but lets see if we can reduce some of the clutter with IIF. IIF takes an expression and true/false values, If the expression evaluates to true then the true value is returned else it’s the false value…

DECLARE @Testing NVARCHAR(4) = 'Test'
SELECT IIF(@Testing = 'Test','Woop','Oh No')

Let’s now turn our heads to the true function. Time for another example…. Let’s imagine we have a forum and we store the amount of posts each user has made, we then convert that into a level 1-5. We then want to return a status for each user depending on their level. Using a CASE statement we could do this…

DECLARE @UserLevel INT = 4
SELECT 
	CASE @UserLevel 
	WHEN  1 THEN 'Newbie'
	WHEN 2 THEN 'Regular'
	WHEN 3 THEN 'Chatterbox'
	WHEN 4 THEN 'Solcial Butterfly'
	WHEN 5 THEN 'Admin'
END

The CHOOSE statement can massively reduce the code needed to do this, It takes an index and a list of items, it then returns the item at the specified index or NULL if there is no item at that index…

SELECT CHOOSE(4,'Newbie','Regular','Chatterbox','Solcial Butterfly','Admin')