You are previewing Microsoft® SQL Server® 2012 Step by Step.
O'Reilly logo
Microsoft® SQL Server® 2012 Step by Step

Book Description

Your hands-on, step-by-step guide to building applications with Microsoft SQL Server 2012

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Discover how to:

  • Install and work with core components and tools

  • Create tables and index structures

  • Manipulate and retrieve data

  • Secure, manage, back up, and recover databases

  • Apply techniques for building high-performing applications

  • Use clustering, database mirroring, and log shipping

  • Table of Contents

    1. Dedication
    2. Special Upgrade Offer
    3. A Note Regarding Supplemental Files
    4. Introduction
      1. Who should read this book
      2. Who should not read this book
      3. Organization of this book
      4. Conventions and features in this book
      5. System requirements
      6. Code samples
        1. Installing the code samples
        2. Using the code samples
      7. Acknowledgments
      8. Errata & book support
      9. We want to hear from you
      10. Stay in touch
    5. I. Getting started with Microsoft SQL Server 2012
      1. 1. Overview of Microsoft SQL Server 2012
        1. Business intelligence
        2. Database Engine
        3. T-SQL programming interface
        4. Security subsystem
        5. Replication
          1. SQL Server Agent
          2. High Availability and Disaster Recovery Tools
          3. SQL Server Integration Services
          4. SQL Server Management Tools
        6. Summary
      2. 2. Installing, Configuring, and Upgrading Microsoft SQL Server 2012
        1. Editions of SQL Server 2012
        2. Choosing hardware for SQL Server
        3. Software prerequisites
        4. Before installation
          1. SQL Server instances
          2. Service accounts
          3. Collation sequences
          4. Authentication modes
        5. Installing SQL Server
          1. Installing SQL Server from the Setup Wizard
        6. After installation
          1. Assigning a TCP/IP port number to the SQL Server Database Engine
          2. Opening a SQL Server instance port using Windows Firewall
        7. How to upgrade to SQL Server 2012
          1. In-place upgrade
          2. Side-by-side upgrade
        8. Summary
      3. 3. Using SQL Server 2012 administration and development tools
        1. Using SQL Server Books Online
        2. Using SQL Server Management Studio
        3. Using SQL Server Management Studio to create solutions and projects
        4. Using SQL Server Data Tools
        5. Using SQL Server Configuration Manager
        6. Summary
    6. II. Designing databases
      1. 4. Designing SQL Server databases
        1. Understanding SQL Server system databases
          1. master database
          2. tempdb database
          3. model database
          4. msdb database
          5. resource database
          6. distribution database
        2. Understanding the SQL Server database structure
          1. Creating a database
          2. Understanding arguments
        3. Adding files and filegroups
        4. Detaching and attaching SQL Server databases
        5. Understanding database recovery models
          1. Simple model
          2. Full model
          3. Bulk-logged model
        6. Summary
      2. 5. Creating your first table
        1. Developing a naming standard
        2. Understanding schemas
        3. Understanding SQL Server data types
          1. Numeric data types
          2. String data types
          3. Date and time data types
          4. Other data types
        4. Understanding column properties
        5. Creating tables
        6. Altering tables
        7. Understanding computed columns
        8. Adding constraints to a table
          1. Primary key constraints
          2. Default constraints
          3. Unique constraints
          4. Check constraints
          5. Foreign key constraints
        9. Understanding the FileTable
        10. Creating database diagrams
        11. Summary
      3. 6. Building and maintaining indexes
        1. Index structure overview
          1. Clustered index structure
          2. Nonclustered index structure
          3. Columnstore index structure
        2. Adding index options
        3. Adding included columns
        4. Adding filters to indexes
        5. Placing indexes
        6. Disabling and dropping indexes
        7. Summary
    7. III. Advanced database design topics
      1. 7. Table compression
        1. Understanding row compression
          1. Row compression with T-SQL
        2. Understanding page compression
          1. Page compression with T-SQL
        3. Estimating effects of compression
        4. Compression considerations
        5. Summary
      2. 8. Table partitioning
        1. Creating a partition function
          1. Using partition function arguments
        2. Creating a partition scheme
          1. Specifying partition scheme arguments
        3. Partitioning tables and indexes
        4. Summary
      3. 9. Database snapshots
        1. Understanding database snapshot prerequisites and limitations
        2. Creating and viewing database snapshots
        3. Dropping database snapshots
        4. Reverting to a database snapshot
        5. Summary
      4. 10. The SELECT statement
        1. Writing a SELECT statement
        2. Sorting results
        3. Filtering data with the WHERE clause
          1. Using comparison operators
          2. Using the BETWEEN operator
          3. Using the WHERE clause with multiple conditions
          4. Searching for a list of values
          5. Using a wildcard search
        4. Creating aliases
        5. Using the JOIN operator to return data from multiple tables
          1. Using INNER JOIN
            1. The JOIN syntax
          2. Using OUTER JOINs
        6. Limiting the data returned in your result set
          1. Using TOP
          2. Using DISTINCT and NULL
        7. Using UNION to combine result sets
        8. Summary
    8. IV. Using Transact-SQL (T-SQL)
      1. 11. Advanced data retrieval topics
        1. Pivoting and unpivoting data
          1. Using the PIVOT operator
          2. Using the UNPIVOT operator
        2. Paging data
        3. Writing expressions
        4. Using variables
        5. Summary
      2. 12. Modifying data
        1. Inserting data into SQL Server tables
          1. Using the INSERT INTO statement
        2. Using sequence numbers
          1. Using the SELECT INTO statement
        3. Updating data in tables
          1. Updating rows while referencing multiple tables
        4. Deleting data from tables
        5. Merging data
        6. Returning output data
        7. Summary
      3. 13. Built-in scalar functions
        1. Using date and time functions
          1. Deriving dates from parts
          2. Differencing, modifying, and validating date values
        2. Using conversion functions
          1. New SQL Server 2012 conversion functions
        3. Using string functions
        4. Using logical functions
        5. Summary
    9. V. Creating other database objects
      1. 14. Advanced T-SQL topics
        1. Aggregating, windowing, and grouping
          1. Performing aggregations
          2. Performing aggregations with groupings
        2. Windowing
          1. New T-SQL windowing features
          2. Using the <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>HAVING</em></span> clause clause
        3. Using SQL Server temporary objects
          1. Common table expressions
          2. Table variables
          3. Temporary tables
        4. Handling T-SQL errors
        5. Controlling flow keywords
          1. BEGIN...END
          2. IF...ELSE
          3. WHILE
        6. Summary
      2. 15. Views
        1. What are views?
        2. Creating indexed views
          1. Referenced table requirements
          2. Indexed view requirements
        3. Summary
      3. 16. User-defined functions
        1. Understanding user-defined scalar functions
          1. Parameterizing functions
          2. Executing scalar functions
            1. Calling scalar functions inline
            2. Calling scalar functions using the <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="emphasis"><em>EXECUTE</em></span> keyword keyword
        2. Understanding table-valued functions
          1. Using table-valued functions
        3. Limitations of functions
        4. Summary
      4. 17. Stored procedures
        1. Working with stored procedures
        2. Using the EXECUTE keyword
        3. Parameterizing stored procedures
        4. Dropping stored procedures
        5. Summary
      5. 18. Data manipulation triggers
        1. Types of triggers
        2. Creating triggers
        3. Altering triggers
        4. Dropping triggers
        5. Enabling and disabling triggers
        6. Summary
    10. VI. SQL Server replication
      1. 19. Replication
        1. Types of replication
          1. Snapshot
          2. Transactional
          3. Merge
        2. Replication agents
          1. Snapshot Agent
          2. Distribution Agent
          3. Log Reader Agent
          4. Merge Agent
        3. Configuring replication
        4. Monitoring replication
        5. Summary
    11. VII. Database maintenance
      1. 20. Backups
        1. Understanding backup devices
        2. Full database backups
        3. Differential database backups
        4. Transaction log database backups
        5. Restoring databases
        6. Summary
      2. 21. Managing and maintaining indexes and statistics
        1. Checking index fragmentation
        2. Defragmenting indexes
          1. Reorganizing indexes
          2. Rebuilding indexes
          3. Checking index usage
        3. Creating and updating database statistics
          1. Viewing database statistic options
          2. Updating database statistics
        4. Summary
      3. 22. Maintenance plans
        1. Performing database consistency checks
        2. Creating maintenance plans
          1. Using the Maintenance Plan Wizard
        3. Summary
    12. VIII. Database management
      1. 23. SQL Server Profiler
        1. Understanding SQL Server Profiler
          1. Typical uses of SQL Server Profiler
        2. Creating traces
          1. Filtering a trace
          2. Creating trace templates
        3. Running server-side traces
        4. Summary
      2. 24. Extended Events
        1. Understanding the Extended Events architecture
        2. Creating and configuring an Extended Events session
        3. Using an Extended Events session to monitor system performance
        4. Summary
      3. 25. SQL Server security
        1. Understanding principals
        2. Creating server logins
        3. Creating user-defined server roles
        4. Creating database users
        5. Creating built-in database roles
        6. Configuring contained databases
          1. Creating a contained user
          2. Benefits and limitations of contained databases
        7. Summary
      4. 26. Resource Governor
        1. Enabling and disabling Resource Governor
        2. Creating resource pools
        3. Creating a workload group
        4. Using classifier functions
        5. Testing classifier functions
        6. Modifying Resource Governor configurations
        7. Summary
      5. 27. SQL Server Agent
        1. SQL Server Agent components
        2. Viewing SQL Server Agent Configuration Manager options
        3. Configuring SQL Server Agent properties
        4. Creating operators
        5. Configuring alerts
        6. Configuring jobs
        7. Creating proxies
        8. Summary
      6. 28. Database Mail
        1. Database Mail components
        2. Configuring Database Mail
        3. Sending email using Database Mail
        4. Monitoring Database Mail
        5. Summary
      7. 29. Data definition triggers
        1. Types of triggers
        2. Creating triggers
        3. Altering triggers
        4. Enabling and disabling triggers
        5. Dropping triggers
        6. Summary
      8. 30. Dynamic management objects
        1. Retrieving SQL Server metadata from DMOs
        2. Querying server performance statistics
        3. Querying server information
          1. sys.dm_server_services
          2. sys.dm_os_volume_stats
          3. sys.dm_os_sys_memory
          4. sys.dm_exec_requests and sys.dm_exec_sessions
          5. sys.dm_exec_sql_text
        4. Querying performance information
          1. sys.dm_exec_query_stats
        5. Querying indexing statistics
          1. sys.dm_db_index_usage_stats
          2. sys.dm_db_index_physical_stats
        6. Summary
    13. IX. High-availability solutions
      1. 31. AlwaysOn
        1. Failover cluster instance improvements
          1. Multisubnet clustering
          2. Robust failure detection
          3. tempdb on local drive
        2. Creating AlwaysOn Availability Groups
          1. Windows Server Failover Clustering
          2. Enabling AlwaysOn
          3. Creating availability groups
        3. Administering AlwaysOn Availability Groups
        4. Reading secondary database copies
        5. Summary
      2. 32. Log shipping
        1. Log shipping components
        2. Log shipping prerequisites
        3. Configuring log shipping
        4. Summary
      3. A. About the Author
    14. Index
    15. About the Author
    16. Special Upgrade Offer
    17. Copyright