Going from MS SQL to MySQL - How difficult?

[Sysman]

They still work though

That'll do for me

[TheFaQQer]

Tables don't fill up

Depends on how you define them If, for example, you use

max_rows=20

when defining a table in MySQL, then when you attempt to insert the 21st record, it will error because the table is full.

Anyway, as you said earlier, if you don't define tables correctly you are

...potentially left with a lot of data in them which SQL Server will then want to do maintenance on and will be included in the backups including log shipping if you're doing any.

If it's a temporary table, then you shouldn't be left with a lot of data in them which any database will then want to do maintenance on and get included in backups. That's why they are temporary, no?

[Spacecadet]

If it's a temporary table, then you shouldn't be left with a lot of data in them which any database will then want to do maintenance on and get included in backups. That's why they are temporary, no?

If it's a temporary table in SQL Server you aren't even left with a table after execution

Going back to your original post and your remark about MySQL not allowing temporary table to be created dynamically.
Really this makes no difference. When a temporary table is defined in a stored procedure in SQL Server, only that session can see it. Defining the table is up to the developer writing the code, it has no impact on the rest of the database and is not visible by the rest of the database.
There is no option to create temporary tables like you demonstrated in SQL Server.

[TheFaQQer]

If it's a temporary table in SQL Server you aren't even left with a table after execution

Going back to your original post and your remark about MySQL not allowing temporary table to be created dynamically.
Really this makes no difference. When a temporary table is defined in a stored procedure in SQL Server, only that session can see it. Defining the table is up to the developer writing the code, it has no impact on the rest of the database and is not visible by the rest of the database.
There is no option to create temporary tables like you demonstrated in SQL Server.

So in SQL Server, is it purely memory resident, or is it on disk as well?

I'm genuinely curious as to how you can create somewhere to store something without impacting anything else in the database. I know very little about the practicalities of creating tables in SQL server, to be honest.

[Spacecadet]

So in SQL Server, is it purely memory resident, or is it on disk as well?

I'm genuinely curious as to how you can create somewhere to store something without impacting anything else in the database. I know very little about the practicalities of creating tables in SQL server, to be honest.

Maybe I phrased that in too generalist a way:
Temporary tables are stored in the tempdb which on a well set up system should be on a different disk to the main data files and log files. Since it is stored away from the main datafile there is no risk of having to extend the data file unnecessarily (although the data file should be maintained in such a way as to keep enough free space to avoid unplanned extensions).
System tables outside of tempdb are left untouched
Statistics on the temporary table are not maintained
The log file is not written to

Depending on the size of the table which is being created and the system set up then the data might not even get written to disk.

Having said all of that, there are only 2 reasons I can justify using temporary tables in SQL Server.
1. Distributed transactions - occasionally these refuse to play nicely and the SQL Engine will try and pull entire tables across the network. Not good when you've got 100 million rows in a source table sat on a different server. I have a feeling (unproven as of yet) that this is caused by a lack of required indexes on the source database
2. For the rare occasion I do need to use a cursor (last time was 3 years ago). You can use a temporary table to get a snapshot of the data you need and then process that row by row

Common Table Expressions, Derived tables and Table Valued variables should be used in most circumstances.

[Durbs]

Another option is Navicat, which is free in the "Lite" version for Mac, Linux, or Windows

Just started using their SQLite version of that after stumbling across it in the Mac App Store, great, nicely polished bit of software for a freebie. Looks like they do flavours for all the main DB's.

[Robinho]

Well, all of this has actually taught me a lot about the differences between the two database engines.

[TheFaQQer]

Well, all of this has actually taught me a lot about the differences between the two database engines.

If you are considering a move, download MySQL and have a play around. Some bits will come naturally, and some will seem strange.

I think MySQL is going to really start taking off commercially, now that it's Oracle owned, so knowing a bit more about it may not be a bad thing.

[yorkshireman]

It has a command line tool as well. It doesn't have anything like the SQL Server GUI tools, at least not that I am aware of. You can use things like the eclipse database tools although they aren't the greatest.

Mysql now has the GUI Mysql Workbench which makes MSSQL folk upgrading to MySql a lot more comfortable.

[minestrone]

I am pretty sure Oracle got SUN to hinder mySQL's development. It is now a very good a free database and I am sure oracle would have been getting worried since it added in stored procs.