The concept of sql tables creation and use. Creating a database using SQL

Determine the location for the Databases folder. After connecting to the server (local or remote), the Object Explorer window will open on the left side of the screen. At the top of the Object Explorer tree will be the server you are connected to. If the tree is not expanded, click on the "+" icon next to it. Locate the Databases database folder.

Create new base data. Right-click on the Databases folder and select "New Database...". A window will appear that allows you to configure the database before creating it. Give the database a name that will help you identify it. Most users can leave the remaining settings at their default values.

• You will notice that when you enter the database name there are two additional file will be created automatically: Data and Log. The Data file contains all the data in your database, while the Log file tracks changes to the database.
• Click OK to create the database. You will see your new database appear in the expanded Databases folder. It will have a top hat icon.

Create a table. A database can only store data if you create a structure for that data. The table contains the information you enter into your database, and you will need to create it before you can continue. Expand the new database in the Databases folder, and right-click on the Tables folder and select "New Table...".

• Windows will open to the rest of the screen, allowing you to manage your new spreadsheet.

Create a Primary Key. It is highly recommended that you create a primary key as the first column in your table. It acts as an identification number, or record number, allowing you to easily retrieve those records later. To create it, enter "ID" in the Name field column, int type in the Data Type field and uncheck the "Allow Nulls" checkbox. Click the Key icon in the toolbar to set this column as the Primary Key.

• You don't want to allow null values, since you always want to have at least a "1" entry. If you allow 0, your first entry will be "0".
• In the Column Properties window, scroll down until you find the Identity Specification option. Expand it and set "(ls Identity)" to "Yes". This option will automatically increment the ID column value for each entry, automatically numbering each new entry.

Understand how tables are structured. Tables are made up of fields or columns. Each column represents one aspect of a database record. For example, if you are creating an employee database, you might have a "FirstName" column, a "LastName" column, an "Address" column, and a "PhoneNumber" column.

Create the remaining columns. When you finish filling out the fields for the Primary Key, you will notice that new fields appear below it. This will allow you to enter your next column. Fill out the fields as you see fit and make sure you select the correct data type for the information you will enter in this column:

• nchar(#) - This data type should be used for text, like names, addresses, etc. The number in parentheses is the maximum number of characters allowed for this field. Setting a limit will ensure that your database size remains manageable. Phone numbers must be saved in this format since you are not doing mathematical functions with them.
• ints are integers, and are typically used in an identifier field.
• decimal(x,y) - will store numbers in decimal form, and the numbers in brackets indicate, respectively, the total number of digits and the number of digits after the decimal. For example, decimal(6,2) will store numbers as 0000.00.

Save your table. When you are finished creating your columns, you will need to save the table before entering the information. Click the Save icon on the toolbar, and then enter a name for the table. It is recommended that you name your table in a way that helps you recognize the content, especially for large databases data with multiple tables.

Add data to your table. Once you have saved the table, you can start adding data to it. Open the Tables folder in Object Explorer. If your new table is not listed, right-click the Tables folder and select Refresh. Right-click on the table and select "Edit Top 200 Rows".

The table_name parameter is the name of the base table to be created. Maximum amount The number of tables a single database can contain is limited by the number of database objects, which cannot exceed 2 billion, including tables, views, stored procedures, triggers, and constraints. The parameters col_name1, col_name2, ... indicate the names of the table columns, and the parameters type1, type2, ... indicate the data types of the corresponding columns.

The name of a database object can usually consist of four parts, in the form:

]]object_name

Here object_name is the name of the database object, schema_name is the name of the schema to which the object belongs, and server_name and db_name are the names of the server and database to which the object belongs. Table names grouped with a schema name must be unambiguous within the database. Likewise, column names must be unambiguous within the table.

Let us now consider the constraint associated with the presence or absence of NULL values ​​in a column. If a column is not set to allow NULL values ​​(NOT NULL), then the column cannot contain NULL values ​​and the system will return an error message if you attempt to insert such a value.

As already mentioned, the database object (in in this case table) is always created in the database schema. A user can only create tables in a schema for which they have ALTER authority. Any user with the sysadmin, db_ddladmin, or db_owner role can create tables in any schema.

The creator of a table does not have to be its owner. This means that one user can create tables that are owned by other users. Similarly, a table created with a CREATE TABLE statement does not need to belong to the current database as long as the table name is prefixed with a different (existing) database and schema name.

The schema to which a table belongs can have two possible default names. If a table is specified without an explicit schema name, the system looks up the table name in the appropriate default schema. If the object name cannot be found in the default schema, the system searches in the dbo schema. Table names should always be specified along with the corresponding schema name. This will avoid possible uncertainties.

The example below shows how to create all the tables in the SampleDb database. (The SampleDb database must be set as the current database.)

USE SampleDb; CREATE TABLE Department (Number CHAR (4) NOT NULL, DepartmentName NCHAR (40) NOT NULL, Location NCHAR (40) NULL); CREATE TABLE. ( CHAR (4) NOT NULL, NCHAR (15) NOT NULL, FLOAT (53) NULL); CREATE TABLE dbo.Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL); CREATE TABLE dbo.Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL);

In addition to the data type and the presence of the NULL value, you can specify the following parameters in the column specification:

DEFAULT clause;

IDENTITY property.

DEFAULT clause in the column specification indicates the default value of the column, i.e. When a new row is inserted into a table, a cell in that column will contain the specified value, which will remain in the cell unless another value is entered into it. You can use a constant as the default value, such as one of system functions, such as USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP and NULL.

Identifier column created by specifying a property IDENTITY, can only have integer values, which are usually assigned implicitly by the system. Each subsequent value inserted into such a column is calculated by incrementing the last value inserted into this column. Therefore, the definition of a column with the IDENTITY property contains (either explicitly or implicitly) initial value and increment step (this column is also called an auto-increment column).

Below is an example of how these instructions are used:

USE SampleDb; CREATE TABLE UserInfo (-- For the Id column, auto-increment IDENTITY(10,5) will be used, -- i.e., when inserting data, the first element will be assigned -- the value 10, the second 15, the third 20, etc. Id INT NOT NULL PRIMARY KEY IDENTITY (10,5), Login VARCHAR(40) NOT NULL, -- The BirthDate field will be set to a default date -- (unless this field is explicitly specified when inserting data) BirthDate DATETIME DEFAULT (-- Default - 30 years from current date DATEADD(year, -30, GETDATE())))

CREATE TABLE Statement and Declarative Integrity Constraints

One of the most important features What the DBMS must provide is a way to ensure data integrity. Constraints that are used to validate data when it is modified or inserted are called constraints to ensure integrity (integrity CONSTRAINTs). Ensuring data integrity can be done by the user in the application program or by the database management system. Most important advantages The integrity constraints provided by the database management system are as follows:

data reliability increases;

programming time is reduced;

simplifies maintenance.

Defining integrity constraints through the DBMS improves data reliability by eliminating the possibility that the application programmer might forget to implement them. If integrity constraints are provided application programs, then all applications affected by these restrictions must contain appropriate code. If the code is missing from even one application, the integrity of the data will be questioned.

If integrity constraints are not provided by the database management system, they must be defined in each application program that uses the data included in the constraint. In contrast, if integrity constraints are provided by the database management system, then they only need to be defined once. In addition, the code for application-provided constraints is typically more complex than for the same constraints provided by the DBMS.

If the integrity constraints are provided by the DBMS, then if the constraints change, the corresponding changes in the code need to be implemented only once - in the database management system. And if the restrictions are provided by applications, then modification to reflect changes in restrictions must be done in each of those applications.

Database management systems provide two types of integrity constraints:

declarative constraints to ensure integrity;

procedural constraints to ensure integrity, implemented through triggers.

Declarative constraints are defined using DDL CREATE TABLE and ALTER TABLE statements. These restrictions can be column level or table level. Column-level constraints are defined along with the data type and other column properties in the column declaration, whereas table-level constraints are always defined at the end of a CREATE TABLE or ALTER TABLE statement after all columns have been defined.

There is only one difference between column-level constraints and table-level constraints: column-level constraints can only apply to one column, while table-level constraints can span more than one column in a table.

Each declarative constraint is given a name. This name can be assigned explicitly by using the option CONSTRAINT in a CREATE TABLE or ALTER TABLE statement. If the CONSTRAINT option is not specified, the constraint name is assigned implicitly by the Database Engine. It is highly recommended to use explicit constraint names as this can greatly improve the search for those constraints.

Declarative restrictions can be grouped into the following categories:

DEFAULT clause;

UNIQUE clause;

PRIMARY KEY clause;

CHECK clause;

referential integrity and the FOREIGN KEY clause.

The use of the DEFAULT clause to define a default constraint was shown earlier. All other restrictions are discussed in subsequent sections.

UNIQUE offer

Sometimes several columns or a group of columns in a table have unique values, allowing them to be used as a primary key. Columns or groups of columns that can be used as a primary key are called potential keys (candidate key). Each candidate key is defined using a clause UNIQUE in a CREATE TABLE or ALTER TABLE statement. The UNIQUE clause syntax is as follows:

The CONSTRAINT option in the UNIQUE clause assigns an explicit name to the candidate key. Option CLUSTERED or NONCLUSTERED This is due to the fact that the Database Engine creates an index on each potential table key. This index can be clustered, where the physical order of the rows is determined by the indexed order of the column values. If row order is not specified, the index is nonclustered. The default is NONCLUSTERED. The col_name1 parameter specifies the name of the column that creates the candidate key. (A candidate key can have up to 16 columns.)

The use of the UNIQUE clause is shown in the example below. (Before you run this example, you must drop the Projects table in the SampleDb database using the DROP TABLE Projects statement.)

USE SampleDb; CREATE TABLE Projects (Number CHAR(4) DEFAULT "p1", ProjectName NCHAR (15) NOT NULL, Budget FLOAT (53) NULL, CONSTRAINT unique_number UNIQUE (Number));

Each value in the Number column of the Projects table is unique, including NULL values. (In the same way as for any other value with UNIQUE constraint, if NULL values ​​are allowed for the corresponding column, that column can contain at most one row with a NULL value.) Attempting to insert a value into the Number column that is already in it will fail because the system will not accept it. The explicit name of the constraint defined in the example is unique_number.

PRIMARY KEY offer

Primary key of the table is a column or group of columns whose values ​​are different in each row. Each primary key is defined using the clause PRIMARY KEY in a CREATE TABLE or ALTER TABLE statement. The syntax of the PRIMARY KEY clause is as follows:

All parameters of the PRIMARY KEY clause have the same meanings as the corresponding parameters of the same name in the UNIQUE clause. But unlike the UNIQUE column, the PRIMARY KEY column does not allow NULL values ​​and has a default value of CLUSTERED.

The example below shows the primary key declaration for the Employee table in the SampleDb database. Before you run this example, you must drop the Employee table in the SampleDb database using the DROP TABLE Employee statement.

USE SampleDb; CREATE TABLE Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL, CONSTRAINT primary_id PRIMARY KEY (Id));

Running this code again creates the Employee table with a primary key defined. The primary key of a table is defined by a declarative integrity constraint named primary_id. This integrity constraint is a table-level constraint because it is specified after all columns in the Employee table are defined.

The following example is equivalent to the previous one, except that the primary key of the Employee table is defined as a column-level constraint.

USE SampleDb; DROP TABLE Employee; CREATE TABLE Employee (Id INT NOT NULL CONSTRAINT primary_id PRIMARY KEY, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL);

In the example, the PRIMARY KEY clause belongs to the declaration of the corresponding column, along with the declaration of its data type and the property to contain NULL values. For this reason, this limitation is called column level constraint.

CHECK offer

Check constraint (CHECK CONSTRAINT) defines the conditions for data inserted into the column. Every row that is inserted into a table, or every value that updates a column's value, must meet these conditions. Test limits are established by means of a proposal CHECK, defined in a CREATE TABLE or ALTER TABLE statement. The syntax of the CHECK clause is as follows:

expression must be a Boolean value (true or false) and can reference any columns in the current table (or just the current column if defined as a column-level constraint), but not other tables. The CHECK clause is not enforced when replicating data if the NOT FOR REPLICATION option is present. (With replication, a database, or part of it, is stored in multiple locations. Replication can improve data availability.)

The example below shows the use of the CHECK clause:

USE SampleDb; CREATE TABLE Customer (CustomerId INTEGER NOT NULL, CustomerRole VARCHAR(100) NULL, CHECK (CustomerRole IN ("admin", "moderator", "user")));

The Customer table that the example creates includes a CustomerRole column that contains the corresponding check constraint. When you insert a new value that differs from the values ​​in the set ("admin", "moderator", "user"), or when you try to change an existing value to a value that differs from these values, the database management system returns an error message.

FOREIGN KEY offer

Foreign key is a column (or group of table columns) containing values ​​that match the primary key values ​​in the same or another table. A foreign key is defined using a clause FOREIGN KEY in combination with offer REFERENCES. The syntax of the FOREIGN KEY clause is as follows:

[ ((col_name1) ,...)] REFERENCES table_name ((col_name2),...) Syntax conventions

The FOREIGN KEY clause explicitly defines all columns that are included in the foreign key. The REFERENCES clause specifies the name of the table that contains the columns that create the corresponding primary key. The number of columns and their data type in the FOREIGN KEY clause must match the number of corresponding columns and their data type in the REFERENCES clause (and, of course, they must match the number of columns and data types in the primary key of the table they refer to).

USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id) , CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number));

The Works_on table in this example is specified with three declarative constraints to ensure integrity: primary_works, foreign_employee, and foreign_project. These constraints are a table level constraint where the first specifies the primary key and the second and third specifies the foreign key of the Works_on table. In addition, foreign keys define the Employee and Projects tables as reference tables, and their Id and Number columns as the corresponding primary key of the column of the same name in the Works_on table.

The FOREIGN KEY clause can be omitted if the foreign key is defined as a table-level constraint because the column on which the constraint applies is an implicit "list" of columns foreign key, and the REFERENCES keyword is sufficient to indicate what type this constraint is. A table can contain a maximum of 63 FOREIGN KEY constraints.

Defining foreign keys on database tables imposes the definition of another important integrity constraint: referential integrity.

Referential integrity enforces rules for inserts and updates on tables that contain a foreign key and a corresponding primary key constraint. The example above has two such constraints: foreign_employe and foreign_project. The REFERENCES clause in the example defines the Employee and Projects tables as the reference (parent) tables.

If referential integrity is specified for two tables, modifying the values ​​in the primary key and the corresponding foreign key will not always be possible. The following sections discuss when this is possible and when it is not.

Modifying foreign or primary key values ​​can cause problems in four ways. All these cases will be demonstrated using the SampleDb database. The first two cases involve modifications to the referencing table, and the last two involve modifications to the parent table.

Potential Referential Integrity Issues - Case 1

Inserts a new row into the Works_on table with employee number 11111. The corresponding Transact-SQL statement looks like this:

USE SampleDb; INSERT INTO Works_on VALUES (11111, "p1", "qwe", GETDATE())

When inserting a new row into a child table, Works_on is used new number employee EmpId for which there is no matching employee (and number) in the parent Employee table. If referential integrity is defined for both tables, as was done previously, the Database Engine will not allow a new row to be inserted with that EmpId number.

Possible Referential Integrity Issues - Case 2

Changing employee number 9502 in all rows of the Works_on table to number 11111. The corresponding Transact-SQL statement looks like this:

In this case, the existing foreign key value in the referencing Works_on table is replaced with a new value that does not have a matching value in the parent Employee table. If referential integrity is defined for both tables, then the database management system will not allow modification of a row with this EmpId number in the Works_on table.

Potential Referential Integrity Issues - Case 3

Replacing the value 9502 of the employee number Id with the value 22222 in the Employee table. The corresponding Transact-SQL statement would look like this:

This case attempts to replace the existing employee Id value 9502 with the value 22222 in the parent Employee table only, without changing the corresponding Id values ​​in the referencing Works_on table. The system does not allow this operation. Referential integrity does not allow values ​​to exist in the referencing table (the table for which a foreign key is defined by the FOREIGN KEY clause) for which no corresponding value exists in the parent table (the table for which the primary key is defined by the PRIMARY KEY clause). Otherwise, such rows in the referencing table would be "orphans". If the modification to the Employee table described above were allowed, then the rows in the Works_on table with an Id value of 9502 would be orphans. Therefore, the system does not allow such modification.

Potential Referential Integrity Issues - Case 4

Deleting a row in the Employee table with an Id value of 9502.

This case is similar to case 3. If this operation were performed, the row with the value Id for which there are matching values ​​in the referencing (child) table Works_on would be deleted from the Employee table.

ON DELETE and ON UPDATE options

The Database Engine may react differently to an attempt to delete or modify a primary key. If you try to update the values ​​of a foreign key, all of those updates will be inconsistent with the corresponding primary key, and the database will refuse to make those updates and display an error message.

But if you try to make updates to primary key values ​​that cause an inconsistency in the corresponding foreign key, the database system can react quite flexibly. In general, there are four options that determine how the database system can respond:

Only those values ​​in the parent table that do not have corresponding values ​​in the foreign key of the child (referring) table are modified (updated or deleted).

Modification (updating or deleting) of any values ​​in the parent table is allowed. When you update a primary key value in a parent table or when you delete an entire row containing given value, in the child (referring) table, all rows with corresponding foreign key values ​​are updated (i.e. deleted).

Modification (updating or deleting) of any values ​​in the parent table is allowed. If updating a value in a parent table causes an inconsistency in a child table, the database system sets the foreign key of all corresponding rows in the child table to NULL. The same thing happens if a row in a parent table is deleted, causing an inconsistency in the child table. Thus, all data inconsistencies are ignored.

Similar to the SET NULL option, but with one exception: all foreign keys corresponding to the primary key being modified are assigned a default value. It goes without saying that after modification, the primary key of the parent table should still contain the default value.

Transact-SQL supports the first two of these options. The use of the ON DELETE and ON UPDATE options is shown in the example below:

USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id) ON DELETE CASCADE, CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number) ON UPDATE CASCADE);

This example creates the Works_on table using the ON DELETE CASCADE and ON UPDATE CASCADE options. If the Works_on table is loaded with values, each deletion of a row in the Employee table will cause a cascade delete of all rows in the Works_on table that have foreign key values ​​that match the primary key values ​​of the rows being deleted in the Employee table. Similarly, each update to the value of the Number column of the Project table will cause the same update to all corresponding values ​​of the ProjectNumber column of the Works_on table.

Before you can create an SQL table, you need to define the database model. Design an ER diagram in which to define entities, attributes and relationships.

Basic Concepts

Entities are objects or facts about which information must be stored. For example, an employee of a company or projects implemented by the company. Attributes are components that describe or qualify an entity. For example, the attribute of the “employee” entity is salary, and the attribute of the “project” entity is the estimated cost. Connections are associations between two elements. It can be bidirectional. There is also a recursive connection, that is, the connection of an entity with itself.

It is also necessary to determine the keys and conditions under which the integrity of the database will be maintained. What does it mean? In other words, restrictions that will help keep databases in a correct and consistent form.

Transition from ER diagram to tabular model

Rules for transition to a tabular model:

1. Convert all entities to tables.
2. Convert all attributes to columns, that is, each entity attribute must appear in the table column name.
3. Convert unique identifiers into primary keys.
4. Convert all relationships to foreign keys.
5. Create the SQL table.

Creating a database

First you need to start the MySQL server. To launch it, go to the Start menu, then Programs, then MySQL and MySQL Server, select MySQL-Command-Line-Client.

To create a database, use the Create Database command. This function has the following format:

CREATE DATABASE database_name.

The restrictions on the name of the database are as follows:

• length is up to 64 characters and can include letters, numbers, "" and "" symbols;
• the name can start with a number, but it must contain letters.

We need to remember and general rule: Any query or command ends with a delimiter. In SQL, it is common to use a semicolon as a delimiter.

The server needs to indicate which database it will need to work with. There is a USE statement for this. This operator has a simple syntax: USE n database_name.

Creating a SQL Table

So, the model is designed, the database is created, and the server is told exactly how to work with it. Now you can start creating SQL tables. There is Data Definition Language (DDL). It is used to create MS SQL table, as well as to define objects and work with their structure. DDL includes a set of commands.

SQL Server table creation

Using just one DDL command, you can create various objects base, varying its parameters. The Create Table command is used. The tt format looks like this:

CREATE TADLE table_name,(column_name1 Name _column2 datatype [column_constraint],[table_constraints]).

The syntax of this command should be described in more detail:

• The table name must be up to 30 characters long and begin with a letter. Only alphabetic characters, letters, and the symbols "_", "$" and "#" are allowed. The use of Cyrillic alphabet is allowed. It is important to note that table names should not be the same as other object names or database server reserved words such as Column, Table, Index, etc.
• You must specify a data type for each column. There is a standard set used by most. For example, Char, Varchar, Number, Date, Null type, etc.

• The Default parameter allows you to set a default value. This ensures that there are no null values ​​in the table. What does it mean? The default value can be a symbol, an expression, a function. It is important to remember that this default data type must match the column's input data type.
• Constraints on each column are used to enforce integrity conditions for data at the table level. There are other nuances. It is prohibited to delete a table if there are other tables dependent on it.

How to work with the database

Large projects often require the creation of multiple databases, each requiring many tables. Of course, it is impossible for users to retain all the information in their heads. To do this, it is possible to view the structure of databases and tables in them. There are several commands, namely:

• SHOW DATABASES - shows all created SQL databases on the screen;
• SHOW TABLES - displays a list of all tables for the current database that are selected by the USE command;
• DESCRIBE table_name- shows a description of all table columns.
• ALTER TABLE - allows you to change the table structure.

The last command allows:

• add a column or constraint to a table;
• change an existing column;
• delete column or columns;
• remove integrity constraints.

The syntax for this command is: ALTER TABLE table_name( | | | | [(ENABLE | DISABLE) CONSTANT constraint_name ] | }.

There are other commands:

• RENAME - rename the table.
• TRUNCATE TABLE - removes all rows from the table. This function may be needed when it is necessary to fill the table again, but there is no need to store previous data.

There are also situations when the structure of the database has changed and the table should be deleted. There is a DROP command for this. Of course, you first need to select the database from which you want to delete the table, if it is different from the current one.

The command syntax is quite simple: DROP TABLE Name_tables.

In SQL Access creation tables and their modification is carried out by the same commands listed above.

Using CREATE TABLE you can create an empty table and then fill it with data. But that is not all. You can also directly create a table from another table. Like this? That is, it is possible to define a table and fill it with data from another table. There is a special keyword AS for this.

The syntax is very simple:

• CREATE TABLE Name_tables[(column_definition)] AS subquery;
• column_definition - column names, integrity rules for columns again created table and default values;
• subquery - returns the rows that need to be added to the new table.

Thus, such a command creates a table with certain columns, inserts rows into it, which are returned in the query.

Temporary tables

Temporary tables are tables whose data is erased at the end of each session or earlier. They are used to record intermediate values ​​or results. They can be used as worksheets. You can define temporary ones in any session, but you can use their data only in the current session. Creating temporary SQL tables is similar to regular tables, using the CREATE TABLE command. In order to show the system that the table is temporary, you need to use the GLOBAL TEMPORARY parameter.

The ON COMMIT clause sets the lifetime of data in such a table and can do the following:

• DELETE ROWS - clear the temporary table (delete all session data) after each transaction completion. This is usually the default value.
• PRESERVE ROWS - leave data for use in the next transaction. In addition, you can clear the table only after the session ends. But there are some peculiarities. If a transaction is rolled back (ROLLBACK), the table will be returned to its state at the end of the previous transaction.

The syntax for creating a temporary table can be represented as follows: CREATE TABLE Name_tables,(Namecolumn_1 datatype [column_constraint], Name _column2 datatype [column_constraint], [table_constraints]).

Creating and deleting tables in ms sql server

All data in the sql server database is stored in tables.
Tables consist of columns that combine values ​​of the same type, and rows—records in the table. One database can have up to 2 billion tables, a table has 1024 columns, and one row (record) has 8060 bytes.

sql server supports following types data:

Data type Designation Size, bytes
Binary data
varbinary[(n)] 1-8000
Characters char[(n)]
varchar[(n)] 1-8000
(up to 8000 characters)
Unicode characters nchar[(n)]
nvarchar[(n)] 1-8000
(up to 4000 characters)
Date and time datetime 8
smalldatetime 4
Exact numbers decimal[(p[,s])]
numeric[(p[,s])] 5-17
Approximate numbers float[(n)] real 4-8 4
Global identifier uniqueidentifier 16
Integers int smallint, tinyint 4 2, 1
Money money, smallmoney 8, 4
Special bit, cursor,
sysname, timestamp 1, 0-8
Text and image text, image 0-2 GB
Text unicode ntext 0-2 GB

Tables can be created using the create table statement in the transact-sql language, as well as using the enterprise manager. Let's first look at how this is done using transact-sql.

Creating tables using create table
To create tables, use the create table statement.
Here's what the simplified syntax for this operator looks like:

create table table_name
(column_name data_type
[,…n])
For example:

create table member (member_no int not null, lastname char(50) not null, firstname char(50) not null, photo image null)

This statement creates a member table consisting of four columns:

member_no - has an int type, null values ​​​​are not allowed
lastname - type char(50) - 50 characters, null values ​​are not allowed
firstname - similar to lastname
photo - has type image (image), null value is allowed
Note
null is a special designation for indicating that a data element has no value. The column type description indicates that data elements may be uninitialized. When specifying not null, “empty” values ​​are not allowed. If you omit a value for such a column when inserting a record, the insertion will not occur and sql server will generate an error.

Try this command. Run query analyzer. Connect to your server. From the list of databases, select sqlstep. Copy the table creation command into the command window and run it. (If you haven’t forgotten, you need to press f5 or ctrl-e).

To make sure that the table has been created, type the command:

sp_help member
Select it (as in a regular editor) and press f5 again. The results window will display information about the member table.

On a note!

sp_help is a system procedure that returns information about database objects (tables, stored procedures, etc.).
The call format is:

sp_help<имя таблицы>

Deleting a table couldn't be easier. There, in the requester (that’s what we call query analyzer), type:

drop table member

Select this line and press f5. The table will be deleted and you will be notified about it. In our case, this procedure is simple. In fact, in a large database it will not be possible to simply delete a table, since it will be connected to other tables, and to delete it you will need to break these connections. How to do this, see the following steps.

How to create a table using sql server enterprise manager
Expand sequentially: sql server group,<Ваш sql server>, databases. Select DB (sqlstepbystep I think:), click right button mouse and select the "new" item in the context menu, and then the "table..." item. First of all, you will be asked for the table name. Enter it and press enter. A window will appear on the screen in which you can enter:
column names, type, length, dimension, precision (these three columns are locked depending on the type), null permission flag, default value. The last three columns are not of interest yet.

Enter the column names, their type and length as in the example above. Click on the floppy disk icon to save the table and you can close the window. Open your database, click on the "tables" category and in the list of tables you will see the table you just entered. To delete it, select it in the list, right-click and select "delete" in the context menu. The table will be deleted.

We studied how tables are created and deleted. Our next step is to create a full-fledged database, which we will use as an example:

what is relational database integrity and how is it ensured in sql server
how to modify data in tables (insert, update, delete, select statements)
how to use stored procedures and triggers

Good bad