MsSQL and MySQL

[peternem]

Could someone please explain the differences between these 2?

It seems you find MySQL on Unix servers and MSSQL on windows servers.

Is MS SQL just a microsoft branded version of MySQL? Are there any fundemental differences in their useage? Are they compatible?

Thanks!

------------------
Pete

[kid A]

I'll tell you this, the MySQL server is GPL software, i.e. FREE! So there... It also works great with PHP, while MS SQL basically is comletely different as it's used with ASP (I think)...

------------------
Now listening to:
U-star Recordings - More or Less

[Wollington]

As a little extension to the question..

Can I use MySQL with asp?

------------------
Microsoft has acknowledged that its engineers substituted certain file names with the phrase, "Netscape engineers are weenies," in some of its internet software.

Oh well now we all know why Nutscrape is so crap!

[Nick_B]

Originally posted by Wollington:
As a little extension to the question..

Can I use MySQL with asp?

I've never done it myself, but I would guess that there is the correct data connection thingy (my mind isn't working quite right today) that would allow you to do it relatively easily. If not, it would be possible to write one--not that that is a very good option.

------------------
Nick_B
ICQ: 1697810
P4 1.4 GHz
256 Megs PC800 RDRAM
GeForce2 Ultra
Win 2K

[Begby]

MySQL and MS SQL are both database servers, so is Oracle. They both run databases and let you run SQL queries on them across a network. They have their plusses and minuses. MS SQL is expensive, MySQL is free. MS SQL can supposedly support a lot more simultaneous connections, but this wouldn't be a concern with a small or medium website. MySQL cannot do transactions but MS SQL can, they both work with SQL however the syntax is somewhat different. MySQL can also currently not do subqueries or SSL, but this is supposed to be in the next version. MySQL can run on nix systems as well as windows NT based stuff.

MS SQL is not a Microsoft branded version of MySQL, they are coded completely different, the only thing they have in common is that logically they are database servers and they support some sort of SQL.

As far as compatiblity, you can export data and structure from one database into the other without too much hassle. The SQL is somewhat different but not too different when it comes to moving an application from one server to the other.

Both are accessible by just about any program that can support ODBC, and they are both accessible also with languages and programs that support their propietary drivers. You can access either/or with ASP and PHP, as well as access both with Cold Fusion, Access, and about anything I can think of quite easily. I use MySQL for everything here, it is a good robust solution for the kind of stuff I do, however I am really looking forward to some of the features that are currently missing, especially subqueries. I think the best thing about it is the price, free, plus that it runs on our Linux server and my win2k box.

To answer Wollington's question, yes you can use ASP with MySQL. You just have to download and install the MySQL 32bit ODBC driver on the webserver hosting your ASP stuff, then create a DSN for your database server using that driver. MySQL, since it runs on NT, can also be ran on the same box as your webserver, but you still need to install the driver and create the DSN.

[This message has been edited by Begby (edited November 21, 2001).]

[peternem]

Begby, Thanks for a great reply, that pretty much explains everything I wanted to know

------------------
Pete

[e_dawg]

Originally posted by Begby:
MySQL and MS SQL are both database servers, so is Oracle.

And DB2 and Informix and Sybase...

They both run databases and let you run SQL queries on them across a network. They have their plusses and minuses. MS SQL is expensive, MySQL is free. MS SQL can supposedly support a lot more simultaneous connections, but this wouldn't be a concern with a small or medium website.

In my experience MySQL actually beats MS SQL by leaps and bounds, though it is due to it lacking some of the features of MS SQL... In fact, in most instances, I'd say MySQL can handle a greater deal of load than many high-end database servers on much less powerful hardware.

Just my $0.02...

MySQL cannot do transactions but MS SQL can, they both work with SQL however the syntax is somewhat different.

Actually, MySQL can now handle transactions on two types of databases (it handles an array of them)... Read this

MySQL can also currently not do subqueries or SSL, but this is supposed to be in the next version. MySQL can run on nix systems as well as windows NT based stuff.

And MS SQL cannot run on *nix systems, which is a major downside... can anyone say "lets throw hardware and money at it"?

MS SQL is not a Microsoft branded version of MySQL, they are coded completely different, the only thing they have in common is that logically they are database servers and they support some sort of SQL.

If anyone is interested, MS SQL uses the Microsoft-style SQL syntax, which is very loose by convention, and it requires some odd things in various statements (for instance, 'delete from [tablename] where [condition]' doesn't work on the Microsoft-style SQL, rather, you must add an asterisk between 'delete' and 'from', or it doesn't work (as if you would want to delete a single field from the table?)... but overall, you can do some very bizzare things and they work.)
MySQL is very close to completely ANSI SQL92 compliant, whereas MS SQL is not compliant with any standard that I am aware of.

As far as compatiblity, you can export data and structure from one database into the other without too much hassle. The SQL is somewhat different but not too different when it comes to moving an application from one server to the other.

