Querying Extended Properties
Produced: 18/12/2016 11:58:00
Now you know all about Extended Properties, I’ll provide a quick script that allows you to query them effectively and quickly.

Continued...
Adding and Removing Extended Properties
Produced: 11/12/2016 11:54:00
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.

Continued...
Lead and Lag Built In Functions
Produced: 03/04/2016 17:48:00
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.

Continued...
Obtaining the Max Values Across Columns
Produced: 27/03/2016 17:45:00
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.

Continued...
Passing a Table to a Stored Procedure
Produced: 13/03/2016 11:47:00
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.

Continued...
Table Types
Produced: 06/03/2016 11:42:00
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.

In the end I got annoyed with copy and paste and decided to use a Table Type as a template.

Continued...
Quick Warning about Variables and Truncation
Produced: 21/02/2016 08:48:00
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.

Continued...
Foreign Keys Are Allowed to be Null
Produced: 07/02/2016 06:31:00
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.

Continued...
Datepart DW Giving Inconsistent Results
Produced: 31/01/2016 22:13:00
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.

Continued...
Quick Case Statement Quiz
Produced: 29/11/2015 11:44:00
Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.

However, there are those instances in which I see it used incredibly badly so I thought I’d make it into a quick quiz. (Although it’s not really long enough to be a quiz, and it contains answers… therefore more just me showing examples really).

Continued...
CTEs are Updateable
Produced: 22/11/2015 11:31:00
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.

To be honest it makes perfect logical sense that it works because a CTE is effectively just a nicely formatted subquery or derived table, but I’d never even thought to use it this way.

Continued...
Using CONCAT
Produced: 25/10/2015 09:43:00
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.

Continued...
Easily Calculating End of Month
Produced: 04/10/2015 16:54:00
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.

Luckily that’s now changed, but I found that a good few people didn’t seem to know that.

Continued...
Calculating Cumulative Totals
Produced: 20/09/2015 14:55:00
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.

Continued...
The Best Way to Obtain Percentages
Produced: 13/09/2015 14:17:00
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.

Continued...
Grant Execute To All Procedures
Produced: 07/06/2015 07:34:00
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.

Continued...
Basic Warning About UNION
Produced: 05/10/2014 10:28:00
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.

To be honest the C# code itself was pretty ropey, but aside from that I could see what the guy was trying to achieve but the t-SQL he was using would likely let him down at some point due to his misuse of “UNION”.

Continued...
Numeric DataTypes
Produced: 17/03/2013 09:50:00
This article will simply cover a basic manipulation of some numerical datatypes to highlight some key uses and pitfalls.

Continued...
How to Create a Database Using T-SQL
Produced: 24/02/2013 10:26:00
This article will show you how to create your very first database using T-SQL code. This is a very simple process and will allow you to then move on to creating tables and other objects in which to store your data.

Continued...
Basic GROUP BY Usage
Produced: 10/02/2013 09:45:00
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.

Continued...
Basic DELETE statement
Produced: 03/02/2013 20:26:00
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.

Continued...
Basic INSERT statement
Produced: 27/01/2013 14:59:00
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.

Continued...
Basic SELECT statement (with WHERE clause)
Produced: 20/01/2013 17:28:00
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.

As the name implies, we simply SELECT that data from a table. It’s that easy.

Continued...
Basic UPDATE statement
Produced: 13/01/2013 11:29:00
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.

Continued...
Basic OUTPUT statement
Produced: 06/01/2013 20:26:00
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.

Continued...