MySQL and MSSQL

For most of my programming life I’ve used MySQL for no real reason. I had nothing to complain about, so why switch? That’s really the wrong perspective as I can see now.

User Defined Variables

Both have their advantages and disadvantages. For instance in MySQL you don’t have to declare variable types. For example in MySQL you can simply do this:

SET @v = 1;
SELECT @v; -- 1

But in MSSQL you must set the data type like so

DECLARE @v AS INT;
SET @v = 1;
SELECT @v; -- 1

Default Values

Something that MSSQL can do that MySQL can’t is set “functions” as defaults. For instance, you may want a randomly generated string, or the current timestamp for a default value. For MSSQL you can simply set NEWID() or GETDATE() as the default value.

In MySQL you can use the data type “timestamp” to get the current date, but I’d hardly call this a function call. Another alternative, you could use triggers to get the last insert id and update it with NOW() but those are usually a pain to transfer from server to server.

Sorting

With any database, in the “SELECT” you could do a sub-select to get some extra data, or a function call, like RAND()/NEWID() or whatever you need, then assign that a column name. However in MSSQL you cannot sort by this column, in MySQL you can.

There are two work rounds I can find for this. The first being putting the same sub-query/function in the ORDER BY. There is the obvious downfall that it would have to run the query again if not cached. The second would be to make it a sub-select.

SELECT
    *
FROM
    (
        SELECT
            *,
            NEWID() AS myId
        FROM
            [table]
    ) AS x
ORDER BY
    x.myId

Limiting

Both databases allow you to limit the number of records you want to select, update, or delete with a only slightly different syntax. MySQL has it at the end, while MSSQL has it at the beginning.

SELECT * FROM `table` LIMIT 1 -- MySQL
SELECT TOP 1 * FROM [table] -- MSSQL

DELETE FROM `table` WHERE `id`=1 LIMIT 1 -- MySQL
DELETE TOP (1) FROM [table] WHERE [id] = 1 -- MSSQL

For those of you familiar with MySQL you will quickly point out that “LIMIT” accepts two parameters the first of which is the “offset” which defaults to “0″ and the second is the actual limit. This is useful for pagination for instance, if each page had 20 “products” and you were on the second page you may do something similar to below, which starts the offset at the 20th product.

SELECT * FROM `products` LIMIT 20, 20

With MSSQL it’s a bit trickier. The only implementation I’ve seen it to simply move the cursor to the offset, and loop until you get to the end or the 20th product. I’ve tried to implement using ROW_NUMBER and BETWEEN to impose pages.

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER() OVER(ORDER BY id)  AS rowNumber,
            *
        FROM
            [products]
    ) AS x
WHERE
    x.rowNumber BETWEEN
        20 and 30