Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT require the length to be the same?

Let's say that I have the following sample data that I need to unpivot:

CREATE TABLE People
(
    PersonId int, 
    Firstname varchar(50), 
    Lastname varchar(25)
)

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');

If I attempt to UNPIVOT the Firstname and Lastname columns similar to:

select PersonId, ColumnName, Value  
from People
unpivot
(
  Value 
  FOR ColumnName in (FirstName, LastName)
) unpiv;

SQL Server generates the error:

Msg 8167, Level 16, State 1, Line 6

The type of column "Lastname" conflicts with the type of other columns specified in the UNPIVOT list.

In order to resolve the error, we must use a subquery to first cast the Lastname column to have the same length as Firstname:

select PersonId, ColumnName, Value  
from
(
  select personid, 
    firstname, 
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR 
  ColumnName in (FirstName, LastName)
) unpiv;

See SQL Fiddle with Demo

Prior to UNPIVOT being introduced in SQL Server 2005, I would use a SELECT with UNION ALL to unpivot the firstname/lastname columns and the query would run without the need to convert the columns to the same length:

select personid, 'firstname' ColumnName, firstname value
from People
union all
select personid, 'LastName', LastName
from People;

See SQL Fiddle with Demo.

We are also able to successfully unpivot the data using CROSS APPLY without having the same length on the datatype:

select PersonId, columnname, value
from People
cross apply
(
    select 'firstname', firstname union all
    select 'lastname', lastname
) c (columnname, value);

See SQL Fiddle with Demo.

I have read through MSDN but I didn't find anything explaining the reasoning for forcing the length on the datatype to be the same.

What is the logic behind requiring the same length when using UNPIVOT?

share|improve this question
3  
My vote is for lazy type checking on the developer's part. Not the same but compatible? That's too much work. Let's just raise an error message instead. There probably is an efficiency to the implementation by forcing it to be identical types since you can presize your memory but that's complete and total hand waving and guesses –  billinkc Dec 3 '13 at 14:25
    
@billinkc Well I'm hoping there is some sort of explanation about why this happens, instead of it just does. –  bluefeet Dec 3 '13 at 16:36
3  
(Possibly unrelated but...) Same strictness is applied when comparing column types of the two parts of a recursive CTE. –  Andriy M Dec 3 '13 at 16:36

2 Answers 2

up vote 7 down vote accepted

What is the logic behind requiring the same length when using UNPIVOT?


This question may only be truly answerable by the people who worked on the implementation of UNPIVOT. You might be able to obtain this by filing a Connect item. The following is my understanding of the reasoning, which may not be 100% accurate:


T-SQL contains any number of instances of weird semantics and other counter-intuitive behaviours. Some of these will eventually go away as part of deprecation cycles, but others may never be 'improved' or 'fixed'. Quite aside from anything else, applications exist that depend on these behaviours, so backward compatibility has to be preserved.

The rules for implicit conversions, and expression type derivation account for a significant proportion of the weirdness mentioned above. I do not envy the testers who have to ensure that the weird (and often undocumented) behaviours are preserved (under all combinations of SET session values and so on) for new versions.

That said, there is no good reason not to make improvements, and avoid past mistakes, when introducing new language features (with obviously no backward compatibility baggage). New features like recursive common table expressions (as mentioned by Andriy M) and UNPIVOT were free to have relatively sane semantics and clearly-defined rules.

There will be a range of views as to whether including the length in the type is taking explicit typing too far, but personally I welcome it. In my view, the types varchar(25) and varchar(50) are not the same, any more than decimal(8) and decimal(10) are. Special casing string type conversion complicates things unnecessarily and adds no real value, in my opinion.

One could argue that only implicit conversions that might lose data should be required to be explicitly stated, but there are edge-cases there too. Ultimately, a conversion is going to be needed, so we might as well make it explicit.

If the implicit conversion from varchar(25) to varchar(50) were allowed, it would just be another (most likely hidden) implicit conversion, with all the usual weird edge cases and SET setting sensitivities. Why not make the implementation the simplest and most explicit possible? (Nothing is perfect, however, and it is a shame that hiding varchar(25) and varchar(50) inside a sql_variant is allowed.)

Rewriting the UNPIVOT with APPLY and UNION ALL avoids the (better) type behaviour because the rules for UNION are subject to backward compatibility, and are documented in Books Online as allowing different types so long as they are comparable using implicit conversion (for which the arcane rules of data type precedence are used, and so on).

The workaround involves being explicit about the data types and adding explicit conversions where necessary. This looks like progress to me :)

One way to write the explicitly-typed workaround:

SELECT
    U.PersonId,
    U.ColumnName,
    U.Value
FROM dbo.People AS P
CROSS APPLY
(
    VALUES (CONVERT(varchar(50), Lastname))
) AS CA (Lastname)
UNPIVOT
(
    Value FOR
    ColumnName IN (P.Firstname, CA.Lastname)
) AS U;

Recursive CTE example:

-- Fails
WITH R AS
(
    SELECT Dummy = 'A row'
    UNION ALL
    SELECT 'Another row'
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

-- Succeeds
WITH R AS
(
    SELECT Dummy = CONVERT(varchar(11), 'A row')
    UNION ALL
    SELECT CONVERT(varchar(11), 'Another row')
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

Finally, note that the rewrite using CROSS APPLY in the question is not quite the same as the UNPIVOT, because it does not reject NULL attributes.

share|improve this answer
    
Thank you so much for the explanation of your understanding of the reasoning. I came across on a question over on Stack Overflow the other day and was curious why we were forced to do the conversion, this helps explain it. As far as your last comment, I know that CROSS APPLY isn't quite the same due to the handling of null items but that can be managed using a where - I excluded it solely for the demo purposes. I also saw the typo and corrected it. :) –  bluefeet Dec 5 '13 at 11:56
    
@bluefeet I know you know :) The observation was for the benefit of future readers, who might not be so expert with UNPIVOT and APPLY. I'll remove the typo bit. –  Paul White Dec 5 '13 at 12:09

The UNPIVOT operator utilizes the IN operator. The specifications for the IN operator (screenshot below) indicate that both the test_expression (in this case, on the left of the IN) and each expression (on the right side of the IN) must be the same data type. Thanks to the transitive property of equality, each expression must be of the same data type as well.

enter image description here

share|improve this answer
    
Right, I understand the datatype requirement but the question is why does the length have to be the same. –  bluefeet Dec 3 '13 at 22:29
    
I overlooked that, and yeah, the IN operator typically doesn't care about the length. –  dev_etter Dec 4 '13 at 15:26
    
My guess (sorry, can't find anything to back this up)...the results are stored into a new column. SQL creates the metadata for that new column based on the largest type/length combination. If the requirement is that the source column metadata must match the destination column metadata, then length would be included with type. –  dev_etter Dec 4 '13 at 15:57
    
An alternative that allows you to overlook the need for specifying the length is to cast each as SQL_Variant: sqlfiddle.com/#!3/13b9a/2/0 –  dev_etter Dec 4 '13 at 15:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.