FILESTREAMING SQL 2008 R2
in [Programming SQL]
|
Prev: Covering index
Next: FILESTREAMING SQL 2008 R2
From: Erland Sommarskog on 12 May 2010 09:04 Geoff Schaller (geoffx(a)softxwareobjectives.com.au) writes: > What issues? Except for the fact they can be a little overly restrictive > (which you can change), they use ALTER TABLE under the hood. Yes, when it would be accepted by SQL 6.5. > What else can it use? Did you ever look at the script it generates? Very often it creates a new version of the table and moves data over. Which can be a disaster in production environments. Here are a couple of issues: o The transaction scope is wrong. Say that you change a table which is referenced by FKs. There will be one transaction for the table, and one for moving the FKs. Meaning that if the script is interrupted halfway, your FKs are not migrated. o Constraints are reapplied with NOCHECK, which means that the optimizer will not trust them. This can have effects in several places, one example is a partitioned view. o If run the generated script, the transaction handling has a flaw: if a batch fails with a batch-aborting error, the transaction is rolled back. Which means that the rest of the script runs without a transaction. (This is not an issue when you run directly, as the GUI understands to interrupt execution.) o Make a change to a table, generate a script for the change, but don't run it. Close the table (because you realise that you were messing in the wrong place). Instead open another table in the designer, and change that table. If you now generate a script, or even worse "save", your abandoned change will be included. There may be more, these are the issues that I recall on the top of my head. In SSMS 2008, there is by default a stopper that prevents you from saving changes which includes a table reload. Why you can remove this check, there is all reason to keep it. While the message is non- descript, the meaning is that you are entering a danger zone, and you do best in keeping out. Still, the generated scripts can be used, provided that you understand the issues above, and modify the script accordingly. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Plamen Ratchev on 12 May 2010 10:48 You can store in VARCHAR(MAX) up to 2 GB (2^31-1 bytes). See the following white paper on the topic of file system vs. database storage: http://research.microsoft.com/apps/pubs/default.aspx?id=64525 From there: "As expected from the common wisdom, objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors." -- Plamen Ratchev http://www.SQLStudio.com
From: Geoff Schaller on 12 May 2010 18:34 My goodness! :-) Well I guess we're just lucky. Mostly I do table structure changes myself in code but my methodology is just not that far different to that used by the wizard. We just don't encounter the problems you describe. Wizard or code. Cheers. Geoff "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D76996D128C8Yazorman(a)127.0.0.1: > Geoff Schaller (geoffx(a)softxwareobjectives.com.au) writes: > > > What issues? Except for the fact they can be a little overly restrictive > > (which you can change), they use ALTER TABLE under the hood. > > > Yes, when it would be accepted by SQL 6.5. > > > > What else can it use? > > > Did you ever look at the script it generates? Very often it creates a > new version of the table and moves data over. Which can be a disaster in > production environments. > > Here are a couple of issues: > > o The transaction scope is wrong. Say that you change a table which is > referenced by FKs. There will be one transaction for the table, and one > for moving the FKs. Meaning that if the script is interrupted halfway, > your FKs are not migrated. > > o Constraints are reapplied with NOCHECK, which means that the optimizer > will not trust them. This can have effects in several places, one > example is a partitioned view. > > o If run the generated script, the transaction handling has a flaw: if > a batch fails with a batch-aborting error, the transaction is rolled > back. Which means that the rest of the script runs without a > transaction. (This is not an issue when you run directly, as the GUI > understands to interrupt execution.) > > o Make a change to a table, generate a script for the change, but > don't run it. Close the table (because you realise that you were > messing in the wrong place). Instead open another table in the > designer, and change that table. If you now generate a script, or > even worse "save", your abandoned change will be included. > > There may be more, these are the issues that I recall on the top of my > head. In SSMS 2008, there is by default a stopper that prevents you > from saving changes which includes a table reload. Why you can remove > this check, there is all reason to keep it. While the message is non- > descript, the meaning is that you are entering a danger zone, and you > do best in keeping out. > > Still, the generated scripts can be used, provided that you understand > the issues above, and modify the script accordingly. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Geoff Schaller on 12 May 2010 18:35 No, sorry, your advice doesn't fit. Take Sharepoint for instance.... "Plamen(a)SQLStudio.com" <Plamen(a)SQLStudio.com> wrote in message news:bpflu51kmnl5kl8up4j7h0d6j893m6vhun(a)4ax.com: > You can store in VARCHAR(MAX) up to 2 GB (2^31-1 bytes). > > See the following white paper on the topic of file system vs. database > storage: > http://research.microsoft.com/apps/pubs/default.aspx?id=64525 > > From there: > "As expected from the common wisdom, objects smaller than 256K are > best stored in a database while objects larger than 1M are best stored > in the filesystem. Between 256K and 1M, the read:write ratio and rate > of object overwrite or replacement are important factors." > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Plamen Ratchev on 12 May 2010 21:17
What doesn't fit? Or you find the white paper by Jim Gray and his colleagues wrong? I very much doubt that... -- Plamen Ratchev http://www.SQLStudio.com |