count(uniqueidentifier data type field)

Question

count(uniqueidentifier data type field)

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
March 9, 2010 at 2:51 pm

#216468

when I use an unique identifier data type field inside a count() statement in a T-SQL it throws the below error.
"Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument."
select count(column1) from table1;
column1 -> uniqueidentifier data type field & its the primary key in table1.
Appreciate your clarification in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I don't have an answer why you cannot use a COUNT function on a uniqueidentifier data type.

But considering it's the primary key I'd say COUNT(*) would work as a substitute, since you obviously don't have to struggle with NULL values.

Doesn't answer your question but may solve the issue itself... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 2

lmu92 (3/9/2010)
I don't have an answer why you cannot use a COUNT function on a uniqueidentifier data type.
But considering it's the primary key I'd say COUNT(*) would work as a substitute, since you obviously don't have to struggle with NULL values.
Doesn't answer your question but may solve the issue itself... 😉

Thanks, Lutz

The problem here is the application is trying to configure itself with the table & uses the primary key. Hence, it cannot use count(*) & the application cannot call a stored procedure too. Moreover, count(*) uses the table scan but a primary key is indexed & will never have a NULL value.

I didn't find good answer from Microsoft help & internet why I cannot use count(uniqueidentifier datatype) in the select statement? Its a mystery.....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Like Lutz, I don't have an answer to why you can't use COUNT().

However, I'd suggest that, since you can't (and shouldn't be able to) use MAX() by design because the result would be meaningless, ALL of the aggregate functions have been excluded from working with uniqueidentifier.

Note however that you can CAST the uniqueidentifier to VARCHAR(36) and it will work - MAX of a VARCHAR value is (generally) meaningful.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

RJ-476420 (3/10/2010)
Moreover, count(*) uses the table scan but a primary key is indexed & will never have a NULL value.

No it won't.

Count(*) scans the smallest index on the table to get the row counts. SQL's smart enough to know that Count(column) when that column is not nullable is the same as count(*) and it will use the same execution plan.

http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

Count will use a table scan if there are no indexes at all or if there's no cluster and the column specified is nullable and not indexed

Count will use a clustered index scan if there are no nonclustered indexes at all or if column specified is nullable and not indexed

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 5

Chris Morris-439714 (3/10/2010)
Like Lutz, I don't have an answer to why you can't use COUNT().
However, I'd suggest that, since you can't (and shouldn't be able to) use MAX() by design because the result would be meaningless, ALL of the aggregate functions have been excluded from working with uniqueidentifier.
Note however that you can CAST the uniqueidentifier to VARCHAR(36) and it will work - MAX of a VARCHAR value is (generally) meaningful.

Thanks, Chris

Thats the problem here. The application developer uses java interface & it configures itself to database schema hence it cannot use CAST or CONVERT function. As per the developer's statement the application will try to use the column configured to access the table. Developer's got access to primary key & another column in the same table which is not indexed (a varchar field) & we are trying to use the secondary field here as primary key is uniqueidentifier.

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 6

GilaMonster (3/10/2010)
RJ-476420 (3/10/2010)
Moreover, count(*) uses the table scan but a primary key is indexed & will never have a NULL value.
No it won't.
Count(*) scans the smallest index on the table to get the row counts. SQL's smart enough to know that Count(column) when that column is not nullable is the same as count(*) and it will use the same execution plan.
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/
Count will use a table scan if there are no indexes at all or if there's no cluster and the column specified is nullable and not indexed
Count will use a clustered index scan if there are no nonclustered indexes at all or if column specified is nullable and not indexed

Thanks, Gail

The link you posted is an update to my knowledge of sql