Microsoft SQL Server 2008

Import zbiorczy w SQL Server 2008 Udostępnij na: Facebook

Opublikowano: 18 lipca 2008

Dokument opisuje możliwości optymalizowania importu zbiorczego danych do tabeli na serwerze Microsoft SQL Server przy użyciu polecenia bcp, instrukcji BULK INSERT lub funkcji Transact-SQL OPENROWSET(BULK...).

*

Zawartość strony
 Optymalizacja wydajności importu zbiorczego   Optymalizacja wydajności importu zbiorczego
 O operacjach importu i eksportu zbiorczego   O operacjach importu i eksportu zbiorczego
 Przegląd modeli odzyskiwania   Przegląd modeli odzyskiwania
 Wskazówki dotyczące optymalizacji importu zbiorczego   Wskazówki dotyczące optymalizacji importu zbiorczego
 Rozważania dotyczące przełączania się z modelu odzyskiwania Full lub Bulk-Logged   Rozważania dotyczące przełączania się z modelu odzyskiwania Full lub Bulk-Logged
 Operacje, które mogą być rejestrowane w minimalnym zakresie   Operacje, które mogą być rejestrowane w minimalnym zakresie
 Wymagania wstępne dla rejestrowania w minimalnym zakresie w imporcie zbiorczym   Wymagania wstępne dla rejestrowania w minimalnym zakresie w imporcie zbiorczym
 Importowanie danych równoległe z blokowaniem na poziomie tabeli   Importowanie danych równoległe z blokowaniem na poziomie tabeli
 Zarządzanie wsadami importu zbiorczego   Zarządzanie wsadami importu zbiorczego
 Kontrolowanie wykonania wyzwalaczy podczas importu zbiorczego danych   Kontrolowanie wykonania wyzwalaczy podczas importu zbiorczego danych
 Kontrolowanie sprawdzania ograniczeń przez operacje importu zbiorczego   Kontrolowanie sprawdzania ograniczeń przez operacje importu zbiorczego
 Kontrolowanie porządku sortowania podczas importu zbiorczego danych   Kontrolowanie porządku sortowania podczas importu zbiorczego danych
 Kontrolowanie mechanizmu blokowania w imporcie zbiorczym   Kontrolowanie mechanizmu blokowania w imporcie zbiorczym
 Wykorzystanie formatu macierzystego do importowania lub eksportowania danych   Wykorzystanie formatu macierzystego do importowania lub eksportowania danych
 Wykorzystanie formatu macierzystego Unicode do importowania lub eksportowania danych   Wykorzystanie formatu macierzystego Unicode do importowania lub eksportowania danych

Optymalizacja wydajności importu zbiorczego

Dokument opisuje możliwości optymalizowania importu zbiorczego danych do tabeli na serwerze Microsoft SQL Server przy użyciu polecenia bcp, instrukcji BULK INSERT lub funkcji Transact-SQL OPENROWSET(BULK...). Aby minimalizować czas trwania operacji importu zbiorczego lub eksportu danych, trzeba zrozumieć czynniki, które wpływają na wydajność oraz dostępne kwalifikatory poleceń, które pozwalają zarządzać wydajnością. O ile to możliwe, do importu zbiorczego danych na serwer SQL Server lepiej jest wykorzystywać instrukcję Transact-SQL, ponieważ działa ona szybciej niż bcp.

Najlepsza metoda zwiększania wydajności określonej operacji importu zbiorczego zależy od następujących czynników:

  •  Czy tabela zawiera ograniczenia lub wyzwalacze lub jedne i drugie?

  •  Jaki model odzyskiwania jest wykorzystywany w bazie danych?

  •  Czy tabela, do której kopiowane są dane, jest pusta?

  •  Czy tabela zawiera indeksy?

  •  Czy określona jest opcja TABLOCK?

  •  Czy dane kopiowane są z pojedynczej aplikacji klienckiej czy równolegle z wielu aplikacji?

  •  Czy dane mają być kopiowane między dwoma komputerami, na których działa SQL Server?

Ważne:

W wersji SQL Server 2005 i późniejszych możliwa jest optymalizacja importu zbiorczego, gdy wyzwalacze są włączone. Na potrzeby wyzwalaczy wykorzystywany jest mechanizm wersjonowania wierszy (wersje wierszy są składowane w magazynie wersji w tempdb). W związku z tym w przypadku importu zbiorczego dużego wsadu rekordów danych z wykorzystaniem wyzwalaczy być może trzeba będzie wcześniej rozszerzyć rozmiar bazy danych tempdb, aby dostosować go do wpływu wyzwalaczy na magazyn wersji.

Metody optymalizacji importu zbiorczego

SQL Server oferuje następujące metody pozwalające przyśpieszyć import zbiorczy danych:

  •   Wykorzystanie rejestrowania w minimalnym zakresie (ang. minimal logging) Model odzyskiwania Simple w minimalnym zakresie rejestruje większość operacji zbiorczych.

W przypadku bazy danych wykorzystującej model odzyskiwania Full, wszystkie operacje wstawiania wierszy, które są wykonywane podczas importu zbiorczego, są w pełni rejestrowane w dzienniku transakcji. Jeśli import obejmuje duże ilości danych, może to powodować gwałtowne wypełnienie dziennika transakcji. W przypadku operacji importu zbiorczego rejestrowanie w minimalnym zakresie jest bardziej efektywne niż rejestrowanie pełne i redukuje zagrożenie, że operacja importu wypełni obszar dziennika. Aby w minimalnym zakresie rejestrować operację importu zbiorczego w bazie danych, która normalnie wykorzystuje model odzyskiwania Full, można najpierw przełączyć bazę danych do modelu odzyskiwania Bulk-logged. Po zbiorczym zaimportowaniu danych należy powrócić do modelu odzyskiwania Full.

Uwaga:

Wstawione wiersze są rejestrowane w minimalnym zakresie, jeśli można zastosować zoptymalizowane rejestrowanie zbiorcze. W przeciwnym wypadku wstawione wiersze są w pełni rejestrowane w dzienniku transakcji. Informacje na temat tego, kiedy operacje importu zbiorczego są rejestrowane i w jaki sposób można realizować rejestrowane w minimalnym zakresie operacje importu zbiorczego znaleźć można w artykułach Operacje, które mogą być rejestrowane w minimalnym zakresie oraz Wymagania wstępne dla rejestrowania w minimalnym zakresie w imporcie zbiorczym.

  •   Importowanie danych z wielu aplikacji klienckich równolegle do jednej tabeli SQL Server umożliwia zbiorcze importowanie danych do jednej tabeli z wielu aplikacji klienckich w sposób równoległy. Wszystkie trzy mechanizmy importowania zbiorczego wspierają równoległy import danych. Pozwala on poprawić wydajność operacji importu danych.

  •   Wykorzystanie wsadów Informacje na temat wykorzystania wsadów podczas importowania danych oraz informacje na temat kwalifikatorów poleceń do zarządzania wsadami znaleźć można w artykule Zarządzanie wsadami importu zbiorczego .

Uwaga:

Opcja BULK klauzuli OPENROWSET nie wspiera kontrolowania rozmiaru wsadu.

  •   Wyłączenie wyzwalaczy Wyłączenie wyzwalaczy może poprawić wydajność. Więcej informacji na temat wpływu wykonania wyzwalaczy na operacje importu zbiorczego oraz sposobu włączania i wyłączania wyzwalaczy znaleźć można w artykule Kontrolowanie wykonania wyzwalaczy podczas importu zbiorczego danych.

  •   Wyłączenie ograniczeń Informacje na temat wpływu sprawdzania ograniczeń na operacje importu zbiorczego oraz sposobu włączania i wyłączania ograniczeń CHECK oraz FOREIGN KEY tabeli znaleźć można w artykule Kontrolowanie sprawdzania ograniczeń przez operacje importu zbiorczego.

  •   Porządkowanie danych w pliku danych Domyślnie operacja importu zbiorczego przyjmuje założenie, że plik danych jest nieuporządkowany. Jeśli tabela zawiera indeks klastrowany, narzędzie bcp, instrukcja BULK INSERT oraz funkcja Transact-SQL OPENROWSET(BULK…) umożliwiają określenie podczas operacji importu zbiorczego, w jaki sposób posortowane są dane w pliku danych. Dane w pliku danych nie muszą być posortowane w ten sam sposób, co w tabeli. Jednak wydajność operacji importu zbiorczego może się zwiększyć, jeśli określone zostanie to samo sortowanie.

  •   Kontrolowanie mechanizmu blokowania Informacje na temat sposobu określania mechanizmu blokowania podczas operacji importu zbiorczego znaleźć można w artykule Kontrolowanie mechanizmu blokowania w imporcie zbiorczym .

 Do początku strony Do początku strony

O operacjach importu i eksportu zbiorczego

Microsoft SQL Server wspiera eksportowanie zbiorcze danych z tabeli SQL Server oraz importowanie zbiorcze danych do tabeli lub widoku niepartycjonowanego SQL Server. Dostępne są następujące podstawowe metody.

Metoda Opis Import danych Export danych
narzędzie bcp Narzędzie wiersza polecenia (Bcp.exe), które zbiorczo eksportuje i importuje dane oraz generuje pliki formatu. Tak Tak
instrukcja BULK INSERT Instrukcja Transact-SQL, która importuje dane bezpośrednio z pliku danych do tabeli bazodanowej lub widoku niepartycjonowanego. Tak Nie
instrukcja INSERT ... SELECT * FROM OPENROWSET(BULK...) Instrukcja Transact-SQL, która wykorzystuje dostawcę zestawu wierszy zbiorczych dla funkcji OPENROWSET do zbiorczego importowania danych do tabeli SQL Server, określając funkcję OPENROWSET(BULK…) w celu wybrania danych w instrukcji INSERT. Tak Nie

Operacja w trybie In-Process kontra Out-of-Process

Instrukcja BULK INSERT and OPENROWSET(BULK) jest wykonywana w trybie in-process z serwerem SQL Server, dzieląc ten sam obszar przestrzeni adresowej. Ponieważ pliki danych są otwierane przez proces SQL Server, dane nie są kopiowane między procesem klienckim a procesami SQL Server. Względy bezpieczeństwa związane z importowaniem danych przy użyciu instrukcji BULK INSERT lub INSERT ... SELECT * FROM OPENROWSET(BULK...) opisane zostały w artykule Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) .

Natomiast narzędzie bcp działa w trybie out-of-process. Aby przenieść dane między obszarami pamięci procesu, narzędzie bcp musi zastosować mechanizm inter-process data marshalling. Proces inter-process data marshalling konwertuje parametry wywołania metody w strumień bajtów. Może to znacznie zwiększać obciążenie procesora. Jednak ponieważ narzędzie bcp analizuje dane i konwertuje je do macierzystego formatu składowania w procesie klienckim, może zmniejszyć obciążenie związane z analizą i konwersją danych w ramach procesu SQL Server. A zatem jeśli zasoby procesora są ograniczone, można osiągnąć lepszą wydajność importu zbiorczego na komputerze, który ma więcej niż jeden procesor lub na różnych komputerach, wykorzystując narzędzie bcp zamiast BULK INSERT lub INSERT ... SELECT * FROM OPENROWSET(BULK).

