[dba-SQLServer] MySQL to SQL Server Data Type Equivalents

Here's an easy one (I'm not kidding!) for all you pros with a good working
knowledge of both MySQL and SQL Server.  (Calling Arthur Fuller!)

 

I'm in the process of reading an excellent book, (i.e. "SQL Antipatterns:
Avoiding the Pitfalls of Database Programming") by Bill Karwin.  This is one
of the best books I've come across on the topic of normalization and
database design.  In his book, Bill creates a "bug tracking" database which
a hypothetical team of software developers use to track and resolve bugs
during a major (hypothetical) software development project.  Most of the SQL
code in Bill's book is written in the MySQL dialect.  (On my computer, I
have SQL Server Express - Version 2008R2 - installed.)  There are eight (8)
tables in Bill's bug tracking application.  I have already created a "Bugs"
database on my instance of SQL Server Express.  What I want to do is use the
CREATE TABLE DDL statement to recreate all eight of the MySQL tables (in the
"Bugs" database) on my SQL Server instance and then work my way through the
book using the SQL Server dialect.  What I'm not sure about are the
equivalent MySQL-to-SQL Server data types for two of the column attributes
in the first table.  Here is the CREATE TABLE statement (from Bill Karwin's
book) for the first table.

 

CREATE TABLE Accounts (

account_id            SERIAL PRIMARY KEY,

account_name     VARCHAR(20),

first_name            VARCHAR(20),

last_name             VARCHAR(20),

email                      VARCHAR(20),

password_hash    CHAR(64),

portrait_image     BLOB,

hourly_rate           NUMERIC(9,2)

);

 

After spending quite a bit of time reviewing SQL Server and MySQL data
types, here is how I think (or guess) the identical CREATE TABLE statement
should look in SQL Server.

 

USE Bugs

GO

CREATE TABLE Accounts (

account_id            INT PRIMARY KEY IDENTITY,

account_name     VARCHAR(20),

first_name            VARCHAR(20),

last_name             VARCHAR(20),

email                      VARCHAR(20),

password_hash    CHAR(64),

portrait_image     VARBINARY(MAX),

hourly_rate           NUMERIC(9,2)           --  No change for this column

);

 

According to the MySQL web site, the keyword SERIAL is an alias for: "BIGINT
UNSIGNED NOT NULL AUTO INCREMENT UNIQUE" so I'm assuming "INT PRIMARY KEY
IDENTITY" is the SQL Server equivalent for the MySQL "SERIAL PRIMARY KEY"
keywords.  The second column I'm wondering about is the "portrait_image"
column - which appears to be a column for storing large binary objects.  Is
the SQL Server data type: "VARBINARY(MAX)" the equivalent of MySQL's "BLOB"
data type?

 

Initially I thought the "hourly_rate" column should become a "money" data
type in SQL Server, but DECIMAL(9,2) and NUMERIC(9,2) are synonymous in both
dialects.

 

Do I have the correct data type conversions (between dialects) for the
"account_id" and "portrait_image" columns?

 

Alan C. Lawhon