Microsoft SQL Server 2012 Bible

Adam Jorgensen, Jorge Segarra, Patrick LeBlanc, Jose Chinchilla, Aaron Nelson

• Publisher: Wiley
• Year: 2012
• Print ISBN: 9781118106877
• eBook ISBN: 9781118282175
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Harness the powerful new SQL Server 2012

Microsoft SQL Server 2012 is the most significant update to this product since 2005, and it may change how database administrators and developers perform many aspects of their jobs. If you're a database administrator or developer, Microsoft SQL Server 2012 Bible teaches you everything you need to take full advantage of this major release. This detailed guide not only covers all the new features of SQL Server 2012, it also shows you step by step how to develop top-notch SQL Server databases and new data connections and keep your databases performing at peak.

The book is crammed with specific examples, sample code, and a host of tips, workarounds, and best practices. In addition, downloadable code is available from the book's companion web site, which you can use to jumpstart your own projects.

• Serves as an authoritative guide to Microsoft's SQL Server 2012 for database administrators and developers
• Covers all the software's new features and capabilities, including SQL Azure for cloud computing, enhancements to client connectivity, and new functionality that ensures high-availability of mission-critical applications
• Explains major new changes to the SQL Server Business Intelligence tools, such as Integration, Reporting, and Analysis Services
• Demonstrates tasks both graphically and in SQL code to enhance your learning
• Provides source code from the companion web site, which you can use as a basis for your own projects
• Explores tips, smart workarounds, and best practices to help you on the job

Get thoroughly up to speed on SQL Server 2012 with Microsoft SQL Server 2012 Bible.

Part I
Laying the Foundations

In This Part

1. Chapter 1 The World of SQL Server
2. Chapter 2 Data Architecture
3. Chapter 3 Installing SQL Server
4. Chapter 4 Client Connectivity
5. Chapter 5 SQL Server Management and Development Tools

Chapter 1
The World of SQL Server

In This Chapter

1. Understanding SQL Server History and Overview
2. Understanding SQL Server Components and Tools
3. Understanding Notable Features in SQL 2012
4. What's New with SQL Server 2012?

SQL Server 2012 represents another tremendous accomplishment for the Microsoft data platform organization. A number of new features in this release drive performance and scalability to new heights. A large focus is on speed of data access, ease and flexibility of integration, and capability of visualization. These are all strategic areas in which Microsoft has focused on to add value since SQL Server 2005.

SQL Server History

SQL Server has grown considerably over the past two decades from its early roots with Sybase.

In 1989, Microsoft, Sybase, and Ashton-Tate jointly released SQL Server 1.0. The product was based on Sybase SQL Server 3.0 for UNIX and VMS.

SQL Server 4.2.1 for Windows NT released in 1993. Microsoft began making changes to the code.

SQL Server 6.0 (code named SQL 95) released in 1995. In 1996, the 6.5 upgrade (Hydra) was released in 1996. It included the first version of Enterprise Manager (StarFighter I) and SQL Server Agent (StarFighter II.)

SQL Server 7.0 (Sphinx), released in 1999 and was a full rewrite of the database engine by Microsoft. From a code sense, this was the first Microsoft SQL Server. SQL Server 7 also included English Query (Argo), OLAP Services (Plato), Replication, Database Design and Query tools (DaVinci), and Full-Text Search (aptly code named Babylon). Data Transformation Services (DTS) was introduced.

SQL Server 2000 (Shiloh) 32-bit, version 8, introduced SQL Server to the enterprise with clustering, better performance, and OLAP. It supported XML through three different XML add-on packs. It added user-defined functions, indexed views, clustering support, OLAP, Distributed Partition Views, and improved Replication. SQL Server 2000 64-bit version for Intel Itanium (Liberty) released in 2003, along with the first version of Reporting Services (Rosetta) and Data Mining tools (Aurum). DTS becomes powerful and gained in popularity. Northwind joined Pubs as the sample database.

SQL Server 2005 (Yukon), version 9, was another rewrite of the database engine and pushed SQL Server further into the enterprise space. In 2005, a ton of new features and technologies were added including Service Broker, Notification Services, CLR, XQuery and XML data types, and SQLOS. T-SQL gained try-catch, and the system tables were replaced with Dynamic Management Views. Management Studio replaced Enterprise Manager and Query Analyzer. DTS was replaced by Integration Services. English Query was removed, and stored procedure debugging was moved from the DBA interface to Visual Studio. AdventureWorks and AdventureWorksDW replaced Northwind and Pubs as the sample database. SQL Server 2005 supported 32-bit, 64x, and Itanium CPUs. Steve Ballmer publically vowed to never again make customers wait 5 years between releases and to return to a 2-to-3-year release cycle.

SQL Server 2008 (Katmai), version 10, is a natural evolution of SQL Server adding Policy-Based Management, Data Compression, Resource Governor, and new beyond relational data types. Notification Services went the way of English Query. T-SQL finally has date and time data types, table-valued parameters, the debugger returns, and Management Studio gets IntelliSense.

SQL Server 2008R2, version 10.5, is a release mostly focused on new business intelligence features and SharePoint 2010 supportability. The list of major new work and code in the SQL Server 2005 and 2008/R2 releases have been fully covered in previous editions, but the high points would be SQLCLR (this was the integration of another long-term strategy project); XML support; Service Broker; and Integration Services, which is all ground up code. Microsoft formed a new team built on the original members of the DTS team, adding in some C++, hardware, AS and COM+ folks, and Report Builder. Additional features to support SharePoint 2010 functionality and other major releases are also critically important. Now you have SQL 2012; so look at where this new release can carry you forward.

SQL Server in the Database Market

SQL Server's position in the database market has consistently grown over time. This section discusses some of the primary competition to SQL Server, and what makes SQL a strong choice for data management, business intelligence, and cloud computing along with the strength of the SQL Server community.

SQL Server's Competition

SQL Server competes primarily with two other major database platforms, Oracle and IBM's DB2. Both of these products have existed for longer than SQL Server, but the last four releases of SQL Server have brought them closer together. They are adding features that SQL has had for years and vice versa. Many of the scalability improvements added since SQL 2005 have been directly focused on overtaking the performance and other qualities of these products. Microsoft has succeeded in these releases in besting benchmarks set by many other products both in the relational database platforms as well as in data integration, analytics, and reporting. These i...

Table of contents

• Cover
• Contents
• Title
• Copyright
• Dedication
• About the Authors
• About the Contributors
• About the Technical Editors
• Credits
• Acknowledgments
• Introduction
• Part I: Laying the Foundations
• Part II: Building Databases and Working with Data
• Part III: Advanced T-SQL Data Types and Querying Techniques
• Part IV: Programming with T-SQL
• Part V: Enterprise Data Management
• Part VI: Securing Your SQL Server
• Part VII: Monitoring and Auditing
• Part VIII: Performance Tuning and Optimization
• Part IX: Business Intelligence
• End User License Agreement