Pliki formatu

Narzędzie bcp oraz instrukcje BULK INSERT i INSERT ... SELECT * FROM OPENROWSET(BULK...) wspierają wykorzystanie specjalnego pliku formatu, który przechowuje informacje o formacie dla każdego pola w pliku danych. Plik formatu może również zawierać informacje o odpowiedniej tabeli SQL Server. Plik formatu może posłużyć do dostarczenia wszystkich informacji o formacie, które są potrzebne do zbiorczego eksportu lub importu danych na instancję SQL Server.

Pliki formatu podczas importu zapewniają elastyczny sposób interpretowania danych znajdujących się w pliku danych, a podczas eksportu pozwalają formatować dane w pliku danych. Taka elastyczność eliminuje konieczność pisania dedykowanego kodu służącego do interpretowania danych lub zmiany formatu danych zgodnie z określonymi wymaganiami serwera SQL Server lub aplikacji zewnętrznej. Na przykład podczas zbiorczego eksportu danych, które mają zostać pobrane przez aplikację wymagającą wartości rozdzielonych przecinkiem, można użyć pliku formatu do wstawienia przecinków w roli terminatorów pól w danych eksportowanych.

Wersja SQL Server 2005 oraz późniejsze zapewniają wsparcie dla dwóch typów plików formatu: plików formatu XML oraz plików formatu nie-XML. Pliki formatu nie-XML są wspierane przez wcześniejsze wersje SQL Server. Pliki formatu XML zostały wprowadzone w wersji SQL Server  2005.  

Narzędzie bcp to jedyne narzędzie, które potrafi generować plik formatu.

Uwaga:

W sytuacjach, gdy podczas operacji importu lub eksportu zbiorczego plik formatu nie jest dostarczony, użytkownik może zdecydować się na nadpisanie domyślnego formatowania w wierszu polecenia.

Procesor kwerend a import zbiorczy

Podczas zbiorczego importu danych na instancję SQL Server, zarówno narzędzie bcp, jak i instrukcje BULK INSERT oraz INSERT ... SELECT * FROM OPENROWSET(BULK...) współpracują z procesorem kwerend.

Wszystkie trzy metody konwertują dane pochodzące z pliku danych do zestawów wierszy OLE DB. Jednak metody konwersji są różne:

  •  Narzędzie bcp odczytuje plik danych i wysyła strumień TDS do interfejsu API programu SQL Server Bulk Copy Program (BCP), który konwertuje dane do zestawów wierszy OLE DB.

  •  Dostawca zestawu wierszy zbiorczych dla instrukcji BULK INSERT oraz OPENROWSET konwertuje dane z pliku bezpośrednio do zestawu wierszy OLE DB.

Zestawy wierszy OLE DB są wstawiane do tabeli docelowej przez procesor kwerend, który planuje i optymalizuje każdą operację.

Względy wydajnościowe

Względy wydajnościowe mogą być bardzo istotne w przypadku importowania dużych ilości danych. W niektórych sytuacjach wydajność może zostać poprawiona poprzez zmianę sposobu, w jaki operacja importu lub eksportu zbiorczego obsługuje wybrane spośród następujących aspektów:

  •  Przełączniki wsadu

  •  Sprawdzanie ograniczeń CHECK

  •  Sposób rejestrowania transakcji zbiorczych. Dotyczy to baz danych, które z reguły wykorzystują model odzyskiwania Full.

  •  Porządkowanie danych eksportowanych

  •  Równoległe importowanie danych

  •  Blokowanie na poziomie tabeli

  •  Wykonanie wyzwalaczy

Uwaga:

Nie istnieją żadne szczególne techniki optymalizacji dla operacji eksportu zbiorczego. Operacje te pobierają po prostu dane z tabeli źródłowej przy pomocy instrukcji SELECT.

 Do początku strony Do początku strony

Przegląd modeli odzyskiwania

Modele odzyskiwania (ang. Recovery model) zostały zaprojektowane z myślą o kontroli procesu utrzymywania dziennika transakcji. Istnieją trzy modele odzyskiwania: Simple, Full oraz Bulk-logged. Zazwyczaj bazy danych wykorzystują model odzyskiwania Full lub model odzyskiwania Simple.

Poniższa tabela prezentuje zestawienie wspomnianych modeli odzyskiwania.

Model odzyskiwania Opis Ryzyko utraty wyniku pracy Odzyskiwanie do punktu w czasie?
Simple

Brak kopii zapasowych dzienników.

Automatycznie odzyskuje miejsce dziennika, aby utrzymywać małe wymagania pamięci, w zasadzie eliminując potrzebę zarządzania obszarem dziennika transakcji.

Zmiany wprowadzone od czasu wykonania ostatniej kopii zapasowej nie są chronione. W przypadku awarii zmiany te muszą zostać przeprowadzone ponownie. Można przywrócić kopię zapasową jedynie do samego końca.
Full

Wymaga kopii zapasowych dzienników.

Żaden wynik pracy nie zostanie utracony w wyniku utraty lub uszkodzenia pliku danych. Możliwe jest odzyskanie do dowolnego punktu w czasie (np. przed błędem aplikacji lub użytkownika).

Zazwyczaj żadne.

Jeśli końcówka dziennika zostanie zniszczona, zmiany wprowadzone od czasu wykonania ostatniej kopii zapasowej muszą zostać przeprowadzone ponownie. Więcej informacji znaleźć można w artykule Tail-Log Backups.

Można odzyskać stan sprzed określonego punktu w czasie przy założeniu, że istnieją pełne kopie zapasowe utrzymywane aż do tego momentu. Więcej informacji znaleźć można w artykule Restoring a Database to a Point Within a Backup .
Bulk logged

Wymaga kopii zapasowych dzienników.

Dopełnienie modelu odzyskiwania Full, umożliwia operacje kopiowania zbiorczego o wysokiej wydajności. Redukuje rozmiar miejsca wykorzystywanego przez dziennik poprzez rejestrowanie zbiorcze większości operacji zbiorczych.

Jeśli dziennik jest uszkodzony lub po wykonaniu ostatniej kopii zapasowej wystąpiły operacje rejestrowane zbiorczo, zmiany wprowadzone po ostatniej archiwizacji muszą zostać przeprowadzone ponownie.

W przeciwnym wypadku żadne efekty pracy nie zostają utracone.

Można przywrócić dowolną kopię zapasową do końca. Odzyskiwanie do punktu w czasie nie jest wspieran

Uwaga:

Wybór właściwego modelu odzyskiwania zależy od wymagań dotyczących dostępności oraz odzyskiwania określonej bazy danych.

Model odzyskiwania Simple

Model odzyskiwania Simple minimalizuje obciążenie związane z dziennikiem transakcji, ponieważ nie są dla niego tworzone kopie zapasowe. Model odzyskiwania Simple niesie za sobą wysokie ryzyko utraty znacznej części pracy, jeśli baza danych zostanie zniszczona. Możliwe jest tylko odzyskanie danych sprzed momentu wykonania ostatniej kopii zapasowej utraconych danych. W związku z tym w modelu odzyskiwania Simple przerwy między archiwizacjami powinny być na tyle krótkie, aby zapobiec utracie znacznych ilości danych. Jednocześnie powinny być one na tyle długie, aby obciążenie spowodowane wykonywaniem kopii zapasowych nie wpływało zbyt negatywnie na pracę w środowisku produkcyjnym. Opracowanie strategii archiwizacji z wykorzystaniem różnicowych kopii zapasowych może pomóc w zredukowaniu tego obciążenia.

Ogólnie dla baz danych użytkownika model odzyskiwania Simple przydaje się w przypadku baz danych testowych i fazy rozwoju lub w przypadku baz danych zawierających głównie dane tylko do odczytu, takich jak hurtownia danych. Model odzyskiwania Simple nie jest odpowiedni w przypadku systemów produkcyjnych, w których utrata ostatnich zmian jest nie do zaakceptowania. W takich sytuacjach zaleca się zastosowanie modelu odzyskiwania Full.

Modele odzyskiwania Full i Bulk-logged

Modele odzyskiwania Full i Bulk-logged zapewniają większą ochronę danych niż model odzyskiwania Simple. Te modele odzyskiwania wykorzystują kopie zapasowe dziennika transakcji do zapewniania maksymalnej możliwości odzyskiwania i zapobiegania utracie efektów pracy w najszerszym zakresie scenariuszy awarii.

  •  Model odzyskiwania Full Zapewnia normalny model utrzymywania bazy danych dla baz danych, w których konieczna jest trwałość transakcyjna. Wymagane jest tworzenie kopii zapasowych dziennika. Model ten w pełni rejestruje wszystkie transakcje i przechowuje rekordy dziennika transakcji do momentu, dopóki nie zostaną one zarchiwizowane. Model odzyskiwania Full umożliwia odzyskiwanie bazy danych z momentu sprzed awarii, przy założeniu że po awarii może zostać stworzona kopia zapasowa końcówki dziennika. Model odzyskiwania Full wspiera również odzyskiwanie poszczególnych stron danych.

  •  Model odzyskiwania Bulk-logged Ten model odzyskiwania zbiorczo rejestruje większość operacji zbiorczych. Został on zaprojektowany jedynie jako uzupełnienie modelu odzyskiwania Full. W przypadku pewnych operacji zbiorczych na dużą skalę, takich jak zbiorczy import lub tworzenie indeksu, chwilowe przełączenie się do modelu odzyskiwania Bulk-logged zwiększa wydajność i redukuje wykorzystanie miejsca dziennika. Nadal wymagane są kopie zapasowe dziennika. Podobnie jak model odzyskiwania Full, model odzyskiwania Bulk-logged przechowuje rekordy dziennika transakcji do momentu, dopóki nie zostaną one zarchiwizowane. Model ten pociąga za sobą większy rozmiar kopii zapasowych dziennika oraz zwiększone ryzyko utraty efektów pracy, ponieważ model odzyskiwania Bulk-logged nie wspiera odzyskiwania do punktu w czasie.

Ważne:

W modelach odzyskiwania Full oraz Bulk-logged kopie zapasowe dziennika są niezbędne. Aby nie wykonywać kopii zapasowych dziennika, trzeba użyć modelu odzyskiwania Simple.

 Do początku strony Do początku strony

Wskazówki dotyczące optymalizacji importu zbiorczego

Dokument zawiera zalecenia dotyczące optymalizacji wydajności w wybranych scenariuszach importu zbiorczego:

  •  Importowanie danych z jednej aplikacji klienckiej (lub strumienia) do pustej tabeli.

  •  Importowanie danych z jednej aplikacji klienckiej (lub strumienia) do częściowo wypełnionej, niepustej tabeli.

Uwaga:

