Введение в 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 : удаляет все данные из таблиц
DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбору данных, их обновление, добавление, удаление — в общем все те команды, с помощью которыми мы можем управлять данными.
К этому типу относятся следующие команды:
SELECT : извлекает данные из БД
UPDATE : обновляет данные
INSERT : добавляет новые данные
DELETE : удаляет данные
DCL (Data Control Language / Язык управления доступа к данным). К этому типу относят команды, которые управляют правами по доступу к данным. В частности, это следующие команды:
Схема элементов синтаксиса MS SQL
В данной статье описаны основы t-sql и примеры использования основных программируемых элементов, а именно: функций, определенных пользователем (UDF), триггеры, хранимые процедуры, описаны курсоры, циклы, описан принцип обработки ошибок.
Просто об объектах T-SQL:
- Таблица — хранит данные.
- Вьюха — содержит в себе сложный запрос к одной или нескольким таблицам, вьюхам, табличным функциям. В отличие от таблиц, не хранит в себе данные. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную, локальную временную таблицу нельзя создавать в теле вьюхи. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
- Табличная функция — похожа на вьюху, но основное отличие — принимает входные параметры и в зависимости от этих параметров запускается обработка данных, которая возращает тот или иной набор данных. Нельзя использовать внутри функции операции обработки данных (Insert, Update, Delete) над физическими таблицами. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
- Скалярная функция — Принимает входные параметры, использует запросы к таблицам, вьюхам, табличным функциям и возращает одно значение (заданного типа). Нельзя использовать внутри функции операции обработки данных (Insert, Update, Delete) над физическими таблицами. Для хранения промежуточных результатов обработки данных можно использовать табличную переменную. Временную таблицу нельзя создавать. Над табличной переменной можно выполнять действия INSERT, UPDATE, DELETE.
- Хранимая процедура — набор действий над данными. В основном требуется для того, чтобы взять данные из таблиц, вьюх, табличных функций, обработать данные и положить их в целевую таблицу. Можно использовать операции обработки данных (Insert, Update, Delete). Для хранения промежуточных результатов в хранимой процедуре используются как временные таблицы (в основном локальные), так и табличные переменные. Эффективней использовать временные таблицы.
Введение основы T-SQL и примеры, которые будут разобраны
В данной статье будут подробно рассмотрены основные объекты, создаваемые на языке T-SQL, которые покрывают 80% всех задач по обработке данных (триггеры, хранимые процедуры, табличные функции):
Таблицы базы данных, в которых хранятся собственно данные, содержат:
- cтроки: каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;
- cтолбцы: каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер
Представления (виртуальные таблицы) или Views — создаются для отображения данных из таблиц.
Подобно реальным таблицам, Views содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект
Хранимые процедуры (Stored Procedures) – это группа команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое
Хранимые процедуры (Stored Procedure) являются программами, хранящимися в базе данных и выполняющими различные действия, обычно с данными из базы данных, хотя процедуры могут и не осуществлять никаких обращений к базе. К хранимым процедурам могут обращаться любые программы, работающие с базой данных, к ним также могут обращаться и другие хранимые процедуры и триггеры. Допустима рекурсия, когда хранимая процедура обращается к самой себе. Хранимые процедуры выполняются на стороне сервера, а не на стороне клиента. Во многих случаях это может резко снизить сетевой трафик при решении различных задач работы с большой по объему базой данных и повысить производительность системы.
Триггеры (Triggers) – специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы.
Триггеры (trigger) так же как и хранимые процедуры, являются программами, выполняющимися на стороне сервера. Однако напрямую обращение к триггерам невозможно. Они автоматически вызываются при наступлении некоторого события базы данных — при добавлении, изменении и или удалении строк конкретной таблицы. Триггеры могут вызываться при соединении с базой данных.
Функции, определенные пользователем (user defined functions, UDF) — это конструкции, содержащие исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение/набор данных. К функциям можно обращаться из триггеров, хранимых процедура и из других программных компонентов.
SQL Server 2012 и Transact-SQL
Система управления базами данных (СУБД) , в состав которой входит компонент Database Engine, службы анализа Analysis Services, службы отчетов Reporting Services, интеграционные службы Integration Services и расширение SQLXML — является наилучшим выбором для широкого диапазона конечных пользователей и программистов баз данных, работающих над созданием бизнес-приложений, по двум причинам:
SQL Server — несомненно, наилучшая система для операционных систем Windows, вследствие ее тесной интеграции с ними (а также вследствие низкой стоимости). Благодаря огромному и все возрастающему количеству установленных систем Windows, SQL Server является широко применяемой системой управления базами данных.
Будучи составляющей системы реляционной базы данных, компонент Database Engine является самой легкой в использовании системой баз данных. Кроме хорошо знакомого пользовательского интерфейса, разработчики Microsoft предоставляют несколько разных инструментов для создания объектов баз данных, настраивания приложений баз данных и управления задачами системного администрирования.
В целом, SQL Server является больше, чем просто системой управления реляционными базами данных. Это платформа, которая не только позволяет управлять структурированными, полуструктурированными и неструктурированными данными, но также предоставляет комплексное, интегрированное системное программное обеспечение и программное обеспечение для аналитических исследований, которые позволяют организациям надежно управлять критически важными данными.
— язык реляционной базы данных системы SQL Server, который обладает несколькими нестандартными свойствами, которые неизвестны пользователям, знакомым только с языком SQL. В приведенных ниже статьях мы опишем возможности языка T-SQL.
What is T-SQL?
Transact-SQL, often abbreviated to T-SQL or even TSQL, is Microsoft’s and Sybase’s proprietary extension to SQL. Transact-SQL expands on the SQL standard to include extra features that aren’t included in the SQL standard.
Here’s an example of a simple T-SQL statement:
This is as simple as a T-SQL example could get. This creates a new database called Movies .
However, T-SQL provides for other options to be included in this statement, such as where the database files should be located, the size of those files, what their maximum size should be, and more.
T-SQL Features
Some of the features of T-SQL include:
- Procedural programming
- Local variables
- Various support functions for string processing, date processing, mathematics, etc
- Changes to the DELETE and UPDATE statements. In particular, they allow a FROM clause to be added, which allows joins to be included.
- T-SQL has a BULK INSERT statement that allows you to import a data file into a database table or view in a user-specified format.
One of the main benefits of the above extensions is that you have much more programmability when using T-SQL vs SQL. Stored procedures for example allow you to incorporate programming logic into your database. You can pass one or more parameters to a stored procedure, and the stored procedure will return an output which varies depending on the values of the parameters.
T-SQL also includes a large number of built-in functions, as well as supporting user-defined functions.
An Example of a Built-In Function
Here’s another basic example , this time using one of T-SQL’s built-in functions:
In this case we use T-SQL’s GETDATE() function to return the current date and time. This is used along with the SELECT statement, which is a common SQL statement for returning data.
We can also use that function as an argument for another function. Here’s an example:
In this case we use the T-SQL FORMAT() function to extract just the month portion of the date, and present it in a certain format. You can see more date/time examples at How to Format the Date & Time in SQL Server.
History of T-SQL
Although Transact-SQL (and SQL Server) is most commonly known as a Microsoft thing, it’s actually the result of a partnership between Microsoft and Sybase that started in 1987. This partnership was dissolved in 1994.
Following this, T-SQL continued to be supported by database products released by both companies, however, each company developed its database products in its own direction. Therefore, there are some differences between Sybase T-SQL and Microsoft T-SQL.
T-SQL and GUI Tools
Transact-SQL is central to SQL Server. Most operations in SQL Server are done using T-SQL. This is true even if you use a GUI tool (such as SSMS or DBeaver) and you don’t actually do any programming. Any time you use an application to interact with SQL Server, the application uses T-SQL behind the scenes to carry out the specified task.
But SQL Server isn’t the only database management system that supports Transact-SQL. Microsoft Azure SQL Database also supports T-SQL. Most T-SQL features that applications use are fully supported in both Microsoft SQL Server and Azure SQL Database (although there are some exceptions).
About the GO Command
Anyone who has used T-SQL will probably be familiar with the GO command. This keyword is often placed at the end of a batch of statements. For example, the first example above could also be written like this:
However, the GO command is not actually part of Transact-SQL. In fact, the GO command cannot even occupy the same line as a Transact-SQL statement.
GO is one of the commands that are recognized by the sqlcmd and osql utilities, as well as SQL Server Management Studio Code Editor to facilitate the readability and execution of batches and scripts. The GO command signals the end of a batch of Transact-SQL statements to the SQL Server utilities.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO , or since the start of the ad hoc session or script if this is the first GO .
Other Proprietary SQL Extensions
Transact-SQL isn’t the only extension to the SQL standard. Various database systems have their own extensions. For example, Oracle uses a proprietary procedural language called PL/SQL, and PostgreSQL uses a procedural language called PL/pgSQL.
Although proprietary SQL extensions have their advantages (such as extended functionality), they do have their disadvantages. One major disadvantage is that they can make things difficult if you ever need to port a database from one system to another. For example, if your organization suddenly decides that it’s going to use Oracle instead of SQL Server, you may need to go through all the SQL Server code and rewrite it for Oracle. This also requires you to come up to speed with the syntax of both systems.