Диалект Transact-SQL
- язык сценариев для большей гибкости запросов к БД.
Типы данных и переменные Transact-SQL, создание таблиц в графической среде разработки
- целые (TINYINT - 1, SMALLINT - 2, INT - 4, BIGINT - 8 байт):
DECLARE @I INT
SET @I = 10;
- с плавающей точкой – REAL и FLOAT (нельзя использовать для денежных данных, т.к. могут теряться разряды);
- целое + дробные разряды DECIMAL (пример с переполнением):
DECLARE @D DECIMAL(3,1)
SET @D = 10.9 + 100
SELECT @D
- денежные форматы MONEY & SMALLMANEY:
DECLARE @D MONEY
SET @D = 999999999.12345
SELECT @D
- одиночный символ:
DECLARE @C CHAR = 'ABC'
SELECT @C
- строки постоянной длины ( у всех строк ограничение по размерам – одна страница (8 кБ)! ):
DECLARE @C CHAR(25) = 'ABC'
SELECT @C +@C
- Строки переменной длины (ASCII):
DECLARE @C VARCHAR(25) = 'ABC'
SELECT @C +@C
- Строки переменной длины (юникод – в 2 раза объемнее латинских строк, латинские – без N (от «national»)):
DECLARE @C NVARCHAR(25) = N'Кока'
SELECT @C +N'-Кола'
/*************************************************/
DECLARE @S NVARCHAR(25)
SELECT @S + N'-Кола'
дает NULL или -Кола! в зависимости от настройки сеанса;
- двоичные данные (в примере – приведение типов переменных):
DECLARE @B BINARY(25) = CAST('Hello' AS BINARY)
SELECT @B
SELECT CAST(@B AS VARCHAR(25))
- объекты большого объема нельзя использовать для переменных, нельзя производить их соединение (TEXT для текстов и IMAGE для картинок и медиа-данных), но есть исключение – текст объемом до 2Гб (хранение текста вне БД, замедляет работу):
DECLARE @T VARCHAR(MAX) = 'Text'
SELECT @T
Или для любых данных:
DECLARE @T VARBINARY(MAX)
= CAST('Text' AS VARBINARY(MAX))
SELECT @T
- старый тип даты и времени (целое, отображаемое в формате даты, по умолчанию удобной для текстовой сортировки):
DECLARE @DT DATETIME = GETDATE();
SELECT @DT
SET @DT = '2015-31-12 12:00'
SELECT @DT
SELECT CONVERT(VARCHAR(25), @DT, 104)
- новые типы даты и времени:
- отдельно дата:
DECLARE @DT DATE = GETDATE();
SELECT @DT
SELECT CONVERT(VARCHAR(25), @DT, 104)
- отдельно время:
DECLARE @DT TIME = GETDATE();
SELECT @DT
- дата и время высокой точности (для навигации и т.п. технических задач):
DECLARE @DT DATETIME2 = GETDATE();
SELECT @DT
- дата и время с часовым поясом:
DECLARE @DT DATETIMEOFFSET;
SET @DT = SYSDATETIMEOFFSET();
SELECT @DT
- сложные типы данных:
- XML (для контроля синтаксиса разметки):
DECLARE @XML XML;
SET @XML = '<root>articles</root>';
SELECT @XML
- TABLE – ссылка на таблицу;
- переменный тип данных: VARIANT;
- глобальный уникальный идентификатор: UNIQUEIDENTIFIER ( значения добавляются функцией NEWID() – не по возрастанию (не в конец), а в середину списка, или NEWSEQUENTIALID() – как свойство DEFAULT для поляпри создании таблицы ).
Особенности диалекта T-SQL
- Псевдонимы (псевдо-пользовательские типы), новые операторы (смотри приложение).
Использование транзакций
- создаем пустую таблицу:
CREATE TABLE [dbo].[Books2]
(
N int PRIMARY KEY NOT NULL,
Name nchar(10) NULL,
New bit NULL
) ON [PRIMARY]
- пробуем добавить три строки, последняя команда вставки - с ошибкой несоответствия типов. Ни одна вставка не будет выполнена (ошибка обнаружена на уровне среды разработки, запрос не отправлен):
BEGIN TRANSACTION
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book1')
SAVE TRANSACTION savepoint1
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book2')
INSERT INTO Books2 (N, New, Name) VALUES (1, 'one', 'Book3')
IF @@ERROR<>0
BEGIN
PRINT 'Error!'
ROLLBACK TRANSACTION savepoint1
COMMIT TRANSACTION
END
ELSE
COMMIT TRANSACTION
- пробуем добавить три строки, последняя команда вставки - с ошибкой дублирования значения в поле первичного ключа. В оперативной памяти будут выполнены первые две вставки, затем произойдет откат к точке сохранения, и в БД сохранится только результат первой вставки:
BEGIN TRANSACTION
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book1')
SAVE TRANSACTION savepoint1
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book3')
IF @@ERROR<>0
BEGIN
PRINT 'Error!'
ROLLBACK TRANSACTION savepoint1
COMMIT TRANSACTION
END
ELSE
COMMIT TRANSACTION
- пробуем добавить три строки, последняя команда вставки - с ошибкой несоответствия типов. Будут выполнены первые две вставки:
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book1')
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')
INSERT INTO Books2 (N, New, Name) VALUES (3, 'one', 'Book3')
- пробуем добавить три строки, последняя команда вставки - с ошибкой несоответствия типов. Помещаем все внутрь блока простой транзакции. В оперативной памяти будут выполнены первые две вставки, затем произойдет откат к началу блока транзакции, в БД не будет внесено ни одной строки:
BEGIN TRANSACTION
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book1')
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')
INSERT INTO Books2 (N, New, Name) VALUES (3, 'one', 'Book3')
COMMIT TRANSACTION
- пробуем добавить три строки, последняя команда вставки - с ошибкой дублирования значения в поле первичного ключа. Помещаем все внутрь блока простой транзакции. Будут добавлены две первые записи (транзакционный механизм пропустит запросы, содержащие подобные ошибки):
BEGIN TRANSACTION
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book1')
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book3')
COMMIT TRANSACTION
- пробуем добавить три строки, вторая команда вставки - с ошибкой дублирования значения в поле первичного ключа. Будут выполнены первая и третья вставки:
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book3')
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book1')
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')
- пробуем добавить три строки, вторая команда вставки - с ошибкой несоответствия типов. Будет выполнена первая вставка (выполнение пакета запросов прекращается после первой же ошибки такого рода):
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book3')
INSERT INTO Books2 (N, New, Name) VALUES (3, 'one', 'Book1')
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')
- пробуем добавить три строки, вторая команда вставки - с ошибкой несоответствия типов. Разделяем запросы на отдельные пакеты (уровень среды разработки). Будут выполнены первая и третья вставки (все, не содержащие ошибок):
INSERT INTO Books2 (N, New, Name) VALUES (1, 1, 'Book3')
GO
INSERT INTO Books2 (N, New, Name) VALUES (3, 'one', 'Book1')
GO
INSERT INTO Books2 (N, New, Name) VALUES (2, 1, 'Book2')