Ms sql удалить файл
Платформа 1С 8.2, в базе есть примерно 68 млн. документов одного вида, без табличных частей, около 35 млн. из них надо удалить с отбором по дате меньше определенной, ссылочная целостность не важна. Время простоя базы ограничено. Понятно, что средствами 1С быстро это сделать нереально. Как сделать это максимально быстро? Удалять в одной транзакции или бить на несколько, если на несколько, то как определить оптимальный с точки зрения скорости размер порции?
(4) Один из вариантов, да. Но и тут можно либо одной транзакцией сделать, либо несколькими. Как быстрее, вот в чем вопрос.
(10) Значит уменьшайте до 50-30-20-10-5-1 тысяч и опытным путем находите подходящий размер порции.
Индексы именно для такого удаления есть? Или просто "какие-то индексы" есть? План запроса на удаление смотрели?
(11) Это я про средствами 1С, такто через ADODB одной транзакцией за 2 часа удалилось, но хочется еще быстрее :) Индексы специальные для этого разового удаления делать както не очень хочется.
(14) Точно быстрее будет, чем одной транзакцией? По месяцам это будет 15 транзакций, запустил на тестовой, сравню время с удалением одной транзакцией. Но интересно не только из опыта, но и теоретически понять как быстрее всего решить такую задачу.
(13)Если хочется быстрее, то надо нарисовать процедуру по удалению порциями с транзакциями для каждой порции. В которой, может быть, и создавать временно нужный индекс. Если нет подходящего. И размер порции подобрать эмпирически. И эту процедуру запускать через ADO.
(15) когда в одной транзакции - не знаешь умер процесс или шевелится и ресурсов много надо.
а когда квантами - сразу видно, что процесс идет:)
а движения почему не удаляешь ?
(17) Конфигурация нетиповая, движения только по одному регистру накопления с одним регистратором, почистил раньше уже.
в рабочей базе - truncate table, а потом из копии в нее bulk insert нужного
(0) Быстрое в ы б о р о ч н о е удаление большого количества записей из раздутого регистра!? Нет сынок, это фантастика - намучились вволю уже. Если подскажете по-настоящему быстрый способ буду благодарен. (20) точно не быстро.
(24) delete - фигня по скорости, пробовал уже. Я бы (19) попробовал - вот bulk insert может ускорить.
(30) Ну в теории можно выгрузить в файл - из файла bulk insert, только не факт, что по времени это быстрее будет.
Вариант - ПодключитьОбработчикОжидания. Интервал вызова и количество документов на удаление обработка читает каждый раз из регистра сведений - можно подбирать нагрузку, чтоб и процесс шел, и пользователи работали. Иногда только так выкручиваемся, когда надо изменять большие объемы в фоновом режиме.
(0) Поставь платформу 8.3 ))) попробуй как удаляет. (офигеешь от разницы в скорости)
Попробуй грохнуть в файловом варианте.
1. Выгрузка данных в промежуточную таблицу (те что надо оставить) truncate потом таблицы. и те что оставил заливаешь обратно
2. delete from tab where tab.date < date
(39) Фоновый режим не вариант, есть 3-4 часа допустимого простоя системы, надо в них уложиться. Пока укладываюсь, но на грани, что-то пойдет не так и проблемы обеспечены.
(43) Можно, но в этой задаче не нужно :) Да и потом статистики всеравно пересчитывать, можно словить тормоза на неактуальных статистиках.
(47) Да, рабочий сервер на SSD да и памяти на скуле побольше, надеюсь раза в 2 быстрее, чем на тестовом будет. Но нет пределов совершенству :)
(10)если эти 68 млн Г копились столько лет и никому не мешали - ничего не мешает их так же потихоньку удалять.
каждый день по 100 тыс и все .
(49) Ну да, если при работе пользователей удалять много записей, кривая статистика на работе этих пользователей может сказаться отрицательно.
теперь запускаем это периодично, когда нагрузка на систему ниже определенной психологической отметки.
Если пользователи не работают, то можно грохнуть перед удалением все лишние индексы ( сильно ускорит, так как в индексах так же проставляется пометка удаления ), оставить только нужный для отбора.
(59) А вот это интересно, попробую. Там еще хинт TABLOCK попробовать советуют. А SET NO_BROWSETABLE ON можно через ADODB сделать, или только в менеджмент студии?
(0)
Выбери во временную таблицу, те документы, которые нужно оставить, сделай truncate основной, вставь из временной таблицы обратно.
(64) Выборка может быть медленной ( если объём ), а потом ещё и вставка ( индекс так же будет вставляться ). Проще как я описал. И после удаления пересоздать удалённые индексы ( можно уже руками, предварительно сделав дефрагментацию ).
>2 млн записей удаляется 8 минут.
(70) Ты все индексы удалил кроме одного где Дата + Ссылка ( Дата в индексе должна быть первой )?
Можно ещё статистику подсобрать после удаления индексов приблизительную. И сообщи какие у тебя условия в where. И зачем тебе транзакция в этом действии ( ты хочешь чтобы пользователи в этот момент работали? )
(15) "2 млн записей удаляется 8 минут.
нормальный результат 15 летней давности когда запись 20 МБ/сек была нормальной. сейчас ~30 сукунд.
(71) Не, индексы не удалял. Транзакция для надежности, вдруг что-нибудь пойдет не так. Хотя, наверное можно и без транзакции, если подумать. Кстати, в менеджмент студии Delete выполняется по умолчанию в транзакции или нет?
(72) Замедление из-за перестроения индексов в общем нормальное предположение, склонен с ним согласится.
Что касается ресурсов вопрос интересный конечно, недостатка ресурсов нет однозначно, насчет избытка не уверен.
(75) Нужно удалить индексы. Они удаляются пометкой удаления без физического удаления. Транзакция создаёт лишний объём работы, да и не нужна она в общем-то ( актуально для oracle с его undotablespace. Скорее всего для M$ так же хотя и не уверен, так как M$ всё равно в лог всё запишет и восстановление идёт из него если что. Транзакция влияет на блокировки ). DELETE работает без транзакции но в лог попадает разумеется, возможно режим логирования SIMPLE уменьшит объём логов ( не уверен. Надеюсь log файл на отдельном диске ). После удаления просто командой DML - CREATE INDEX создаёшь удалённые индексы. Время должно уменьшиться серьёзно, особенно если индексов много, да и индексные файлы сами по себе большие из-за большой таблицы. При относительно маленьком объёме оперативки может происходить много I/O.
(76) из Ваших вопросов ясно, что у Вас не понимания эксплуатации субд.
поэтому пользуйтесь простыми методами. Вам тут не расскажут
главы из руководства субд про массовые вставки , удаления обновления.
уточню (74) если с таблицами не работают реальные
много пользователей (olap) то одни варианты , иначе - другие.
(77) + Приведи что в конструкции после WHERE. Если удаление диапазонами, то оставь 1 индекс, где поле Дата будет первым полем и количество полей в индексе минимально.
(83) в (0) Время простоя базы ограничено
в (41) есть 3-4 часа допустимого простоя системы, надо в них уложиться.
(82) OLAP это аналитика, для неё обычно данные переносятся в другое хранилище, да и нагрузка не такая как на OLTP, так как данные в OLAP обычно агрегируются.
И да после удаления такого объёма нужно как минимум перестраивать индексы данной таблицы + статистику для ускорения доступа ( запросы )
(77) Да, удаление индексов помогло - оставил только кластерный и по периоду, и вместо 8 минут удаление прошло за минуту примерно. Скрипты на удаление и создание индексов очень просто делаются из менеджмент студии, создание индексов делается достаточно быстро.
(79) Да, вы правы, эксплуатацией СУБД занимается отдельный человек, просто для себя интересно чуть глубже понимать механизмы. Главы пересказывать не надо, надо просто подтолкнуть в правильном направлении :)
(89) Можно, но и так вроде неплохо. Сейчас протестирую удаление одним запросом, замерю время создания индексов, и будет понятно стоит ли дальше оптимизировать.
(90) Только что делать, если таблиц - десяток. Руками с ума сойдешь индексы удалять/пересоздавать :(
А то мне предстоит скоро таблицы на сотни миллионов записей чистить.
(91) Да скрипт готовишь используя SQL Server Management Studio. Можно ещё программно из 1С скрипт подготовить, используя ПолучитьСтруктуруХраненияБазыДанных
(90) По итогу - 30 млн записей удалились за 16 минут, 28 минут на пересоздание индексов. Дальше оптимизировать смысла не вижу.
(92) Ну да, для одного индекса получить скрипт на drop/create, дальше по аналогии для всех нужных индексов делаешь, и через ADODB запускаешь итоговый скрипт.
SQL Server databases store their data and objects in files. Each database has at least one data file (and a log file), but it’s not restricted to just one – it could have many data files. If you ever find yourself in the situation where you need to remove a data file from a database, you’ll need to empty that file first, before removing it.
But don’t worry, emptying a file doesn’t actually delete the data. It simply migrates the file’s data to other files within the same filegroup.
The examples below demonstrate how to empty a data file, then remove it from the database using Transact-SQL.
Quick Example
Here’s a quick example to show you how to empty a data file and remove it from the database:
So as explained, this empties the file, then removes it completely from the database. When you use EMPTYFILE , this moves all data from the file to other files in the same filegroup. Therefore, you’ll need to make sure this isn’t the only file in the filegroup (otherwise you’ll get an error). EmptyFile also assures you that no new data will be added to the file.
A Longer Example
In case you’re confused by the previous example, let’s walk through the process of creating a new database, adding a new data file, then emptying it, then removing it.
Create a Database and View its Data File Information
First we switch to the master database before creating a new database called Test . We then switch to the new database and select certain info about its database files from the sys.database_files system catalog view.
Add a New Data File
We now add a new data file with a logical name of Test2 and a physical path of /var/opt/mssql/data/Test2.mdf (note that this is a Linux/Mac path. If using Windows, use backslashes instead of forward slashes). Again we query the sys.database_files system catalog view so that we can see the details of our new file.
Empty the Data File and Remove it
For the purposes of this example, let’s assume that the database has been put into production and the data file we just created has been loaded up with data. Now we want to remove that file (for whatever reason). But before we remove the file, we need to empty it (migrate its data to another file).
Here’s how to do that:
So this is just like the first example on this page, except our database has a different name. And in this example, we query sys.database_files to confirm that the file has indeed been deleted.
It is sometimes required to reach the outside of SQL Server, I mean the folder structures of the operating systems and create a file, write a file, delete a file or read from file.
Since the MS SQL Server is not build for operating system tasks, it is not easy to manage such operatings on the file system by just using t-sql statements. Fortunately we have some tools for this tasks like Ole Automation Procedures and like xp_cmdshell extended stored procedure.
In this sql server tutorial or t-sql article I will try to summarize the task and how we can enable OLE Automation Procedures, or enable xp_cmdshell extended procedures as a Microsoft SQL Server administrator.
And how we can use ole automation procedures and xp_cmdshell in our t-sql codes or statements in order to manage file system operations on the OS.
I'll deal in this t-sql tutorial with deleting a file using both xp_cmdshell and OLE Automation Procedures.
How to Enable xp_cmdshell on a MS SQL Server Instance
xp_cmdshell has the power to make modifications on the outer world of SQL Server. So this power has to be controlled in the security concepts and be manageable.
In the early versions of Microsoft SQL Server the xp_cmdshell extended procedure was enabled default. This caused some security gaps for SQL Server owners.
Although some administrators do not use xp_cmdshell functionality, it was out of control and can be used in an unsecure way by a sql server developer.
Microsoft now enables SQL Server administrators to enable or disable the xp_cmdshell extenden procedure and releases the SQL Server products with xp_cmdshell is disabled fby default.
So if you think you are capable of taking the security risks and prevent those risks you can enable xp_cmdshell by using the sp_configure stored procedure.
The below t-sql code displays how xp_cmdshell can be used to delete a file named delete-me-file.txt in the root folder of C drive
Since the xp_cmdshell extended procedure has not been enabled yet, the SQL Server will return the following error and warning message:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Actually the warning message is self explaining in details. We can either enable the sys.xp_cmdshell procedure by using sp_configure or by using the SQL Server Surface Area Configuration Tool.
Of course in order to make configuration changes on the sql server, you have to have the system administrator rights or permissions on the SQL Server instance.
Enable xp_cmdshell using sp_configure
exec sp_configure
go
exec sp_configure 'xp_cmdshell', 1
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go
When you run the exec sp_configure t-sql command, you will see a returned list of configuration values for the related SQL Server installation.
The last row of the configuration list is probably displaying values of xp_cmdshell.
config_value and run_value columns are displaying if xp_cmdshell is enabled and if this change is reflected to the running configuration values.
exec sp_configure 'xp_cmdshell', 1 sql command sets the configuration value ("config_value") to 1 which means enable.
And the last t-sql code or command reconfigure sets the running value to enabled in a way reflects the changes to the running server configurations.
How to delete a file using xp_cmdshell extended procedure
After enabling xp_cmdshell it is straight forward for deleting a file from the file system if you have the necessary permissions on the file object for the user who is running the xp_cmdshell procedure.
How to Enable Ole Automation Procedures on a MS SQL Server Instance
Ole Automation Procedures can be configured just like xp_cmdshell both from Surface Area Configuration Tool and using the sp_configure command in the t-sql batch statements.
Ole Automation Procedures are disabled for new instances by default.
If you want to deal with the FileSystem Object (FSO) and create, delete files or folders, copy files or move files from a folder to an other folder OLE Automation Procedures will be very useful.
Here is a list of OLE Automation Procedures you might probably feel the need to use while working with FSO (File System Object) from SQL Server.
sp_OACreate
sp_OADestroy
sp_OAGetProperty
sp_OASetProperty
sp_OAMethod
sp_OAGetErrorInfo
sp_OAStop
Enable Ole Automation Procedures using sp_configure
If you have one of the following error messages when you run an OLE Automation procedure command, this points to an issue where you can solve by enabling the Ole Automation Procedures on the SQL Server instance.
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
exec sp_configure
go
exec sp_configure 'Ole Automation Procedures', 1
-- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go
How to delete a file using OLE Automation Procedures
After enabling OLE Automation Procedures the following sql script codes will guide us to delete a file from file system.
DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'C:\delete-me-file.txt'
EXEC @Result = sp_OADestroy @FSO_Token
With the first line of sql batch code, we are creating a token of the object which we have created as an instance of Scripting.FileSystemObject. This "Scripting.FileSystemObject" programmatic identifier is used as a parameter to the sp_OACreate extended procedure. And the created token is used as a reference parameter to the following sql procedure call codes.
The sp_OAMethod extended stored procedure is used to call a method of the object whose token (created by sp_OACreate) is passed as a parameter. sp_OAMethod calls methods of objects with the help of the following parameters:
The object token created by sp_OACreate
The method name
The method's return value
Parameters that will be used by the object method
The following sp_OAMethod is passing the object token @FSO_Token, and the delete command of the FileSystemObject "DeleteFile" method name, and the name of the file which will be deleted as parameters.
The last command sp_OADestroy cleans the memory objects that is not needed any more.
Язык 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 можно указывать, среди прочих, следующие объекты:
Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:
Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.
В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:
В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:
В данном примере изменяются все строки таблицы Project, поскольку в запросе отсутствует предложение WHERE.
В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:
Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:
В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:
Инструкция DELETE
Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:
Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью 'Менеджер':
Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:
Поскольку сотрудница Вершинина уволилась, из базы данных удаляются все записи, связанные с ней. Запрос из этого примера можно также выполнить с помощью предложения FROM, как это показано ниже. В данном случае семантика этого предложения такая же, как и предложения FROM в инструкции UPDATE.
Использование предложения WHERE в инструкции DELETE не является обязательным. Если это предложение отсутствует, то из таблицы удаляются все строки:
Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.
Другие инструкции и предложения Transact-SQL для модификации таблиц
Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:
инструкцию TRUNCATE TABLE;
Эти инструкции и предложение рассматриваются в последующих подразделах.
Инструкция TRUNCATE TABLE
Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.
Инструкция TRUNCATE TABLE имеет следующий синтаксис:
Инструкция MERGE
Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.
Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing). Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.
Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу "запись за записью".
Предложение OUTPUT
Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.
Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.
В примере ниже показано использование инструкции OUTPUT с инструкцией DELETE:
При условии, что содержимое таблицы находится в исходном состоянии, выполнение запроса в примере дает следующий результат:
В этом примере сначала объявляется табличная переменная @deleteTable с двумя столбцами: Id и LastName. В этой таблице будут сохранены удаленные строки. Синтаксис инструкции DELETE расширен предложением OUTPUT: "OUTPUT deleted.Id, deleted.LastName INTO @deleteTable". Посредством этого предложения система сохраняет удаленные строки в таблице deleted, содержимое которой потом копируется в переменную @deleteTable.
В примере ниже показано использование предложения OUTPUT в инструкции UPDATE:
Читайте также: