Aggregate of columns, not rows

In SQL Server it is very easy to aggregate a value across many rows. You simply just write something like this: SELECT MAX(SomeColumn) FROM SomeTable This will return one row with the aggregate value (in this case MAXimum) of the column. However, what if you want to do this across columns. What if you have […]

Getting Entity Framework to Pick up on the Auto-generated Part of the Composite Primary Key

In my previous post, I wrote about how to get SQL Server to automatically generate a composite primary key for a table when part of that key is also a foreign key to a parent table. That all works nicely in SQL Server and using regular ADO.NET commands from a .NET application. However, as the […]

Composite Primary Keys Including Identity Like Column

I’ve been thinking of a way to organise some data for a multi-tenanted system. As such the database that would be used would have to mark pretty much every piece of data with the id of the tenant in some way. This got me thinking about using composite primary keys on tables. Using a composite […]

Optimising clustered indexes in SQL Server 2008

I’ve just found a script on another blog to go through all the clustered indexes in a SQL Server database and rebuild them in order to reduce fragmentation and improve the disk IO needed to get the data. The original script didn’t take into account tables in different schemas so I updated it. I also […]

Rewiring the users and logins in SQL Server

As a developer I find that I’m frequently backing up and restoring SQL Server databases between servers for development and testing purposes. However, each time I do the link between the login (a server concept) and the user (a per database concept) gets broken. There is a stored procedure in SQL Server to wire it […]

Running Queries on Excel Spreadsheets using SQL Server 2008

I’m more a database person than a spreadsheet person. I’m more used to using SQL to bend data to my will than all the fancy gubbins that you’ll find in Excel. With some chunky (for a spreadsheet) ad hoc data in hand I set about connecting it up to SQL Server so I could run […]

Visual Studio / SQL Server install order on Windows 7

Quite a while ago I blogged about the Visual Studio / SQL Server install order on Windows Vista. I’m about to go through a similar exercise on Windows 7 and given the issues I had then I thought that it would be only right to document the procedure in case any problems arose. Last time, […]

Developing with SQL Server 2008, deploying on SQL Server 2005

I received the following by email today: Hi Colin! I found your blog after googling for a bit about SQL Server. I had a question for you… As someone fairly new to .NET development, would it be easier to stick with SQL Server 2005 for now, or just install SQL Server 2008 express? I ask […]

Where's My Data? An introduction to Spatial Queries in SQL Server 2008

The slide deck used for my presentations to the Scottish SQL Server UG on 13/Feb/2008, the North-East Scotland .NET User Group on 20/Feb/2008, VBUG Manchester on 24/July/2008, and SQL Bits III on 13/Sept/2008. There is also a set of demo code to go along with the slide deck variants. Further information The following blog posts […]