Изменить файловую группу таблицы sql
У меня есть SQL Server 2008 Ent и OLTP database с двумя большими столами. Как я могу переместить эти столы в другой filegroup без прерывания обслуживания? Теперь в эти таблицы вставлено около 100–130 записей и каждую секунду обновляется 30–50 записей. Каждая таблица содержит около 100 миллионов записей и шесть field (включая одно поле geography ).
Ищу решение в Google, но все решения содержат
создать вторую таблицу, вставить строки из первой таблицы, удалить первую таблицу и т. д.
Могу ли я использовать функции разбиения для решения этой проблемы?
Если вы хотите просто переместить таблицу в новую файловую группу, вам необходимо воссоздать кластерный индекс таблицы (в конце концов: кластерный индекс - это данные таблицы) в новой файловой группе, которую вы хотите.
Вы можете сделать это, например:
Или если ваш кластерный индекс уникален :
Это создает новый кластеризованный индекс и удаляет существующий, а также создает новый кластеризованный индекс в указанной вами группе файлов - и вуаля, данные вашей таблицы были перемещены в новую файловую группу.
Подробную информацию обо всех доступных вариантах, которые вы можете хочу уточнить.
Это, конечно, еще не касается разделения, но это уже отдельная история .
Чтобы ответить на этот вопрос, сначала мы должны понять
- Если таблица не имеет индекса, ее данные называются кучей .
- Если таблица имеет кластеризованный индекс, этот индекс фактически является данными вашей таблицы. Следовательно, если вы переместите кластерный индекс, вы также переместите свои данные.
Первый шаг - узнать больше о таблице, которую мы хотим переместить. Мы делаем это, выполняя этот T-SQL:
Вывод покажет вам столбец под названием «Data_located_on_filegroup». Это удобный способ узнать, в какой файловой группе находятся данные вашей таблицы. Но более важен вывод, который показывает вам информацию об индексах таблицы. (Если вы хотите увидеть информацию только об индексах таблиц, просто запустите sp_helpindex N'>' ). Ваша таблица может иметь 1) без индексов (так что это куча), 2) один индекс или 3) несколько индексов. Если index_description начинается с «кластеризованный, уникальный, . », это индекс, который вы хотите переместить. Если индекс также является первичным ключом, это нормально, вы все равно можете его переместить.
Чтобы переместить индекс, запишите index_name и index_keys, показанные в результатах приведенного выше справочного запроса, затем используйте их для заполнения > в следующем запросе:
Параметры DROP EXISTING, ONLINE выше важны. DROP EXISTING следит за тем, чтобы индекс не дублировался, а ONLINE сохраняет таблицу в оперативном режиме, пока вы ее перемещаете (теперь доступно только в версиях Enterprise).
Если перемещаемый индекс не кластеризованный, замените UNIQUE CLUSTERED выше на NONCLUSTERED
Чтобы переместить таблицу кучи, добавьте к ней кластеризованный индекс, затем запустите указанную выше инструкцию, чтобы переместить ее в другую файловую группу, а затем отбросьте индекс.
Теперь вернитесь и запустите sp_help для своей таблицы и проверьте результаты, чтобы увидеть, где теперь находятся данные вашей таблицы и индекса.
Если ваша таблица имеет более одного индекса , то после выполнения вышеуказанного оператора для перемещения кластеризованного индекса sp_helpindex покажет, что ваш кластерный индекс находится в новой файловой группе, но все оставшиеся индексы по-прежнему будет в исходной файловой группе. Таблица продолжит нормально функционировать, но у вас должна быть веская причина, по которой вы хотите, чтобы индексы располагались в разных файловых группах. Если вы хотите, чтобы таблица и все ее индексы находились в одной файловой группе, повторите приведенные выше инструкции для каждого индекса, при необходимости заменяя CREATE [NONCLUSTERED, or other] . DROP EXISTING. , в зависимости от типа перемещаемого индекса.
Разделение - одно из решений, но вы можете «переместить» кластерный индекс в новую файловую группу без прерывания обслуживания (при определенных условиях, см. Ссылку ниже), используя
Кластерный индекс - это данные, и это то же самое, что и перемещение файловой группы.
Это зависит от того, кластеризован ваш первичный ключ или нет, что меняет то, как мы это будем делать.
Как уже говорили другие друзья, например, принятый ответ от marc_s, следующий снимок экрана дает вам другой способ сделать это с помощью графического интерфейса SSMS.
Обратите внимание, что вы можете легко перейти к другой файловой группе свойства index на вкладке хранилища
Обратите внимание, что при воссоздании кластерного индекса перемещаются только «примитивные» столбцы, такие как int, bit, datetime и т. Д.
Чтобы переместить varchar(max), varbinary и другие столбцы "blob", вы должны воссоздать таблицу. К счастью, в SSMS есть способ сделать это полуавтоматически - изменив «текстовую файловую группу» в окне «Дизайн» таблицы, а затем сохранив изменения.
(Быстрое обновление с 2021 года): в качестве альтернативы вы можете создать временное правило «разделения» (правило разделения - это функция, которая решает, в какую файловую группу попадают данные), которое будет указывать на новую файловую группу для всех значений в таблице. Применение этой схемы разделения фактически переместит данные
ПРИМЕЧАНИЕ. Перемещение таблицы в другую файловую группу работает только с Enterprise Edition.
Шаг 1:
Проверьте, какая таблица файловой группы находится:
Шаг 2:
Переместить существующую таблицу / таблицы в новую файловую группу
Если файловая группа, в которую вы хотите переместить таблицу, еще не существует, создайте дополнительную файловую группу, а затем переместите таблицу.
Чтобы переместить таблицу в другую файловую группу, необходимо переместить кластерный индекс таблицы в новую файловую группу. Конечный уровень кластеризованного индекса фактически содержит данные таблицы. Таким образом, перемещение кластеризованного индекса можно выполнить одним оператором с помощью предложения DROP_EXISTING следующим образом:
Шаг 3:
Переместите оставшиеся некластеризованные индексы во вторичную файловую группу
Вам необходимо переместить некластеризованные индексы вручную, используя указанный ниже синтаксис:
Перемещение кучи в другую файловую группу:
Насколько я знаю, единственный способ переместить кучу в другую файловую группу - это временно добавить кластерный индекс в новую файловую группу, а затем удалить его (при необходимости).
Я думаю, что эти шаги очень просты и понятны для перемещения любой таблицы в другую группу файлов (через Management Studio):
Переместите все некластеризованные индексы в новую группу файлов, просто изменив свойство FileGroup для каждого индекса
Измените индекс кластера на некластерный и просто измените его группу файлов (как на предыдущем шаге)
Добавьте новый временный индекс кластера с «новой группой файлов» с помощью этой команды (или через IDE):
(приведенная выше команда приводит к перемещению всех данных в новую группу файлов)
Удалите указанный выше временный ПК (когда он отлично выполняет свою работу!)
Измените свой основной индекс кластера снова на индекс кластера (снова через IDE)
Преимущество вышеперечисленных шагов заключается в том, что не нужно отказываться от существующих отношений FK. Также использование IDE предотвращает потерю данных в условиях ошибки.
ПРИМЕЧАНИЕ: убедитесь, что дисковая квота не включена для вашей файловой группы, или установите ее правильно. В противном случае вы получите исключение "файловая группа заполнена"!
В SSMS разверните «Таблицы», разверните таблицу, которую вы хотите переместить, разверните «Индексы», щелкните правой кнопкой мыши кластерный индекс, выберите «Сценарий индексирования как» -> «Перетащить и создать в»
Это откроет окно запроса со сценарием, чтобы удалить кластерный индекс и создать новый с теми же характеристиками, что и исходный.
В окне запроса в операторе «ALTER TABLE <> ADD CONSTRAINT» измените имя файловой группы после ключевого слова «ON» в конце оператора, например если таблица находится в ПЕРВИЧНОЙ файловой группе и вы хотите перейти в файловую группу с именем «ВТОРИЧНАЯ», измените «ВКЛ [ПЕРВИЧНАЯ]» на «ВКЛ [ВТОРИЧНАЯ]». Также измените «ONLINE = OFF» на «ONLINE = ON», если вы хотите, чтобы стол оставался в сети.
Выполните сценарий, и он отбросит оригинал и создаст новый в данной файловой группе.
Добавляет файловую группу в схему секционирования или изменяет обозначение файловой группы NEXT USED для данной схемы секционирования.
В базе данных SQL Azure поддерживаются только первичные файловые группы.
Примеры
В следующем примере предполагается, что в базе данных существуют схема секционирования MyRangePS1 и файловая группа test5fg .
Файловая группа test5fg получает любые дополнительные секции из секционированной таблицы или индекс как результат выполнения инструкции ALTER PARTITION FUNCTION.
У меня есть SQL Server 2008 Ent и база данных OLTP с двумя большими таблицами. Как я могу переместить эти таблицы в другую файловую группу без прерывания службы? Теперь, около 100-130 вставленных записей и 30-50 записей обновляются каждую секунду в этой таблице. Каждая таблица имеет около 100 миллионов записей и шесть полей (включая поле географии).
Я ищу решение через google, но все решения содержат "создать вторую таблицу, вставить строки из первой таблицы, падение первой таблицы, бла-бла бла".
могу ли я использовать функции разбиения для решения этой проблемы? Спасибо.
Если вы хотите просто переместить таблицу в новую файловую группу, вам нужно воссоздать кластеризованный индекс в таблице (в конце концов: кластеризованный индекс is данные таблицы) в новой файловой группе, которую вы хотите.
Вы можете сделать это с например:
или если ваш кластеризованный индекс уникальный:
это создает новый кластеризованный индекс и отбрасывает существующий, а также создает новый кластеризованный индекс в файловой группе, которую вы указанный - et вуаля, данные таблицы были перемещены в новую файловую группу.
посмотреть документы MSDN для создания индекса для получения подробной информации о всех доступных параметрах, которые вы можете указать.
Это, конечно, еще не связано с разделением, но это совсем другая история для себя.
чтобы ответить на этот вопрос, сначала мы должны понять
- если таблица не имеет индекса, ее данные называются кучу
- если таблица имеет кластеризованный индекс, этот индекс фактически табличных данных. Поэтому при перемещении кластеризованного индекса также будут перемещены данные.
первый шаг-узнать больше информации о таблице, которую мы хотим переместить. Мы делаем это, выполняя это T-SQL:
вывод покажет вам столбец под названием " Data_located_on_filegroup."Это удобный способ узнать, в какой файловой группе находятся данные вашей таблицы. Но более важным является вывод, который показывает вам информацию об индексах таблицы. (Если вы хотите видеть только информацию об индексах таблицы, просто запустите sp_helpindex N'>' ) в вашей таблице может быть 1) нет индексов (так что это куча), 2) один индекс или 3) несколько индексов. Если index_description начинается с ' кластеризованный, уникальный, . - это индекс, который вы хотите переместить. Если индекс также является первичным ключом, это нормально, вы все равно можете переместить его.
чтобы переместить индекс, запишите index_name и index_keys, показанные в результатах вышеуказанного запроса справки, затем используйте их для заполнения > в следующем запросе:
на DROP EXISTING, ONLINE параметры выше важны. DROP EXISTING убедитесь, что индекс не дублируется, и ONLINE держит таблицу онлайн пока вы двигаете он.
если индекс, который вы перемещаете,не кластеризованный индекс, а затем заменить UNIQUE CLUSTERED выше NONCLUSTERED
чтобы переместить таблицу кучи, добавьте к ней кластеризованный индекс, затем запустите приведенную выше инструкцию, чтобы переместить ее в другую файловую группу, а затем удалите индекс.
теперь возвращайся и беги sp_help на вашем столе, и проверьте результаты, чтобы увидеть, где ваши данные таблицы и индекса теперь находится.
если ваша таблица имеет более одного индекс, затем после выполнения вышеуказанного оператора для перемещения кластеризованного индекса, sp_helpindex покажет, что ваш кластеризованный индекс находится в новой файловой группе, но все остальные индексы по-прежнему будут в исходной файловой группе. Таблица будет продолжать нормально функционировать, но у вас должна быть веская причина, почему вы хотите, чтобы индексы находились в разных файловых группах. Если вы хотите, чтобы таблица и все ее индексы были в одной файловой группе, повторите приведенные выше инструкции для каждого индекса, подставляя CREATE [NONCLUSTERED, or other] . DROP EXISTING. при необходимости, в зависимости от типа индекса, который вы перемещаете.
секционирование-это одно из решений, но вы можете "переместить" кластеризованный индекс в новую файловую группу без прерывания обслуживания (при соблюдении некоторых условий см. ссылку ниже) с помощью
кластерный индекс is данные, и это то же самое, что и перемещение файловой группы.
Это зависит от того, кластеризован ли ваш первичный ключ или нет, что меняет то, как мы это сделаем
Как уже говорили другие друзья, такие как принятый ответ marc_s, следующий скриншот дает вам другой способ сделать это с помощью SSMS GUI.
обратите внимание, что вы можете легко перейти в другую файловую группу свойства index на вкладке storage
Примечание: перемещение таблицы в другую файловую группу работает только с Enterprise Edition.
Шаг 1 :
проверьте, в какой таблице файловой группы находится:
Шаг 2 :
переместить существующую таблицу / таблицы в новую файловую группу
если файловая группа, в которую вы хотите переместить таблица to еще не существует, затем создайте вторичную файловую группу, а затем переместите таблицу.
для перемещения таблицы в другую файловую группу необходимо переместить кластеризованный индекс таблицы в новую файловую группу. Конечный уровень некластеризованного индекса содержит табличные данные. Таким образом, перемещение кластеризованного индекса можно сделать в одном операторе, используя предложение DROP_EXISTING следующим образом:
Шаг 3:
переместить оставшиеся некластеризованные индексы во вторичную файловую группу
вы должны переместить некластеризованные индексы вручную, используя следующий синтаксис:
перемещение кучи в другую файловую группу:
как я знаю, единственный способ переместить кучу в другую файловую группу-временно добавить кластеризованный индекс в новую файловую группу, а затем удалить его (при необходимости).
Я думаю, что эти шаги очень просты и прямо вперед, чтобы переместить любую таблицу в другую файловую группу (через Management Studio):
переместите все некластеризованные индексы в новую файловую группу, просто изменив свойство FileGroup для каждого индекса
измените индекс кластера на некластерный и измените его файловую группу просто (как на предыдущем шаге)
Добавить новый временный индекс кластера с "новый файл группа " через эту команду (или через IDE) :
(вышеуказанная команда заставляет переместить все данные в новую файловую группу)
удалить вышеуказанный временный ПК (когда он делает свою работу превосходно!)
измените свой основной индекс кластера, чтобы снова стать индексом кластера (через IDE снова)
преимущество вышеуказанных шагов-не нужно отбрасывать существующие отношения FK. Также используя IDE предотвращает потери данных в ошибка условия.
Примечание: убедитесь, что дисковая квота не включена для вашей файловой группы или установить ее ocrrectly. В противном случае вы получите исключение" файловая группа заполнена"!
Я только что создал вторичную файловую группу и хочу переместить в нее некоторые таблицы, а затем сделать ее доступной только для чтения.
Я не уверен, как это сделать?
Я просто использовать ALTER blah blah TO MyFileGroup ?
что-то вроде этого должно помочь:
существует два способа; один из SSMS, а другой с помощью TSQL.
из SQL Server 2008 книги в Интернете:
в Обозревателе объектов подключитесь к экземпляру базы данных SQL Server Engine, а затем разверните этот экземпляр.
развернуть базы данных, развернуть базу данных, содержащую таблицу с этот определенный индекс, а затем разверните Таблицы.
разверните таблицу, в которой принадлежит индекс, а затем разверните индексы.
щелкните правой кнопкой мыши индекс для перемещения и выберите Свойства.
в диалоговом окне Свойства индекса выберите страницу хранилище.
выберите файловую группу, в которую необходимо переместить указатель.
[Pre SQL Server 2008: размещение существующей таблицы в другой файловой группе (SSMS)
- разверните узел базы данных, разверните базу данных, содержащую таблицу, и нажмите кнопку таблицы.
- в области сведений щелкните таблицу правой кнопкой мыши и выберите команду таблица конструктора.
- щелкните правой кнопкой мыши любой столбец и выберите пункт Свойства.
- на вкладке таблицы в списке файловая группа таблицы выберите файловую группу, в которую таблица.
- дополнительно в списке текстовая файловая группа выберите файловую группу, в которую будут помещены столбцы текста, изображения и текста. ]
более эффективным способом является
для создания кластеризованного индекса на таблица. Если в таблице уже есть кластеризованный индекс, вы можете использовать Создать команду INDEX с помощью Предложение drop_existing воссоздать кластеризованный индекс и переместить его в конкретной группе. Когда таблица имеет ля кластеризованный индекс, уровень листа индекс и страницы данных таблица, по сути, стал одним и тот же. Таблица должна существовать там, где кластеризованный индекс существует, поэтому если вы создание или воссоздание кластера index-размещение индекса на a особую группу вы двигаетесь таблица для новой файловой группы.
вы можете создать (или воссоздать) кластеризованный индекс, указав новую файловую группу для Предложение ON и это переместит таблицу (данные). Но если у вас есть некластеризованные индексы и вы хотите, чтобы они были в другой файловой группе, вы должны удалить и воссоздать их, указав новую файловую группу в предложении ON. Ref.
Я согласен с Марком. Rebuild cluster работает хорошо и является много быстрее, чем задачи->сжать базу данных->файлы-> Empty file by migrating data to another file.
создание индексов индивидуально позволяет контролировать нагрузку на систему без создания огромных файлов журнала из select * into [your.new.table.] from .
Язык Transact-SQL поддерживает изменение структуры следующих объектов базы данных:
В последующих двух разделах описывается изменение первых двух объектов из этого списка: баз данных и таблиц. Изменение структуры последних четырех объектов этого списка будет описано при их обсуждении в следующих статьях.
Автономные базы данных
Одна из значительных проблем с базами данных 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 позволяет осуществлять следующие виды изменений таблиц:
добавлять и удалять столбцы;
изменять свойства столбцов;
добавлять и удалять ограничения для обеспечения целостности;
разрешать или отключать ограничения;
переименовывать таблицы и другие объекты базы данных.
Эти типы изменений рассматриваются в последующих далее разделах.
Установка опций базы данных
Для установки различных опций базы данных используется предложение 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 управляют процессом восстановления базы данных.
Переименование таблиц и других объектов баз данных
Для изменения имени существующей таблицы (и любых других объектов базы данных, таких как база данных, представление или хранимая процедура) применяется системная процедура sp_rename. В примере ниже показано использование этой системной процедуры:
Использовать системную процедуру sp_rename настоятельно не рекомендуется, поскольку изменение имен объектов может повлиять на другие объекты базы данных, которые ссылаются на них. Вместо этого следует удалить объект и воссоздать его с новым именем.
Добавление и удаление столбцов
Чтобы добавить новый столбец в существующую таблицу, в инструкции ALTER TABLE используется предложение ADD. В одной инструкции ALTER TABLE можно добавить только один столбец. Применение предложения ADD показано в примере ниже:
В этом примере инструкция ALTER TABLE добавляет в таблицу Employee столбец PhoneNumber. Компонент Database Engine заполняет новый столбец значениями NULL или IDENTITY или указанными значениями по умолчанию. По этой причине новый столбец должен или поддерживать значения NULL, или для него должно быть указано значение по умолчанию.
Новый столбец нельзя вставить в таблицу в какой-либо конкретной позиции. Столбец, добавляемый предложением ADD, всегда вставляется в конец таблицы.
Столбцы из таблицы удаляются посредством предложения DROP COLUMN. Применение этого предложения показано в примере ниже:
В этом коде инструкция ALTER TABLE удаляет в таблице Employee столбец PhoneNumber, который был добавлен в эту таблицу предложением ADD ранее.
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Изменение свойств файлов и файловых групп
С помощью предложения MODIFY FILE можно выполнять следующие действия по изменению свойств файла:
изменять логическое имя файла, используя параметр NEWNAME;
увеличивать значение свойства SIZE;
изменять значение свойств FILENAME, MAXSIZE и FILEGROWTH;
отмечать файл как OFFLINE.
Подобным образом с помощью предложения MODIFY FILEGROUP можно выполнять следующие действия по изменению свойств файловой группы:
изменять логическое имя файловой группы, используя параметр NAME;
помечать файловую группу, как файловую группу по умолчанию, используя для этого параметр DEFAULT;
помечать файловую группу как позволяющую осуществлять доступ только для чтения или для чтения и записи, используя для этого параметр read_only или read_write соответственно.
Изменение свойств столбцов
Для изменения свойств существующего столбца применяется предложение ALTER COLUMN инструкции ALTER TABLE. Изменению поддаются следующие свойства столбца:
поддержка значения NULL.
Применение предложения ALTER COLUMN показано в примере ниже:
Инструкция ALTER TABLE в этом примере изменяет начальные свойства (nchar(40), значения NULL разрешены) столбца Location таблицы Department на новые (nchar(25), значения NULL не разрешены).
Добавление и удаление файлов базы данных, файлов журналов и файловых групп
Добавление или удаление файлов базы данных осуществляется посредством инструкции 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. Как и удаляемый файл, удаляемая файловая группа также должна быть пустой.
Аргументы
partition_scheme_name
Имя изменяемой схемы секционирования.
filegroup_name
Указывает файловую группу, которую требуется пометить для схемы секционирования как NEXT USED. Это означает, что файловая группа примет новое секционирование, созданное с помощью инструкции ALTER PARTITION FUNCTION.
В схеме секционирования только одна файловая группа может быть отмечена как NEXT USED. Можно указать пустую файловую группу. Если указан аргумент filegroup_name и ни одна файловая группа не является в данный момент NEXT USED, то группа filegroup_name помечается как NEXT USED. Если указан аргумент filegroup_name и файловая группа с пометкой NEXT USED уже существует, то свойство NEXT USED переносится от текущей файловой группы к группе filegroup_name.
Если аргумент filegroup_name не указан и файловая группа с пометкой NEXT USED уже существует, эта файловая группа теряет состояние NEXT USED, чтобы в схеме секционирования partition_scheme_name не осталось файловой группы NEXT USED.
Если аргумент filegroup_name не указан и ни одна файловая группа не отмечена как NEXT USED, инструкция ALTER PARTITION SCHEME возвращает предупреждение.
Разрешения
Для выполнения инструкции ALTER PARTITION SCHEME необходимы следующие разрешения.
Разрешение ALTER ANY DATASPACE. Это разрешение назначено по умолчанию членам предопределенной роли сервера sysadmin и предопределенных ролей базы данных db_owner и db_ddladmin .
Разрешение CONTROL или ALTER на базу данных, в которой была создана схема секционирования.
Разрешения CONTROL SERVER или ALTER ANY DATABASE на сервер базы данных, в которой была создана схема секционирования.
Разрешение и запрещение ограничений
Как упоминалось ранее, ограничение для обеспечения целостности всегда имеет имя, которое может быть объявленным или явно посредством опции CONSTRAINT, или неявно посредством системы. Имена всех ограничений таблицы (объявленных как явно, так и неявно) можно просмотреть с помощью системной процедуры sp_helpconstraint.
В последующих операциях вставки или обновлений значений в соответствующий столбец ограничение по умолчанию обеспечивается принудительно. Кроме этого, при объявлении ограничения все существующие значения соответствующего столбца проверяются на удовлетворение условий ограничения. Начальная проверка не выполняется, если ограничение создается с параметром WITH NOCHECK. В таком случае ограничение будет проверяться только при последующих операциях вставки и обновлений значений соответствующего столбца. (Оба параметра - WITH CHECK и WITH NOCHECK - можно применять только с ограничениями проверки целостности CHECK и проверки внешнего ключа FOREIGN KEY.)
В примере ниже показано, как отключить все существующие ограничения таблицы:
Все ограничения таблицы Sales отключаются посредством ключевого слова ALL. Применять опцию NOCHECK не рекомендуется, поскольку любые подавленные нарушения условий ограничения могут вызвать ошибки при будущих обновлениях.
Удаление объектов баз данных
Все инструкции Transact-SQL для удаления объектов базы данных имеют следующий общий вид:
Для каждой инструкции CREATE object для создания объекта имеется соответствующая инструкция DROP object для удаления. Инструкция для удаления одной или нескольких баз данных имеет следующий вид:
Эта инструкция безвозвратно удаляет базу данных из системы баз данных. Для удаления одной или нескольких таблиц применяется следующая инструкция:
При удалении таблицы удаляются все ее данные, индексы и триггеры. Но представления, созданные по удаленной таблице, не удаляются. Таблицу может удалить только пользователь, имеющий соответствующие разрешения.
Кроме объектов DATABASE и TABLE, в параметре objects инструкции DROP можно указывать, среди прочих, следующие объекты:
Хранение данных типа 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.
Комментарии
Все файловые группы, на которые действует инструкция ALTER PARTITION SCHEME, должны быть в режиме "в сети".
Добавление и удаления ограничений для обеспечения целостности (ключей и проверок)
Для добавления в таблицу новых ограничений для обеспечения целостности используется параметр ADD CONSTRAINT инструкции ALTER TABLE. В примере ниже показано использование параметра ADD CONSTRAINT для добавления проверочного ограничения и определения первичного ключа таблицы:
Ограничения для обеспечения целостности можно удалить посредством предложения DROP CONSTRAINT инструкции ALTER TABLE, как это показано в примере ниже:
Определения существующих ограничений нельзя модифицировать. Чтобы изменить ограничение, его сначала нужно удалить, а потом создать новое, содержащее требуемые модификации.
Изменение базы данных
Для изменения физической структуры базы данных используется инструкция ALTER DATABASE. Язык Transact-SQL позволяет выполнять следующие действия по изменению свойств базы данных:
добавлять и удалять один или несколько файлов базы данных;
добавлять и удалять один или несколько файлов журнала;
добавлять и удалять файловые группы;
изменять свойства файлов или файловых групп;
устанавливать параметры базы данных;
изменять имя базы данных с помощью хранимой процедуры sp_rename.
Эти разные типы модификаций базы данных рассматриваются далее.
Читайте также: