October 4, 2012 at 10:27 am
Hi,
Is it safe to say that XML data type should be used to store data, instead of creating more columns on the table, data that won't be searched. If any field is used in a search criteria it should be placed directly as a column on the table?
For example a customers table. It has Id, Code, Name, .... The UI only allows search on Code and Name.. can we create a XML field to store the other data (Address, Phone, ....)?
I know that a "strong typed" xml with a namespace can have indexes but it's not as fast as having a column on the table, at least it wasn't on 2005...
Thanks,
Pedro
October 4, 2012 at 11:35 am
PiMané (10/4/2012)
Hi,Is it safe to say that XML data type should be used to store data, instead of creating more columns on the table, data that won't be searched. If any field is used in a search criteria it should be placed directly as a column on the table?
For example a customers table. It has Id, Code, Name, .... The UI only allows search on Code and Name.. can we create a XML field to store the other data (Address, Phone, ....)?
I know that a "strong typed" xml with a namespace can have indexes but it's not as fast as having a column on the table, at least it wasn't on 2005...
Thanks,
Pedro
I would not recommend that. It takes more space in the database. It also is more painful to run quick queries like "I need a list of customers from Alabama" or "I need to know how many customers in area code xxx made a purchase in June".
Then lets consider what happens to any front end. Today you can only search by those columns but next week somebody wants to search by a column that you have stuck in xml.
What is it that you are trying to gain with this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2012 at 2:14 am
The example I gave of storing the address is just that, an example. Like I said I would only store in XML data that I know I wouldn't need to search.
Or just store the hole object serialized from the .net app and when I made the "get" it would deserialize into to .net object to use in the UI.
Any fields needed for FKs, PKs, search would be stored in normal columns...
But like you said, XML takes a lot of space...
Probably we will only use it for passing parameters master/detail to the database.
Pedro
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply