Microsoft SQL Server 2012 A Beginners Guide

833 Pages·2012·28.919 MB·English

by  Dusan Petkovic

SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
Blind folio i
Microsoft® SQL Server™ 2012
A Beginner’s
guide
Fifth Edition
Dušan Petkovic´
New York Chicago San Francisco Lisbon
London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore
Sydney Toronto
Fm.indd 1 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter / Blind folio i
Copyright © 2012 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States
Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or
stored in a database or retrieval system, without the prior written permission of the publisher.
ISBN: 978-0-07-176159-8
MHID: 0-07-176159-4
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-176160-4,
MHID: 0-07-176160-8.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in
corporate training programs. To contact a representative please e-mail us at [email protected].
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of
a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no
intention of infringement of the trademark. Where such designations appear in this book, they have been printed with
initial caps.
Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the
possibility of human or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the
accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results
obtained from the use of such information.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGrawHill”) and its licensors reserve all rights in
and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the
right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce,
modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any
part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use;
any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with
these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR
WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE
OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED
THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant
or guarantee that the functions contained in the work will meet your requirements or that its operation will be
uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any
inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has
no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-
Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that
result from the use of or inability to use the work, even if any of them has been advised of the possibility of such
damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in
contract, tort or otherwise.
eBook 160-8 cr_pg.indd 1 2/17/12 5:26:54 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
Blind folio iii
Dedicated to my sons, Ilja and Igor.

Fm.indd 3 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
Blind folio iv
About the Author
Dušan Petković is a professor in the Department of Computer Science at the
University of Applied Sciences in Rosenheim, Germany. He is the bestselling author
of four editions of SQL Server: A Beginner’s Guide and has authored numerous
articles for SQL Server Magazine and technical papers for Embarcadero.
About the Technical Editor
Todd Meister has been working in the IT industry for over 15 years. He’s been a
technical editor on over 75 titles ranging from SQL Server to the .NET Framework.
Besides technical editing books, he is the Senior IT Architect at Ball State
University in Muncie, Indiana. He lives in central Indiana with his wife, Kimberly,
and their four clever children.
Fm.indd 4 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
Contents at a Glance
Part I Basic Concepts and Installation
Chapter 1 Relational Database Systems: An Introduction 3
Chapter 2 Planning the Installation and Installing SQL Server 21
Chapter 3 SQL Server Management Studio 41
Part II Transact-SQL Language
Chapter 4 SQL Components 71
Chapter 5 Data Definition Language 95
Chapter 6 Queries 135
Chapter 7 Modification of a Table’s Contents 209
Chapter 8 Stored Procedures and User-Defined Functions 227
Chapter 9 System Catalog 259
Chapter 10 Indices 273
Chapter 11 Views 293
Chapter 12 Security System of the Database Engine 315
Chapter 13 Concurrency Control 359
Chapter 14 Triggers 383
Part III SQL Server: System Administration
Chapter 15 System Environment of the Database Engine 405
Chapter 16 Backup, Recovery, and System Availability 427
Chapter 17 Automating System Administration Tasks 467
Chapter 18 Data Replication 487
v
Fm.indd 5 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
vi Microsoft SQL Server 2012: A Beginner’s Guide
Chapter 19 Query Optimizer 507
Chapter 20 Performance Tuning 541
Part IV SQL Server and Business Intelligence
Chapter 21 Business Intelligence: An Introduction 581
Chapter 22 SQL Server Analysis Services 597
Chapter 23 Business Intelligence and Transact-SQL 627
Chapter 24 SQL Server Reporting Services 659
Chapter 25 Optimizing Techniques for Relational Online Analytical Processing 683
Part V Beyond Relational Data
Chapter 26 SQL Server and XML 705
Chapter 27 Spatial Data 735
Chapter 28 SQL Server Full-Text Search 755
Index 781
Fm.indd 6 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
Contents
Acknowledgments xxiii
Introduction xxv
Part I Basic Concepts and Installation
Chapter 1 Relational Database Systems: An Introduction 3
Database Systems: An Overview 4
Variety of User Interfaces 5
Physical Data Independence 5
Logical Data Independence 5
Query Optimization 6
Data Integrity 6
Concurrency Control 6
Backup and Recovery 7
Database Security 7
Relational Database Systems 7
Working with the Book’s Sample Database 8
SQL: A Relational Database Language 11
Database Design 11
Normal Forms 13
Entity-Relationship Model 15
Syntax Conventions 17
Summary 18
Exercises 18
Chapter 2 Planning the Installation and Installing SQL Server 21
SQL Server Editions 22
Planning Phase 23
General Recommendations 23
Planning the Installation 27
Installing SQL Server 31
Summary 40
vii
Fm.indd 7 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
viii Microsoft SQL Server 2012: A Beginner’s Guide
Chapter 3 SQL Server Management Studio 41
Introduction to SQL Server Management Studio 42
Connecting to a Server 43
Registered Servers 44
Object Explorer 45
Organizing and Navigating SQL Server Management Studio’s Panes 46
Using SQL Server Management Studio with the Database Engine 47
Administering Database Servers 47
Managing Databases Using Object Explorer 50
Authoring Activities Using SQL Server Management Studio 60
Query Editor 60
Solution Explorer 63
SQL Server Debugging 64
Summary 66
Exercises 67
Part II Transact-SQL Language
Chapter 4 SQL Components 71
SQL’s Basic Objects 72
Literal Values 72
Delimiters 73
Comments 74
Identifiers 74
Reserved Keywords 74
Data Types 75
Numeric Data Types 75
Character Data Types 76
Temporal Data Types 76
Miscellaneous Data Types 78
Storage Options 81
Transact-SQL Functions 82
Aggregate Functions 83
Scalar Functions 83
Scalar Operators 90
Global Variables 91
Fm.indd 8 2/1/12 1:47:41 PM
SQL_2008 / Microsoft SQL Server 2012: ABG / Petkovic / 176160-8 / Front Matter
Contents ix
NULL Values 92
Summary 93
Exercises 93
Chapter 5 Data Definition Language 95
Creating Database Objects 96
Creation of a Database 96
CREATE TABLE: A Basic Form 101
CREATE TABLE and Declarative Integrity Constraints 104
Referential Integrity 110
Creating Other Database Objects 113
Integrity Constraints and Domains 115
Modifying Database Objects 117
Altering a Database 118
Altering a Table 125
Removing Database Objects 130
Summary 131
Exercises 131
Chapter 6 Queries 135
SELECT Statement: Its Clauses and Functions 136
WHERE Clause 138
GROUP BY Clause 151
Aggregate Functions 153
HAVING Clause 159
ORDER BY Clause 160
SELECT Statement and IDENTITY Property 163
CREATE SEQUENCE Statement 164
Set Operators 167
CASE Expressions 172
Subqueries 174
Subqueries and Comparison Operators 175
Subqueries and the IN Operator 176
Subqueries and ANY and ALL Operators 177
Temporary Tables 179
Join Operator 180
Two Syntax Forms to Implement Joins 180
Natural Join 181
Fm.indd 9 2/1/12 1:47:42 PM