The theoretical foundations of the SQL Server 2012 DBMS are considered in a simple and accessible way. Installation, configuration and support of MS SQL Server 2012 are shown. The Transact-SQL data manipulation language is described. Considered creating a database, modifying tables and their contents, queries, indexes, views, triggers, stored procedures, and user-defined functions.
The implementation of security using authentication, encryption and authorization is shown. Attention is paid to automation of DBMS administration tasks. The creation of backup copies of data and the implementation of system recovery are considered. Describes Microsoft Analysis Services, Microsoft Reporting Services, and other business intelligence tools. The technology of working with XML documents, spatial data management, full-text search and much more are considered. For beginner programmers.

In the modern world, information is of the highest value, but it is equally important to be able to manage this information. This book is about the SQL query language and database management. The material is presented starting with the description of basic queries and ending with complex manipulations using joins, subqueries and transactions. If you are trying to understand the organization and management of databases, this book will be an excellent practical guide and will provide you with everything necessary tools. A feature of this edition is the unique way of presenting the material, which distinguishes O\'Reilly's Head First series from a number of boring programming books.

This book will teach you how to work with SQL commands and statements, create and configure relational databases, load and modify database objects, run powerful queries, improve performance, and build security. You will learn how to use DDL statements and apply APIs, integrate XML and Java scripts, use SQL objects, create web servers, work with remote access, and perform distributed transactions.
In this book, you will find information such as descriptions of working with in-memory databases, streaming and embedded databases, databases for mobile and handheld devices, and much more.

SQL for Mortals is a complete introduction to Structured Query Language written specifically for beginners.

If you are new to database management, this book will teach you how to work with SQL easily and fluently, using simple queries and complex operations. To master SQL:

- Gain insight into database management concepts with a concise and simple introduction to relational databases.
— Follow these instructions for using basic SQL commands to find and work with information placed in data tables. Learn how to select and summarize data, as well as skillfully manage it.
- Efficiently work with composite data tables by applying advanced query techniques to more than one table at the same time, constructing complex queries and subqueries.
- Create new data tables for retail business applications. Learn the important principles of effective database design and techniques for ensuring data integrity and protection.
- Learn to use SQL with programming languages ​​using a dedicated chapter for programmers.

SQL is older than most of us, so I can't claim to be conveying some extraordinary stuff through this book. What makes this title unique is its slender size. If you are looking for a real compact practical guide on SQL, then this book is for you. For beginners, I have tried to confine an ocean to a bucket in order to equip them with SQL knowledge in the shortest time possible. SQL language is too voluminous and exposure of every aspect of this huge language is a very tedious task. Keeping aside the least utilized features, this book is rolled out to focus on the more operational areas of the language. It is meant to help you learn SQL quickly by yourself. It follows a tutorial approach while hundreds of hands-on exercises are provided, augmented with illustrations, to teach you SQL in a short period of time. Without any exaggeration, the book will expose SQL in record time. The book explicitly covers a free platform of the world’s number 1 DBMS to expose SQL: Oracle Database Express Edition. I have chosen Oracle XE because it is free to develop, deploy, and distribute; fast to download; and simple to administer.

Beginning Oracle PL/SQL gets you started in using the built-in language that every Oracle developer and database administrator must know. Oracle Database is chock-full of built-in application features that are free for the using, and PL/SQL is your ticket to learning about and using those features from your own code. with it, you can centralize business logic in the database, you can offload application logic, and you can automate database- and application-administration tasks.

Author Don Bales provides in Beginning Oracle PL/SQL a fast-paced and example-filled tutorial. Learn from Don\’s extensive experience to discover the most commonly used aspects of PL/SQL, without wasting time on obscure and obsolete features.

The book "SQL. The User's Bible is unique in that each chapter compares the implementations of the SQL query language standard in the three leading DBMSs. The result is a comprehensive and practical reference for database users, from beginners to professionals. This book on SQL conveniently combines theory with practice, contains a description of new technologies and will allow you to understand the numerous nuances of the SQL query language standard and its implementations. It can be used as a reference - a kind of desktop manual.
— Learn the basics of the SQL query language and relational databases
- Learn to work with tables, views, sequences and other database objects
- Learn how to apply transactions and locks in a multi-user environment
- Get to know the features offered by the SQL standard and three leading database vendors
- Learn how to access metadata and implement database security
- Explore additional topics: SQL to XML integration, OLAP business intelligence and more

If you have basic HTML skills, then with the help of the book by Robin Nixon, an experienced developer and author of numerous best-selling web mastering books, you will easily learn how to create dynamic sites characterized by a high level of user interaction.
Discover the combination of PHP and MySQL, learn how they make it easy to create modern websites, learn how to add javascript to these technologies, allowing you to create high-tech applications.
This tutorial looks at each technology separately, shows how to combine PHP, MySQL and javascript into a single whole, and gives an introduction to the latest web programming concepts. With detailed case studies and quizzes in each chapter, you'll be able to put what you've learned into practice.

This guide will help you:
— master the basics of PHP and object-oriented programming;
- thoroughly study MySQL, starting with the structure of databases and ending with the compilation of complex queries;
- create web pages that use PHP and MySQL to combine forms and other HTML components;
- learn javascript, starting with functions and event handling and ending with access to the Document Object Model (DOM);
— use software libraries and packages, including the Smarty system, the PEAR software repository, and the Yahoo! user interface;
- make Ajax calls and turn your website into a highly dynamic information environment;
- upload files and images to the website and work with them, check the data entered by the user;
- Ensure the security of your applications.

Queries not running fast enough? Wondering about the in-memory database features in 2014? Tired of phone calls from frustrated users? Grant Fritchey’s book SQL Server Query Performance Tuning is the answer to your SQL Server query performance problems. The book is revised to cover the very latest in performance optimization features and techniques, especially including the newly-added, in-memory database features formerly known under the code name Project Hekaton. This book provides the tools you need to approach your queries with performance in mind.

SQL Server Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You'll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server Query Performance Tuning into practice today.

Covers the in-memory features from Project Hekaton
Helps establish performance baselines and monitor against them
Guides in troubleshooting and eliminating bottlenecks that frustrated users
What you'll learn
— Establish performance baselines and monitor against them
— Recognize and eliminate bottlenecks leading to slow performance
- Deploy quick fixes when needed, following up with long term solutions
— Implement best-practices in T-SQL so as to minimize performance risk
— Design in the performance that you need through careful query and index design
— Take advantage of the very latest performance optimization features in SQL Server 2014
— Understand the new, in-memory database features formerly code-named as Project Hekaton

SQL book in 10 minutes offers simple and practical solutions for those who want to get results quickly. After working through all 22 lessons, each of which will take no more than 10 minutes, you will learn everything that is necessary for the practical application of SQL. The examples in the book are suitable for IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, MariaDB, and Apache OpenOffice Base. Illustrative examples will help you understand how SQL statements are structured. Tips will prompt short cuts to solutions. Warnings help you avoid common mistakes. Notes provide further clarification.

This tutorial is something like a "stamp of my memory" in the SQL language (DDL, DML), i.e. This is information that has accumulated along the way. professional activity and is constantly stored in my head. This is a sufficient minimum for me, which is used most often when working with databases. If the need arises to use more complete SQL constructs, then I usually turn to the MSDN library located on the Internet for help. In my opinion, keeping everything in your head is very difficult, and there is no particular need for this. But knowing the basic constructions is very useful, because. they are applicable almost in the same form in many relational databases such as Oracle, MySQL, Firebird. The differences are mainly in the data types, which may differ in details. There are not so many basic SQL language constructs, and with constant practice they are quickly remembered. For example, to create objects (tables, constraints, indexes, etc.) it is enough to have a text editor of the environment (IDE) at hand for working with a database, and there is no need to learn a visual toolkit sharpened for working with a specific type of database (MS SQL , Oracle, MySQL, Firebird, …). This is also convenient because the entire text is in front of your eyes, and you do not need to run through numerous tabs in order to create, for example, an index or a limit. When constantly working with the database, creating, modifying, and especially re-creating an object using scripts is many times faster than if it is done in visual mode. Also in script mode (respectively, with due care), it is easier to set and control the rules for naming objects (my subjective opinion). In addition, scripts are convenient to use when changes made in one database (for example, a test one) need to be transferred in the same form to another database (productive).

The SQL language is divided into several parts, here I will consider the 2 most important parts of it:

• DML - Data Manipulation Language (data manipulation language), which contains the following constructs:
  • SELECT - data selection
  • INSERT - inserting new data
  • UPDATE - data update
  • DELETE - deleting data
  • MERGE - data merging

Because I am a practitioner, as such there will be little theory in this textbook, and all constructions will be explained with practical examples. In addition, I believe that a programming language, and especially SQL, can only be mastered in practice, by touching it on your own and understanding what happens when you execute this or that construction.

This tutorial was created on the principle of Step by Step, i.e. it is necessary to read it sequentially and preferably immediately following the examples. But if along the way you need to learn about a command in more detail, then use a specific search on the Internet, for example, in the MSDN library.

When writing this tutorial, I used a MS SQL Server version 2014 database, and I used MS SQL Server Management Studio (SSMS) to run the scripts.

Briefly about MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a utility for Microsoft SQL Server for configuring, managing and administering database components. This utility contains a script editor (which we will mainly use) and a graphical program that works with objects and server settings. The main tool of SQL Server Management Studio is the Object Explorer, which allows the user to view, retrieve, and manage server objects. This text is partly borrowed from Wikipedia.

To create a new script editor, use the New Query button:

To change the current database, you can use the drop-down list:

To execute a specific command (or group of commands), select it and press the "Execute" button or press the "F5" key. If there is only one command in the editor at the moment, or if you need to execute all the commands, then you do not need to select anything.

After executing scripts, especially those that create objects (tables, columns, indexes), to see the changes, use Refresh from the context menu, highlighting the appropriate group (for example, Tables), the table itself, or the Columns group in it.

Actually, this is all we need to know to complete the examples given here. The rest of the SSMS utility is easy to learn on your own.

A bit of theory

A relational database (RDB, or further in the context of just a database) is a collection of tables interconnected. Roughly speaking, a database is a file in which data is stored in a structured form.

DBMS - the System for Managing these Databases, i.e. this is a set of tools for working with a specific type of database (MS SQL, Oracle, MySQL, Firebird, ...).

Note
Because in life, in colloquial speech, we mostly say: “Oracle DB”, or even just “Oracle”, actually meaning “Oracle DBMS”, then in the context of this tutorial the term DB will sometimes be used. From the context, I think it will be clear what exactly is at stake.

A table is a collection of columns. Columns can also be called fields or columns, all these words will be used as synonyms, expressing the same thing.

The table is the main object of the RDB, all RDB data is stored line by line in the columns of the table. Lines, records are also synonyms.

For each table, as well as its columns, names are given, by which they are subsequently referred to.
The object name (table name, column name, index name, etc.) in MS SQL can have a maximum length of 128 characters.

For reference– in the ORACLE database, object names can have a maximum length of 30 characters. Therefore, for a particular database, you need to develop your own rules for naming objects in order to meet the limit on the number of characters.

SQL is a language that allows you to query the database through the DBMS. In a particular DBMS, the SQL language may have a specific implementation (its own dialect).

DDL and DML are a subset of the SQL language:

• The DDL language is used to create and modify the database structure, i.e. to create/modify/delete tables and relationships.
• The DML language allows you to manipulate table data, i.e. with her lines. It allows you to select data from tables, add new data to tables, and update and delete existing data.

In SQL language, you can use 2 types of comments (single-line and multi-line):

Single line comment
And

/* multiline comment */

Actually, everything for the theory of this will be enough.

DDL - Data Definition Language (data description language)

For example, consider a table with data about employees, in the form familiar to a person who is not a programmer:

In this case, the table columns have the following names: Personnel number, Full name, Date of birth, E-mail, Position, Department.

Each of these columns can be characterized by the type of data it contains:

• Personnel number - integer
• full name - string
• Date of birth - date
• Email - string
• Position - string
• department - string

Column type is a characteristic that indicates what kind of data this column can store.

To begin with, it will be enough to remember only the following basic data types used in MS SQL:

Meaning	Notation in MS SQL	Description
Variable length string	varchar(N) And nvarchar(N)	With the number N, we can specify the maximum possible string length for the corresponding column. For example, if we want to say that the value of the "Name" column can contain a maximum of 30 characters, then we need to set its type to nvarchar (30). The difference between varchar and nvarchar is that varchar allows you to store strings in ASCII format, where one character occupies 1 byte, while nvarchar stores strings in Unicode format, where each character occupies 2 bytes. The varchar type should only be used if you are 100% sure that the field will not need to store Unicode characters. For example, varchar can be used to store email addresses because they usually contain only ASCII characters.
Fixed length string	char(N) And nchar(N)	This type differs from a variable length string in that if the length of the string is less than N characters, then it is always padded on the right up to the length of N spaces and stored in the database in this form, i.e. in the database it occupies exactly N characters (where one character occupies 1 byte for char and 2 bytes for nchar). In my practice, this type is very rarely used, and if it is used, then it is used mainly in the char (1) format, i.e. when the field is defined by a single character.
Integer	int	This type allows us to use only integers, both positive and negative, in the column. For reference (now it is not so relevant for us) - the range of numbers that the int type allows from -2 147 483 648 to 2 147 483 647. This is usually the main type that is used to set identifiers.
Real or real number	float	In simple terms, these are numbers in which a decimal point (comma) may be present.
date of	date	If the column needs to store only the Date, which consists of three components: Number, Month and Year. For example, 02/15/2014 (February 15, 2014). This type can be used for the column "Date of admission", "Date of birth", etc., i.e. in cases where it is important for us to fix only the date, or when the time component is not important to us and can be discarded, or if it is not known.
Time	time	This type can be used if the column needs to store only time data, i.e. Hours, Minutes, Seconds and Milliseconds. For example, 17:38:31.3231603 For example, the daily “Flight Departure Time”.
date and time	datetime	This type allows you to store both Date and Time at the same time. For example, 02/15/2014 5:38:31.323 PM For example, this could be the date and time of an event.
Flag	bit	This type is useful for storing Yes/No values, where Yes will be stored as 1 and No will be stored as 0.

Also, the value of the field, in the event that it is not prohibited, may not be specified, for this purpose the NULL keyword is used.

To run the examples, let's create a test database called Test.

A simple database (without specifying additional parameters) can be created by running the following command:

CREATE DATABASE Test
You can delete the database with the command (you should be very careful with this command):

DROP DATABASE Test
In order to switch to our database, you can run the command:

US Test
Alternatively, select the Test database from the drop-down list in the SSMS menu area. At work, I often use this method of switching between databases.

Now in our database we can create a table using the descriptions as they are, using spaces and Cyrillic characters:

CREATE TABLE [Employees]([Personnel Number] int, [Name] nvarchar(30), [Date of Birth] date, nvarchar(30), [Position] nvarchar(30), [Department] nvarchar(30))
In this case, we will have to enclose the names in square brackets […].

But in the database, for greater convenience, it is better to specify all the names of objects in Latin and not use spaces in the names. In MS SQL, usually in this case, each word begins with an uppercase letter, for example, for the "Personnel number" field, we could set the name PersonnelNumber. You can also use numbers in the name, for example, PhoneNumber1.

On a note
In some DBMS, the following format of names "PHONE_NUMBER" may be more preferable, for example, this format is often used in the ORACLE database. Naturally, when setting the field name, it is desirable that it does not match the keywords used in the DBMS.

For this reason, you can forget about the square bracket syntax and delete the [Employees] table:

DROP TABLE [Employees]
For example, a table with employees can be named "Employees" and its fields can be given the following names:

• ID - Personnel Number (Employee ID)
• Name - full name
• Birthday - Date of birth
• Email
• Position
• Department - Department

Very often, the word ID is used to name the identifier field.

Now let's create our table:

CREATE TABLE Employees(ID int, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
You can use the NOT NULL option to specify required columns.

For an already existing table, the fields can be redefined using the following commands:

Update ID field ALTER TABLE Employees ALTER COLUMN ID int NOT NULL -- update Name field ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL

On a note
The general concept of the SQL language for most DBMS remains the same (at least, I can judge this from the DBMS with which I had a chance to work). The difference between DDL in different DBMSs is mainly in data types (not only their names may differ here, but also the details of their implementation), the very specifics of the implementation of the SQL language may also differ slightly (i.e. the essence of the commands is the same, but there may be slight differences in the dialect, alas, but there is no one standard). Knowing the basics of SQL, you can easily switch from one DBMS to another, because. in this case, you will only need to understand the details of the implementation of commands in the new DBMS, i.e. in most cases, it will be enough just to draw an analogy.

Table creation CREATE TABLE Employees(ID int, -- in ORACLE type int is the equivalent (wrapper) for number(38) Name nvarchar2(30), -- nvarchar2 in ORACLE is equivalent to nvarchar in MS SQL Birthday date, Email nvarchar2(30) , Position nvarchar2(30), Department nvarchar2(30)); -- updating ID and Name fields (here MODIFY(…) is used instead of ALTER COLUMN ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- adding a PK (in this case, the construction looks like in MS SQL, it will be shown below) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
For ORACLE, there are differences in terms of the implementation of the varchar2 type, its encoding depends on the database settings and the text can be saved, for example, in UTF-8 encoding. In addition, the field length in ORACLE can be set both in bytes and in characters, for this, additional options BYTE and CHAR are used, which are specified after the field length, for example:

NAME varchar2(30 BYTE) -- field capacity will be 30 bytes NAME varchar2(30 CHAR) -- field capacity will be 30 characters
Which option will be used by default BYTE or CHAR, in case of a simple specification of the varchar2(30) type in ORACLE, depends on the database settings, it can also sometimes be set in the IDE settings. In general, sometimes you can easily get confused, so in the case of ORACLE, if the varchar2 type is used (and this is sometimes justified here, for example, when using UTF-8 encoding), I prefer to explicitly write CHAR (because it is usually more convenient to read the length of a string in characters ).

But in this case, if there is already some data in the table, then for the successful execution of the commands, it is necessary that the ID and Name fields in all rows of the table must be filled in. Let's demonstrate this with an example, insert data into the table in the ID, Position and Department fields, this can be done with the following script:

INSERT Employees(ID,Position,Department) VALUES (1000,N"Director",N"Administration"), (1001,N"Programmer",N"IT"), (1002,N"Accountant",N"Accounting" ), (1003,N"Senior programmer",N"IT")
In this case, the INSERT command will also throw an error, because when inserting, we did not specify the value of the required Name field.
In the event that we already had this data in the original table, then the command "ALTER TABLE Employees ALTER COLUMN ID int NOT NULL" would be successful, and the command "ALTER TABLE Employees ALTER COLUMN Name int NOT NULL" would issue an error message, that there are NULL (not specified) values ​​in the Name field.

Let's add values ​​for the Name field and fill in the data again:

Also, the NOT NULL option can be used directly when creating a new table, i.e. in the context of the CREATE TABLE command.

First, delete the table with the command:

DROP TABLE Employees
Now let's create a table with mandatory ID and Name columns:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
You can also write NULL after the column name, which will mean that NULL values ​​(not specified) will be allowed in it, but this is not necessary, since this characteristic is implied by default.

If, on the contrary, you want to make an existing column optional, then use the following command syntax:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL
Or simply:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)
With this command, we can also change the field type to another compatible type, or change its length. For example, let's expand the Name field to 50 characters:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

primary key

When creating a table, it is desirable that it has a unique column or a set of columns that is unique for each of its rows - a record can be uniquely identified by this unique value. This value is called the primary key of the table. For our Employees table, this unique value could be the ID column (which contains "Employee Personnel Number" - even if in our case this value is unique for each employee and cannot be repeated).

You can create a primary key to an existing table using the command:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)
Where "PK_Employees" is the name of the constraint responsible for the primary key. Usually, the primary key is named with the prefix "PK_" followed by the table name.

If the primary key consists of several fields, then these fields must be listed in brackets separated by commas:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(field1,field2,…)
It is worth noting that in MS SQL all fields that are included in the primary key must have the NOT NULL characteristic.

Also, the primary key can be defined directly when creating a table, i.e. in the context of the CREATE TABLE command. Let's delete the table:

DROP TABLE Employees
And then create it using the following syntax:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) -- describe PK after all fields as a constraint)
After creation, fill in the table data:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Director",N"Administration",N"Ivanov II.), (1001,N"Programmer",N"IT",N" Petrov P.P."), (1002,N"Accountant",N"Accounting",N"Sidorov S.S."), (1003,N"Senior Programmer",N"IT",N"Andreev A. BUT.")
If the primary key in the table consists of only the values ​​of one column, then the following syntax can be used:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- specify Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
In fact, the constraint name can be omitted, in which case it will be given a system name (like "PK__Employee__3214EC278DA42077"):

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), PRIMARY KEY(ID))
Or:

CREATE TABLE Employees(ID int NOT NULL PRIMARY KEY, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
But I would recommend that you always explicitly set the name of the constraint for permanent tables, because by an explicitly given and understandable name, it will subsequently be easier to manipulate it, for example, you can delete it:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees
But such a short syntax, without specifying the names of restrictions, is convenient to use when creating temporary database tables (the name of a temporary table begins with # or ##), which will be deleted after use.

Let's summarize

So far we have covered the following commands:

• CREATE TABLE table_name (enumeration of fields and their types, restrictions) - used to create a new table in the current database;
• DROP TABLE table_name - used to delete a table from the current database;
• ALTER TABLE table_name ALTER COLUMN column_name … – used to update the column type or to change its settings (for example, to set the NULL or NOT NULL characteristic);
• ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(field1, field2,…) – adding a primary key to an existing table;
• ALTER TABLE table_name DROP CONSTRAINT constraint_name - remove the constraint from the table.

A little about temporary tables

Clipping from MSDN. There are two types of temporary tables in MS SQL Server: local (#) and global (##). Local temporary tables are only visible to their creators until the connection session with the SQL Server instance is terminated once they are first created. Local temporary tables are automatically deleted after a user disconnects from an instance of SQL Server. Global temporary tables are visible to all users during any connection sessions after these tables are created and are deleted when all users referencing these tables disconnect from the instance of SQL Server.

Temporary tables are created in the tempdb system database, i.e. creating them, we do not clog the main database, otherwise temporary tables are completely identical to regular tables, they can also be deleted using the DROP TABLE command. Local (#) temporary tables are more commonly used.

To create a temporary table, you can use the CREATE TABLE command:

CREATE TABLE #Temp(ID int, Name nvarchar(30))
Since a temporary table in MS SQL is similar to a regular table, you can also delete it accordingly with the DROP TABLE command:

DROP TABLE #Temp

You can also create a temporary table (as well as a regular table) and immediately fill it with the data returned by the query using the SELECT ... INTO syntax:

SELECT ID,Name INTO #Temp FROM Employees

On a note
In different DBMS, the implementation of temporary tables may differ. For example, in the ORACLE and Firebird DBMS, the structure of temporary tables must be defined in advance by the CREATE GLOBAL TEMPORARY TABLE command, indicating the specifics of storing data in it, then the user sees it among the main tables and works with it as with a regular table.

Normalization of the database - splitting into sub-tables (directories) and determining relationships

Our current Employees table has the disadvantage that the user can enter any text in the Position and Department fields, which is primarily fraught with errors, since for one employee he can simply indicate “IT” as the department, and for the second employee, for example , enter "IT department", have the third "IT". As a result, it will not be clear what the user meant, i.e. Are these employees employees of the same department, or did the user describe himself and these are 3 different departments? And even more so, in this case, we will not be able to correctly group the data for some report, where it may be required to show the number of employees in the context of each department.

The second drawback is the amount of storage of this information and its duplication, i.e. for each employee, the full name of the department is indicated, which requires a place in the database to store each character from the name of the department.

The third drawback is the difficulty of updating these fields if the name of a position changes, for example, if you need to rename the position “Programmer” to “Junior programmer”. In this case, we will have to make changes to each line of the table, in which the Position is equal to "Programmer".

To avoid these shortcomings, the so-called normalization of the database is used - splitting it into sub-tables, reference tables. It is not necessary to climb into the jungle of theory and study what normal forms are, it is enough to understand the essence of normalization.

Let's create 2 reference tables "Positions" and "Departments", the first one will be called Positions, and the second one, respectively, Departments:

CREATE TABLE Positions(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Name nvarchar(30) NOT NULL) CREATE TABLE Departments(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY, Name nvarchar(30 ) NOT NULL)
Note that here we used the new IDENTITY option, which means that the data in the ID column will be numbered automatically, starting from 1, with a step of 1, i.e. when new records are added, they will be sequentially assigned the values ​​1, 2, 3, and so on. Such fields are usually called auto-incrementing. Only one field with the IDENTITY property can be defined in a table, and usually, but not necessarily, such a field is the primary key for that table.

On a note
In different DBMS, the implementation of fields with a counter can be done differently. In MySQL, for example, such a field is defined using the AUTO_INCREMENT option. In ORACLE and Firebird, this functionality could previously be emulated using SEQUENCEs. But as far as I know, ORACLE has now added the GENERATED AS IDENTITY option.

Let's fill in these tables automatically, based on the current data recorded in the Position and Department fields of the Employees table:

Fill in the Name field of the Positions table with unique values ​​from the Position field of the Employees table INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL -- discard records with no position specified
We will do the same for the Departments table:

INSERT Departments(Name) SELECT DISTINCT Department FROM Employees WHERE Department IS NOT NULL
If we now open the Positions and Departments tables, we will see a numbered set of values ​​​​by the ID field:

SELECT * FROM Positions

SELECT * FROM Departments

These tables will now play the role of directories for setting positions and departments. We will now refer to job and department IDs. First of all, let's create new fields in the Employees table to store the ID data:

Add field for position ID ALTER TABLE Employees ADD PositionID int -- add field for department ID ALTER TABLE Employees ADD DepartmentID int
The type of reference fields must be the same as in the directories, in this case it is int.

You can also add several fields to the table at once with one command, listing the fields separated by commas:

ALTER TABLE Employees ADD PositionID int, DepartmentID int
Now let's write links (reference constraints - FOREIGN KEY) for these fields, so that the user does not have the opportunity to write into these fields, values ​​that are not among the ID values ​​in the directories.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
And we will do the same for the second field:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
Now the user will be able to enter only ID values ​​from the corresponding reference book into these fields. Accordingly, in order to use a new department or position, he will first have to add a new entry to the corresponding directory. Because positions and departments are now stored in directories in a single copy, then to change the name, it is enough to change it only in the directory.

The name of the referential constraint is usually compound, it consists of the prefix "FK_", followed by the table name, and after the underscore, comes the field name that refers to the identifier of the lookup table.

The identifier (ID) is usually an internal value that is used only for links and what value is stored there, in most cases, it is absolutely indifferent, so there is no need to try to get rid of holes in the sequence of numbers that arise in the course of working with a table, for example, after deleting records from the handbook.

ALTER TABLE table ADD CONSTRAINT constraint_name FOREIGN KEY(field1,field2,…) REFERENCES lookup table(field1,field2,…)
In this case, in the "table_reference" table, the primary key is represented by a combination of several fields (field1, field2, ...).

Actually, now let's update the PositionID and DepartmentID fields with the ID values ​​from the directories. Let's use the UPDATE DML command for this purpose:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e
Let's see what happens by running the query:

SELECT * FROM Employees

That's it, the PositionID and DepartmentID fields are filled with the corresponding positions and departments with IDs of need in the Position and Department fields in the Employees table now, you can delete these fields:

ALTER TABLE Employees DROP COLUMN Position,Department
The table now looks like this:

SELECT * FROM Employees

ID	Name	birthday	Email	PositionID	DepartmentID
1000	Ivanov I.I.	NULL	NULL	2	1
1001	Petrov P.P.	NULL	NULL	3	3
1002	Sidorov S.S.	NULL	NULL	1	2
1003	Andreev A.A.	NULL	NULL	4	3

Those. we eventually got rid of storing redundant information. Now, by the position and department numbers, we can uniquely determine their names using the values ​​in the lookup tables:

SELECT e.ID,e.Name,p.Name PositionName,d.Name DepartmentName FROM Employees e LEFT JOIN Departments d ON d.ID=e.DepartmentID LEFT JOIN Positions p ON p.ID=e.PositionID

In the Object Inspector, we can see all the objects created for a given table. From here you can also perform various manipulations with these objects - for example, rename or delete objects.

It is also worth noting that a table can refer to itself, i.e. you can create a recursive link. For example, let's add another field ManagerID to our table with employees, which will indicate the employee to whom this employee reports. Let's create a field:

ALTER TABLE Employees ADD ManagerID int
The NULL value is allowed in this field, the field will be empty if, for example, there are no superiors over the employee.

Now let's create a FOREIGN KEY on the Employees table:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
Let's now create a diagram and see how the relationships between our tables look on it:

As a result, we should see the following picture (the Employees table is related to the Positions and Depertments tables, and also refers to itself):

Finally, it is worth mentioning that reference keys can include additional options ON DELETE CASCADE and ON UPDATE CASCADE, which tell how to behave when deleting or updating a record referenced in the lookup table. If these options are not specified, then we cannot change the ID in the directory table of the entry that has links from another table, nor can we delete such an entry from the directory until we delete all rows that refer to this entry or, Let's update these lines of references to another value.

For example, let's recreate the table with the ON DELETE CASCADE option for FK_Employees_DepartmentID:

DROP TABLE Employees CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID ) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)) INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID )VALUES (1000,N"Ivanov I.I.","19550219",2,1,NULL), (1001,N"Petrov P.P.","19831203",3,3,1003), (1002 ,N"Sidorov S.S.","19760607",1,2,1000), (1003,N"Andreev A.A.","19820417",4,3,1000)
Let's remove the department with ID 3 from the Departments table:

DELETE Departments WHERE ID=3
Let's look at the data in the Employees table:

SELECT * FROM Employees

ID	Name	birthday	Email	PositionID	DepartmentID	ManagerID
1000	Ivanov I.I.	1955-02-19	NULL	2	1	NULL
1002	Sidorov S.S.	1976-06-07	NULL	1	2	1000

As you can see, the data for department 3 has also been deleted from the Employees table.

The ON UPDATE CASCADE option behaves similarly, but it takes effect when updating the ID value in the directory. For example, if we change the position ID in the positions directory, then in this case the DepartmentID in the Employees table will be updated to the new ID value that we set in the directory. But in this case, it simply won’t be possible to demonstrate this, because. the ID column in the Departments table has the IDENTITY option, which will prevent us from executing the following query (change department ID 3 to 30):

UPDATE Departments SET ID=30 WHERE ID=3
The main thing is to understand the essence of these 2 options ON DELETE CASCADE and ON UPDATE CASCADE. I use these options on very rare occasions, and I recommend that you think carefully before specifying them in a referential constraint. if you accidentally delete a record from the reference table, this can lead to big problems and create a chain reaction.

Let's restore department 3:

Give permission to add/change IDENTITY values ​​SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"IT") -- deny adding/changing IDENTITY values ​​SET IDENTITY_INSERT Departments OFF
Completely clear the Employees table using the TRUNCATE TABLE command:

TRUNCATE TABLE Employees
And again, reload the data into it using the previous INSERT command:

INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov II","19550219",2,1,NULL), (1001,N"Petrov P.P." ,"19831203",3,3,1003), (1002,N"Sidorov S.S.","19760607",1,2,1000), (1003,N"Andreev A.A.","19820417" ,4,3,1000)

Let's summarize

At the moment, a few more DDL commands have been added to our knowledge:

• Adding the IDENTITY property to the field - allows you to make this field automatically filled (counter field) for the table;
• ALTER TABLE table_name ADD list_of_fields_with_characteristics – allows you to add new fields to the table;
• ALTER TABLE table_name DROP COLUMN list_of_fields - allows you to remove fields from the table;
• ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(fields) REFERENCES lookup_table(fields) – allows you to define a relationship between a table and a lookup table.

Other restrictions - UNIQUE, DEFAULT, CHECK

With the UNIQUE constraint, you can say that the value for each row in a given field or set of fields must be unique. In the case of the Employees table, we can impose such a restriction on the Email field. Just pre-populate Email with values ​​if they are not already defined:

UPDATE Employees SET Email=" [email protected]"WHERE ID=1000 UPDATE Employees SET Email=" [email protected]" WHERE ID=1001 UPDATE Employees SET Email=" [email protected]"WHERE ID=1002 UPDATE Employees SET Email=" [email protected]" WHERE ID=1003
And now you can impose a unique-restriction on this field:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)
Now the user will not be able to enter the same E-Mail for several employees.

The uniqueness constraint is usually named as follows - first comes the prefix "UQ_", then the name of the table, and after the underscore is the name of the field on which this constraint is applied.

Accordingly, if a combination of fields should be unique in the context of the rows of the table, then we list them separated by commas:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(field1,field2,…)
By adding a DEFAULT constraint to a field, we can set a default value that will be substituted if the field is not listed in the INSERT command field list when a new record is inserted. This restriction can be set directly when creating a table.

Let's add a new field "Recruitment Date" to the Employees table and name it HireDate and say that the default value for this field will be the current date:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
Or if the HireDate column already exists, then the following syntax can be used:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
Here I did not specify the name of the constraint, because in the case of DEFAULT, I was of the opinion that this is not so critical. But if you do it in a good way, then, I think, you don’t need to be lazy and you should set a normal name. This is done as follows:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Since this column did not exist before, when it is added to each record, the current date value will be inserted into the HireDate field.

When adding a new entry, the current date will also be inserted automatically, of course, if we do not explicitly set it, i.e. not specified in the list of columns. Let's show this with an example without specifying the HireDate field in the list of added values:

INSERT Employees(ID,Name,Email)VALUES(1004,N"Sergeev S.S."," [email protected]")
Let's see what happened:

SELECT * FROM Employees

ID	Name	birthday	Email	PositionID	DepartmentID	ManagerID	HireDate
1000	Ivanov I.I.	1955-02-19	[email protected]	2	1	NULL	2015-04-08
1001	Petrov P.P.	1983-12-03	[email protected]	3	4	1003	2015-04-08
1002	Sidorov S.S.	1976-06-07	[email protected]	1	2	1000	2015-04-08
1003	Andreev A.A.	1982-04-17	[email protected]	4	3	1000	2015-04-08
1004	Sergeev S.S.	NULL	[email protected]	NULL	NULL	NULL	2015-04-08

The check constraint CHECK is used when it is necessary to check the values ​​inserted into the field. For example, let's impose this restriction on the personnel number field, which is our employee identifier (ID). Using this constraint, let's say that personnel numbers must have a value from 1000 to 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)
The constraint is usually named the same, first with the "CK_" prefix, then the table name and the name of the field on which the constraint is applied.

Let's try to insert an invalid entry to check that the restriction works (we should get the corresponding error):

INSERT Employees(ID,Email) VALUES(2000," [email protected]")
Now let's change the value to be inserted to 1500 and make sure the record is inserted:

INSERT Employees(ID,Email) VALUES(1500," [email protected]")
You can also create UNIQUE and CHECK constraints without specifying a name:

ALTER TABLE Employees ADD UNIQUE(Email) ALTER TABLE Employees ADD CHECK(ID BETWEEN 1000 AND 1999)
But this is not a good practice and it is better to specify the name of the constraint explicitly, because to figure out later what will be more difficult, you will need to open the object and see what it is responsible for.

With a good name, a lot of information about a constraint can be learned directly from its name.

And, accordingly, all these restrictions can be created immediately when creating a table, if it does not already exist. Let's delete the table:

DROP TABLE Employees
And recreate it with all the created constraints with one CREATE TABLE command:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- for DEFAULT I will throw a CONSTRAINT PK_Employees PRIMARY KEY exception (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1009))

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES (1000,N"Ivanov I.I.","19550219"," [email protected]",2,1), (1001,N"Petrov P.P.","19831203"," [email protected]",3,3), (1002,N"Sidorov S.S.","19760607"," [email protected]",1,2), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3)

A little about indexes created when creating PRIMARY KEY and UNIQUE constraints

As you can see in the screenshot above, when creating the PRIMARY KEY and UNIQUE constraints, indexes with the same names (PK_Employees and UQ_Employees_Email) were automatically created. By default, the index for the primary key is created as CLUSTERED, and for all other indexes as NONCLUSTERED. It is worth saying that the concept of a clustered index is not available in all DBMS. A table can only have one CLUSTERED index. CLUSTERED - means that the records of the table will be sorted by this index, it can also be said that this index has direct access to all table data. It so to say the main index of the table. To put it even rougher, it is an index screwed to the table. The clustered index is a very powerful tool that can help with query optimization, just keep that in mind. If we want to say that the clustered index is used not in the primary key, but for another index, then when creating the primary key, we must specify the NONCLUSTERED option:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY NONCLUSTERED(field1,field2,…)
For example, let's make the constraint index PK_Employees non-clustered, and the constraint index UQ_Employees_Email clustered. First of all, let's remove these restrictions:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Email
And now let's create them with the CLUSTERED and NONCLUSTERED options:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (ID) ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE CLUSTERED (Email)
Now, when we select from the Employees table, we can see that the records are sorted by the clustered index UQ_Employees_Email:

SELECT * FROM Employees

ID	Name	birthday	Email	PositionID	DepartmentID	HireDate
1003	Andreev A.A.	1982-04-17	[email protected]	4	3	2015-04-08
1000	Ivanov I.I.	1955-02-19	[email protected]	2	1	2015-04-08
1001	Petrov P.P.	1983-12-03	[email protected]	3	3	2015-04-08
1002	Sidorov S.S.	1976-06-07	[email protected]	1	2	2015-04-08

Prior to this, when the clustered index was the PK_Employees index, records were by default sorted by the ID field.

But in this case, this is just an example that shows the essence of the clustered index, because. most likely, queries will be made to the Employees table by the ID field, and in some cases, it may itself act as a reference.

For directories, it is usually advisable that the clustered index be built on the primary key, because in requests, we often refer to the directory identifier to obtain, for example, the name (Position, Department). Here we recall what I wrote about above, that the clustered index has direct access to the rows of the table, and it follows that we can get the value of any column without additional overhead.

The clustered index is beneficial to apply to the fields that are selected most often.

Sometimes tables create a key by a surrogate field, in which case it is useful to keep the CLUSTERED index option for a more appropriate index and specify the NONCLUSTERED option when creating a surrogate primary key.

Let's summarize

At this stage, we got acquainted with all types of restrictions, in their simplest form, which are created by a command like "ALTER TABLE table_name ADD CONSTRAINT constraint_name ...":

• PRIMARY KEY- primary key;
• FOREIGN KEY- setting up links and monitoring the referential integrity of data;
• UNIQUE- allows you to create uniqueness;
• CHECK- allows you to carry out the correctness of the entered data;
• DEFAULT– allows you to set the default value;
• It is also worth noting that all restrictions can be removed using the command " ALTER TABLE table_name DROP CONSTRAINT constraint_name".

We also partially touched upon the topic of indices and analyzed the concept of cluster ( CLUSTERED) and non-clustered ( NONCLUSTERED) index.

Creating standalone indexes

Self-sufficiency here refers to indexes that are not created for a PRIMARY KEY or UNIQUE constraint.

Indexes on a field or fields can be created with the following command:

CREATE INDEX IDX_Employees_Name ON Employees(Name)
You can also specify the CLUSTERED, NONCLUSTERED, UNIQUE options here, and you can also specify the sorting direction for each individual field ASC (default) or DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees(Email DESC)
When creating a non-clustered index, the NONCLUSTERED option can be omitted, as it is implied by default, it is shown here simply to indicate the position of the CLUSTERED or NONCLUSTERED option in the command.

You can remove the index with the following command:

DROP INDEX IDX_Employees_Name ON Employees
Simple indexes, just like constraints, can be created in the context of the CREATE TABLE command.

For example, let's delete the table again:

DROP TABLE Employees
And recreate it with all created constraints and indexes with one CREATE TABLE command:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID), CONSTRAINT UQ_Employees_Email CONSTRAINT CHECK(ID BETWEEN 1000 AND 1999), INDEX IDX_Employees_Name(Name))
Finally, insert into the table of our employees:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov II","19550219"," [email protected]",2,1,NULL), (1001,N"Petrov P.P.","19831203"," [email protected]",3,3,1003), (1002,N"Sidorov S.S.","19760607"," [email protected]",1,2,1000), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3,1000)
Additionally, it is worth noting that values ​​can be included in a non-clustered index by specifying them in INCLUDE. Those. in this case, the INCLUDE index will somewhat resemble a clustered index, only now the index is not attached to the table, but the necessary values ​​are attached to the index. Accordingly, such indexes can greatly improve the performance of select queries (SELECT), if all of the listed fields are in the index, then it is possible that there will be no need to access the table at all. But this naturally increases the size of the index, because the values ​​of the listed fields are duplicated in the index.

Clipping from MSDN. General Command Syntax for Creating Indexes

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED ] INDEX index_name ON