Microsoft SQL Server 2005: A Beginner''s Guide

by Petkovic, Dusan

• ISBN13: 9780072260939
• ISBN10: 0072260939
• Edition: 1st
• Format: Paperback
• Copyright: 2005-12-13
• Publisher: McGraw-Hill Education

Summary

It's been five years since Microsoft released a new version of SQL Server-which holds 39% of the Windows database market-and the latest edition of this bestselling beginner's guide gets users up and running on SQL Server 2005. The book covers database concepts, and discusses key topics for new users including the SQL Server Workbench,T-SQL, automated administration tasks, security, and analysis. Previous edition sold 30,000 copies Worked examples and exercises throughout provide hands-on experience Free code will be available online

Author Biography

Dusan Petkovic (Rosenheim, Germany) is a professor in the Department of Computer Science at the Polytechnic in Rosenheim, Germany. He is the best-selling author of two editions of SQL Server: A Beginner's Guide and has authored numerous articles for SQL Server Magazine and technical papers for Embarcadero.

Table of Contents

Acknowledgments	xix
Introduction	xxi
Part I SQL Server: Basic Concepts
Database Systems and SQL Server	3	(20)
Database Systems: An Overview	6	(3)
Variety of User Interfaces	7	(1)
Physical Data Independence	7	(1)
Logical Data Independence	7	(1)
Query Optimization	7	(1)
Data Integrity	8	(1)
Concurrency Control	8	(1)
Backup and Recovery	8	(1)
Security and Authorization	9	(1)
Relational Database Systems	9	(3)
Working with the Book's Sample Database	9	(3)
SQL: A Relational Database Language	12	(2)
Syntax Conventions	14	(1)
Database Design	14	(6)
Normal Forms	16	(2)
Entity-Relationship (ER) Model	18	(2)
Conclusion	20	(1)
Overview of Microsoft SQL Server	20	(1)
Exercises	21	(2)
SQL Server Management Studio	23	(26)
The SQL Server Program Group and Books Online	24	(1)
Introduction to SQL Server Management Studio	25	(19)
Using Management Studio with the SQL Server Database Engine	28	(4)
Managing Databases Using Object Explorer	32	(7)
Authoring Activities Using SQL Server Management Studio	39	(5)
Conclusion	44	(1)
Exercises	44	(5)
Part II Transact-SQL Language
SQL Components	49	(24)
SQL's Basic Objects	50	(3)
Comments	52	(1)
Identifiers	52	(1)
Reserved Keywords	52	(1)
Data Types	53	(6)
Numeric Data Types	53	(1)
String Data Types	54	(2)
Specifying Date and Time	56	(1)
Derived Data Types	57	(1)
Miscellaneous Data Types	58	(1)
User-Defined Data Types	59	(1)
Predicates	59	(1)
Aggregate Functions	60	(1)
Scalar Functions	60	(7)
Numeric Functions	61	(1)
Date Functions	62	(1)
String Functions	63	(2)
Text/Image Functions	65	(1)
System Functions	65	(2)
Scalar Operators	67	(2)
Global Variables	68	(1)
Null Values	69	(1)
Conclusion	70	(1)
Exercises	71	(2)
Data Definition Language	73	(38)
Creating Database Objects	74	(24)
Creation of a Database	74	(4)
Create Table: A Basic Form	78	(2)
Create Table: The Enhanced Form	80	(1)
Create Table and Declarative Integrity Constraints	81	(5)
Referential Constraints	86	(5)
Creating Other Database Objects	91	(2)
Integrity Constraints and Domains	93	(5)
Modifying Database Objects	98	(7)
Altering a Database	99	(1)
Altering a Table: A Basic Form	100	(2)
Altering a Table: Enhanced Form	102	(3)
Removing Database Objects	105	(1)
Conclusion	106	(1)
Exercises	106	(5)
Simple Queries	111	(58)
Select Statement: A Basic Form	112	(2)
Where Clause	114	(17)
Boolean Operators	117	(5)
In and Between Operators	122	(3)
Queries Involving Null Values	125	(2)
Like Operator	127	(4)
Simple Subqueries	131	(8)
Subqueries and Comparison Operators	132	(1)
Subqueries and In Operator	133	(2)
Any and All Operators	135	(3)
Exists Function	138	(1)
Queries in the From Clause	138	(1)
Group by Clause	139	(2)
Aggregate Functions	141	(8)
Convenient Aggregates	141	(6)
Statistical Aggregates	147	(1)
Superaggregates (Operators Cube and Rollup)	148	(1)
User-Defined Aggregate Functions	148	(1)
Having Clause	149	(1)
Order by Clause	150	(2)
Select Statement and Identity Property	152	(2)
Set Operators	154	(5)
Case Expressions	159	(2)
Compute Clause	161	(3)
Temporary Tables	164	(1)
Computed Columns	165	(1)
Conclusion	166	(1)
Exercises	166	(3)
Complex Queries	169	(36)
Join Operator	170	(17)
Two Syntax Forms to Implement Joins	171	(1)
Equijoin	171	(4)
Cartesian Product	175	(2)
Natural Join	177	(2)
Thetajoin	179	(1)
Joining More Than Two Tables	180	(2)
Joining a Table with Itself	182	(2)
Outer Join	184	(3)
Correlated Subqueries	187	(6)
Exists Function and Subqueries	189	(4)
Should You Use Join or Subqueries?	193	(2)
Subquery Advantages	193	(1)
Join Advantages	194	(1)
Common Table Expressions	195	(7)
CTE and Nonrecursive Queries	195	(2)
Common Table Expressions and Recursive Queries	197	(5)
Conclusion	202	(1)
Exercises	203	(2)
Modification of a Table's Contents	205	(12)
Insert Statement	206	(4)
Inserting a Single Row	206	(3)
Inserting Multiple Rows	209	(1)
Update Statement	210	(3)
Delete Statement	213	(2)
Conclusion	215	(1)
Exercises	216	(1)
Stored Procedures and User-Defined Functions	217	(28)
SQL Extensions	218	(7)
Block of Statements	218	(1)
If Statement	219	(1)
While Statement	220	(1)
Local Variables	221	(1)
Miscellaneous Procedural Statements	222	(1)
Handling Events With Try and Catch Statements	223	(2)
Stored Procedures	225	(12)
Creation and Execution of Stored Procedures	226	(5)
System Stored Procedures	231	(1)
Stored Procedures and CLR	231	(6)
User-Defined Functions	237	(6)
Creation and Execution of User-Defined Functions	237	(4)
User-Defined Functions and CLR	241	(2)
Conclusion	243	(1)
Exercises	243	(2)
Indices and Query Optimization	245	(38)
Indices	246	(14)
Clustered and Nonclustered Indices	248	(2)
Indices and the Corresponding Transact-SQL Statements	250	(6)
Editing Index Information	256	(1)
Indices and Keys	257	(1)
Guidelines for Creating Indices	258	(2)
General Criteria to Improve Efficiency	260	(5)
Join vs. Correlated Subquery	260	(1)
Incomplete Statements	261	(1)
Like Operator	261	(1)
Transact-SQL Statements for Query Optimization	262	(3)
Query Optimizer	265	(13)
Optimizer Statistics	265	(2)
Implementing the Join Operation	267	(1)
Optimizer Hints	268	(10)
The DBCC Command and Indices	278	(2)
Conclusion	280	(1)
Exercises	280	(3)
Views	283	(24)
DDL Statements and Views	284	(7)
Creating a View	285	(4)
Altering and Removing Views	289	(2)
DML Statements and Views	291	(7)
View Retrieval	291	(1)
Insert Statement and a View	292	(3)
Update Statement and a View	295	(2)
Delete Statement and a View	297	(1)
Indexed Views	298	(7)
Creating an Indexed View	299	(3)
Benefits of Indexed Views	302	(1)
Using Indexed Views	303	(2)
Conclusion	305	(1)
Exercises	305	(2)
System Catalog	307	(18)
System Tables	308	(3)
Sysobjects	308	(1)
Syscolumns	309	(1)
Sysindexes	309	(1)
Sysusers	310	(1)
Sysdatabases	310	(1)
Sysdepends	310	(1)
Sysconstraints	311	(1)
Catalog Views	311	(2)
Sys_objects	312	(1)
Sys.columns	313	(1)
Sys.database_principals	313	(1)
Querying Catalog Views	313	(2)
Other Ways to Access System Information	315	(8)
System Procedures	316	(2)
System Functions	318	(2)
Property Functions	320	(1)
Information Schema	320	(3)
Conclusion	323	(1)
Exercises	324	(1)
SQL Server Security	325	(40)
Authentication	326	(6)
SQL Server Encryption Policy and Mechanisms	327	(2)
Setting Up SQL Server Security Using DDL	329	(2)
Setting Up SQL Server Security Using System Procedures	331	(1)
System Procedures Concerning Logins	331	(1)
Schema	332	(4)
User-Schema Separation	333	(1)
DDL Statements Concerning Schema	334	(2)
Database Security	336	(4)
Setting Up Database User Accounts with DDL	336	(2)
Setting Up Database User Accounts Using System Procedures	338	(1)
Default Database Schemas	339	(1)
Roles	340	(11)
Fixed Server Roles	340	(1)
Fixed Server Roles and Their Permissions	341	(2)
Fixed Database Roles	343	(1)
Fixed Database Roles and Their Permissions	344	(2)
Application Roles	346	(3)
User-Defined Database Roles	349	(2)
Authorization	351	(8)
Grant Statement	351	(6)
Deny Statement	357	(1)
Revoke Statement	358	(1)
Views and Data Access	359	(1)
Stored Procedures and Data Access	360	(1)
Conclusion	361	(1)
Exercises	362	(3)
Triggers	365	(20)
Introduction	366	(1)
How Triggers Work	366	(10)
Application Areas for After Triggers	369	(4)
Application Areas for Instead of Triggers	373	(1)
First and Last Triggers	374	(1)
DDL Triggers	375	(1)
Triggers and CLR	376	(5)
The Output Clause	381	(2)
Modifying Trigger's Structure	383	(1)
Conclusion	383	(1)
Exercises	384	(1)
Transactions	385	(20)
Introduction	386	(1)
Transact-SQL Statements and Transactions	387	(4)
Transaction Logging	391	(1)
Locking	391	(6)
Lock Granularity	392	(1)
Kinds of Locks	393	(3)
The Select Statement and Locking	396	(1)
Isolation Levels	397	(4)
Row Versioning and Snapshot Isolation Level	399	(2)
Deadlock	401	(1)
Conclusion	402	(1)
Exercises	402	(3)
SQL Server System Environment	405	(22)
Disk Storage	406	(4)
Data Pages	407	(3)
Large Objects	410	(1)
Index Pages	410	(1)
System Databases	410	(1)
Utilities	411	(5)
bcp Utility	412	(1)
osql Utility	413	(1)
sqlcmd Utility	414	(2)
Instances of SQL Server	416	(2)
Unicode	418	(1)
Character Encoding	418	(1)
SQL Server Architecture	419	(4)
Multiprocessor Hardware Architectures	420	(1)
Multithreading Architecture of SQL Server	421	(2)
Conclusion	423	(1)
Exercises	423	(4)
Part III SQL Server: System Administration
Overview of System Administration	427	(6)
Administration Tools	428	(3)
MS SQL Server 2005 Program Group	429	(1)
SQL Computer Manager	429	(1)
Dynamic Management Views	430	(1)
System Administrator	431	(1)
Conclusion	432	(1)
Planning the Installation and Installing SQL Server	433	(16)
Planning the Installation	434	(3)
Purpose of SQL Server	434	(1)
Hardware and Software Requirements	435	(1)
SQL Server Editions	436	(1)
Installation Recommendations	437	(1)
Installing SQL Server	437	(8)
Beginning the Installation	438	(7)
Configuring SQL Server After Installation	445	(1)
Create Server Groups and Register the Server	445	(1)
Set Server Options	446	(1)
Starting and Stopping an Instance of SQL Server	446	(2)
Conclusion	448	(1)
Managing Databases and Database Files	449	(12)
Managing Filegroups	450	(2)
Viewing Filegroups	451	(1)
Managing Databases	452	(6)
Creating Databases	452	(1)
Viewing and Modifying Database Options	453	(3)
Modifying Databases	456	(2)
Managing Transaction Logs	458	(1)
Conclusion	459	(1)
Exercises	459	(2)
Managing Security	461	(12)
Introduction	462	(3)
Implementing a Security Mode	463	(1)
Managing SQL Server Logins	464	(1)
Database Security Permissions	465	(3)
Roles	465	(3)
Managing Permissions	468	(3)
Conclusion	471	(1)
Exercises	471	(2)
Backup and Recovery	473	(34)
Introduction	474	(1)
Software and Hardware Failures	474	(1)
Transaction Log	475	(1)
Backup	475	(3)
Full Database Backup	476	(1)
Differential Backup	476	(1)
Transaction Log Backup	476	(2)
Database File Backup	478	(1)
Performing Backup	478	(13)
Backup Using Management Studio	478	(4)
Backup Using Transact-SQL Statements	482	(3)
Which Databases to Back Up?	485	(1)
Minimizing System Downtime	486	(3)
High Availability	489	(2)
Recovery	491	(13)
Automatic Recovery	491	(1)
Manual Recovery	492	(9)
Recovery Models	501	(2)
Recovery to a Mark	503	(1)
Conclusion	504	(1)
Exercises	504	(3)
Automating System Administration Tasks	507	(20)
Introduction	508	(1)
SQL Server Agent	509	(1)
Running and Configuring SQL Server Agent	510	(1)
Creating Jobs and Operators	510	(7)
Job Steps	510	(1)
Creating a Job Using SQL Server Management Studio	511	(3)
Creating Job Schedules	514	(1)
Creating Operators for Notification	515	(1)
Viewing the Job History Log	516	(1)
Alerts	517	(7)
Error Messages	517	(2)
SQL Server Error Log	519	(1)
Event Log	519	(1)
Defining Alerts to Handle SQL Server Errors	520	(3)
Defining Alerts to Handle User-Defined Errors	523	(1)
Conclusion	524	(1)
Exercises	525	(2)
Performance and Tuning	527	(30)
Introduction	528	(1)
Factors that Affect Performance	529	(9)
Database Applications and Performance	529	(2)
The Database Server and Performance	531	(1)
System Resources and Performance	532	(1)
CPU	533	(5)
Monitoring Tools	538	(10)
Application Performance Tools	538	(4)
SQL Server-Specific Monitoring	542	(4)
Tools for Monitoring System Resources	546	(2)
Choose the Right Tool	548	(9)
SQL Server Profiler	548	(1)
Database Engine Tuning Advisor	549	(7)
Conclusion	556	(1)
Data Partitioning	557	(12)
Introduction	558	(1)
Ways to Partition Your Data	559	(1)
Steps for Creating Partitioned Tables	559	(7)
Set Partition Goals	560	(1)
Determine Partitioning Key and Number of Partitions	560	(1)
Create a Filegroup for Each Partition	561	(1)
Create Partition Function and Partition Scheme	562	(3)
Collocating Objects	565	(1)
Guidelines for Partitioning Tables and Indices	566	(1)
Conclusion	566	(3)
Data Replication	569	(20)
Distributed Data	570	(2)
Methods for Distributing Data	570	(2)
SQL Server Replication---An Overview	572	(8)
Publishers, Distributors, and Subscribers	572	(1)
Publications and Articles	573	(2)
Replication Types	575	(3)
Replication Models	578	(2)
Managing Replication	580	(4)
Configuring the Distribution and Publication Servers	580	(1)
Setting Up Publications	581	(2)
Setting Up Subscription Servers	583	(1)
Conclusion	584	(1)
Exercises	585	(4)
Part IV Microsoft Analysis Services
Data Warehousing: An Introduction	589	(16)
Online Transaction Processing vs. Data Warehousing	590	(2)
Online Transaction Processing	590	(1)
Data Warehouse Systems	591	(1)
Data Warehouses and Data Marts	592	(3)
Data Warehouse Design	595	(3)
Dimensional Model	595	(3)
Cubes and Storage Modes	598	(2)
MOLAP, ROLAP, and HOLAP	599	(1)
Aggregation	600	(2)
How Much to Aggregate?	601	(1)
Data Access	602	(1)
Conclusion	603	(1)
Exercises	603	(2)
Microsoft Analysis Services	605	(16)
BI Development Studio	607	(5)
Analysis Services and Cubes	612	(7)
Creating a Cube	614	(1)
Designing Storage Aggregation and Processing the Cube	615	(3)
Browsing a Cube	618	(1)
Conclusion	619	(2)
Business Intelligence and SQL	621	(18)
OLAP Extensions in Transact-SQL	622	(15)
Cube Operator	622	(4)
Rollup Operator	626	(2)
Ranking Functions	628	(6)
Top n Clause	634	(2)
Pivot and Unpivot Operators	636	(1)
Conclusion	637	(2)
Microsoft Reporting Services	639	(20)
Introduction	640	(2)
Report Manager	640	(1)
Report Server	641	(1)
Report Server Database	641	(1)
Creating Reports	642	(10)
Report Wizard	643	(7)
Parameterized Reports	650	(2)
Processing and Managing Reports	652	(1)
Accessing and Delivering Reports	653	(3)
On-Demand Reports	653	(2)
Report Subscription	655	(1)
Conclusion	656	(1)
Exercises	656	(3)
Part V XML Support
Overview of XML	659	(10)
World Wide Web	660	(1)
XML and Related Languages	661	(4)
XML-Basic Concepts	661	(4)
Document Type Definitions	665	(3)
XML Schema	667	(1)
Conclusion	668	(1)
SQL Server 2005 and XML	669	(18)
Storing XML in Relational Databases	670	(1)
The XML Data Type	671	(7)
Indexing an XML Column	674	(1)
Typed vs. Untyped XML	675	(3)
Constructing XML Documents Using the for XML Clause	678	(7)
Raw Mode	678	(1)
Auto Mode	679	(1)
Explicit Mode	680	(2)
Path Mode	682	(1)
Specifying Directives with Different Modes	683	(2)
XML Query Languages	685	(1)
Conclusion	686	(1)
Index	687