Tym razem znów coś z zawodowego podwórka. I jak zwykle niczego nowego nie odkryję, ale może kogoś zaciekawię...

Odwieczny problem wszystkich bazodanowców: jak w sprawny, elegancki oraz wydajny sposób "spoziomić" dane; innymi słowy, przekształcić dane z postaci "pionowej" (każda wartość w osobnym rekordzie) do postaci "poziomej" (pojedyncza wartość zbudowana z wartości z poszczególnych rekordów rozdzielonych przecinkiem, średnikiem czy czym tam kto chce).

Do dalszych rozważań przyda sie skrypt tworzący tabelę testową i wypełniający ją losowymi danymi:

Tworzymy tabelę:

CREATE TABLE tbl2 ( id INT IDENTITY(1, 1) , nm VARCHAR(100) , snm VARCHAR(100) )

Wypełniamy tabelę losowymi danymi:

DECLARE @counter INT = 0

WHILE @counter <= 1000 BEGIN

INSERT INTO dbo.tbl2 ( nm , snm ) SELECT CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) , CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25)) + CHAR(65 + ABS(CONVERT(BIGINT, CONVERT(VARBINARY, NEWID())) % 25))

SELECT @counter+= 1

END

Na koniec jeszcze zakładamy indeks:

CREATE NONCLUSTERED INDEX idx01tbl2 ON tbl2 ( nm)

Jeszcze raz, żeby było wiadomo co próbujemy osiągnąć:

Chcemy przejść z takiej postaci:

DTOPJ LPQYJ YXNJQ KBWDO AOWEC BHLOP

do takiej:

DTOPJ;LPQYJ;YXNJQ;KBWDO;AOWEC;BHLOP;

Proste? No, w zasadzie tak. Klasyczne podejście wygląda, z grubsza rzecz ujmując, o tak:

DECLARE c CURSOR FAST_FORWARD FOR SELECT nm FROM dbo.tbl2 t

DECLARE @output VARCHAR(MAX), @cnm VARCHAR(100) SELECT @output = '', @cnm = ''

OPEN c

FETCH NEXT FROM c INTO @cnm

WHILE @@FETCH_STATUS=0 BEGIN SELECT @output += (@cnm + ';') FETCH NEXT FROM c INTO @cnm END

CLOSE c DEALLOCATE c SELECT @output AS c1

W zasadzie dość proste, względnie czytelne ("otwórz kursor, przewal wszystkie rekordy, zamnknj kursor") i nawet jako tako działa. Dla tysiąca rekordów wykonuje się w około sekundę, dla stu tysięcy - ciut ponad 3 minuty.

A teraz coś, na widok czego starzy wyjadacze ziewną (o ile jeszcze nie zasnęli do tej pory), za to młodzi adepci SQL westchną z podziwem:

SELECT (SELECT nm + ';' FROM tbl2 FOR XML PATH('')) AS c1

Co to robi? Ano, to samo... Z tą tylko różnicą, że zamiast zużywać prawie 15 linii kodu, robi to samo w jednej.

A wydajność? Dla 1000 rekordów poniżej 15 milisekund, dla 100,000 rekordów 62 milisekundy. Dla pół miliona rekordów 406 milisekund (czyli nadal poniżej pół sekundy).

Jakieś wady?

Tak, jedna: specyfikacja FOR XML PATH jest słabo udokumentowana i może się w każdej chwili zmienić. Póki co jednak - działa pięknie (zarówno w 2005 jak i w 2008) - a ponieważ powyższy trick jest bardzo popularny wśród braci SQL-owej, nie sądzę, żeby Microsoft strzelił sobie samobója i wprowadził tu jakieś znaczące zmiany.

Na koniec żart. Z brodą, moje żarty się nie golą:

• Po której stronie gęś ma najwięcej piór?
• ?!?
• Po zewnętrznej...