CodeProject: SQL Tips

CodeProject: SQL Tips

JOIN Instead of Repeating a Subquery

There is sometimes a need to JOIN to a table, but only include the first result that matches the JOIN condition so the result records aren’t duplicated by the JOIN. The typical way to do that is to use a subquery. However, if the fields on the table are used in more than one place, that can become cumbersome to add the same subquery several times, as in the following example:

-- Some example table variables with sample data.
DECLARE @FirstTable table(FirstName varchar(20))
DECLARE @SecondTable table
    (ID int,
    FirstName varchar(20),
    LastName varchar(20),
    FirstNameHashCode varchar(20),
    FirstNameFrequency int)
INSERT INTO @FirstTable(FirstName) VALUES('Billy')
INSERT INTO @FirstTable(FirstName) VALUES('Super')
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(1, 'Billy', 'Mays', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(2, 'Billy', 'TheKid', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(3, 'Super', 'Man', 'SJ3JD', 40)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(4, 'Super', 'Friends', 'SJ3JD', 40)

-- Multiple subqueries.
SELECT
    (
        -- Subquery is returning one column.
        SELECT TOP 1
            ST.FirstNameHashCode
        FROM @SecondTable AS ST
        WHERE
            -- Match condition.
            ST.FirstName = FT.FirstName
    ),
    (
        -- The same subquery, except it's returning a different column.
        SELECT TOP 1
            ST.FirstNameFrequency
        FROM @SecondTable AS ST
        WHERE
            -- Match condition.
            ST.FirstName = FT.FirstName
    ),
    (
        -- The query appears a third time, with the randomly chosen row.
        SELECT TOP 1
            ST.ID
        FROM @SecondTable AS ST
        WHERE
            -- Match condition.
            ST.FirstName = FT.FirstName
    )
    -- You'd have to repeat the entire subquery for each new column returned.
FROM @FirstTable AS FT

A better (at least in non-contrived cases) way to do this is to use what I call a “TOP 1 JOIN”:

-- Some example table variables with sample data.
DECLARE @FirstTable table(FirstName varchar(20))
DECLARE @SecondTable table
    (ID int,
    FirstName varchar(20),
    LastName varchar(20),
    FirstNameHashCode varchar(20),
    FirstNameFrequency int)
INSERT INTO @FirstTable(FirstName) VALUES('Billy')
INSERT INTO @FirstTable(FirstName) VALUES('Super')
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(1, 'Billy', 'Mays', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(2, 'Billy', 'TheKid', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(3, 'Super', 'Man', 'SJ3JD', 40)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
    VALUES(4, 'Super', 'Friends', 'SJ3JD', 40)

-- Uses a single JOIN instead of multiple subqueries.
SELECT
    -- Three different columns are used from SecondTable.
    ST.FirstNameHashCode,
    ST.FirstNameFrequency,
    ST.ID
    -- More columns could be added without changing the below JOIN.
FROM @FirstTable AS FT
JOIN @SecondTable AS ST
    -- Step 1: match condition is here so the query isn't slow (e.g., so indexes will be used).
    --         This step could be skipped, but it would hurt performance.
    ON ST.FirstName = FT.FirstName
    -- Only include the first record, chosen via "TOP 1".
    AND EXISTS
    (
        SELECT
            -- Return some junk data so EXISTS will pass.
            0
        FROM @SecondTable AS STMany
        JOIN
        (
            -- Step 3: only select first record that matches the condition.
            SELECT TOP 1
                *
            FROM @SecondTable AS STInner
            WHERE
                -- Match condition.
                STInner.FirstName = FT.FirstName
        ) AS STTop1
            -- Step 4: only return record if the record matches the TOP 1 record (via the primary key).
            ON STMany.ID = STTop1.ID
        WHERE
            -- Step 2: find the record associated with step 1 (via the primary key).
            STMany.ID = ST.ID
    )

There is a simpler version, but this version works with tables that have primary keys composed of more than one column, so it is more generally applicable. The above example code is entirely self-contained, so go ahead and give it a run to see what it returns.

String Concatenation in Transact-SQL

Posted as an alternative to another member's tip, so it starts mid-conversation.

I like the original solution, especially when compared to this (which I am posting just to show that there are other solutions):

-- Initialize table.
CREATE TABLE #BigStrings
(
	StringID bigint IDENTITY(1,1) NOT NULL,
	StringValue text NOT NULL,
	CONSTRAINT PK_BigStrings PRIMARY KEY CLUSTERED
	(
		StringID ASC
	)
) ON [PRIMARY]

-- Some sample data.
DECLARE @SampleStrings AS TABLE (StringID int IDENTITY(1, 1), StringField varchar(100))
INSERT INTO @SampleStrings
SELECT SampleValue FROM
(
	SELECT 'a' AS SampleValue
	UNION ALL SELECT 'b'
	UNION ALL SELECT 'c'
	UNION ALL SELECT '123'
) AS SampleTable

-- Variables.
DECLARE @StringID AS bigint
DECLARE @StringPointer AS binary(16)
DECLARE @StringValue AS varchar(8000)
DECLARE @StringOffset AS int
DECLARE @StringLength AS int

-- Initialize.
SET @StringOffset = 0
SELECT @StringLength = SUM(LEN(StringField)) FROM @SampleStrings

-- Pre-allocate the required string length.
INSERT INTO #BigStrings(StringValue) VALUES(REPLICATE(' ', @StringLength))

-- Get pointer to text.
SET @StringID = SCOPE_IDENTITY()
SELECT @StringPointer = TEXTPTR(StringValue)
FROM #BigStrings WHERE StringID = @StringID

-- Loop through each input string segment.
DECLARE SampleCursor CURSOR FOR
SELECT StringField FROM @SampleStrings
ORDER BY StringID ASC
OPEN SampleCursor
FETCH NEXT FROM SampleCursor INTO @StringValue
WHILE @@FETCH_STATUS = 0
BEGIN

	-- Update main string with string segment.
	UPDATETEXT #BigStrings.StringValue @StringPointer @StringOffset 0 @StringValue
	SET @StringOffset = @StringOffset + LEN(@StringValue)
	FETCH NEXT FROM SampleCursor INTO @StringValue

END
CLOSE SampleCursor
DEALLOCATE SampleCursor

-- Show concatenated string.
DECLARE @Result AS VARCHAR(8000)
SELECT @Result = StringValue FROM #BigStrings WHERE StringID = @StringID
DELETE FROM #BigStrings WHERE StringID = @StringID
SELECT @Result

-- Done with temporary table.
DROP TABLE #BigStrings

Here are some notes about the above code:

  • Trailing spaces aren’t handled well.
  • I wouldn’t recommend using it unless your SQL environment does not allow for XML processing.
  • I only used a temporary table to make the example self-contained. You can instead use a normal table if you like.
  • This example, which was tested in SQL Server 2000, is limited to 8000 characters just because I’m converting the result to a varchar and SQL Server 2000 has a maximum size of 8000 characters for that data type. However, even in SQL Server 2000, you need not convert to a varchar, so the length need not be limited if you are willing to deal with the text data type.
  • Since the data in temporary tables (and normal tables) is written to the hard drive, performance may actually be worse than simple string concatenation for a small number of string segments.

List All Tables and Columns in a Database

This is a query I created to show all the tables in a database and all of the columns on those tables (the columns also show their type, not including things like max length). If you run this in SSMS and output the results to text rather than to a grid, you can just paste it into Excel. You’ll get one table per row, and one field per column when you paste into Excel.

-- Optional (may already be set by SQL connection.
USE NameOfYourDatabase
GO

-- Parameters.
DECLARE @DatabaseName AS varchar(max)
SET @DatabaseName = 'NameOfYourDatabase'

-- Variables.
DECLARE @TabChar AS varchar(1)
DECLARE @Name AS varchar(256)
DECLARE @Column AS varchar(256)
DECLARE @Type AS varchar(256)
DECLARE @Columns AS varchar(max)
DECLARE @Tables AS table(info varchar(max))

-- Cursor to query table names.
DECLARE TableCursor CURSOR FOR
SELECT
	name
FROM sys.Tables
WHERE
	type_desc = 'USER_TABLE'
ORDER BY
	name ASC

-- Loop through each table name.
OPEN TableCursor
GOTO FetchNextTable
WHILE @@FETCH_STATUS = 0
BEGIN

	-- Variables.
	SET @TabChar = ''
	SET @Columns = ''

	-- Cursor to query column info.
	DECLARE ColumnCursor CURSOR FOR
	SELECT
		Column_Name, Data_Type
	FROM information_schema.columns
	WHERE
		Table_Catalog = @DatabaseName
		AND Table_Name = @Name
	ORDER BY
		Ordinal_Position ASC

	-- Loop through each column.
	OPEN ColumnCursor
	GOTO FetchNextColumn
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Columns = @Columns + @TabChar + @Column + ' AS ' + @Type
		SET @TabChar = CHAR(9)
		FetchNextColumn:
		FETCH NEXT FROM ColumnCursor INTO @Column, @Type
	END
	CLOSE ColumnCursor
	DEALLOCATE ColumnCursor

	-- Store the table name and column information.
	INSERT INTO @Tables(info) VALUES (@Name + @TabChar + @Columns)

	-- Next table.
	FetchNextTable:
	FETCH NEXT FROM TableCursor INTO @Name

END
CLOSE TableCursor
DEALLOCATE TableCursor

-- Show all table information.
SELECT * FROM @Tables

Notice that there is a variable near the top that you’ll need to change to match the name of your database. Also, if you include the command (at the top) to specify the database, you’ll have to change that to your database name too.

See Also