Greetings. Using SQL2K8, SP1.

I've got a group of developers that want to use an XML data type column to store multiple zip codes, then parse out the column to query specfic zip codes if needed. This would be as opposed to normalizing the DB properly.

Now I'll be the first to say that at rare times, a proper design should be ditched for what will be faster, but my guess (could be wrong) is that this will slow things down. I'm certain it would slow things down with older data types, but I've honestly never encountered this request with the XML data type.

Has anyone compared these various types of scenarios, or have any experience that would suggest this idea is either good or bad? I'm open to this way of doing things, provided it won't make my life miserable in the future due to poor performance.

Thanks!

You can't index the XML, so parsing it isn't even relevant. Think how long it will take to tablescan instead of index seek when you need to know how many customers are in a given zip code.

The developers may force you to use XML, but I wouldn't go there voluntarily.

-PatP

Thanks Pat.

I will likely attempt to avoid this. However, this arguement is not valid as the XML data type in SQL2K8 does in fact allow indexing. I would still think the parse itself would slow things down, if not disregard the index entirely, but on "the surface" indexes are allowed.

The XML datatype does in fact allow the creation of indexes, that wasn't my point... When you search a table using a simple column to find out who lives in zip code 90201 you can use an index to find the appropriate row. When you do the same search for an XML column, you must retrieve and parse every row. On a million row table, a simple column takes about 700 mlliseconds. The same serach against XML encoded, multi-valued data would be measured in minutes.

-PatP

This was my thinking as well, thanks for confirming!