A selection of materials for studying databases and SQL. Learning SQL

• Tutorial

What is this tutorial about?

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

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

MS SQL database was used to write this tutorial. Server version 2014, I used MS SQL Server to execute scripts Management Studio(SSMS).

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 server objects and settings. The main tool of SQL Server Management Studio is Object Explorer, which allows the user to view, retrieve, and manage server objects. This text partially 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 the “F5” key. If there is only one command currently in the editor, or you need to execute all commands, then you do not need to select anything.

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

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

A little theory

DBMS – Database Management System, 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 textbook the term DB will sometimes be used. From the context, I think it will be clear what exactly we are talking about.

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 row by row in the columns of the table. Lines and records are also synonyms.

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

For reference– in the ORACLE database, object names can have a maximum length of 30 characters. Therefore, for a specific 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 a database using a DBMS. In a specific 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, as well as update and delete existing data.

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

One line comment
And

/* multiline comment */

Actually, this will be enough for the theory.

DDL – Data Definition Language

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 - line

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

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:

USE Test
Alternatively, select the Test database from the drop-down list in the SSMS menu area. When working, 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 names in square brackets […].

But in the database, for greater convenience, it is better to specify all object names in Latin and not use spaces in names. In MS SQL, usually in this case each word begins with a capital 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.

Note
In some DBMSs, the following naming format “PHONE_NUMBER” may be more preferable; for example, this format is often used in the ORACLE database. Naturally, when specifying a field name, it is desirable that it does not coincide with the keywords used in the DBMS.

For this reason, you can forget about the square brackets 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 – Email
• Position - Position
• Department - Department

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))
To specify required columns, you can use the NOT NULL option.

For an existing table, 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

Note
The general concept of the SQL language remains the same for most DBMSs (at least, this is what I can judge from the DBMSs that I have worked with). The differences between DDL in different DBMSs mainly lie in the data types (not only their names may differ here, but also the details of their implementation), and 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 dialect, alas, but there is no one standard). Having mastered 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, simply drawing an analogy will suffice.

Creating a table CREATE TABLE Employees(ID int, -- in ORACLE the int type 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 the 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 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 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 specified 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) -- the field capacity will be 30 bytes NAME varchar2(30 CHAR) -- the field capacity will be 30 characters
Which option will be used by default BYTE or CHAR, in the case of simply specifying the varchar2(30) type in ORACLE, depends on the database settings, and it can 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 (since it is usually more convenient to calculate the length of the string in characters ).

But in this case, if there is already some data in the table, then for successful execution of commands it is necessary that the ID and Name fields in all rows of the table 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 generate an error, because When inserting, we did not specify the value of the required Name field.
If we already had this data in the original table, then the command “ALTER TABLE Employees ALTER COLUMN ID int NOT NULL” would be executed successfully, and the command “ALTER TABLE Employees ALTER COLUMN Name int NOT NULL” would produce an error message, that the Name field contains NULL (unspecified) values.

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

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

First, delete the table using the command:

DROP TABLE Employees
Now let’s create a table with the required 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

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. Typically, 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 parentheses, 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.

The primary key can also be determined 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 we'll 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 the PK after all fields as a limitation)
After creation, fill the table with data:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Director",N"Administration",N"Ivanov I.I."), (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. A.")
If the primary key in a table consists of only the values ​​of one column, then you can use the following syntax:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- specify as a characteristic of the field Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
In fact, you don’t have to specify the name of the constraint, in which case it will be assigned 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 for permanent tables you always explicitly specify the name of the constraint, because With an explicitly specified and understandable name, it will be easier to manipulate it later; for example, you can delete it:

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

Let's summarize

