Home » SQL Server Management Studio (SSMS): The Definitive Guide (2022)
sql-server-management-studio-definitive-guide-2022

This is a complete guide to SQL Server Management Studio (2022).

In this new guide you’ll learn:

  • What is SSMS?

  • How To Download SSMS?

  • How To Install SSMS?

  • How To Running SSMS?

  • How To Entering SSMS?

  • What are SSMS Components?

  • SSMS Settings

  • SSMS Tutorial

So let’s go!

Indexes

Definition SSMS

ssms-definition-content

In the following, we are going to define SSMS from 8 different perspectives to have a better insight into this application tool.

1The First Definition

The SQL Server Management Studio program, which is abbreviated as SSMS, is the SQL Server 2019 management tool.

2The Second Definition

SSMS is one of the SQL Server management tools, regardless of your location, used for designing queries and managing databases and data warehouses via personal computer or Cloud.

3The Third Definition

In reality, SSMS is an integrated environment that provides tools for configuring, monitoring, and administering SQL Server instances and databases.

4The Fourth Definition

SSMS also provides a complete and comprehensive tool that combines a vast group of graphical tools with script editors to provide access to SQL Server for database administrators and developers at all levels.

5The Fifth Definition

This tool includes many script editors (like T-SQL, MDX, DMX, and XML) and graphical tools that work with SQL Server objects and features.

6The sixth Definition

This program is equal to Enterprise Manager and Query Analyzer in SQL Server 2000.

7The Seventh Definition

When you want to perform operations related to a database (implementing, developing, and managing) without coding and only by a few clicks of the mouse, you use this method.

For example, to create a database, you only have to right-click on the Database tool and select the “New Database” option.  In the opened window, you can write the database’s name and create a new database easily.

To create other objects, we nearly act like this.

Isn’t this wonderful?

8The Eighth Definition

From its 2005 version onward, this tool is known as the primary development tool for SQL Server, and the Query Editor T-SQL programming tool is also available in this SSMS tool.

Download SSMS

ssms-download-content

SSMS is a free tool that you can easily download from Microsoft’s website via the address below.

The latest version available to the public is the 18.8 version.

If you are using an older version, it is recommended to always download and use the latest version to benefit from more facilities.

#1Download from Microsoft site

Download the latest versions of SQL Server Management Studio (SSMS) from Microsoft site (Indirect Download):

sql-server-management-studio-ssms-download-link

Direct download link of the latest SSMS version:

sql-server-management-studio-ssms-download-direct-link

#2Download from SQL Server Installation Center window

  • Open SQL Server Installation Center window.

  • Select the “Install SQL Server Management Tools” option 

sql-server-installation-center-page-installing-management-tools

Figure 1: SQL Server Installation Center Page for Installing Management Tools

  • ِِDownload the SSMS Setup.exe on the page opened from the Microsoft website.

download-SSMS-management-tool

Figure 2: Download SSMS Management Tool

Install SSMS

ssms-install-content

 the SSMS management tool must be installed separately.

Therefore, you must download it separately and then install it on the SQL Server database engine to manage the database and its objects via Wizard or Coding.

Installing this tool is very simple.

3 Steps to Install SSMS:

  •  Double-click on the downloaded file and install it by following a few steps.

After receiving and executing the file, the following window will open.

In this step, you can change the SSMS installation path if necessary and then click on the Install option for installation.

The installation steps are easy and short and are illustrated in the below pictures, respectively.

sql-server-management-tools-installation-window

Figure 3. SQL Server Management Tools Installation Window

sql-server-management-tools-installation-progress-window

Figure 4. SQL Server Management Tools Installation Progress Window

sql-server-management-tools-installation-completed-window

Figure 5. SQL Server Management Tools Installation Completed Window

After executing the above steps, the SSMS management tool will be installed.

If you want to see how to install SQL Server 2019 in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

Running SSMS

ssms-running-content

It is clear that to make changes and configure SSMS, you first need to run it on your computer.

To run SSMS, in the Windows 10 environment, easily

  • type SSMS in the Start menu

  • and then click on the SQL Server Management Studio option.

If you are using an older version of Windows,

  • you only have to go to the Start menu,

  • select the All Programs option,

  • then select the Microsoft SQL Server option,

  • and finally, click on SQL Server Management Studio.

Entering SSMS

ssms-entering-content

After running the program, you need to connect to it.

In this part, we want to describe how to connect to a SQL Server instance in the SSMS environment.

#1Connect to SSMS when launching

After running SSMS using one of these two methods, the SQL Server instance connection window opens up as illustrated in the figure below, and you can connect to a SQL Server instance.

connect-to-a-sql-server-instance-window

Figure 6: “Connect to a SQL Server Instance” Window

If this window is not opened automatically, you can open it up manually from the Object Explorer > Connect > Database Engine path:

manual-opening-connect-to-server-window

Figure 7: Manual Opening of the “Connect to Server” Window

After running the above program, you need to select which SQL Server you want to connect to from the Server Type expanding list.

If you want to access OLTP databases, you must select the Database Engine option.

And if you want to use OLAP databases, select the Analysis Services option.

If you want to learn about SSAS, I suggest you read the following reference article.

Everything you need to learn about SSAS is available in this article:

The name of the service provider’s instance will be determined in the Server Name section.

If you want to connect to a service provider installed on this computer, use the default server and do not change it.

The authentication method (determine User Name and Password to the service provider) is determined in the Authentication section. By default, when SQL Server is installed, it only accepts connections that use Windows Authentication (meaning that your Windows account is used for entering).

Since you are the user who has installed SQL Server, you have full access to the SQL Server instance.

You can change the default options by clicking on Options.

For example, in this section,

you can determine stuff such as which database to connect to by default,
what protocol to use for connecting to a network service provider,
what is the maximum waiting time (based on seconds) for connecting to a service provider,
and maximum waiting time (based on seconds) for running commands.

Now, Based on the above factors, follow these steps to connect to SSMS:

  • Set the first option (Server Type) on the Database Engine option and

  • Select server name and authentication type (Windows or SQL Server authentication) in this window.

  • Based on the authentication option you have selected, enter the User Name and Password that you have specified in the install step

  • Finally, click on the Connect button.

You will connect to your desired SQL Server instance by entering the server address and user information.

Note: In times that you want to connect to your outside database server or Host, you need to enter the server’s IP.

If you have successfully connected to the SQL Server instance, you will see the Object Explorer panel recommending multiple methods of database management to you.

Object Explorer will provide a tree view of all the database objects of a server.

object-explorer-window

Figure 8: Object Explorer Window

To make sure that your connection to SQL Server is done properly, try to expand the database objects using the Object Explorer window (click on the + alongside them).

Do not forget that these objects will be different based on the server type that you chose for connection.

expanding-database-objects

Figure 9: Expanding Database Objects

To make sure that your connection to SQL Server is done properly, try to expand the database objects using the Object Explorer window (click on the + alongside them).

Do not forget that these objects will be different based on the server type that you chose for connection.

By doing this, you will enter the SSMS environment.

#2Connect To SSMS From Within Its Environment

You can also use this environment (after entering SSMS) and the Object Explorer panel to disconnect from your current connection or connect to another instance.

Follow these steps to connect to a SQL Server instance using SSMS:

  • Click on the Connect menu from the Object Explorer panel.
  • Select the first option (Database Engine).
  • “Connect to SQL Server Instance” window will appear once again.
  • Like before, enter the needed information and click on the Connect.

Another method for connecting to another database or instance is to select the Explore Connect Object option from the File menu and enter the connection’s info.

Note: As mentioned before, there are two methods for connecting to a SQL Server instance (SQL Authentication and Windows Authentication) and we have used Windows Authentication in the examples presented in this article.

Nevertheless, SQL Azure DB does not support the Windows Authentication method and you can only use the SQL Authentication there.

From its 2005 version onward, this tool is known as the primary development tool for SQL Server, and the Query Editor T-SQL programming tool is also available in this SSMS tool.

SSMS Components

ssms-components-content

Thus far, you must have run SSMS and connected to it through the “Connect to Server” window.

Like other Microsoft software environments, this tool has a set of components that we must get familiarized with them first.

In this section, we are going to get familiarized with some important SSMS components and configurations.

After connecting to SSMS, you will see a window like this:

ssms-environment

Figure 10: SSMS Environment

As you see in the above figure, the SSMS window is created from multiple parts that we will describe them in the next section:

1Object Explorer [F8]

In this window, you can see a tree view of the database objects including:

  • SQL Server Database Engine,
  • SQL Server Analysis Serveries,
  • SQL Server Reporting Services
  • and SQL Server Integration Services of a server.

Object Explorer includes all the information related to all the servers that you have connected to.

if you have installed your SQL Server by following the orders of this article, you will see this section similar to the figure below.

object-explorer-window

Figure 11: Object Explorer

2Query Window [Ctrl + N]

By clicking on the New Query option from the icons window (toolbar), a new environment opens up that can be used for writing T-SQL codes and is known as the Query Window.

This window includes two sections:

  • in the top section, you can enter T-SQL queries
  • and in the bottom section of the same window, you can see the results of the queries.

For example, by writing the below T-SQL command in the top section, all the records from the Databases’ table are retrieved and its output (retrieved rows) is shown in the bottom section:

SELECT * FROM SYS.DATABASES

3Properties [F4]

When you open the Query window, you can also see the “Properties” window in which you can see the basic features of queries.

For example in this window, after the execution of a query, you can see the number of returned records and the connection details (figure 8).

properties-window

Figure 12: Properties Window

4Template Browser [Ctrl + Alt + T]

In this window, you see a set of pre-built templates related to T-SQL (codes written in T-SQL).

You can perform many different operations on databases (like creating a database, working with indexes, and creating backups) by making slight changes in codes.

For example, by expanding the Backup option and selecting Backup Database, you can see the code needed for creating a backup from databases.

Here, only enough to make the needed changes in this code and use it based on your needs;

for example, insert the name of the database that you want to back up or change the backup directory.

template-browser-window

Figure 13: Template Browser Window

5Object Explorer Details [F7]

This window shows more details in comparison with the Object Explorer.

You can use this window for manipulating different objects simultaneously.

For example, in this window, you can select multiple databases and delete them at the same time or run a script on them.

You cannot select multiple databases at the same time in the Object Explorer window.

object-explorer-details-window

Figure 14: Object Explorer Details Window

SSMS Preliminary Settings

ssms-settings-content

As you know, each software has its own primary settings that the user can change based on need.

You must use the “Options” settings box to apply the most SQL Server settings.

To view this box, just click on “Options” from the “Tools” menu to appear the following figure:

options-settings-window

Figure 15: Options Settings Window

