Microsoft SQL Server 2016: A Beginner's Guide, Sixth Edition, 6th Edition

Microsoft SQL Server 2016: A Beginner's Guide, Sixth Edition, 6th Edition

by Dusan Petkovic
Released October 2016
Publisher(s): McGraw-Hill
ISBN: 9781259641800

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

Up-to-date Microsoft SQL Server 2016 skills made easy!

Get up and running on Microsoft SQL Server 2016 in no time with help from this thoroughly revised, practical resource. The book offers thorough coverage of SQL management and development and features full details on the newest business intelligence, reporting, and security features.

Filled with new real-world examples and hands-on exercises, Microsoft SQL Server 2016: A Beginner's Guide, Sixth Edition, starts by explaining fundamental relational database system concepts. From there, you will learn how to write Transact-SQL statements, execute simple and complex database queries, handle system administration and security, and use the powerful analysis and BI tools. XML, spatial data, and full-text search are also covered in this step-by-step tutorial.

· Revised from the ground up to cover the latest version of SQL Server

· Ideal both as a self-study guide and a classroom textbook

· Written by a prominent professor and best-selling author

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Dedication
  5. About the Author
  6. Contents at a Glance
  7. Contents
  8. Introduction
  9. 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 and Management Components
        1. SQL Server Editions
        2. Management Components
      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
  10. Part II Transact-SQL Language
    1. Chapter 4 SQL Components
      1. SQL’s Basic Objects
        1. Literal Values
        2. Identifiers
        3. Delimiters
        4. Comments
        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. Editing Index Information
        3. Altering Indices
        4. 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
      5. Summary
      6. Exercises
    8. Chapter 11 Views
      1. DDL Statements and Views
        1. Creating a View
        2. Altering and Removing 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
        5. Editing Information Concerning Views
      3. Summary
      4. Exercises
    9. Chapter 12 Security System of the Database Engine
      1. Encrypting Data
        1. Symmetric Keys
        2. Asymmetric Keys
        3. Certificates
        4. Editing User Keys
        5. Extensible Key Management
        6. Methods of Data Encryption
      2. Authentication
        1. Setting Up the Database System Security
      3. Schemas
        1. User-Schema Separation
        2. DDL Schema-Related Statements
        3. Default Database Schemas
      4. Database Security
        1. Managing Database Security Using SQL Server Management Studio
        2. Managing Database Security Using Transact-SQL Statements
      5. Roles
        1. Fixed Server Roles
        2. Fixed Database Roles
        3. Application Roles
        4. User-Defined Server Roles
        5. User-Defined Database Roles
      6. Authorization
        1. GRANT Statement
        2. DENY Statement
        3. REVOKE Statement
        4. Managing Permissions Using SQL Server Management Studio
        5. Managing Authorization and Authentication of Contained Databases
      7. Change Tracking
      8. Data Security and Views
      9. Summary
      10. 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
        2. Snapshot Isolation
      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
  11. 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 SQL Server 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. AlwaysOn
        7. Comparison of High-Availability Components
      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. Agents
        4. The distribution Database
        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. SQL Server Management Studio and Graphical Execution Plans
        3. Examples of Execution Plans
        4. Dynamic Management Views and Query Optimizer
      4. Optimizer Hints
        1. Why Use Optimizer Hints
        2. Types of Optimizer 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 the Database Engine
        1. Query Store
        2. Performance Data Collector
        3. Resource Governor
        4. Live Query Statistics
      5. Summary
      6. Exercises
    7. Chapter 21 In-Memory OLTP
      1. Memory-Optimized Tables
        1. Pinned Tables as Predecessor of Memory-Optimized Tables
        2. Creating Memory-Optimized Tables
      2. Row and Index Storage
        1. Row Storage
        2. Index Storage
      3. In-Memory OLTP and Concurrency Control
        1. Logging Memory-Optimized Objects
        2. Optimistic Multiversion Concurrency Control
      4. Accessing the Content of Memory-Optimized Tables
        1. Interpreted Transact-SQL
        2. Compiled Stored Procedures
      5. Editing Information Concerning In-Memory Objects
        1. Property Functions
        2. Catalog Views
        3. Dynamic Management Views
      6. Tools for In-Memory OLTP
        1. Memory Management for In-Memory OLTP: Overview
        2. Memory Management Tools
        3. Migration Tools for In-Memory OLTP
      7. Summary
  12. Part IV SQL Server and Business Intelligence
    1. Chapter 22 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 23 SQL Server Analysis Services
      1. Multidimensional Model
        1. Multidimensional Model Terminology
        2. Properties of SSDT
        3. Creating and Processing a Multidimensional Cube
        4. Delivering Data from the Multidimensional Model
      2. Tabular Model
        1. Workspace Database
        2. Creating a Tabular Model Solution
        3. Deploying and Processing the Tabular Project
        4. Delivering Data from the Tabular Project
      3. Multidimensional Model vs. Tabular Model
      4. Analysis Services: Data Security
      5. Summary
      6. Exercises
    3. Chapter 24 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 25 SQL Server Reporting Services
      1. Reports: An Introduction
      2. SQL Server Reporting Services Architecture
        1. Reporting Services Windows Service
        2. Report Catalog
      3. Installation and Configuration of Reporting Services
        1. Installation of SSRS
        2. Configuration of SSRS
      4. Creating Reports
        1. Creating Your First Report
        2. Creating a Parameterized Report
        3. Creating a Report Using a Chart
      5. Managing and Tuning Reports
        1. Reporting Services Web Portal
        2. Performance Issues
      6. Reporting Services Security
        1. Authentication
        2. Authorization
      7. Summary
      8. Exercises
    5. Chapter 26 Optimizing Techniques for Data Warehousing
      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. Indexed or Materialized 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
    6. Chapter 27 Columnstore Indices
      1. Benefits of Columnstore Indices
      2. Storage of Columnstore Indices
        1. Index Storage
        2. Compression
      3. Query Plan Execution
      4. Types of Columnstore Indices
        1. Clustered Columnstore Index
        2. Nonclustered Columnstore Index
      5. Summary
      6. Exercises
  13. Part V Beyond Relational Data
    1. Chapter 28 XML and JSON Integration in the Database Engine
      1. XML: An Introduction
        1. XML Elements
        2. XML Attributes
        3. XML Namespaces
        4. XML and World Wide Web
        5. XML-Related Languages
        6. Schema Languages
        7. Document Type Definition
        8. XML Schema
      2. Storing XML Documents in the Database Engine
        1. Storing XML Documents Using the XML Data Type
        2. Storing XML Documents Using Decomposition
      3. Presenting and Querying XML Documents
        1. Presenting XML Documents as Relational Data
        2. Presenting Relational Data as XML Documents
        3. Querying XML Documents
      4. JSON: An Introduction
        1. XML vs. JSON
        2. Why Support JSON in SQL?
      5. Storing JSON Documents in the Database Engine
      6. Presenting and Querying JSON Documents
        1. Presenting JSON Documents as Relational Data
        2. Presenting Relational Data as JSON Documents
        3. Querying JSON Documents
      7. Summary
    2. Chapter 29 Spatial and Temporal Data
      1. Spatial Data
        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. Spatial Indices
        4. Editing Information Concerning Spatial Data
      3. Temporal Tables
        1. Creation of Temporal Tables
        2. Temporal Tables and DML Statements
        3. Querying Temporal Data
        4. Converting Convenient Tables in Temporal Tables
      4. Summary
    3. Chapter 30 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. Indexing 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
        5. Searching Extended Properties
      4. Editing Information Concerning Full-Text Data
      5. Summary
      6. Exercise
  14. Index

Product information

  • Title: Microsoft SQL Server 2016: A Beginner's Guide, Sixth Edition, 6th Edition
  • Author(s): Dusan Petkovic
  • Release date: October 2016
  • Publisher(s): McGraw-Hill
  • ISBN: 9781259641800