Steve Bryant - SQL

Return a list of related records in SQL Server 2017

Tue, 04 Feb 2025 11:00:00-0500

I absolutely love SQL (especially SQL Server) and I love how it continually gets better. A little over four years ago, I wrong an entry on how to return a comma-delimited list of records in SQL Server. It turns out, that it is now much easier to do. SQL Server 2017 introduced the STRING_AGG function. For those of you doing math, it would have actually been available when I wrote the previous entry, but we'll just ignore that and I'll use the same example. For example, if you have users that can work in multiple regions and you need a list of which regions they work in. [More]

Return a list of related records in SQL Server

Wed, 21 Oct 2020 10:45:00-0500

I absolutely love SQL (especially SQL Server) and I love how I continually find new ways to solve problems with it. I wish I could remember where I first found this tip to give proper credit, but I really love it. Have you ever wanted to return a comma-delimited list of records in SQL Server? It turns out, that it is actually pretty easy to do. For example, if you have users that can work in multiple regions and you need a list of which regions they work in. [More]

SQL Excluding Record sets

Thu, 14 Jul 2011 09:45:00-0500

I was eating with another speaker at cf.Objective() (I am resolved to find a way to work my having spoken at a conference into all future anecdotes) and an interesting SQL question came up: How to delete everything except the first 1000 records. To my mind, this brought up a general class of problems in SQL. Which is, returning results that exclude the result of a query. With that in mind, let's look at a few of those. [More]

Many-to-Many Relationships and All

Wed, 09 Jul 2008 07:00:00-0500

Most of us have deal with many-to-many relationships from time to time. This is generally easy. What happens, though, when the client wants some items to be available to all members of the related table? For example, I might have some articles that are only available to users with certain permissions. If that were the case, then I might have an articles2permissions table. Then I might pass in a comma-delimited list of permissions when retrieving articles: [More]

Slow Table in SQL Server? Re-Index it!

Wed, 18 Jun 2008 07:30:00-0500

One of my sites has started getting sluggish lately. I thought it was just that the traffic was increasing and so I should optimize my code. Then, in the last couple of weeks, I had a 3AM scheduled task crash a few times. This weekend, I had a simple query with one row and one column take several minutes to execute even from the SQL Server Query Analyzer. This is getting weird! [More]

Trouble with Triggers

Fri, 21 Mar 2008 07:00:00-0500

I was trying to create a trigger in Oracle last night with cfquery and it wouldn't work. Oracle kept reporting that the trigger was invalid. If I copied the same exact SQL into Oracle directly, it would work. When I would look at the invalid trigger in Oracle and copy the SQL from there into Oracle, it would fix the trigger, but still I couldn't get it to work from there. [More]

Copy Legacy Data with SQL

Fri, 01 Feb 2008 14:00:00-0500

In talking to a friend of mine yesterday, I realized that a great many programmers are not aware of one of the nicest features of SQL, the ability to insert the results of a query into a table. This is really handy for copying data from an old format to a newer format or if you have a need to denormalize part of a database.

[More]

Using the Derby Database

Fri, 08 Jun 2007 13:37:00-0500

When I read Ben Forta's announcement (and follow up) that ColdFusion 8 would ship with support for the Derby database, my first thought was "I should add support for that in DataMgr". This proved to be a good introduction to Derby.

I decided that I would get my local copy of my demonstration site running on Derby.

[More]

Join Tables and Primary Keys

Thu, 24 May 2007 13:44:00-0500

I have found that many people set up the primary keys for join tables in a manner that I find undesirable. This leaves open the possibility of some sneaky bugs in their programs.

I'll start by covering what I mean by a join table (as opposed to what I will call a data table).

Most basic data is stored in data tables. For example a "users" table is a data table, as is a "groups" table. Each row in a "users" table represents one user, as each row in a "groups" table would represent a group.

[More]

Traveling Reference

Fri, 26 Jan 2007 19:29:00-0500

Ben Forta is asking for input on his next CFWACK book. The discussion in the comments reminded of the old CFML Reference that I keep with me. It is one of a few books that I always have near my computer when I travel.

CSS Pocket Reference (outdated, but still handy when I can't remember some bit of syntax)
ColdFusion Quick Reference Guide to CFML (no longer in print, but still handy to jog my memory a bit)
Regular Expressions in 10 Minutes (Forta strikes again!)
SQL Pocket Reference (perhaps the least used of the bunch, but I still like to have it)
The Elements of Style (I don't write the copy, but it has still been handy)

These books taken together weigh less than a pound and take up less space than your average computer book. They have still managed to be tremendously useful when I am working on the road (especially if I am trying to get some work done with no internet connection).

Anyone else have suggestions for books that they don't leave home without?

SQL to Put Record for Current User First

Tue, 09 May 2006 21:01:00-0500

I was recently asked how to show a record for the current user before the record for other users - in one query. Here is my solution.

[More]

Avoid Select *

Mon, 17 Apr 2006 13:41:00-0500

A recent discussion on CF-Talk implied cfqueryparam as the culprit in a query problem. It seems, however that the problem only happens when using cfqueryparam in conjuction with "select *".

In the discussion, Ben Nadel pointed out reasons he recommends listing out columns instead of using of "select *". His words:
[More]

SQL Server Comments and ColdFusion MX 7

Mon, 27 Jun 2005 00:03:00-0500

This problem (and solution) comes from my good friend Will Spurgeon of Tech-It-Easy. He has long been using SQL comments when developing for SQL Server. The advantage of this approach is that you can see these comments when reviewing transactions in SQL Server Profiler. It can really help in a lot of trouble-shooting situations.

For example:

[More]

Prevent duplicate Inserts

Wed, 16 Mar 2005 13:46:00-0500

I frequently need to insert a record that relates to tables to each other (or relates one table to external data) and I cannot have a duplicate record. Here is my method for doing this in case it helps anyone and as a convenient reference for myself.

Let's suppose that you have three tables: Users, Classes and Students. Classes has a primary key of ClassID (int). Users has a primary key of UserID (int). Students has a two-column primary key of ClassID and UserID which relate to the corresponding columns in Classes and Users respectively.
[More]

Outer Join on Multiple Fields

Mon, 14 Mar 2005 22:37:00-0500

I ran into a situation where I needed to do an outer join on more than one field (if only SQL supported such a thing). After banging my head against a wall for a bit, I asked the CF-Talk list.

Lo and behold! SQL does support exactly what I want. I was just to dim to try it myself. Mark Gaulin provided the answer:
[More]