Dlaczego SQL Server wymaga, aby długość typu danych była taka sama podczas korzystania z UNPIVOT?

Podczas stosowania UNPIVOTfunkcji do danych, które nie są znormalizowane, SQL Server wymaga, aby typ danych i długość były takie same. Rozumiem, dlaczego typ danych musi być taki sam, ale dlaczego UNPIVOT wymaga, aby długość była taka sama?

Powiedzmy, że mam następujące przykładowe dane, które muszę rozdzielić:

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');

Jeśli spróbuję UNPIVOT Firstnamei Lastnamekolumny podobne do:

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

SQL Server generuje błąd:

Msg 8167, poziom 16, stan 1, wiersz 6

Typ kolumny „Lastname” powoduje konflikt z typem innych kolumn określonych na liście UNPIVOT.

Aby rozwiązać błąd, musimy użyć podzapytania, aby najpierw rzutować Lastnamekolumnę, aby miała taką samą długość jak 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;

Zobacz SQL Fiddle with Demo

Przed wprowadzeniem UNPIVOT w SQL Server 2005, używałbym znaku SELECTz, UNION ALLaby cofnąć przestawienie kolumn firstname/, lastnamea zapytanie uruchamiałoby się bez potrzeby konwertowania kolumn na tę samą długość:

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

Zobacz SQL Fiddle with Demo .

Jesteśmy również w stanie z powodzeniem cofnąć przestawienie danych przy użyciu CROSS APPLYtej samej długości w typie danych:

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

Zobacz SQL Fiddle with Demo .

Przeczytałem MSDN, ale nie znalazłem niczego, co tłumaczyłoby powód, dla którego zmuszono by długość typu danych była taka sama.

Jaka jest logika wymagająca tej samej długości podczas korzystania z UNPIVOT?

Jaka jest logika wymagająca tej samej długości podczas korzystania z UNPIVOT?

Na to pytanie mogą odpowiedzieć prawdziwie tylko ludzie, którzy pracowali nad wdrożeniem UNPIVOT. Możesz to uzyskać, kontaktując się z nimi w celu uzyskania wsparcia . Oto moje rozumowanie rozumowania, które może nie być w 100% dokładne:

T-SQL zawiera dowolną liczbę wystąpień dziwnej semantyki i innych zachowań sprzecznych z intuicją. Niektóre z nich ostatecznie znikną w ramach cykli amortyzacji, ale inne mogą nigdy nie zostać „ulepszone” lub „naprawione”. Niezależnie od wszystkiego, istnieją aplikacje, które zależą od tych zachowań, dlatego należy zachować kompatybilność wsteczną.

Reguły dotyczące niejawnych konwersji i wyprowadzania typu wyrażenia stanowią znaczną część wspomnianej powyżej dziwności. Nie zazdroszczę testerom, którzy muszą zapewnić zachowanie dziwnych (i często nieudokumentowanych) zachowań (pod wszystkimi kombinacjami SETwartości sesji itd.) Dla nowych wersji.

To powiedziawszy, nie ma żadnego powodu, aby nie wprowadzać ulepszeń i unikać błędów w przeszłości, wprowadzając nowe funkcje językowe (oczywiście bez bagażu kompatybilności wstecznej). Nowe funkcje, takie jak rekurencyjne wspólne wyrażenia tabelowe (jak wspomniał Andriy M w komentarzu) i UNPIVOTmiały swobodę względnie rozsądnej semantyki i jasno określonych reguł.

Pojawi się szereg poglądów na temat tego, czy uwzględnienie długości w typie idzie za daleko w jednoznacznym pisaniu, ale osobiście jestem z tego zadowolony. Moim zdaniem typy varchar(25)i nievarchar(50) są takie same, podobnie jak i są. Specjalna konwersja typu łańcucha w obudowie niepotrzebnie komplikuje rzeczy i, moim zdaniem, nie dodaje żadnej wartości.decimal(8)decimal(10)

Można argumentować, że tylko jawne konwersje, które mogą utracić dane powinny wymagać wyraźnego stwierdzenia, ale są też przypadki skrajne. Ostatecznie konieczna będzie konwersja, więc równie dobrze możemy to wyrazić.

Gdyby niejawna konwersja z varchar(25)na varchar(50)była dozwolona, ​​byłaby to po prostu kolejna (najprawdopodobniej ukryta) niejawna konwersja ze wszystkimi zwykłymi dziwnymi przypadkami na krawędziach i SETustawieniem wrażliwości. Dlaczego nie sprawić, by wdrożenie było najprostsze i jak najbardziej jednoznaczne? (Nic nie jest idealne, jednak i to wstyd, że ukrywanie varchar(25)i varchar(50)Wewnątrz sql_variantjest dozwolone).

Przepisanie UNPIVOTz APPLYi UNION ALLuniknięcie (lepszego) zachowania typu, ponieważ reguły UNIONpodlegają kompatybilności wstecznej i są udokumentowane w Books Online jako zezwalające na różne typy, o ile są one porównywalne przy użyciu niejawnej konwersji (dla której tajemne reguły pierwszeństwa typów danych są używane itd.).

Obejście tego problemu polega na jawnym określeniu typów danych i dodaniu jawnych konwersji w razie potrzeby. Dla mnie wygląda to na postęp :)

Jednym ze sposobów na napisanie jednoznacznie opisanego obejścia:

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;

Przykład rekurencyjnego CTE:

-- 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;

Na koniec zauważ, że przepisywanie przy użyciu CROSS APPLYw pytaniu nie jest dokładnie takie samo jak UNPIVOT, ponieważ nie odrzuca NULLatrybutów.

UNPIVOTOperator wykorzystuje INoperatora. Te specyfikacje dla operatora IN (screenshot poniżej) wskazują, że zarówno test_expression(w tym przypadku, po lewej stronie IN), a każdy expression(po prawej stronie IN), muszą być tego samego typu danych. Dzięki przechodniejszej właściwości równości każde wyrażenie musi mieć również ten sam typ danych.