Tutorial on SQL (DDL, DML) on the example of MS SQL Server dialect

Total: 3 Average: 4

Introduction

This tutorial includes information about SQL (DDL, DML) that I have gathered during my professional life. This is the minimum you need to know while working with databases. If there is a need to use complex SQL constructions, then usually I surf the MSDN library, which can be easily found on the internet. To my mind, it is very difficult to keep everything in your head and, by the way, there is no need for this. I recommend that you should know all the main constructions used in most relational databases such as Oracle, MySQL, and Firebird. Still, they may differ in data types. For example, to create objects (tables, constraints, indexes, etc.), you may simply use integrated development environment (IDE) to work with databases and there is no need in studying visual tools for a particular database type (MS SQL, Oracle, MySQL, Firebird, etc.). This is convenient because you can see the whole text, and you do not need to look through numerous tabs to create, for example, an index or a constraint. If you are constantly working with databases, creating, modifying, and especially rebuilding an object using scripts is much faster than in a visual mode. Besides, in my opinion, in the script mode (with due precision), it is easier to specify and control rules for naming objects. In addition, it is convenient to use scripts when you need to transfer database changes from a test database to a production database.

SQL is divided into several parts. In my article, I will review the most important ones:

DDL – Data Definition Language

DML – Data Manipulation Language, which includes the following constructions:

  • SELECT – data selection
  • INSERT – new data insertion
  • UPDATE – data update
  • DELETE – data deletion
  • MERGE – data merging

I will explain all the constructions in study cases. In addition, I think that a programming language, especially SQL, should be studied in practice for better understanding.

This is a step-by-step tutorial, where you need to perform examples while reading it. However, if you need to know command in details, then surf the Internet, for example, MSDN.

When creating this tutorial, I have used the MS SQL Server database, version 2014, and MS SQL Server Management Studio (SSMS) to execute scripts.

Briefly about MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is the Microsoft SQL Server utility for configuring, managing and administering database components. It includes a script editor and a graphics program that works with server objects and settings. The main tool of SQL Server Management Studio is Object Explorer, which allows a user to view, retrieve, and manage server objects. This text is partially taken from Wikipedia.

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

new query

To switch from the current database, you can use the drop-down menu:

test

To execute a particular command or set of commands, highlight it and press the Execute button or F5. If there is only one command in the editor or you need to execute all commands, then do not highlight anything.

Execute

After you have executed scripts that create objects (tables, columns, indexes), select the corresponding object (for example, Tables or Columns) and then click Refresh on the shortcut menu to see the changes.

refresh

Actually, this is all you need to know to execute the examples provided herein.

Theory

A relational database is a set of tables linked together. In general, a database is a file that stores structured data.

Database Management System (DBMS) is a set of tools to work with particular database types (MS SQL, Oracle, MySQL, Firebird, etc.).

Note: As in our daily lives, we say “Oracle DB” or just “Oracle” actually meaning “Oracle DBMS”, then in this tutorial, I will use the term “database”.

A table is a set of columns. Very often, you can hear the following definitions of these terms: fields, rows, and records, which mean the same.

A table is the main object of the relational database. All the data is stored row by row in table columns.

For each table as well as for its columns, you need to specify a name, according to which you can find a required item.

The name of the object, table, column, and index may have the minimum length – 128 symbols.

Note: In Oracle databases, an object name may have the minimum length – 30 symbols. Thus, in a particular database, it is necessary to create custom rules for object names.

SQL is a language that allows executing queries in databases via DBMS. In a particular DBMS, an SQL language may have its own dialect.

DDL and DML –  the SQL sublanguage:

  • The DDL language serves for creating and modifying a database structure (table and link deletion);
  • The DML language allows manipulating table data, its rows. It also serves for selecting data from tables, adding new data, as well as updating and deleting current data.

It is possible to use two types of comments in SQL (single-line and delimited):

and

That’s all as to the theory.

DDL – Data Definition Language

Let’s consider a sample table with data about employees represented in a manner familiar to a person who is not a programmer.

Employee ID Full Name Birthdate E-mail Position Department
1000 John 19.02.1955 [email protected] CEO Administration
1001 Daniel 03.12.1983 [email protected] programmer IT
1002 Mike 07.06.1976 [email protected] Accountant Accounts dept
1003 Jordan 17.04.1982 [email protected] Senior programmer IT

In this case, the columns have the following titles: Employee ID, Full Name, Birthdate, E-mail, Position, and Department.

