Reply to: Going from MS SQL to MySQL - How difficult?

[Sysman]

MySQL was named after his other daugher My. Didn't know that, I thought it was named that to appear friendly and approachable.

I'd missed that bit. In fact the earliest book I bought on MySQL also covered a lighter weight one called mSQL.

mSQL was the first low-cost SQL-based database management system. Combined with the free Linux operating system, the availability of relatively-powerful low-cost PC hardware, and the development of world wide web standards and technologies, mSQL was an important factor in the early development of interactive, dynamic web applications[3], particularly in the period 1994-1997, after which it was increasingly supplanted by the more highly featured MySQL. mSQL had a large and widespread install base by the late 1990s[4].

[Durbs]

A chap who goes by the moniker of Monty. His Wiki entry.

MySQL was named after his other daugher My. Didn't know that, I thought it was named that to appear friendly and approachable.

[Sysman]

The guy that came up with the MySQL idea (who then sold it to Sun and moaned when Sun was a takeover target that it was being sold on to a big corporation) was working on another project as well, which looked quite interesting. I can't remember what it's called now though.

A chap who goes by the moniker of Monty. His Wiki entry.

Monty is currently working on a branch of the MySQL code base with the Aria storage engine. The product is known affectionately as MariaDB, named after his youngest daughter. MariaDB is a product of Monty Program AB

[TheFaQQer]

MySQL has in-line views now:

mysql> select * from (select 1 from dual) x;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Does MySQL allow you to insert into DUAL to totally screw up the database, though? Oracle does

[TheFaQQer]

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.

There were quite a few conditions that Oracle had to commit to before being allowed to complete the takeover. Oracle will be investing more in R&D for MySQL than Sun ever did, by some way.

The guy that came up with the MySQL idea (who then sold it to Sun and moaned when Sun was a takeover target that it was being sold on to a big corporation) was working on another project as well, which looked quite interesting. I can't remember what it's called now though.

[Freamon]

IIRC, MS SQL has things like being able to read uncommitted transactions and also to create a temporary table dynamically in code, which is something that MySQL doesn't do for good reason.

MySQL has in-line views now:

mysql> select * from (select 1 from dual) x;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

[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.

[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.

[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.

[Robinho]

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

[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.

[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.

[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]

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]

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?