SQL

I’ve been working with SQL since around the year 2000. Over that time I have worked with MySQL, Oracle, SQL Server, PostgreSQL, and SQLite.

Database Schema Migrations : A Few Lessons Learned

About 5 years ago I was introduced to Flyway, a tool for managing schema migrations using simple SQL scripts. I’ve used it on several projects now, and have more recently been introduced to Liquibase. The two tools both solve similar problems, with slightly different approaches. The main purpose of these tools is to version changes to the schema of the databasse. Liquibase has a good …

Database Schema Migrations : A Few Lessons Learned Continue Reading »

Getting a quick hash of a query in PostgreSQL

I’ve been doing a lot of tweaking of SQL queries as of late, trying to squeeze some performance out of a query by adding indexes, optimizing joins, and some other operations. To do a quick check to ensure that the queries return the same results, I’ve been hashing the results to see if I get an identical hash back. Courtesy of https://stackoverflow.com/a/13948327/206480 sha1 is only …

Getting a quick hash of a query in PostgreSQL Continue Reading »

PostgreSQL’s GREATEST (and LEAST) function

PostgreSQL has two functions that are not standard SQL, but are very helpful in certain situations : GREATEST and LEAST. PostgreSQL does not have scalar functions for MIN and MAX (those are aggregate functions that operate on all rows in a query), but GREATEST and LEAST accomplish that and then some. Both allow for more than two values to be passed in, and also handle …

PostgreSQL’s GREATEST (and LEAST) function Continue Reading »

Sql Quickie: Find zip codes near the center of a region

At work we have a copy of the zip-codes.com Business database, which I tend to reference somewhat frequently due to the nature of projects I work on. Today I needed a list of postal codes from major metro areas, which would be used to drive a test data generator. The data generator has access to a simpler, non-commercial zip code list, and can do radius …

Sql Quickie: Find zip codes near the center of a region Continue Reading »

Troubleshooting a SQL Server Implicit Conversion Issue

Over the last couple of weeks, I have been spending some time troubleshooting performance issues in some of our Java-based RESTful services. This week I came across one that required some different steps to troubleshoot. First, the tl;dr – our JDBC driver (jTDS) converts CHAR to NCHAR, which causes an index SCAN on a CHAR column instead of an index SEEK. The Architecture Here is …

Troubleshooting a SQL Server Implicit Conversion Issue Continue Reading »