XPost: microsoft.public.sqlserver.newusers   
   From: esquel@sommarskog.se   
      
   Gene Wirchenko (genew@ocis.net) writes:   
   >      Thank you for that bit.  Do you have any examples of these   
   > errors?  A URL would be fine.   
      
   There are maybe Connect items about them, but I'm not sure - I filed these   
   bugs in a system preceding Connect originally.   
      
   Anyway, here is a list:   
      
   1) They use ALTER TABLE only in situations when it would be accepted   
      on SQL 6.5. In many cases, ALTER TABLE could do, they instead create   
      the table under a new name, move data over, move referencing foreign   
      keys, recreates indexes etc, drops the old table and then rename the   
      new table to the right name.   
      
   2) In the scheme in 1) the transaction scope is wrong. There are three   
      transactions for something that should be a single transaction - at   
      least when everything happens behind your back.   
      
   3) The script consists of a number of batches. If you opt to generate   
      the script and run it manually, and some statement fails, this may   
      abort the transaction. The rest of the script will still be executed,   
      but now without the transaction. (I am told that if you run the script   
      from within SSMS, execution will be aborted.)   
      
   4) When constraints are moved or readded, they are added with NOCHECK.   
      This means that SQL Server does not verify the correctness of the   
      existing data. This goes faster, but it also means that the optimizer   
      will not trust the constraints, which can have performance implications.   
      
   5) Say that you have a Parent and a Child table. You first open the   
      Child table, and add a column. You generate a script. But then you   
      decide that the change is wrong, and you close the table without   
      saving. Then you open Parent and change that table and generate a   
      script. When you review it, you find that the abandoned change in   
      Child is there!   
      
   Overall, my impression is that the people who wrote this tool in the   
   dim and distant past, had very little understanding of what it makes   
   schema changes. The result is a tool which is incorrectly designed from   
   bottom up. (The reason I talk about the dim and distant past is   
   that the same bugs are in the SQL 2000 tools as well. They were very   
   faithfully ported to SQL 2005.)   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)