• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Going from MS SQL to MySQL - How difficult?

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #21
    Originally posted by NickFitz View Post
    They still work though
    That'll do for me
    Behold the warranty -- the bold print giveth and the fine print taketh away.

    Comment


      #22
      Originally posted by Spacecadet View Post
      Tables don't fill up
      Depends on how you define them If, for example, you use
      Code:
      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

      Originally posted by Spacecadet View Post
      ...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?
      Best Forum Advisor 2014
      Work in the public sector? You can read my FAQ here
      Click here to get 15% off your first year's IPSE membership

      Comment


        #23
        Originally posted by TheFaQQer View Post
        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.
        Coffee's for closers

        Comment


          #24
          Originally posted by Spacecadet View Post
          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.
          Best Forum Advisor 2014
          Work in the public sector? You can read my FAQ here
          Click here to get 15% off your first year's IPSE membership

          Comment


            #25
            Originally posted by TheFaQQer View Post
            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.
            Coffee's for closers

            Comment


              #26
              Originally posted by NickFitz View Post
              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.

              Comment


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

                Comment


                  #28
                  Originally posted by Robinho View Post
                  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.
                  Best Forum Advisor 2014
                  Work in the public sector? You can read my FAQ here
                  Click here to get 15% off your first year's IPSE membership

                  Comment


                    #29
                    Originally posted by doodab View Post
                    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.

                    Comment


                      #30
                      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.

                      Comment

                      Working...
                      X