Beginning SQL Server for Developers

670 Pages·2014·26.116 MB·English

by  Robin Dewson

BOOKS FOR PROFESSIONALS BY PROFESSIONALS®
Dewson
Beginning SQL Server for Developers
RELATED
Beginning SQL Server for Developers is the perfect book for developers new to SQL
Server and planning to create and deploy applications against Microsoft’s market-
leading database system for the Windows platform. Now in its fourth edition, the
book is enhanced to cover the very latest developments in SQL Server, including the
in-memory features that are introduced in SQL Server 2014.
Within the book, there are plenty of examples of tasks that developers routinely
perform. You’ll learn to create tables and indexes, and be introduced to best practices
for securing your valuable data. You’ll learn design tradeoffs and find out how to make
sound decisions resulting in scalable databases and maintainable code.
Beginning SQL Server for Developers takes you through the entire database
development process, from installing the software to creating a database to writing
the code to connect to that database and move data in and out. By the end of the
book, you’ll be able to design and create solid and reliable database solutions using
SQL Server.
Shelve in
ISBN 978-1-4842-0281-4
Databases/MS SQL Server 54999
User level:
FOURTH
Beginning–Intermediate
EDITION
SOURCE CODE ONLINE 9781484202814
www.apress.com
www.it-ebooks.info
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.

www.it-ebooks.info
Contents at a Glance
About the Author ���������������������������������������������������������������������������������������������������������������xix
About the Technical Reviewer �������������������������������������������������������������������������������������������xxi
Acknowledgments �����������������������������������������������������������������������������������������������������������xxiii
Introduction ����������������������������������������������������������������������������������������������������������������������xxv
■ Chapter 1: Overview and Installation ��������������������������������������������������������������������������������1
■ Chapter 2: SQL Server Management Studio ��������������������������������������������������������������������25
■ Chapter 3: Database Design and Creation �����������������������������������������������������������������������43
■ Chapter 4: Security and Compliance �������������������������������������������������������������������������������93
■ Chapter 5: Defining Tables ���������������������������������������������������������������������������������������������127
■ Chapter 6: Creating Indexes and Database Diagramming ���������������������������������������������167
■ Chapter 7: In-Memory Tables ����������������������������������������������������������������������������������������197
■ Chapter 8: Database Backups and Recovery �����������������������������������������������������������������211
■ Chapter 9: Database Maintenance ���������������������������������������������������������������������������������265
■ Chapter 10: Data Insertion, Deletion, and Transactions—Disk-Based ��������������������������303
■ Chapter 11: Selecting and Updating Data from Disk-Based Tables �������������������������������335
■ Chapter 12: Working with In-Memory Tables ����������������������������������������������������������������379
■ Chapter 13: Building a View ������������������������������������������������������������������������������������������395
■ Chapter 14: Stored Procedures, Functions, and Security ����������������������������������������������421
v
www.it-ebooks.info
■ Contents at a GlanCe
■ Chapter 15: Natively Compiled Stored Procedures ��������������������������������������������������������463
■ Chapter 16: Essentials for Effective Coding ������������������������������������������������������������������469
■ Chapter 17: Advanced T-SQL and Debugging ����������������������������������������������������������������521
■ Chapter 18: Triggers ������������������������������������������������������������������������������������������������������567
■ Chapter 19: Connecting via Code ����������������������������������������������������������������������������������593
Index ���������������������������������������������������������������������������������������������������������������������������������641
vi
www.it-ebooks.info
Introduction
Beginning SQL Server for Developers is for those people who see themselves as becoming either developers, database
administrators, or a mixture of both, but have yet to tread that path with SQL Server. This edition of my book is for
readers who wish to learn and develop on either the free version of SQL Server, SQL Server Express, or with either the
trial or licensed versions of SQL Server.
Whether you have no knowledge of databases, or have knowledge of desktop databases such as MS Access, or
even come from a server-based background such as Oracle, DB2, Sybase, then this book will provide you with the
insight to get up and running with SQL Server.
Right from the start, your basic knowledge will be expanded, and you will soon be moving from being a beginner
through to a competent and professional developer. It is the aim of this book to cater to a wide range of developers,
from those who prefer to use the graphical interface for as much work as possible, to those who want to become more
adept at using SQL Server’s programming language, T-SQL. Where practical, each method of using SQL Server is
demonstrated, explained, and expanded so that you can evaluate what works best in your situation. You will also find
a chapter on how to work with Excel, .NET and Java, and incorporate them with T-SQL into your programming.
There are plenty of examples within the book of every action, along with details about the security of your data.
You will learn the best way to complete a task and even how to make the correct decision when there are two or
more choices that can be made. And let us not forget that SQL Server can work with in-memory tables and natively
compiled stored procedures.
Once you reach the end of this book, you will be able to design and create solid and reliable database solutions
competently and proficiently.
xxv
www.it-ebooks.info
Chapter 1
Overview and Installation
Welcome to Beginning SQL Server for Developers. This book has been written for those who are interested in learning
how to create solutions with Microsoft SQL Server, but have no prior knowledge of SQL Server. You may well have
had exposure to other database management systems (DBMSs), such as MySQL, Oracle, or Microsoft Access, but SQL
Server uses different interfaces and has a different way of working compared to much of the competition. The aim of
this book is to bring you quickly up to a level at which you are developing competently with SQL Server. This book
is specifically dedicated to beginners and to those who at this stage wish to use only SQL Server. You may find this
book useful for understanding the basics of other database management systems in the marketplace, especially when
working with T-SQL within this book. Many DBMSs use an ANSI-standard SQL, so moving from SQL Server to Oracle,
Sybase, and so on, will be a great deal easier after reading this book.
This chapter answers the following questions:
• Why should I use SQL Server?
• How do I know whether my hardware meets the requirements?
• Can I just confirm that I have the right operating system?
• What can I do with SQL Server?
We will also look at installing your chosen edition and cover the following topics:
• Installing SQL Server on a Windows platform
• Options not installed by default
• Where to install SQL Server physically
• Multiple installations on one computer
• How SQL Server runs on a machine
• How security is implemented
• Logon IDs for SQL Server, especially the sa (system administrator) logon
Why Should I Use SQL Server?
The following discussion is from my point of view, and although it no doubt differs from that of others, the basis of the
discussion holds true. SQL Server faces competition from other database management systems, not only from other
Microsoft products such as Microsoft Access, but also from competitors such as Oracle, Sybase, DB2, and Informix, to
name a few.
1
www.it-ebooks.info
Chapter 1 ■ Overview and installatiOn
You can find Microsoft Access on a large number of PCs. The fact that it is packaged with some editions of Office
and has been around for a number of years in different versions of Office has helped make this DBMS ubiquitous; in
fact, a great number of people actually do use the software. Unfortunately, it does have its limitations when it comes to
scalability, speed, and flexibility, but for many small, in-house systems, these areas of concern are not an issue since
such systems do not require major DBMS functionality. To this end, Microsoft Access can be the correct solution.
Now you come to the serious competition: Oracle and Sybase. Oracle is seen as perhaps the market leader in
the DBMS community, and it has an extremely large user base. There is no denying it is a great product to work with,
if somewhat more complex to install and administer than SQL Server; it fits well with large companies that require
large solutions. It also forms the backbone of some major software packages. There are many parts to Oracle, which
make it a powerful tool, including scalability and performance. It also provides flexibility in that you can add on tools
as you need them, making Oracle more accommodating in that area than SQL Server. For example, SQL Server forces
you to install the .NET Framework on your server regardless of whether you use the new .NET functionality. However,
Oracle isn’t as user-friendly from a developer’s point of view in areas like its ad hoc SQL Query tool and its XML and
web technology tools. It is also less straightforward in how to build up a complete database solution. Other drawbacks
include its cost and the complexity involved in installing and running it effectively. However, you will find that it is
used extensively by web search engines even though SQL Server could and does work just as effectively. SQL Server
has always been a one-purchase solution, such that (providing you buy the correct version or license) tools that allow
you to analyze your data or copy data from one data source such as Excel into SQL Server will all be “in the box.” With
Oracle, on the other hand, you have to purchase more options for every additional feature you want.
Then there is Sybase. It is very much like SQL Server with similar SQL commands and statements (this is the
code used to work with the database and its data) although not as feature-rich. There is a GUI for Sybase although
it is separate from the DBMS installation and a separate product. You will find that many Sybase developers use
command-line commands or third-party tools.
Sybase is also mainly found on Unix/Linux although there is a Windows version. It is very fast and very robust,
and it is rebooted only once, or maybe twice, a year. Being hosted on Unix, Linux, or Solaris also helps with the
reduction in reboots because you don’t have the monthly Windows patching. However, Sybase isn’t as command-and
feature-rich as SQL Server. SQL Server has a more extensive programming language and functionality that is more
powerful than Sybase.
Each DBMS has its own SQL syntax although they all will have the same basic SQL syntax, known as the ANSI-92
standard. This means that the syntax for retrieving data and such is the same from one DBMS to another. However,
each DBMS has its own special syntax to maintain it—trying to use a feature from this SQL syntax in one DBMS may
not work, or may work differently, in another.
SQL Server for me is the best choice in the DBMS marketplace. There are versions of SQL Server that can be
installed and be small enough for a handful of users and versions large enough for the largest corporations, and
there is even a version for Cloud computing. There is also a specific Development Edition, which gives almost the
full functionality of the most expensive version, the Enterprise Edition, but for a tiny fraction of the price. It has the
ability to scale up and deal with terabytes of data without many concerns. As you will see, it is easy to install; it comes
as one complete package for most of its functionality, with a simple install to be performed for the remaining areas if
required.
Now that you know the reasons behind choosing SQL Server, you need to know which versions of SQL Server are
available for purchase, what market each version is aimed at, and which version will be best for you, including which
version can run on your machine.
SQL Server Editions
Before buying this book, you will have perhaps found the Microsoft SQL Server home page, taken a look at the
different editions of SQL Server, and noticed that there are several choices to make. This book focuses on the
Development Edition as this is the edition to work with prior to moving into industry. There are, however, many
developers who will use the Express Edition because it is a free version that is ideal for smaller developments and,
2
www.it-ebooks.info
Chapter 1 ■ Overview and installatiOn
where required, the change or non-existence of functionality is detailed. The differences between the two editions
that this book will cover are the in-memory held data and options concerning backing up and restoring data. Other
editions, where relevant, may be mentioned in passing.
The editions you have are as follows:
• Enterprise: the most powerful edition of SQL Server often used by corporations that will be
running SQL Server on at least one powerful server; fully functional
• Developer: the same functionality as Enterprise but not licensed for production
• Business Intelligence (BI): tools and feature set orientated to building and browsing business
intelligence based data; has a feature set that is reduced or not available where the feature is
designed for transactional based data
• Standard: reduced functionality and memory allowed to be used is limited to 128GB; ideal for
corporations that don’t work with very large data sets due to the reduction useful functionality
• Web: ideal for web \-based corporations that work with smaller online-based data sets; also
useful for ISPs to offer to clients to build their web sites off
• Azure: SQL Server is provided as a service in the cloud where you are supplied with a pre-
installed SQL Server instance and you buy compute and memory
• Express: ideal for small companies and for those wishing to develop small database
applications such as students, charity organizations, and small work-at-home developers. This
edition is free to license.
If you are planning to read this book with an Express Edition of SQL Server, either the Express Edition with Tools
or Express Edition with Advanced Services is required.
The Example
In order to demonstrate SQL Server fully, we will develop a system for a financial company that will have features
such as banking, purchasing shares, and regular buying, including a unit trust savings plan. This is an application that
could fit into a large organization or, with very minor modifications, could be used by a single person to record their
own banking and financial transactions.
The book builds on this idea and develops the example, demonstrating how to take an idea and formulate it into
a design with the correct architecture. It should be said, though, that the example will be the bare minimum to make it
run, as I don’t want to detract from SQL Server and why you have bought the book.
■ Note it would be possible to use the Microsoft standard example that can be downloaded; however, this detracts
from many areas that, as a developer, you need to be aware of and be confident dealing with. Building an example from
the very beginning is the only way to learn sQl server.
if you are coming from another database, you may find that you can skip parts of the example building.
I also use a tool from Red Gate Software to generate some random test data. You do not need this tool for the
book as the random data are available for download from the Apress web site and my own web site. But before you
can get to this point, you need to install SQL Server.
3
www.it-ebooks.info
Chapter 1 ■ Overview and installatiOn
Installation
This chapter will guide you through the installation process of the Developer Edition, although virtually all that you
see will be in every edition. Reporting Services is only available within the Advanced Services Edition of the Express
Edition, which is not covered within the book. Security is also different in the Express and Web Editions, as will be
noted later. Microsoft offers a 180-day trial version at www.microsoft.com/sql, which you can use to follow along with
the examples in this book if you don’t already have SQL Server and are not ready to purchase. However, the Developer
Edition is very cheap to purchase a license to use compared to other editions, and so, by selecting this version, it is not
cost-prohibitive to continue past the trial period.
This book will cover many of the options and combinations of features that can be included within an
installation. A number of different tools are supplied with SQL Server to be included with the installation. You will
look at these tools so that a basic understanding of what they are will allow you to decide which to install.
Installation covers a great many different areas:
• Security issues
• Different types of installation—whether this is the first installation and instance of SQL Server
or a subsequent instance, for development, test, or production
• Custom installations
• Installing only some of the products available
Most of these options will be covered so that by the end of the chapter, you can feel confident and knowledgeable
enough to complete any subsequent installations that suit your needs.
As mentioned in the Editions section of the chapter, this book uses the Developer Edition because it is most likely
the edition you will use as a developer since it doesn’t have all the operating system requirements of the Enterprise
Edition. I will denote in the installation when a feature is not available in other editions. Insert the CD or download
and extract SQL Server from the web site for the Microsoft SQL Server edition of your choice or mount an ISO image
as a virtual drive on your computer. The installation is based on a local computer with only a C: hard drive. I will also
mention installation options concerning servers and network storage where appropriate.
Install
Ensure that you have logged on to the machine with administrative rights so that you are allowed to create files and
folders on your machine, which is obviously required for installation to be successful. If you are using a CD-ROM
and the installation process does not automatically start, open up Windows Explorer and double-click setup.exe,
found at the root level of the CD-ROM. If you are not using a CD-ROM, double-click the installer executable that you
downloaded. This may expand the downloaded single file to a folder with the setup.exe within it.
■ Tip For installation on multiple computers, it is possible to create an automatic build script to ensure all servers are
built to the same company standard. i do not cover this topic, but it is an option to be aware of.
You are now presented with the installation screen for Microsoft .NET Framework if it is not already installed.
.NET is a framework that Microsoft created that allows programs written in VB .NET, C#, and other programming
languages to have a common compile set for computers. SQL Server uses .NET for some of its own internal work, but
also, as a developer, you can write .NET code in any of Microsoft’s .NET languages and include this within SQL Server
databases. With SQL Server 2008 and later, there is also the ability to query the database using .NET and LINQ rather
than native T-SQL, although for clarity the code would be converted to T-SQL.
4
www.it-ebooks.info
Chapter 1 ■ Overview and installatiOn
You are then presented with the SQL Server Installation Center. This screen, shown in Figure 1-1, deals with all
setup processes, including new installations, upgrades from previous versions of SQL Server, and many other options
for maintaining SQL Server installations.
Figure 1-1. Beginning the install with the Installation Center
When you click Installation on the left-hand menu, the options within the main menu alter and you can now
select the New SQL Server stand-alone installation or add features to an existing installation item (at the top of the
Installation Center); then SQL Server installation starts. The first few screens may be different depending on the
edition you are installing.
The first screen asks for an edition and product key. If you have a product key, such as that for the Developers
Edition, you would enter it now. Whatever your selection you wish to use here, click Next. You will then be asked to
accept the license terms. Then click Next. You then come to the system configuration check known as the Global Rules
Check, as you see in Figure 1-2. This is my setup support rules screen, but your screen may slightly differ depending
on operating systems and service packs. It will only be shown if you have action points; otherwise, the SQL Server
installation process will continue automatically. Its main function is to check that the PC meets the hardware and
software requirements, that there are no outstanding reboots, and that you are logged in as an administrator.
5
www.it-ebooks.info