Введение в MS SQL Server и T-SQL
SQL Server является одной из наиболее популярных систем управления базами данных (СУБД) в мире. Данная СУБД подходит для самых различных проектов: от небольших приложений до больших высоконагруженных проектов.
SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. А текущей версией является версия 2022, которая вышла в ноябре 2022 году и которая будет использоваться в текущем руководстве.
SQL Server долгое время был исключительно системой управления базами данных для Windows, однако начиная с версии 16 эта система доступна и на Linux.
SQL Server характеризуется такими особенностями как:
- Производительность. SQL Server работает очень быстро.
- Надежность и безопасность. SQL Server предоставляет шифрование данных.
- Простота. С данной СУБД относительно легко работать и вести администрирование.
Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Нередко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.
Для организации баз данных MS SQL Server использует реляционную модель. Эта модель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных.
Реляционная модель предполагает хранение данных в виде таблиц, каждая из которых состоит из строк и столбцов. Каждая строка хранит отдельный объект, а в столбцах размещаются атрибуты этого объекта.
Для идентификации каждой строки в рамках таблицы применяется первичный ключ (primary key). В качестве первичного ключа может выступать один или несколько столбцов. Используя первичный ключ, мы можем ссылаться на определенную строку в таблице. Соответственно две строки не могут иметь один и тот же первичный ключ.
Через ключи одна таблица может быть связана с другой, то есть между двумя таблицами могут быть организованы связи. А сама таблица может быть представлена в виде отношения («relation»).
Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.
Изначально язык SQL был разработан в компании IBM для системы баз данных, которая называлась System/R. При этом сам язык назывался SEQUEL (Structured English Query Language). Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как «сиквел».
В 1979 году компания Relational Software Inc. разработала первую систему управления баз данных, которая называлась Oracle и которая использовала язык SQL. В связи с успехом данного продукта компания была переименована в Oracle.
Впоследствии стали появляться другие системы баз данных, которые использовали SQL. В итоге в 1989 году Американский Национальный Институт Стандартов (ANSI) кодифицировал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2011 году. Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга.
Выделяются две разновидности языка SQL: PL-SQL и T-SQL. PL-SQL используется в таких СУБД как Oracle и MySQL. T-SQL (Transact-SQL) применяется в SQL Server. Собственно поэтому в рамках текущего руководства будет рассматриваться именно T-SQL.
В зависимости от задачи, которую выполняет команда T-SQL, он может принадлежать к одному из следующих типов:
- DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д. В общем определяют данные. В частности, к этому типу мы можем отнести следующие команды:
- CREATE : создает объекты базы данных (саму базу даных, таблицы, индексы и т.д.)
- ALTER : изменяет объекты базы данных
- DROP : удаляет объекты базы данных
- TRUNCATE : удаляет все данные из таблиц
- SELECT : извлекает данные из БД
- UPDATE : обновляет данные
- INSERT : добавляет новые данные
- DELETE : удаляет данные
- GRANT : предоставляет права для доступа к данным
- REVOKE : отзывает права на доступ к данным
Сравнение языков Access SQL и SQL Server TSQL
При переносе данных Access на сервер SQL Server или создании решения Access с внутренней базой данных SQL Server чрезвычайно важно знать различия между SQL (язык программирования в Access) и Transact SQL, или TSQL (язык программирования в SQL Server). Ниже приведены важные различия, знание которых необходимо для правильной работы вашего решения.
Различия в синтаксисе и выражениях
Имеется несколько различий в синтаксисе и выражениях, которые требуют преобразования. В приведенной ниже таблице указаны наиболее распространенные различия.
SQL в Access
TSQL в SQL Server
Атрибут реляционной базы данных
Обычно называется полем
Обычно называется столбцом
Кавычки («), например, «Mary Q. Contrary»
Апостроф (‘), например, ‘Mary Q. Contrary’
Решетка (#), например, #1/1/2019#
Апостроф (‘), например, ‘1/1/2019’
Многосимвольный подстановочный знак
Звездочка (*), например, «Cath*»
Процент (%), например, ‘Cath%’
Односимвольный подстановочный знак
Вопросительный знак (?), например, «Cath?»
Подчеркивание (_), например, «Cath_»
Оператор остатка от деления
Оператор MOD, например, Value1 MOD Value2
Процент (%), например, Value1 % Value2
WHERE Bitvalue = [True | False]
WHERE Bitvalue = [-1 | 0]
WHERE Bitvalue = [1 | 0]
В режиме SQL используйте объявление параметров SQL.
- Программа Access берет в кавычки («) имена и объекты таблицы. Язык T-SQL может использовать кавычки для имен таблиц с пробелами, но это не общепринятая практика. В большинстве случаев объекты следует переименовать, чтобы в имени не было без пробелов. Также необходимо перезаписать запросы, чтобы отображались новые имена таблиц. Используйте квадратные скобки [ ] для таблиц, которые невозможно переименовать, хотя они не соответствуют стандартам именования. Программа Access заключает параметры в запросах в дополнительные круглые скобки, но в языке T-SQL их можно удалить.
- Рекомендуется использовать канонический формат даты (гггг-мм-дд чч:мм:сс), который является стандартом ODBC для дат, сохраненных в виде знаков, и обеспечивает единообразие представления данных в базах данных и сохраняет порядок сортировки дат.
- Чтобы избежать путаницы при сравнении логических значений, можно использовать приведенное ниже сравнение для Access и SQL Server.
- Проверка на значение FALSE WHERE Bitvalue = 0
- Проверка на значение TRUE WHERE Bitvalue <> 0
Значения NULL
Значение NULL — это не пустое поле, это “поле, не содержащее никакого значения”. Значение NULL — это заполнитель, который означает, что данные отсутствуют или неизвестны. Системы баз данных, которые распознают значения NULL, применяют трехзначную логику. Это означает, что что-то может быть истинно, ложно или неизвестно. Если неправильно обрабатывать значения NULL, то можно получить неправильные результаты сравнения на равенство или оценки предложений WHERE. Ниже приведено сравнение того, как Access и SQL Server работают со значениями NULL.
Отключение значений NULL в таблице
По умолчанию в Access и SQL Server значения NULL включены. Чтобы отключить значения NULL в столбце таблицы, выполните указанные ниже действия.
- В программе Access установите значение свойства поля Обязательное равным “Да”.
- В SQL Server добавьте атрибут NOT NULL в столбец в инструкции CREATE TABLE.
Проверка на значения NULL в предложении WHERE
Используйте предикаты сравнения IS NULL и IS NOT NULL.
-
В Access используйте предикаты IS NULL или IS NOT NULL. Пример.
SELECT … WHERE column IS NULL.
SELECT … WHERE field IS NULL
Преобразование значений NULL с помощью функций
Используйте функции NULL для защиты выражений и возврата альтернативных значений.
-
В Access используйте функцию NZ (value, [valueifnull]), которая возвращает 0 или другое значение. Пример.
SELECT AVG (NZ (Weight, 50) ) FROM Product
SELECT AVG (ISNULL (Weight, 50)) FROM Product
Общие сведения о параметрах базы данных
В некоторых системах баз данных есть особые механизмы.
- В Access нет параметров базы данных со значением NULL.
- В SQL Server можно применить параметр SET ANSI_NULLS OFF для прямых сравнений на равенство со значением NULL, используя операторы = и <>. Рекомендуется не использовать этот параметр, потому что он устарел и может вызвать замешательство у других пользователей, которые в работе с NULL придерживаются стандарта ISO.
Преобразование и приведение
При работе с данными и в процессе программирования постоянно требуется преобразовывать один тип данных в другой. Процесс преобразования может быть простым или сложным. Обратите внимание на такие моменты: явное или неявное преобразование, текущие региональные параметры даты и времени, округление или усечение чисел и размер типов данных. Ничто не заменит тщательного тестирования и подтверждения результатов.
В Access можно использовать функции преобразования типа, которых всего одиннадцать (по одной для каждого типа данных). Каждая из них начинается с буквы С. Например, вот как можно преобразовать число с плавающей запятой в строку.
CStr(437.324) returns the string "437.324".
В SQL Server в основном используются функции TSQL CAST и CONVERT TSQL, хотя имеются и другие функции преобразования для специальных целей. Например, вот как можно преобразовать число с плавающей запятой в строку.
CONVERT(TEXT, 437.324) returns the string "437.324"
Функции DateAdd, DateDiff и DatePart
Такие же функции (DateAdd, DateDiff и DatePart) часто используются в Access и TSQL, но у них отличается первый аргумент.
- В Access первый аргумент называется interval. Это строковое выражение, которое берется в кавычки.
- В SQL Server первый аргумент называется datepart. Он использует значения ключевых слов, не требующие кавычек.
Компонент Access SQL Server Год «yyyy» year, yy, yyyy Квартал «q» quarter, qq, q Месяц «m» month, mm, m День года «y» dayofyear, dy, y День «d» day, dd, d Неделя «ww» wk, ww День недели «w» weekday, dw Часы «h» hour, hh Минуты «n» minute, mi, n Секунды «s» second, ss, s Миллисекунды millisecond, ms Сравнение функций
В запросах Access могут содержаться вычисляемые столбцы, которые иногда используют функции Access Functions, чтобы получить результаты. При миграции запросов на сервер SQL Server необходимо заменить функцию Access эквивалентной функцией TSQL, если она доступна. Если не имеется соответствующей функции TSQL, обычно можно создать вычисляемый столбец, чтобы выполнить необходимые действия. В TSQL есть широкий набор функций, и вы сможете узнать, какие из них доступны. Дополнительные сведения см. в статье Функции базы данных SQL.
В приведенной ниже таблице показано соответствие функций Access и TSQL.
Категория Access
Разница между SQL и T-SQL
SQL (structured query language) — известнейший декларативный язык программирования, используемый при создании, модификации и управлении данными в реляционных БД. T-SQL — это расширенная версия SQL, и ее особенность заключается в том, что она предназначена для работы с сервером от Microsoft. Речь идет о базе данных MS SQL — Microsoft SQL Server. Вдобавок к этому, процедурное расширение с именем T-SQL характеризуется совместимостью с БД компании Sybase (Sybase ASE, Adaptive Server Enterprise).
Но прежде чем продолжить, вспомним некоторые основы.
Теоретическое отступление
База данных представляет собой структуру, где данные имеют связи между собой. Для управления такими данными используют специализированное программное обеспечение — СУБД (системы управления базами данных). Когда говорят о базах реляционного типа (RDBMS), то подразумевают, что данные и связи между данными организованы с помощью таблиц. Кроме непосредственных данных, в таких таблицах есть идентификаторы (id), а также первичные и вторичные ключи, которые имеют связи с идентификаторами.
Работа СУБД предполагает выполнение определенных операций (создание, удаление, обновление и т. п.). Чтобы выполнение этих операций стало возможным, разработчики БД используют язык структурированных запросов SQL. Это язык стандарта ANSI/ISO, помогающий хранить данные и манипулировать ими в рамках какой-нибудь СУБД (MySQL, MSSQL, Oracle Database, IBM DB2 и пр.).
Таким образом, SQL — стандартный язык запросов к реляционным базам данных.
Выделяют 3 вида SQL-команд:
- DDL (Data Definition Language). Команды для изменения структуры БД и связанных объектов: ALTER, CREATE, DROP.
- DML (Data Manipulation Language). Для управления данными (для вставки, просмотра и выборки, обновления, удаления и пр.): INSERT, SELECT, UPDATE, DELETE.
- DCL (Data Control Language). Для управления пользователями: GRANT, REVOKE.
Несколько слов про TSQL
В указанных выше пояснениях определили, что SQL — это язык запросов для большинства RDBMS-продуктов. Да, совместимость (compatibility) — это хорошо, но совместимости много не бывает. Именно поэтому некоторые поставщики СУБД создали собственные аналоги языков структурированных запросов (languages), а если быть точнее — расширили имеющееся решение, но уже с учетом потребностей своего продукта. Как раз таким аналогом и является процедурное расширение Transact Structure Query Language (TSQL). Это не что иное, как специализированная версия, заточенная под MS SQL Server (2012, 2016 и пр., версия неважна).
Используя TSQL-операторы, разработчик может писать запросы и выполнять нужные операции над таблицами, объединять их, добавлять ограничения, писать хранимые процедуры и индексы, осуществлять транзакции и многое другое. Язык TSQL поддерживает разные полезные функции, многие из которых присутствуют и в версии-первоисточнике. К примеру, агрегатные функции, которые выполняют вычисления на наборе значений. Либо оконные функции и инструкцию OVER (OVER определяет окно для использования оконной функции). Оконные функции не меняют выборку, как GROUP BY, а лишь добавляют дополнительную информацию о ней. Если же вспомнить JOIN, то, несмотря на одинаковую производительность конструкций JOIN и OVER, последняя предоставляет больше свободы, чем жесткий JOIN.
Как бы там не было, есть и отличия, так как в TSQL добавили:
— поддержку аутентификации Microsoft Windows;
— глобальные и локальные переменные;
— дополнительные функции, предназначенные для обработки дат, строк и пр.
Тезисно о разнице
Для закрепления материала смотрите таблицу ниже, где собраны основные тезисы.
SQL TSQL Специфичный для предметной области язык, используется для управления данными в СУБД. Запатентованная версия SQL от Microsoft, созданная специально для СУБД MS SQL Server в качестве аналога. Язык структурированных запросов (queries). Transact-язык структурированных запросов. Разработан IBM. Разработан Microsoft. Можно встраивать SQL в TSQL. TSQL в SQL встраивать нельзя. Помогает обрабатывать и анализировать данные посредством простых запросов. Помогает добавлять бизнес-логику в приложения. Очень надеемся, что после прочтения статьи вы четко уясните разницу между этими двумя аббревиатурами. Если же хотите получить действительно продвинутые знания, связанные с управлением БД, добро пожаловать на специализированный курс OTUS!
В чем разница между SQL и TSQL
Основное различие между SQL и TSQL является то, что SQL — это язык , который используется для хранения и управления данными в СУБД, а TSQL является расширенной версией SQL предназначен для выполнения операций на сервере MS SQL.
База данных — это набор связанных данных. Система управления базами данных (СУБД) представляет собой программное обеспечение, которое помогает управлять данными в базе данных. Более того, система управления реляционными базами данных (RDBMS) является усовершенствованной СУБД. Она хранит данные в таблицах, и эти таблицы связаны друг с другом. Разработчик может использовать язык SQL для выполнения операций в СУБД. Кроме того, TSQL является расширенной версией SQL.
Ключевые термины
Разница между SQL и TSQL
Что такое SQL
SQL расшифровывается как язык структурированных запросов . Это язык базы данных стандарта ANSI/ISO, который помогает хранить и манипулировать данными в СУБД. Большинство СУБД, таких как MySQL и Oracle, используют SQL в качестве стандартного языка запросов к базе данных. Можно выполнять различные операции над данными, хранящимися в РСУБД, используя SQL. Разработчик может писать запросы для создания таблиц, создания баз данных, вставки, обновления, удаления данных и многих других операций.
Существует три типа команд SQL, выглядят они следующим образом.
DDL (Data Definition Language ) — эти команды помогают изменить структуру базы данных и связанных объектов. Пример: CREATE, ALTER, DROP
DML (Data Manipulation Language) — эти команды помогают управлять данными в базе данных. Пример: SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language) — эти команды помогают управлять пользователями, которые могут получить доступ к данным. Пример: GRANT, REVOKE
Что такое TSQL
SQL является языком запросов для большинства продуктов RDBMS. Различные поставщики СУБД разработали свой собственный язык баз данных, расширив SQL в соответствии со своим продуктом. TSQL — это расширение SQL, разработанное Microsoft. TSQL означает Transact Structure Query Language . Это SQL-версия Microsoft SQL Server.
Приложения могут связываться с SQL Server, выполняя операторы TSQL. Разработчик может писать запросы для выполнения операций над таблицами, объединения таблиц и добавления ограничений. Он также может выполнять транзакции, писать хранимые процедуры, представления, индексы и многое другое. Существуют различные числовые, строковые функции, функции даты. Кроме того, существуют функции агрегирования для выполнения операции с набором значений.
Разница между SQL и TSQL
Определение
SQL — это предметно-ориентированный язык, используемый в программировании и предназначенный для управления данными, хранящимися в системе управления реляционными базами данных. В отличие от этого, TCQL является собственной версией Microsoft Microsoft для своей СУБД SQL Server. Таким образом, в этом главное отличие SQL от TSQL.
Дополнительно
Более того, SQL означает язык структурированных запросов, а TSQL — язык запросов Transact Structure.
Основа
SQL — это язык запросов для управления данными в СУБД, а TSQL — это расширение SQL, которое используется на сервере MS SQL.
Разработчик
IBM разработала SQL, в то время как Microsoft разработала TSQL. Это еще одно различие между SQL и TSQL.
Встраивание
Кроме того, еще одно различие между SQL и TSQL заключается в том, что можно встраивать SQL в TSQL, но невозможно встраивать TSQL в SQL.
Использование
В то время как SQL помогает обрабатывать и анализировать данные с помощью простых запросов, TSQL помогает добавлять бизнес-логику в приложение. Следовательно, это еще одно различие между SQL и TSQL.
Заключение
Основное различие между SQL и TSQL состоит в том, что SQL — это язык, который используется для хранения данных и управления ими в СУБД, а TSQL — это расширенная версия SQL, предназначенная для выполнения операций на сервере MS SQL. Вкратце, TSQL — это версия SQL, которая является реализацией для Microsoft SQL Server.