One thing about building a database IDE is that you need to deal with understand all of the quirks and inconsistencies between different database management systems (DBMS). After adding support for Microsoft SQL Server to modelDBA, I’ve learned a few things that distinguish SQL Server from MySQL. If you’re switching from a different DBMS, you might want to take note below of what you should expect.
Are you a SQL Server user? Try out modelDBA to experience the easiest way to manage database changes.
Signed Integers
In MySQL, you have the option to store integer types as signed or unsigned. This allows you to store much higher positive integers if you want to (and disables negative integer storage). SQL Server doesn’t give you this option. Integers are always signed.
Altering Columns with Constraints
SQL Server is very restrictive when it comes to making changes to tables. Where MySQL can accommodate changing a column and deal with most second-order effects due to the change, SQL Server takes a much more conservative approach. Before making any changes to a column, you need to make sure that any constraints (like foreign keys, indexes, etc.) have been dropped first. If not, SQL Server will complain and block the change.
This is a major pain when you’re trying to make what should be a simple update to a table. Instead of writing a script to drop all related constraints and re-create them after a change, why not let modelDBA do it for you?
Storing Defaults Separately
Speaking of needing to drop constraints, in SQL Server, column default values are stored separately as constraints on columns. In MySQL, these values are much more closely connected to the column and can be changed through a simple ALTER statement. In SQL Server, you need to drop and re-create the constraint! Combined with the need to drop all constraints before altering a column, this makes changing tables much more of a hassle on SQL Server.
Renaming Tables and Columns
One aspect of altering tables and columns in SQL Server is easy, renaming them. Renaming a table or column is still different though. You must use a stored procedure (SP_RENAME) instead of using a typical ALTER statement like you might expect.
Indexes vs Unique Keys for Foreign Keys
In an ideal world, you’d always reference a table using it’s primary key columns. However, real life is complicated and you might find yourself needing to reference a column outside of a foreign key. In that case, your DBMS needs some way to keep track of the values in the column being referenced in order to enforce the foreign key constraint. In MySQL, this is as simple as adding an index to the column. For SQL Server, you need to use a unique key instead.
Automatically Removing Primary Keys
Do you want to drop the only primary key column in a table? If you’re using MySQL, that’s no problem. Just drop the column. Using SQL Server? Not so fast. As you might expect by now, SQL Server treats that primary key as a constraint. That constraint must be removed before you drop that last primary key column.
Altering Identity/Autoincrement Columns
You’ve decided that you want to remove the IDENTITY/autoincrement attribute on a column. No problem, you might think. It’s just another attribute. Unfortunately, it’s not just another attribute. There’s no way for you to modify a column to add or remove the IDENTITY attribute on it. The IDENTITY attribute can only be changed on column creation. To change an existing column, you’ll need to create a new table with the change and move your data over before before dropping the original table (don’t forget to drop and re-create all those constraints on your table at the same time!).
This is a massive headache. modelDBA makes changing autoincrement columns as easy as clicking a checkbox. We take care of the busy-work for you.
Specifying the MAXimum Length for Character Types
Looking in the other direction, for someone who cut their teeth using SQL Server, it might be second nature to use VARCHAR(MAX) columns when storing arbitrarily large values. It may come as a surprise that other DBMS don’t support the MAX keyword, and you’ll always need to provide the maximum number of characters.
And More
While the above will give you a few examples of differences between SQL Server and MySQL, these are only the differences I ran into for making changes to a database. There are a lot more the further you dig.
Whichever DBMS you choose, knowing all the intricate details can be tricky and time-consuming. That’s why I built modelDBA, so that you can easily complete your database tasks, and get back to the more important work you need to do. Get started for free here.