SQL query things you keep forgetting

When working with SQL databases, you may find there are things you need to do which although seemingly basic on the surface, are easy to forget due to nuances in query syntax and the required ordering of statements.

For these situations, it is useful to have a working example to refer to which reminds you of how the query you wish to create can be achieved.

In this short post, I offer up some demonstration SQL queries for things that you commonly want to do in a SQL Server database but may have forgotten.

Sample prerequisites

All of the examples in the following sections can be tested using the standard AdventureWorks database.

If you don’t have the AdventureWorks database already, download it using the above link and then restore it into a SQL Server instance for testing purposes.

Of course, you will also need SQL Server and SQL Server Management Studio which will allow you to host the database and execute the queries.

UPDATE with a JOIN

First up, there are occasions where you’ll find the need to update the contents of a table using some data from another related table.

In this instance, you may be asking yourself what order do the statements need to be in? Where do I put the JOIN statements? Etc.

The answer is simpler than you might remember and I have included an example query below by way of demonstration.

UPDATE dbo.DimProductSubcategory 
SET EnglishProductSubcategoryName = 
dbo.DimProductCategory.EnglishProductCategoryName + ': ' + 
DimProductSubcategory.EnglishProductSubcategoryName  FROM dbo.DimProductSubcategory  INNER JOIN dbo.DimProductCategory ON
DimProductSubcategory.ProductCategoryKey = 
dbo.DimProductCategory.ProductCategoryKey WHERE dbo.DimProductCategory.EnglishProductCategoryName IS NOT NULL

In the AdventureWorks database the dbo.DimProductCategory table holds top-level product categories and the dbo.DimProductSubcategory table holds the related sub-categories which are connected on the ProductCategoryKey foreign key column.

In simple terms, the above SQL updates the EnglishProductSubcategoryName column in the sub-categories table and prefixes it with a colon character, a space, and the EnglishProductCategoryName from the top-level categories table.

As you can see, after the SET the rest of the query is just like a normal SELECT statement, with a FROM clause followed by any required JOIN statements and a WHERE clause.

Find rows with duplicate values

Another thing that you may forget, is how to write a query which can find rows within a table which are potential duplicates.

Typically, in this scenario, the duplication criteria will be based on the value of one particular column.

The SQL below demonstrates a basic pattern for achieving this.

-- Example of how to find rows with duplicate values.
SELECT ProductAlternateKey, EnglishProductName, Color 
FROM dbo.DimProduct 
WHERE EnglishProductName IN (SELECT EnglishProductName 
FROM dbo.DimProduct 
GROUP BY EnglishProductName 
HAVING COUNT(EnglishProductName) > 1) ORDER BY EnglishProductName

The aim of the above query is to provide a set of results which includes all of the columns we are interested in while also grouping and ordering the results in a useful way.

In the example, an inner SELECT is used within the WHERE clause to calculate the duplicates. The GROUP BY in combination with the HAVING and its call to the COUNT function, groups and filters the remaining rows to only those rows which have the same value within the specified column.

To reuse the query elsewhere, simply update the column names you are selecting and the table you are selecting the data from in the first part of the query. Then replace the EnglishProductName column with the name of the column you wish to base the duplication search on and you’re all set.

Convert IMAGE to VARCHAR

Sometimes there are cases where you’ll find binary columns in a database that contain strings of text, as opposed to files, such as photos or other documents.

Whenever you come across this it can be useful to retrieve the text via a query so that you can copy it elsewhere.

This is straightforward with VARBINARY columns, as you can simply convert the value of these directly to VARCHAR.

However, if you’re dealing with older database schemas which use IMAGE columns instead, it is important to convert the value of these columns to VARBINARY before converting to VARCHAR.

The query below demonstrates how to correctly perform the conversion.

SELECT CONVERT(VARCHAR(MAX), 
               CONVERT(VARBINARY(MAX), 
                       (SELECT LargePhoto 
FROM dbo.DimProduct 
WHERE ProductKey = 101)))

In the example above, the LargePhoto column in the AdventureWorks database is already a VARBINARY column and this SQL will return the following text.

GIF89að

There are no IMAGE type columns in any recent versions of the AdventureWorks database to test against.

The example query will prove much more useful in cases where we are dealing with IMAGE columns e.g. if we were selecting from a column containing HTML we would get the result as text i.e. something more like the following.

<html><head><title>My HTML Page</title></head><body ...

Completion

So there you have it, in this post I’ve covered a few simple things that are easy to forget with SQL.

Having a set of scripts to refer to when they are needed is very useful.

As always, I’ve added the scripts from the post to my SQL Scripts repository on GitHub where you can find other scripts for handling day-to-day DBA tasks.

Comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.