{"id":699,"date":"2008-04-08T17:50:00","date_gmt":"2008-04-08T17:50:00","guid":{"rendered":"\/blogs\/bobb\/post\/Converting-an-EAV-design-to-sparse-columns-and-populating.aspx"},"modified":"2008-04-08T17:50:00","modified_gmt":"2008-04-08T17:50:00","slug":"converting-an-eav-design-to-sparse-columns-and-populating","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/","title":{"rendered":"Converting an EAV design to sparse columns and populating"},"content":{"rendered":"

\nOne of the uses for sparse columns will likely be replacing entity-attribute-value designs. In an EAV design, the "sparse attribute" values are represented by a separate table. The attribute value can either be declared as (n)varchar or sql_variant. An example I've always used is the hardware store that wants to keep track of its products in a "products" table, but each product has different attributes. If you use the minimum number of columns to simplify the example, the EAV design looks like this.\n<\/p>\n

\ncreate table products — base table
\n(
\n id int primary key,
\n name varchar(max)
\n)
\ngo
\ncreate table properties — sparse attribute (name-value pair) table
\n(
\n id int,
\n name varchar(50),
\n value varchar(max),
\n CONSTRAINT PK_properties PRIMARY KEY (id, name),
\n FOREIGN KEY (id) REFERENCES products (id)
\n)
\ngo\n<\/p>\n

\nHere's a straightforward way to convert the table to use SQL Server 2008's sparse columns. It uses dynamic SQL, but in this case there's no user input (SQL injection worries).\n<\/p>\n

\ndeclare @tab nvarchar(max),
\n        @sql nvarchar(max)\n<\/p>\n

\nset @tab=N'create table products2 (id int primary key, name nvarchar(max) '
\nselect  @tab=@tab+','+ name + ' varchar(max) sparse' from properties
\ngroup by name
\nset @tab += ' ,col_values xml column_set for all_sparse_columns);'
\n— select @tab
\nexec(@tab)\n<\/p>\n

\nPopulating it is also straghtforward using the pivot operator, introduced in SQL Server 2005. In the case where each item has only one of each sparse property (the table constraint enforces this), and our properties table has only three columns, there's no real aggregation with pivot. The aggregate is just required by the pivot operator syntax. This populates the table:\n<\/p>\n

\ndeclare @col nvarchar(max),
\n        @sql nvarchar(max)\n<\/p>\n

\nset @cols=N''
\nselect  @cols=@cols+','+ name from properties
\ngroup by name
\n select @cols
\nset @cols=substring(@cols,2,datalength(@cols)\/2 – 1)
\nset @sql=N'insert into products2 (id, name,' + @cols + ') select p.id, p.name, '+@cols<\/a>+
\n      N' from (
\n      select id, name, value
\n      from properties
\n      ) as q
\n      pivot
\n      (
\n      max(value)
\n      for name in ('+@cols<\/a>+
\n      ')
\n      ) as PivotTable'
\nset @sql += ' join products p on PivotTable.id = p.id'
\n— select @sql
\nexec (@sql)\n<\/p>\n

\nWhat remains to be done now is to choose better data types for the sparse columns, if the data isn't really a string. If you haven't enforced value type correctness in the application, this may require some data cleansing. Full example as an attachment. Remember that currently (in CTP6) you can only have 1024 total columns in a table; but the limit will be increased to 30000 sparse columns before RTM.\n<\/p>\n

eav_to_sparse.zip (1.23 KB)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

One of the uses for sparse columns will likely be replacing entity-attribute-value designs. In an EAV design, the "sparse attribute" values are represented by a separate table. The attribute value can either be declared as (n)varchar or sql_variant. An example I've always used is the hardware store that wants to keep track of its products […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,29],"tags":[],"class_list":["post-699","post","type-post","status-publish","format-standard","hentry","category-sparse-columns","category-sql-server-2008"],"yoast_head":"\nConverting an EAV design to sparse columns and populating - Bob Beauchemin<\/title>\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.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Converting an EAV design to sparse columns and populating - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"One of the uses for sparse columns will likely be replacing entity-attribute-value designs. In an EAV design, the "sparse attribute" values are represented by a separate table. The attribute value can either be declared as (n)varchar or sql_variant. An example I've always used is the hardware store that wants to keep track of its products […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2008-04-08T17:50:00+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/\",\"name\":\"Converting an EAV design to sparse columns and populating - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2008-04-08T17:50:00+00:00\",\"dateModified\":\"2008-04-08T17:50:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Sparse Columns\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sparse-columns\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Converting an EAV design to sparse columns and populating\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Converting an EAV design to sparse columns and populating - Bob Beauchemin","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.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/","og_locale":"en_US","og_type":"article","og_title":"Converting an EAV design to sparse columns and populating - Bob Beauchemin","og_description":"One of the uses for sparse columns will likely be replacing entity-attribute-value designs. In an EAV design, the "sparse attribute" values are represented by a separate table. The attribute value can either be declared as (n)varchar or sql_variant. An example I've always used is the hardware store that wants to keep track of its products […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/","og_site_name":"Bob Beauchemin","article_published_time":"2008-04-08T17:50:00+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/","name":"Converting an EAV design to sparse columns and populating - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2008-04-08T17:50:00+00:00","dateModified":"2008-04-08T17:50:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/converting-an-eav-design-to-sparse-columns-and-populating\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Sparse Columns","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sparse-columns\/"},{"@type":"ListItem","position":3,"name":"Converting an EAV design to sparse columns and populating"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/699","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=699"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/699\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}