Overview
There are certain differences between some commonly used SQL code in Microsoft SQL Server’s Transact SQL and MySQL.
Explanation
The following code snippets show examples of some commons functions which are different in the two database management systems. The first code is for MySQL and the second code is for MS SQL.
Length of a string:
SELECT CHARACTER_LENGTH(string_data)
FROM TestTable
SELECT LEN(string_data)
FROM TestTable
Concatenation of strings:
SELECT CONCAT(‘MS’,’SQL’,’Tips’)
SELECT (‘MS’ + ‘SQL’ + ‘Tips’)
Select first 10 records from a table:
SELECT * FROM TestTable WHERE id=12 LIMIT 10
SELECT TOP 10 * FROM TestTable WHERE id=12
Generate Globally Unique Identifier (GUID):
SELECT UUID()
SELECT NEWID()
Select a random record:
SELECT * FROM TestTable ORDER BY RAND() LIMIT 1
SELECT TOP 1 * FROM TestTable ORDER BY NEWID()
Return current date and time:
SELECT NOW()
SELECT GETDATE()
Auto increment field definition:
TestTable INTEGER AUTO_INCREMENT PRIMARY KEY
TestTable INT IDENTITY PRIMARY KEY
Database version:
SELECT VERSION()
SELECT @@VERSION
I am a technical and business professional based in Budapest, Hungary. I am working in the financial industry on the operations side, but also have test management experience in the educational software domain. My experience includes Microsoft SQL Server 2000-2012, IBM DB2 and MySQL database administration, troubleshooting, migration and development. My focus points are performance tuning and security. I am passionate about sharing my knowledge with the greater audience.
I hold MSc degree in Engineering and Management. I am married and I have a beautiful daughter.
LinkedIn account:
http://www.linkedin.com/in/nagytibor
Twitter account:
https://twitter.com/#!/tnagy_eeft