Now that you have access to the main settings section of SQL Server Management Studio, you can apply your intended changes in the program.

We will talk about some of the useful and practical settings.

Of course, remember that you can change many elements and options of SQL Server from the Options window. The following are just some of the needed settings to make your Coding process more pleasant based on your taste.

1Font and Color

One of the features that can be changed in any software environment is the font and color used in that environment.

Probably each user is comfortable with a specific font or color. Selecting a comfortable font or color is rather important, especially for programmers who spend a lot of time coding.

If the default fonts and colors are not pleasant for you, you can change them by opening the Options section and selecting your desired Fonts and Colors.

You can change the text display style in the Management Studio by changing the different options of this section (figure 10).

options-window-font-color-settings

Figure 16: Options Window (Font and Color Settings)

In the “Show Settings for” section, you can select that part of the “Management Studio” that you will change the texts displayed in it.

For example, Text Editor, as the section in which the codes are written, is the most used among the other options.

You can select your desired font from the Font section and change the text size from the Size section.

Notably, the default text size is very small and unreadable; you can increase it to your desired size in this section.

You can change the foreground and background colors from the Foreground and Background sections, respectively.

After applying the changes, if you want to revert everything to the default state, enough to click the “Use Default” option.

2Showing Line Numbers

By default, you cannot see each line’s number beside it, but you can easily activate this feature from the Options window.

For this, select the “Text Editor” option from the left part of the window, then open “All Languages,” and then click on “General.

In the end, you can activate this feature by ticking the “Line Numbers” option (figure 4.12).

Notably, viewing the line numbers is helpful in troubleshooting and tracking program errors.

showing-line-numbers

Figure 17: Showing Line Numbers

In the “Show Settings for” section, you can select that part of the “Management Studio” that you will change the texts displayed in it.

For example, Text Editor, as the section in which the codes are written, is the most used among the other options.

You can select your desired font from the Font section and change the text size from the Size section.

Notably, the default text size is very small and unreadable; you can increase it to your desired size in this section.

You can change the foreground and background colors from the Foreground and Background sections, respectively.

After applying the changes, if you want to revert everything to the default state, enough to click the “Use Default” option.

3IntelliSense Feature (Autocomplete)

IntelliSense is a feature that presents a Drop Down List to the users to complete their T-SQL command using similar sentences and helps the users find their intended command.

In simpler words, the system presents the commands matching what you have typed, and you can select your intended command from among the suggested commands.

In this situation, you no longer need to type the command entirely.

IntelliSense feature is only available in SQL Server 2008 services and the later versions and is always recommended to the users as a helpful tool.

This feature is active by default, but if for any reason you do not want to use it, you can easily deactivate it from the Options window.

For accessing the “IntelliSense” option, first, select the “Text Editor” option from the left window, then open the “Transact-SQL” option and select “IntelliSense.” Then you can deactivate this feature by removing the “Enable IntelliSense” tick.

activating-deactivating-intellisense-feature

Figure 7: Activating or Deactivating the IntelliSense Feature

4Showing Columns’ Header while Copying Query Results

As was said before, when you write and execute T-SQL codes in the “Query Window,” you will see the results (retrieved records) in the bottom part of this window.

Now a situation arises where you need to transfer the results of that query to software like Excel.

As you know, the query output will be in the form of a series of rows (records) and columns (field names):

SELECT * FROM SYS.DATABASES
query-output

Figure 7: Query Output

As you can see in the above example, this query output has five records and some fields (including NAME, DATABASE_ID, etc.).

Surely, in such a situation you would like to transfer the column names (fields) alongside the output records. Unfortunately, the capability of showing the columns’ names has not been provided in “Management Studio” by default, but you can simply activate it.

For this, open the Query Results option from the left-side list of the Options window, then select SQL Server, and finally, the Result to Grid option.

For showing the header name (fields names) in columns, activate the “Include column headers when copying or saving the results” option.

By doing this, when you copy query results, the column headers will also be copied.

showing-column-headers-while-copying-query-results

Figure 7: Showing Column Headers while Copying the Query Results

5Viewing and Editing All the Records of a Table

When you are going to view and edit all the records of a table, you will face a limitation.

By default, just the first 1000 records of a table are shown, and you can only edit the first 200.

However, by changing the settings, you will be able to view and edit all the table records.

For this, you only have to open the SQL Server Object Explorer option and then click on Commands.

Then, by changing the “Table and View Options” values to zero, you will be able to view and edit all the records of a table.

viewing-editing-all-records-table

Figure 7: Viewing and Editing all the Records of a Table

After performing these changes, you can right-click on a table and select “Edit All Rows” to view and edit all the records of that table.

6Options Needed for Minimizing Logs

SQL Server databases maximize the chance of tracing potential problems and documenting databases by recording different events and saving them as Logs.

Logs include all needed information about people who log in to the system, records added to the system, and all of their time and dates.

Although this information can be helpful in database owners, sometimes they can make the databases too large if there are space limitations in subscription host services.

If you are also using subscription-based host services and do not want Logs to make your database too large and lead to the suspension of the host service, you can simply change the Recovery Model option to Simple and minimize the volume of created logs.

For this, after connecting to the intended database, right-click on the databases and select Option, then change the Recovery Model option to Simple, and finally, press the Save option to implement desired changes.

By doing this, your database will create fewer logs and will not save all the events.

Be careful that some hosting companies do not allow this on their database servers; therefore, you need to change the database on your computer to Simple.

needed-options-minimizing-logs

Figure 7: Needed Options for Minimizing Logs

7Changing the SSMS Environment Layout

In this section, we want to talk about changing the SSMS environment layout (including how to move windows).

To move a window, click and hold on its title and then Drag it around and Drop it on your intended location.

To pin or unpin a window, select the Pushpin icon available in the window’s title bar.

pinning-unpinning-a-window

Figure 7: Pinning or Unpinning a Window

Every window has a Drop-Down Menu that allows you to manipulate that window in different ways.

manipulating-ssms-environment-window

Figure 7: Manipulating SSMS Environment Window

When two or multiple query windows are opened, they can be arranged horizontally or vertically, so that all of them could be visible at the same time.

For viewing the arranged windows, right-click on the title of the New Query window and select the arrangement option (horizontal or vertical).

Finally, to revert all of these options to their default state, enough to select the “Reset Window Layout” option from the Window menu:

resetting-made-changes-default-state

Figure 7: Resetting the Made Changes to the Default State

Working with Database Objects

ssms-tutorial-content

As you know, SQL Server 2019 is a relational database management system that can have multiple instances.

Each instance can have multiple relational databases, and each database may consist of various tables; each table can also have a rational relation with other tables.
Using SQL Server Management Studio, we can access the available databases and objects in a SQL Server instance.

Anything inside a database, such as tables, views, indexes, saved procedures, etc., are all database objects.

In this section, we are going to describe the methods of creating databases and tables as well as how to create a connection between tables.

Nevertheless, before that, you need to get familiarized with different types of SQL Server databases.

SQL Server has two types of databases:

  • Systemic Databases

  • Master: A fundamental systemic database that saves system-level information about users, different configuration settings, and even information about other SQL Server databases.
  • Model: This is a template database; therefore, it includes default settings that will be implemented in every created database.
  • MSBD: This database is used for timing operations and alerts using other tools such as SQL Server Management Studio, Service Broker, and Database Mail.
  • Tempdb: This is temporary exclusive memory for SQL Server that can maintain temporary objects such as tables, saved procedures, etc.
systemic-databases

Figure 7: Systemic Databases

  • User Databases

These types of databases are those either come, as instances, with SQL Server (like pubs, northwind, and AdventureWorks) or are created by users based on their needs. (We will create a number of this type of database, later on.)

1Create Database

The first job in implementing the database system of a project is database creation.

After creating the database, you can make the other needed objects (like tables) inside it.

In the following, we want to describe the steps of creating a database.

In this section, we will implement a university-related database (called University) besides its three tables.

Follow these steps to create the University database:

Step 1: Run SQL Server Management Studio

Before creating the database, run SQL Server Management Studio.

Step 2: Right-Click on the Database Folder

Step 3: Select the “New Database…”

After running SQL Server Management Studio, right-click on the Database folder from the Object Explorer window and select the “New Database…” option from the opened menu.

If the Object Explorer window is not visible, select the “Option Explorer” option from the View menu (figure 21).

creating-new-database

Figure 7: Creating a New Database

Step 4: Enter the name of your intended database 

Step 5: Click the OK 

The New Database page appears, set on the General tab by default.

Enter the name of your intended database in the Database name section and then click the OK button to create the database.

The list of the files related to this database is shown in the Database File section.

There are two files created by default:

  • An MDF file for available information in the database
  • and an LDF file (the Log file) for saving different events.

There needs to be at least one Log file in each database.

This file is a retrieval file and keeps the Log information required to retrieve a database after occurring an error.

Using the Add and Remove buttons at the bottom of this window, you can add or delete new files to the database.

The initial size of a file (while loading) is determined in the Initial Size section. By default, the data files size is 8 megabytes. If you think your information volume is higher, you can grow this initial size.

Of course, the size of the files will grow automatically by locating the information in them. You can determine the amount and manner of growth in the “Autogrowth” section. After performing the needed changes, click on OK to create the database.

creating-new-database2

Figure 7: Creating a New Database

As you can see in the picture below, a database named University is added to the bottom of the Databases list (in the Object Explorer window).

If you have not viewed the database’s name after creating it, right-click on the Databases option and select Refresh (figure 23).

university-database

Figure 7: University Database

Note:

Default values while creating a database are taken from the Model database in the System Database section.

You can right-click on the Model database and select the Properties option to change these features. This means that by changing the attributes of the Model database, the default values will also change for defining the database you created.

Sometimes you want to have more control over the database that you have created. For example, you can determine stuff like the maximum size that the database can grow into or even the address (folder) of the primary data files and Logs. The main arguments that you can use are shown in Table 4-1:

Argument Name Description
Logical Name This argument does not act as it seems. This is a name that you assign to a file, but it is only a logical name, which means, that SQL Server uses this name within itself to refer to that file. When you want to change (increase or decrease) the database or file size, you will use this name.
File Name This argument does what it seems. The database files (.ldf and .mdf) are saved in the physical path. A physical name of the disk in which the data and Logs (based on the section that you define) are saved. If you are working with physical database files, their name will be the same as the database file plus an .mdf file extension. If you are working with a Log, the name of the file will the name of the database plus Log and a.Ldf file extension. Remember that the FILENAME parameter is optional. This option is optional when you use the simple structure (creating a new database based on the Model database) that was described in the beginning. If you provide extra information, you must add the file name explicitly – make sure that you have determined the full path.
Size This argument does what its name implies (meaning it determines the initial size of the database that you want to create). The default size uses Megabytes (MB) unit. Meaning that if you define the size as Size=1, this phrase is equal to Size=1MB. However, you can change this to Kilobyte by typing KB in front of the numerical value instead of MB. In addition, you can use bigger units such as Gigabyte (GB) and Terabyte (TB). Remember that this value must be at least equal to the Model database and needs to be an integer; otherwise, you will receive an error. If you do not give a value to SIZE, the database will be created with a size equal to the Model database.
AutoGrowth/MaxSize Determines the growth amount and maximum size of the database files.

