Как удалить файловую группу sql

Обновлено: 06.07.2024

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

Файл базы данных

Базы данных SQL Server имеют три типа файлов:

Файл основных данных
Основной файл данных является отправной точкой базы данных. Помимо системы хранения и пользовательских данных, в файле основных данных также хранится такая информация, как путь, имя и размер всех файлов вспомогательных данных в базе данных и файл журнала повторов. SQL Server получает информацию о других файлах данных и восстанавливает файлы журналов, читая основной файл данных. Эта функция аналогична управляющему файлу Oracle. Каждая база данных имеет файл основных данных. Рекомендуемое расширение файла для файла основных данных .mdf.
Вторичный файл данных
Все остальные файлы данных, кроме основного файла данных, являются вторичными файлами данных, которые обычно хранят только пользовательские данные. Некоторые базы данных могут не содержать никаких вторичных файлов данных, в то время как некоторые базы данных содержат несколько вторичных файлов данных. Рекомендуемое расширение файла для вторичных файлов данных .ndf.
Лог-файл
Файл журнала содержит всю информацию журнала, используемую для восстановления базы данных. В каждой базе данных должен быть хотя бы один файл журнала, разумеется, их может быть несколько. Рекомендуемое расширение файла для файлов журнала .ldf.

SQL Server не требует использования расширений файлов .mdf, .ndf и .ldf, но их использование может помочь идентифицировать различные типы и использование файлов.
В SQL Server местоположения всех файлов в базе данных записываются в главном файле и главной базе данных базы данных. В большинстве случаев ядро базы данных SQL Server использует информацию о расположении файлов в базе данных master. Однако в следующих случаях ядро базы данных использует информацию о расположении файла главного файла для инициализации элемента местоположения файла в главной базе данных:

При использовании оператора CREATE DATABASE с опцией FOR ATTACH или FOR ATTACH_REBUILD_LOG для присоединения базы данных.
При обновлении с SQL Server 2000 или 7.0.
При восстановлении основной базы данных.

Группа файлов базы данных

Для облегчения распространения и управления объекты базы данных и файлы могут быть разделены на группы файлов вместе. Файловая группа SQL Server состоит из несколькихДата файлысочинение.
Файловая группа SQL Server - это основная файловая группа и пользовательская файловая группа, которые соответствуют системному табличному пространству и пользовательскому табличному пространству в базе данных Oracle соответственно.

основная группа файлов
Основная группа файлов содержит основной файл данных и любые другие файлы, которые явно не назначены другим группам файлов. Все страницы системных таблиц размещены в основной файловой группе. Подобно системному табличному пространству базы данных Oracle, первичная файловая группа не может быть удалена, и ее имя первичное также является фиксированным и не может быть изменено.
Пользовательская группа файлов
Пользовательская файловая группа - это любая файловая группа, указанная с помощью ключевого слова FILEGROUP в операторе CREATE DATABASE или ALTER DATABASE.

Файлы журнала не входят в файловую группу. Пространство журнала управляется отдельно от пространства данных.
В базе данных SQL Server нет группы файлов, соответствующей временному табличному пространству Oracle. Многоверсионные данные (отмена) SQL Server и временные данные, сгенерированные с помощью операций сортировки или хеширования, хранятся в системе tempdb. В базе данных несколько баз данных совместно используют базу данных tempdb.

Файл не может быть членом нескольких групп файлов. Таблицы, индексы и данные больших объектов могут быть связаны с указанными группами файлов. В этом случае все их страницы будут выделены для группы файлов, или таблица и индекс будут разделены. Данные секционированных таблиц и индексов делятся на блоки, и каждый блок может быть помещен в отдельную файловую группу в базе данных.
В базе данных SQL Server запрещается удалять группу файлов, содержащую таблицу или индекс. Это отличается от Oracle. В Oracle, если табличное пространство содержит данные, когда табличное пространство удаляется с помощью отбрасывания табличного пространства , Вы можете добавить пункт включения содержимого.
Файловая группа в каждой базе данных назначается файловой группой по умолчанию. Если при создании таблицы или индекса файловая группа не указана, предполагается, что все страницы выделены из файловой группы по умолчанию. Только одна файловая группа может быть файловой группой по умолчанию одновременно. Если файловая группа по умолчанию не указана, основная файловая группа используется в качестве файловой группы по умолчанию. Члены предопределенной роли базы данных db_owner могут переключать файловую группу по умолчанию из одной файловой группы в другую.

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

Файл или файловая группа не могут использоваться несколькими базами данных. Например, никакая другая база данных не может использовать файлы sales.mdf и sales.ndf, которые содержат данные и объекты в базе данных продаж.
Файл может быть членом только одной группы файлов.
Файлы журнала транзакций не могут принадлежать какой-либо файловой группе.

Площадь (протяженность)

Экстент - это единица, которая выделяет место для хранения таблицы или индекса, а также является основной единицей управления пространством.
В SQL Server размер экстента представляет собой фиксированные 8 последовательных страниц данных, 64 КБ, что означает, что в базе данных SQL Server имеется 16 областей на МБ. При создании файловой группы нельзя указывать размер экстента, аналогичный условиям автоматического выделения или единообразного размера в Oracle, поэтому SQL Server менее гибок.

SQL Server выделяет экстенты для таблиц не так, как в Oracle. Чтобы сделать выделение пространства эффективным, SQL Server не будет выделять все области таблицам, которые содержат небольшой объем данных, поэтому SQL Server не будет выделять экстенты для пустых таблиц, и выделение расширений будет отложено до тех пор, пока записи не будут добавлены в таблицу.
SQL Server имеет два типа зон:

Смешанная область (смешанный экстент): смешанная область используется несколькими таблицами или индексами и может использоваться несколькими объектами. Каждая из восьми страниц в зоне может принадлежать другому объекту.
Единая степень: Единая степень принадлежит одному объекту. Все 8 страниц в зоне могут быть предназначены только для одной таблицы или индекса.

Обычно первые 8 страниц данных, выделенных для таблицы или индекса, размещаются в смешанной зоне, а последующие страницы данных - в объединенной зоне. Этот метод отличается от Oracle. Зона Oracle может быть назначена только одной таблице или индексу. Несколько объектов не могут быть общими, или можно сказать, что Oracle имеет только один тип объединенной зоны в SQL Server.

Управление файловыми группами и файловыми группами SQL Server

<add_or_modify_files> :: = </ add_or_modify_files>: укажите файлы, которые будут добавлены, удалены или изменены.
имя_базы_данных: имя базы данных, которую нужно изменить.
ДОБАВИТЬ ФАЙЛ: Добавить файл в базу данных.
TO FILEGROUP : укажите группу файлов, в которую нужно добавить указанный файл.
ADD LOG FILE: Добавьте файл журнала, который будет добавлен в указанную базу данных.
УДАЛИТЬ ФАЙЛ логическое_файл_имя: удалите описание логического файла и удалите физический файл из экземпляра SQL Server. Если файл не пуст, файл не может быть удален.
логическое_файл_имя: логическое имя, используемое при обращении к файлу в SQL Server.
MODIFY FILE: укажите файл, который необходимо изменить. Если указан SIZE, новый размер должен быть больше текущего размера файла.
Чтобы изменить логическое имя файла данных или файла журнала, укажите имя логического файла для переименования в предложении NAME и укажите новое логическое имя файла в предложении NEWNAME. Например:

Чтобы переместить файлы данных или файлы журналов в новое место, укажите текущее логическое имя файла в предложении NAME и укажите новый путь и имя файла операционной системы (физическое) в предложении FILENAME. Например:

os_file_name: для стандартных (ROWS) групп файлов это путь и имя файла, используемые операционной системой при создании файла.
'filestream_path': для группы файлов FILESTREAM FILENAME указывает путь, в котором будут храниться данные FILESTREAM.
memory_optimized_data_path: Для групп файлов, оптимизированных для памяти, FILENAME будет указывать путь, где будут храниться данные, оптимизированные для памяти. Атрибуты SIZE, MAXSIZE и FILEGROWTH не применяются к оптимизированным для памяти группам файлов.
FILEGROWTH: используется для указания размера каждого файла. Если определенное значение не указано, по умолчанию используется значение 1 МБ. Если указано значение 0, файл данных не может автоматически увеличиваться. В качестве единицы измерения можно использовать МБ, КБ, ГБ, ТБ или процент (%). Значение по умолчанию - МБ. Если указан%, то размер приращения - это определенный процент от размера файла при росте. Указанный размер округляется до ближайшего кратного 64 КБ.
OFFLINE: отключить файл и сделать все объекты в группе файлов недоступными.
<add_or_modify_filegroups> :: = </ add_or_modify_filegroups>: добавление, изменение или удаление файловых групп в базе данных.
СОДЕРЖИТ FILESTREAM: указывает, что файловая группа хранит большие двоичные объекты FILESTREAM (BLOB) в файловой системе.
CONTAINS MEMORY_OPTIMIZED_DATA: укажите группу файлов для хранения данных оптимизации памяти в файловой системе. В каждой базе данных может быть только одна группа файлов MEMORY_OPTIMIZED_DATA. При создании таблицы, оптимизированной для памяти, файловая группа не может быть пустой, и она должна содержать хотя бы один файл.
REMOVE FILEGROUP filegroup_name: удалите файловую группу filegroup_name, чтобы удалить файловую группу из базы данных. Если файловая группа не пуста, ее нельзя удалить. Сначала удалите все файлы из группы файлов.
MODIFY FILEGROUP filegroup_name: Изменить файловую группу.
ПО УМОЛЧАНИЮ: Измените группу файлов базы данных по умолчанию на имя_группы файлов. В качестве файловой группы по умолчанию может быть только одна файловая группа в базе данных.
AUTOGROW_SINGLE_FILE: Когда файлы в файловой группе соответствуют порогу автоматического роста, увеличивается только файл. Это значение по умолчанию.
AUTOGROW_ALL_FILES: если файлы в файловой группе достигают порога автоматического роста, все файлы в файловой группе растут.
<filegroup_updatability_option>: установить атрибуты только для чтения или чтения / записи для файловой группы.
READ_ONLY | READONLY: указанная группа файлов доступна только для чтения. Объекты в нем не могут быть обновлены. Основная группа файлов не может быть установлена только для чтения. Чтобы изменить этот статус, вы должны иметь эксклюзивный доступ к базе данных.
Поскольку базы данных только для чтения не позволяют изменять данные, произойдет следующее:
При запуске системы автоматическое восстановление будет пропущено.
Невозможно сжать базу данных.
не будет заблокирован в базе данных только для чтения. Это может ускорить запрос.

A. Добавить группу файлов, состоящую из двух файлов, в базу данных
В следующем примере создается группа файлов Test1FG1 в базе данных AdventureWorks2012, а затем добавляются два файла по 5 МБ в группу файлов.

Б. Добавьте два файла журнала в базу данных.

C. Удалить файлы из базы данных

D. Изменить файлы
Размер файла, добавленного в следующем примере. Команда ALTER DATABASE MODIFY FILE может увеличить размер файла, поэтому, если вам нужно уменьшить размер файла, вам нужно использовать DBCC SHRINKFILE.

В этом примере размер файла сжатых данных составляет 100 МБ, а затем укажите размер в этом размере.

E. переместить файл в новое место
Далее в качестве примера используется файл данных E: \ t1dat2.ndf в данных AdventureWorks в C: \ t1dat2.ndf, чтобы проиллюстрировать процесс перемещения файла данных.
Сначала переведите базу данных в автономный режим:

Переместите E: \ t1dat2.ndf в C: \ t1dat2.ndf в операционной системе:

Измените запись этого пути к файлу в базе данных:

Наконец, верните базу данных в оперативный режим:

Затем запросите физический путь к файлу t1dat2:

F. Сделать файловую группу файловой группой по умолчанию
В следующем примере Test1FG1 становится файловой группой по умолчанию. Затем группа файлов по умолчанию сбрасывается в группу файлов PRIMARY. Обратите внимание, что PRIMARY должен быть разделен скобками или кавычками.

Запрос информации назначенного экстента указанной таблицы

В SQL Server вы можете использовать команду dbcc exteninfo для запроса информации о выделенном экстенте таблицы.

Каждая база данных SQL Server имеет как минимум два рабочих системных файла: файл данных и файл журнала. Файлы данных содержат данные и объекты, такие как таблицы, индексы, хранимые процедуры и представления. Файлы журнала содержат сведения, необходимые для восстановления всех транзакций в базе данных. Файлы данных могут быть объединены в файловые группы для удобства распределения и администрирования.

Файлы базы данных

SQL Server имеют три типа файлов.

Файл	Описание
Первичная	Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется расширение MDF.
Вторичная	Необязательные определяемые пользователем файлы данных. Данные могут быть распределены на несколько дисков, в этом случае каждый файл записывается на отдельный диск. Для имени вторичного файла данных рекомендуется расширение NDF.
Журнал транзакций	Журнал содержит информацию для восстановления базы данных. Для каждой базы данных должен существовать хотя бы один файл журнала. Для файлов журнала транзакций рекомендуется расширение LDF.

Например, простая база данных с именем Sales включает один первичный файл, содержащий все данные и объекты, и один файл журнала, содержащий сведения журнала транзакций. Более сложная база данных с именем Orders может содержать один первичный файл и пять вторичных файлов. Данные и объекты внутри базы данных распределяются по всем шести файлам, а четыре файла журнала содержат сведения журнала транзакций.

По умолчанию и данные, и журналы транзакций помещаются на один и тот же диск и имеют один и тот же путь для обработки однодисковых систем. Для производственных сред это может быть неоптимальным решением. Рекомендуется помещать данные и файлы журнала на разные диски.

Логические и физические имена файлов

Файлы SQL Server имеют два типа имен файлов.

logical_file_name: имя, используемое для ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных.

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

Дополнительные сведения об аргументах NAME и FILENAME см. в статье Параметры ALTER DATABASE ((Transact-SQL)) для файлов и файловых групп.

Файлы данных и файлы журналов SQL Server могут использоваться как в файловой системе FAT, так и в системе NTFS. В системах Windows рекомендуется использовать файловую систему NTFS по причинам ее большей безопасности.

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

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

Размер файла

Файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов.

Дополнительные сведения о страницах и их типах см. в разделе Руководство по архитектуре страниц и экстентов.

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

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

Файлы моментального снимка базы данных

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

Данные моментального снимка базы данных, созданного пользователем, хранятся в одном или нескольких разреженных файлах. Технология разреженных файлов является свойством файловой системы NTFS. Изначально разреженный файл не содержит данных пользователя, и место на диске под него не выделяется. Общие сведения об использовании разреженных файлов в моментальных снимках базы данных и о том, как растут моментальные снимки базы данных, см. в разделе Просмотр размера разреженного файла моментального снимка базы данных.
Моментальные снимки базы данных могут использоваться внутренними механизмами при выполнении определенных команд DBCC. Эти команды включают DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC и DBCC CHECKFILEGROUP. Внутренним моментальным снимком базы данных используются разреженные дополнительные потоки данных исходных файлов базы данных. Подобно разреженным файлам, дополнительные потоки данных являются свойством файловой системы NTFS. Использование разреженных дополнительных потоков данных позволяет связать несколько расположений данных с одним файлом или папкой, не затрагивая при этом размер файла или статистику тома.

Файловые группы

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

Например, Data1.ndf , Data2.ndf и Data3.ndf могут быть созданы на трех дисках соответственно и отнесены к файловой группе fgroup1 . В этом случае можно создать таблицу на основе файловой группы fgroup1 . Запросы данных из таблицы будут распределены по трем дискам, и это улучшит производительность. Подобного улучшения производительности можно достичь и с помощью одного файла, созданного на чередующемся наборе дискового массива RAID. Тем не менее файлы и файловые группы позволяют без труда добавлять новые файлы на новые диски.

Все файлы данных хранятся в файловых группах, перечисленных в следующей таблице.

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

Файловая группа по умолчанию (первичная)

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

