Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted December 10, 2012

Mapping Data Types between MySQL and SQL Server

By Rob Gravelle

The SQL language that was first developed by IBM in the early seventies was adopted as a standard by the American National Standards Institute (ANSI) in 1986 as SQL-86 and the International Organization for Standardization (ISO) in 1987.  MySQL Server follows the ANSI SQL standard by and large, but performs some operations a little differently.

SQL Server, on the other hand, uses Transact SQL (or T-SQL for short), which expands on IBM’s original SQL standard to include vendor-specific procedural programming instructions, local variables, various support functions for string and date processing, mathematics, as well as changes to the DELETE and UPDATE statements. Transact-SQL is really a "superset" of SQL, so T-SQL supports all the commands that SQL supports, but SQL doesn't support all the commands that T-SQL supports.

With respect to data types, MySQL and MS SQL Server have some important differences. Chances are that you’ll have to do some mapping to get the correct data when migrating from one to the other. To that end, this article describes the most important data type differences to be aware of between MySQL and SQL Server.

Unsigned Integers

All integer types in MySQL, including the TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, can have the optional UNSIGNED attribute. Unsigned integers accept non-negative numbers only, but with a larger upper numeric range.

You can always cast unsigned integers to the larger int type in SQL Server, such as from unsigned mediumint to bigint:

UPDATE dbo.MyTable
 SET BigIntColumn = CAST(UnsignedMedInt AS BIGINT)

Of course, that approach falls apart at the unsigned bigint. There is a larger Int64 type, but according to Microsoft, they’ve

...looked into this issue and found out some of the history behind the handling of the BIGINT type. At the time [Microsoft] implemented this functionality, there were issues supporting the Int64 data type across platforms. It was decided to return these values are strings instead.*
*from the Microsoft Connect site

That being the case, you can forgo the up-casting when converting unsigned bigints to SQL Server. You’ll likely have to store the data as strings.

Display Width and the Zerofill Attribute

MySQL supports an extension for specifying the display width of integer data types in parentheses following the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width can be used by applications to left-pad column values having a width less than the specified display number. The default padding character is spaces. 

Hence, the value within the parentheses only affects the display of the value and in no way limits the data type’s inherent storage capacity. Nor does it prevent values wider than the column display width from being displayed in full. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767. Values that exceed three digits are displayed in full using all of the required digits, such as “1500”, whereas values that are comprised of less than three digits are padded, i.e., “ 76”.

Using the width attribute in conjunction with the ZEROFILL attribute replaces the default padding of spaces with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is now presented as “0005”.

In SQL Server, padding can be accomplished using the STUFF() function as per:

SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID) AS EmployeeCode
 FROM dbo.RequestItems
 WHERE ID=6

Dates

MySQL is fairly lenient with dates and allows you to store '0000-00-00' as a “dummy date” unless you are using the NO_ZERO_DATE SQL mode. Moreover, any invalid DATETIME, DATE, YEAR, or TIMESTAMP values are converted to the “zero” value of the appropriate type. For date and time types other than TIMESTAMP, the default is the appropriate zero value for the type - '0000-00-00' or '0000'. For the first TIMESTAMP column in a table, the default value is the current date and time - '0000-00-00 00:00:00'.

In SQL Server, it is recommended that you replace zero date values with a value of "1753 January 01". Another method is to use a string or number data type to store zero dates.

Date Validation

Turning off the ALLOW_INVALID_DATES flag tells MySQL to verify that the month is in the range from 0 to 12 and that the day is in the range from 0 to 31.

Microsoft now suggests using the new DateTime2 Data Type instead of DateTime, in part because it will reject invalid dates. It was introduced in SQL Server 2008.

Here’s an informative Database Journal article on the DateTime2 (and Date) type.

In SQL Server 2012, you can also use the TRY_CONVERT function:

SELECT TRY_CONVERT(DATETIME2, '2012-02-02 13:42:55.2323623'),
        TRY_CONVERT(DATETIME2, '2012-02-31 13:42:55.2323623');

Results in:

2012-02-02 13:42:55.2323623    NULL

ESCAPE Sequences in Strings

In MySQL, each ESCAPE sequence in a string literal begins with a backslash (‘\’).

Example:

select 'This is a \'Quoted string\'';

-- This is 'Quoted string'

In SQL Server, string literals must be changed by duplicating the single quote character:

SELECT 'This is a ''Quoted string''';

ENUM Data Type

MySQL supports the ENUM (enumeration) data type. An ENUM is a string object with a value chosen from a list of permissible values that are enumerated explicitly in the column specification at table creation time.  Each enumeration value has a numeric index and is sorted according to the order in which the enumeration members were listed in the column specification.

If you insert an invalid value into an ENUM column that does not allow nulls, the empty string is inserted instead as a special error value; its default value is the first element of the list of allowed values. For an ENUM column that is declared to allow nulls, Null then becomes the default value.

Here’s how to define an ENUM in MySQL:

create table table_enum (e enum ('a','b','c') not null);

The SQL Server approach is to emulate the ENUM data type as a lookup table, such as in the following example:

create table someenumtype (_id integer, _value varchar(max))

The original table will have a reference to the above hash-table by the _id.  It can be used to add joins to all tables where the ENUM field value is used.

Conclusion

Converting data between different databases often entails two separate conversion stages.  The first is transforming the data types into the equivalent one in the new database.  If none fit the bill, the default string/varchar is sometimes the only fall-back.  The next step is to add validation and formatting code that may be absent in the target database.  In a future instalment, we’ll explore function differences between MySQL and SQL Server.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM