Microsoft SQL Server 2012 A Beginners Guide 5/E, 5th Edition

Microsoft SQL Server 2012 A Beginners Guide 5/E, 5th Edition

by Dusan Petkovic
Released November 2011
Publisher(s): McGraw-Hill
ISBN: 9780071761598

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Essential Microsoft SQL Server 2012 Skills Made Easy

Get up and running on Microsoft SQL Server 2012 in no time with help from this thoroughly revised, practical resource. Filled with real-world examples and hands-on exercises, Microsoft SQL Server 2012: A Beginner's Guide, Fifth Edition starts by explaining fundamental relational database system concepts. Then, you'll learn how to write Transact-SQL statements, execute simple and complex database queries, handle system administration and security, and use the powerful analysis, business intelligence, and reporting tools. XML, spatial data, and full-text search are also covered in this step-by-step tutorial.

  • Install, configure, and customize SQL Server 2012
  • Create and modify database objects with Transact SQL statements
  • Write stored procedures and user-defined functions
  • Handle backup and recovery
  • Automate administrative tasks
  • Tune your database system for optimal performance, availability, and reliability
  • Implement security measures using authentication, encryption, and authorization
  • Work with SQL Server Analysis Services, SQL Server Reporting Services, and other business intelligence tools
  • Store, display, and query XML documents
  • Manage spatial data
  • Query documents using MS Full-Text Search (FTS)

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. About the Author
  6. Contents at a Glance
  7. Contents 
  8. Acknowledgments
  9. Introduction
  10. Part I: Basic Concepts and Installation
    1. Chapter 1. Relational Database Systems: An Introduction
      1. Database Systems: An Overview
        1. Variety of User Interfaces
        2. Physical Data Independence
        3. Logical Data Independence
        4. Query Optimization
        5. Data Integrity
        6. Concurrency Control
        7. Backup and Recovery
        8. Database Security
      2. Relational Database Systems
        1. Working with the Book’s Sample Database
        2. SQL: A Relational Database Language
      3. Database Design
        1. Normal Forms
        2. Entity-Relationship Model
      4. Syntax Conventions
      5. Summary
      6. Exercises
    2. Chapter 2. Planning the Installation and Installing SQL Server
      1. SQL Server Editions
      2. Planning Phase
        1. General Recommendations
        2. Planning the Installation
      3. Installing SQL Server
      4. Summary
    3. Chapter 3. SQL Server Management Studio
      1. Introduction to SQL Server Management Studio
        1. Connecting to a Server
        2. Registered Servers
        3. Object Explorer
        4. Organizing and Navigating SQL Server Management Studio’s Panes
      2. Using SQL Server Management Studio with the Database Engine
        1. Administering Database Servers
        2. Managing Databases Using Object Explorer
      3. Authoring Activities Using SQL Server Management Studio
        1. Query Editor
        2. Solution Explorer
        3. SQL Server Debugging
      4. Summary
      5. Exercises
  11. Part II: Transact-SQL Language
    1. Chapter 4. SQL Components
      1. SQL’s Basic Objects
        1. Literal Values
        2. Delimiters
        3. Comments
        4. Identifiers
        5. Reserved Keywords
      2. Data Types
        1. Numeric Data Types
        2. Character Data Types
        3. Temporal Data Types
        4. Miscellaneous Data Types
        5. Storage Options
      3. Transact-SQL Functions
        1. Aggregate Functions
        2. Scalar Functions
      4. Scalar Operators
        1. Global Variables
      5. NULL Values
      6. Summary
      7. Exercises
    2. Chapter 5. Data Definition Language
      1. Creating Database Objects
        1. Creation of a Database
        2. CREATE TABLE: A Basic Form
        3. CREATE TABLE and Declarative Integrity Constraints
        4. Referential Integrity
        5. Creating Other Database Objects
        6. Integrity Constraints and Domains
      2. Modifying Database Objects
        1. Altering a Database
        2. Altering a Table
      3. Removing Database Objects
      4. Summary
      5. Exercises
    3. Chapter 6. Queries
      1. SELECT Statement: Its Clauses and Functions
        1. WHERE Clause
        2. GROUP BY Clause
        3. Aggregate Functions
        4. HAVING Clause
        5. ORDER BY Clause
        6. SELECT Statement and IDENTITY Property
        7. CREATE SEQUENCE Statement
        8. Set Operators
        9. CASE Expressions
      2. Subqueries
        1. Subqueries and Comparison Operators
        2. Subqueries and the IN Operator
        3. Subqueries and ANY and ALL Operators
      3. Temporary Tables
      4. Join Operator
        1. Two Syntax Forms to Implement Joins
        2. Natural Join
        3. Cartesian Product
        4. Outer Join
        5. Further Forms of Join Operations
      5. Correlated Subqueries
        1. Subqueries and the EXISTS Function
        2. Should You Use Joins or Subqueries?
      6. Table Expressions
        1. Derived Tables
        2. Common Table Expressions
      7. Summary
      8. Exercises
    4. Chapter 7. Modification of a Table’s Contents
      1. INSERT Statement
        1. Inserting a Single Row
        2. Inserting Multiple Rows
        3. Table Value Constructors and INSERT
      2. UPDATE Statement
      3. DELETE Statement
      4. Other T-SQL Modification Statements and Clauses
        1. TRUNCATE TABLE Statement
        2. MERGE Statement
        3. The OUTPUT Clause
      5. Summary
      6. Exercises
    5. Chapter 8. Stored Procedures and User-Defined Functions
      1. Procedural Extensions
        1. Block of Statements
        2. IF Statement
        3. WHILE Statement
        4. Local Variables
        5. Miscellaneous Procedural Statements
        6. Exception Handling with TRY, CATCH, and THROW
      2. Stored Procedures
        1. Creation and Execution of Stored Procedures
        2. Stored Procedures and CLR
      3. User-Defined Functions
        1. Creation and Execution of User-Defined Functions
        2. Changing the Structure of UDFs
        3. User-Defined Functions and CLR
      4. Summary
      5. Exercises
    6. Chapter 9. System Catalog
      1. Introduction to the System Catalog
      2. General Interfaces
        1. Catalog Views
        2. Dynamic Management Views and Functions
        3. Information Schema
      3. Proprietary Interfaces
        1. System Stored Procedures
        2. System Functions
        3. Property Functions
      4. Summary
      5. Exercises
    7. Chapter 10. Indices
      1. Introduction
        1. Clustered Indices
        2. Nonclustered Indices
      2. Transact-SQL and Indices
        1. Creating Indices
        2. Obtaining Index Fragmentation Information
        3. Editing Index Information
        4. Altering Indices
        5. Removing and Renaming Indices
      3. Guidelines for Creating and Using Indices
        1. Indices and Conditions in the WHERE Clause
        2. Indices and the Join Operator
        3. Covering Index
      4. Special Types of Indices
        1. Virtual Computed Columns
        2. Persistent Computed Columns
      5. Summary
      6. Exercises
    8. Chapter 11. Views
      1. DDL Statements and Views
        1. Creating a View
        2. Altering and Removing Views
        3. Editing Information Concerning Views
      2. DML Statements and Views
        1. View Retrieval
        2. INSERT Statement and a View
        3. UPDATE Statement and a View
        4. DELETE Statement and a View
      3. Indexed Views
        1. Creating an Indexed View
        2. Modifying the Structure of an Indexed View
        3. Editing Information Concerning Indexed Views
        4. Benefits of Indexed Views
      4. Summary
      5. Exercises
    9. Chapter 12. Security System of the Database Engine
      1. Authentication
        1. Implementing an Authentication Mode
        2. Encrypting Data
        3. Setting Up the Database Engine Security
      2. Schemas
        1. User-Schema Separation
        2. DDL Schema-Related Statements
      3. Database Security
        1. Managing Database Security Using Management Studio
        2. Managing Database Security Using Transact-SQL Statements
        3. Default Database Schemas
      4. Roles
        1. Fixed Server Roles
        2. Fixed Database Roles
        3. Application Roles
        4. User-Defined Server Roles
        5. User-Defined Database Roles
      5. Authorization
        1. GRANT Statement
        2. DENY Statement
        3. REVOKE Statement
        4. Managing Permissions Using Management Studio
        5. Managing Authorization and Authentication of Contained Databases
      6. Change Tracking
      7. Data Security and Views
      8. Summary
      9. Exercises
    10. Chapter 13. Concurrency Control
      1. Concurrency Models
      2. Transactions
        1. Properties of Transactions
        2. Transact-SQL Statements and Transactions
        3. Transaction Log
      3. Locking
        1. Lock Modes
        2. Lock Granularity
        3. Lock Escalation
        4. Affecting Locks
        5. Displaying Lock Information
        6. Deadlock
      4. Isolation Levels
        1. Concurrency Problems
        2. The Database Engine and Isolation Levels
      5. Row Versioning
        1. READ COMMITTED SNAPSHOT Isolation Level
        2. SNAPSHOT Isolation Level
      6. Summary
      7. Exercises
    11. Chapter 14. Triggers
      1. Introduction
        1. Creating a DML Trigger
        2. Modifying a Trigger’s Structure
        3. Using deleted and inserted Virtual Tables
      2. Application Areas for DML Triggers
        1. AFTER Triggers
        2. INSTEAD OF Triggers
        3. First and Last Triggers
      3. DDL Triggers and Their Application Areas
        1. Database-Level Triggers
        2. Server-Level Triggers
      4. Triggers and CLR
      5. Summary
      6. Exercises
  12. Part III: SQL Server: System Administration
    1. Chapter 15. System Environment of the Database Engine
      1. System Databases
        1. master Database
        2. model Database
        3. tempdb Database
        4. msdb Database
      2. Disk Storage
        1. Properties of Data Pages
        2. Types of Data Pages
        3. Parallel Processing of Tasks
      3. Utilities and the DBCC Command
        1. bcp Utility
        2. sqlcmd Utility
        3. sqlservr Utility
        4. DBCC Command
      4. Policy-Based Management
        1. Key Terms and Concepts
        2. Using Policy-Based Management
      5. Summary
      6. Exercises
    2. Chapter 16. Backup, Recovery, and System Availability
      1. Reasons for Data Loss
      2. Introduction to Backup Methods
        1. Full Database Backup
        2. Differential Backup
        3. Transaction Log Backup
        4. File or Filegroup Backup
      3. Performing Database Backup
        1. Backing Up Using Transact-SQL Statements
        2. Backing Up Using Management Studio
        3. Determining Which Databases to Back Up
      4. Performing Database Recovery
        1. Automatic Recovery
        2. Manual Recovery
        3. Recovery Models
      5. System Availability
        1. Using a Standby Server
        2. Using RAID Technology
        3. Database Mirroring
        4. Failover Clustering
        5. Log Shipping
        6. High-Availability and Disaster Recovery (HADR)
      6. Maintenance Plan Wizard
      7. Summary
      8. Exercises
    3. Chapter 17. Automating System Administration Tasks
      1. Starting SQL Server Agent
      2. Creating Jobs and Operators
        1. Creating a Job and Its Steps
        2. Creating a Job Schedule
        3. Notifying Operators About the Job Status
        4. Viewing the Job History Log
      3. Alerts
        1. Error Messages
        2. SQL Server Agent Error Log
        3. Windows Application Log
        4. Defining Alerts to Handle Errors
      4. Summary
      5. Exercises
    4. Chapter 18. Data Replication
      1. Distributed Data and Methods for Distributing
      2. SQL Server Replication: An Overview
        1. Publishers, Distributors, and Subscribers
        2. Publications and Articles
        3. The Distribution Database
        4. Agents
        5. Replication Types
        6. Replication Models
      3. Managing Replication
        1. Configuring the Distribution and Publication Servers
        2. Setting Up Publications
        3. Configuring Subscription Servers
      4. Summary
      5. Exercises
    5. Chapter 19. Query Optimizer
      1. Phases of Query Processing
      2. How Query Optimization Works
        1. Query Analysis
        2. Index Selection
        3. Join Order Selection
        4. Join Processing Techniques
        5. Plan Caching
      3. Tools for Editing the Optimizer Strategy
        1. SET Statement
        2. Management Studio and Graphical Execution Plans
        3. Examples of Execution Plans
        4. Dynamic Management Views and Query Optimizer
      4. Optimization Hints
        1. Why Use Optimization Hints
        2. Types of Optimization Hints
      5. Summary
    6. Chapter 20. Performance Tuning
      1. Factors That Affect Performance
        1. Database Applications and Performance
        2. The Database Engine and Performance
        3. System Resources and Performance
      2. Monitoring Performance
        1. Performance Monitor: An Overview
        2. Monitoring the CPU
        3. Monitoring Memory
        4. Monitoring the Disk System
        5. Monitoring the Network Interface
      3. Choosing the Right Tool for Monitoring
        1. SQL Server Profiler
        2. Database Engine Tuning Advisor
      4. Other Performance Tools of SQL Server
        1. Performance Data Collector
        2. Resource Governor
      5. Summary
      6. Exercises
  13. Part IV: SQL Server and Business Intelligence
    1. Chapter 21. Business Intelligence: An Introduction
      1. Online Transaction Processing vs. Business Intelligence
        1. Online Transaction Processing
        2. Business Intelligence Systems
      2. Data Warehouses and Data Marts
      3. Data Warehouse Design
      4. Cubes and Their Architectures
        1. Aggregation
        2. Physical Storage of a Cube
      5. Data Access
      6. Summary
      7. Exercises
    2. Chapter 22. SQL Server Analysis Services
      1. SSAS Terminology
      2. Developing a Multidimensional Cube Using BIDS
        1. Create a BI Project
        2. Identify Data Sources
        3. Specify Data Source Views
        4. Create a Cube
        5. Design Storage Aggregation
        6. Process the Cube
        7. Browse the Cube
      3. Retrieving and Delivering Data
        1. Querying Data Using PowerPivot for Excel
        2. Querying Data Using Multidimensional Expressions
      4. Security of SQL Server Analysis Services
      5. Summary
      6. Exercises
    3. Chapter 23. Business Intelligence and Transact-SQL
      1. Window Construct
        1. Partitioning
        2. Ordering and Framing
      2. Extensions of GROUP BY
        1. CUBE Operator
        2. ROLLUP Operator
        3. Grouping Functions
        4. Grouping Sets
      3. OLAP Query Functions
        1. Ranking Functions
        2. Statistical Aggregate Functions
      4. Standard and Nonstandard Analytic Functions
        1. TOP Clause
        2. OFFSET/FETCH
        3. NTILE Function
        4. Pivoting Data
      5. Summary
      6. Exercises
    4. Chapter 24. SQL Server Reporting Services
      1. Introduction to Data Reports
      2. SQL Server Reporting Services Architecture
        1. Reporting Services Windows Service
        2. The Report Catalog
        3. Report Manager
      3. Configuration of SQL Server Reporting Services
      4. Creating Reports
        1. Creating Reports with the Report Server Project Wizard
        2. Creating Parameterized Reports
      5. Managing Reports
        1. On-Demand Reports
        2. Report Subscription
        3. Report Delivery Options
      6. Summary
      7. Exercises
    5. Chapter 25. Optimizing Techniques for Relational Online Analytical Processing
      1. Data Partitioning
        1. How the Database Engine Partitions Data
        2. Steps for Creating Partitioned Tables
        3. Partitioning Techniques for Increasing System Performance
        4. Guidelines for Partitioning Tables and Indices
      2. Star Join Optimization
      3. Columnstore Index
        1. Managing Columnstore Index
        2. Advantages and Limitations of Columnstore Indices
      4. Summary
  14. Part V: Beyond Relational Data
    1. Chapter 26. SQL Server and XML
      1. XML: Basic Concepts
        1. Requirements of a Well-Formed XML Document
        2. XML Elements
        3. XML Attributes
        4. XML Namespaces
        5. XML and World Wide Web
        6. XML-Related Languages
      2. Schema Languages
        1. Document Type Definition
        2. XML Schema
      3. Storing XML Documents in SQL Server
        1. Storing XML Documents Using the XML Data Type
        2. Storing XML Documents Using Decomposition
      4. Presenting Data
        1. Presenting XML Documents as Relational Data
        2. Presenting Relational Data as XML Documents
      5. Querying Data
      6. Summary
    2. Chapter 27. Spatial Data
      1. Introduction
        1. Models for Representing Spatial Data
        2. GEOMETRY Data Type
        3. GEOGRAPHY Data Type
        4. GEOMETRY vs. GEOGRAPHY
        5. External Data Formats
      2. Working with Spatial Data Types
        1. Working with the GEOMETRY Data Type
        2. Working with the GEOGRAPHY Data Type
        3. Working with Spatial Indices
      3. Displaying Information Concerning Spatial Data
      4. New Spatial Data Features in SQL Server 2012
        1. New Subtypes of Circular Arcs
        2. New Spatial Indices
        3. New System Stored Procedures Concerning Spatial Data
      5. Summary
    3. Chapter 28. SQL Server Full-Text Search
      1. Introduction
        1. Tokens, Word Breakers, and Stop Lists
        2. Operations on Tokens
        3. Relevance Score
        4. How SQL Server FTS Works
      2. Indexing Full-Text Data
        1. Indexing Full-Text Data Using Transact-SQL
        2. Index Full-Text Data Using SQL Server Management Studio
      3. Querying Full-Text Data
        1. FREETEXT Predicate
        2. CONTAINS Predicate
        3. FREETEXTTABLE Function
        4. CONTAINSTABLE Function
      4. Troubleshooting Full-Text Data
      5. New Features in SQL Server 2012 FTS
        1. Customizing a Proximity Search
        2. Searching Extended Properties
      6. Summary
  15. Index

Product information

  • Title: Microsoft SQL Server 2012 A Beginners Guide 5/E, 5th Edition
  • Author(s): Dusan Petkovic
  • Release date: November 2011
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071761598