Introduction to

Microsoft SQL Server

1
2  A 'database server' is a server which uses a database application that
provides database services to other computer programs or to computers, as defined by
the client–server model.
 Database management systems (DBMSs) frequently provide database-server
functionality, it is a "software system that enables users to define, create, maintain and
control access to the database".
 Some database management systems (such as Microsoft SQL Server ,MySQL) rely
exclusively on the client–server model for database access (while others e.g. SQLite are
meant for using as an embedded database).
 A client/server database system is one for which a dedicated process, the database server,
processes requests for database operations on behalf of multiple client applications.
 The client applications might all reside on the same physical system as the database
server, or might all reside on different systems, or be a mixture of local and remote clients
3
 An embedded database system is one that is linked directly to the application and executes
in the same address space as the application.
 In other words, it is embedded within the application such that end-users of the
application are unaware of the database system’s existence.
 An embedded database system is inherently zero- or low-administration because the ‘care-
and-feeding’ of the database management system (DBMS) is carried out by the
application itself.
 Relational database management system( RDBMS ) is a database management system
(DBMS) based on the relational model.
4 Microsoft SQL Server
 Microsoft SQL Server is a relational database management system developed
by Microsoft.
 As a database server, it is a software product with the primary function of storing and
retrieving data as requested by other software applications—which may run either on the
same computer or on another computer across a network (including the Internet).
 SQL Server is built over SQL , Structured Query Language (SQL), a programming language
which deals with relational Databases.
 SQL server is also tied to Transact-SQL, or T-SQL.
 Basically, T-SQL is a set of programming extensions from Microsoft which adds several
features to the Structured Query Language (SQL), including transaction control, exception
and error handling, row processing and declared variables.
5

 SQL Server consists of different components including a Database Engine and a

Management Studio.
 The Database Engine has no graphical interface - it is just a service running in the
background of your computer (preferable on the server).
 Database Engine A database engine (or storage engine) is the underlying software
component that a database management system (DBMS) uses to create, read,
update and delete (CRUD) data from a database. The Database Engine is the core
service for storing, processing, and securing data.
 The Management Studio is graphical tool for configuring managing and viewing
the information in the database. It can be installed on the server or on the client (or
both).
6
7
 A machine can have one or multiple SQL Servers installed. And, each of whose instance
may contain one or multiple databases. Then, there are schemas in the databases which
have the database objects such as tables, views, and stored procedures.
 To identify database objects (Database ,Table and Columns) they should be named.
 Do not use reserved words for naming database objects , variables
 SQL Reserved words ([Link]
reserved-words)
8 SQL Server Management Studio (SSMS)
 There are many ways to connect to SQL Server. For example, it is possible to connect via a web
application, an online admin tool, via a command line utility, or via SQL Server Management Studio.
 SQL Server Management Studio (SSMS)
• is an IDE(Integrated Development Environment ) that provides a graphical interface for connecting
and working with MS SQL server
• is the main administration console for SQL Server
• is an admin tool which allows a Data Base user to create and manage databases, create user
accounts, configure advanced security options, schedule regular backups, and more
Note
An integrated development environment (IDE) combines common developer tools into a single graphical
user interface (GUI).
9 Open SSMS
 open SSMS by finding it in your Windows apps list under Microsoft SQL Server 2016.
Screenshot of the Program Files in Windows.

Connect to SQL Server

 SSMS will prompt you to connect to SQL Server.
 Select the server type, server name, and the authentication details and click Connect.
 Screenshot of connecting to SQL Server.

10
 Object explorer
11  is an essential part of SSMS since it provides a quick and easy way to connect to
databases and manage their objects.
 Object Explorer shows the objects in a hierarchy thereby providing a quick object
management solution.
 It is a way database user primarily interact with database and server components when
using SSMS.
 As soon as SSMS starts the Object Explorer should open.
If not , it can be opened by one of the
following ways:
• Pressing the shortcut key F8
or
• From the menus select View >
Object Explorer as shown below
12
13 Create a Database
 In SQL Server, you can create a new database with its elements either via the SSMS
graphical user interface (GUI), or by running an SQL script.
Create a Database using SSMS GUI
• In Object Explorer, connect to an instance of the SQL Server Database Engine and then
expand that instance.
• Right-click Databases, and then click New Database.
• In New Database, enter a database name.
• To create the database by accepting all default values, click OK
14
15 Create a new database table using SSMS GUI
1. Create a New Table
• Ensure that the correct database is expanded in the Object Explorer
• Right-click on Tables
• and select New > Table...
from the contextual menu.
A new window will open
 2. Add Columns
16  Add the columns that need to go into the
table
 Also specify the data type for each column,
as well as any other properties needed

 Once Column Name and a Data Type entered the appropriate Column Properties for that data
type will be visible
 Common Column Properties
17
 Name: is the name of the column.
 Allow Nulls: Allow Nulls indicates whether or not the column will allow null values.
 Data Type: is the type of data stored for the column.
 Default Value or Binding : will allow the DB user to enter a default value in case a value is not
specified in an insert statement.
 Length :Length displays the number of characters for character-based data types.
 Collation: This property displays the collating sequence that SQL Server applies to the column
can be specified at the instance level, database level, and even down to the column level..
To change the collation using column properties, click the ellipsis and choose the collation
Note
• A collation is a configuration setting that determines how the database engine should treat
character data at the server, database, or column level.
• SQL Server includes a large set of collations for handling the language and regional differences
that come with supporting users and applications in different parts of the world.
  Computed Column Specification displays information about a computed column.
18 • A computed column is a logical column that is not physically stored in the table unless the column is
marked as Persisted
• Formula: This field is where formula's are added .
• Is Persisted: This field indicates whether the results of the formula are stored in the database or are
calculated each time the column is referenced
 Description: is a field that describes the column.
 Identity Specification displays whether or not the column is an Identity
• (Is Identity): Displays whether or not this column is an Identity.
• An Identity column is a unique column that can create a numeric sequence for you based on Identity
Seed and Identity Increment.
• Identity Increment: Identity Increment indicates the increment in which the numeric values will use.
The default value is 1.
• Identity Seed: Identity Seed is the value assigned to the first row. The default value is 1.
  Size displays how many bytes each record entered into the column will use.
19  Indexable: simply lets the developer know if an index can be applied to the particular column.
 Full-text Specification: will only be editable if the column has a full-text index defined.
Note:
 Some properties only appear for certain data types
 It is possible to change the name of the column , the data type and other properties in the table
design view or in the column properties.
 3 . Set a Primary Key
20
In the top pane, right-click in the field selected as a primary key
and select Primary Key from the contextual menu.
 [Link] the Table
21
 Save the table by clicking on the Save icon in the toolbar.

5. Refresh the Tables Node

 Right-click on the Tables node and select Refresh from the contextual menu.
 Create a foreign key relationship in Table Designer
22
 In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and
click Design.
 The table opens in Table Designer.
 From the Table Designer menu, click Relationships.
23 In the Foreign-key Relationships dialog box, click Add.
 The relationship appears in the Selected Relationship list with a system-provided name in the format
FK_<tablename>_<tablename>
 Click the relationship in the Selected Relationship list.
 Click Tables and Columns Specification
in the grid to the right and
click the ellipses (...) to the right of the property.
24
  In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that
25 will be on the primary-key side of the relationship.
 In the grid beneath, choose the columns contributing to the table's primary key. In the adjacent
grid cell to the right of each column, choose the corresponding foreign-key column of the
foreign-key table.
 Table Designer suggests a name for the relationship. To change this name, edit the contents of
the Relationship Name text box.
 Choose OK to create the relationship.
 Close the table designer window and save your changes for the foreign key relationship change
to take effect.
 CHECK constraint
26  The CHECK constraint is used to limit the value range that can be placed in a column.
 defining a CHECK constraint on a single column allows only certain values for this column.
To Set CHECK constraints using SSMS
• right-click on the column where to set the constraints
• select “Check Constraints…”
• click “Add” and
• click “…” in order to open the Expression window
• In the Expression window type in the expression
that encloses a condition to be checked
27
 DEFAULT constraint
28
 DEFAULT constraint is used to insert a default value into a column.
 The default value will be added to all new records, if no other value is specified.
To set DEFAULT value using SSMS
• Select the column and go into the “Column Properties”
• Set Default value
29  IDENTITY
 is used to create a primary key field automatically every time a new record is inserted
IDENTITY(1,1) means the first value will be 1 and then it will increment by 1
to set IDENTITY using SSMS
Click on the column
go into the Column Properties window
 Set identity increment
 And set the first value in identity seed
30 VIEW
 Like a table, a view consists of a set of named columns and rows of data.
 A view is a virtual table whose contents are defined by a query.
 The query that defines the view can be from one or more tables or from other views in the
current or other databases.
 Unless indexed, a view does not exist as a stored set of data values in a database..
 Create view Using SQL Server Management Studio
1. In Object Explorer, expand the database where you want to create your new view.
2. Right-click the Views folder, then click New View....
31 3. In the Add Table dialog box, select the element or elements that you want to include in
your new view from one of the following tabs: Tables, Views, Functions, and
Synonyms.
4. Click Add, then
click Close.
 5. In the Diagram Pane, select the columns or other elements to include in the new view.
32 6. In the Criteria Pane, select additional sort or filter criteria for the columns.

7. On the File menu, click Saveview name.

8. In the Choose Name dialog box, enter a name for the new view and click OK.
  Database diagrams
33
Database diagrams graphically show the structure of the database.
 To create a new database diagram
1. In Object Explorer, expand the database where you want to create your new Diagram
2. Right-click the Database Diagrams
3. Choose New Database Diagram on the shortcut menu.
4. The Add Table dialog box appears.
 5. Select the required tables in the Tables list and click Add.
34 6. The tables are displayed graphically in the new database diagram.
7. You can continue to add or delete tables, modify the existing tables, and alter table relationships
until the new database diagram is complete.
35 Write and Execute Basic SQL Statements in SQL Server

1. In Object Explorer ,Right Click on the database

Select New query
or
From the menu bar Click New Query
 2. In the new Query Window, enter the SQL statement to be executed
36 3. And Click Execute from The menu bar
“ Commands completed
successfully.” message will appear

Note : if the SQL statement has some errors

error messages will apper
accordingly