Basic T SQL

Now you know all about Extended Properties, I’ll provide a quick script that allows you to query them effectively and quickly.

This is an obvious continuation of my previous post about Extended Properties in which I will show you how easy it is to add and remove Extended Properties from a table in SQL Server.

These aren’t new functions as they were brought in for SQL 2012, but I was using them the other day in a SQL 2014 environment and the person I was talking to at the time had never heard of them, so I thought I’d make mention of them here in case other people hadn’t heard of them either.

In some code I was writing recently I needed to perform this action which, having had a quick look on the internet, seems to be a topic that annoy people as it’s not something natively built in to SQL Server and to obtain it yourself can be a pain.

This is a direct follow on from my previous post really as one of the most common uses for the Table Type is that you can use it (in SQL Server 2008 onwards) to pass a table as a parameter into a Stored Procedure. This is incredibly useful as one of the things that I see most often asked on the internet is how to pass tabular data to a stored procedure.

This was something which I used recently within my own personal work at home because I was writing a stored procedure which involved regularly creating tables with the same structure.

Well, after a short break let’s start the new year with a nice and short post.


This one is simply because I was reviewing some code for someone the other day as they had an issue they couldn’t track down and, as it turned out, it was something that I’ve definitely been stung by in the past so I thought I’d make a note here.

Something that came up the other day when talking to a developer was that they were unsure as to whether or not they could have a foreign key relationship to a table when they didn’t necessarily have a foreign key value for all records.

I would imagine that most people tend to write “set nocount on” at the start of most code blocks and procedures as standard these days but, having been caught out with this recently, I have found a new “set” command that I’ve started to add to my code as well.

Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.

Now to all those people saying “well, duh”, this clearly isn’t a post for you, but this is genuinely something I didn’t realize until I saw it the other day and went to try it out for myself.

This is another new function in SQL Server 2012 which is incredibly useful and I’ve been using everywhere I can in place of the previous ISNULL or COALESCE functions.

Something I’ve had to do a lot recently is to calculate financials which involved using an end of month date. This has always been a pain to calculate in SQL Server. There are a lot of ways of doing it, but all are annoying.

Following on from my last post really, this was another aspect of the MI I was helping out with which benefitted from the use of some more modern code (SQL 2012 or above) in order to make code both clearer and more concise. It is also a lot faster and less strain on the server using the newer method.

Again, this is something that I decided to write about because I see code in a lot of places used in a lot of different ways and within most MI there is always a need to obtain a percentage figure across a dataset and, for some reason, no-one seems to use SUM with OVER but rather they choose an older, longer method.

I’ve been in numerous situations now in which people require security in their SQL Server (obviously) but find that the provided db_dataReader and db_dataWriter aren’t quite good enough. They require execute rights on all procedures, but find this is hard to set up.

I was looking for some sample code to help me with a personal C# application I was coding and in looking around I saw a perfect example as to what not to do when manipulating a database.

I will presume that you know the most common numerical datatypes (if not, please refer to the list here).

IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.

This is a key phrase when referring to any aggregate function as all aggregate functions require grouping in order for the data to have any meaning. As such this is a mandatory key phrase to be used within all aggregation.

Okay, this time an UPDATE simply won’t suffice. Something is badly wrong and we need to remove a record rather than just update it.

This article is a simple and brief introduction to the INSERT statement within SQL Server. This, as the name suggests, is the method used to enter data into our databases.

For this example we'll use a Person table which you'll need to create using the following script:

This article is a simple and brief introduction to the SELECT statement within SQL Server. This is the most basic and most useful tool in the SQL language. It allows us to see what data is being held in our databases.

Okay, we’ve inserted data, selected data, but what happens if we find some of our data is wrong? That’s where we need to UPDATE our records.

Within SQL Server there is a very useful clause that you can combine with all statements (INSERT, UPDATE, DELETE) in order to provide further information or details as to what’s happening… that is the OUTPUT statement.