Useful Tips

Transact-SQL - creating a database

Pin
Send
Share
Send
Send


Microsoft Visual Studio 2008 includes the Microsoft SQL Server 2005 Express Edition database server. It differs from a full-featured data server only by limiting the database size to 2 gigabytes, which allows the development and testing of database applications.

To work on creating a database and tables, we will use Microsoft SQL Server Management Studio Express. This software product is freely distributed and available for download on the Internet.

1.4.1. Database structure definition

The appearance of the Microsoft SQL Server Management Studio Express program window is shown in Fig. 14.

Fig. 14. Window appearance of Microsoft SQL Server Management Studio Express

To create a database, right-click on the item "Databases" and select the menu item "Create database". The database creation window is shown in Fig. 15.

Fig. 15. Database creation window

This window sets the database name, names and paths to the database files, the initial file size and the step to increase the database size if necessary. After clicking the “OK” button, the created database appears in the list of databases.

Fig. 16. View of Management Studio with the created database

The created database is empty, i.e. does not contain any tables. Therefore, the next task is to create tables whose structure is similar to tables from the Access database. When creating tables, you need to pay attention to the relationship between Access and SQL Server types, presented in Table 6.

Table 6. Correspondence between Microsoft Access and Microsoft SQL data types

Data typeMicrosoft access

Data typeMicrosoft SQL

Data Type DescriptionMicrosoftSQL

Text

nvarchar

Data type for storing text up to 4000 characters

Field MEMO

ntext

Unicode character data type up to 1,073,741,823 characters

Numerical

Numerical values ​​(integer) in the range from -2,147,483,648 to +2,147,483,647

Date Time

smalldatetime

Date and time from January 1, 1900 to June 6, 2079 with an accuracy of one minute

Monetary

money

A monetary data type whose values ​​are in the range

from -922 337 203 685 477.5808 to +922 337 203 685 477.5807, with an accuracy of one ten thousandth

Counter

See point 3

Logical

A variable capable of accepting only two values ​​- 0 or 1

OLE Object Field

image

Variable for storing an array of bytes from 0 to 2 147 483 647 bytes

Hyperlink

ntext

See point 2

Lookup wizard

nvarchar

See point 1

To create tables, select the "Create table" item in the context menu of the "Tables" branch. Management Studio takes the following form.

Fig. 17. Management Studio in table creation mode

To determine the relationships between tables, you must specify the primary keys of the tables. To do this, select the "Set primary key" item in the context menu of the corresponding field.

Fig. 18. Setting the primary key

To create relationships between tables and database schemas, you need to create a new database diagram by selecting the corresponding item in the context menu of the "Database Diagrams" branch. Adding the necessary tables to the diagram in the window that appears, we obtain the following view of Management Studio.

Fig. 19. Beginning of building a database diagram

Creating links occurs by combining the fields to be linked. The result is the appearance of a relationship creation window.

Fig. 20. Creating a relationship between database tables

Of particular note is the “INSERT and UPDATE Specification” item, which defines the rules for updating and deleting related data in tables.

After creating the remaining tables and their relationships, the data scheme will look as follows.

Unlike the Microsoft Access data schema, here the lines showing the default relationships are not graphically snapped to the primary and secondary fields. However, when you click on any link with the left button, information about the selected relation appears in the properties panel.

Having finished work with the data scheme, we save it. Note that in SQL Management Studio, unlike Access, several charts can be created for a single database.

This feature is useful for databases with a very large number of tables, since one common diagram would be too loaded.

Fig. 21. Database schema BDTur_firmSQL

Fig. 22. Several diagrams for one database

© 2019 Science Library

Copying information from a page is allowed only with a link to this site

Create a database snapshot

In addition to creating a new database, the CREATE DATABASE statement can be used to take a snapshot of an existing database (source database). A database snapshot is a copy of the original database that was consistent in terms of completed transactions at the time the snapshot was created. The following is the syntax for creating a database snapshot:

Thus, to create a database snapshot, you must insert the AS SNAPSHOT OF clause in the CREATE DATABASE statement. The example below illustrates creating a SampleDb database snapshot and saving it in the D: temp folder. (Before you run this example, you need to create this directory.)

A snapshot of an existing database is a read-only copy of the source database that reflects the state of the database at the time of copying. (In this way, you can create multiple snapshots of an existing database.) The snapshot file (in the example above is D: temp snapshot_DB.mdf) contains only the modified data from the source database. Therefore, in the code for creating a snapshot, it is necessary to indicate the logical name of each data file of the source database, as well as the corresponding physical names.

Since the snapshot contains only the changed data, only a small fraction of the disk space required for the corresponding source database is required for each snapshot.

Database snapshots can only be created on disks with the NTFS file system (New Technology File System). only this file system supports sparse file technology used to store snapshots.

Database snapshots are typically used as a mechanism to prevent data corruption.

Attaching and detaching databases

All database data can be detached and then reconnected to the same or another database server. This functionality is used when moving the database.

The sp_detach_db system procedure is used to disconnect the database from the database server.. (The detachable database must be in single-user mode.)

To attach the database, use the CREATE DATABASE statement with the FOR ATTACH clause. For the attached database, all required files must be available. If any data file has a path different from the original path, then the current path must be specified for this file.

What does it take to create a database in Microsoft SQL Server?

In this section, I will introduce some kind of steps for creating a database in Microsoft SQL Server, i.e. this is exactly what you need to know and what you must have in order to create a database:

  1. You must have Microsoft SQL Server installed. The free edition of Microsoft SQL Server Express is ideal for training. If you have not installed SQL Server yet, then here is a detailed video instruction, where I show how to install Microsoft SQL Server 2017 in Express edition,
  2. You must have SQL Server Management Studio (SSMS) installed. SSMS is the main tool through which database development is implemented in Microsoft SQL Server. This environment is free, if you don’t have it, then in the above video instruction I also show the installation of this environment,
  3. Design a database. Before proceeding with the creation of the database, you must design it, i.e. define all the entities that you will store, determine the characteristics that they will possess, and also determine all the rules and restrictions that apply to the data during their addition, storage and change. In other words, you must determine the database structure, which tables it will contain, what relations will be between the tables, which columns each of the tables will contain. In our case, i.e. during training, this stage will be rather formal, since the beginner will not be able to correctly design the database. But a beginner should know that it’s impossible to proceed to creating a database without preliminary design, since it’s most likely that it’s impossible to implement a database without a clear idea of ​​how this database should look in the end.
  4. Create an empty database. There are two ways to create a database in SQL Server Management Studio: the first using the graphical interface, the second using the T-SQL language,
  5. Create tables in the database. By this stage, you will already have a database, but it will be empty, since there are no tables in it yet. At this point, you will need to create tables and the corresponding restrictions,
  6. Fill the database with data. There are already tables in the database, but they are empty, so now you can move on to adding data to the tables,
  7. Create other database objects. You already have a database, and tables, and data, so you can develop other database objects, such as: views, functions, procedures, triggers, with the help of which business rules and application logic are implemented.

Here is a general plan for creating a database that you should know before you begin your introduction to Microsoft SQL Server and the T-SQL language.

In this article, we will consider stage 4, this is creating an empty database, both ways of creating a database will be considered: using the graphical interface, and using the T-SQL language. The first three steps you should already have done, i.e. you already have installed SQL Server and Management Studio, and the approximate database structure that you want to implement, as I said, you can skip this point at the training stage, and in the following materials I will show how to create tables in Microsoft SQL Server albeit with a simple but more or less real structure.

Creating a database in SQL Server Management Studio

The first thing you need to do is start SQL Server Management Studio and connect to the SQL server.

Then in the object browser click on the container "Database" right click and select "Create a database".

As a result, a window will open "Database creation". Here you need to fill in only the field "Database Name", other parameters are configured as necessary. After you have entered the database name, click “OK”.

If there is no database with the same name on the server yet, then it will be created, it will immediately appear in the object browser.

As you can see, the database has been created, and there is nothing complicated about it.

Creating a T-SQL Database (CREATE DATABASE)

The process of creating a database in T-SQL is probably even simpler, since in order to create a database with default settings (as we did a little higher), you need to write only three words in the SQL query editor - the CREATE DATABASE statement and the name of the database.

First, open the SQL query editor, for this, click on the button Create Request on the toolbar.

Then we enter the following instruction, and run it to execute, the button "Run".

Where CREATE is a T-SQL command for creating objects on an SQL server, with the DATABASE command we indicate that we want to create a database, and TestDB is the name of the new database.

Of course, at this stage, many do not know either Microsoft SQL Server or the T-SQL language, many probably just create a database in order to begin to get acquainted with this DBMS and begin to learn the T-SQL language. Therefore, to make it easier for you to do this, I advise you to read the book The T-SQL Programmer Path - This is a tutorial on the Transact-SQL language for beginners, in which I talk in detail about both the basic constructions and advanced constructions of the T-SQL language, and I consistently move from simple to complex.

Using the CREATE DATABASE instruction, you can set absolutely all the parameters that were displayed in the SSMS graphical interface. For example, if we replaced the above statement with the following, then we would create a database in the DataBases directory on drive D.

Removing a database in Microsoft SQL Server

If necessary, you can delete the database. In reality, of course, this will rarely be required, but in the learning process, perhaps often. This can also be done using the graphical interface as well as using the T-SQL language.

In the case of the graphical interface, in the object browser, right-click on the database you need and select Delete.

Note!It is possible to delete a database only if there are no connections to it, i.e. nobody works in it, even your own connection context in SSMS must be configured to another database (for example, using the USE command). Therefore, before deleting, you must first ask everyone to end the sessions with the database, or in the case of test databases, force all connections to be closed.

In the window “Delete an object” click "OK". To force close existing database connections, you can check Close existing connections.

In the case of T-SQL, to delete the database, just write the following statement (in a DB also no one should work).

Where DROP DATABASE is the instruction to delete the database, TestDB is the name of the database. In other words, with the DROP command, objects on the SQL server are deleted.

Pin
Send
Share
Send
Send