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 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
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 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 VARCHAR.
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 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