Sunday, August 8, 2010

Select distinct on text field in a data warehouse

Have you ever tried doing a select distinct on a text field in SQL Server 2005? You can't. Typically, business intelligence tools like OBIEE do a select distinct query on every field that needs to be reported from the data warehouse. So, how can you have a text column in a table that needs to be reported upon by a reporting layer like OBIEE?

Make the field varchar(max). This will allow you to run a select distinct on the field. The text field is deprecated in SQL Server 2005.

Also, this concise, informative article sets all the confusion about when to use varchar(8000), varchar(max) straight:

No comments:

Post a Comment