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 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. 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 ■ 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 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 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 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 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 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 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