Differences between MySQL and MS SQL

To continue on the differences between PHP and C# theme, here’s a list of most striking differences between MySQL and MS SQL:

  1. The paging in MS SQL/ASP.NET seems to be implemented completely differently than MySQL’s LIMIT x,y. Heh? I guess some of the calculations/logic is done in the following stored procedure as opposed to in the code as is the case with PHP:
CREATE PROCEDURE CatalogGetProductsOnFrontPromo
(@DescriptionLength INT,
@PageNumber INT,
@ProductsPerPage INT,
@HowManyProducts INT OUTPUT)
AS
— declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name NVARCHAR(50),
Description NVARCHAR(MAX),
Price MONEY,
Thumbnail NVARCHAR(50),
Image NVARCHAR(50),
PromoFront bit,
PromoDept bit)
— populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
CASE WHEN LEN(Description) <= @DescriptionLength THEN Description
ELSE SUBSTRING(Description, 1, @DescriptionLength) + ‘…’ END
AS Description, Price, Thumbnail, Image, PromoFront, PromoDept
FROM Product
WHERE PromoFront = 1
— return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
— extract the requested page of products
SELECT ProductID, Name, Description, Price, Thumbnail,
Image, PromoFront, PromoDept
FROM @Products
WHERE RowNumber > (@PageNumber – 1) * @ProductsPerPage
AND RowNumber <= @PageNumber * @ProductsPerPage

CREATE PROCEDURE CatalogGetProductsOnFrontPromo

(@DescriptionLength INT,

@PageNumber INT,

@ProductsPerPage INT,

@HowManyProducts INT OUTPUT)

AS

— declare a new TABLE variable

DECLARE @Products TABLE

(RowNumber INT,

ProductID INT,

Name NVARCHAR(50),

Description NVARCHAR(MAX),

Price MONEY,

Thumbnail NVARCHAR(50),

Image NVARCHAR(50),

PromoFront bit,

PromoDept bit)

— populate the table variable with the complete list of products

INSERT INTO @Products

SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),

ProductID, Name,

CASE WHEN LEN(Description) <= @DescriptionLength THEN Description

ELSE SUBSTRING(Description, 1, @DescriptionLength) + ‘…’ END

AS Description, Price, Thumbnail, Image, PromoFront, PromoDept

FROM Product

WHERE PromoFront = 1

— return the total number of products using an OUTPUT variable

SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

— extract the requested page of products

SELECT ProductID, Name, Description, Price, Thumbnail,

Image, PromoFront, PromoDept

FROM @Products

WHERE RowNumber > (@PageNumber – 1) * @ProductsPerPage

AND RowNumber <= @PageNumber * @ProductsPerPage

3 Comments to “Differences between MySQL and MS SQL”

  1. By Joe, November 30, 2009 @ 10:04 pm

    Isn’t it crazy? I was floored when I first tried to do a LIMIT x,y in MSSQL and found out I had to use TOP, or crazy sub-queries or stored procedures just to fetch items 21-40, etc. MSSQL is very powerful and is a great database, but could certainly learn a lot from MySQL at the same time.

  2. By Kevin Korb, December 3, 2009 @ 11:02 am

    Another thing I noticed is using double-quotes tends to make the query barf, for example:

    SELECT * FROM Product WHERE name=”test” is perfectly fine in MySQL, however MSSQL it NEEDS to be:

    SELECT * FROM Product WHERE name = ‘test’

    Typically you’d bind the parameters, but sometimes you might do queries directly on the DB for admin purposes, so you might just write out the values…

  3. By php trivandrum, December 13, 2009 @ 9:08 pm

    In the past weeks, we at Saturn SPL [http://www.saturn.in] were also passing through the nightmare of porting an application which was developed with just MySQL in view to MSSQL and php on IIS. Read about the frustration I felt at the start of the migration [ http://www.php-trivandrum.org/general-articles/migration-from-mysql-to-mssql-our-solutions/ ], and the solutions which I stitched into our application for keeping the code as such while providing the MSSQL for the specified version [http://www.php-trivandrum.org/code-snippets/migration-from-mysql-to-mssql-our-solutions-continued/], thanks to all those who have written about the differences.

RSS feed for comments on this post. TrackBack URI

Leave a Reply