Marek Adamczuk

Limit 8060 bajtów na wiersz odszedł inaczej

2008-08-30T19:44:00Z

Jedną z istotnych nowości w SQL Server 2005 było zniesienie limitu 8060 bajtów zapisanych w jednym wierszu. Oto krótki test.

create table dbo.temp_duzaTabela ( a varchar(8000), b varchar(8000), c varchar(8000) ) GO insert dbo.temp_duzaTabela (a,b,c) select replicate('x',8000),replicate('x',8000),replicate('x',8000)

Na SQL Server 2000 pierwszy batch generował ostrzeżenie, a drugi błąd. Na SQL Server 2005 oba przechodzą bez problemu. Czy zatem limit 8060 bajtów odszedł? Niestety nie do końca. Spróbujmy tego samego z kolumnami typu char.

create table dbo.temp_duzaTabela ( a char(8000), b char(8000), c char(8000) )

Próba wykonania tego batcha ku mojemu zdziwieniu skończyła się komunikatem:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'temp_duzaTabela' failed because the minimum row size would be 24007, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Chwila uparczywego wpatrywania się w komunikat obnaża problem. Kluczowe jest tu słówko minimum. Zatem dla minimalnej wielkości wiersza limit wciąż obowiązuje. Problem rozwiązuje w jakiejś mierze SQL Server 2008 w tej kwestii bynajmniej nie wprowadza żadnej nowej jakości.

Dziękuję Pawłowi Gailardowi za uświadomienie mi tego problemu.

[01-09-2008] Edycja: Okazuje się, że jednak SQL Server 2008 w jakiś sposób życie nam tu ułatwia. Nie automatycznie, ale jednak. Otóż atrybut SPARSE powoduje, że damy radę założyć tabelę z typami o stałej długości przekraczającymi sumarycznie 8060 bajtów.

create table dbo.temp_duzaTabela ( a char(8000) SPARSE, b char(8000) SPARSE, c char(8000) SPARSE )

Konkurs - przywrócenie pierwotnej kolejności kolumn tabeli

2008-07-23T19:24:00Z

Zgodnie z obietnicą ogłaszam konkurs zapowiedziany na wczorajszej sesji PLSSUG. Zadanie polega na przywróceniu pierwotnej kolejności kolumn tabeli po usunięciu i ponownym odtworzeniu kolumny wyliczanej. Np. mamy tabelę:

create table dbo.Osoby ( id int, Imie varchar(50) NOT NULL, Nazwisko varchar(100) NOT NULL, NazwaPelna as Imie+' '+Nazwisko, TelefonDoPracy varchar(30) NULL, TelefonDomowy varchar(30) NULL, TelefonKom varchar(30) NULL, Telefony as ISNULL(TelefonDoPracy+';','')+ISNULL(TelefonDomowy+';','')+ISNULL(TelefonKom+';',''), EMail varchar(100), DataRejestracji datetime )

Po usunięciu i ponownym odtworzeniu kolumn NazwaPelna i Telefony, znajdą się one na końcu listy kolumn tabeli. Słabo napisane aplikacje mogą tej zmiany nie wytrzymać. Dlatego będziemy chcieli przywrócić kolejność pierwotną.

W czasie sesji pokazałem, jak wygenerować skrypty, które dla dowolnej tabeli:

1. Zapisze definicję wszystkich kolumn wyliczanych wraz z pierwotną kolejnością do tabeli
2. Usunie wszystkie kolumny wyliczane
3. Odtworzy kolumny wyliczane (lądują na końcu listy)

Definicja tabeli jest w skrypcie recollate.sql umieszczonych w zasobach PLSSUG. Dla wygody zacytuję ją ponownie tutaj.

create table dbo.temp_ComputedCols ( TableName sysname, ColumnName sysname, definition nvarchar(max), position int, PRIMARY KEY (TableName, ColumnName) )

Zadanie konkursowe polega na tym, żeby na podstawie definicji tabeli po operacji usunięcia i odtworzenia kolumn wyliczanych + zawartości tabeli temp_ComputedCols wygenerować skrypt, który doprowadzi tabelę do pierwotnej postaci - wszystkie kolumny są w tej samej kolejności co przed operacją. Linie skryptu mają być wygenerowane w języku TSQL w postaci procedury lub funkcji tabelarycznej. Jako jedyny parametr procedury/funkcji powinna być przekazana nazwa tabeli, dla której generujemy skrypt.

Warunki oceny rozwiązania:

1. Rozwiązanie nie może spowodować jakiejkolwiek utraty danych w tabeli
2. Rozwiązanie powinno wykonać minimalną liczbę manipulacji na danych i strukturze tabeli
3. Rozwiązanie powinno przewidywać, że jest więcej niż jedna kolumna wyliczana, tak jak w przykładzie
4. Rozwiązanie powinno być możliwie jak najszybsze

