Post reply

distinct of ntext types

  • August 16, 2005 at 7:11 am

    #165715

    hi

    i have following query

    SELECT  COUNT( DISTINCT [SupplierID]),

    COUNT( DISTINCT [CompanyName]),

    COUNT( DISTINCT [ContactName]),

    COUNT( DISTINCT [ContactTitle]),

    COUNT( DISTINCT [Address]),

    COUNT( DISTINCT [City]),

    COUNT( DISTINCT [Region]),

    COUNT( DISTINCT [PostalCode]),

    COUNT( DISTINCT [Country]),

    COUNT( DISTINCT [Phone]),

    COUNT( DISTINCT [Fax])

    , COUNT( DISTINCT [HomePage])

    FROM Suppliers

    Above is from suppliers table in northwind database

    basically we need to find distinct count of every column in tables in database .my question is

    1 . Is there any better way(performance wise) to implement it?

    2. in above query HomePage is of type ntext , so subquery wont work , is there a way to give it a default value so whole query doesnt fails. without knwing the data type of columns

     

    thanks

    Amrita

     

  • August 16, 2005 at 8:12 am

    #582464

    You'll have ignore those types when you generate the query. And no, there's not a better query to get those results.

  • August 16, 2005 at 8:24 am

    #582472

    Definitely costly but is the best way to count the distinct values Good I/O needed for speed

    In regards with the text datatype the only possible way is to splitt the value into smaller datatypes and perform colum aggregates on those then use the distinct of the aggregates. This method is less than perfect but it is better than nothing. One of the things that makes me avoid text data types is their unfriendliness to any kind of manipulation different than pure reads!

      


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply