Меню

Экспорт таблиц MS SQL Server 2000 2005 2008 в XML файл



Средство импорта и экспорта данных в Microsoft SQL Server 2008

В СУБД Microsoft SQL Server 2008 существует отличный функционал по импорту и экспорту данных, причем в разные форматы и разные базы данных. Его можно также использовать для простого переноса данных из одной базы в другую или с одного сервера на другой. Сегодня мы рассмотрим примеры использования данного средства, и, как мне кажется, это очень удобно.

Мы с Вами уже не раз затрагивали тему импорта и экспорта данных в MS SQL Server 2008, например, в статьях:

Но так или иначе, это было связанно с клиентским приложением, т.е. Access, другими словами, мы это делали для пользователей, чтобы они могли выгружать или загружать данные в базу. А теперь пришло время поговорить о том, чем может, и, наверное, должен пользоваться системный администратор или программист для подобного рода задач.

Примечание! Далее подразумевается, что у Вас уже установлена СУБД Microsoft SQL Server 2008 и средство импорта и экспорта данных, так как оно идет в комплекте, и на примере Windows 7 Вы можете наблюдать в меню «Пуск-> Все программы-> Microsoft SQL Server 2008 R2-> Импорт и экспорт данных (32-разрядная версия)». У меня это выглядит следующим образом:

Скриншот 1

И для того чтобы рассмотреть функционал по импорту и экспорту данных, давайте разберем два примера, первый по импорту данных, а второй по экспорту данных.

  1. Импорт данных из Excel документа в MSSql 2008
  2. Создаем тестовые данные в документе Excel
  3. Шаг 1
  4. Шаг 2
  5. Шаг 3
  6. Шаг 4
  7. Шаг 5
  8. Шаг 6
  9. Шаг 7
  10. Шаг 8
  11. Экспорт данных из Microsoft SQL Server 2008 в файл Excel
  12. Шаг 1
  13. Шаг 2
  14. Шаг 3
  15. Шаг 4
  16. Шаг 5
  17. Шаг 6
  18. Шаг 7

Импорт данных из Excel документа в MSSql 2008

Создаем тестовые данные в документе Excel

Мы будем использовать старый, но проверенный Excel 2003 и формат файла у нас будет xls.

Данные будут вот такие, файл я назвал test_file.xls:

Скриншот 2

Сразу скажу, что в данном примере мы будем импортировать данные в новую таблицу, поэтому на название полей мы не обращаем внимания. Но если бы мы импортировали уже в существующую таблицу, то нам в процессе импорта пришлось бы задавать соответствие этих полей или изначально в файле создать столько полей с тем же названием и в той же последовательности, как и в таблице. А если этого не сделать, то те поля, которые отсутствуют в таблице в базе, будут со значением null. Как задать соответствие этих полей и как импортировать данные в уже существующую таблицу я покажу в процессе импорта.

Примечание! Сервер располагается локально, база данных называется test.

Шаг 1

Итак, приступим, у нас есть файл, теперь запускаем средство импорта, и у нас открывается следующее окно:

Скриншот 3

Шаг 2

Нажимаем далее, где нам предлагают выбрать источник данных, в нашем случае это Excel, мы выбираем файл, версию excel и ставим галочку, что первая строка — это заголовок:

Скриншот 4

Шаг 3

Жмем далее, нам предлагают выбрать назначение, куда копировать эти данные, мы выбираем SQL Server, указываем имя сервера, т.е. его адрес, в нашем случае, как я уже сказал, он расположен локально. Также не забудьте про проверку подлинности, выбирайте тот метод, который у Вас настроен на сервере, и, конечно же, про базу данных, в которую копировать:

Скриншот 5

Шаг 4

Снова жмем далее, где мы укажем все ли данные копировать, в нашем случае мы говорим что все:

Скриншот 6

Шаг 5

Жмем далее, и попадаем на окно выбора листа с данными и задания названия таблицы в нашей базе, я выбрал лист 1 и назвал таблицу test_table:

Скриншот 7

И, кстати, вот на этом этапе можно задать соответствие полей. В случае если Вы импортируете данные в существующую таблицу, для соответствия полей Вам необходимо выбрать таблицу из выпадающего списка и нажать изменить, где Вы также можете задать такие параметры как назначение, тип данных и другие, для примера вот это окно:

Скриншот 8

Шаг 6

Это было небольшое отступление, а в нашем примере мы жмем далее, и попадаем в окно, в котором можно сохранить все наше действия в пакет, но мы этого делать не будем, а сразу же нажмем готово:

Скриншот 9

Шаг 7

После появится окно, где мы все проверяем и жмем готово:

Скриншот 10

Шаг 8

И в заключение у нас появится еще одно окно, так сказать результат наших действий, жмем закрыть:

Скриншот 11

Как видно, передано 3 строки, т.е. импортировано — это означает, что все наши данные, которые были в файле, импортировались.

И для того, чтобы проверить какие данные у нас импортировались, выполним в Management Studio простой запрос select.

Скриншот 12

И как видите все хорошо!

Экспорт данных из Microsoft SQL Server 2008 в файл Excel

А теперь давайте рассмотрим пример экспорта данных из нашей только что созданной таблицы в Excel документ.

Шаг 1

Для этого делаем практически то же самое, открываем средство экспорта, но уже здесь в качестве источника указываем нашу базу:

Скриншот 13

Шаг 2

Жмем далее, где нам предлагают указать назначение экспорта, мы соответственно выбираем Excel, и задаем путь и название выгружаемого файла:

Скриншот 14

Шаг 3

После того как Вы нажмете далее, Вы попадете в окно выбора данных, т.е. какие именно данные мы будем выгружать, и здесь давайте укажем — выгружать данные на основе запроса. Так как, когда мы импортировали данные, мы выбрали все, а теперь для примера выберем не все, а на основе запроса, можно также выбрать все и указать таблицу или представление VIEWS, в котором уже будут отфильтрованные данные, но мы напишем SQL запрос:

Скриншот 15

Шаг 4

И в следующем окне вставляем свой запрос, например, я написал вот такой:

Скриншот 16

Также в этом окне Вы можете выполнить анализ своего запроса, на предмет наличия ошибок или выбрать файл, который содержит текст запроса, и, если все хорошо, жмем далее.

Шаг 5

На следующем окне сразу же можем нажимать далее, если конечно Вы не хотите задать свои названия полей в выгружаемом файле.

Шаг 6

Затем на следующем окне все проверяем и жмем готово.

Шаг 7

Далее, как и в импорте, жмем последний раз готово. И все, после этого у Вас в той папке, которую Вы указали, появится документ Excel с Вашими данными.

После рассмотрения этих примеров, я думаю стало понятно, как можно осуществлять импорт и экспорт данных в MS SQL Server 2008. Использовать можно не только Excel, но и другие источники данных, ну я думаю, дальше Вы разберетесь сами, так как это не так сложно, но если у Вас все равно возникают вопросы, можете задавать их в комментариях.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Источник

Экспорт таблиц MS SQL Server 2000/2005/2008 в XML файл

Здравствуйте, уважаемое хабрасообщество !

Поговорим о проблеме, ставшей заголовком этой темы.

Постановка:

Необходимость вывода таблицы на сервере в XML файл нужной кодировки для дальнейших нужд (анализ, включение XML в другие компоненты и приложения и т.п.). Будем использовать bat-сценарий.

Возникшие трудности

©
Простота и скорость использования для различных таблиц и баз.

Шаги реализации

Для начала, воспользуемся утилитой bcp, которая входит в комплект поставки MS SQL Server (Даже в Express версии). Подробрее.
Из ее возможностей нам понадобится только вывод результата запроса в файл.
Значения ключей на примере:
bcp «SELECT * FROM DB.SCHEMA.TABLE FOR XML AUTO, ROOT(‘ROOT’)» queryout temp.xml -w -S %SERVERNAME% -U %DBUSER% -P %DBPASS%

SELECT запрос для выборки всех данных из таблицы (указывается полное имя).
XML AUTO отвечает за преобразование результата в XML дерево.
ROOT назначает корневой элемент в этом дереве
queryout задает выходной файл
-w задает использование юникода для массового копирования
-S имя экземпляра сервера
-P пароль
-U пользователь

Этой командой мы получим XML файл БЕЗ заголовка в кодировке UTF-16.
Нужно присоединить заголовок и сделать xml нужной кодировки.
Создадим шаблон заголовка xml_header.xml с содержимым:

* This source code was highlighted with Source Code Highlighter .

Теперь достаточно будет выполнить команду
copy xml_header.xml + bcp_out.xml result.xml
и получить валидный XML документ.

Для преобразования кодировки же будем использовать iconv, любой реализации. Я выбрал самое компактное и портативное решение под Windows, написанное на Win32 API от Yukihiro Nakadaira.

Читайте также:  Тест по истории Нужна ли нам единая и неделимая Италия 8 класс

Итак, файл сценария:

if «%1» == «» (
rem Отсутстуют параметры
echo Use with : db_name db_table [out_file]
exit /b 1
)

if «%2» == «» (
echo Use with : db_name db_table [out_file]
exit /b 1
)

rem Читаем настройки из файла settings.txt, который должен располагаться в
rem том же каталоге, что и bat-файл. Если не удалось распарсить настройки —
rem выходим с ненулевым кодом возврата.
call :read_settings %

dp0settings.txt || exit /b 1

set DBNAME=%1
set DBTABLE=%2
set OUTFILE=%3

echo;
echo ====== ECHO SETTINGS FROM CONFIG ======
echo;
echo ServerName : %SERVERNAME%
echo Schema : %SCHEMA%
echo Out codepage: %OUTCP%
echo User : %DBUSER%
echo Pass : ********
echo Iconv path : %ICONVPATH%
echo;
echo =======================================
echo;
echo ====== ECHO SETTINGS FROM CMD =========
echo;
echo DB Name = %1
echo DB Table = %2
echo Output file = %3
echo;
echo =======================================
echo;
echo ====== CALL TO BCP UTIL ===============
echo;
call :bcp_call
echo;
echo ====== CALL TO ICONV ==================
echo;
call :iconv_call
echo;
echo =======================================
echo;
echo See the log\log.txt for details
exit /b 0

rem
rem Функция для чтения настроек из файла.
rem Вход:
rem %1 — Имя файла с настройками
:read_settings

rem Проверка существования файла
if not exist %SETTINGSFILE% (
echo FAIL: No such file %SETTINGSFILE%
exit /b 1
)

rem Обработка файла c настройками

for /f «eol=# delims== tokens=1,2» %%i in (%SETTINGSFILE%) do (
set %%i=%%j
)

rem
rem Функция для обращения к БД
:bcp_call

bcp «SELECT * FROM %DBNAME%.%SCHEMA%.%DBTABLE% FOR XML AUTO, ROOT(‘%DBTABLE%’)» queryout temp.xml -w -r «» -S %SERVERNAME% -U %DBUSER% -P %DBPASS% > log\rawlog.txt

rem Кодируем лог в нормальную кодировку
%ICONVPATH% -f cp866 -t cp1251 log\rawlog.txt > log\log.txt
del log\rawlog.txt

copy lib \xml_header.xml + temp.xml temp2.xml > nul
del temp.xml

rem
rem Функция перекодировки
:iconv_call

rem Дефолтное значение выходной кодировки
if «%OUTCP%» == «» (
set OUTCP=CP1251
)

rem Дефолтное значение выходного файла
if «%OUTFILE%» == «» (
set OUTFILE=out\%DBTABLE%.xml
)

if not exist %ICONVPATH% (
echo FAIL: Check Iconv path !
exit /b /1
)

%ICONVPATH% -f UTF-16 -t %OUTCP% temp2.xml > %OUTFILE%
del temp2.xml

* This source code was highlighted with Source Code Highlighter .

Через параметры командной строки передаем: имя_базы имя_таблицы [выходной файл]
Остальную конфигурацию прописываем в settings.txt:

# Имя сервера
SERVERNAME=WIND\SQLEXPRESS
# Имя схемы
SCHEMA=dbo
# Имя выходной кодировки
OUTCP=CP1251

# Имя пользователя
DBUSER=dzhon
# Пароль пользователя
DBPASS=123

#Путь к iconv.exe
ICONVPATH=lib\win_iconv.exe

Для логики, в XML файле создано 3 функции.
Первая парсит конфигурацию, вторая вызывает bcp, третья — iconv для результирующего файла.

Заключение

Сразу замечу, что по-умолчанию используется CP1251 и лог работы bcp кодируется в нее же. Сделано это для удобства работы именно в Windows, а не каких-то иных религиозных предпочтений. Помню времена, когда Windows XP выдавала крокозябры при вызове простого route PRINT в описаниях интерфейсов… Поэтому я сам и предпочитаю всегда и везде UTF-8. Впрочем, скрипт достаточно гибок к замене кодировки выхода, правда придется задать в xml_header.xml другое значение.

Скачать архив с рабочим вариантом решения можно здесь (13 Кб).

Для SQL Server 2000 придется внести некоторые изменения, в связи с тем, что понятие схемы (в смысле контейнера объектов) было введено только с 2005-го.

Вы можете также посмотреть материал по написанию bat файлов, из которого была утащена функция парсинга конфигурации.

Спасибо за внимание, надеюсь, кому-то поможет.
Перенесу в тематический блог при наличии кармы, впрочем, решать вам.

Источник

Организация хранения файлов в базе данных Microsoft SQL Server. Использование файловых таблиц

Начиная с версии 2012, в SQL Server стала доступен новый формат хранения файловых данных – файловые таблицы.

Файловые таблицы (FileTables) – особый вид таблиц, который позволяет помимо хранения файлов практически неограниченного размера (с этим легко справлялся и FileStream, появившийся в версии 2008), также получать к ним доступ из совершенно сторонних приложений, которые даже не подключены к данной БД, при помощи обычных средств файловой системы Windows.

Также поддерживается и хранение папок.

Подготовка к работе

Работа файловых таблиц основана на технологии FileStream. Поэтому перед тем как создать файловую таблицу необходимо включить поддержку FileStream для данного экземпляра SQL Server.

Включение производится в два этапа.

  1. Включение поддержки FileStream на уровне сервера в окне его свойств;
    Включение FileStream Сервер
  2. Включение поддержки FileStream для службы SQL Server данного экземпляра и её последующая перезагрузка. Это проще всего сделать при помощи Sql Server Configuration Manager.
    ВключениеFileSream служба

После того как поддержка FileStream будет включена, необходимо добавить в БД файловую группу FileStream и создать в ней хотя бы один файл.

Файловая группа FileStream

Файл группы FileStream

Теперь можно приступить непосредственно к работе с файловыми таблицами.

Создание файловых таблиц

Для того чтобы создать файловую таблицу достаточно воспользоваться простейшей командой Transact-SQL

Ключевым параметров в ней является параметр FILETABLE_DIRECTORY, который определяет имя условной «папки» в файловой системе, через которую и будет осуществляться доступ к файлам, хранящимся в таблице. Почему эта папка названа условной будет пояснено далее.

В вышеприведённой инструкции отсутствуют столбцы и их типы данных. Дело в том, что структура файловых таблиц строго фиксирована и создаётся вместе с таблицей автоматически. Эта структура исчерпывающе описана в документации [1].

Работа с файловыми таблицами средствами файловой системы

После создания файловой таблицы создаётся сетевая папка со следующим адресом:

Эта условная сетевая папка и позволяет работать с содержимым файловых таблиц как с самыми обыкновенными файлами. При этом поддерживаются файлы любых форматов. Также можно добавлять целые папки.

При добавлении, изменении или удалении файлов из этой папки изменяются соответствующие данные в самой файловой таблице. Ниже показан пример подобного добавления файла в файловую таблицу.

Доступ через файловую систему

Что же представляет собой данная условная» папка» на самом деле?

А, на самом деле в папке, в которой хранятся файлы БД создаётся подпапка с именем файла данных FileStream. В ней, в свою очередь, создаются ещё две вложенные друг в друга подпапки с именами в формате GUID.

Наконец в папке на самом нижнем уровне этой структуры имеются два файла.

В них и сохранён добавленный в БД файл.

Работа с файловыми таблицами средствами Transact-SQL и языков программирования общего назначения

Список файлов и папок

Одной из самых распространённых задач является получение списка файлов и папок хранящихся в БД.

Решить её можно при помощи несложного SQL запроса:

Если значение поля is_directory равно 1, значит это папка (0 – обычный файл).

Поля path_locator и parent_path_locator – соответственно первичный и внешний ключи для хранения иерархических связей.

Например, поле parent_path_locator у файла WeatherForecast.apk указывает на папку а которой он расположен (см.скриншот).

Это позволяет, в частности, легко визуализировать структуру хранящихся данных.

Выгрузка файлов с помощью клиентской программы

Содержимое файлов в файловых таблицах физически хранится в формате varbinary(max) в поле file_stream.

Поэтому оно может быть без труда получено клиентской программы при помощи метода, который был рассмотрен ранее для хранения файлов универсальным способом [2].

Загрузка, изменение и удаление файлов с помощью клиентской программы

Загрузка файлов в файловую таблицу также не имеет принципиальных отличий от универсального способа загрузки в поле формата varbinary(max) [2].

Существуют некоторые ограничения связанные с поддержкой хранения папок и общей структурой таблицы [3]. В частности:

  • Все столбцы атрибутов файла имеют ограничения NO NULL.Если значения не заданы явным образом, предоставляются соответствующие значения по умолчанию;
  • Если инструкция INSERT устанавливает name, path_locator, parent_path_locator или атрибуты файлов, то применяются системные ограничения;
  • Приложение может получить path_locator для файла или каталога при указании пути файловой системы для функции GetPathLocator (Transact-SQL);
  • Разрешается обновлять любые данные, определяемые пользователем;
  • Обновление данных FILESTREAM в столбце file_stream не влияет на другие столбцы, включая отметки времени;
  • При удалении строки удаляется соответствующий файл или каталог из файловой системы (подробное пояснение см. выше);
  • Невозможно удалить строку, если она относится к каталогу, который содержит другие файлы или каталоги.
Резюме

Использование файловых таблиц предоставляет ряд существенных преимуществ:

  • Расширенные возможности работы за счёт доступа к данным средствами файловой системы;
  • Уменьшение размера БД и увеличения быстродействия за счёт хранения больших объёмов данных во внешних файлах;
  • Возможность хранения неограниченного объёма данных, так как данные файловых таблиц (как и «обычные» данные FileStream) не подпадают под ограничение 2ГБ для типа данных varbinary(max).
  • Более сложное развёртывание и сопровождение. В частности потому, что на файловые таблицы распространяются те же ограничения, что и на FileStream;
  • Более сложное взаимодействие с клиентскими программами вследствие технологических ограничений;
  • Несмотря на кажущуюся простоту и удобство использование файловых таблиц в значительной е снижает надёжность и отказоустойчивость.
Читайте также:  Таблица температур кипения солей

Применение файловых таблиц, как и любой другой технологии должно быть, прежде всего, оправданным.

Несмотря на все свои преимущества это узкоспециализированное решение предназначенное, прежде всего для организации хранения файлов. Также не рекомендуется их использование для хранения файлов малого размера (менее 1 МБ)

Важно отметить, что для корректной работы необходимо организовать достаточную защиту физического места хранения данных файловой таблицы от несанкционированного доступа и предусмотреть дополнительные меры в плане резервного копирования и восстановления. Если же это по каким-либо причинам не возможно, лучше не рисковать и по возможности прибегнуть к «обычной» форме хранения данных в varbinary(max) [2].

Источник

Transact-SQL — изменение базы данных и таблиц

Язык Transact-SQL поддерживает изменение структуры следующих объектов базы данных:

В последующих двух разделах описывается изменение первых двух объектов из этого списка: баз данных и таблиц. Изменение структуры последних четырех объектов этого списка будет описано при их обсуждении в следующих статьях.

Изменение базы данных

Для изменения физической структуры базы данных используется инструкция ALTER DATABASE. Язык Transact-SQL позволяет выполнять следующие действия по изменению свойств базы данных:

добавлять и удалять один или несколько файлов базы данных;

добавлять и удалять один или несколько файлов журнала;

добавлять и удалять файловые группы;

изменять свойства файлов или файловых групп;

устанавливать параметры базы данных;

изменять имя базы данных с помощью хранимой процедуры sp_rename.

Эти разные типы модификаций базы данных рассматриваются далее.

Добавление и удаление файлов базы данных, файлов журналов и файловых групп

Добавление или удаление файлов базы данных осуществляется посредством инструкции ALTER DATABASE. Операция добавления нового или удаления существующего файла указывается предложением ADD FILE и REMOVE FILE соответственно. Кроме этого, новый файл можно определить в существующую файловую группу посредством параметра TO FILEGROUP.

В примере ниже показано добавление нового файла базы данных в базу данных SampleDb:

В этом примере инструкция ALTER DATABASE добавляет новый файл с логическим именем sampledb_dat1. Здесь же указан начальный размер файла 10 Мбайт и автоувеличение по 5 Мбайт до максимального размера 100 Мбайт. Файлы журналов добавляются так же, как и файлы баз данных. Единственным отличием является то, что вместо предложения ADD FILE используется предложение ADD LOG FILE.

Удаления файлов (как файлов базы данных, так и файлов журнала) из базы данных осуществляется посредством предложения REMOVE FILE. Удаляемый файл должен быть пустым.

Новая файловая группа создается посредством предложения CREATE FILEGROUP, а существующая удаляется с помощью предложения DELETE FILEGROUP. Как и удаляемый файл, удаляемая файловая группа также должна быть пустой.

Изменение свойств файлов и файловых групп

С помощью предложения MODIFY FILE можно выполнять следующие действия по изменению свойств файла:

изменять логическое имя файла, используя параметр NEWNAME;

увеличивать значение свойства SIZE;

изменять значение свойств FILENAME, MAXSIZE и FILEGROWTH;

отмечать файл как OFFLINE.

Подобным образом с помощью предложения MODIFY FILEGROUP можно выполнять следующие действия по изменению свойств файловой группы:

изменять логическое имя файловой группы, используя параметр NAME;

помечать файловую группу, как файловую группу по умолчанию, используя для этого параметр DEFAULT;

помечать файловую группу как позволяющую осуществлять доступ только для чтения или для чтения и записи, используя для этого параметр read_only или read_write соответственно.

Установка опций базы данных

Для установки различных опций базы данных используется предложение SET инструкции ALTER DATABASE. Некоторым опциям можно присвоить только значения ON или OFF, но для большинства из них предоставляется выбор из списка возможных значений. Каждый параметр базы данных имеет значение по умолчанию, которое устанавливается в базе данных model. Поэтому значения определенных опций по умолчанию можно модифицировать, изменив соответствующим образом базу данных model.

Все опции, значения которых можно изменять, можно разбить на несколько групп, наиболее важными из которых являются опции состояния, опции автоматических действий и опции SQL.

Опции состояния управляют следующими возможностями:

доступом пользователей к базе данным (это опции single_user, restricted_user и multi_user);

