none
What are the Pros and Cons of storing delimited value in a single record field?

Answers

  • Pro: If you'll never, ever, need to apply a filter to those values nor will there ever be the need to format the data then you can "save" additional columns.

    Cons:

    (a) Such a concept usually violates Codd's rules for the definition of an RDBMS. Especially, it'll violate rule #2 (It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing table. [quoted from Wiki, Codd's_12_rules]). It'll only be compliant with rule #2, if the delimited values are considered a "scalar value".

    Even though there my not be the need to filter on those data at the moment, it may happen sooner than later. If that happens, then benefit of an index cannot be used, eventually leading to poor performance.

    (b) If there's ever the need to update/delete/insert a single elment of such a delimited list, it's going to take much more effort than a simple UPDATE/DELETE/INSERT statement

    (c) There's no way to implement any kind of referential integrity, e.g. each element of such a "list" in table1 needs to be found in table2.

    (d) The storage size required for a column should match the data stored in that column and it should be possible to perform a profound calculation of the data size needed. It's hard to do that for a delimited value of an unknown length.

    (e) If there's the real need to store delimited values in one column, at least use the XML data type (together with the data being in XML format, too). This data can be queried and modified using SQL Server standard language (e.g. XQuery) without any major manual coding.

    Wednesday, August 13, 2014 9:17 PM
  • What are the Pros and Cons of storing delimited value in a single record field?

    This is very very rarely the right thing to do. The only time it would be OK is if you only use the database a unintelligent data store for key/value pairs and will never ever query it. Which is quite a waste of resources, since SQL Server is made for querying.

    When you put data in a delimited string, any querying against the data will be immensly painful.

    So the short answer is: don't do it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 13, 2014 10:00 PM
  • What are the Pros and Cons of storing delimited value in a single record field?

    Basically a good thing if the field is used by the application as a storage.

    That means the application manipulates the delimited values string.

    As noted above, if relational operations are necessary on the values, then it is not a good idea.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Friday, August 22, 2014 12:42 AM
    Moderator

All replies

  • Pro: If you'll never, ever, need to apply a filter to those values nor will there ever be the need to format the data then you can "save" additional columns.

    Cons:

    (a) Such a concept usually violates Codd's rules for the definition of an RDBMS. Especially, it'll violate rule #2 (It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing table. [quoted from Wiki, Codd's_12_rules]). It'll only be compliant with rule #2, if the delimited values are considered a "scalar value".

    Even though there my not be the need to filter on those data at the moment, it may happen sooner than later. If that happens, then benefit of an index cannot be used, eventually leading to poor performance.

    (b) If there's ever the need to update/delete/insert a single elment of such a delimited list, it's going to take much more effort than a simple UPDATE/DELETE/INSERT statement

    (c) There's no way to implement any kind of referential integrity, e.g. each element of such a "list" in table1 needs to be found in table2.

    (d) The storage size required for a column should match the data stored in that column and it should be possible to perform a profound calculation of the data size needed. It's hard to do that for a delimited value of an unknown length.

    (e) If there's the real need to store delimited values in one column, at least use the XML data type (together with the data being in XML format, too). This data can be queried and modified using SQL Server standard language (e.g. XQuery) without any major manual coding.

    Wednesday, August 13, 2014 9:17 PM
  • What are the Pros and Cons of storing delimited value in a single record field?

    This is very very rarely the right thing to do. The only time it would be OK is if you only use the database a unintelligent data store for key/value pairs and will never ever query it. Which is quite a waste of resources, since SQL Server is made for querying.

    When you put data in a delimited string, any querying against the data will be immensly painful.

    So the short answer is: don't do it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 13, 2014 10:00 PM
  • What are the Pros and Cons of storing delimited value in a single record field?

    Basically a good thing if the field is used by the application as a storage.

    That means the application manipulates the delimited values string.

    As noted above, if relational operations are necessary on the values, then it is not a good idea.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Friday, August 22, 2014 12:42 AM
    Moderator
  • You do not even know that rows are not records, and fields are not column! 

    Please read any book on RDBMS, so you will not embarrass yourself in the future. Look up Normal Forms.

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 22, 2014 10:10 PM