|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?concatenating of column data in sql.Faq2559,written by a very knowledgable MVP,is often cited as a reference for this problem's solution.This problem is interesting as it has spurred debate on a number of issues including client vs. server, static vs. dynamic sql and the nature of the sql solutions. Perhaps there is even more of interest here between the code. For example, the author starts out with the following: >This question is asked quite frequently. People want to take a column like And return a resultset like this:>this: Color ------ red orange blue green Colors ------------------------- >red,orange,blue,green by stating:>This isn't exactly relational... Has anyone wondered exactly what 'isn't exactly relational' means?I assume I'm safe in assuming he is referring to the resultset. Which part is relational and which part is not relational?(Putting aside the idea whether it even makes sense to refer to a resultset as being 'relational').Is it the comma's that cause the problem? What about: 1,100,100 which could be money?Has anyone said this is not exactly relational? Or John's Pizza Hut Is the apostrophe objectionable? Nothing wrong with: Lastname+','+Firstname is there? Or does the fuzzy part of relational come from the fact that the result came from a single column?This is interesting since 2V logic, whether a result is relational or quasi-relational,is predicated on 'where' the result came from.So we don't know if '1,100,100' is relational or almost relational unless we know 'exactly' where it came from.What if we don't know where it came from?Is the result null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented that a 'pure' sql solution to concatenation 'would never destroy 1NF by building a list structure..'.Sounds real good but it is Bizarre! Perhaps he's suggesting a new type of check constraint.The 99.9% ivory soap constraint:) Anyone on what this phrase means? Here's another one.The author states: 'SQL Server 2005 adds some interesting options for this kind of non-relational query.' What is a 'non-relational' query?Is it a query that produces a result that isn't exactly relational?Is it a query that some dislike?You can see where this is going:) (Note that another MVP referred to the xml solution as a 'set based solution'. but that is a subject for another thread:) Anyone on what a non-relational query is? It's obviously easier to code this stuff than it is to talk about it. Which would explain why sql remains such a mystery to so many.Be that as it may, to try to get some clarity in all the haze is much more interesting,challenging and fun than coding it.You just gotta love the glass:) (The author of the faq is to be recognized for his great contributions and assistance to the server community.His words have impact and the bar is set high in such a case). musings from www.rac4sql.net Hi
In my opinion the list of contacanation values ( if you want to store it in the table) would destroy 1NF However , you can generate a report using this technique but it would be better if we build such reports on the client side . One reason is that you cannot guarantee the ordering of the result set > Or John's Pizza Hut When you design the database you would like to have two separated colums > Is the apostrophe objectionable? > Nothing wrong with: > Lastname+','+Firstname > is there? fo Firstname and Lastname as well. In result set as a report you may contacanate these columns depends upon requirements I have my doubt i have answered your question , so It is my opinion only Show quoteHide quote "05ponyGT" <non***@overwood.com> wrote in message news:Or7TnghLGHA.2036@TK2MSFTNGP14.phx.gbl... > Lately there seems to be quite a few posts regarding the > concatenating of column data in sql.Faq2559,written by a very > knowledgable MVP,is often cited as a reference for this > problem's solution.This problem is interesting as it has > spurred debate on a number of issues including client vs. server, > static vs. dynamic sql and the nature of the sql solutions. > Perhaps there is even more of interest here between the code. > For example, the author starts out with the following: > >>This question is asked quite frequently. People want to take a column like >>this: > Color > ------ > red > orange > blue > green > > And return a resultset like this: > > Colors > ------------------------- >>red,orange,blue,green > > by stating: >>This isn't exactly relational... > > Has anyone wondered exactly what 'isn't exactly relational' means? > I assume I'm safe in assuming he is referring to the resultset. > Which part is relational and which part is not relational?(Putting > aside the idea whether it even makes sense to refer to a resultset > as being 'relational').Is it the comma's that cause the problem? > What about: > 1,100,100 > which could be money?Has anyone said this is not exactly relational? > Or John's Pizza Hut > Is the apostrophe objectionable? > Nothing wrong with: > Lastname+','+Firstname > is there? > Or does the fuzzy part of relational come from the fact that the > result came from a single column?This is interesting since 2V logic, > whether a result is relational or quasi-relational,is predicated on > 'where' the result came from.So we don't know if '1,100,100' is > relational or almost relational unless we know 'exactly' where it > came from.What if we don't know where it came from?Is the result > null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented > that a 'pure' sql solution to concatenation 'would never destroy 1NF > by building a list structure..'.Sounds real good but it is Bizarre! > Perhaps he's suggesting a new type of check constraint.The 99.9% > ivory soap constraint:) > Anyone on what this phrase means? > > Here's another one.The author states: > 'SQL Server 2005 adds some interesting options for this kind of > non-relational > query.' > What is a 'non-relational' query?Is it a query that produces a result > that isn't exactly relational?Is it a query that some dislike?You can > see where this is going:) > (Note that another MVP referred to the xml solution as a 'set based > solution'. > but that is a subject for another thread:) > Anyone on what a non-relational query is? > > It's obviously easier to code this stuff than it is to talk about it. > Which would explain why sql remains such a mystery to so many.Be that > as it may, to try to get some clarity in all the haze is much more > interesting,challenging and fun than coding it.You just gotta love > the glass:) > > (The author of the faq is to be recognized for his great contributions > and assistance to the server community.His words have impact and the > bar is set high in such a case). > > musings from > www.rac4sql.net > > > > > Anybody wanting concatenation like that and is using SQL Server 2005 should
be using the FOR XML in there first instance, it scales, is fully documented in books online and works really well. > One reason is that you cannot guarantee the ordering of the result set Yes you can in SQL Server 2005!Use the FOR XML syntax.... declare @colour table ( name nvarchar( 50 ) not null primary key ) insert @colour ( name ) values( 'Red' ) insert @colour ( name ) values( 'Green' ) insert @colour ( name ) values( 'Blue' ) select * from @colour -- Now concatenated select ( select name + ', ' as [text()] from @colour order by name for xml path( '' ) ) as colour_name_concatentated -- More powerful grouping example select distinct type, ( select name + ', ' as [text()] from sys.objects s2 where s2.type = s1.type order by s2.name desc for xml path( '' ) ) as concatenated_names from sys.objects s1 order by type desc Tony. Show quoteHide quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:ODn2yrhLGHA.2912@tk2msftngp13.phx.gbl... > Hi > > In my opinion the list of contacanation values ( if you want to store it > in the table) would destroy 1NF > > However , you can generate a report using this technique but it would be > better if we build such reports on the client side . > One reason is that you cannot guarantee the ordering of the result set > >> Or John's Pizza Hut >> Is the apostrophe objectionable? >> Nothing wrong with: >> Lastname+','+Firstname >> is there? > > When you design the database you would like to have two separated colums > fo Firstname and Lastname as well. > In result set as a report you may contacanate these columns depends upon > requirements > > I have my doubt i have answered your question , so It is my opinion only > > > > > "05ponyGT" <non***@overwood.com> wrote in message > news:Or7TnghLGHA.2036@TK2MSFTNGP14.phx.gbl... >> Lately there seems to be quite a few posts regarding the >> concatenating of column data in sql.Faq2559,written by a very >> knowledgable MVP,is often cited as a reference for this >> problem's solution.This problem is interesting as it has >> spurred debate on a number of issues including client vs. server, >> static vs. dynamic sql and the nature of the sql solutions. >> Perhaps there is even more of interest here between the code. >> For example, the author starts out with the following: >> >>>This question is asked quite frequently. People want to take a column >>>like this: >> Color >> ------ >> red >> orange >> blue >> green >> >> And return a resultset like this: >> >> Colors >> ------------------------- >>>red,orange,blue,green >> >> by stating: >>>This isn't exactly relational... >> >> Has anyone wondered exactly what 'isn't exactly relational' means? >> I assume I'm safe in assuming he is referring to the resultset. >> Which part is relational and which part is not relational?(Putting >> aside the idea whether it even makes sense to refer to a resultset >> as being 'relational').Is it the comma's that cause the problem? >> What about: >> 1,100,100 >> which could be money?Has anyone said this is not exactly relational? >> Or John's Pizza Hut >> Is the apostrophe objectionable? >> Nothing wrong with: >> Lastname+','+Firstname >> is there? >> Or does the fuzzy part of relational come from the fact that the >> result came from a single column?This is interesting since 2V logic, >> whether a result is relational or quasi-relational,is predicated on >> 'where' the result came from.So we don't know if '1,100,100' is >> relational or almost relational unless we know 'exactly' where it >> came from.What if we don't know where it came from?Is the result >> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented >> that a 'pure' sql solution to concatenation 'would never destroy 1NF >> by building a list structure..'.Sounds real good but it is Bizarre! >> Perhaps he's suggesting a new type of check constraint.The 99.9% >> ivory soap constraint:) >> Anyone on what this phrase means? >> >> Here's another one.The author states: >> 'SQL Server 2005 adds some interesting options for this kind of >> non-relational >> query.' >> What is a 'non-relational' query?Is it a query that produces a result >> that isn't exactly relational?Is it a query that some dislike?You can >> see where this is going:) >> (Note that another MVP referred to the xml solution as a 'set based >> solution'. >> but that is a subject for another thread:) >> Anyone on what a non-relational query is? >> >> It's obviously easier to code this stuff than it is to talk about it. >> Which would explain why sql remains such a mystery to so many.Be that >> as it may, to try to get some clarity in all the haze is much more >> interesting,challenging and fun than coding it.You just gotta love >> the glass:) >> >> (The author of the faq is to be recognized for his great contributions >> and assistance to the server community.His words have impact and the >> bar is set high in such a case). >> >> musings from >> www.rac4sql.net >> >> >> >> >> > > Probably best reading this http://en.wikipedia.org/wiki/Relational_model, it
gives a good explanation. As for the concatenation being non-relational, it depends on what you are doing. If 'red, green, blue' where stored in a description column does that then mean that column is now non-relational? My take is that just because we merge rows in this way does not break normal form because the new data becomes something else, its no longer a set of colours its a set of descriptors, probably for data export or for scalability reasons so the client or middle tier doesn't have to do the work. Now consider the SQL in SQL Server 2005 that does concatenation... select distinct type, ( select name + ', ' as [text()] from sys.objects s2 where s2.type = s1.type order by s2.name desc for xml path( '' ) ) as concatenated_names from sys.objects s1 order by type desc This still yeilds a set of data, that set can be used in other queries, inserted - whatever! I hear arguments that this type of thing should be done in the client application, imho thats just plain wrong now, the FOR XML extensions are extremely powerful and scale extremely well, and the code is very succinct and maintainable. It would take dozens of lines of code to do this client side, which is one maintanence and development overhead, but the other overhead is that you need to do it in each application that wants that data, that might be ASP.NET in one instance, RS in another etc... This leads me into the debate on where do you put business logic now, Jim Gray has done some really good research on this and his take is that the SQL Server (not the database because SQL Server 'the product' is more than just a data store) is becoming the central place to hold logic that can be shared via multitudes of applications - it saves you coding a middle tier and having all that infrastructure. SQL Server can be a web service now, has CLR etc... The biggest single thing that people will need to change their thinking on is should SQL Server be exposed that close to the internet say, there are ways to proxy stuff - but its a discussion. Anyway, I'm rambling completely off topic. Tony. Show quoteHide quote "05ponyGT" <non***@overwood.com> wrote in message news:Or7TnghLGHA.2036@TK2MSFTNGP14.phx.gbl... > Lately there seems to be quite a few posts regarding the > concatenating of column data in sql.Faq2559,written by a very > knowledgable MVP,is often cited as a reference for this > problem's solution.This problem is interesting as it has > spurred debate on a number of issues including client vs. server, > static vs. dynamic sql and the nature of the sql solutions. > Perhaps there is even more of interest here between the code. > For example, the author starts out with the following: > >>This question is asked quite frequently. People want to take a column like >>this: > Color > ------ > red > orange > blue > green > > And return a resultset like this: > > Colors > ------------------------- >>red,orange,blue,green > > by stating: >>This isn't exactly relational... > > Has anyone wondered exactly what 'isn't exactly relational' means? > I assume I'm safe in assuming he is referring to the resultset. > Which part is relational and which part is not relational?(Putting > aside the idea whether it even makes sense to refer to a resultset > as being 'relational').Is it the comma's that cause the problem? > What about: > 1,100,100 > which could be money?Has anyone said this is not exactly relational? > Or John's Pizza Hut > Is the apostrophe objectionable? > Nothing wrong with: > Lastname+','+Firstname > is there? > Or does the fuzzy part of relational come from the fact that the > result came from a single column?This is interesting since 2V logic, > whether a result is relational or quasi-relational,is predicated on > 'where' the result came from.So we don't know if '1,100,100' is > relational or almost relational unless we know 'exactly' where it > came from.What if we don't know where it came from?Is the result > null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented > that a 'pure' sql solution to concatenation 'would never destroy 1NF > by building a list structure..'.Sounds real good but it is Bizarre! > Perhaps he's suggesting a new type of check constraint.The 99.9% > ivory soap constraint:) > Anyone on what this phrase means? > > Here's another one.The author states: > 'SQL Server 2005 adds some interesting options for this kind of > non-relational > query.' > What is a 'non-relational' query?Is it a query that produces a result > that isn't exactly relational?Is it a query that some dislike?You can > see where this is going:) > (Note that another MVP referred to the xml solution as a 'set based > solution'. > but that is a subject for another thread:) > Anyone on what a non-relational query is? > > It's obviously easier to code this stuff than it is to talk about it. > Which would explain why sql remains such a mystery to so many.Be that > as it may, to try to get some clarity in all the haze is much more > interesting,challenging and fun than coding it.You just gotta love > the glass:) > > (The author of the faq is to be recognized for his great contributions > and assistance to the server community.His words have impact and the > bar is set high in such a case). > > musings from > www.rac4sql.net > > > > > Could it be that in IT 'I code therefore I am' has replaced 'I think
therefore I am':) We need more knowledgable people 'rambling' about the things we often take for granted.I hope you ramble more for all our sakes:) Show quoteHide quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:uRLVZqiLGHA.500@TK2MSFTNGP15.phx.gbl... > Probably best reading this http://en.wikipedia.org/wiki/Relational_model, > it gives a good explanation. > > As for the concatenation being non-relational, it depends on what you are > doing. > > If 'red, green, blue' where stored in a description column does that then > mean that column is now non-relational? > > My take is that just because we merge rows in this way does not break > normal form because the new data becomes something else, its no longer a > set of colours its a set of descriptors, probably for data export or for > scalability reasons so the client or middle tier doesn't have to do the > work. > > Now consider the SQL in SQL Server 2005 that does concatenation... > > select distinct type, > ( select name + ', ' as [text()] > from sys.objects s2 > where s2.type = s1.type > order by s2.name desc > for xml path( '' ) ) as concatenated_names > from sys.objects s1 > order by type desc > > This still yeilds a set of data, that set can be used in other queries, > inserted - whatever! > > I hear arguments that this type of thing should be done in the client > application, imho thats just plain wrong now, the FOR XML extensions are > extremely powerful and scale extremely well, and the code is very succinct > and maintainable. It would take dozens of lines of code to do this client > side, which is one maintanence and development overhead, but the other > overhead is that you need to do it in each application that wants that > data, that might be ASP.NET in one instance, RS in another etc... > > This leads me into the debate on where do you put business logic now, Jim > Gray has done some really good research on this and his take is that the > SQL Server (not the database because SQL Server 'the product' is more than > just a data store) is becoming the central place to hold logic that can be > shared via multitudes of applications - it saves you coding a middle tier > and having all that infrastructure. SQL Server can be a web service now, > has CLR etc... The biggest single thing that people will need to change > their thinking on is should SQL Server be exposed that close to the > internet say, there are ways to proxy stuff - but its a discussion. > > Anyway, I'm rambling completely off topic. > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "05ponyGT" <non***@overwood.com> wrote in message > news:Or7TnghLGHA.2036@TK2MSFTNGP14.phx.gbl... >> Lately there seems to be quite a few posts regarding the >> concatenating of column data in sql.Faq2559,written by a very >> knowledgable MVP,is often cited as a reference for this >> problem's solution.This problem is interesting as it has >> spurred debate on a number of issues including client vs. server, >> static vs. dynamic sql and the nature of the sql solutions. >> Perhaps there is even more of interest here between the code. >> For example, the author starts out with the following: >> >>>This question is asked quite frequently. People want to take a column >>>like this: >> Color >> ------ >> red >> orange >> blue >> green >> >> And return a resultset like this: >> >> Colors >> ------------------------- >>>red,orange,blue,green >> >> by stating: >>>This isn't exactly relational... >> >> Has anyone wondered exactly what 'isn't exactly relational' means? >> I assume I'm safe in assuming he is referring to the resultset. >> Which part is relational and which part is not relational?(Putting >> aside the idea whether it even makes sense to refer to a resultset >> as being 'relational').Is it the comma's that cause the problem? >> What about: >> 1,100,100 >> which could be money?Has anyone said this is not exactly relational? >> Or John's Pizza Hut >> Is the apostrophe objectionable? >> Nothing wrong with: >> Lastname+','+Firstname >> is there? >> Or does the fuzzy part of relational come from the fact that the >> result came from a single column?This is interesting since 2V logic, >> whether a result is relational or quasi-relational,is predicated on >> 'where' the result came from.So we don't know if '1,100,100' is >> relational or almost relational unless we know 'exactly' where it >> came from.What if we don't know where it came from?Is the result >> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented >> that a 'pure' sql solution to concatenation 'would never destroy 1NF >> by building a list structure..'.Sounds real good but it is Bizarre! >> Perhaps he's suggesting a new type of check constraint.The 99.9% >> ivory soap constraint:) >> Anyone on what this phrase means? >> >> Here's another one.The author states: >> 'SQL Server 2005 adds some interesting options for this kind of >> non-relational >> query.' >> What is a 'non-relational' query?Is it a query that produces a result >> that isn't exactly relational?Is it a query that some dislike?You can >> see where this is going:) >> (Note that another MVP referred to the xml solution as a 'set based >> solution'. >> but that is a subject for another thread:) >> Anyone on what a non-relational query is? >> >> It's obviously easier to code this stuff than it is to talk about it. >> Which would explain why sql remains such a mystery to so many.Be that >> as it may, to try to get some clarity in all the haze is much more >> interesting,challenging and fun than coding it.You just gotta love >> the glass:) >> >> (The author of the faq is to be recognized for his great contributions >> and assistance to the server community.His words have impact and the >> bar is set high in such a case). >> >> musings from >> www.rac4sql.net >> >> >> >> >> > > Here is another interesing "thought experiment". You have a table that stores
information about people. Let's say there are three columns CREATE TABLE People ( PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY, PersonName varchar(512) NOT NULL, Address varchar(512) NOT NULL ) An entry may look like PersonId PersonName Address ------------------------------------------------------------------------------- 101 Mark Williams 1519 15th Street, Albany, NY 12183 One could arguably way that storing the address in this way violates 1NF, so you redesign your table. CREATE TABLE People ( PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY, PersonName varchar(512) NOT NULL, StreetAddress varchar(512) NOT NULL, City varchar(512) NOT NULL, State char(2) NOT NULL, ZipCode int NOT NULL ) All fine, but now someone comes along and says your table isn't in BCNF (or 5NF) because there's a non trivial functional dependency (ZipCode depends on City / State). Relational theory does not expressly prohibit multi-valued attributes, like XML, so the table I started with could have been (at least in SQL 2005) CREATE TABLE People ( PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY, PersonName varchar(512) NOT NULL, Address xml ) And now the table is (somewhat magically) back to BCNF, just by changing the datatype that the Addrress was expressed in. To me, that means that it was always in BCNF, even though you used commas to express the address. -- Show quoteHide quoteIf you posted to this forum through TechNet, and you found my answers helpful, please mark them as answers. "05ponyGT" wrote: > Lately there seems to be quite a few posts regarding the > concatenating of column data in sql.Faq2559,written by a very > knowledgable MVP,is often cited as a reference for this > problem's solution.This problem is interesting as it has > spurred debate on a number of issues including client vs. server, > static vs. dynamic sql and the nature of the sql solutions. > Perhaps there is even more of interest here between the code. > For example, the author starts out with the following: > > >This question is asked quite frequently. People want to take a column like > >this: > Color > ------ > red > orange > blue > green > > And return a resultset like this: > > Colors > ------------------------- > >red,orange,blue,green > > by stating: > >This isn't exactly relational... > > Has anyone wondered exactly what 'isn't exactly relational' means? > I assume I'm safe in assuming he is referring to the resultset. > Which part is relational and which part is not relational?(Putting > aside the idea whether it even makes sense to refer to a resultset > as being 'relational').Is it the comma's that cause the problem? > What about: > 1,100,100 > which could be money?Has anyone said this is not exactly relational? > Or John's Pizza Hut > Is the apostrophe objectionable? > Nothing wrong with: > Lastname+','+Firstname > is there? > Or does the fuzzy part of relational come from the fact that the > result came from a single column?This is interesting since 2V logic, > whether a result is relational or quasi-relational,is predicated on > 'where' the result came from.So we don't know if '1,100,100' is > relational or almost relational unless we know 'exactly' where it > came from.What if we don't know where it came from?Is the result > null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented > that a 'pure' sql solution to concatenation 'would never destroy 1NF > by building a list structure..'.Sounds real good but it is Bizarre! > Perhaps he's suggesting a new type of check constraint.The 99.9% > ivory soap constraint:) > Anyone on what this phrase means? > > Here's another one.The author states: > 'SQL Server 2005 adds some interesting options for this kind of > non-relational > query.' > What is a 'non-relational' query?Is it a query that produces a result > that isn't exactly relational?Is it a query that some dislike?You can > see where this is going:) > (Note that another MVP referred to the xml solution as a 'set based > solution'. > but that is a subject for another thread:) > Anyone on what a non-relational query is? > > It's obviously easier to code this stuff than it is to talk about it. > Which would explain why sql remains such a mystery to so many.Be that > as it may, to try to get some clarity in all the haze is much more > interesting,challenging and fun than coding it.You just gotta love > the glass:) > > (The author of the faq is to be recognized for his great contributions > and assistance to the server community.His words have impact and the > bar is set high in such a case). > > musings from > www.rac4sql.net > > > > > > Using xml to prove a relational point!It could only happen in the IT
industry:) Show quoteHide quote "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message news:48FAE3B3-9E61-452E-9E6B-614E07AF5299@microsoft.com... > Here is another interesing "thought experiment". You have a table that > stores > information about people. Let's say there are three columns > > CREATE TABLE People ( > PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY, > PersonName varchar(512) NOT NULL, > Address varchar(512) NOT NULL > ) > > An entry may look like > > PersonId PersonName Address > ------------------------------------------------------------------------------- > 101 Mark Williams 1519 15th Street, Albany, NY 12183 > > One could arguably way that storing the address in this way violates 1NF, > so > you redesign your table. > > CREATE TABLE People ( > PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY, > PersonName varchar(512) NOT NULL, > StreetAddress varchar(512) NOT NULL, > City varchar(512) NOT NULL, > State char(2) NOT NULL, > ZipCode int NOT NULL > ) > > All fine, but now someone comes along and says your table isn't in BCNF > (or > 5NF) because there's a non trivial functional dependency (ZipCode depends > on > City / State). > > Relational theory does not expressly prohibit multi-valued attributes, > like > XML, so the table I started with could have been (at least in SQL 2005) > > CREATE TABLE People ( > PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY, > PersonName varchar(512) NOT NULL, > Address xml > ) > > And now the table is (somewhat magically) back to BCNF, just by changing > the > datatype that the Addrress was expressed in. To me, that means that it was > always in BCNF, even though you used commas to express the address. > > -- > If you posted to this forum through TechNet, and you found my answers > helpful, please mark them as answers. > > > "05ponyGT" wrote: > >> Lately there seems to be quite a few posts regarding the >> concatenating of column data in sql.Faq2559,written by a very >> knowledgable MVP,is often cited as a reference for this >> problem's solution.This problem is interesting as it has >> spurred debate on a number of issues including client vs. server, >> static vs. dynamic sql and the nature of the sql solutions. >> Perhaps there is even more of interest here between the code. >> For example, the author starts out with the following: >> >> >This question is asked quite frequently. People want to take a column >> >like >> >this: >> Color >> ------ >> red >> orange >> blue >> green >> >> And return a resultset like this: >> >> Colors >> ------------------------- >> >red,orange,blue,green >> >> by stating: >> >This isn't exactly relational... >> >> Has anyone wondered exactly what 'isn't exactly relational' means? >> I assume I'm safe in assuming he is referring to the resultset. >> Which part is relational and which part is not relational?(Putting >> aside the idea whether it even makes sense to refer to a resultset >> as being 'relational').Is it the comma's that cause the problem? >> What about: >> 1,100,100 >> which could be money?Has anyone said this is not exactly relational? >> Or John's Pizza Hut >> Is the apostrophe objectionable? >> Nothing wrong with: >> Lastname+','+Firstname >> is there? >> Or does the fuzzy part of relational come from the fact that the >> result came from a single column?This is interesting since 2V logic, >> whether a result is relational or quasi-relational,is predicated on >> 'where' the result came from.So we don't know if '1,100,100' is >> relational or almost relational unless we know 'exactly' where it >> came from.What if we don't know where it came from?Is the result >> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented >> that a 'pure' sql solution to concatenation 'would never destroy 1NF >> by building a list structure..'.Sounds real good but it is Bizarre! >> Perhaps he's suggesting a new type of check constraint.The 99.9% >> ivory soap constraint:) >> Anyone on what this phrase means? >> >> Here's another one.The author states: >> 'SQL Server 2005 adds some interesting options for this kind of >> non-relational >> query.' >> What is a 'non-relational' query?Is it a query that produces a result >> that isn't exactly relational?Is it a query that some dislike?You can >> see where this is going:) >> (Note that another MVP referred to the xml solution as a 'set based >> solution'. >> but that is a subject for another thread:) >> Anyone on what a non-relational query is? >> >> It's obviously easier to code this stuff than it is to talk about it. >> Which would explain why sql remains such a mystery to so many.Be that >> as it may, to try to get some clarity in all the haze is much more >> interesting,challenging and fun than coding it.You just gotta love >> the glass:) >> >> (The author of the faq is to be recognized for his great contributions >> and assistance to the server community.His words have impact and the >> bar is set high in such a case). >> >> musings from >> www.rac4sql.net >> >> >> >> >> >> >> All fine, but now someone comes along and says your table isn't in BCNF Dependencies are business specific. If the specific business model does not >> (or 5NF) because there's a non trivial functional dependency (ZipCode >> depends on City / State). explicitly establish a transitive dependency as above, you don't have to consider it. >> Relational theory does not expressly prohibit multi-valued attributes, Relational theory does prohibit multi-valued attributes, but in this case >> like XML, so the table I started with could have been (at least in SQL >> 2005) since the SQL 2005 supports an XML type, Address values is just as scalar as CHAR, INT or DATETIME values. -- Anith > Relational theory does prohibit multi-valued attributes, but in this case In a recent publication by C.J. Date, Database In Depth from O'Reilly Press, > since the SQL 2005 supports an XML type, Address values is just as scalar as > CHAR, INT or DATETIME values. > he writes, verbatim, that relational theory does not prohibit multi-valued attributes. He spends at least 3 pages discussing multi-valued attributes, including arrays and even relations-within-relations. So who is right? >> In a recent publication by C.J. Date, Database In Depth from O'Reilly I suggest you read that section ( Data value atomicity ) carefully, along >> Press, he writes, verbatim, that relational theory does not prohibit >> multi-valued attributes. with the previous and next sections on types. He meticulously constructs an example with multiple values on a single column, then encapsulates them into a set to illustrate that the notion of atomicity has no absolute meaning. >> He spends at least 3 pages discussing multi-valued attributes, including He discusses ( in chapter 2 ) to show that when the system supports an array >> arrays and even relations-within-relations. type or a relation type, having an array or an RVA in a relation doesn't violate 1NF. -- Anith > I suggest you read that section ( Data value atomicity ) carefully, along I did read that section carefully, and looked at the samples. Read the > with the previous and next sections on types. He meticulously constructs an > example with multiple values on a single column, then encapsulates them into > a set to illustrate that the notion of atomicity has no absolute meaning. > > He discusses ( in chapter 2 ) to show that when the system supports an array > type or a relation type, having an array or an RVA in a relation doesn't > violate 1NF. > > -- > Anith > footnote at the bottom of page 30. A relation that has a column with a multi-valued attribute, such as a set, arrary, or even another relation, is legal from the standpoint of relational theory. Pg. 35: the relational model nowhere formally relies on the scalar versus nonscalar distinction. As long as you define the type for the data that you store in the column, along with all of the operator, selectors, and so on, any data type is legal, even multi-valued ones like arrays, sets, or xml. If I defined a datatype for addresses that had a visual representation of (THE_Address operator) 1519 15th Street, Albany, NY 12192 with the appropriate operators, and selectors, and functions to extract the "scalar" portions of the address, like state and zip code, the original relational theory model does not expressly forbid it. Mark Williams wrote:
Show quoteHide quote > > I suggest you read that section ( Data value atomicity ) carefully, along You have not quoted Date exactly and you have confused the points he> > with the previous and next sections on types. He meticulously constructs an > > example with multiple values on a single column, then encapsulates them into > > a set to illustrate that the notion of atomicity has no absolute meaning. > > > > > He discusses ( in chapter 2 ) to show that when the system supports an array > > type or a relation type, having an array or an RVA in a relation doesn't > > violate 1NF. > > > > -- > > Anith > > > I did read that section carefully, and looked at the samples. Read the > footnote at the bottom of page 30. A relation that has a column with a > multi-valued attribute, such as a set, arrary, or even another relation, is > legal from the standpoint of relational theory. > > Pg. 35: the relational model nowhere formally relies on the scalar versus > nonscalar distinction. > > As long as you define the type for the data that you store in the column, > along with all of the operator, selectors, and so on, any data type is legal, > even multi-valued ones like arrays, sets, or xml. If I defined a datatype for > addresses that had a visual representation of (THE_Address operator) > > 1519 15th Street, Albany, NY 12192 > > with the appropriate operators, and selectors, and functions to extract the > "scalar" portions of the address, like state and zip code, the original > relational theory model does not expressly forbid it. makes. The point of his examples is to show that so called "non-scalar" types (a relation type or an XML type or an array type) are NOT multi-valued and therefore ARE permitted in relations. Multiple values assigned to a single attribute would indeed violate the Information Principle. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- > You have not quoted Date exactly and you have confused the points he And while making that point, the author demonstrates by example the > makes. The point of his examples is to show that so called "non-scalar" > types (a relation type or an XML type or an array type) are NOT > multi-valued and therefore ARE permitted in relations. Multiple values > assigned to a single attribute would indeed violate the Information > Principle. > arbitrary nature of atomicity and types. 2, 3, 5 => multi-valued, not allowed {2,3,5} => single-valued, allowed For a (SQL unsupported) data type of set or array, the visual representation of a vaue (THE_ ) would *appear* to be a multi-valued attribute. So, back to the address. Defined like this: 1519 15th Street, Albany, NY 12192 => multi-valued, not allowed but, defined like <ROOT> <STREET>1519 15th Sreet</STREET> <CITY>Albany</CITY> <STATE>NY</STATE> <ZIP>12192</ZIP> </ROOT> could be interpreted as single-valued, and therefore allowed. The *expression* of that value, through the THE_ operator, would appear to be multi-valued. SELECT address.query('/ROOT/STREET') + ',' + address.query('/ROOT/CITY') + ',' + address.query('ROOT/STATE') + address.query('/ROOT/ZIP') It then comes down to symantics: an array (or set, or relation), while traditionally thought of as a multi-valued, can be interpreted as single-valued. In my original posting, I incorrectly, in this context, referred to xml as being multi-valued. However, in other contexts, most people would consider XML to be multi-valued. Mark Williams wrote:
> It then comes down to symantics: an array (or set, or relation), while It is more than just semantics or interpretation. The only important> traditionally thought of as a multi-valued, can be interpreted as > single-valued. In my original posting, I incorrectly, in this context, > referred to xml as being multi-valued. However, in other contexts, most > people would consider XML to be multi-valued. question from a formal theoretical point of view is whether an XML document or an array or whatever can be represented as a single value of a single type within the database. That is all. That doesn't mean that all such designs will be wise or practical but it does mean there is no need to place arbitrary restrictions on an RDBMS based on the external representation or meaning of data. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- It depends on how accurately you want to define the operation as relational.
In certain contexts, many general SQL operations are deemed relational while in some other formal discourses they are not. SQL's concept of types/domains is a bit strange. All it provides is exposed representations using primitive built-in datatypes that are subject to different user interpretations. As we all know, a type is a defined set of values as associated operators on those values -- this concept on associated operators is important. For instance, consider some commonly understood types like HEIGHT, QUANTITY, TEMPERATURE, DISTANCE etc. In a language like SQL that cannot adequately support such types/domains, the user is forced to use built-in datatypes like say NUMERIC or INTEGER. Thus, while multiplying values of TEMPERATURE to that of HEIGHT is meaningless, a system where these are all represented using INTEGER datatype would allow such multiplications. Due to insufficient type support in such systems, the type specificity and validity of such operations is often maintained and external to the DBMS; and often subject to different interpretations. With a resultset of concatenated values, it boils down to whether each column value in the resultset is treated as a scalar or not. Technically, all values in SQL are scalar; however the user interpretations of these values are not. Thus the value "red,orange,blue,green", while a VARCHAR value --- technically scalar in SQL -- might be interpreted by the user as a list of 4 smaller strings that represent color values arranged in some predetermined order. ( Another perspective is that concatenation of row values as a "relational" operation relies on essential ordering of values which relational model explicitly prohibits. Thus one could question the relevance of the results being outside the realms of relational model. So should the results be "red,orange,blue,green" or "orange,blue,red,green" or "blue,green,red,orange" or something else? ) Coming to the questions in your post, >> Has anyone wondered exactly what 'isn't exactly relational' means? Something that is not exactly specific to theory of relational data management. >> Yet the emperor himself (Mr. Celko) has commented that a 'pure' sql Precisely because SQL cannot support anything beyond its built-in datatypes. >> solution to concatenation 'would never destroy 1NF by building a list >> structure..'.Sounds real good but it is Bizarre! If SQL supported a list type then it wouldn't be an issue at all. >> Anyone on what a non-relational query is? A query formulation that deviates from prescribed operations of relations, generally in a semi-relational language like SQL. Some use the term for query expressions that violate relational principles like data independence as well. Oh yeah, and I am sure all these are all Greek & Latin to you :-) Btw, Date's 1NF paper in the dbdebunk site is the simplest, yet the best one I have seen so far on this topic. -- Anith "Anith Sen" <an***@bizdatasolutions.com> wrote in message Sorry for the dely:(news:OOla6coLGHA.3732@TK2MSFTNGP10.phx.gbl.. >SQL's concept of types/domains is a bit strange. All it provides is exposed >representations using primitive built-in datatypes that are subject to >different user interpretations. >Due to insufficient type support in such systems, the type specificity and >validity of such operations is often maintained and external to the DBMS; >and often subject to different interpretations. >With a resultset of concatenated values, it boils down to whether each >column value in the resultset is treated as a scalar or not. Thanks for sharing your thoughts.(Where is your blog?) I believe we are on the save wavelength here.I choose to present the ideas in,shall we say,a slightly different style:)I regret that I didn't use the word 'intrepretation' explicitly although I would point out that I 'strongly' implied it everywhere including the title:) An 'sql' db invites intrepretation and the inevitable arguments that too often confuse the apples from the pears.Is this the result of sql being the 'silent' type instead of the stong type? But users have to work with what they have so lets keep the fruit coming:) One would think that what is written about a product has importance. Especially what is written by the vendor.It appears that there is stuff coming from MS that is nonsense but becomes sense by merely being repeated often enough.Funny and sad:)But that is only my 'intrepretation':) You can do and get anything you want at Alice's resturant:) Least I be accused of any ulterior motive picking on concatenating,the next bit of fun will be: 'Is it ok to return a line number or a rank or a....' If anyone liked this discussion you will love this one:) >Btw, Date's 1NF paper in the dbdebunk site is the simplest, yet the best I have seen so far on this topic.one Thanks and thanks.  
Other interesting topics
Query help - resultset too large... impossible!
Used Disk Space reporting and sending Email Problem updating two tables in a transaction. Does dynamic SQL allow table variables? Insert Trigger Query assistance or advice Any way to avoid using a cursor and a script on this one? Optimizing query with UDF and table vars and IN Problem with Cursor and Union in select Please help with SQL query |
|||||||||||||||||||||||