2Create Table

After creating a database, now it is time to create tables in that database so that you can store data in them.

In other words, a database is a method of categorizing tables.

For example, we create a database for the university and insert all the tables needed for this system in that database. Then, we create another database for an online sales system and insert all the tables required for this system in that database.

This makes the management of tables easier because the tables related to each project are inserted in their specific database, so avoid any kind of interference.

In this section, we are going to create

  • the Student,
  • Course,
  • and SC tables

in the University database.

Remember that all the tables related to this example are created in the same database (the University database).

Follow these steps to create the Student, Course, and SC tables inside the University database:

  • In the Object Explorer window, expand the Databases option (right-click on the + sign near this option) to view the list of created databases.
  • To see the object available in the University database (including tables), click on the + sign next to University.
  • To create a new table, right-click on the Tables option, select New, and finally click on Table (figure 24).
creating-new-table

Figure 7: Creating a New Table

  • First, you need to determine the table name.
    The table name is determined in the Properties window. If you cannot view the Properties window, select the Properties window option from the View menu (the F4 key).
    If you have not determined the table’s name, after defining the table and when saving this new table, the program will ask for a table name and you can determine the table name at this section.
  • Double-click on the Name option (in the Properties window) and type Student.
determining-table-name-properties-window

Figure 7: Determining the Table Name in the Properties Window

  • Click on the Column Name (in the middle window opened for creating a table) to define this table’s first field (column).
  • The first field is the student number that we will name as StudentID. Enter the StudentID phrase.
    Note: While naming columns, try to avoid blank spaces. Either keep column names without blank spaces or use the underline (_) character instead of such spaces. Using blank spaces is completely allowed, but if they contain any spaces, when coding in T-SQL, you must surround the names with brackets that is an arduous task.
  • The next step is determining the data type entered to this field.
    Click on the Data Type column. An expanding list of all the different data types is one of the first sections of SQL Server that is presented to you for creating tables.
    Because of this, you do not need to memorize all the different data types available in SQL Server. By having all the listed necessary values, choosing the most appropriate one is simple (after finishing this exercise, you can see all the different data types available in SQL Server).
  • Select the Int data type.
  • Each field has another important characteristic (“Allow Null” field). In some fields, this checkbox is ticked but in some fields, it is not ticked. If it is ticked, the field will be allowed to store the Null value inside itself.
    By default, each defined field can receive the Null value (meaning that no data is stored in it).
    To better understand this concept, consider the Amazon website for example. To register on this website, a form is presented to you with some mandatory fields (e.g. name and last name) and some optional fields (e.g. phone number). Here, the database designer has removed the Null tick for first name and surname fields but left it there for the phone number field. Since in this table (Students Table) there must be a student number value then the Allow Nulls characteristic should be equal to No (Click on the Allow Nulls check box to remove the tick from the square in front of it).
    Note: We will completely discuss the matters related to the constraints that can be implemented on the fields of a table (including Nullable and Un-nullable Primary and Foreign Keys, Index, etc.) in Chapter 5 (T-SQL).
  • We are going to define the StudentID field of the Student table as a Primary Key. We select the StudentID field and click on it, then select the “Set Primary Key” option. A yellow key symbol appears on the left side of this field. Therefore, the StudentID field becomes the Primary Key of the Student table.
  • We enter the rest of the fields as follows.
creating-student-table

Figure 7: Creating the Student Table

  • Click on the Save button.
  • Then create the Course and SC tables following the same steps. The field specifications of these two tables are as follows:
creating-course-table

Figure 7: Creating the Course Table

creating-sc-table

Figure 7: Creating the SC Table

As you can see, we use the Student (for saving student information), Course (for saving course information), and SC (for creating a connection between the Student and Course tables) tables in this simple project.

Finally, when these aforementioned tables are created, their names must appear in the Tables (in the Object Explorer window) section (figure 4-29):

list-available-tables-university-database

Figure 7: List of Available Tables in the University Database

Table 4-2 is not a complete list of SQL Server data types but you should focus on the data types that you might use in your own projects.

Note: Table 4-2 describes the different data types available in SQL Server 2019. Of course, these data types are available in the older versions of SQL Server as well.

To summarize Table 4-2, we only wrote the most commonly used data types under the “Data Type” header while some similar data types are explained under the “Description and Notes” header. Remember this list, but you need to have an idea about the available data types.

Table 4-2: Different SQL Server data types

Data Type Size Description and Notes
INT 4 Saves all numbers from -2’147’483’648 to 2’147’483’647. You will use them for ID fields and other situations that need integers. Related types include SmallInt (can save numbers from -32768 to 32767 or null) and TinyInt (can save numbers from Zero to 255 or null). A Bit data type can store 0 and 1 values (for saving Yes/No or True/False)
MONEY 8 It saves monetary data from – 263 to 1- 263 with a 4 decimal accuracy. This type of data will be used for saving product prices, total purchases, etc. SQL Server also supports Float data that hold floating-point data; however, since this type of data is not accurate enough, they are not recommended for saving monetary data. Smallmoney is one of Money’s variables that have a smaller range but similar accuracy.
DATETIME 8 Supports date and time from June 1st, 1753 to December 31st, 9999 with a 33.3 milliseconds accuracy. The Smalldatetime type has the limitation of January 1st, 1900 until June 6th, 2079 with a one-minute accuracy. This data type will be used for saving data like order dates.
UNIQUEIDENTIFIRE 16 Saves a universally unique numeric identifier. It is a guaranteed unique GUID; this characteristic makes this datatype very useful in specific situations. In this book, we prefer to create unique IDs and references using other methods, however, remember that more options exist too.
VARCHAR, NVARCHAR Variable Stores variable-length character data. The number of savable characters in varchar columns is variable, for example, varchar (20) can save a maximum of 20 characters. The maximum limit of this datatype is 8000 characters and if we use the varchar (max) format, 2 to the 31st Power characters can be stored. Nvarchar saves Unicode data with a maximum length of 4000 characters and varchar saves non-Unicode data with a maximum length of 8000 characters. This data type should be used for saving short strings with variable lengths.
TEXT, NTEXT Variable Saves big character data. Ntext is a Unicode version and can save a maximum of 1’073’741’823 characters. Using this type of data can slow down databases and usually, it is suggested to use char, varchar, Nchar, or nvarchar data types instead of them. While adding text or Ntext to the fields, their length is set to 16, which shows the indicator’s length and the place where the real text is stored (not the length of the text itself). The Text data type can be used for saving big characters like paragraphs, long product descriptions, etc. In this book, we do not use this data type.
BINARY Fixed/Variable Saves binary data with a maximum 8000-byte length.
IMAGE Variable Saves binary data with a 222 -1 byte maximum. Despite its name, this field can save every type of binary data, not just images. In many cases, it is much faster and easier to save fields in our computer hard drive and only use their names in the database for saving binary data. For the Ballonshop project, you will save the images related to the products in the hard disk and just use their names in the database to store binary data.
CHAR, NCHAR Fixed Save character data with a fixed length. They fill values less than the fixed value with blank spaces. Can store a limited number of non-Unicode characters in themselves. That is, a char (20) column always save 20 characters even if strings shorter than 20-character is assigned to it. NChar is the Unicode version and goes to a maximum 4,000 characters, while Char can store 8,000 characters. When the strings that must be saved has a fixed length, it is better and more efficient to use Char instead of Varchar.

Notes about Using Different Data Types:

  • It is better to use Nchar and Nvarchar for saving variable data.
  • It is better to use Char and Varchar for data with fixed values because this will be much faster.
  • Text and Ntext are used for data with more than 8000 characters.
  • Nchar, Nvarchar, and Ntext Unicode data types should be used just when special characters may present in the data.

4Creating Relationship between Tables

In the previous sections, we finished the tables creation process, but this is not the end of the road.

If you look closely at the exercise, you can see that just three tables are created; each task is to save specified data.

For example, the Student table for saving the student’s personal information and the Course table for saving course details.

However, in the real world (university environment), these two entities are connected.

For example, it needs to be specified what courses each student has chosen (here, for simplicity, we only consider the choice of courses by students).

This means that if you want to design a database for a specific environment, you need to understand and know the connection(s) between tables and implement them.

We will create a new relationship between the Student and Course tables in the following exercise.

If the multiplicity of the relationship between two tables is N: N (Many-to-Many), we will need three tables.

In this example, we build separate tables for each of the Student and Course entities (Student table and Course table), and to make a relationship between them, create a third table (SC).

Then, put the Primary Keys of the Student and Course tables as the Foreign Keys in the third table; therefore, we create a relationship between the two tables (the SC table with the two previously created tables).

In the upcoming exercise, we want to analyze the method of creating a relationship between tables.

Exercise: Creating Relationship between Tables

You can create multiple databases using the Database Diagram branch. The relationship between tables becomes clearer using these diagrams. Follow the following steps to create a relationship between the Student and Course tables:

  • Expand the University database (click the + sign beside it).
  • To create a new diagram, right-click on the Database Diagram option and select New Database Diagram.
creating-new-diagram

Figure 7: Creating a New Diagram

  • By doing so, a new window will be opened in which you must click on Yes.
creating-new-diagram2
  • Now, a list of tables existing in the database appears. Select the Student, Course, and SC tables and click on Add (figure 31).
selecting-tables-to-create-connections

Figure 7: Selecting the Tables to Create the Connections among Them

  • As you can see, the tables have been added to the diagram view (figure 32).
tables-in-diagram-view

Figure 7: Tables in the Diagram View

By right-clicking on the diagram and selecting the New Text Annotation option, you can write text-based descriptions on the diagram.

Using this diagram, the database structure and the relationship between the tables become very clear. In this way, you can create multiple diagrams and show the relationship among them.

By right-clicking on the diagram and selecting New Table, you can add another table to this diagram and by clicking on each table and selecting the “Remove from Diagram” option, you can delete it from the diagram.

  • To create a relationship between the tables, you must drag the Student table’s Primary Key (StudentID) using your mouse and drop it on the SC table.
    In this situation, a window similar to figure 33 opens up and asks you for determining the Primary Key (in this example, the StudentID field from the Student table) and the Foreign Key (StudentID field from the SC table).
    If the name of the Primary Key and the Foreign Key is the same in both the tables, they will be selected automatically.
    Remember that the Primary and Foreign Key fields can have different names but they must have the same data type.
    In this example, since the Primary Key is in the Student table, we select the Student table in the Primary Key Table and the SC table in the Foreign Key Table and click on OK (Figure 33).
creating-connection-between-student-and-sc-tables

Figure 7: Creating a connection between Student and SC tables

  • Next, in the “Database Designer” section of the window, in the message-box related to the Foreign Key in which we have created the relationship, you can set some options for the Foreign Key.
    We will discuss all of these options later on (Figure 4.34):
configuring-foreign-key-options

Figure 7: Configuring Foreign Key Options

  • Perform the operation on the Course and SC tables, same as what we have done on the Student and SC tables for creating relationship. With this difference that this time, drag the Course table’s Primary key (CourseID) towards the SC table (figures 4.36 and 4.37).
setting-delete-rule-option

Figure 7: Setting the Delete Rule Option

setting-update-rule-option

Figure 7: : Setting the Update Rule Option

  • First, you need to determine the table name.
    The table name is determined in the Properties window. If you cannot view the Properties window, select the Properties window option from the View menu (the F4 key).
    If you have not determined the table’s name, after defining the table and when saving this new table, the program will ask for a table name and you can determine the table name at this section.
  • Double-click on the Name option (in the Properties window) and type Student.
creating-relationship-between-course-and-sc-tables

Figure 7: : Creating a Relationship between the Course and SC Tables

setting-foreign-key-options

Figure 7: Setting the Foreign Key Options

Finally, after performing the above operation, the relationship among the Student, Course and SC tables will be created (figure 4.39).

final-diagram

Figure 7: Final Diagram

Finally, click on the Save button to save the diagram.

selecting-a-name-diagram

Figure 7: Selecting a Name for the Diagram

As you can see, in addition to the tables, the diagram that you just created has been added to the University database (in the Database Diagrams section).

Now you can double-click on the diagram, to view it, add things to it, or change it based on your taste (figure 4-40).

diagram-is-added-to-database-diagrams-section

Figure 7: The Diagram is Added to the Database Diagrams Section

5Database Backup

Sometimes data may be destroyed because of different reasons such as hardware (or software) errors, programmer’s mistake, being hacked, etc. (And sometimes you need to move your database from one computer to another.)

Therefore, there must be a way to retrieve the latest version of it.

The best method is to make periodic backups from the data available in your database so that you can recover those data if some unexpected problems happen.

There are multiple methods to backup in SQL Server, we discuss them in the following (Table 3):

Table 4-2: Different SQL Server data types

Backup Type Description
Complete Backing up the complete database
Differential Only makes a backup from the sections that have been changed since the last Complete backup (backing up the changes and data added to the database).
Transaction Log Backing up the active part of the LOG file and freeing up the inactive part of the LOG file.
File/Filegroup Backing up different files or filegroups separately.
File/Differential Combining the Differential and File/Filegroup backup types.

Note: As was said before, when you create a database, two files are created by default; one is for data with an MDF suffix and the other is for LOGs with an LDF suffix.

It is possible to create more than one file for each of these.

Using more than one file in the situation of having multiple hard disks (alongside a proper design) can highly increase your processing speed.

When the files in your database are more than one, you can use the File/Filegroup and File/Differential backup methods.

Besides all the backup methods that have already been explained, this can be done manually or automatically.

Further on, we are going to discuss both of these methods in the format of exercises.

First, we will discuss manual backups and then automatic ones.

Exercise: Manual Backup

Follow these steps to manually backup:

  • From the list of visible folders, click on the + sign beside the Databases folder, then right-click on the intended database (here is University) and just like figure 4.41, first select Task and then click on Back Up.

selecting-back-up-option-to-make-a-backup

Figure 7: Selecting the Back-Up Option to Make a Backup

  • When you click on Back Up, its specific window opens up (figure 4.42). On the left side of this window (Select a Page), you can see General and Options that are used for the overall configuration of the backup process. By selecting the General option like figure 4.42, you will be able to determine the database for the backup operation, backup type, a date for backup file expiration, file name, and file save path; all of which will be discussed in the following:

  • Databases: Determines the intended database for backup (here is University).
  • Backup Type: Determines the backup type. Since in this example, we want to create a backup from the entire database, we select the Full option.
  • Backup Component: We selected the intended component for backup. Here, we select the Database option.
  • Backup To: Here you can determine the backup file save path. By default, the backup file will be saved in the SQL Server installation path inside the Backup file as a single file (of course, you can change this path). You can create the backup file in multiple separate-but-dependent files (you need all the files for restoration). You can delete the default backup path (by clicking on the Remove button) and then add a new path by clicking on Add… and determine the backup file save path by selecting the path and writing the file name in the File Name section.
general-options

Figure 7: General Options

As was said before, Select a Page has the two General and Options sections; we talked about the General section before. Now it is time to discuss the Options section. You only have to select Backup Options from the Select a Page section of the Back-Up Database window to view and set the options related to it:

  • Name: Here you can select a name for the backup version. Of course, it has a default name that can be changed.
  • Backup set will expire: Here we can determine an expiration date for the backup. This part has two options: If you want this backup to be expired after a specified number of days, you only have to write the number of days in the box in front of After (the default number is 0 meaning that the files will not be expired). If you want to determine the expiration as a date, you should select the On option.
backup-options

Figure 7: Backup Options

You can create multiple databases using the Database Diagram branch. The relationship between tables becomes clearer using these diagrams. Follow the following steps to create a relationship between the Student and Course tables:

  • Expand the University database (click the + sign beside it).
  • To create a new diagram, right-click on the Database Diagram option and select New Database Diagram.
  • By doing so, a new window will be opened in which you must click on Yes.
  • Now, a list of tables existing in the database appears. Select the Student, Course, and SC tables and click on Add (figure 31).
selecting-tables-to-create-connections
  • As you can see, the tables have been added to the diagram view (figure 32).
tables-in-diagram-view

Figure 7: Tables in the Diagram View

By right-clicking on the diagram and selecting the New Text Annotation option, you can write text-based descriptions on the diagram.

Using this diagram, the database structure and the relationship between the tables become very clear. In this way, you can create multiple diagrams and show the relationship among them.

By right-clicking on the diagram and selecting New Table, you can add another table to this diagram and by clicking on each table and selecting the “Remove from Diagram” option, you can delete it from the diagram.

  • To create a relationship between the tables, you must drag the Student table’s Primary Key (StudentID) using your mouse and drop it on the SC table.
    In this situation, a window similar to figure 33 opens up and asks you for determining the Primary Key (in this example, the StudentID field from the Student table) and the Foreign Key (StudentID field from the SC table).
    If the name of the Primary Key and the Foreign Key is the same in both the tables, they will be selected automatically.
    Remember that the Primary and Foreign Key fields can have different names but they must have the same data type.
    In this example, since the Primary Key is in the Student table, we select the Student table in the Primary Key Table and the SC table in the Foreign Key Table and click on OK (Figure 33).
creating-connection-between-student-and-sc-tables

Figure 7: Creating a connection between Student and SC tables

  • Next, in the “Database Designer” section of the window, in the message-box related to the Foreign Key in which we have created the relationship, you can set some options for the Foreign Key.
    We will discuss all of these options later on (Figure 4.34):
configuring-foreign-key-options

Figure 7: Configuring Foreign Key Options

  • Perform the operation on the Course and SC tables, same as what we have done on the Student and SC tables for creating relationship. With this difference that this time, drag the Course table’s Primary key (CourseID) towards the SC table (figures 4.36 and 4.37).
setting-delete-rule-option

Figure 7: Setting the Delete Rule Option

setting-update-rule-option

Figure 7: : Setting the Update Rule Option

  • First, you need to determine the table name.
    The table name is determined in the Properties window. If you cannot view the Properties window, select the Properties window option from the View menu (the F4 key).
    If you have not determined the table’s name, after defining the table and when saving this new table, the program will ask for a table name and you can determine the table name at this section.
  • Double-click on the Name option (in the Properties window) and type Student.
creating-relationship-between-course-and-sc-tables

Figure 7: : Creating a Relationship between the Course and SC Tables

setting-foreign-key-options

Figure 7: Setting the Foreign Key Options

Finally, after performing the above operation, the relationship among the Student, Course and SC tables will be created (figure 4.39).

final-diagram

Figure 7: Final Diagram

Finally, click on the Save button to save the diagram.

selecting-a-name-diagram

Figure 7: Selecting a Name for the Diagram

As you can see, in addition to the tables, the diagram that you just created has been added to the University database (in the Database Diagrams section).

Now you can double-click on the diagram, to view it, add things to it, or change it based on your taste (figure 4-40).

diagram-is-added-to-database-diagrams-section

Figure 7: The Diagram is Added to the Database Diagrams Section

6Restoring Database

In the last section, we review the database backup steps.

Now if we want to select the backed-up version (on that computer or another computer), we have to restore it.

Now, we are going to teach the method of restoring a backed-up version:

Exercise: Restoring Databases

  • Right-click on the Databases folder and select Restore Database… option from the opened menu.

selecting-restore-database-option

Figure 7: Selecting the “Restore Database…” Option

  • In the opened window, just like the below figure, select the Device option from the Source section and then click on the “…” button to select your backup file.

restoring-backup-file

Figure 7: Restoring the Backup File

  • Now a window opens up that you can use to add your backup file. To select a backup file click on the Add button.

adding-backup-file

Figure 7: Adding the Backup File

  • Now a window opens that you can use to determine the backup’s specific location. Select the backup file and click on OK.

selecting-backup-file

Figure 7: Selecting the Backup File

  • In the following window, you will see the complete address. In this step just click on OK.

backup-file-path

Figure 7: Backup File Path

  • Once more, click on OK.

last-step-in-restoring-backup-file

Figure 7: Last Step in Restoring the Backup File

  • If the operation is successful, you will see the following message.

successful-restoration-operation

Figure 7: Successful Restoration Operation

  • The restored database will be added to the list of available databases in SQL Server (the Databases section).

restored-database-added-to-database-list

Figure 7: The Restored Database Added to the Database List

This article first appeared in Nill2Bill

Written by: afrashteh

Get Exclusive SSAS Tips

That I Only Share With

Email Subscribers

2 Comments

  1. 就爱要 June 9, 2022 at 11:53 am - Reply

    Where there is a will, there is a way. This article is the ultimate guide I have seen about SSMS. Thanks a lot.

Leave A Comment