Introduction to Microsoft SQL Server

The estimated reading time for this post is 4 minutes

This post is an overview of Microsoft SQL Server. After reading this, you will know about basic architecture of SQL Server, SQL Server Management Studio and how to execute a query against SQL Server database.

Basic Architecture of SQL Server

SQL Server is a relational database management system to store and manage data. Relational databases use objects that called table to store information. Each table just holds information about one thing such as customers table or orders table, therefore each database contains multiple tables that are connected together to extract meaningful information.

You can have about 32,762 user defined databases within each server, in addition SQL Server includes five system databases:

  • Master: system level information of SQL Server that includes logins, Linked Servers, end points and other system configuration settings.
  • Model: template database whenever a new user database is created.
  • Msdb: used by SQL Server Agent, Service Broker, Database Mail, and other services. Msdb also holds backup history.
  • Tempdb: workhorse of SQL Server databases. It’s used to store temporal and intermediate results of query processing.
  • Resource: a hidden, read-only system database where system objects are stored.

SQL Server Database Objects

T-SQL database includes several types of objects:

  • Tables: SQL Server stores all the data in tables that organized in a row-column format as the same as spreadsheet.
  • Views: to retrieve data from one or more tables.
  • Schemas: logical container for SQL Server objects. Security permissions can apply to schemas.

SQL Server is also a client server system that client software (SQL Server management studio and Visual Studio) is separated from the SQL Server database engine which means client never has direct access to other databases through the network by using Four-Part name, Instance.Database.Schema.Object.

T-SQL is a set-based language, which means T-SQL doesn’t extract data row by row. T-SQL scripts are written in scripts with .sql extension. Each script can be divided into batchs by GO keyword.

SQL Server Editions and Versions

SQL Server Versions

Version Release Year Code Name
2017(14.0) 2017 2017
2016(13.0) 2016 2016
2014(12.0) 2014 Hekaton
2012(11.0) 2012 Denali
2008 R2(10.5) 2010 Kilimanjaro
2008(10.0) 2008 Katmai
2005(9.0) 2005 Yukon
2000(8.0) 2000 Shiloh
7.0 OLAP Tools 1999 Palato mania
7.0 1998 Sphinx
6.5 1996 Hydra
6.0 1995 SQL95
4.2.1 1993 SQLNT
4.2 1992
1.1 1991
1.0 1989

SQL Server Editions

  • Enterprise: flagship edition including all features.
  • Standard: this includes database engine, reporting and analytics capabilities. It doesn’t include all the High Availability, Data Warehousing and security features.
  • Express: a free version of SQL Server and limited to resources.
  • Developer: build, and test applications in a non-production environment with full features.
 

SQL Server 2017 Enterprise

SQL Server 2017 Standard

SQL Server 2017 Express

SQL Server 2017 Developer

Max number of core

Unlimited

24 Cores 4 Cores

Unlimited

Memory: maximum buffer pool size per instance

Operating system max

128 GB 1410 MB

Operating system max

Memory: maximum columnstore segment cache per instance

Operating system max

32 GB 352 MB

Operating system max

Memory: maximum memory-optimized data per database

Operating system max

32 GB 352 MB

Operating system max

Maximum database size

524 PB

524 PB 10 GB

524PB

SQL Server Management studio

SQL Server Management Studio which is known as SSMS is an integrated application with many features to manage, develop and query databases. You can start SSMS in two ways:

  • Launch SSMS from windows start screen.
Windows Startup

Windows Startup

  • Type ssms.exe in command prompt window.
windows command prompt window

windows command prompt window

SSMS display a connection to a server (or instance), by default, although it’s possible to work on SSMS without connecting to a SQL Server instance. The useful windows in SSMS include Query Editor, Object Explorer, and Solution Explorer. To connect to an instance you  need to specify several items:

  • The instance name which must be in this format: hostname\instancename
  • Authentication type that you can connect by windows authentication which is based on your windows login information or SQL Server authentication that you need to specify your SQL Server login name and password. There are another type of authentication which are based on active directory.
  • The name of database. If you don’t specify a database, you will connect to the default database.
SQL Server Management Studio Login Window

SQL Server Management Studio Login Window

Working with Object Explorer

Object Explorer is a hierarchical, graphical tool to manage SQL Server instances and databases.

Object Explorer

Object Explorer

Script Files and Projects

T-SQL Scripts are text files with .sql extension. You can organize several script files into solutions and projects. A solution is a conceptual container for projects. Solutions have .ssmssln extension and are displayed at the top. Projects contain queries (T-SQL scripts), database connection, and other miscellaneous files. Projects can be filed within a solution. Projects have .ssmssqlproj extension.

Executing Queries

To execute queries in SSMS after writing a query or opening a saved script file, there are three ways to run the query:

  • Click Execute button on toolbar

  • Click the Query menu, and then click Execute.
Query Execution
image-2863

Query Execution

  • Press F5 key, the Alt+X or Ctrl+E keyboard shortcuts.

SQL Server enables to display results in three different ways:

  • Grid: a spreadsheet-like display (use Ctrl+D)
Grid Result

Grid Result

  • Text: a notepad-like display (use Ctrl+T)
Text Result

Text Result

  • File: save query results to a text file with .rpt extension (use Ctrl+Shift+F)

So far we learnt the basics of SQL Server and how to use SQL Server Management Studio to connect and execute some queries, the next step is to read Introduction to Transact-SQL blog post to understand the basics of T-SQL language to start your journey as SQL Server database developer or/and administrator.


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz