0

I am trying to write a SQL query that will show me the records that are largest in size along with the User Names associated with the record. 

SELECT 
    Data.Id,
    User.UserName, 
    (
    ISNULL(DATALENGTH(ColumnA), 1) + 
    ISNULL(DATALENGTH(ColumnB), 1) + 
    ISNULL(DATALENGTH(ColumnC), 1)
    ) AS SizeOfRow
FROM Data
    LEFT JOIN User ON Data.UserId = User.UserId 
ORDER BY SizeOfRow DESC

When I run this I get the error Conversion failed when converting from a character string to uniqueidentifier. I am not sure where I am going wrong here. If I remove the JOIN to the User table then the SQL runs without issue. However, I really want to include the UserName field and I am not sure why it's addition is causing a problem.

EDIT: I should mention that ColumnA is a datatype of UniqueIdentifier. While ColumnB and ColumnC are both varchar(max).

webworm
  • 10,587
  • 33
  • 120
  • 217
  • 1
    Most likely it is different datatypes across tables. Just a random guess since you didn't show us the table definition...is UserID a uniqueidentifier in one table and varchar in another? – Sean Lange May 15 '17 at 19:27
  • The issue seems to be in the join, then. What happens if you remove the DATALENGTH summation altogether, but keep the join? – John Pasquet May 15 '17 at 19:27
  • @SeanLange - You are exactly correct. Is there a way to join `varchar` to `UniqueIdentifier`? Perhaps a direct cast? – webworm May 15 '17 at 19:28
  • OK....so do you still have a question? You would have to convert your guid to a varchar. But if it UserID why are you changing datatypes across tables? That should be a foreign key. – Sean Lange May 15 '17 at 19:30
  • I did not even notice the type mismatch before Sean's answer. Should I delete the question? Is there a way to reward Sean for his help? – webworm May 15 '17 at 19:31

0 Answers0