Results 1 to 5 of 5

Thread: MySQL vs MS SQL

  1. #1
    Join Date
    Aug 2002
    Posts
    3,959
    Rep Power
    25

    Default MySQL vs MS SQL

    Between work and classes I've got quite a few projects working on, and they keep increasing. Right now I'm conversting my offices web site from ASP (which I just completed about 2 weeks ago) to PHP. (which is what our new server *hostrocket* supports), the whole process has to be completed in less than a week. As I'm writing this I just competed porting the database from Access to MySQL (why doesn't access have a dump to SQL feature and why doesn't MSSQL have a dump to STANDARD SQL feature?).
    Good questions...

    Samba allows us to run some Windows apps on a Linux server.

    There a few apps that will only run on MS SQL. Have you heard of any plans to reverse engineer MS SQL? If that could be done, apps written for MS SQL could run on open source software.

    What are the technical issues involved? How does MS SQL differ from the standard SQL? How do the open source SQL databases differ from each other?


  2. #2
    Join Date
    Jul 2002
    Posts
    818
    Rep Power
    0

    Default Re: MySQL vs MS SQL

    Well, you can't actually compare MSSQL (Microsoft SQL Server) and SQL (Standard Query Language) as these are two different entities all together.

    MSSQL is a Relational Database Management System (RDBMS) from Microsoft.

    SQL is a language for manipulating data within a database and is managed by the International Standards Organization (ISO) and the American National Standards Institute (ANSI).

    All RDBMS's implement some form of SQL, some more standards compliant than others. My grouse with a few of them is not the SQL that it uses to manipulate data (which in all cases is stndard), but the SQL it generates to reproduce this data. All too often the syntax cannot be interpreted by any other system.

    One of the most standard compliant is that employed by MySQL which allows you to actually copy and paste any code it produces (lets say from running "mysqldump&quot into another RDBMS and have it run without error.

    MSSQL's implementation of ANSI/ISO SQL is called TSQL and is EXREMELY proprietary. So mush so that whenever I have a table to port from MSSQL to MYSQL I have to write a PHP application to accomplish it.

    To be fare, we must understand that TSQL was developed not just to insert, update and delete records, but to do much more. It's the language MSSQL uses to implement it's Stored Procedures (which is a big drawing point when you look at the fact that MySQL still does not support this needed feature).

    Oracle has a similar adaptation of SQL called PL/SQL, which does pretty much the same thing as the MSSQL/TSQL combo. With this in mind I suppose that we can expect MySQL to follow suit with it's own proprietary version of SQL once it fully supports Stored Procedures.

    However, I digress.

    Have I heard of any plans to reverse engineer MSSQL? No. Do I see it happening in the near future? No. Microsoft is notorious for spitting in the face of the competition as well as the concept of a heterogeneous system and as such will most likely not do anything to make it easier to have your information co-exist within separate database systems.

    Keep in mind however, that a properly written application can easily traverse from system to system (can you say “Abstraction”) and as such should pose no problem when being implemented atop proprietary, open source or vendor specific RDBMS’s.

    Having an application running on different database systems is one thing, however managing different database systems within your enterprise environment is a completely different ball game.

    I’m sure more seasoned DBAs / SysAdmins than myself may be able to speak more on this but certain issues you’re sure come across are:

    1)   Data replication
    2)   Differing data types
    3)   Proprietary SQL implementations
    4)   Non-existent features across platforms

    Just to name a few.

    As to how open source RDBMS’s differ from each other, I can’t speak to that topic either. A few board members have some experience with PostgreSQL and Advantage, they should be able to give some insight.

  3. #3
    Join Date
    Jun 2002
    Posts
    648
    Rep Power
    0

    Default Re: MySQL vs MS SQL

    Advantage Database Server (ADS) (which I am quite familiar with) is not open source. In fact it is not really cheap either. Although its SQL has a few proprietary keywords it is closer to ANSI SQL than MSSQL is.

  4. #4
    Join Date
    Feb 2003
    Posts
    3,184
    Rep Power
    0

    Default Re: MySQL vs MS SQL

    a few a little differences here and there. Most things such as table definitions and select statements are the same between MYSQL AND MSSQL.
    The differences are mainly in the "system" functions which most programs use quite often :
    e.g. in MYSQL stores it's table columns in a database called "mysqldb".

    On the flip side MSSQL server stores it's stuff in a table called sys_columns.

    Now the program your trying to convert uses alot of these low level stuff - then your gonna have to do alot of testing and fixing to do.

    Other differences include nasty little ones like in MYSQL: to add strings together you would use a function called CONCAT('str', 'ing2&#039 while in MSSQL server you would just add the strings together using 'str' + 'ing2'.

    Also MSSQL has stored procedure functionality which was recently added to MYSQL. Expect a lot a differences there.

    When it comes down to it : if the program was written avoiding a couple a things like the ones I mentioned - it wouldn't be any problem to convert it. Otherwise it's going to take a little work.

  5. #5
    Join Date
    May 2003
    Posts
    99
    Rep Power
    0

    Default Re: MySQL vs MS SQL


    I personally like Postgres SQL which is very similar to MySQL. They are both very standard ANSII SQL 97. One thing I like about Postgres over MS SQL is the way it handles date and time values. I will take virtually any format you though at it. It will also return it in any format you like. MS SQL only understand a few standard formats. In Postgres there are date, datetime, timestamp and time formats. In MS sql you better know how to use up you convert(varchar(10), mydate, 101). Also MSSQL is very nonstandard. Try defining a Boolean data field

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •