In MSSQL, table names and column names are enclosed in double quotes or square brackets. In MySQL, they're enclosed in backtick (`) characters.
CREATE TABLE "Employees" ("Empno" VARCHAR(10),"EmpName" Varchar(100)......
SELECT [Empno],[EmpName] from "Employees"......CREATE TABLE `Employees` (`Empno` VARCHAR(10),`EmpName` Varchar(100)......
SELECT `Empno`,`EmpName` from `Employees`......Enclosing identifier names is optional in both databases, but becomes mandatory if the identifier name contains blank spaces. For example, without enclosing characters:
CREATE TABLE Employees (Empno VARCHAR(10),EmpName Varchar(100)......CREATE TABLE Employees (Empno VARCHAR(10),EmpName Varchar(100)......But if the identifier contains blank spaces, it must be enclosed:
CREATE TABLE "Employees Table" ("Emp No" VARCHAR(10),"EmpName" Varchar(100)......CREATE TABLE `Employees Table` (`Emp No` VARCHAR(10),`EmpName` Varchar(100)......In MySQL, enabling the ANSI_QUOTES SQL mode (SET sql_mode='ANSI_QUOTES';) lets you quote identifiers in double quotation marks too — but then you can only use single quotes for literal strings.
In MS SQL Server, table and column names are case-sensitive only if the database uses a case-sensitive collation. For example, creating a table with a capital "E" in a case-sensitive database:
create table Employee (SNo int,Name Varchar(100),Sal money)Then running select * from employee (lowercase) produces an error:
You have to reference the table name in the exact case used at creation:
MySQL: schema names are not case-sensitive.
MSSQL uses the TOP keyword after SELECT. For example, to view the top 5 salaries:
SELECT TOP 5 [Empno],[Name],[Salary],[Jdate] FROM [Scott].[dbo].[Emp] order by salary desc
MySQL achieves the same result with the LIMIT n keyword:
select * from emp order by sal desc limit 5