Файловая группа PRIMARY является группой по умолчанию, если только она не была изменена инструкцией ALTER DATABASE. Системные объекты и таблицы распределяются внутри первичной файловой группы, а не новой файловой группой по умолчанию.

Файловая группа данных, оптимизированных для памяти

Дополнительные сведения об оптимизированных для памяти файловых группах см. в разделе Оптимизированные для памяти файловые группы.

Файловая группа файлового потока

Дополнительные сведения о файловых группах файлового потока см. в статьях FILESTREAM и Создание базы данных с поддержкой FILESTREAM.

Пример файлов и файловых групп

В следующем примере создается база данных на основе экземпляра SQL Server. База данных содержит первичный файл данных, пользовательскую файловую группу и файл журнала. Первичный файл данных входит в состав первичной файловой группы, а пользовательская файловая группа состоит из двух вторичных файлов данных. Инструкция ALTER DATABASE придает пользовательской файловой группе статус файловой группы по умолчанию. Затем создается таблица, определяющая пользовательскую файловую группу. (В этом примере используется универсальный путь к c:\Program Files\Microsoft SQL Server\MSSQL.1 , чтобы не указывать версию SQL Server.)

Данная иллюстрация обобщает все вышесказанное (кроме данных файлового потока).

Стратегия заполнения файлов и файловых групп

В файловых группах для каждого файла используется стратегия пропорционального заполнения. При записи данных в файловую группу компонент Компонент SQL Server Database Engine записывает в каждый файл количество данных, пропорциональное свободному пространству этого файла, вместо записи всех данных в первый файл до его заполнения. Затем запись производится в следующий файл. Например, если в файле f1 свободно 100 МБ, а в файле f2 — 200 МБ, то в файл f1 записывается одна часть данных, а в файл f2 — две части, и так далее. Таким образом, оба файла будут заполнены примерно в одно и то же время, и достигается простейшее распределение данных между хранилищами.

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

Правила проектирования файлов и файловых групп

Для файлов и файловых групп действуют следующие правила:

файл или файловая группа не могут использоваться несколькими базами данных. Например, файлы sales.mdf и sales.ndf, содержащие данные и объекты базы данных sales, не могут использоваться никакой другой базой данных.
файл может быть элементом только одной файловой группы;
файлы журнала транзакций не могут входить ни в какие файловые группы.

Создание таблицы FileTable

Таблица FileTable является специализированной пользовательской таблицей с заранее заданной и фиксированной схемой. Эта схема содержит данные FILESTREAM, сведения о файлах и каталогах и атрибуты файлов. Сведения о данном образце схемы см. в разделе FileTable Schema.

Новую таблицу FileTable можно создать с помощью языка Transact-SQL или среды SQL Server Management Studio. Поскольку таблица FileTable имеет фиксированную схему, нет необходимости указывать список столбцов. Простой синтаксис для создания таблицы FileTable позволяет указать следующее:

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

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

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

Как создать FileTable

Создание таблицы FileTable с помощью Transact-SQL
Создайте объект FileTable, вызвав инструкцию CREATE TABLE (Transact-SQL) с параметром AS FileTable. Поскольку таблица FileTable имеет фиксированную схему, нет необходимости указывать список столбцов. Можно указать следующие параметры для новой FileTable:

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

Это значение имеет тип данных nvarchar(255) и использует фиксированные параметры сортировки Latin1_General_CI_AS_KS_WS.

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

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

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

FILETABLE_COLLATE_FILENAME. Указывает имя параметров сортировки, применяемых к столбцу Name в таблице FileTable.

Указанные параметры сортировки должны быть нечувствительны к регистру , чтобы соответствовать семантике имен файлов Windows.

Если для параметра FILETABLE_COLLATE_FILENAME не указано значение или было задано значение database_default, столбец унаследует параметры сортировки текущей базы данных. Если в текущей базе данных используются параметры сортировки с учетом регистра, то операция CREATE TABLE завершится ошибкой.

Можно также указать имена, предназначенные для использования в трех первичных ключах и ограничениях уникальности, создаваемых автоматически. Если имена не будут предоставлены, то система сформирует имена, как описано ниже в этом разделе.

FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME

FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME

FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME

Примеры

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

В следующем примере также создается новый FileTable. Определяемые пользователем значения не заданы, поэтому значение FILETABLE_DIRECTORY становится именем таблицы FileTable, значение FILETABLE_COLLATE_FILENAME становится равным database_default, а первичный ключ и ограничения уникальности принимают значения, сформированные системой.

Создание таблицы FileTable в среде SQL Server Management Studio
В обозревателе объектов разверните объекты в выбранной базе данных, затем щелкните правой кнопкой мыши папку Таблицы и выберите Создать FileTable.

Этот параметр открывает новое окно скрипта, в котором содержится шаблон скрипта Transact-SQL, который можно настроить и выполнить с целью создания таблицы FileTable. Для простой настройки скрипта используйте параметр Указать значения для параметров шаблона в меню Запрос .

Требования и ограничения для создания FileTable

Существующую таблицу невозможно изменить, преобразовав ее в таблицу FileTable.

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

Поскольку таблица FileTable содержит столбец FILESTREAM, требуется действующая файловая группа FILESTREAM. При необходимости можно указать действующую файловую группу FILESTREAM в команде CREATE TABLE для создания таблицы FileTable. Если файловая группа не указана, то таблица FileTable использует файловую группу FILESTREAM по умолчанию для базы данных. Если база данных не содержит файловую группу FILESTREAM, то возникнет ошибка.

Невозможно создать ограничение таблицы в составе инструкции CREATE TABLE…AS FILETABLE. Однако можно добавить ограничение позже с помощью инструкции ALTER TABLE .

Невозможно создать FileTable в базе данных tempdb или любой другой системной базе данных.

Невозможно создать таблицу FileTable как временную таблицу.

изменение таблицы FileTable

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

Сведения об использовании инструкции ALTER TABLE для включения или отключения пространства имен FileTable, включая системные ограничения, см. в разделе Управление объектами FileTable.

Как изменить каталог для таблицы FileTable

Изменение каталога для таблицы FileTable с помощью Transact-SQL
Вызовите инструкцию ALTER TABLE и задайте новое допустимое значение параметра FILETABLE_DIRECTORY SET.

Пример

Изменение каталога для таблицы FileTable с помощью среды SQL Server Management Studio
Щелкните правой кнопкой мыши таблицу FileTable в обозревателе объектов и выберите пункт Свойства , чтобы открыть диалоговое окно Свойства таблицы . На странице FileTable введите новое значение для свойства Имя каталога FileTable.

Требования и ограничения для изменения FileTable

Изменить значение FILETABLE_COLLATE_FILENAME невозможно.

Нельзя изменить, удалить или отключить системные столбцы в таблице FileTable.

Нельзя добавлять новые пользовательские столбцы, вычисляемые или материализованные вычисляемые столбцы в таблицу FileTable.

удаление таблицы FileTable

Таблицу FileTable можно удалить с помощью обычного синтаксиса инструкции DROP TABLE (Transact-SQL).

При удалении таблицы FileTable также удаляются следующие объекты:

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

Каталог таблицы FileTable и вложенные каталоги удаляются из файла FILESTREAM и иерархии каталогов базы данных.

Команда DROP TABLE завершается ошибкой, если в пространстве имен файлов FileTable имеются открытые дескрипторы файлов. Сведения о закрытии открытых дескрипторов см. в разделе Управление объектами FileTable.

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

При создании новой таблицы FileTable также создаются некоторые системные индексы и ограничения. Эти объекты нельзя изменять или удалять, они исчезают только после удаления самой таблицы FileTable. Чтобы просмотреть список этих объектов, отправьте запрос представлению каталога sys.filetable_system_defined_objects (Transact-SQL).

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

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

Язык 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:

Чтобы иметь возможность только читать данные типа 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 (Дополнительно):

После этого в правой панели из выпадающего списка выберите 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 для добавления проверочного ограничения и определения первичного ключа таблицы:

Ограничения для обеспечения целостности можно удалить посредством предложения 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 можно указывать, среди прочих, следующие объекты:

Читайте также: