Tag: SQL
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...
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...
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...
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...
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...
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...
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...
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...
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...
Tip of the Day #4 (Connection Strings in Config files)
From .NET 2.0 onwards a new and improved configuration management system has been put in place. You can now add a <connectionString> element to the config file...
Creating Many-to-Many joins
A topic that comes up from time to time in forums is how to join two tables together when there is a many-to-many relationship. Typical examples include...
Spatial Operations in SQL Server 2008 (Katmai) - Union and Convex Hull
CODE EXAMPLES IN THIS POST WORK WITH THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008. Say you would like to create a polygon out of a group of points. One way...
Getting started with Spatial Data in SQL Server 2008
THIS POST REFERS TO THE NOVEMBER 2007 CTP (CTP 5) OF SQL SERVER 2008 This post is probably going to be a wee bit random. After the running around over the last...
What is a DAL (Part 4)
As has been mentioned previously, one of the purposes of the DAL is to shield that application from the database. That said, what happens if a DAL throws an...
Normalising the data model
Sometimes I see on forums someone who is trying to get some SQL statement to wield data in a particular way but the data model is just thwarting their...
Types of join
Occasionally there is a post on a forum asking what a certain type of join is all about, so I thought it would probably be good to have a stock explanation to...
SQL Injection Attacks and Some Tips on How to Prevent Them
Introduction Security in software applications is an ever more important topic. In this article, I discuss various aspects of SQL Injection attacks, what to...
The Stored Procedure Now Runs How Fast?
On our test server the stored procedure ran acceptably in a few seconds so it came as somewhat as a shock when trying the application out on the client's test server that the same code was timing out after 20 minutes...'
The benefits of Stored Procedures
There are lots of books that discuss getting data in and out of databases and the best ways to do that. Many advocate the use of stored procedures to ensure the safety of the data...
SQL Injection Attacks
There is also a proportion of people responding to these questions that give advice that opens up gaping security holes...'