• CREATE TABLE table_name (listing 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 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 – removes a constraint from the table.

A little about temporary tables

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

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, it can also be deleted using the DROP TABLE command:

DROP TABLE #Temp

You can also create a temporary table (like 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

Note
The implementation of temporary tables may differ in different DBMSs. For example, in the ORACLE and Firebird DBMS, the structure of temporary tables must be determined 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.

Database normalization – splitting into subtables (directories) and identifying connections

The second disadvantage is the volume of storage of this information and its duplication, i.e. For each employee, the full name of the department is indicated, which requires space in the database to store each character from the department name.

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 row of the table whose Position is equal to “Programmer”.

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

Let's create 2 reference tables “Positions” and “Departments”, let's call the first Positions, and the second, 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 says that the data in the ID column will be numbered automatically, starting from 1, in increments of 1, i.e. When adding new records, they will be sequentially assigned the values ​​1, 2, 3, etc. Such fields are usually called auto-incrementing. A table can only have one field defined with the IDENTITY property, and usually, but not necessarily, that field is the primary key for that table.

Note
In different DBMSs, 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 SEQUENCE. But as far as I know, ORACLE has now added the GENERATED AS IDENTITY option.

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

We fill 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 for which the position is not specified
Let's 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 ​​for the ID field:

SELECT * FROM Positions

SELECT * FROM Departments

These tables will now play the role of reference books for specifying 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 identifier data:

Add a field for position ID ALTER TABLE Employees ADD PositionID int -- add a field for department ID ALTER TABLE Employees ADD DepartmentID int
The type of reference fields must be the same as in 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 restrictions - 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 ​​​​found in the directories.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
And we'll 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 directory in 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 one single copy, so to change the name, it is enough to change it only in the directory.

The name of a reference constraint is usually a composite name, consisting of the prefix "FK_", followed by the table name, and followed by an underscore, followed by the name of the field that refers to the reference table identifier.

An identifier (ID) is usually an internal value that is used only for relationships and what value is stored there is completely indifferent in most cases, so there is no need to try to get rid of holes in the sequence of numbers that arise while working with the table, for example, after deleting records from the directory.

ALTER TABLE table ADD CONSTRAINT constraint_name FOREIGN KEY(field1,field2,…) REFERENCES reference_table(field1,field2,…)
In this case, in the “reference_table” 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 ID values ​​from the directories. Let's use the DML UPDATE 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 request:

SELECT * FROM Employees

That’s it, the PositionID and DepartmentID fields are filled with the identifiers corresponding to positions and departments; the Position and Department fields are no longer needed in the Employees table, you can delete these fields:

ALTER TABLE Employees DROP COLUMN Position,Department
Now our table looks like this:

SELECT * FROM Employees

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 perform various manipulations with these objects - for example, rename or delete objects.

It is also worth noting that the 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 he reports this employee. Let's create a field:

ALTER TABLE Employees ADD ManagerID int
This field allows a NULL value; the field will be empty if, for example, there are no superiors over the employee.

Now let's create a FOREIGN KEY for 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 connected to the Positions and Depertments tables, and also refers to itself):

Finally, it is worth saying that reference keys can include additional options ON DELETE CASCADE and ON UPDATE CASCADE, which indicate how to behave when deleting or updating a record that is referenced in the reference table. If these options are not specified, then we cannot change the ID in the directory table for a record that is referenced from another table, and we will also not be able to delete such a record from the directory until we delete all rows referencing this record or, Let’s update the references in these lines to a different value.

For example, let's recreate the table specifying 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,Man agerID )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 delete 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

The ON UPDATE CASCADE option behaves similarly, but it is effective when updating the ID value in the directory. For example, if we change the ID of a position in the position 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 not allow us to execute 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 very rarely and recommend that you think carefully before specifying them in a reference constraint, because if you accidentally delete an entry from a directory table, this can lead to big problems and create a chain reaction.

Let's restore department 3:

We give permission to add/change IDENTITY value SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"IT") -- we prohibit adding/change IDENTITY value SET IDENTITY_INSERT Departments OFF
Let's completely clear the Employees table using the TRUNCATE TABLE command:

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

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 summarize

• Adding the IDENTITY property to a field – allows you to make this field an automatically populated field (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_fields – allows you to remove fields from the table;
• ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(fields) REFERENCES table_reference (fields) – allows you to define the relationship between the table and the reference table.

Other restrictions – UNIQUE, DEFAULT, CHECK

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
Now you can impose a uniqueness constraint 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.

A unique constraint is usually named as follows - first comes the prefix “UQ_”, then the name of the table and after the underscore comes the name of the field on which this constraint is applied.

Accordingly, if a combination of fields must be unique in the context of table rows, 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 specify a default value that will be substituted if, when inserting a new record, this field is not listed in the list of fields of the INSERT command. This restriction can be set directly when creating the table.

Let's add a new field “Hire Date” to the Employees table and call it HireDate and say that the default value for this field will be 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 have 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
That's how of this column did not exist before, then when you add it 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, unless we explicitly set it, i.e. We will not indicate it 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

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

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

INSERT Employees(ID,Email) VALUES(2000," [email protected]")
Now let’s change the inserted value to 1500 and make sure that 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 very good practice and it is better to specify the name of the constraint explicitly, because To figure it out later, which will be more difficult, you will need to open the object and look at what it is responsible for.

With a good name, a lot of information about the 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 exist yet. Let's delete the table:

DROP TABLE Employees
And we will recreate it with all the created restrictions 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 make an exception 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 UQ_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_ID CHECK (ID BETWE EN 1000 AND 1999))

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 the indexes created when creating PRIMARY KEY and UNIQUE constraints

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
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, by selecting from the Employees table, we will see that the records are sorted by the UQ_Employees_Email clustered index:

SELECT * FROM Employees

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

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

It is advantageous to apply a cluster index to fields that are sampled most frequently.

Sometimes tables are created with a key based on a surrogate field; in this case, it can be useful to save the CLUSTERED index option for a more suitable index and specify the NONCLUSTERED option when creating a surrogate primary key.

Let's summarize

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

Creating standalone indexes

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

CREATE INDEX IDX_Employees_Name ON Employees(Name)
Also here you can specify the options CLUSTERED, NONCLUSTERED, UNIQUE, and you can also specify the sorting direction of 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, because it is implied by default and is shown here simply to indicate the position of the CLUSTERED or NONCLUSTERED option in the command.

You can delete the index with the following command:

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

For example, let's delete the table again:

DROP TABLE Employees
And we will recreate it with all the created restrictions 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_Emplo yees_Email UNIQUE(Email), CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999), INDEX IDX_Employees_Name(Name))
Finally, let’s insert our employees into the table:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov I.I.","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 you can include values ​​in a non-clustered index by specifying them in INCLUDE. Those. in this case, the INCLUDE index will be somewhat reminiscent of 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 selection queries (SELECT); if all the listed fields are in the index, then access to the table may not be needed at all. But this naturally increases the size of the index, because... the values ​​of the listed fields are duplicated in the index.

Extract from MSDN. General command syntax for creating indexes

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