Both are accessible by just about any program that can support ODBC, and they are both accessible also with languages and programs that support their propietary drivers. You can access either/or with ASP and PHP, as well as access both with Cold Fusion, Access, and about anything I can think of quite easily. I use MySQL for everything here, it is a good robust solution for the kind of stuff I do, however I am really looking forward to some of the features that are currently missing, especially subqueries. I think the best thing about it is the price, free, plus that it runs on our Linux server and my win2k box.

Yes, I must agree on the need for subqueries -- they would be very handy, though I am sure they will decrease the efficiency of the system, which is one of its big pluses (the extra overhead from niceties like that are the archiles heel with speed for most high-end database).

To answer Wollington's question, yes you can use ASP with MySQL. You just have to download and install the MySQL 32bit ODBC driver on the webserver hosting your ASP stuff, then create a DSN for your database server using that driver. MySQL, since it runs on NT, can also be ran on the same box as your webserver, but you still need to install the driver and create the DSN.

But, a word of caution -- if you have pre-written ASP that is designed to work with MS SQL, you will have to edit most (if not all) of the SQL so it will work correctly with MySQL.

[Grizzly]

Originally posted by e_dawg

If anyone is interested, MS SQL uses the Microsoft-style SQL syntax, which is very loose by convention, and it requires some odd things in various statements (for instance, 'delete from [tablename] where [condition]' doesn't work on the Microsoft-style SQL, rather, you must add an asterisk between 'delete' and 'from', or it doesn't work (as if you would want to delete a single field from the table?)... but overall, you can do some very bizzare things and they work.)
MySQL is very close to completely ANSI SQL92 compliant, whereas MS SQL is not compliant with any standard that I am aware of

What version of MS SQL are you talking about? I've been using MSSQL 7.0 w/ SP3 for quite some time now, you saying "DELETE FROM [tablename] WHERE [condition] works fine. In fact....using an asterix after the DELETE keyword will throw a syntax error.

Outside of that e_dawg, great info, great post. I'm a true fan of MySQL, and I can't wait until they start supporting stored procedures.

[Scuttle]

I can't wait until they make MySQL Scuttle friendly so he can get it working on his damn lan server. I've tried umpteen times with 3 different software packages and have never got MySQL and PHP working in harmony/at all together.

[e_dawg]

Originally posted by Grizzly:
What version of MS SQL are you talking about? I've been using MSSQL 7.0 w/ SP3 for quite some time now, you saying "DELETE FROM [tablename] WHERE [condition] works fine. In fact....using an asterix after the DELETE keyword will throw a syntax error.

Outside of that e_dawg, great info, great post. I'm a true fan of MySQL, and I can't wait until they start supporting stored procedures.

My data may be a bit out of date, as I haven't used MS SQL directly in about 3 years... Microsoft is just known for backward compatibility, so, I figured it would be correct. I know that MS SQL used extremely similar SQL to that which is used by Access due to the fact that the least-capable ASP developers that I worked with always used Access to generate their SQL for them (weaklings!)... And it would cut, paste, and work 95+% of the time...

Yes, I cannot wait for a number of features... But it is an excellent start, and (to me, at least) very handy at this point. If I had serious work to be done, I'd go DB2 or Oracle, but for most projects, MySQL is more than enough.

[e_dawg]

Originally posted by Scuttle:
I can't wait until they make MySQL Scuttle friendly so he can get it working on his damn lan server. I've tried umpteen times with 3 different software packages and have never got MySQL and PHP working in harmony/at all together.

If you need help, IM me, it is actually very easy, but it takes a little knowledge to get PHP setup correctly.


------------------
Founder of the Sharky (Ultra) High-Resolution Club[SHRC] [SHRC:ETCH]
[SGC Ultimate pass... Don't read anything I post unless you want a headache]
SF Linux Hippie Club Vice-Vice President [Card]
America Online Doit Mourir
Everything you ever wanted to know and see about e-dawg...

[Grizzly]

Well said e_dawg. But yeah, MSSQL 7.0's SQL syntax is pretty legit, it has very little to do with Access SQL really. I always hated how Access's NOT EQUAL operator was "<>", that's just plain dumb.

The company I work for uses MS SQL 7 almost exclusively. And we've never developed an ASP application, we stick with Cold Fusion mostly, and a sometimes PHP if I can convince them

But yeah, I 100% agree e_dawg. I would choose MySQL for 99% of databases. If we really had to get hardcore with it, and work with stored procedures and the like...I wouldn't choose MSSQL. I would choose Oracle hands down. The thing that always bothered me about MSSQL is yes...it offers the ability to write Stored Procedures...but there are a few functions which Oracle offers that MSSQL does not. One of which is the "cascade delete." If you want to do a cascading delete on all child objects than you basically have to write the stinkin' queries yourself, which is no fun.

Personally I've never worked with DB2, but I've heard nothing but great things about it. So as far as I'm concerened....the only databases people should consider using are MySQL, Oracle, DB2, and possibly SyBase. Anything outside of that can be wiped off the face of the earth as far as I'm concerned.