{"id":2298,"date":"2011-09-19T13:40:00","date_gmt":"2011-09-19T11:40:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/"},"modified":"2011-09-19T13:40:00","modified_gmt":"2011-09-19T11:40:00","slug":"sql-server-tuning-detect-sparse-columns-candidate","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/","title":{"rendered":"SQL Server Tuning: Detect SPARSE columns candidate"},"content":{"rendered":"<p>Performance tunning is becoming a key focus for most of the DBAs. In scope of the &#8220;dbi services labs&#8221;, I made some tests on functions which can help improving these, but which are not always well-known. Today&#8217;s article is going to describe one of these, the SPARE COLUMNS.<br \/>\nThe property of Spare columns is not the most used in client environment that we have seen.<br \/>\nThis article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).<\/p>\n<p>The database example that we used, is AdventureWorksDW2008R2.<\/p>\n<h3>Principle<\/h3>\n<p>The msdn explain very well the principle. I will just resume it:<br \/>\n&#8220;Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values&#8221;<br \/>\nFor more information, go to <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc280604.aspx\">msdn web site<\/a><\/p>\n<h3>Step 1: Create a temporary table<\/h3>\n<p>A temporary table is created to retrieve information about the columns of the tables in the database being audited. This table provides the following information:<\/p>\n<ul>\n<li>Name of th column<\/li>\n<li>Column ID<\/li>\n<li>Table name<\/li>\n<li>Table ID<\/li>\n<li>Type of the column<\/li>\n<li>If column is a sparse column<\/li>\n<li>If column is nullable<\/li>\n<li>Number of row in this table<\/li>\n<li>Number of null row in the column<\/li>\n<li>Ratio Nb NULL Rows \/ Nb Rows<\/li>\n<\/ul>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">CREATE TABLE #temp (\nColumnName varchar(50),\nColumnID int,\nTableName varchar(50),\nTableId int,\nTypeName varchar(50),\nIsParse bit,\nIsNullable bit,\nNumberOfRow int,\nNumberOfRowNULL int,\nRatio int)\nSET NOCOUNT ON<\/pre>\n<p><samp style=\"padding-left: 60px\">\u00a0<\/samp><\/p>\n<p>Note that &#8220;SET NOCOUNT ON&#8221; option is used. In the Best Practice, setting SET NOCOUNT to ON can provide a significant performance boost, as network traffic is greatly reduced.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png\" alt=\"1CreateTable\" width=\"474\" height=\"205\" \/><\/p>\n<h3>Step 2: Insert the first information and introduction to some usage&#8217;s restrictions<\/h3>\n<p>We insert in the temporary table the table name, the column name and the column type which can be candidate for SPARSE by using sp_MSForEachTable.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">INSERT #temp\nEXEC sp_MSForEachTable '\n SELECT DISTINCT\nsys.columns.name ColumnName,\nsys.columns.column_id ColumnID,\nSUBSTRING(''?'', 8, Len(''?'')-8) AS TableName,\nsys.columns.object_id TableID,\nCASE systypes.name \nWHEN ''sysname'' THEN ''nvarchar''\nELSE systypes.name\nEND AS TypeName,\nsys.columns.is_sparse IsParse,\nsys.columns.is_nullable IsNullable,\n0,0,0 \n FROM sys.columns (NoLock) \nINNER JOIN systypes (NoLock) ON systypes.xtype = sys.columns.system_type_id\n WHERE sys.columns.object_id = (\nSELECT id FROM sysobjects (NoLock)\nWHERE name = SUBSTRING(''?'', 8, Len(''?'')-8))'<\/pre>\n<p>But, we can optimize this query with a first part of the usage restrictions for the properties of Spare columns:<\/p>\n<ul>\n<li>The following data types cannot be specified as SPARSE: geography, geometry, image, ntext, text and timestamp<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">systypes.name NOT IN (&#8221;geography&#8221;, &#8221;geometry&#8221;, &#8221;image&#8221;, &#8221;ntext&#8221;, &#8221;text&#8221;, &#8221;timestamp&#8221;)<\/p>\n<ul>\n<li>A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.is_nullable = 1<br \/>\nsys.columns.is_rowguidcol = 0<br \/>\nsys.columns.is_identity = 0<\/p>\n<ul>\n<li>A sparse column cannot have the FILESTREAM attribute<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.is_filestream = 0<\/p>\n<ul>\n<li>A sparse column cannot have a default value<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.default_object_id = 0<\/p>\n<ul>\n<li>A sparse column cannot be bound to a rule<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.rule_object_id = 0<\/p>\n<ul>\n<li>Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.is_computed = 0<\/p>\n<ul>\n<li>A sparse column cannot be part of a user-defined table type<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.system_type_id=sys.columns.user_type_id<\/p>\n<ul>\n<li>And if the columns have the Spare properties, naturally we don&#8217;t count this columns<\/li>\n<\/ul>\n<p style=\"padding-left: 60px\">sys.columns.is_sparse = 0<\/p>\n<p>The new query with the restricted usage is :<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">INSERT #temp\nEXEC sp_MSForEachTable '\n SELECT DISTINCT\nsys.columns.name ColumnName,\nsys.columns.column_id ColumnID,\nSUBSTRING(''?'', 8, Len(''?'')-8) AS TableName,\nsys.columns.object_id TableID,\nCASE systypes.name \nWHEN ''sysname'' THEN ''nvarchar''\nELSE systypes.name\nEND AS TypeName,\nsys.columns.is_sparse IsParse,\nsys.columns.is_nullable IsNullable,\n0,0,0 \n FROM sys.columns (NoLock) \nINNER JOIN systypes (NoLock) ON systypes.xtype = sys.columns.system_type_id\n WHERE sys.columns.object_id = (\nSELECT id FROM sysobjects (NoLock)\nWHERE name = SUBSTRING(''?'', 8, Len(''?'')-8)\nand systypes.name NOT IN (''geography'', ''geometry'', ''image'', ''ntext'', ''text'', ''timestamp'')\nand sys.columns.is_sparse = 0\nand sys.columns.is_nullable = 1\nand sys.columns.is_rowguidcol = 0\nand sys.columns.is_identity = 0\nand sys.columns.is_computed = 0\nand sys.columns.is_filestream = 0\nand sys.columns.default_object_id = 0\nand sys.columns.rule_object_id = 0\nand sys.columns.system_type_id=sys.columns.user_type_id\n)'<\/pre>\n<p><samp><samp style=\"padding-left: 30px\"><br \/>\n<\/samp><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step2.png\" alt=\"\" width=\"574\" height=\"354\" \/><\/samp><\/p>\n<p>In our example, we have 182 rows for the databases AdventureWorksDW2008R2<\/p>\n<h3>Step 3: Usage restrictions on Index, PK and data compression<\/h3>\n<p>This query deletes all columns that can be a part of a clustered index or a unique primary key(PK) index :<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">delete tps from #temp tps where exists (\nselect DISTINCT 'Exists' from sys.columns\ninner join sys.indexes i on i.object_id = tps.TableId\ninner join sys.index_columns ic on ic.column_id = tps.ColumnID inner join sys.columns c on c.object_id = tps.TableId and ic.column_id = c.column_id\nwhere i.type =1 or i.is_primary_key = 1)<\/pre>\n<p><samp style=\"padding-left: 30px\"><br \/>\n<\/samp><samp style=\"padding-left: 30px\"><br \/>\n<\/samp><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step3.png\" alt=\"\" width=\"443\" height=\"301\" \/><\/p>\n<p>We reduce the number of row at 95 in this sample.<br \/>\nSparse columns are incompatible with data compression<br \/>\nTo see if data compression is active, you can use this query:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">SELECT partition_id,\nOBJECT_NAME(object_id) AS objectname,\ndata_compression_desc,data_compression\nFROM sys.partitions where data_compression&lt;&gt;0;<\/pre>\n<p><samp><br \/>\n<\/samp><samp><br \/>\n<\/samp><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step31.png\" alt=\"\" width=\"380\" height=\"296\" \/><\/p>\n<p>We can see that one table (DimEmployee) use ROW Compression.<br \/>\nTo delete columns with data compression, we use this query:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">delete tps from #temp tps inner join sys.partitions p \non p.object_id = tps.TableId where p.data_compression&lt;&gt;0;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step32.png\" alt=\"\" width=\"464\" height=\"254\" \/><\/p>\n<p>We reduce the number of row at 79.<\/p>\n<h3>Step 4: Add count information for the selected columns<\/h3>\n<p>In this step, we will get the information about the totally number of row, number of row with a &#8220;null&#8221; value and calculate the ratio in % between these 2 values.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">DECLARE @TableName nvarchar(1000)\nDECLARE @ColumnName nvarchar(1000) \nDECLARE @vQuery nvarchar(1000) \nDECLARE @result1 INT \nDECLARE @result2 INT\nDECLARE tables_cursor CURSOR FAST_FORWARD\n FOR\n SELECT TableName,ColumnName FROM #temp\n OPEN tables_cursor\nFETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName\n WHILE @@FETCH_STATUS = 0\n BEGIN\n -- Search the number of row in a table\n SET @vQuery = 'SELECT @result1= COUNT(*) FROM [' + @TableName + ']'\n EXEC SP_EXECUTESQL \n @Query = @vQuery \n , @Params = N'@result1 INT OUTPUT' \n , @result1 = @result1 OUTPUT \n -- Search the number of row in a table\n SET @vQuery = 'SELECT @result2= COUNT(*) FROM [' + @TableName + '] where [' + @ColumnName + '] is null'\n EXEC SP_EXECUTESQL \n @Query = @vQuery \n , @Params = N'@result2 INT OUTPUT' \n , @result2 = @result2 OUTPUT \n update #temp set NumberOfRow = @result1,NumberOfRowNULL = @result2,Ratio = (@result2*100\/@result1) where \nColumnName=@ColumnName and TableName=@TableName\n FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName\n END\n CLOSE tables_cursor\nDEALLOCATE tables_cursor<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step4.png\" alt=\"2Step4\" width=\"453\" height=\"302\" \/><\/p>\n<p>For the end, we delete all line that not has a NULL value in NumberOfRowNULL and NumberOfRow columns :<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">delete tps from #temp tps where NumberOfRowNULL = 0 or NumberOfRow = 0<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step41.png\" alt=\"\" width=\"635\" height=\"467\" \/><\/p>\n<h3>Conclusion<\/h3>\n<p>In this example, you can see that the column MinPaymentAmount from the table DimReseller has 72% of Null value.<br \/>\nIn the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc280604.aspx\">msdn web site<\/a>, you can see that for a money type, we win 40% for 52% of null value.<\/p>\n<p><span style=\"color: #ff0000\">This is clearly a good candidate to be SPARE column.<\/span><\/p>\n<p>msdn extract :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" style=\"margin-left: auto;margin-right: auto\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2Step42.png\" alt=\"2Step42\" width=\"825\" height=\"353\" \/><\/p>\n<p>This code can be optimized but the different steps above help the comprehension of this functionality.<br \/>\nFor my example, this is the changed query :<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">ALTER TABLE DimReseller ALTER COLUMN MinPaymentAmount ADD SPARSE<\/pre>\n<p>You can download the detection script <a title=\"sparsecolumninfo script\" href=\"http:\/\/dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2011\/09\/sparsecolumninfo.zip\">here<\/a>. It&#8217;s free ! \ud83d\ude44<\/p>\n","protected":false},"excerpt":{"rendered":"<p>dbi services search and test all tools or functions possible to optimize or to win something to propose the best tuning possible in their client.<br \/>The property of Spare columns is not the most used in client environment that we have seen.<br \/>This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).<\/p>\n<p>The database example that we used, is AdventureWorksDW2008R2.<\/p>\n","protected":false},"author":15,"featured_media":2299,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[23,49,265],"type_dbi":[],"class_list":["post-2298","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-integration-middleware","tag-dba","tag-microsoft","tag-sql-server-2008"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server Tuning: Detect SPARSE columns candidate - dbi Blog<\/title>\n<meta name=\"description\" content=\"This is a sql server tuning article. This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Tuning: Detect SPARSE columns candidate\" \/>\n<meta property=\"og:description\" content=\"This is a sql server tuning article. This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-09-19T11:40:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1015\" \/>\n\t<meta property=\"og:image:height\" content=\"440\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server Tuning: Detect SPARSE columns candidate\",\"datePublished\":\"2011-09-19T11:40:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/\"},\"wordCount\":714,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/1CreateTable.png\",\"keywords\":[\"DBA\",\"Microsoft\",\"SQL Server 2008\"],\"articleSection\":[\"Application integration &amp; Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/\",\"name\":\"SQL Server Tuning: Detect SPARSE columns candidate - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/1CreateTable.png\",\"datePublished\":\"2011-09-19T11:40:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\"},\"description\":\"This is a sql server tuning article. This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/1CreateTable.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/1CreateTable.png\",\"width\":1015,\"height\":440},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-tuning-detect-sparse-columns-candidate\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Tuning: Detect SPARSE columns candidate\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \\\/ Financial Services, and many other industries.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/stephane-haby\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server Tuning: Detect SPARSE columns candidate - dbi Blog","description":"This is a sql server tuning article. This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Tuning: Detect SPARSE columns candidate","og_description":"This is a sql server tuning article. This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/","og_site_name":"dbi Blog","article_published_time":"2011-09-19T11:40:00+00:00","og_image":[{"width":1015,"height":440,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png","type":"image\/png"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server Tuning: Detect SPARSE columns candidate","datePublished":"2011-09-19T11:40:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/"},"wordCount":714,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png","keywords":["DBA","Microsoft","SQL Server 2008"],"articleSection":["Application integration &amp; Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/","name":"SQL Server Tuning: Detect SPARSE columns candidate - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png","datePublished":"2011-09-19T11:40:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"description":"This is a sql server tuning article. This article present a script to find the potential columns that can have the sparse property with the usage restrictions to make a space tuning in your database and especially for Data Warehouse (DWH).","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/1CreateTable.png","width":1015,"height":440},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tuning-detect-sparse-columns-candidate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server Tuning: Detect SPARSE columns candidate"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2298"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2298\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/2299"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2298"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}