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.
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.
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
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
Find rows with duplicate values
Another thing that you may forget, is how to write a query that can find rows within a table that 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
GROUP BY EnglishProductName
HAVING COUNT(EnglishProductName) > 1) ORDER BY EnglishProductName
The aim of the above query is to provide a set of results that 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
However, if you’re dealing with older database schemas that use
IMAGE columns instead, it is important to convert the value of these columns to
VARBINARY before converting to
The query below demonstrates how to correctly perform the conversion.
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), (SELECT LargePhoto
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.
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 ...
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.