Monday, 23 April 2007

Schema Management in SQL Server 2005

The schema changes can be effectively managed in SQL Server 2005 by understanding its internal behavior. The following points should be kept in mind before making changes the schema in large tables

The fastest schema changes will occur when

  • Adding a column with NULL property
  • Adding a column with NULL property and DEFAULT
  • Changing NOT NULL to NULL property
  • Adding DEFAULT constraint
  • Dropping CHECK or DEFAULT constraint

The slowest schema changes have accompanying read and write operations such as:

  • CHECK constraints require a read of the entire table to enforce the CHECK.
  • Batch updates occur when adding a NULL column in combination with DEFAULT and WITH VALUES clauses
  • Batch updates occur when adding NOT NULL column (to provide Default value)
  • Batch updates occur when changing NULL to NOT NULL property
  • Batch updates occur when column data types are changed or when lengths change. The only exception is increasing the length of varchar.

The schema change best practice for performance and concurrency of large tables includes adding columns with NULL properties and avoid batch updates.The more detailed performance and concurrency issues during the schema cahnge can be found at the below link

http://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspx

2 comments:

Anonymous said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Anonymous said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face