статусом базы данных (это опции online, offline и emergency);

режимом чтения и записи (опции read_only и read_write).

Опции автоматических операций управляют, среди прочего, остановом базы данных (опция auto_close) и способом создания статистики индексов (опции auto_create_statistics и auto_update_statistics).

Опции SQL управляют соответствием базы данных и ее объектов стандарту ANSI. Значения всех операторов SQL можно узнать посредством функции DATABASEPROPERTY, а редактировать — с помощью инструкции ALTER DATABASE.

Опции восстановления full, bulk-logged и simple управляют процессом восстановления базы данных.

Хранение данных типа FILESTREAM

При описании типов данных T-SQL мы рассмотрели данные типа FILESTREAM и причины, по которым их используют. В этом разделе мы рассмотрим, как данные типа FILESTREAM можно сохранять в базе данных. Чтобы данные FILESTREAM можно было сохранять в базе данных, система должна быть должным образом инициирована. В следующем подразделе объясняется, как инициировать операционную систему и экземпляр базы данных для хранения данных типа FILESTREAM.

Инициирование хранилища FILESTREAM

Хранилище данных типа FILESTREAM требуется инициировать на двух уровнях:

для операционной системы Windows;

для конкретного экземпляра сервера базы данных.

Инициирование хранилища данных типа FILESTREAM на уровне системы осуществляется с помощью диспетчера конфигурации SQL Server Configuration Manager. Чтобы запустить диспетчер конфигурации, выполните следующую последовательность команд по умолчанию Пуск —> Все программы —> Microsoft SQL Server 2012 —> Configuration Tools . В открывшемся окне Sql Server Configuration Manager щелкните правой кнопкой пункт SQL Server Services (Службы SQL Server) и в появившемся контекстном меню выберите команду Open. В правой панели щелкните правой кнопкой экземпляр, для которого требуется разрешить хранилище FILESTREAM, и в контекстном меню выберите команду Properties. В открывшемся диалоговом окне SQL Server Properties выберите вкладку FILESTREAM:

Диалоговое окно SQL Server Properties, вкладка FILESTREAM

Чтобы иметь возможность только читать данные типа FILESTREAM, установите флажок Enable FILESTREAM for Transact-SQL access (Разрешить FILESTREAM при доступе через Transact-SQL). Чтобы кроме чтения можно было также записывать данные, установите дополнительно флажок Enable FILESTREAM for file I/O streaming access (Разрешить использование FILESTREAM при доступе файлового ввода/вывода). Введите имя общей папки Windows в одноименное поле. Общая папка Windows используется для чтения и записи данных FILESTREAM, используя интерфейс API Win32. Если для возвращения пути для FILESTREAM BLOB использовать имя, то это будет имя общей папки Windows.

Диспетчер конфигурации SQL Server создаст на системе хоста новую общую папку с указанным именем. Чтобы применить изменения, нажмите кнопку OK.

Чтобы разрешить хранилище FILESTREAM, необходимо быть администратором Windows локальной системы и обладать правами администратора (sysadmin). Чтобы изменения вступили в силу, необходимо перезапустить экземпляр сервера базы данных.

Следующим шагом будет разрешить хранилище FILESTREAM для конкретного экземпляра. Мы рассмотрим, как выполнить эту задачу с помощью среды SQL Server Management Studio. (Для этого можно также воспользоваться хранимой системной процедурой sp_configure с параметром FILESTREAM ACCESS LEVEL.) Щелкните правой кнопкой требуемый экземпляр в обозревателе объектов и в появившемся контекстном меню выберите пункт Properties, в левой панели открывшегося диалогового окна Server Properties выберите пункт Advanced (Дополнительно):

Диалоговое окно Server Properties с уровнем доступа FILESTREAM, установленным в Full Access Enabled

После этого в правой панели из выпадающего списка выберите FILESTREAM Access Level (Уровень доступа FILESTREAM) одну из следующих опций:

Disabled

Отключено — хранилище FILESTREAM не разрешено.

Transact-SQL Access Enabled

Включен доступ с помощью Transact-SQL — к данным FILESTREAM можно обращаться посредством инструкций T-SQL.

Full Access Enabled

Включен полный доступ — к данным FILESTREAM можно обращаться как посредством инструкций T-SQL, так и через интерфейс API Win32.

Добавление файла в файловую группу

Разрешив хранилище FILESTREAM для требуемого экземпляра, можно сначала создать файловую группу для данных FILESTREAM (посредством инструкции ALTER DATABASE), а затем добавить файл в эту файловую группу, как это показано в примере ниже. (Конечно же, эту задачу также можно было бы выполнить с помощью инструкции CREATE DATABASE.)

Первая инструкция ALTER DATABASE в примере добавляет в базу данных SampleDb новую файловую группу Employee_FSGroup. Параметр CONTAINS FILESTREAM этой инструкции указывает системе, что данная файловая группа будет содержать только данные FILESTREAM. Вторая инструкция ALTER DATABASE добавляет в созданную файловую группу новый файл.

Читайте также:  Элементы управления передней бабки

Теперь можно создавать таблицы, содержащие столбцы с типом данных FILESTREAM. Создание такой таблицы показано в примере ниже:

В этом примере таблица EmployeeInfo содержит столбец FilestreamData, тип данных которого должен быть VARBINARY(MAX). Определение такого столбца включает атрибут FILESTREAM, указывающий, что данные столбца сохраняются в файловой группе FILESTREAM. Для всех таблиц, в которых хранятся данные типа FILESTREAM, требуется наличие свойств UNIQUE ROWGUIDCOL. Поэтому таблица EmployeeInfo содержит столбец Id, определенный с использованием этих двух атрибутов.

Данные в столбце типа FILESTREAM вставляются посредством стандартной инструкции INSERT. А для считывания данных используется стандартная инструкция SELECT.

Автономные базы данных

Одна из значительных проблем с базами данных SQL Server состоит в том, что они трудно поддаются экспортированию и импортированию. Как рассматривалось ранее, базы данных можно присоединять и отсоединять, но при этом утрачиваются важные части и свойства присоединенных баз данных. (Основной проблемой в таких случаях является безопасность базы данных, в общем, и учетные записи, в частности, в которых после перемещения обычно отсутствует часть информации или содержится неправильная информация.)

Разработчики Microsoft планируют решить эти проблемы посредством использования автономных баз данных (contained databases). Автономная база данных содержит все параметры и данные, необходимые для определения базы данных, и изолирована от экземпляра Database Engine, на котором она установлена. Иными словами, база данных данного типа не имеет конфигурационных зависимостей от экземпляра и ее можно с легкостью перемещать с одного экземпляра SQL Server на другой.

По большому счету, что касается автономности, существует три вида баз данных:

полностью автономные базы данных;

частично автономные базы данных;

неавтономные базы данных.

Полностью автономными являются такие базы данных, объекты которых не могут перемещаться через границы приложения. (Граница приложения определяет область видимости приложения. Например, пользовательские функции находятся в границах приложения, в то время как функции, связанные с экземплярами сервера, находятся вне границ приложения.)

Частично автономные базы данных позволяют объектам пересекать границы приложения, в то время как неавтономные базы данных вообще не поддерживают концепции границы приложения.

В SQL Server 2012 поддерживаются частично автономные базы данных. В будущих версиях SQL Server также будет поддерживаться полная автономность. Базы данных предшествующих версий SQL Server являются неавтономными.

Рассмотрим, как создать частично автономную базу данных в SQL Server 2012. Если существующая база данных SampleDb является неавтономной (созданная, например, посредством инструкции CREATE DATABASE), с помощью инструкции ALTER DATABASE ее можно преобразовать в частично автономную, как это показано в примере ниже:

Инструкция ALTER DATABASE изменяет состояние автономности базы данных SampleDb с неавтономного на частично автономное. Это означает, что теперь система базы данных позволяет создавать как автономные, так неавтономные объекты для базы данных SampleDb. Все другие инструкции в примере являются вспомогательными для инструкции ALTER DATABASE.

Функция sp_configure является системной процедурой, с помощью которой можно, среди прочего, изменить дополнительные параметры конфигурации, такие как ‘contained database authentication’. Чтобы изменить дополнительные параметры конфигурации, сначала нужно присвоить параметру ‘show advanced options’ значение 1, а потом переконфигурировать систему (инструкция RECONFIGURE). В конце кода этому параметру опять присваивается его значение по умолчанию — 0.

Теперь для базы данных SampleDb можно создать пользователя, не привязанного к учетной записи.

Изменение таблиц

Для модифицирования схемы таблицы применяется инструкция ALTER TABLE. Язык Transact-SQL позволяет осуществлять следующие виды изменений таблиц:

добавлять и удалять столбцы;

изменять свойства столбцов;

добавлять и удалять ограничения для обеспечения целостности;

разрешать или отключать ограничения;

переименовывать таблицы и другие объекты базы данных.

Эти типы изменений рассматриваются в последующих далее разделах.

Добавление и удаление столбцов

Чтобы добавить новый столбец в существующую таблицу, в инструкции ALTER TABLE используется предложение ADD. В одной инструкции ALTER TABLE можно добавить только один столбец. Применение предложения ADD показано в примере ниже:

В этом примере инструкция ALTER TABLE добавляет в таблицу Employee столбец PhoneNumber. Компонент Database Engine заполняет новый столбец значениями NULL или IDENTITY или указанными значениями по умолчанию. По этой причине новый столбец должен или поддерживать значения NULL, или для него должно быть указано значение по умолчанию.

Новый столбец нельзя вставить в таблицу в какой-либо конкретной позиции. Столбец, добавляемый предложением ADD, всегда вставляется в конец таблицы.

Столбцы из таблицы удаляются посредством предложения DROP COLUMN. Применение этого предложения показано в примере ниже:

В этом коде инструкция ALTER TABLE удаляет в таблице Employee столбец PhoneNumber, который был добавлен в эту таблицу предложением ADD ранее.

Изменение свойств столбцов

Для изменения свойств существующего столбца применяется предложение ALTER COLUMN инструкции ALTER TABLE. Изменению поддаются следующие свойства столбца:

поддержка значения NULL.

Применение предложения ALTER COLUMN показано в примере ниже:

Инструкция ALTER TABLE в этом примере изменяет начальные свойства (nchar(40), значения NULL разрешены) столбца Location таблицы Department на новые (nchar(25), значения NULL не разрешены).

Добавление и удаления ограничений для обеспечения целостности (ключей и проверок)

Для добавления в таблицу новых ограничений для обеспечения целостности используется параметр ADD CONSTRAINT инструкции ALTER TABLE. В примере ниже показано использование параметра ADD CONSTRAINT для добавления проверочного ограничения и определения первичного ключа таблицы:

В этом примере сначала инструкцией CREATE TABLE создается таблица Sales, содержащая два столбца с типом данных DATE: OrderDate и ShipDate. Далее, инструкция ALTER TABLE определяет ограничение для обеспечения целостности order_check, которое сравнивает значения обоих этих столбцов и выводит сообщение об ошибке, если дата отправки ShipDate более ранняя, чем дата заказа OrderDate. Далее инструкция ALTER TABLE используется для определения первичного ключа таблицы в столбце Id.

Ограничения для обеспечения целостности можно удалить посредством предложения DROP CONSTRAINT инструкции ALTER TABLE, как это показано в примере ниже:

Определения существующих ограничений нельзя модифицировать. Чтобы изменить ограничение, его сначала нужно удалить, а потом создать новое, содержащее требуемые модификации.

Разрешение и запрещение ограничений

Как упоминалось ранее, ограничение для обеспечения целостности всегда имеет имя, которое может быть объявленным или явно посредством опции CONSTRAINT, или неявно посредством системы. Имена всех ограничений таблицы (объявленных как явно, так и неявно) можно просмотреть с помощью системной процедуры sp_helpconstraint.

В последующих операциях вставки или обновлений значений в соответствующий столбец ограничение по умолчанию обеспечивается принудительно. Кроме этого, при объявлении ограничения все существующие значения соответствующего столбца проверяются на удовлетворение условий ограничения. Начальная проверка не выполняется, если ограничение создается с параметром WITH NOCHECK. В таком случае ограничение будет проверяться только при последующих операциях вставки и обновлений значений соответствующего столбца. (Оба параметра — WITH CHECK и WITH NOCHECK — можно применять только с ограничениями проверки целостности CHECK и проверки внешнего ключа FOREIGN KEY.)

В примере ниже показано, как отключить все существующие ограничения таблицы:

Все ограничения таблицы Sales отключаются посредством ключевого слова ALL. Применять опцию NOCHECK не рекомендуется, поскольку любые подавленные нарушения условий ограничения могут вызвать ошибки при будущих обновлениях.

Переименование таблиц и других объектов баз данных

Для изменения имени существующей таблицы (и любых других объектов базы данных, таких как база данных, представление или хранимая процедура) применяется системная процедура sp_rename. В примере ниже показано использование этой системной процедуры:

Использовать системную процедуру sp_rename настоятельно не рекомендуется, поскольку изменение имен объектов может повлиять на другие объекты базы данных, которые ссылаются на них. Вместо этого следует удалить объект и воссоздать его с новым именем.

Удаление объектов баз данных

Все инструкции Transact-SQL для удаления объектов базы данных имеют следующий общий вид:

Для каждой инструкции CREATE object для создания объекта имеется соответствующая инструкция DROP object для удаления. Инструкция для удаления одной или нескольких баз данных имеет следующий вид:

Эта инструкция безвозвратно удаляет базу данных из системы баз данных. Для удаления одной или нескольких таблиц применяется следующая инструкция:

При удалении таблицы удаляются все ее данные, индексы и триггеры. Но представления, созданные по удаленной таблице, не удаляются. Таблицу может удалить только пользователь, имеющий соответствующие разрешения.

Кроме объектов DATABASE и TABLE, в параметре objects инструкции DROP можно указывать, среди прочих, следующие объекты:

Источник