Wednesday, October 28, 2009

MySQL vs MS SQL Server

I know MySQL's query engine isn't as powerful as MSSQL, but there are things in the MySQL syntax that just run circles around MSSQL. For example, MySQL has a great option to the INSERT command called IGNORE.

It is very common to construct a many to many table using just two columns for the relationship and making those two columns the primary key. This implies each row is unique. A common problem is how to update this relationship.

Say you have a web site where a user can choose from a list of options. When the user wants to update their settings, they check or uncheck some new settings. On the backend, we need to update the relationship.

A common approach is to delete all the old relationships, then reinsert them. This to me is an expensive and brute force approach. A less "heavy handed" approach would be to select all the current mappings, then figure out what is new and what should be removed. However, we've now made a rather complex difference engine on the client and it's not concurrent, unless we lock the database while doing the work on the client.

So, welcome MySQL's INSERT IGNORE command.

Here's how it works:

INSERT IGNORE INTO table (field1,field2) VALUES (val1, val2)

Simple, if the row already exists (a unique constraint is violated), MySQL simply skips the insert.


However, what if we want to do the same thing in MS SQL? Not nearly as easy, and a lot more resource intensive..

T-SQL:
insert into table (field1,field2)
select val1, val2
where
not exists(select 1 from table where field1=val1 and field2=val2)

Another command I love in MySQL, but absent from MSSQL is the INSERT .. ON DUPLICATE syntax.. It solves a common problem, do you need to add a new record or update an existing one.

Most people have some code that looks like this:

SELECT COUNT(*) FROM table WHERE id=val1

IF COUNT(*) = 0 THEN
INSERT ....
ELSE
UPDATE ....

Straight from the MySQL manual:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

is equivalent to

UPDATE table SET c=c+1 WHERE a=1;

but if no value exists for a=1, then

INSERT table (a,b,c) VALUES (1,2,3)

Sometimes, it's those little extra I just love.