Importowanie danych do niepustej tabeli nazywane jest przyrostowym importem zbiorczym. Kluczowe pytanie w przypadku przyrostowego importu zbiorczego to, czy indeksy powinny zostać wcześniej usunięte.

  •  Importowanie danych równolegle z wielu aplikacji klienckich (lub strumieni) z blokowaniem na poziomie tabeli.

  •  Kopiowanie danych między instancjami Microsoft SQL Server.

Omówione zostanie również zestawienie blokowania na poziomie tabeli oraz rejestrowania podczas operacji importu zbiorczego.

Importowanie danych z jednej aplikacji klienckiej (lub strumienia) do pustej tabeli

W przypadku importu danych do pustej tabeli z pojedynczej aplikacji klienckiej (lub strumienia) firma Microsoft zaleca wykonanie następujących czynności:

  •  Określenie kwalifikatora TABLOCK, który jest dostępny jako wskazówka (ang. hint) lub opcja we wszystkich trzech metodach importu zbiorczego. Wykorzystanie kwalifikatora TABLOCK powoduje zastosowanie blokady na poziomie tabeli na czas operacji zbiorczej i eliminuje obciążenie spowodowane blokowaniem poszczególnych wierszy.

  •  Obsłużenie indeksów w sposób następujący. W przypadku wykorzystania bcp, BULK INSERT lub INSERT ... SELECT * FROM OPENROWSET(BULK...), jeśli tabela jest pusta i zawiera indeks klastrowany, a dane w pliku danych są uporządkowane tak, aby odpowiadały kolumnom klucza indeksu klastrowanego, należy dodatkowo:

  •  Zbiorczo zaimportować dane z istniejącym indeksem klastrowanym.

  •  Określić wskazówkę ORDER, a także wskazówkę TABLOCK.

W przypadku pustej tabeli to rozwiązanie jest znacznie szybsze niż tworzenie indeksu klastrowanego po zaimportowaniu danych, ponieważ eliminuje fazę sortowania.

Uwaga:

Jeśli niepusta tabela zawiera indeksy, importy zbiorcze są w pełni rejestrowane, nawet w modelu odzyskiwania Bulk-logged. Decydując o tym, czy indeksy mają zostać usunięte, należy wziąć pod uwagę fakt, czy korzyść wynikająca z importowania zbiorczego do tabeli bez indeksów jest większa niż koszt ich usunięcia, a następnie odtworzenia.

Gdy dane są importowane zbiorczo do pustej tabeli z indeksami i określony jest rozmiar wsadu, tabela przestaje być pusta po wykonaniu pierwszego wsadu. Począwszy od drugiego wsadu dane są w pełni rejestrowane. W przypadku pustych tabel z indeksami warto spróbować zrealizować import zbiorczy w ramach pojedynczego wsadu.

Uwaga:

Gdy rozmiar wsadu nie jest określony, domyślnie optymalizator kwerend SQL Server przyjmuje założenie, że domyślny rozmiar jest rozmiarem pliku danych. Aby poprawić wydajność, można wykorzystać kwalifikator ROWS_PER_BATCH lub KILOBYTES_PER_BATCH jako wskazówkę dla optymalizatora dotyczącą przybliżonej liczby wierszy w pliku danych.

Generalnie import zbiorczy do tabeli bez jakichkolwiek indeksów jest szybszy niż import zbiorczy do tabeli z indeksami. Z tego względu jeśli puste tabele zawierają indeksy, warto je usunąć przed importem danych do tabeli, a następnie ponownie je stworzyć. Jeśli dane nie są posortowane według kolumny klucza klastrowanego i jeśli tabela jest pusta, należy usunąć wszystkie indeksy,

zaimportować dane, a następnie stworzyć nowe indeksy.

Importowanie danych z jednej aplikacji klienckiej (lub strumienia) do niepustej tabeli

Importowanie danych do niepustej tabeli nazywane jest przyrostowym importem zbiorczym. Kluczowe pytanie w przypadku przyrostowego importu zbiorczego to, czy indeksy powinny zostać wcześniej usunięte.

W przypadku importowania danych z jednej aplikacji klienckiej (lub strumienia) do niepustej tabeli, decyzja, czy należy utrzymywać indeksy, powinna zależeć od ilości importowanych nowych danych w stosunku do ilości danych znajdujących się w tabeli:

  •  W przypadku gdy stosunek ilości nowych, importowanych danych do ilości danych istniejących jest niewielki, usunięcie i odtworzenie indeksów może okazać się zupełnie nieefektywne. Czas konieczny do odbudowania indeksów będzie prawdopodobnie dłuższy niż czas zaoszczędzony podczas operacji zbiorczej.

  •  Natomiast gdy import obejmuje większą ilość nowych danych, usunięcie indeksów z tabeli przed wykonaniem operacji zbiorczej pozwala zwiększyć wydajność bez znaczącego zwiększania czasu, jaki zajmuje ponowne zaindeksowanie.

Następująca tabela zawiera listę minimalnych ilości nowych danych, które powinna zawierać tabela, aby usunięcie indeksów mogło być opłacalne. Minimalna ilość stanowi stosunek nowych danych do całkowitej ilości danych w tabeli. Ilość ta różni się w zależności od typów i kombinacji indeksów. Jeśli rozmiar nowych danych przekroczy sugerowany procent dla danego typu indeksu lub grup indeksów, warto rozważyć usunięcie indeksów przed wykonaniem operacji zbiorczej i odtworzenie ich po jej wykonaniu. Dokładne proporcje zależą od wzorca istniejących danych oraz pobieranych danych, dlatego przedstawione liczby należy traktować jedynie jako ogólną wskazówkę.

Indeksy Względna ilość nowych danych
Tylko indeks klastrowany 30 procent
Indeks klastrowany i jeden indeks nieklastrowany 25 procent
Indeks klastrowany i dwa indeksy nieklastrowane 25 procent
Tylko jeden indeks nieklastrowany 100 procent
Dwa indeksy nieklastrowane 60 procent

Importowanie danych równolegle z wielu aplikacji klienckich (lub strumieni) z blokowaniem na poziomie tabeli

Jeśli SQL Server działa na maszynie, która zawiera więcej niż jeden procesor, a dane, które mają być zbiorczo importowane do tabeli, mogą zostać podzielone na osobne pliki danych, można podnieść wydajność, importując dane z wielu aplikacji klienckich równolegle. W przypadku zbiorczego importowania z wielu aplikacji klienckich do jednej tabeli każda aplikacja kliencka musi posiadać swój własny wejściowy plik danych.

W przypadku importowania danych do tabeli z wielu aplikacji klienckich warto rozważyć następujące kwestie:

  •  Poszczególne strumienie importu zbiorczego mogą potencjalnie blokować siebie nawzajem. Aby temu zapobiec, SQL Server wprowadza specjalną blokadę wewnętrzną o nazwie Bulk-Update (BU). Aby wykorzystać blokadę BU trzeba określić opcję TABLOCK w każdym strumieniu importu zbiorczego bez blokowania innych strumieni importu zbiorczego. To pozwala uniknąć konfliktów w dostępie do tabeli między aplikacjami klienckimi. Jednak blokada BU jest dostępna jedynie dla tabeli (pustej lub niepustej), które nie posiada indeksów. Jeśli TABLOCK zostanie ustawiony na tabeli z indeksami, równoległy import zbiorczy nie będzie możliwy. Jeśli tabela zawiera jakieś indeksy, nadal można wykorzystać blokowanie BU - w tym celu trzeba usunąć wszystkie indeksy przed zbiorczym importem danych. Następnie można dokonać równoległego importu zbiorczego przy użyciu kwalifikatora TABLOCK, a po zakończeniu odtworzyć indeks lub indeksy. Warto mieć również na uwadze, że jeśli niepusta tabela zawiera indeksy, importy zbiorcze są w pełni rejestrowane, nawet w modelu odzyskiwania Bulk-logged. Decydując o tym, czy indeksy mają zostać usunięte, należy wziąć pod uwagę fakt, czy korzyść wynikająca z importowania zbiorczego do tabeli bez indeksów jest większa niż koszt ich usunięcia, a następnie odtworzenia.

Uwaga:

W przypadku usunięcia indeksów pomocniczych warto rozważyć równoległe ich odtworzenie poprzez stworzenie każdego indeksu pomocniczego z osobnej aplikacji klienckiej.

Aby uniknąć usuwania i odtwarzania indeksów, można wykonać równoległy import bez określania

wskazówki TABLOCK. Jednak w tym przypadku poszczególne strumienie importu zbiorczego mogą blokować się wzajemnie, a ponadto niedostępna staje się optymalizacja rejestrowania zbiorczego. Aby zminimalizować blokowanie, można określić mniejszy rozmiar wsadu i wykorzystać wskazówkę ORDER, aby wyeliminować etap sortowania podczas operacji importu zbiorczego.

Dane muszę zostać podzielone na wiele plików wejściowych, po jednym pliku dla każdego klienta. Aby zasoby procesora była jak najefektywniej wykorzystywane, pliki danych powinny mieć podobne rozmiary.

Blokowanie na poziomie tabeli a rejestrowanie podczas importu zbiorczego

Następująca tabela prezentuje, w jaki sposób podczas operacji importu zbiorczego schemat tabeli determinuje typy blokad. Tabela ta wskazuje również, czy tabela jest pusta, czy dla operacji ustawiona jest wskazówka TABLOCK oraz jakiego typu rejestrowanie pojawia się, jeśli baza danych wykorzystuje model odzyskiwania Bulk-logged.

Uwaga:

Po pomyślnym wykonaniu pierwszego wsadu, tabela przestaje być pusta.

Tabela docelowa importu zbiorczego Czy tabela jest pusta? Czy ustawiono TABLOCK? Blokady Rejestrowanie w modelach odzyskiwania Bulk-logged oraz Simple
Sterta Tak Tak BU-Tab Bulk-logged
Sterta Tak Nie IX-Tab Full
Sterta Nie Tak BU-Tab Bulk-logged
Sterta Nie Nie IX-Tab Full
Sterta z jednym indeksem nieklastrowanym Tak Tak SCH-M Bulk-logged
Sterta z jednym indeksem nieklastrowanym Tak Nie IX-Tab Full
Sterta z jednym indeksem nieklastrowanym Nie Tak SCH-M

  •  Dane—Bulk-logged

  •  Indeks—Full

Sterta z jednym indeksem nieklastrowanym Nie Nie IX-Tab Full
Indeks klastrowany Tak Tak SCH-M Bulk-logged
Indeks klastrowany Tak Nie IX-Tab Full
Indeks klastrowany Nie Tak X-TAB Full
Indeks klastrowany Nie No Nie IX-Tab Full

Kopiowanie danych między instancjami SQL Server

Abt kopiować zbiorczo dane z jednej instancji SQL Server na inną, należy użyć narzędzia bcp do eksportowania danych z tabeli do pliku danych. Następnie należy użyć jednej z metod importu zbiorczego do zaimportowania danych z pliku do tabeli. Zarówno operację eksportu, jak i importu zbiorczego należy przeprowadzić z wykorzystaniem formatu macierzystego lub formatu macierzystego Unicode.

Uwaga:

Więcej informacjo na temat tych formatów znaleźć można w artykułach Wykorzystanie formatu macierzystego do importowania lub eksportowania danych oraz Wykorzystanie formatu macierzystego Unicode do importowania lub eksportowania danych.

Jeśli tabela źródłowa zawiera indeks klastrowany lub jeśli planowany jest zbiorczy import danych do tabeli posiadającej indeks klastrowany, należy:

  1.  Zbiorczo wyeksportować dane z tabeli źródłowej przy użyciu narzędzia bcp z opcją query służącą do wykonania instrukcji SELECT oraz wykorzystania odpowiedniej klauzuli ORDER BY w celu stworzenia uporządkowanego pliku danych. Więcej informacji znaleźć można w artykule bcp Utility .

  2.  Gdy zbiorczo importuje się dane do SQL Server, należy wykorzystać kwalifikator ORDER, który jest wspierany jedynie przez bcp oraz BULK INSERT.

 Do początku strony Do początku strony

Rozważania dotyczące przełączania się z modelu odzyskiwania Full lub Bulk-Logged

Baza danych może zostać przełączona do innego modelu odzyskiwania w dowolnym momencie. Jeśli przełączenie następuje podczas operacji zbiorczej, odpowiednio zmienia ono sposób rejestrowania tej operacji.

Przełączanie się między modelami odzyskiwania Full oraz Bulk-logged jest przydatne przed i po dużych operacjach zbiorczych. Model odzyskiwania Full, który w pełni rejestruje wszystkie transakcje, jest przeznaczony do codziennego wykorzystania. Model odzyskiwania Bulk-logged jest przeznaczony do wykorzystania tymczasowego podczas dużej operacji zbiorczej, przy założeniu jest to jedna z operacji zbiorczych, na które wpływa model odzyskiwania Bulk-logged (więcej informacji znaleźć można w artykule Operacje, które mogą być rejestrowane w minimalnym zakresie ). Jeśli podczas operacji zbiorczej następuje przełączenie między modelami odzyskiwania Full oraz Bulk-logged, powoduje ono odpowiednią zmianę sposobu rejestrowania tej operacji.

Uwaga:

Pewne funkcje, takie jak np. database mirroring, wymagają, aby baza danych pozostała w modelu odzyskiwania Full.

Przełączanie między odzyskiwaniem Full a Bulk-Logged

W przypadku bazy danych wykorzystującej model odzyskiwania Full, tymczasowe przełączenie się do modelu odzyskiwania Bulk-logged na czas operacji zbiorczych pozwala poprawić wydajność. Jednak jeśli ewentualna utrata danych jest nie do zaakceptowania, aby jej zapobiec zaleca się przełączanie do modeli odzyskiwania Bulk-logged tylko wtedy, gdy spełnione są następujące warunki:

  •  Użytkownicy nie mają aktualnie dostępu do bazy danych.

  •  Podczas przetwarzania zbiorczego nie są dokonywane żadne modyfikacje, które nie mogą zostać odzyskane bez odpowiednich kopii zapasowych dziennika np. poprzez ponowne uruchomienie procesów zbiorczych.

Zaleca się:

  •  Stworzenie kopii zapasowej dziennika przed przełączeniem do modelu odzyskiwania Bulk-logged. Jest to istotne, na wypadek gdyby w bazie danych znajdującej się w modelu odzyskiwania Bulk-logged nastąpiła awaria, ponieważ tworzenie kopii zapasowych dziennika dla operacji zbiorczych wymaga dostępu do danych.

  •  Po wykonaniu operacji zbiorczych należy natychmiast przełączyć się z powrotem do trybu odzyskiwania Full.

  •  Po przełączeniu się z powrotem do modelu odzyskiwania Full należy ponowne stworzyć kopię zapasową dziennika.

W przypadku przełączania się między dwoma modelami odzyskiwania, strategia tworzenia kopii zapasowych pozostaje taka sama: kontynuowane jest wykonywanie okresowych kopii zapasowych bazy danych, dziennika oraz kopii różnicowych.

W przypadku przełączania się z modelu odzyskiwania Full do Bulk-logged nie trzeba podejmować żadnych dodatkowych akcji w celu ochrony danych. Wystarczy natychmiast po przełączeniu się z trybu odzyskiwania Bulk-logged do Full stworzyć kopię zapasową dziennika. To w pełni zabezpieczy dane i umożliwi odzyskiwanie do punktu w czasie.

Przełączanie z odzyskiwania Full lub Bulk-Logged do odzyskiwania Simple

Przełączanie z trybu odzyskiwania Full lub Bulk-logged do odzyskiwania Simple jest możliwe, ale rzadkie.

Należy stworzyć kopię zapasową dziennika transakcji tuż przed przełączeniem się do modelu odzyskiwania Simple, aby umożliwić odzyskiwanie do tego momentu. Mechanizm tworzenia kopii zapasowych dziennika nie jest wspierany w modelu odzyskiwania Simple, więc po przełączeniu należy przerwać wykonywanie zaplanowanych zadań tworzenia kopii zapasowych dziennika transakcji.

Zmiana modelu odzyskiwania

Aby zmienić model odzyskiwania (Transact-SQL) należy wykorzystać polecenie ALTER DATABASE w następujący sposób:

  •  Aby ustawić model odzyskiwania bazy danych na Full: USE master; ALTER DATABASE nazwa_bazy_danych SET RECOVERY FULL;

  •  Aby ustawić model odzyskiwania bazy danych na Bulk-logged: USE master ; ALTER DATABASE nazwa_bazy_danych SET RECOVERY BULK_LOGGED;

Uwaga:

Aby zmienić domyślny model odzyskiwania dla nowych baz danych, należy użyć instrukcji ALTER DATABASE do zmiany modelu odzyskiwania bazy danych model.

 Do początku strony Do początku strony

Operacje, które mogą być rejestrowane w minimalnym zakresie

Rejestrowanie w minimalnym zakresie (ang. minimal logging) wiąże się z rejestrowaniem w dzienniku transakcji minimalnej ilości informacji, która jest konieczna do przywracania transakcji bez wsparcia dla odzyskiwania do punktu w czasie. Niniejszy artykuł prezentuje operacje, które są logowane w minimalnym zakresie w modelu odzyskiwania Bulk-logged (jak również w modelu odzyskiwania Simple), ale w pełnym zakresie w modelu odzyskiwania Full.

W modelu odzyskiwania Full wszystkie operacje zbiorcze są rejestrowane w pełnym zakresie. Jednak można zminimalizować zakres rejestrowania dla szeregu operacji zbiorczych, przełączając bazę danych do modelu odzyskiwania Bulk-logged na czas działania tych operacji. Rejestrowanie zbiorcze jest bardziej efektywne niż rejestrowanie w pełnym zakresie i eliminuje ryzyko, że pokaźna operacja zbiorcza wypełni cały dostępny obszar dziennika transakcji. Jednak w przypadku gdy baza danych ulegnie uszkodzeniu lub utracie w czasie działania rejestrowania zbiorczego, nie będzie można przywrócić stanu bazy danych sprzed awarii.

Uwaga:

Jeśli proces tworzenia kopii zapasowej nie jest uruchomiony, rejestrowanie w minimalnym zakresie jest wykorzystywane w modelu odzyskiwania Simple.

Następujące operacje, które są rejestrowane w pełnym zakresie w modelu odzyskiwania Full, są rejestrowane w minimalnym zakresie w modelu odzyskiwania Bulk-logged:

  •  Operacje importu zbiorczego ( bcp, INSERT ... SELECT * FROM OPENROWSET(BULK...) oraz BULK INSERT).

  •  Zbiorcze wstawianie z wskazówką ORDER do niepustego indeksu klastrowanego.

  •  Operacje text, ntext oraz image z wykorzystaniem instrukcji WRITETEXT and UPDATETEXT podczas wstawiania lub dołączania nowych danych. Warto mieć na uwadze, że rejestrowanie w minimalnym zakresie nie jest stosowane, gdy aktualizowane są istniejące wartości.

Uwaga:

Instrukcje WRITETEXT oraz UPDATETEXT zostały zaniechane, dlatego należy unikać stosowania ich w nowych aplikacjach.

  •  Operacja SELECT INTO. Operacja SELECT INTO tworzy nową tabelę w domyślnej grupie plików.

  •  Obszerne operacje Transact-SQL INSERT INTOSELECT * FROM wstawiania do istniejącej tabeli. Tabela ta może zawierać indeks klastrowany lub stanowić stertę (tabelę bez indeksu klastrowanego), może także zawierać wiersze (tabela niepusta). Podstawowa składnia tego typu operacji jest następująca:

  •  Wstawianie do tabeli zawierającej indeks klastrowany INSERT INTO tabela_docelowa_z_indeksem_klastrowanym SELECT * FROM tabela_źródłowa ORDER BY docelowy_klucz_klastrowany

  •  Wstawianie do stery: INSERT INTO sterta_docelowa SELECT * FROM tabela_źródłowa

  •  Obszerne operacje Transact-SQL UPDATE lub MERGE .

  •  Częściowe modyfikacje typów danych dużych wartości przy pomocy klauzul .WRITE instrukcji UPDATE podczas wstawiania lub dołączania nowych danych. Warto mieć na uwadze, że rejestrowanie w minimalnym zakresie nie jest stosowane, gdy aktualizowane są istniejące wartości.

  •  Jeśli baza danych wykorzystuje model odzyskiwania Bulk-logged, pewne operacje INDEX DDL są rejestrowane w minimalnym zakresie, niezależnie od tego czy wykonywane są w trybie offline czy online. Następujące operacje indeksów są rejestrowane w minimalnym zakresie:

  •  Operacje CREATE INDEX (także widoki indeksowane).

  •  Operacje ALTER INDEX REBUILD lub DBCC DBREINDEX.

Uwaga:

Instrukcja DBCC DBREINDEX została zaniechana, dlatego należy unikać stosowania jej w nowych aplikacjach.

  •  Ponowne budowanie nowej sterty w ramach operacji DROP INDEX (o ile to możliwe).

Uwaga:

Dealokacja strony indeksu podczas operacji DROP INDEX jest zawsze rejestrowana w pełnym zakresie.

 Do początku strony Do początku strony

Wymagania wstępne dla rejestrowania w minimalnym zakresie w imporcie zbiorczym

Operacja importu zbiorczego może być rejestrowana w minimalnym zakresie tylko w modelu odzyskiwania Bulk-logged lub modelu odzyskiwania Simple. W przypadku bazy danych w modelu odzyskiwania Full wszystkie operacje wstawiania wierszy realizowane przez import zbiorczy są rejestrowane w pełnym zakresie w dzienniku transakcji. Duże importy danych mogą powodować, że dziennik transakcji wypełni się gwałtownie, gdy wykorzystywany jest model odzyskiwania Full. Natomiast rejestrowanie w minimalnym zakresie redukuje zagrożenie, że operacja importu zbiorczego wypełni cały obszar dziennika. Rejestrowanie w minimalnym zakresie jest również bardziej efektywne niż rejestrowanie w pełnym zakresie, ale wymaga, aby baza danych wykorzystywała model odzyskiwania Bulk-logged (jeśli normalnie wykorzystuje model odzyskiwania Full) lub model odzyskiwania Simple.

Uwaga:

Informacje na temat przełączania się między modelem odzyskiwania Full a modelem odzyskiwania Bulk-logged znaleźć można w artykule Rozważania dotyczące przełączania się z modelu odzyskiwania Full lub Bulk-Logged .

Wymagania dotyczące tabeli związane z rejestrowaniem w minimalnym zakresie

Rejestrowanie w minimalnym zakresie wymaga, aby tabela docelowa spełniała następujące kryteria:

  •  Tabela nie jest replikowana.

  •  Określone jest blokowanie na poziomie tabeli (przy pomocy opcji TABLOCK).

Uwaga:

Chociaż operacje wstawiania danych nie są rejestrowane w dzienniku transakcji podczas operacji importu zbiorczego rejestrowanej w minimalnym zakresie, silnik bazy danych nadal rejestruje alokacje zakresów za każdym razem, gdy nowy zakres jest alokowany do tabeli.

Indeksy w tabelach

To, czy rejestrowanie w minimalnym zakresie może zostać zastosowane w tabeli, zależy również od tego, czy tabela została zaindeksowana i jeśli tak, czy jest ona pusta:

  •  Jeśli tabela nie zawiera indeksów, strony danych są rejestrowane w minimalnym zakresie.

  •  Jeśli tabela nie zawiera indeksu klastrowanego, ale zawiera jeden lub więcej indeksów nieklastrowanych, strony danych są zawsze rejestrowane w minimalnym zakresie. Jednak to jak rejestrowane są strony indeksów, zależy od tego, czy tabela jest pusta:

  •  Jeśli tabela jest pusta, strony indeksów są rejestrowane w minimalnym zakresie.

  •  Jeśli tabela nie jest pusta, strony indeksów są rejestrowane w pełnym zakresie.

Uwaga:

Jeśli tabela jest początkowo pusta, a import zbiorczy danych obejmuje wiele wsadów, dla pierwszego wsadu zarówno strony indeksów, jak i strony danych są rejestrowane w minimalnym zakresie. Jednak począwszy od drugiego wsadu tylko strony danych są rejestrowane w minimalnym zakresie.

  •  Jeśli tabela posiada indeks klastrowany i jest pusta, zarówno strony danych, jak i indeksów są rejestrowane w minimalnym zakresie. Natomiast jeśli tabela posiada indeks klastrowany i nie jest pusta, strony danych oraz indeksów są rejestrowane w pełnym zakresie niezależnie od modelu odzyskiwania.

Uwaga:

Jeśli tabela jest początkowo pusta, a import zbiorczy danych obejmuje wiele wsadów, dla pierwszego wsadu zarówno strony indeksów, jak i strony danych są rejestrowane w minimalnym zakresie. Jednak w drugim wsadzie i kolejnych tylko strony danych są rejestrowane zbiorczo.

Najlepsze rozwiązanie

W przypadku wykorzystania trybu odzyskiwania Bulk-logged podczas importu dużego zestawu wierszy tabeli, warto rozważyć rozmieszczenie operacji importu zbiorczego w wielu wsadach. Każdy wsad sprowadza się do jednej transakcji. A zatem gdy wykonanie wsadu zostaje zakończone, możliwe jest stworzenie kopii zapasowej jego dziennika. Stworzenie kopii zapasowej kolejnego dziennika spowoduje odzyskanie obszaru dziennika wykorzystywanego do zaimportowania zbiorczego tego wsadu wierszy.

 Do początku strony Do początku strony

Importowanie danych równoległe z blokowaniem na poziomie tabeli

Microsoft SQL Server umożliwia wielu aplikacjom klienckim równoległe zbiorcze importowanie danych do jednej tabeli nieposiadającej indeksów. Mechanizm ten pozwala poprawić wydajność operacji importu zbiorczego. Równoległe importowanie danych jest wspierane przez wszystkie trzy polecenia importu zbiorczego: bcp , BULK INSERT oraz INSERT ... SELECT * FROM OPENROWSET (BULK...).

Uwaga:

Tylko aplikacje, które wykorzystują bazujące na ODBC lub SQL OLE DB interfejsy API mogą realizować równoległe ładowanie danych do pojedynczej tabeli. Aplikacje (także narzędzie bcp) bazujące na bibliotece klienckiej DB-Library dostarczanej z wersją Microsoft SQL Server  6.  5 lub wcześniejszą nie mogą uczestniczyć w równoległym ładowaniu danych na instancję SQL Server.

Jeśli opcja/wskazówka TABLOCK nie zostanie określona, wiele konkurencyjnych strumieni importu zbiorczego nałoży blokady na poziomie bardziej szczegółowym niż poziom tabeli. W zależności od rozmieszczenia danych konkurencyjne strumienie ładowania zbiorczego mogą blokować się wzajemnie. Z tego względu równoległe importowanie zbiorcze danych do tabeli bez indeksów jest zazwyczaj realizowane z wykorzystaniem kwalifikatora TABLOCK.

Importując zbiorczo dane na instancję SQL Server w sposób równoległy z wykorzystaniem opcji TABLOCK, warto wziąć pod uwagę następujące aspekty:

  •  Najprostszym scenariuszem równoległego importowania danych jest ładowanie danych do tabeli bez indeksów (sterty).

  •  Jeśli tabela nie posiada indeksów, należy określić opcję TABLOCK dla operacji importu zbiorczego.

Uwaga:

Gdy tabela posiada indeksy, nie można wykonać równoległych operacji ładowania przy użyciu opcji TABLOCK. To powoduje, że współbieżne wątki mogą blokować się nawzajem. Przed operacją importu zbiorczego warto rozważyć usunięcie indeksów z tabeli.

  •  Warto podzielić dane tak, aby były one importowane z różnych aplikacji klienckich, przy czym liczba plików danych powinna być równa liczbie tych aplikacji. Należy umieścić po jednym pliku w każdej aplikacji klienckiej. Najlepsze rozwiązanie Aby jak najefektywniej wykorzystywać zasoby procesora, należy rozdzielić dane po równo między aplikacjami klienckimi. Warto upewnić się, że pliki danych mają podobny rozmiar, gdy chce się importować je równolegle z wielu aplikacji klienckich na instancję

SQL Server. W przeciwnym wypadku wątek mało obciążonej aplikacji klienckiej może zostać zakończony szybciej, a zatem procesor tej aplikacji klienckiej zostanie wykorzystany nieefektywnie.

  •  Aby osiągnięta została maksymalna wydajność, rozmiar wsadu określony dla każdej aplikacji klienckiej powinien być taki sam jak rozmiar jej pliku danych. Po zbiorczym zaimportowaniu danych do tabeli można stworzyć dowolne wymagane indeksy w sposób następujący:

  1.  Stworzyć każdy indeks klastrowany po kolei z pojedynczej aplikacji klienckiej..

  2.  Stworzyć indeksy nieklastrowane. Można tworzyć je współbieżnie z różnych aplikacji klienckich.

Jeśli opcja TABLOCK nie jest stosowana, równoległy zbiorczy import danych jest możliwy niezależnie od liczby indeksów w tabeli docelowej. Jednak w tym przypadku nie są możliwe optymalizacje zbiorcze i mogą wystąpić problemy z wzajemnym blokowaniem się w przypadku nakładania blokad na poszczególne wiersze lub strony.

 Do początku strony Do początku strony

Zarządzanie wsadami importu zbiorczego

W niniejszym artykule zaprezentowano proces zarządzania sposobem, w jaki określany jest rozmiar wsadu dla operacji importu zbiorczego. Domyślnie wszystkie wiersze w pliku danych są importowane jako jeden wsad o nieznanym rozmiarze w pojedynczej transakcji. Takie rozwiązanie powoduje, że jeśli przed zakończeniem operacji importu wystąpi błąd, cała transakcja zostanie wycofana i do tabeli docelowej nie zostaną dodane żadne dane. Nieudana operacja będzie musiała zostać uruchomiona ponownie, od początku pliku danych.

Importowanie dużego pliku danych przy użyciu jednego wsadu może być problematyczne, dlatego bcp oraz BULK INSERT pozwalają importować dane w ramach serii wsadów mniejszych niż plik danych. Każdy wsad jest importowany i rejestrowany w ramach osobnej transakcji i po zatwierdzeniu określonej transakcji wiersze przez nią zaimportowane również zostają zatwierdzone. Jeśli operacja nie powiedzie się, tylko wiersze importowane z aktualnego wsadu są wycofywane i można powrócić do importowania danych począwszy od miejsca, w którym rozpoczął się zakończony niepowodzeniem wsad zamiast od początku pliku danych.

Alternatywnie, gdy nie chce się ograniczać rozmiaru wsadu, można poprawić wydajność poprzez oszacowanie rozmiaru pliku danych w poleceniu importu. Oszacowanie to jest wykorzystywane przez procesor kwerend podczas tworzenia planu wykonania kwerendy dla operacji.

Uwaga:

Podczas określania rozmiaru wsadu lub pliku danych dokładność jest ważna, ale nie krytyczna.

Następująca tabela przedstawia kwalifikatory, które wspierają poszczególne metody.

Polecenie Rozmiar wsadu Liczba wierszy wysłanych/wsad Liczba kilobajtów wysłanych/wsad
bcp 1 -b rozmiar_wsadu -h "ROWS_PER_BATCH = bb" -h "KILOBYTES_PER_BATCH = cc"
BULK INSERT2 BATCHSIZE = rozmiar_wsadu ROWS_PER_BATCH = wierszy_per_wsad

KILOBYTES_PER_BATCH = kilobajtow_per_wsad

Uwaga:

Wykorzystanie tej opcji z BATCHSIZE spowoduje wygenerowanie błędu.

INSERT ... SELECT * FROM OPENROWSET (BULK...) —3 ROWS_PER_BATCH = wierszy_per_wsad —3

1 W poleceniu bcp nie należy wykorzystywać przełącznika -brozmiar_wsadu razem ze wskazówką ROWS_PER_BATCH lub KILOBYTES_PER_BATCH. Takie połączenie spowoduje wygenerowanie błędu.

2 W poleceniu BULK INSERT opcja BATCHSIZE ma wyższy priorytet, gdy jest stosowana razem ze wskazówką ROWS_PER_BATCH lub KILOBYTES_PER_BATCH.

3 OPENROWSET nie posiada opcji BATCHSIZE ani KILOBYTES_PER_BATCH.

Następujące sekcje zawierają omówienia zastosowań wspomnianych kwalifikatorów.

Określenie przybliżonego rozmiaru wsadu

W przypadku importowania bardzo dużej liczby wierszy podzielenie danych na wsady może przynieść korzyści. Po wykonaniu każdego wsadu następuje zarejestrowanie transakcji. Jeśli z jakiegoś względu operacja importu zbiorczego zostanie przerwana przed końcem, wycofywana jest jedynie aktualna transakcja (wsad).

Uwaga:

Rejestrowanie zbiorcze obejmuje kopiowanie zaimportowanych danych do dziennika transakcji. To może powodować szybki przyrost rozmiaru dziennika, lecz po każdym wsadzie można stworzyć kopię zapasową dziennika, aby odzyskać dany obszar.

Aby zaimportować plik danych w ramach serii wsadów o określonej przybliżonej wartości, należy użyć następującego kwalifikatora:

  •  Dla bcp : -b

  •  Dla BULK INSERT: BATCHSIZE

Każdy wsad wierszy jest wstawiany w ramach osobnej transakcji. Jeśli z jakichkolwiek względów operacja importu zbiorczego zostanie przerwana przed końcem, jedynie bieżąca transakcja będzie musiała zostać wycofana. Na przykład, jeśli plik danych zawiera 1000 wierszy i wykorzystywany jest rozmiar wsadu 100, Microsoft SQL Server zarejestruje operacje w postaci dziesięciu osobnych transakcji, każda transakcja wstawi 100 wierszy do tabeli docelowej. Jeśli operacja importu zbiorczego zostanie przerwana podczas ładowania 750 wiersza, tylko ostatnich 49 wierszy zostanie usuniętych w ramach wycofywania przez SQL Server bieżącej transakcji. Tabela docelowa nadal zawierać będzie pierwsze 700 wierszy.

SQL Server automatycznie optymalizuje proces ładowania w zależności od rozmiaru wsadu, co może wpłynąć na poprawę wydajności. Zasadniczo należy określać jak największy użyteczny rozmiar wsadu. Z reguły im większy rozmiar wsadu tym lepsza wydajność operacji importu zbiorczego. Jednak reguła ta ma kilka wyjątków. Jeśli w tabeli docelowej istnieje jeden lub więcej indeksów, większy rozmiar wsadu może obciążyć pamięć podczas sortowania. Ponadto podczas równoległego ładowania wykonywanego bez użycia opcji TABLOCK, większy rozmiar wsadu może prowadzić do nasilonego blokowania.

Uwaga:

Rozmiary wsadów nie mogą zostać zastosowane podczas zbiorczego eksportu danych z instancji SQL Server do pliku danych.

Określenie przybliżonego rozmiaru pliku danych

Jeśli nie określa się rozmiaru wsadu dla operacji importu zbiorczego, można wskazać przybliżony rozmiar pliku danych, aby umożliwić procesorowi kwerend efektywną alokację zasobów w planie kwerendy. Aby określić przybliżony rozmiar pliku danych, można oszacować liczbę wierszy lub liczbę kilobajtów danych w sposób następujący:

  •  Szacowanie liczby wierszy per wsad Do oszacowania liczby wierszy należy użyć wskazówki lub opcji ROWS_PER_BATCH. W przypadku określenia wartości > 0 procesor kwerend wykorzysta wartość ROWS_PER_BATCH jako wskazówkę dotyczącą alokowania zasobów w planie kwerendy. Wartość ta powinna być w przybliżeniu równa rzeczywistej liczbie wierszy. Chociaż wszystkie wiersze z pliku danych są kopiowane na instancję SQL Server w ramach jednego wsadu, po każdym tysiącu wierszy narzędzie bcp wyświetla komunikat "1000 rows sent to SQL Server" (1000 wierszy wysłanych na SQL Server). Komunikat ten ma charakter wyłącznie informacyjny i pojawia się niezależnie od rozmiaru wsadu.

  •  Szacowanie liczby kilobajtów per wsad Do oszacowania rozmiaru pliku danych w kilobajtach należy użyć wskazówki lub opcji KILOBYTES_PER_BATCH. SQL Server optymalizuje operację importu zbiorczego w zależności od podanej wartości.

Uwaga:

Gdy duży plik danych jest importowany zbiorczo bez określenia rozmiaru wsadu bądź zastosowania optymalizacji rejestrowania zbiorczego, dziennik transakcji może zostać wypełniony przed zakończeniem operacji importu zbiorczego. Aby uniknąć takiej sytuacji, można zwiększyć rozmiar dziennika transakcji lub zezwolić na automatyczne zwiększanie rozmiaru.

 Do początku strony Do początku strony

Kontrolowanie wykonania wyzwalaczy podczas importu zbiorczego danych

Wyzwalacz (ang. trigger) to specjalny format procedury składowanej, który jest wykonywany automatycznie, gdy użytkownik zmodyfikuje dane w tabeli lub widoku. Wykonywanie wyzwalaczy może wpłynąć na wydajność operacji importu zbiorczego. Na przykład wyzwalacz wysyłający wiadomość e-mail za każdym razem, gdy importowany jest rekord, znacznie obniża tempo realizacji operacji importu zbiorczego i tworzy zatrzęsienie e-maili.

Podczas importowania zbiorczego danych można kontrolować, czy wyzwalacze mają być uruchamiane (ang. fire) przez operację importu zbiorczego. Operacja importu zbiorczego powinna wykonywać wyzwalacze tylko dla tabeli z wyzwalaczami INSERT oraz INSTEAD OF, które wspierają wstawianie wielu wierszy.

Ważne:

Gdy wyzwalacze są wyłączone, możliwe jest zastosowanie blokady modyfikacji schematu w celu aktualizacji metadanych. To może kolidować z innymi poleceniami (takimi jak budowanie indeksów online) lub transakcjami. Na przykład transakcja z izolacją migawki uzyskująca dostęp do tabeli docelowej może zakończyć się niepowodzeniem z powodu współbieżnych modyfikacji DDL.

Jeśli wyzwalacze są włączone, są one wykonywane jednokrotnie dla każdego wsadu.

Podczas operacji importu zbiorczego mechanizm działania zależy od polecenia wykorzystywanego w danej operacji. Domyślnie polecenie bcp oraz instrukcja BULK INSERT (Transact-SQL) wyłączają wyzwalacze. Natomiast w instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...) wyzwalacze są domyślnie wykonywane.

Następująca tabela prezentuje zestawienie domyślnych mechanizmów działania.

Polecenie importu zbiorczego Domyślne działanie
bcp Wyłącza wyzwalacze
BULK INSERT Wyłącza wyzwalacze
INSERT ... SELECT * FROM OPENROWSET(BULK...) Wykonuje wyzwalacze

Każde z poleceń importu zbiorczego oferuje kwalifikatory, które umożliwiają zmianę sposobu obsługi wyzwalaczy, co zostało opisane w poniższych sekcjach.

Wykonywanie wyzwalaczy w ramach polecenia bcp lub BULK INSERT

Domyślnie polecenie bcp oraz instrukcja BULK INSERT nie wykonują wyzwalaczy. Jednak można włączyć wyzwalacze przy użyciu następujących kwalifikatorów:

Polecenie Kwalifikator Typ kwalifikatora
bcp -h "FIRE_TRIGGERS" Wskazówka
BULK INSERT FIRE_TRIGGERS Argument

Jeśli dla operacji importu zbiorczego określony jest kwalifikator FIRE_TRIGGERS, operacja wykonuje wszystkie zdefiniowane dla tabeli wyzwalacze INSERT oraz INSTEAD OF na wszystkich wstawianych do tabeli wierszach.

Wyłączanie wyzwalaczy w poleceniu INSERT ... SELECT * FROM OPENROWSET(BULK...)

Domyślnie dostawca zestawu wierszy zbiorczych dla funkcji OPENROWSET wykonuje wyzwalacze. Jednak można wyłączyć wyzwalacze przy użyciu następującego kwalifikatora:

Polecenie Kwalifikator Typ kwalifikatora
INSERT ... SELECT * FROM OPENROWSET(BULK...) WITH(IGNORE_TRIGGERS) Wskazówka tabeli

Jeśli określona jest powyższa wskazówka, wyzwalacze nie są wykonywane przez dostawcę zestawu wierszy zbiorczych dla funkcji OPENROWSET.

Importowanie dużych wsadów z włączonymi wyzwalaczami

SQL Server 2005 oraz wersje późniejsze wykorzystują kontrolę wersji dla wyzwalaczy i składują wersje wierszy w magazynie wersji w bazie danych tempdb. Przed zaimportowaniem zbiorczym dużego wsadu rekordów danych z wykorzystaniem wyzwalaczy, być może trzeba będzie zwiększyć rozmiar bazy danych tempdb, aby dostosować go do potencjalnego wpływu wyzwalaczy na magazyn wersji.

 Do początku strony Do początku strony

Kontrolowanie sprawdzania ograniczeń przez operacje importu zbiorczego

Dokonując zbiorczego importu danych, można kontrolować, czy operacja importu zbiorczego wymuszać będzie ograniczenia CHECK. Ograniczenie to reguła biznesowa, która jest umieszczana w kolumnie w celu zdefiniowania jej prawidłowych wartości. Na przykład ograniczenie dla kolumny zawierającej numery wewnętrzne telefonów może wymagać postaci ####. Definiowanie i sprawdzanie ograniczeń stanowi standardowy mechanizm wymuszania integralności danych. Firma Microsoft rekomenduje, aby zwykle stosować sprawdzanie ograniczeń podczas przyrostowych importów zbiorczych.

Czasem pojawia się jednak potrzeba ignorowania ograniczeń. Na przykład w sytuacji, gdy dane wejściowe zawierają wiersze, które naruszają ograniczenia. Zignorowanie ograniczeń pozwala pobrać dane, a następnie uporządkować je przy pomocy instrukcji Transact-SQL.

Uwaga:

Gdy podczas operacji importu zbiorczego ignorowane są ograniczenia, możliwe jest wstawienie do tabeli danych, które naruszają istniejące ograniczenia. W związku z tym w widoku katalogowym sys.check_constraints ograniczenie na tabeli zostaje oznaczone jako is_not_trusted (więcej informacji znaleźć można w artykule sys.check_constraints (Transact-SQL) ). Na pewnym etapie trzeba będzie sprawdzić ograniczenia w całej tabeli.

Jeśli tabela nie była pusta przed wykonaniem operacji importu zbiorczego, koszt ponownego sprawdzenia ograniczeń może przekroczyć koszt zastosowania ograniczeń CHECK do danych przyrostowych. Po zaimportowaniu problematycznych danych trzeba wykorzystać instrukcje Transact-SQL do uporządkowania zaimportowanych danych.

Ważne:

Gdy ograniczenia są wyłączone, możliwe jest zastosowanie blokady modyfikacji schematu w celu aktualizacji metadanych. To może kolidować z innymi poleceniami (takimi jak budowanie indeksów online) lub transakcjami. Na przykład transakcja z izolacją migawki uzyskująca dostęp do tabeli docelowej może zakończyć się niepowodzeniem z powodu współbieżnych modyfikacji DDL.

Podczas operacji importu zbiorczego mechanizm sprawdzania ograniczeń zależy od wykorzystanego polecenia. Domyślnie polecenie bcp oraz instrukcja BULK INSERT ignorują ograniczenia. Natomiast domyślnym zachowaniem instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...) jest sprawdzanie ograniczeń.

Następująca tabela prezentuje domyślny mechanizm sprawdzania ograniczeń dla poleceń importu zbiorczego.

Polecenie Domyślne zachowanie
bcp Ignoruje ograniczenia
BULK INSERT Ignoruje ograniczenia
INSERT ... SELECT * FROM OPENROWSET(BULK...) Sprawdza ograniczenia

Każde polecenie importu zbiorczego dostarcza kwalifikator, który pozwala zmienić sposób obsługi ograniczeń, co zostało opisane w następujących sekcjach.

Sprawdzanie ograniczeń w poleceniu bcp lub BULK INSERT

Domyślnie ograniczenia są ignorowane podczas operacji importu zbiorczego realizowanej przy użyciu polecenia bcp lub instrukcji BULK INSERT.

Polecenie bcp oraz instrukcja BULK INSERT umożliwiają określenie, że podczas operacji importu zbiorczego mają być wymuszane ograniczenia. Wymuszanie ograniczeń powoduje spowolnienie operacji importu zbiorczego, ale gwarantuje, że wstawiane dane nie będą naruszały żadnych istniejących ograniczeń. Następująca tabela prezentuje kwalifikatory, których można użyć w celu ustawienia wymuszania ograniczeń podczas operacji importu zbiorczego.

Polecenie Kwalifikator Typ kwalifikatora
bcp -h "CHECK_CONSTRAINTS" Wskazówka
BULK INSERT CHECK_CONSTRAINTS Argument

Ignorowanie ograniczeń w poleceniu INSERT ... SELECT * FROM OPENROWSET(BULK...)

Domyślnie operacja INSERT sprawdza ograniczenia CHECK, jednak polecenie INSERT ... SELECT * FROM OPENROWSET(BULK...) umożliwia nadpisanie sprawdzania ograniczeń CHECK.

Uwaga:

Tylko ograniczenia CHECK mogą zostać wyłączone. Nie można wyłączyć ograniczeń UNIQUE, PRIMARY KEY, FOREIGN KEY bądź NOT NULL.

Następująca tabela prezentuje wskazówkę tabeli służącą do ignorowania ograniczeń CHECK.

Polecenie Kwalifikator Typ kwalifikatora
INSERT ... SELECT * FROM OPENROWSET(BULK...) WITH (IGNORE_CONSTRAINTS) Wskazówka tabeli

Sprawdzanie, czy dane zostały zaimportowane

Jeśli podczas operacji importu zbiorczego ignoruje się ograniczenia, można w późniejszym czasie ustalić, które zaimportowane wiersze tabeli naruszają ograniczenia, własnoręcznie sprawdzając zaimportowane dane. Do własnoręcznego sprawdzenia danych można wykorzystać kwerendy Transact-SQL lub procedury składowane, które testują warunki ograniczeń.

Uwaga:

Aby dowiedzieć się, czy tabela jest wiarygodna, można zajrzeć do kolumny is_not_trusted w widoku katalogowym sys.check_constraints.

 Do początku strony Do początku strony

Kontrolowanie porządku sortowania podczas importu zbiorczego danych

Domyślnie operacja importu zbiorczego przyjmuje założenie, że dane w pliku są nieposortowane. Jeśli tabela posiada indeks klastrowany, polecenie bcp, instrukcja BULK INSERT (Transact-SQL) oraz funkcja OPENROWSET(BULK…) (Transact-SQL) w czasie operacji importu zbiorczego umożliwiają określanie, w jaki sposób posortowane są dane w pliku danych. Sortowanie danych w pliku danych w takim samym porządku jak w tabeli jest opcjonalne. Jednak określenie tego samego porządku pozwala przyspieszyć operację importu zbiorczego.

Kwalifikatory poleceń

Następująca tabela prezentuje kwalifikatory poleceń służące do określania porządku sortowania dla operacji importu zbiorczego.

Polecenie Kwalifikator Typ kwalifikatora
bcp

-h "ORDER( kolumna [ ASC

DESC ] [ ,...n ] )"

Wskazówka
BULK INSERT

ORDER ( { kolumna [ ASC

DESC ] } [ ,...n ] ) ]

Argument
OPENROWSET(BULK…) ORDER ( { kolumna [ ASC | DESC ] } [ ,...n ] [ UNIQUE ] ) Wskazówka

Najlepsze rozwiązanie

Porządek danych w tabeli jest zależny od indeksu klastrowanego. Aby poprawić wydajność importu zbiorczego w sytuacji, gdy tabela zawiera indeks klastrowany, kolumny wymienione we wskazówce ORDER lub klauzuli ORDER powinny być takie same i mieć taką samą kolejność co kolumny w indeksie klastrowanym.

 Do początku strony Do początku strony

Kontrolowanie mechanizmu blokowania w imporcie zbiorczym

Polecenie bcp, instrukcja BULK INSERT oraz instrukcja INSERT ... SELECT * FROM OPENROWSET(BULK...) umożliwiają określenie, że tabela ma być blokowana podczas operacji importu zbiorczego.

Gdy określone jest blokowanie tabeli, na czas realizacji operacji importu zbiorczego nakładana jest blokada aktualizacji zbiorczej na poziomie tabeli. Blokowanie tabeli pozwala poprawić wydajność operacji importu zbiorczego, redukując kolizje.

Jeśli blokowanie tabeli nie jest wykorzystywane, domyślnie stosowane są blokady na poziomie wierszy, chyba że opcja table lock on bulk load jest ustawiona na on. Ustawienie opcji table lock on bulk load przy użyciu procedury sp_tableoption pozwala zdeterminować mechanizm blokowania na czas realizacji operacji importu zbiorczego.

Opcja blokady tabeli w imporcie zbiorczym Mechanizm blokowania
Wyłączona Wykorzystanie blokad na poziomie wierszy
Włączona Wykorzystanie blokad na poziomie tabeli

Jeśli określone jest blokowanie na poziomie tabeli, domyślne ustawienie dla tabeli określone przy pomocy procedury sp_tableoption jest nadpisywane na czas trwania operacji importu zbiorczego.

Uwaga:

Określenie blokowania na poziomie tabeli nie jest konieczne do realizacji zbiorczego importu danych do tabeli równolegle z wielu aplikacji klienckich, ale pomaga ono poprawić wydajność.

Kwalifikatory poleceń

Następująca tabela prezentuje kwalifikatory służące do określania blokowania na poziomie tabeli w poleceniach importu zbiorczego.

Polecenie Kwalifikator Typ kwalifikatora
bcp -h "TABLOCK" Wskazówka
BULK INSERT TABLOCK Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...) WITH(TABLOCK) Wskazówka tabeli

Uwaga:

W SQL Server 2005 oraz wersjach późniejszych określenie kwalifikatora TABLOCK dla tabeli z indeksem klastrowanym uniemożliwia zbiorcze importowanie danych w sposób równoległy. Gdy istnieje potrzeba realizacji równoległego importu zbiorczego, nie należy stosować kwalifikatora TABLOCK.

 Do początku strony Do początku strony

Wykorzystanie formatu macierzystego do importowania lub eksportowania danych

Format macierzysty jest zalecany w przypadku przenoszenia zbiorczego danych między wieloma instancjami Microsoft SQL Server przy użyciu pliku danych, który nie zawiera żadnych znaków DBCS (pochodzących z rozszerzonego/dwubajtowego zestawu znaków).

Uwaga:

Aby zbiorczo przenosić dane między wieloma instancjami SQL Server z wykorzystaniem pliku danych, który zawiera znaki rozszerzone lub DBCS, należy zastosować format macierzysty Unicode.

Format macierzysty obejmuje macierzyste typy danych bazy danych. Format macierzysty został zaprojektowany z myślą o szybkich transferach danych między tabelami SQL Server. Gdy wykorzystywany jest plik formatu, tabele źródłowa i docelowa nie muszą być identyczne. Transfer danych składa się z dwóch etapów:

  1.  Zbiorczy eksport danych z tabeli źródłowej do pliku danych

  2.  Zbiorczy import danych z pliku danych do tabeli docelowej

Zastosowanie formatu macierzystego między identycznymi tabelami pozwala zapobiec niepotrzebnym konwersjom typów danych z oraz do formatu znaków, oszczędzając tym samym czas i miejsce. Jednak w celu osiągnięcia optymalnej szybkości transferu należy przeprowadzić pewne kontrole dotyczące formatowania danych. Aby uniknąć problemów z pobranymi danymi, warto zapoznać się z następującą listą restrykcji.

Restrykcje

Aby pomyślnie zaimportować dane w formacie macierzystym, należy sprawdzić czy:

  •  Plik danych znajduje się w formacie macierzystym.

  •  Tabela docelowa jest zgodna z plikiem danych (musi zawierać odpowiednią liczbę kolumn, typ danych, długość, status NULL itd.). W przeciwnym przypadku trzeba użyć pliku formatu w celu zmapowania każdego pola do odpowiadających mu kolumn.

Uwaga:

Operacja importowania danych z pliku, który nie jest dopasowany do tabeli docelowej, może zakończyć się powodzeniem, ale wartości danych wstawione do tabeli docelowej będą z dużym prawdopodobieństwem nieprawidłowe. Wynika to z tego, że dane z pliku są interpretowane przy użyciu formatu tabeli docelowej. A zatem wszelkie niezgodności będą skutkowały wstawieniem nieprawidłowych wartości. Jednak pod żadnym pozorem taka niezgodność nie może powodować logicznej lub fizycznej niespójności bazy danych.

Pomyślny import nie spowoduje uszkodzenia tabeli docelowej.

W jaki sposób narzędzie bcp obsługuje dane w formacie macierzystym

Niniejsza sekcja zajmuje się omówieniem wybranych aspektów związanych ze sposobem, w jaki narzędzie bcp eksportuje oraz importuje dane w formacie macierzystym.

  •  Dane nieznakowe Narzędzie bcp wykorzystuje wewnętrzny format danych binarnych SQL Server do zapisywania danych nieznakowych z tabeli do pliku danych.

  •  Dane char lub varchar Na początku każdego pola char lub varchar narzędzie bcp dodaje prefiks długości.

Ważne:

Gdy wykorzystywany jest tryb macierzysty, domyślnie narzędzie bcp konwertuje znaki z SQL Server do znaków OEM przed skopiowaniem ich do pliku danych. Narzędzie bcp konwertuje znaki z pliku danych do znaków ANSI przed zbiorczym zaimportowaniem ich do tabeli SQL Server. Podczas tych konwersji, rozszerzone dane znakowe mogą zostać utracone. W przypadku znaków rozszerzonych należy wykorzystać format macierzysty Unicode lub określić stronę kodową. Więcej informacji na temat zastosowania strony kodowej znaleźć można w artykule Copying Data Between Different Collations .

  •  Dane sql_variant Jeśli dane sql_variant są składowane w postaci SQLVARIANT w pliku danych w formacie macierzystym, zachowują wszystkie swoje cechy charakterystyczne. Metadane, które określają typ danych dla każdej wartości danych, są składowane wraz z wartościami danych. Te metadane służą do odtwarzania wartości danych przy użyciu tego samego typu danych w docelowej kolumnie sql_variant. Jeśli typem danych docelowej kolumny nie jest sql_variant, wszystkie wartości danych są konwertowane do typu danych kolumny docelowej zgodnie ze standardowymi regułami niejawnych konwersji danych. Jeśli podczas konwersji danych wystąpi błąd, bieżący wsad jest wycofywany.

Wszelkie wartości char oraz varchar przenoszone między kolumnami sql_variant mogą wiązać się z problemami w konwersji pomiędzy stronami kodowymi.

Opcje poleceń dla formatu macierzystego

Można importować dane w formacie macierzystym, korzystając z poleceń bcp, BULK INSERT lub INSERT ... SELECT * FROM OPENROWSET(BULK...). W przypadku polecenia bcp lub instrukcji BULK INSERT można określić format danych w wierszu polecenia. Natomiast w przypadku instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...) format danych trzeba określić w pliku formatu.

Format macierzysty jest wspierany przez następujące opcje wiersza polecenia:

Polecenie Opcja Opis
bcp -n Powoduje, że narzędzie bcp wykorzystuje macierzyste typy danych.1
BULK INSERT DATAFILETYPE = ' native ' Wykorzystuje typy danych „native” lub „wide native”. Warto wiedzieć, że opcja DATAFILETYPE nie jest potrzebna, gdy plik formatu określa typy danych.

1 Aby pobrać dane macierzyste (-n) do formatu zgodnego z wcześniejszymi wersjami aplikacji klienckich SQL Server, należy użyć przełącznika -V.

Uwaga:

Można także określić formatowanie dla każdego pola w pliku formatu. Więcej informacji znaleźć można w artykule Format Files for Importing or Exporting Data .

Przykłady

Następujące przykłady prezentują, w jaki sposób można zbiorczo wyeksportować dane macierzyste przy użyciu narzędzia bcp oraz zbiorczo zaimportować te dane przy pomocy instrukcji BULK INSERT.

Przykładowa tabela

W przykładach przyjęto założenie o istnieniu tabeli o nazwie myTestNativeData w przykładowej bazie danych AdventureWorks w schemacie dbo. Dlatego przed uruchomieniem przykładów, trzeba stworzyć tę tabelę. W tym celu w Edytorze kwerend w SQL Server Management Studio należy wykonać następujące instrukcje:

USE AdventureWorks;

GO

CREATE TABLE myTestNativeData (

Col1 smallint,

Col2 nvarchar(50),

Col3 nvarchar(50)

);

Aby wypełnić tę tabelę i wyświetlić wynikową zawartość, należy wykonać następujące instrukcje:

INSERT INTO myTestNativeData(Col1,Col2,Col3)

VALUES(1,'DataField2','DataField3');

INSERT INTO myTestNativeData(Col1,Col2,Col3)

VALUES(2,'DataField2','DataField3');

GO

SELECT Col1,Col2,Col3 FROM myTestNativeData

Wykorzystanie polecenia bcp do zbiorczego eksportu danych macierzystych

Aby wyeksportować dane z tabeli do pliku danych, należy użyć polecenia bcp z opcją out oraz następujących kwalifikatorów:

KwalifikatoryOpis
-NOkreśla macierzyste typy danych.
-TPowoduje, że narzędzie bcp łączy się z SQL Server używając zaufanego połączenia i zintegrowanych zabezpieczeń. Jeśli opcja -T nie jest określona, aby pomyślnie się zalogować, trzeba dodać opcje -U oraz -P.

W następującym przykładzie dane w formacie macierzystym zostaną zbiorczo wyeksportowane z tabeli myTestNativeData do nowego pliku danych o nazwie myTestNativeData-n.Dat. W tym celu w wierszu polecenia systemu Microsoft Windows należy wpisać:

bcp AdventureWorks..myTestNativeData out C:\myTestNativeData-n.Dat -n -T

Wykorzystanie instrukcji BULK INSERT do zbiorczego importu danych macierzystych

W następującym przykładzie instrukcja BULK INSERT jest wykorzystywana do importu danych z pliku danych myTestNativeData-n.Dat do tabeli myTestNativeData. W tym celu w Edytorze kwerend w narzędziu SQL Server Management Studio należy wykonać następujące instrukcje:

USE AdventureWorks;

GO

BULK INSERT myTestNativeData

FROM 'C:\myTestNativeData-n.Dat'

WITH (DATAFILETYPE='native');

GO

SELECT Col1,Col2,Col3 FROM myTestNativeData

 Do początku strony Do początku strony

Wykorzystanie formatu macierzystego Unicode do importowania lub eksportowania danych

Format macierzysty Unicode jest pomocny, gdy informacje muszą być kopiowane z jednej instalacji Microsoft SQL Server do innej. Zastosowanie formatu macierzystego dla danych nieznakowych pozwala oszczędzić czas, eliminując niepotrzebne konwersje typów danych do i z formatu znakowego. Zastosowanie formatu znakowego Unicode dla wszystkich danych znakowych pozwala zapobiec utracie znaków rozszerzonych podczas zbiorczego importu danych między serwerami wykorzystującymi różne strony kodowe. Plik danych w formacie macierzystym Unicode może być odczytywany przez dowolną metodę importu zbiorczego.

Format macierzysty Unicode jest zalecany w przypadku zbiorczych importów danych między wieloma instancjami SQL Server z wykorzystaniem pliku danych, który zawiera znaki rozszerzone lub DBCS. W przypadku danych nieznakowych format macierzysty Unicode wykorzystuje macierzyste (bazodanowe) typy danych. Natomiast w przypadku danych znakowych, takich jak char, nchar, varchar, nvarchar, text, varchar(max), nvarchar(max) oraz ntext wykorzystuje format danych znakowych Unicode.

Dane sql_variant, które są składowane w postaci SQLVARIANT w pliku danych w formacie macierzystym Unicode, zachowują się w ten sam sposób, w jaki zachowują się w pliku danych w formacie macierzystym – z tą różnicą, że wartości char oraz varchar są konwertowane do nchar oraz nvarchar, co powoduje podwojenie ilości miejsca zajmowanego przez stosowne kolumny. Oryginalne metadane zostają zachowane, a wartości są konwertowane z powrotem do oryginalnego typ danych char oraz varchar po zbiorczym zaimportowaniu ich do kolumny tabeli.

Uwaga:

Więcej informacji na temat formatu macierzystego znaleźć można w artykule Wykorzystanie formatu macierzystego do importowania lub eksportowania danych .

Opcje poleceń dla formatu macierzystego Unicode

Dane w formacie macierzystym Unicode można importować do tabeli przy użyciu poleceń bcp, BULK INSERT lub INSERT ... SELECT * FROM OPENROWSET(BULK...). W przypadku polecenia bcp lub instrukcji BULK INSERT można określić format danych w wierszu polecenia. Natomiast w przypadku instrukcji INSERT ... SELECT * FROM OPENROWSET(BULK...) format danych należy określić w pliku formatu.

Następujące opcje zapewniają wsparcie dla formatu macierzystego Unicode:

PolecenieOpcjaOpis
bcp-NPowoduje, że narzędzie bcp stosuje format macierzysty Unicode, który wykorzystuje macierzyste (bazodanowe) typy danych dla wszystkich danych nieznakowych oraz format danych znakowych Unicode dla wszystkich danych znakowych (char, nchar, varchar, nvarchar, text oraz ntext).
BULK INSERTDATAFILETYPE = ' widenative 'Stosuje format macierzysty Unicode podczas zbiorczego importu danych.
Uwaga:  
Można także określić formatowanie osobno dla każdego pola w pliku formatu.  

Przykłady

Następujące przykłady prezentują, w jaki sposób można zbiorczo wyeksportować dane macierzyste przy użyciu narzędzia bcp oraz zbiorczo zaimportować te dane przy pomocy instrukcji BULK INSERT.

Przykładowa tabela

W przykładach przyjęto założenie o istnieniu tabeli o nazwie myTestUniNativeData w przykładowej bazie danych AdventureWorks w schemacie dbo. Dlatego przed uruchomieniem przykładów, trzeba stworzyć tę tabelę. W Edytorze kwerend w SQL Server Management Studio należy wykonać następujące instrukcje:

USE AdventureWorks;

GO

CREATE TABLE myTestUniNativeData (

Col1 smallint,

Col2 nvarchar(50),

Col3 nvarchar(50)

);

Aby wypełnić tę tabelę i wyświetlić wynikową zawartość, należy wykonać następujące instrukcje:

INSERT INTO myTestUniNativeData(Col1,Col2,Col3)

VALUES(1,'DataField2','DataField3');

INSERT INTO myTestUniNativeData(Col1,Col2,Col3)

VALUES(2,'DataField2','DataField3');

GO

SELECT Col1,Col2,Col3 FROM myTestUniNativeData

Wykorzystanie polecenia bcp do zbiorczego eksportu danych macierzystych

Aby wyeksportować dane z tabeli do pliku danych, należy użyć polecenia bcp z opcją out oraz następujących kwalifikatorów:

KwalifikatorOpis
-nOkreśla macierzyste typy danych.
-TPowoduje, że narzędzie bcp łączy się z SQL Server, używając zaufanego połączenia i zintegrowanych zabezpieczeń. Jeśli opcja -T nie jest określona, aby pomyślnie się zalogować, trzeba dodać opcje -U oraz -P.

Następujący przykład prezentuje eksport zbiorczy danych w formacie macierzystym z tabeli myTestUniNativeData do nowego pliku danych o nazwie myTestUniNativeData-N.Dat. W wierszu polecenia systemu Microsoft Windows należy wpisać:

bcp AdventureWorks..myTestUniNativeData out C:\myTestUniNativeData-N.Dat -N -T

Wykorzystanie instrukcji BULK INSERT do zbiorczego importu danych macierzystych

Następujący przykład prezentuje wykorzystanie instrukcji BULK INSERT do zaimportowania danych z pliku danych myTestUniNativeData-N.Dat do tabeli myTestUniNativeData. W Edytorze kwerend w narzędziu SQL Server Management Studio należy wykonać następujące instrukcje:

USE AdventureWorks; 

GO 

BULK INSERT myTestUniNativeData  

    FROM 'C:\myTestUniNativeData-N.Dat'  

   WITH (DATAFILETYPE='widenative');  

GO 

SELECT Col1,Col2,Col3 FROM myTestUniNativeData; 

GO

 

 Do początku strony Do początku strony

Microsoft SQL Server 2008