Use master sql что это

SQL-Ex blog

Вы заглядывали когда-нибудь в раздел системных баз данных SSMS? Те, кто только начинает работать с SQL Server, могут многого не знать о важных системных базах данных. Для функционирования каждого SQL Server требуются четыре системные базы данных.

Ниже я расскажу о базе данных model, которая имеет уникальное значение для SQL Server.

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

Например, вы хотите, чтобы каждая новая база данных имела файл данных размером 4Гб, а журнала — 2Гб; вы можете это сделать. Что если вы захотите, чтобы все новые базы данных находились в режиме Bulk Recovery? Вы можете это сделать. Что мне больше всего нравится, это создать стандартные настройки безопасности для каждой новой базы данных. И да, вы можете это сделать.

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

USE [master] 
GO 
ALTER DATABASE [model] SET RECOVERY BULK_LOGGED WITH NO_WAIT 
GO 
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 4194304KB ) 
GO 
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 2097152KB ) 
GO 
USE [model] 
GO 
CREATE USER [ModelUser] FOR LOGIN [ModelUser] 
GO 
ALTER ROLE [db_datareader] ADD MEMBER [ModelUser] 
GO

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

На скриншоте ниже показаны результаты эти настроек при использовании графического интерфейса SSMS для создания новой базы данных.

Я выполнил пару системных представлений, чтобы показать вам значения, назначенные базе данных ModelDeployment1, которую я создал. Первое, что вы видите, — это режим Bulk Recovery. Затем вы видите размер файла данных и файла журнала, соответствующий сделанным настройкам. Наконец, видно, что был создан новый пользователь SQL с именем ModelUser.

Теперь, если я выполню команду создания базы данных CREATE DATABASE, вы увидите те же самые результаты для созданной с помощью T-SQL базы данных.

Я отмечал, что SSMS GUI и T-SQL работали по-разному в прошлом, и воплощали разные установки model. Это было исправлено в современной версии SSMS и SQL Server.

Как изменить контекст базы данных в Microsoft SQL Server? Команда USE

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

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

В Microsoft SQL Server это делается с помощью команды USE.

Команда USE в T-SQL

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

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

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

Пример использования команды USE

В качестве примера давайте напишем простую инструкцию, в которой мы подключимся к базе данных TestDB, выполним тестовый запрос (вызовем функцию DB_NAME), а затем сменим контекст базы данных на другую, в нашем примере это будет база master.

USE TestDB GO --Инструкции для базы данных TestDB SELECT DB_NAME() AS [Имя базы данных] USE master GO --Инструкции для базы данных master SELECT DB_NAME() AS [Имя базы данных]

Меняем контекст базы данных с помощью Management Studio

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

Данный функционал расположен на панели редактора SQL запросов.

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

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

Например, следующий запрос выполнится успешно, и мы получим данные из таблицы TestTable, хотя, как Вы понимаете, таблицы TestTable в базе данных master нет (именно на ней будет контекст выполнения).

USE master GO SELECT * FROM TestDB.dbo.TestTable

• TestDB – имя базы данных;
• dbo –имя схемы;
• TestTable – имя таблицы.

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

У меня на этом все, удачи!

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

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

• базы данных;
• таблицы;
• представления;
• схемы;
• хранимые процедуры;
• триггеры.

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

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

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

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

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

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

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

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

USE master; GO ALTER DATABASE SampleDb ADD FILE ( NAME=sampledb_dat1, FILENAME='D:\sampledb1.mdf', SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5 );

В этом примере инструкция 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.)

USE SampleDb; ALTER DATABASE SampleDb ADD FILEGROUP Employee_FSGroup CONTAINS FILESTREAM; GO ALTER DATABASE SampleDb ADD FILE ( NAME= employee_FS, FILENAME = 'D:\temp\emp_FS') TO FILEGROUP Employee_FSGroup 

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

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

CREATE TABLE EmployeeInfo ( Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, FilestreamData VARBINARY(MAX) FILESTREAM NULL )

В этом примере таблица 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 ее можно преобразовать в частично автономную, как это показано в примере ниже:

EXEC sp_configure 'show advanced options' , 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'contained database authentication' , 1; RECONFIGURE WITH OVERRIDE; ALTER DATABASE SampleDb SET CONTAINMENT = PARTIAL; EXEC sp_configure 'show advanced options' , 0; RECONFIGURE WITH OVERRIDE;

Инструкция 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 показано в примере ниже:

USE SampleDb; ALTER TABLE Employee ADD PhoneNumber CHAR(12) NULL;

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

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

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

USE SampleDb; ALTER TABLE Employee DROP COLUMN PhoneNumber;

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

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

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

• тип данных;
• поддержка значения NULL.

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

USE SampleDb; ALTER TABLE Department ALTER COLUMN Location NCHAR(25) NOT NULL;

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

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

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

USE SampleDb; CREATE TABLE Sales ( Id INTEGER NOT NULL, OrderDate DATE NOT NULL, ShipDate DATE NOT NULL); -- Добавляем ограничение для дат (поля OrderDate и ShipDate) ALTER TABLE Sales ADD CONSTRAINT order_check CHECK(OrderDate 

ALTER TABLE Sales DROP CONSTRAINT order_check, pkey_sales;

USE SampleDb; ALTER TABLE Sales NOCHECK CONSTRAINT ALL;

USE SampleDb; -- Переименование таблицы Sales в BigSales EXEC sp_rename @objname = Sales, @newname = BigSales; -- Переименование столбца OrderDate в таблице BigSales EXEC sp_rename @objname = 'BigSales.OrderDate', @newname = date_order;

DROP тип_объекта имя_объекта; 

DROP DATABASE database1

DROP TABLE table_name1