Dla uproszczenia zakładamy, że w chwili rozpoczęcia naprawy na tabeli, którą mamy naprawić nie ma żadnych więzów ani indeksów.

Czekam na rozwiązania konkursowe do końca 15-08-2008 - proszę o przysyłanie ich na mój mail z profilu na wss.pl. Najciekawsze rozwiązania opublikuję, a najlepsze nagrodzę na najbliższym spotkaniu PLSSUG. Do 10.08.2008 będę poza Polską i nie wiem, czy będę miał dostęp do maila, zatem proszę nie zrażać się brakiem mojej reakcji w tym czasie.

Pozdrawiam
Marek Adamczuk

Hash czy małpa a sprawa collation

2008-07-23T09:56:00Z

Wczoraj miałem przyjemność poprowadzić na PLSSUG Warszawa prezentację dotyczącą collation na SQL Serverze. W czasie prezentacji wygłosiłem pewną, nie do końca uprawnioną tezę. Mianowicie stwierdziłem, że collation z którym zakładane są domyślnie kolumny tabeli tymczasowej ustawia się na collation bazy bieżącej w SQL Server 2005 i z collation bazy tempdb w SQL Server 2000. W rzeczywistości jest to prawdą tylko dla zmiennych tablicowych. Oto przykład:

-- ważne, żeby to nie było collation servera! create database bad_db collate SQL_Polish_CP1250_CI_AS GO use bad_db GO declare @t table (a nvarchar(128)) select * from @t t join sysobjects o on t.a = o.name GO create table #t (a nvarchar(128)) select * from #t t join sysobjects o on t.a = o.name GO

Przykład uruchamiamy na SQL Server 2000 i SQL Server 2005. Jak się okazuje, query ze zmienną tablicową wykonuje się prawidłowo tylko na 2005. Wszystkie inne zwracają błąd konfliktu collation. Zatem #tabele tymczasowe w kontekście collation nadal są złe na SQL Server 2005. Co więcej, test przeprowadzony na ostatnim dostępnym mi buildzie SQL Server 2008 daje identyczny rezultat, co 2005.

Czy zatem, będąc świadomymi zła, jesteśmy w stanie mu zaradzić? Owszem jesteśmy, ale wymaga to zmiany przyzwyczajeń w pisaniu kodu. Otóż, aby być collation insensitive, musimy przy tworzeniu tabel tymczasowych, a na SQL 2000 również zmiennych tablicowych, dopisywać przy stringowych kolumnach klauzulę COLLATE database_default. Na naszym przykładzie wygląda to tak:

-- tak już nie musimy robić na SQL Server 2005 i wyższych declare @t table (a nvarchar(128) collate database_default) select * from @t t join sysobjects o on t.a = o.name GO -- tak niestety musimy nadal create table #t (a nvarchar(128) collate database_default) select * from #t t join sysobjects o on t.a = o.name drop table #t GO

Wnioski

Jeśli nie chcemy mieć potencjalnego konfliktu collation:
1. Dopisujemy collate database_default przy definicjach kolumn stringowych #tabel tymczasowych zawsze i zmiennych @tablicowych na SQL 2000.
2. Jeśli mamy SQL 2005 i nie chcemy wyrabiać sobie odruchu z punktu 1 - bardziej lubimy @tabele niż #tabele. Abstrahując od innych warunków ich zastosowania, rzecz jasna.

Obsługa wyjątków na SQL - (nie) daj się złapać

2008-07-07T20:59:00Z

Będzie trochę o obsłudze wyjątków na SQL Server. O tym, kiedy może okazać się przydatna i jakie czyhają pułapki. A będzie na przykładzie. Otóż mamy sytuację, w której musimy na chwilę zdropić sporą ilość obiektów proceduralnych na bazie danych, aby je po wykonaniu pewnej operacji odtworzyć. Po co? O tym za następnym razem. Dziś skupię się na zawiłościach dropienia i odtwarzania obiektów. Dla uproszczenia wywodu przyjmijmy, że wszystkie interesujące nas obiekty należą do schematu dbo i są procedurami, widokami, funkcjami TSQL wszelkiej maści lub triggerami. Przyjmijmy też, że nazwy obiektów podlegających naszej operacji mamy w następującej tabeli:

create table dbo.ObjectsToRecreate ( ObjectName sysname NOT NULL, ObjectDefinition sysname NULL, ObjectId int NULL, CONSTRAINT PK_ObjectsToRecreate PRIMARY KEY (ObjectName))

Aby było co odtwarzać po zdropieniu - zapiszmy sobie definicje obiektów do naszej tabeli. Z SQL Server 2005 sprawa jest prosta - używamy nowej, fajnej funkcji object_definition.

update ObjectsToRecreate SET ObjectDefinition = OBJECT_DEFINITION(OBJECT_ID(ObjectName))

Kiedy już mamy kopię treści ciał obiektów możemy zabrać się do dropienia. O ile obiekty nie są użyte w innych z klauzulą SCHEMABINDING, tudzież w niskopoziomowych obiektach takich jak defaulty, checki czy kolumny wyliczalne - operacja powinna pójść bezproblemowo. Jak? Sprawę załatwi kawałek dynamicznego SQL-a.

declare @sql nvarchar(max) select @sql = N'' select @sql = @sql +N'drop '+ case when o.type in ('IF','TF','FN') then N'function ' when o.type = 'P' then N'procedure ' when o.type = 'V' then N'view ' when o.type = 'TR' then N'trigger ' end +schema_name(o.schema_id)+N'.'+quotename(o.name)+N';' from dbo.objectsToRecreate otr join sys.objects o on otr.ObjectName = o.name exec (@sql)

Teraz śmiało wykonujemy naszą operację, której przeszkadzałyby zdropione obiekty i zabieramy się do ich odtworzenia. Tym razem raczej nie obejdzie się bez kursora. Nie wydaje się on na pierwszy rzut oka czymś specjalnie trudnym:

declare @definition nvarchar(max) declare c cursor local static read_only forward_only for select ObjectDefinition from dbo.objectsToRecreate open c fetch next from c into @definition while @@fetch_status = 0 begin exec (@definition) fetch next from c into @definition end close c deallocate c

Wszystko zadziała pięknie, o ile usunięte uprzednio obiekty nie będą zależne od siebie. Szczególnie boleśnie możemy to odczuć na widokach i funkcjach. Jeśli nie odtworzymy obiektów we właściwej kolejności – operacja po prostu się nie uda. Pytanie tylko: Jak znaleźć tę właściwą kolejność? Jak się okazuje – pytanie niebanalne. Mamy wprawdzie tabelę sys.sql_dependencies, ale po zdropieniu obiektów pustą, a i przed trudno wciąż jej ufać. Nawet gdy ją mamy porządnie uzupełnioną – rozplątanie właściwej kolejności obiektów w skomplikowanym przypadku wcale nie jest oczywiste. O sys.sql_dependencies napiszę innym razem – to temat na osobny artykuł. Tymczasem jest dużo prostsza metoda na uzyskanie właściwej kolejności. Jednym zdaniem: próbuj odtwarzać obiekty do skutku. Osiągniemy to łatwo obudowując nasz kod pętlą while. Kończymy, gdy uda nam się odtworzyć wszystkie obiekty. Aby nie oglądać paskudnych komunikatów błędów – opakujmy nasz kod bloki obsługi wyjątków:

declare @definition nvarchar(max), @name sysname while exists (select 1 from dbo.objectsToRecreate where objectId is null) begin declare c cursor local static read_only forward_only for select ObjectDefinition, ObjectName from dbo.objectsToRecreate where objectId is null open c fetch next from c into @definition, @name while @@fetch_status = 0 begin begin try exec (@definition) end try begin catch set @definition = @name+':'+error_message() print @definition end catch update dbo.objectsToRecreate set objectId = object_id(@name) where ObjectName = @name fetch next from c into @definition, @name end close c deallocate c end

Jeśli nasza baza była spójna na moment zdropienia, po kilku iteracjach będziemy mieli prawidłowo odtworzone obiekty. Niestety nasza radość trwa do momentu opakowania w/w kodu w transakcję. Po pierwszym błędzie, każda kolejna próba odtworzenia, prawidłowa lub nie, kończy się komunikatem:

Msg 3930, Level 16, State 1, Line 18
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Co gorsza, ten kod nigdy się nie kończy, bo nigdy nie dojdziemy do warunku wyjścia z pętli. I niestety dochodzimy tu do poważnego ograniczenia stosowalności mechanizmu obsługi wyjątków na SQL-u w obecnie dostępnych wersjach. I właśnie z tego powodu w tym miejscu porzuciłem ten mechanizm do rozwiązania bieżącego problemu i dobrałem się do niego w zupełnie inny sposób. Wniosek: jeśli używamy try catch, nie liczmy na to, że uda nam się w tej samej transakcji cokolwiek jeszcze wykonać poza zaprezentowaniem błędu. Musimy być zawsze gotowi na zakończenie transakcji po takiej operacji.

Pierwszy raz ...

2008-07-07T20:52:03Z

...kiedyś musi być. Do tej pory nie prowadziłem jeszcze bloga i nie mam pojęcia, jak mi to będzie szło. Namówiony przez Pawła Potasińskiego skorzystałem z uprzejmości Michała Grzegorzewskiego i postanowiłem spróbować. Inaczej nigdy się nie przekonam. Będę tu pisał o tym, czym co dzień zaskakuje mnie SQL Server i inne wynalazki świata informatyki. Pierwszy tekst już niebawem.