Subscribe: Steve Bryant - SQL
Added By: Feedage Forager Feedage Grade B rated
Language: English
copy  data  derby  handy  join  oracle  primary key  primary  query  record  reference  server  sql server  sql  tables  users  work 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Steve Bryant - SQL

Steve Bryant - SQL

A Web Programmer's Exploration

Published: Tue, 21 Nov 2017 07:16:51-0600

Last Build Date: Thu, 14 Jul 2011 09:45:00-0600


SQL Excluding Record sets

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

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-0600

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-0600

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-0600

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-0600

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.


Using the Derby Database

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

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.


Join Tables and Primary Keys

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

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.


Traveling Reference

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

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.

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-0600

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.


Avoid Select *

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

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:

SQL Server Comments and ColdFusion MX 7

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

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:


Prevent duplicate Inserts

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

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.

Outer Join on Multiple Fields

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

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:

Join a table to itself in SQL

Fri, 11 Mar 2005 05:59:00-0600

It recently came to my attention that some developers may not know how to join a table to itself. For them, I write this. Why would you want to do such a thing? Perhaps you have a company that is owned by another company, and you want to get any subsidiaries of a given company.

Extracting Date from SQL Server datetime

Fri, 28 Jan 2005 05:59:00-0600

I was working on a project yesterday that required me to get a date from a SQL Server datetime field. I needed it to return just the date (not the time), but for the life of me I couldn't figure it out (don't worry about why, just go with me here).

I called my good friend (and local SQL Server answer-man), Will Spurgeon. As usual, he came through for me. I never would have guessed the answer: