SQL features

This entry is part 1 of 2 in the series SQL tips and tricks

I’m currently going through ‘SQL Cookbook’ by Anthony Molinaro to fresh up my SQL knowledge. I’ve read about 1/6th of the book so far and love it. It’s not meant for newbies, but I’d say for everybody else. It contains tonnes of examples and in detail explanations. I really like that it explains solutions for the most common databases (SQL Server, MySQL, Oracle, PostgreSQL, DB2).

Before I start going into details of what all I’ve learned so far, I quickly explain how I even got to buy the book. I’m using MySQL and MSSQL databases for about 2 years now and all the databases I’ve worked so far were fairly straight forward, small, and the queries simple. I’ve had database courses at university and thought I remembered still most of what I’ve learned (about 10 years ago – what was I thinking?).

One of my current projects is atypical with respect to my previous databases: It still doesn’t consist of too many tables, but the required SQL queries are a whole lot more complicated. I hit a road block. I tried to solve way too many issues on the application side (which resulted in performance costs) and finally decided to step back and do it right in the first place (hmm, that line sounds familiar. Guess I’ve watched too many Mike Holmes episodes years ago).

Pattern matching in search conditions

Let’s assume you want to show all rows that start with the characters ‘Star’ in a certain column.
Furthermore, we have the following table named movie:

title
---------
Superstar
Star Wars
Star Trek
Stargate
Stardust
Despicable Me

The sql statement would be:

SELECT title FROM movie WHERE title LIKE 'Star%';

Real easy, isn’t it? Yeah, as long as you remember what wildcard stands for ‘any sequence of characters’.

List of wildcards that can be used:

%      any sequence of characters
_      any single character
[]     any single character within range/set (e.g. [s-z]/[stuvwxyz]
[^]    any single character not within range/set (e.g. [^s-z]/[^stuvwxyz]

UNION ALL versus UNION

To combine the result of several SELECT queries you use UNION. Nothing knew here, but there is a slight difference between UNION and UNION ALL. UNION ALL will result in duplicates if they exist. UNION, on the other hand side, will filter duplicates.

NOT IN statement in the WHERE clause

Ok, that one was completely new to me, but important to know. Let’s look at the following query:

SELECT title FROM move WHERE title NOT IN ('Despicable Me','Startdust');

As you guessed, the result will contain all columns but ‘Despicable Me’ and ‘Startdust’. Let’s quickly create a second table named ‘wishlist’ with following entries:

id    | title
--------------
1     | Despicable Me
2     | Startdust

Now change the SELECT query a bit and you get the same result:

SELECT title FROM movie WHERE title NOT IN (SELECT title FROM wishlist);

But what if the wishlist contained one entry with id 3 and title NULL? Ok, in this example, a title with NULL doesn’t quite make sense, but let’s just go with it. You still expect the same 4 movie titles as result with the above statement or? If you do, you’re mistaken. The result will be empty. What I didn’t know is that IN and NOT IN are essentially OR operations. So above select statement would be equivalent to following:

SELECT title FROM movie WHERE title NOT IN ('Despicable Me','Startdust',NULL);

So let’s look at the comparison for ‘Star Wars’:

'Star Wars'='Despicable Me' || 'Star Wars'='Startdust' || 'Star Wars'=NULL
==> (FALSE || FALSE || NULL)
==> NULL

The part to remember is that (FALSE or NULL) results in NULL and (TRUE or NULL) results in TRUE. That means if there is a chance that the column will contain a NULL value, use the following statement instead:

SELECT title FROM movie m WHERE NOT EXISTS (SELECT 1 FROM wishlist w WHERE m.title=w.title);

Database/table information

This entry is part 2 of 2 in the series SQL tips and tricks

In the last few weeks, I improved/corrected lots of my existing stored procedures for my current project. It feels good knowing that my understanding of inline views has definitely improved. I’ve additionally learned a few SQL queries that provide me with database/table information.

I also faced a few issues while trying to use views in SQL Server. I have a few views in SQL Server that I can’t actually use because as soon as I save them, the content gets automatically formatted and changed. I’d be fine with it if the change didn’t influence the outcome, but it does. When I have several joins and then a WHERE clause, SQL Server always changes it to joins with an AND instead of the WHERE. That’s really annoying since the outcome with the AND is wrong. I hope I can figure out what’s going on soon but till then, I’ll end up saving those queries in a stored procedure directly (where it doesn’t get auto-formatted and changed).

A few things I’ve learned lately:

SQL Servers sys.sql_modules

In my current project, I ended up having quite a lot of stored procedures. I started getting a bit overwhelmed. For example, I knew I had used certain keywords (like TRANSACTION) in some procedures, but how could I quickly find them. Going through each stored procedure is an option, but a very slow, annoying, and frustrating one. Then I found out about the sys.sql_modules table. It has a ‘description’ column which contains SQL scripts for creating each existing view and stored procedure.

I started using following search

SELECT definition FROM sys.sql_modules 
WHERE definition LIKE '%TRANSACTION%'; 

That helped me tremendously in finding certain stored procedures very quickly.

SQL Servers sys.objects

The SQL query above helps in finding the wanted stored procedure/view since each ‘description’ column contains the name of the stored procedure/view within a few words. But I figured it would be nicer if I could just get the stored procedure/view name instead. And that’s where the sys.objects table comes handy.

SELECT DISTINCT o.name FROM sys.sql_modules sm 
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%account%';

Other useful SQL queries to learn more about the database

The more tables/columns/views/stored procedures/functions/etc., the harder it gets to maintain the database and to keep an overview.

I found querying the Information Schema views very useful. For example, if you want to quickly know the columns of a table or view, use following query:

SELECT TABLE_NAME, COLUMN_NAME
FROM my_database.INFORMATION_SCHEMA.COLUMNS;
WHERE TABLE_NAME = 'my_table'

I’ve already mentioned how to search for certain keywords in views/stored procedures via sys.sql_modules. Another way of doing exactly this is via the Information Schema views. The following code snippet shows all CREATE VIEW/PROCEDURE functions for your database:

SELECT TABLE_NAME, VIEW_DEFINITION
FROM my_table.INFORMATION_SCHEMA.VIEWS;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM my_table.INFORMATION_SCHEMA.ROUTINES;

Example usage

During development, I often have more than one database of the same thing. I have the main database which will eventually be deployed and which I use for all kind of tests. Then I have for each person that plays around with my software (for testing purposes or other) an additional copied database.

During development, I often need to correct some views or/and stored procedures which I update on my database and then retest. As soon as I’m happy with the changes, I need to also make my ‘testers’ happy and provide them with the latest version. I wrote a little script extra for that purpose to make updating the other database views/stored procedures so much easier and quicker.

use tester_x_db;
DECLARE @row int = 1;
DECLARE @sp varchar(8000);
DECLARE @maxViews int = 
    (SELECT COUNT(*) FROM my_db.INFORMATION_SCHEMA.VIEWS);
WHILE @row <= @maxViews
BEGIN
    SELECT @sp=REPLACE(VIEW_DEFINITION, 'CREATE', 'ALTER') FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) AS 
            rownumber,VIEW_DEFINITION
	    FROM my_db.INFORMATION_SCHEMA.VIEWS
	) AS tmp
    WHERE rownumber = @row;
    EXEC (@sp);
    SET @row = @row + 1;
END

DECLARE @maxRoutines int = 
    (SELECT COUNT(*) FROM my_db.INFORMATION_SCHEMA.ROUTINES);
SET @row = 1;
WHILE @row <= @maxRoutines
BEGIN
    SELECT @sp=REPLACE(ROUTINE_DEFINITION, 'CREATE', 'ALTER') FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY ROUTINE_NAME ASC) AS 
            rownumber,ROUTINE_DEFINITION
	    FROM my_db.INFORMATION_SCHEMA.ROUTINES
	 ) AS foo
    WHERE rownumber = @row;
    EXEC (@sp);
    SET @row = @row + 1;
END