Comparing logically adjacent rows in a database table

If you have database table that stores something such as when an action occurred, it might be useful to work out how far apart these events are. It is easy to join different tables together, or even if you have a self-referential join to join rows of the same table together if an existing relationship […]

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 […]

Creating a data dictionary with SQL Server and MediaWiki – Part three: inbound references

In my previous posts (parts one and two), I showed how to generate mediawiki markup to generate lists of tables and columns that can be used as the starting blocks of a data dictionary. In this post I continue that by showing how to generate a list of inbound links (i.e. a list of other […]

Creating a data dictionary with SQL Server and MediaWiki – Part two: a list of columns

In my previous post, I showed how to create a SQL Script that generates Mediawiki mark-up to create a list of tables (or views) in your database. In this post, I’ll continue with generating a list of columns. Script walk through At the top of the script are three variables used to define which table […]

Creating a data dictionary with SQL Server and MediaWiki – Part one: a list of tables

Background We’ve just installed MediaWiki as a documentation tool where I work. To get things up and running as quickly as possible, I created a few SQL Scripts to pull out schema information from the SQL Server in order to create a data dictionary. The SQL Scripts produce WikiMedia mark up that allows various things […]

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 […]

Getting just the columns you want from Entity Framework

I’ve been looking at trying to optimise the data access in the project I’m working on, and the major stuff (like getting a piece of code that generated 6000 queries down to just 7) is now done. The next step is to look at smaller things that can still make savings. At the moment, the […]

Entity Framework query that never brings back data

I was recently optimising some data access code using the Entity Framework (EF) and I saw in the SQL Server Profiler this following emanating from the application: SELECT CAST(NULL AS varchar(1)) AS [C1], CAST(NULL AS varchar(1)) AS [C2], CAST(NULL AS varchar(1)) AS [C3], CAST(NULL AS bit) AS [C4], CAST(NULL AS varchar(1)) AS [C5], CAST(NULL AS […]

If you really must do dynamic SQL…

I may have mentioned in previous posts and articles about SQL Injection Attacks that dynamic SQL (building SQL commands by concatenating strings together) is a source of failure in the security of a data driven application. It becomes easy to inject malicious text in there to cause the system to return incorrect responses. Generally the […]