We can describe each column of this table by its data type:

  • Employee ID – integer
  • Full Name – string
  • Birthdate – date
  • E-mail – string
  • Position – string
  • Department – string

A column type is a property that specifies what data type each column can store.

To start with, you need to remember the main data types used in MS SQL:

Definition Designation in MS SQL Description
Variable-length string varchar(N)
and
nvarchar(N)
Using the N number, we can specify the maximum possible string length for a particular column. For example, if we want to say that the value of the Full Name column can contain 30 symbols (at the most), then it is necessary to specify the type of nvarchar(30).

The difference between varchar from nvarchar is that varchar allows storing strings in the ASCII format, while nvarchar stores strings in the Unicode format, where each symbol takes 2 bytes.
I recommend using the varchar type only if you are sure that you will not need to store the Unicode symbols in the given field. For example, you can use varchar to store email contacts.

Fixed-length string char(N)
and
nchar(N)
This type differs from the variable-length string in the following: if the string length is less than N symbols, then spaces are always added to the N length on the right. Thus, in a database, it takes exactly N symbols, where one symbol takes 1 byte for char and 2 bytes for nchar. In my practice, this type is not used much. Still, if anyone uses it, then usually this type has the char(1) format, i.e when a field is defined by 1 symbol.
Integer int This type allows us to use only integer (both positive and negative) in a column. Note: a number range for this type is as follows: from 2 147 483 648 to 2 147 483 647. Usually, it is the main type used to вуашту identifiers.
Floating-point number float Numbers with a decimal point.
Date date It is used to store only a date (date, month, and year) in a column. For example, 02/15/2014. This type can be used for the following columns: receipt date, birthdate, etc., when you need to specify only a date or when time is not important to us and we can drop it.
Time time You can use this type if it is necessary to store time: hours, minutes, seconds, and milliseconds. For example, you have 17:38:31.3231603 or you need to add the flight departure time.
Date and time datetime This type allows users to store both date and time. For example, you have the event on 02/15/2014 17:38:31.323.
Indicator bit You can use this type to store values such as ‘Yes’/’No’, where ‘Yes’ is 1, and ‘No’ is 0.

In addition, it is not necessary to specify the field value, unless it is forbidden. In this case, you can use NULL.

To execute examples, we will create a test database named ‘Test’.

To create a simple database without any additional properties, run the following command:

To delete a database, execute this command:

To switch to our database, use the command:

Alternatively, you can select the Test database from the drop-down menu in the SSMS menu area.

Now, we can create a table in our database using descriptions, spaces, and Cyrillic symbols:

In this case, we need to wrap names in square brackets […].

Still, it is better to specify all object names in Latin and not to use spaces in the names. In this case, every word starts with a capital letter. For example, for the “EmployeeID” field, we could specify the PersonnelNumber name. You can also use numbers in the name, for example, PhoneNumber1.

Note: In some DBMSs, it is more convenient to use the following name format «PHONE_NUMBER». For example, you can see this format in ORACLE databases. In addition, the field name should not coincide with the keywords used in DBMS.

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

For example, you can name the table with employees as “Employees” and set the following names for its fields:

  • ID
  • Name
  • Birthday
  • Email
  • Position
  • Department

Very often, we use ‘ID’ for the identifier field.

Now, let’s create a table:

To set the mandatory columns, you can use the NOT NULL option.

For the current table, you can redefine the fields using the following commands:

Note: The general concept of the SQL language for most DBMSs is the same (from my own experience). The difference among DDLs in different DBMSs is mainly in the data types (they can differ not only by their names but also by their specific implementation). In addition, the specific SQL implementation (commands) are the same, but there may be slight differences in the dialect. Knowing SQL basics, you can easily switch from one DBMS to another. In this case, you will only need to understand the specifics of implementing commands in a new DBMS.

Compare the same commands in the ORACLE DBMS:

ORACLE differs in implementing the varchar2 type. Its format depends on the DB settings and you can save a text, for example, in UTF-8. In addition, you can specify the field length both in bytes and symbols. To do this, you need to use the BYTE and CHAR values followed by the length field. For example:

The value (BYTE or CHAR) to be used by default when you merely indicate varchar2(30) in ORACLE will depend on the DB settings. Often, you can be easily confused. Thus, I recommend explicitly specifying CHAR when you use the varchar2 type (for example, with UTF-8) in ORACLE (since it is more convenient to read the string length in symbols).

However, in this case, if there is any data in the table, then to successfully execute commands, it is necessary to fill in the ID and Name fields in all the table rows.

I will show it in a particular example.

Let’s insert data in the ID, Position, and Department fields using the following script:

In this case, the INSERT command also returns an error. This happens because we have not specified the value for the mandatory field Name.

If there were some data in the original table, then the “ALTER TABLE Employees ALTER COLUMN ID int NOT NULL” command would work, while the “ALTER TABLE Employees ALTER COLUMN Name int NOT NULL” command would return an error that the Name field has NULL values.

Let’s add values in the Name field:

In addition, you can use NOT NULL when creating a new table with the CREATE TABLE statement.

At first, let’s delete a table:

Now, we are going to create a table with the ID and Name mandatory fields:

Also, you can specify NULL after a column name implying that NULL values are allowed. This is not obligatory, as this option is set by default.

If you need to make the current column non-mandatory, use the following syntax:

Alternatively, you can use this command:

In addition, with this command, we can either modify the field type to another compatible one or change its length. For example, let’s extend the Name field to 50 symbols:

Primary key

When creating a table, you need to specify a column or a set of columns unique for each row. Using this unique value, you can identify a record. This value is called the primary key. The ID column (that contains «an employee’s personal number»  – in our case this is the unique value for each employee and cannot be duplicated) can be the primary key for our Employees table.

You can use the following command to create a primary key for the table:

‘PK_Employees’ is a constraint name defining the primary key. Usually, the name of a primary key consists of the ‘PK_’ prefix and the table name.

If the primary key contains several fields, then you need to list these fields in brackets separated by a comma:

Keep in mind that in MS SQL, all fields of the primary key should be NOT NULL.

Besides, you can define a primary key when creating a table. Let’s delete the table:

Then, create a table using the following syntax:

Add data to the table:

Actually, you do not need to specify the constraint name. In this case, a system name will be assigned. For example, «PK__Employee__3214EC278DA42077»:

or

Personally, I would recommend explicitly specifying the constraint name for permanent tables, as it is easier to work with or delete an explicitly defined and clear value in the future. For example:

Still, it is more comfortable to apply this short syntax, without constraint names when creating temporary database tables (the name of a temporary table begins with # or ##.

Summary:

We have already analyzed the following commands:

  • CREATE TABLEtable_name (listing of fields and their types, as well as constraints) – serves for creating a new table in the current database;
  • DROP TABLEtable_name – serves for deleting a table from the current database;
  • ALTER TABLEtable_name ALTER COLUMN column_name … – serves for updating the column type or for modifying its settings (for example, when you need to set NULL or NOT NULL);
  • ALTER TABLEtable_name ADD CONSTRAINT constraint_name PRIMARY KEY(field1, field2,…) – used to add a primary key to the current table;
  • ALTER TABLEtable_name DROP CONSTRAINT constraint_name – used to delete a constraint from the table.

 Temporary tables

Abstract 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 before the instance of SQL Server is disconnected. They are automatically deleted after the user is disconnected from the instance of SQL Server. Global temporary tables are visible to all users during any connection sessions after creating these tables. These tables are deleted once users are disconnected from the instance of SQL Server.

Temporary tables are created in the tempdb system database, which means that we do not flood the main database. Additionally, you can delete them using the DROP TABLE command. Very often, local (#) temporary tables are used.

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

You can delete the temporary table with the DROP TABLE command:

In addition, you can create a temporary table and fill it in with the data using the SELECT … INTO syntax:

Note: In different DBMSs, the implementation of temporary databases can vary. For example, in the ORACLE and Firebird DBMSs, the structure of temporary tables should be defined in advance by the CREATE GLOBAL TEMPORARY TABLE command. Also, you need to specify the way of storing data. After this, a user sees it among common tables and works with it as with a conventional table.

Database normalization: splitting into subtables (reference tables) and defining table relationships

Our current Employees table has a drawback: a user can type any text in the Position and Department fields, which may returns mistakes, as for one employee he can specify “IT” as a department, while for another employee, he can specify “IT department”. As a result, it will be unclear what the user meant, whether these employees work for the same department or whether there is a misspell and there are 2 different departments. Moreover, in this case, we will not be able to correctly group the data for a report, where we need to show the number of employees for each department.

Another drawback is storage volume and its duplication, i.e. you need to specify a full name of the department for each employee, which requires space in databases to store each symbol of the department name.

The third disadvantage is the complexity of updating field data when you need to modify a name of any position – from programmer to junior programmer. In this case, you will need to add new data in every table row where the Position is “Programmer”.

To avoid such situations, it is recommended to use database normalization – splitting into subtables – reference tables.

Let’s create 2 reference tables “Positions” and “Departments”:

Note that here we have used a new property IDENTITY. It means that data in the ID column will be automatically listed starting with 1. Thus, when adding new records, the values 1, 2, 3, etc. will be assigned sequentially. Usually, these fields are called autoincrement fields. Only one field with the IDENTITY property can be defined as a primary key in a table. Usually, but not always, such field is the primary key of the table.

Note: In different DBMSs, implementation of fields with an incrementer may differ. In MySQL, for example, such a field is defined by the AUTO_INCREMENT property. In ORACLE and Firebird, you could emulate this functionality by sequences (SEQUENCE). But as far as I know, the GENERATED AS IDENTITY property has been added in ORACLE.

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

You need to do the same steps for the Departments table:

Now, if we open the Positions and Departments tables, then we will see a numbered list of values in the ID field:

ID Name
1 Accountant
2 CEO
3 Programmer
4 Senior Programmer

ID Name
1 Administration
2 Accounts dept
3 IT

These tables will be the reference tables to define positions and departments. Now, we will refer to identifiers of positions and departments. At first, let’s create new fields in the Employees table to store the identifiers:

The type of reference fields should be the same as in the reference tables, in this case, it is int.

In addition, you can add several fields using one command by listing the fields separated by commas:

Now, we will add reference constraints (FOREIGN KEY) to these fields, so that a user cannot add any values that are not the ID values of the reference tables.

The same steps should be done for the second field:

Now, users can only insert in these fields the ID values from the corresponding reference table. Thus, to use a new department or position, a user must add a new record in the corresponding reference table. As positions and departments are stored in reference tables in one copy, then in order to change their name, you need to change it only in the reference table.

The name of a reference constraint is usually compound. It consists of prefix «FK» followed by a table name and a field name that refers to the reference table identifier.

The identifier (ID) is usually an internal value used only for links. It does not matter what value it has. Thus, do not try to get rid of gaps in the sequence of values that appear when you work with the table, for example, when you delete records from the reference table.

In some cases, it is possible to build a reference from several fields:

In this case, a primary key is represented by a set of several fields (field1, field2, …) in table “reference_table”.

Now, let’s update the PositionID and DepartmentID fields with the ID values from the reference tables.

To do this, we will use the UPDATE command:

Run the following query:

ID Name Birthday Email Position Department PositionID DepartmentID
1000 John NULL NULL CEO Administration 2 1
1001 Daniel NULL NULL Programmer IT 3 3
1002 Mike NULL NULL Accountant Accounts dept 1 2
1003 Jordan NULL NULL Senior programmer IT 4 3

As you can see, the PositionID and DepartmentID fields match positions and departments. Thus, you can delete the Position and Department fields in the Employees table by executing the following command:

Now, run this statement:

ID Name Birthday Email PositionID DepartmentID
1000 John NULL NULL 2 1
1001 Daniel NULL NULL 3 3
1002 Mike NULL NULL 1 2
1003 Jordan NULL NULL 4 3

Therefore, we do not have information overload. We can define the names of positions and departments by their identifiers using the values in the reference tables:

ID Name PositionName DepartmentName
1000 John CEO Administration
1001 Daniel Programmer IT
1002 Mike Accountant Accounts dept
1003 Jordan Senior programmer IT

In the object inspector, we can see all the objects created for this table. Here we can also manipulate these objects in different ways, for example, rename or delete the objects.

Object inspector

In addition, it should be noted that it is possible to create a recursive reference.

Let’s consider this particular example.

Let’s add the ManagerID field to the table with employees. This new field will define an employee to whom this employee is subordinated.

This field permits the NULL value as well.

Now, we will create a FOREIGN KEY for the Employees table:

Then create a diagram and check how our tables are linked:

New database diagram

As you can see, the Employees table is linked with the Positions and Departments tables and is a recursive reference.

Employees table

Finally, I would like to note that reference keys can include additional properties such as ON DELETE CASCADE and ON UPDATE CASCADE. They define the behavior when deleting or updating a record that is referenced from the reference table. If these properties are not specified, then we cannot change the ID of the record in the reference table referenced from the other table. Also, we cannot delete this record from the reference table until we remove all the rows that refer to this record or update the references to another value in these rows.

For example, let’s re-create the table and specify the ON DELETE CASCADE property for FK_Employees_DepartmentID:

Let’s delete the department with identifier ‘3’ from the Departments table:

Let’s view the data in table Employees:

ID Name Birthday Email PositionID DepartmentID ManagerID
1000 John 1955-02-19 NULL 2 1 NULL
1002 Mike 1976-06-07 NULL 1 2 1000

As you can see, data of Department ‘3’ has been deleted from the Employees table as well.

The ON UPDATE CASCADE property has similar behavior, but it works when updating the ID value in the reference table. For example, if we change the position ID in the Positions reference table, then DepartmentID in the Employees table will receive a new value, which we have specified in the reference table. But in this case this cannot be demonstrated, because the ID column in the Departments table has the IDENTITY property, which will not allow us to execute the following query (change the department identifier from 3 to 30):

The main point is to understand the essence of these 2 options ON DELETE CASCADE and ON UPDATE CASCADE. I apply these options very rarely, and I recommend that you think carefully before you specify them in the reference constraint, because If an entry is accidentally deleted from the reference table, this can lead to big problems and create a chain reaction.

Let’s restore department ‘3’:

We completely clear the Employees table using the TRUNCATE TABLE command:

Again, we will add data using the INSERT command:

Summary:

We have described the following DDL commands:
• Adding the IDENTITY property to a field allows to make this field automatically populated (count field) for the table;
• ALTER TABLE table_name ADD field_list with_features – allows you to add new fields to the table;
• ALTER TABLE table_name DROP COLUMN field_list – allows you to delete fields from the table;
• ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (fields) REFERENCES reference_table – allows you to determine the relationship between a table and a reference table.

Other constraints – UNIQUE, DEFAULT, CHECK

Using the UNIQUE constraint, you can say that the values for each row in a given field or in a set of fields must be unique. In the case of the Employees table, we can apply this restriction to the Email field. Let’s first fill the Email values, if they are not yet defined:

Now, you can impose the UNIQUE constraint on this field:

Thus, a user will not be able to enter the same email for several employees.

The UNIQUE constraint has the following structure: the «UQ» prefix followed by the table name and a field name (after the underscore), to which the restriction applies.

When you need to add the UNIQUE constraint for the set of fields, we will list them separated by commas:

By adding a DEFAULT constraint to a field, we can specify a default value that will be inserted if, when inserting a new record, this field is not listed in the list of fields in the INSERT command. You can set this restriction when creating a table.

Let’s add the HireDate field to the Employees table and set the current date as a default value:

If the HireDate column already exists, then we can use the following syntax:

To specify the default value, execute the following command:

As there was no such column before, then when adding it, the current date will be inserted into each entry of the HireDate field.

When creating a new record, the current date will be also automatically added, unless we explicitly specify it, i.e. specify in the list of columns. Let’s demonstrate this with an example, where we will not specify the HireDate field in the list of the values added:

To check the result, run the command:

ID Name Birthday Email PositionID DepartmentID ManagerID HireDate
1000 John 1955-02-19 [email protected] 2 1 NULL 2015-04-08
1001 Daniel 1983-12-03 [email protected] 3 4 1003 2015-04-08
1002 Mike 1976-06-07 [email protected] 1 2 1000 2015-04-08
1003 Jordan 1982-04-17 [email protected] 4 3 1000 2015-04-08
1004 Ostin NULL [email protected] NULL NULL NULL 2015-04-08

The CHECK constraint is used when it is necessary to check the values being inserted in the fields. For example, let’s impose this constraint on the identification number field, which is an employee ID (ID). Let’s limit the identification numbers to be in the range from 1000 to 1999:

The constraint name is usually as follows: the «CK_» prefix first followed by the table name and a field name, for which constraint is imposed.

Let’s add an invalid record to check if the constraint is working properly (we will get the corresponding error):

Now, let’s change the value being inserted to 1500 and make sure that the record is inserted:

We can also create UNIQUE and CHECK constraints without specifying a name:

Still, this is a bad practice and it is desirable to explicitly specify the constraint name so that users can see what each object defines:

employees db

A good name gives us more information about the constraint. And, accordingly, all these restrictions can be specified when creating a table, if it does not exist yet.

Let’s delete the table:

Let’s re-create the table with all the specified constraints using the CREATE TABLE command:

Finally, let’s insert our employees in the table:

Some words about the indexes created with the PRIMARY KEY and UNIQUE constraints

When creating the PRIMARY KEY and UNIQUE constraints, the indexes with the same names (PK_Employees and UQ_Employees_Email) are automatically created. By default, the index for the primary key is defined as CLUSTERED, and for other indexes, it is set as NONCLUSTERED.

It should be noted that the clustered index is not used in all DBMSs. A table can have only one clustered (CLUSTERED) index. It means that the records of the table will be ordered by this index. In addition, we can say that this index has direct access to all the data in the table. This is the main index of the table.  A clustered index can help with the optimization of queries. If we want to set the clustered index for another index, then when creating the primary key, we should specify the NONCLUSTERED property:

Let’s specify the PK_Employees constraint index as nonclustered, while the UQ_Employees_Email constraint index – as clustered.  At first, delete these constraints:

Now, create them with the CLUSTERED and NONCLUSTERED indexes:

Once it is done, you can see that records have been sorted by the UQ_Employees_Email clustered index:

ID Name Birthday Email PositionID DepartmentID HireDate
1003 Jordan 1982-04-17 [email protected] 4 3 2015-04-08
1000 John 1955-02-19 [email protected] 2 1 2015-04-08
1001 Daniel 1983-12-03 [email protected] 3 3 2015-04-08
1002 Mike 1976-06-07 [email protected] 1 2 2015-04-08

For reference tables, it is better when a clustered index is built on the primary key, as in queries we often refer to the identifier of the reference table to obtain a name (Position, Department). The clustered index has direct access to the rows of the table, and hence it follows that we can get the value of any column without additional overhead.

It is recommended that the clustered index should be applied to the fields that you use for selection very often.

Sometimes in tables, a key is created by the stubbed field. In this case, it is a good idea to specify the CLUSTERED index for an appropriate index and specify the NONCLUSTERED index when creating the stubbed field.

Summary:

We have analyzed all the constraint types that are created with the «ALTER TABLE table_name ADD CONSTRAINT constraint_name …» command:

  • PRIMARY KEY;
  • FOREIGN KEYcontrols links and data referential integrity;
  • UNIQUE– serves for setting a unique value;
  • CHECK– allows monitoring the correctness of added data;
  • DEFAULT– allows specifying a default value;
  • The «ALTER TABLE table_name DROP CONSTRAINT constraint_name» command allows deleting all the constraints.

Additionally, we have reviewed the indexes: CLUSTERED and UNCLUSTERED.

Creating unique indexes

I am going to analyze indexes created not for the PRIMARY KEY or UNIQUE constraints.

It is possible to set indexes by a field or a set of fields using the following command:

Also, you can add the CLUSTERED, NONCLUSTERED, and UNIQUE properties as well as specify the order: ASC (by default) or DESC.

When creating the nonclustered index, the NONCLUSTERED property can be dropped as it is set by default.

To delete the index, use the command:

You can create simple indexes and constraints with the CREATE TABLE command.

At first, delete the table:

Then, create the table with all the constraints and indexes using the CREATE TABLE command:

Finally, add information about our employees:

Keep in mind that it is possible to add values with the INCLUDE command in the nonclustered index. Thus, in this case, the INCLUDE index is a clustered index where the necessary values are linked to the index, rather than to the table. These indexes can improve the SELECT query performance if there are all the required fields in the index. However, it may lead to increasing the index size, as field values are duplicated in the index.

Abstract from MSDN. Here is how the syntax of the command to create indexes looks:

Summary

Indexes can simultaneously improve the SELECT query performance and lead to poor speed for modifying table data. This happens, as you need to rebuild all the indexes for a particular table after each system modification.

The strategy on creating indexes may depend on many factors such as frequency of data modifications in the table.

Conclusion

As you can see, the DDL language is not as difficult as it may seem. I have provided almost all the main constructions. I wish you good luck with studying the SQL language.

 

 

Sergey Menshov

Sergey has been working in IT sector since 2002. Sergey's primary activity is data management. During his carreer, Andey has dealt with such DBMSes, as MS SQL Server, ORACLE, Firebird, MySQL. He also had a vast experience in developing in Delphi, PHP, VBA, C#, Java. Sergey Menshov is also a part-time lecturer in MS SQL Server, Analysis Services, and Integration Services.

Latest posts by Sergey Menshov (see all)