piątek, 31 grudnia 2010

%%rowdump%%

Następną funkcją dostarczającą nam pseudokolumny jest %%rowdump%%

SELECT %%rowdump%%,* FROM test
-------------



Znowu z pomocą przychodzi nam funkcja sys.fn_PhysLocFormatter

SELECT sys.fn_PhysLocFormatter(%%rowdump%%), * FROM test
-------------
                   id          tekst     dtekst               data
------------------ ----------- ----------------------------------------------------
(0:17104896:256)   1           abc       ABCqazwsx0987qa<..>  2010-12-30 13:41:44.673
(0:17104896:256)   2           abc       ABCqazwsx0987qa<..>  2010-12-30 13:44:31.423

Ciekawszych informacji dostarcza nam funkcja tabelaryczna sys.fn_RowDumpCracker

SELECT *
FROM test
CROSS APPLY sys.fn_RowDumpCracker(%%rowdump%%)s
WHERE id =1



Zwraca wyniki dla każdego wiersza typu varchar i nvarchar, jeżeli wartość pola mieści się na jednej stronie to IsInrow jest równe 1 a wartość InRowLength pokazuje ilość znaków, jeżeli serwer stworzy marker do innej strony IsInrow ma wartość 0 a długość pola wynosi zawsze 24.
Jeżeli użyjemy w tabeli pola typu sparse znacznik IsSparse zmieni się na 1 i funkcja zwróci również pola innego typu

czwartek, 30 grudnia 2010

%%lockres%%

Następną ciekawą funkcją dostępną w SQL Server 2005 i 2008 jest %%lockres%%
Pokazuje nam ona w pseudokolumnie klucz każdego rekordu (KeyHashValue)

SELECT %%lockres%%,* FROM test
-------------
                   id          tekst     dtekst               data
------------------ ----------- ----------------------------------------------------
(8194443284a0)     1           abc       ABCqazwsx0987qa<..>  2010-12-30 13:41:44.673
(61a06abd401c)     2           abc       ABCqazwsx0987qa<..>  2010-12-30 13:44:31.423

To ten magiczny znacznik widzimy w widoku sys.dm_tran_locks w polu resource_description dla typu key, to nim serwer posługuje się przy obsłudze lokowań

W innej sesji odpalamy

BEGIN TRAN
UPDATE test
SET tekst ='abcd'
WHERE id =1

I sprawdzamy
select * from sys.dm_tran_locks                     



Dzięki takiemu mechanizmowi możemy sprawdzać jakie wiersze są w zablokowane

SELECT a.* FROM test  AS a with (NOLOCK)
JOIN sys.dm_tran_locks AS l
ON a.%%lockres%% = l.resource_description
WHERE l.resource_type = 'KEY'     

-------------
id          tekst     dtekst               data
------------ ----------------------------------------------------
1           abc       ABCqazwsx0987qa<..>  2010-12-30 13:41:44.673


Wartość ta jest przechowywana na poziomie strony

DBCC PAGE (5, 1,20636,3) WITH TABLERESULTS, NO_INFOMSGS



Klucz jest generowany losowo i w tabeli o bardzo dużej ilości danych może się zdarzyc, że klucze się powtórzą, dochodzi wówczas do zablokowania rekordów zupełnie ze sobą niezwiązanych

select %%lockres%% FROM CalkiemSporaTabela --100 mln rekordow
group by %%lockres%%
having count(%%lockres%%)>1

------------
(06006b69f9b0)
.....
.....

(1485 row(s) affected)

%%physloc%%

W SQL Server 2008 pojawiła się bardzo ciekawa funkcja %%physloc%% dzięki niej możemy w pseudokolumnie zobaczyć adres fizyczny rekordu.

Robimy sobie poligon

CREATE  TABLE test ( id INT IDENTITY (1,1) PRIMARY KEY,
                             tekst VARCHAR(100),
                             dtekst VARCHAR(MAX),
                             data DATETIME)
go

INSERT test(tekst,dtekst,data)
VALUES
('abc','ABC',GETDATE())
go

SELECT %%physloc%%, * FROM test

--------------

                   id          tekst     dtekst               data
------------------ ----------- ----------------------------------------------------
0x9C50000001000000 1           abc       ABC                  2010-12-30 13:41:44.673


Dostajemy zahaszowany adres strony, można go rozwiązać przy pomocy funkcji skalarnej sys.fn_PhysLocFormatter lub funkcji tabelarycznej sys.fn_PhysLocCracker

SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM test

--------------

                   id          tekst     dtekst               data
------------------ ----------- ----------------------------------------------------
(1:20636:0)        1           abc       ABC                  2010-12-30 13:41:44.673

Sprawdzamy

select  OBJECT_ID('test'), DB_ID()

           
----------- ------
2139154666  5
DBCC IND (5,2139154666,0)WITH TABLERESULTS, NO_INFOMSGS



Zgadza się,  co będzie jak zwiększymy rozmiar pola

UPDATE test
SET dtekst =dtekst +REPLICATE('qazwsx0987',20000)

Spodziewamy się, że pole dtekst będzie miało 200003 znaki ?

SELECT LEN(dtekst) FROM test

---------------
8003
Taka mała niespodzianka z polem max, ale nie o to nam chodzi

SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM test

--------------

                   id          tekst     dtekst               data
------------------ ----------- ----------------------------------------------------
(1:20636:0)        1           abc       ABCqazwsx0987qa<..>  2010-12-30 13:41:44.673

Czyli nadal jedna strona ?

DBCC IND (5,2139154666,1)WITH TABLERESULTS, NO_INFOMSGS



Już są dwie strony danych, została dodana strona typu LOB, funkcja wskazuje tylko stronę początkową. Co będzie jak dodamy następny rekord?

INSERT test (tekst,dtekst,data)
SELECT tekst,dtekst,data FROM test
go
SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM test

--------------

                   id          tekst     dtekst               data
------------------ ----------- ----------------------------------------------------
(1:20636:0)        1           abc       ABCqazwsx0987qa<..>  2010-12-30 13:41:44.673
(1:20636:1)        2           abc       ABCqazwsx0987qa<..>  2010-12-30 13:44:31.423

Wszystko nadal jest na jednej stronie, tak to wygląda ponieważ na pierwszej stronie znajdują się wskaźniki do strolny LOB. Faktycznie dane znajdują się już na trzech stronach


wtorek, 30 listopada 2010

translate

Jeżeli robimy dużo zmian w tekście używając funkcji REPLACE dochodzimy do momentu w którym edycja jest możliwa wyłącznie przy odpowiednim formatowaniu kodu.
Przykładem jest funkcja konwertująca stronę kodową Mazovia (CP620) a stroną Windows-1250 napisaną przez Pawła Potasińskiego.

CREATE FUNCTION dbo.ufn_MazoviaTo1250 (@String varchar(maX))
RETURNS varchar(max)
WITH SCHEMABINDING
AS
BEGIN
  SET @String = @String COLLATE Polish_BIN;
  RETURN
    REPLACE (
      REPLACE (
        REPLACE (
          REPLACE (
            REPLACE (
              REPLACE (
                REPLACE (
                  REPLACE (
                    REPLACE (
                      REPLACE (
                        REPLACE (
                          REPLACE (
                            REPLACE (
                              REPLACE (
                                REPLACE (
                                  REPLACE (
                                    REPLACE (
                                      REPLACE (
                                        @String, CHAR(165), CHAR(209) -- Ñ
                                      ), CHAR(143), CHAR(165)         -- ¥
                                    ), CHAR(163), CHAR(211)           -- Ó
                                  ), CHAR(156), CHAR(163)             -- £
                                ), CHAR(149), CHAR(198)               -- Æ
                              ), CHAR(144), CHAR(202)                 -- Ê
                            ), CHAR(152), CHAR(140)                   -- Œ
                          ), CHAR(160), CHAR(143)                     -- 
                        ), CHAR(161), CHAR(175)                       -- ¯
                      ), CHAR(134), CHAR(185)                         -- ¹
                    ), CHAR(141), CHAR(230)                           -- æ
                  ), CHAR(145), CHAR(234)                             -- ê
                ), CHAR(146), CHAR(179)                               -- ³
              ), CHAR(164), CHAR(241)                                 -- ñ
            ), CHAR(162), CHAR(243)                                   -- ó
          ), CHAR(158), CHAR(156)                                     -- œ
        ), CHAR(166), CHAR(159)                                       -- Ÿ
      ), CHAR(167), CHAR(191)                                         -- ¿
    ) COLLATE database_default;
END;

Wygląda nieźle ale jakby trzeba było zamienić więcej niż 100 znaków?

Można zrobić coś takiego, żeby było łatwiej tworzymy tabelę mapowania

CREATE TABLE mapa (id INT IDENTITY(1,1), map VARCHAR(256), rep NVARCHAR(256))
go
Dodajemy do niej wartości kodu ascii

INSERT mapa (map,rep)
VALUES (165,209)
 , (143 , 165)
 , (163 , 211)
 , (156 , 163)
 , (149 , 198)
 , (144 , 202)
 , (152 , 140)
 , (160 , 143)
 , (161 , 175)
 , (134 , 185)
 , (141 , 230)
 , (145 , 234)
 , (146 , 179)
 , (164 , 241)
 , (162 , 243)
 , (158 , 156)
 , (166 , 159)
 , (167 , 191)
 go

I robimy funkcję

create function dbo.translate(@txt nvarchar(max))
returns nvarchar(max)
as
begin
SET @txt =@txt COLLATE Polish_BIN
select @txt = replace(@txt, CHAR(map), CHAR(rep))
from mapa
  return @txt;
end
go

metody tej możemy użyc również do zamieniania dowolnych ciągów znakowych w tekście, należy zmodyfikować funkcję

alter function dbo.translate(@txt nvarchar(max))
returns nvarchar(max)
as
begin
select @txt = replace(@txt, map, rep)
from mapa
  return @txt
end
go

zmieniamy dane do mapowania

DELETE FROM mapa
go
INSERT mapa (map,rep)
VALUES (1,'jeden*')
 , (2 , 'dwa*')
 , (3 , 'trzy*')          
 , (4 , 'cztery*')          
 , (5, 'pięć*')          
 , (6 , 'sześć*')                
 , (7 , 'siedem*')                
 , (8 , 'osiem*')                
 , (9 , 'dziewięć*')                      
 , (0 , 'zero*')                       
 go

testujemy

SELECT dbo.translate ('167870349076');