SQL Server Tutorial – Learn Microsoft SQL 2012

SQL Server is a relational database management system that is capable of handling large amount of data apart from providing several methods to ensure data integrity, it also provides advance administrative utilities. It renders complete data integrity protection and advanced security to objects.

This tutorial explains the special features of the transact-SQL, statements, which provide flexible method of retrieving and modifying data in tables. The query language which enables to access data in SQL server is powerful. The purpose of this online reference is to enable the reader to comprehend the important concept of SQL Server.

What is SQL Server?

SQL Server is a client/server Relational Database Management System (RDBMS) that uses Transact-SQL to send request between a client and SQL Server

Client/Server Architecture

SQL Server is designed to be a client/server system. Client/server systems are connected so that the database can reside on a central computer, known ans a server, and be shared among several users. When users want to access the data in SQL Server, they run an application on their local computer, known as a client that connects over a network to the server running SQL Server.

SQL Server can work with thousands of client applications simultaneously. The server has features to prevent logical problems that occur if a user tries to read or modify data currently being used by others.

While SQL Server is designed to work as a server in a client/serve network, it is also capable of working as a stand-alone database directly on the client. The scalability and ease-of-use features of SQL Server allow it to work efficiently on the client without consuming too many resources. SQL Server effectively allocates the available resources, such as memory network bandwidth, and disk I/O, among the multiple users.

Relational database Management System

The RDBMS is responsible for:

  • Maintaining the relationships between data in the database.
  • Ensuring the data is stored correctly – that the rules defining the relationships between data are not violated.
  • Recovering all data to a point of known consistency in the event of system failure

Transact-SQL

SQL Server uses Transact-SQL as its database query and programming language. SQL is a set of commands that allows the user to specify the information to be retrieved or modified. With Transact-SQL, we can access data and query, update and manage relational database systems. Both American Standard National Institute (ANSI) and the International Standards Organization (ISO) have defined standards for SQL Server.

In this tutorial we’ll begin by choosing the right edition of SQL Server of many available options. We’ll see

  • How to install it properly
  • Explore the different tools and applications for working with SQL Server
  • Start designing and creating databases and tables
  • Learn how to use Transact-SQL – the language of SQL Server to retrieve, insert and manipulate data
  • Perform core database administration tasks like working with indexing to improve performance
  • Learn how to secure your data
  • Learn how to work with backup and restore

We’ll even discuss some of the most advanced features of this product, things like SQL Server Reporting Services and Integration Services

If you’ve been working with SQL Server already you may find that you’ve only scratch the surface of what it really can do. And if you are just beginning to use SQL Server this is the perfect place to start.

You have come to the right place to learn SQL Sercer. And if you want to learn the specifics of SQL for SQL Server 2012, you’ve made an especially good choice. Along the way, you’ll learn a lot about relational database management systems in general and about SQL Server in particular.

Why learn SQL? First, because most programmers would be better at database programming if they knew more about SQL. Second, because SQL programming is a valuable specialty in itself. And third, because knowing SQL is the first step toward becoming a database administrator. In short, knowing SQL makes you more valuable on the job.

This tutorial is the ideal source for application developers who need to work with a SQL Server database. It shows you how to code the SQL statements that you need for your applications. It shows you how to code these statements so they run efficiently. And it shows you how to take advantage of the most useful advanced features that SQL Server has to offer.

This tutorial is also a good choice for anyone who wants to learn standard SQL. Since SQL is a standard language for accessing database data, most of the SQL code in this website will work with any database management system. As a result, once you use this tutorial to learn how to use SQL to work with a SQL Server database, you can transfer most of what you have learned to another database management system such as Oracle, DB2, or MySQL.

How to install the required software for this tutorial

To run the SQL statements described in chapters 1 through 20 of this turial, you only need to have two software products installed: the SQL Server 2012 database engine and the SQL Server 2012 Management Studio. Both of these products are available from the Microsoft website for free, and you can download and install them both on your computer as described below.

To use a NET language such as C# or VB to work with BLOB as described in chapter 20, you can use an Express Edition of C# or VB. However, to use a NET language to develop stored procedures or other database objects as described in chapters 21 through 23 of this tutorial, you need to have a non-express edition of Visual Studio installed. If you don’t already have Visual Studio installed on your system, you can install it as described below.

The four editions of SQL Server 2012 Express

Table below describes the four editions of SQL Server 2012 Express. Of the four editions listed in this figure, we recommend that you install SQL Server 2012 Express with Tools. This edition includes the database engine and the Management Studio that are described throughout this tutorial. In addition, it includes the Loca1DB edition of SQL Server 2012 Express, which is needed to test database objects written using a NET language from within Visual Studio.

The fourth edition (SQL Server 2012 Express with Advanced Services) includes two additional features that aren’t covered in this tutorial. If you eventually want to learn about these features, you can install this edition and use it with this tutorial. However, it requires more system resources than SQL Server 2012 Express with Tools.

EditionDescription
LocalDB (MSI Installer)A lightweight version of Express that's designed for use by application developers and is installed without configuration
SQL Server 2012 ExpressThe core express database server. Contains only the database engine. Accepts remote connections and can be administered remotely.
SQL Server 2012 Express with ToolsContains LocalDB and SQL Server Management Studio Express in addition to the database engine. You can use the Management Studio to develop and run the SQL statements that are presented in chapters 1 through 20 of this tutorial.
SQL Server 2012 Express with Advanced ServicesContains Full Text Search and Reporting Services in addition to the database engine, Loca1DB, and Management Studio Express. Full Text Search and Reporting Services aren't covered in this tutorial.

For this tutorial, we recommend that you install SQL Server 2012 Express with Tools.

How to install SQL Server 2012 Express with Tools

Steps shown below guide you how to install SQL Server 2012 Express with Tools. This edition of SQL Server includes a version of the SQL Server 2012 database engine, it includes the main tool for working with databases, the SQL Server 2012 Management Studio, and it includes the LocalDB edition. Chapter “Using SQL Server Management Studio” presents the basic techniques for using the Management Studio. You can use it to develop and run all of the SQL statements that are presented in chapters 1 through 20 of this tutorial. Chapter 21 shows you how to use the LocalDB edition, and you can use it to test most of the database objects you create with Visual Studio in chapters 21 through 23.

If you already have an instance of SQL Server 2012 Express installed on your computer, you will only want to install the Management Studio tool and, if you’ll be developing database objects from Visual Studio, the LocalDB. To do that, you can download a separate install program for each of these features.

If you already have an instance of SQL Server 2005, 2008, or 2008 R2 Express installed on your computer with a name of SQLEXPRESS, you won’t be able to install the 2012 version with this name. In that case, you have three options. First, you may be able to upgrade the database server by selecting the “Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2″ option in step 3 of how to install SQL Server 2012 Express with Tools section. This will upgrade the old instance to 2012, but it will run the existing databases as if they are running on 2005 or 2008. If you want to update a database so it can use the features of SQL Server 2012, you can change its compatibility level. For information on how to do that, see chapter “Using SQL Server Management Studio“.

Second, if you can’t upgrade the old instance of SQL Server Express, you can uninstall it. Then, you can install a 2012 instance. That way, you can use the default name of SQLEXPRESS for the 2012 instance. To do that, you should start by backing up any databases that are running on the older SQL Server instance. One way to do that is to detach them from the server and copy the data (mdf) and log (ldf) files for the databases to a safe location. Then, you can uninstall all components of SQL Server Express, including the Management Studio components. Next, you can install SQL Server 2012 Express with Tools as described in this figure. Finally, you can attach the databases that you backed up to this server. For more information about detaching and attaching databases, see chapter “Using SQL Server Management Studio“.

Third, you may want to leave the 2005, 2008, or 2008 R2 instance of SQL Server on your computer and install the 2012 instance of the database engine with a new name such as SQLEXPRESS2012. To do that, you can specify a name for the new instance in step 5. However, the examples in this tutorial assume that the SQL Server 2012 database engine is installed with a name of SQLEXPRESS. As a result, if you choose this approach, you may have to modify some of the examples to get them to run successfully on your computer.

The main URL for the SQL Server 2012 Express downloads

http://www.microsoft.com/en-us/download/details.aspx?id=29062

 

How to download and install the prerequisites

  1. Go to the URL shown above.
  2. Follow the links to the download page for SQL Server 2012 Express with Tools.
  3. Follow the instructions provided on that web page to register, download, and install any required components.

How to download and install SQL Server 2012 Express with Tools

  1. Download the setup program for SQL Server 2012 Express with Tools. To do that, you can scroll down the page and click the Download button for the setup program. For 32-bit systems, you can download the file named SQLEXPRWT_x86_ENU.exe. For 64-bit systems, you can download the file named SQLEXPRWT_x64_ENU.exe.
  2. Launch the setup program.
  3. At the SQL Server Installation Center dialog, select the Installation category. Then, select the “New SQL Server standalone installation” option to start the SQL Server 2012 Setup wizard, and respond to the resulting dialog boxes. If there is an older instance of SQL Server installed with the name SQLEXPRESS, you can select the “Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2″ option.
  4. At the Feature Selection step, select all available features. Or, if there is already an instance of SQL Server 2012 installed on your system, you can deselect the “Database Engine Services” check box. This allows you to install the other fea-tures of SQL Server 2012 including the Management Studio without installing another instance of SQL Server, and it allows you to skip the rest of this procedure.
  5. At the Instance Configuration step, create a named instance with a name of SQLEXPRESS. If there is an older instance of SQL Server with this name, you can create a named instance with a name of SQLEXPRESS2012, or you can uninstall the old instance and install the 2012 instance with the default name of SQLEXPRESS.
  6. At the Server Configuration step, select an account name for the SQL Server Database Engine service. If you want to run SQL Server on your computer, you can select NT AUTHORITY\SYSTEM.
  7. At the Database Engine Configuration step, you can click the Add Current User button to add the current user as a SQL Server Administrator.
  8. Continue responding until the installation is complete.

Note – This tutorial assumes that the SQL Server 2012 Express database engine is installed with a name of SQLEXPRESS.

 

How to install Visual Studio and SQL Server Data Tools

For chapters 1-19, you don’t need Visual Studio. As a result, you can wait until you get to chapter 20 to install it.

For chapter 20, you can use an Express Edition of Visual Studio. For example, if you want to use C#, you can install Visual C# 2010 Express. Or, if you want to use Visual Basic, you can install Visual Basic 2010 Express. The Express Editions of Visual Studio are free and can be downloaded from Microsoft’s website. After you download the setup program for the language you want to use, you just run the setup program to install it. This program works similarly to the setup program for Visual Studio 2010, but fewer options are available.

For chapters 21 through 23, however, you must have a full edition (not an Express Edition) of Visual Studio 2010 installed on your computer. Unfortunately, these editions of Visual Studio are not cheap. For example, the Professional Edition is the least expensive edition, and its suggested retail price is usually more than $500. If you’re just curious to see how these features work, you can download and install a trial edition from the Microsoft web site for free. Or, if you intend to begin using Visual Studio regularly, you can purchase an edition. If you purchase a DVD that has the software, you can install it as described below.

For chapters 21 through 23, you must also install SQL Server Data Tools (SSDT) on your system only if you are using Visual Studio 2010. To do that, you can follow the instructions listed below.

How to install Visual Studio 2012 Professional

  1. Insert the installation DVD. The setup program will start automatically.
  2. Click the Install Visual Studio 2012 link and follow the instructions.
  3. On the Options page, select the option for performing a custom installation. Then, when the page with the list of features is displayed, deselect the option to install SQL Server 2008. (For this tutorial, you want to install SQL Server 2012)
  4. To install any updates that are available, click the Check for Service Releases link.
  5. Or alternatively you can download the Trial version of the Microsoft Visual Studio Professional 2012  here, http://www.microsoft.com/en-us/download/details.aspx?id=30682

The main URL for SQL Server Data Tools (SSDT)

http://msdn.microsoft.com/en-us/data/hh297027

How to download and install SQL Server Data Tools (SSDT)

  1. Search the Internet and find the page for downloading SSDT.
  2. Read the instructions and make sure you have installed all of the prerequisites for SSDT. For example, if you’re using Visual Studio 2010 Professional, you must install Service Pack 1 (SP1).
  3. Download and install SSDT.

Description

  • For chapters 1 through 19 of this tutorial, Visual Studio is not needed.
  • For chapter 20 of this tutorial, you can use an Express Edition of Visual Studio such as Visual C# 2010 Express or Visual Basic 2010 Express. To find the page for downloading the appropriate setup program, you can search the Internet.
  • For chapters 21 through 23 of this tutorial, you must install a non-express edition of Visual Studio such as the Professional Edition. If you install Visual Studio 2010, you must also install SQL Server Data Tools (SSDT). If you install Visual Studio 2012, SSDT is included.

 

CHAPTERS