Obsługa dużych plików w bazie wymaga typu VARBINARY(MAX) lub FILESTREAM, zależnie od rozmiaru i sposobu dostępu.
Typ VARBINARY(MAX) a rozmiar przechowywanego obiektu
W Microsoft SQL Server dane binarne, takie jak dokumenty, obrazy, archiwa lub inne pliki, można przechowywać w kolumnie typu VARBINARY(MAX). Typ ten przechowuje zmienną liczbę bajtów i pozwala zapisać pojedynczą wartość o rozmiarze do 2^31-1 bajtów, czyli do 2 GB. Rozmiar rzeczywiście zajmowany przez konkretny rekord zależy od długości zapisanej wartości, dlatego krótkie pliki nie otrzymują automatycznie pełnego przydziału 2 GB.
Przykładowa definicja tabeli może wyglądać następująco: CREATE TABLE Dokumenty (Id int NOT NULL PRIMARY KEY, Nazwa nvarchar(260) NOT NULL, Zawartosc varbinary(max) NULL);. Plik można następnie zapisać poleceniem INSERT albo zmienić za pomocą UPDATE. Przy operacjach wykonywanych przez aplikację należy przekazywać zawartość jako parametr binarny, zamiast składać instrukcję SQL przez łączenie tekstu. Ogranicza to problemy z kodowaniem, znakami specjalnymi i niepotrzebnym kopiowaniem danych w pamięci.
Przechowywanie binariów wewnątrz tabeli
SQL Server może przechowywać wartości VARBINARY(MAX) bezpośrednio w wierszu albo poza stroną danych, gdy wartość jest większa niż dostępne miejsce w wierszu. Sposób fizycznego ułożenia danych jest wewnętrzną decyzją silnika i nie powinien być traktowany jako odpowiednik FILESTREAM. Z punktu widzenia zapytań wartość nadal pozostaje zwykłą kolumną binarną, którą można odczytywać, aktualizować i wykorzystywać w transakcjach.
Przy większych obiektach typowe operacje obejmują zapis całej wartości, pobranie jej do aplikacji albo częściowe odczyty. Do pobierania fragmentów można użyć funkcji SUBSTRING, ponieważ dla danych binarnych zwracany jest odpowiedni fragment bajtów, a nie tekstu. W praktyce należy kontrolować rozmiar buforów po stronie aplikacji, liczbę kopii danych oraz czas trwania transakcji. Duże odczyty mogą zwiększać obciążenie pamięci, przepustowości sieci i systemu wejścia-wyjścia, nawet jeśli sama tabela działa poprawnie.
FILESTREAM i dostęp strumieniowy do dużych plików
FILESTREAM jest funkcją Microsoft SQL Server przeznaczoną do przechowywania dużych wartości varbinary(max) w systemie plików Windows, przy zachowaniu kontroli metadanych przez bazę danych. Kolumna FILESTREAM musi mieć typ varbinary(max) i atrybut FILESTREAM. Tabela musi zawierać kolumnę typu ROWGUIDCOL z unikatowym indeksem, ponieważ identyfikator wiersza jest wymagany do zarządzania obiektem FILESTREAM.
Konfiguracja wymaga również włączenia FILESTREAM na poziomie instancji SQL Server oraz utworzenia grupy plików oznaczonej słowem kluczowym FILESTREAM. Przykładowy fragment definicji tabeli to Plik varbinary(max) FILESTREAM NULL oraz kolumna identyfikatora wiersza z ograniczeniem UNIQUE ROWGUIDCOL. Dostęp strumieniowy przez interfejs Win32 wykorzystuje funkcję OpenSqlFilestream. Operacja wymaga kontekstu transakcji pobranego między innymi przez funkcję GET_FILESTREAM_TRANSACTION_CONTEXT(), a połączenie musi korzystać z odpowiedniego poziomu izolacji i protokołu obsługiwanego przez FILESTREAM.
Diagnostyka zapisu, odczytu i wydajności
Przy problemach z obsługą dużych plików należy najpierw ustalić, czy aplikacja korzysta ze zwykłego parametru VARBINARY(MAX), czy z mechanizmu FILESTREAM. Następnie warto sprawdzić rzeczywisty rozmiar wartości funkcją DATALENGTH, na przykład: SELECT Id, DATALENGTH(Zawartosc) FROM Dokumenty;. Wynik jest podawany w bajtach i pozwala odróżnić pustą wartość NULL od zapisanej wartości o zerowej długości. Trzeba także zweryfikować, czy problem występuje podczas zapisu, pobierania, transmisji sieciowej, czy dopiero przy otwieraniu pliku przez aplikację.
W diagnostyce sprawdza się typ kolumny, ograniczenia tabeli, wolne miejsce w bazie i grupach plików, uprawnienia konta aplikacji oraz czas trwania transakcji. Dla FILESTREAM dodatkowo istotne są ustawienia funkcji FILESTREAM, dostęp do udziału FILESTREAM i poprawność kontekstu transakcji. Nieprawidłowe użycie ścieżki systemowej zamiast uchwytu zwróconego przez SQL Server może prowadzić do błędów dostępu lub niespójności. W przypadku uszkodzeń sprzętowych laptopa albo komputera PC diagnostyka nośnika danych powinna być wykonana przed wielokrotnym kopiowaniem dużych plików, ponieważ dodatkowe operacje zapisu mogą pogorszyć stan niestabilnego dysku.
