{"id":411,"date":"2012-12-07T09:00:46","date_gmt":"2012-12-07T16:00:46","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=411"},"modified":"2012-11-30T11:03:01","modified_gmt":"2012-11-30T18:03:01","slug":"set-data_compression-for-all-tables","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/12\/set-data_compression-for-all-tables\/","title":{"rendered":"Set DATA_COMPRESSION for all tables"},"content":{"rendered":"<p>Working on a quick little project to sync up two databases and I realized the data compression was set differently between the two of them. Apparently, my Google fu was not up to snuff because it took quite a bit of stumbling around to find a forum post matching what I wanted to do.\u00a0 There were actually a few scripts which used sp_MSforeachtable. For whatever reason I ended up with errors about SET QUOTED_IDENTIFIER which I couldn't resolve (yes, I know it's either OFF or ON so why I couldn't get it right might be an indication of my mental state or lack of caffeine).\u00a0 The script which I ended up using actually generates a script for each table in the database and gives you the option to set the DATA_COMPRESSION to ROW, PAGE, or NONE.<\/p>\n<p>The script which worked was as follows:<\/p>\n<pre>DECLARE @Compressiontype VARCHAR(5)\r\nSET @Compressiontype = 'PAGE' -- Value should be NONE or ROW or PAGE\r\nSELECT 'ALTER TABLE [' + SCHEMA_NAME (schema_id)+'].['+ name \r\n+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+ @Compressiontype + ')'  FROM sys.tables<\/pre>\n<p>Note: Make sure to SET QUOTED_IDENTIFER ON before executing.<\/p>\n<p>That's it. I've got nothing witty today. No pop culture tie in. Nothing.<\/p>\n<p>Vidhya Sagar's MSDN forum post is <a href=\"http:\/\/social.msdn.microsoft.com\/Forums\/en-US\/sqldatabaseengine\/thread\/61583053-c929-4ace-ad77-f74a0988921a\" target=\"_blank\">here<\/a>.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Working on a quick little project to sync up two databases and I realized the data compression was set differently between the two of them. Apparently, my Google fu was not up to snuff because it took quite a bit of stumbling around to find a forum post matching what I wanted to do.\u00a0 There [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[86,43,3,57],"tags":[98,10,9,173],"class_list":["post-411","post","type-post","status-publish","format-standard","hentry","category-dba","category-microsoft-sql-server","category-scripts","category-t-sql","tag-compression","tag-ms-sql","tag-sql-server","tag-t-sql"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=411"}],"version-history":[{"count":3,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/411\/revisions"}],"predecessor-version":[{"id":413,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/411\/revisions\/413"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=411"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}