SQL Server 2014 Development Essentials

SQL Server 2014 Development Essentials

by Basit A. Masood-Al-Farooq
Released July 2014
Publisher(s): Packt Publishing
ISBN: 9781782172550

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

Design, implement, and deliver a successful database solution with Microsoft SQL Server 2014

In Detail

SQL Server 2014 Development Essentials is an easy-to-follow yet comprehensive guide that is full of hands-on examples. With Microsoft SQL Server 2014, you can design, build, and deploy mission-critical database applications. The variety of new in-memory features enable you to design high performance database applications that can improve the performance of your applications, making them as much as ten times more efficient in some cases.

Whether you are thinking about becoming a database developer, architect, or administrator, or you are a seasoned database expert, this book will provide you with all the skills you need to successfully create, design, and deploy databases using SQL Server 2014. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how SQL Server works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

What You Will Learn

  • Get introduced to SQL Server 2014's new in-memory database engine
  • Understand SQL Server database architecture and relational database design
  • Use joins, subqueries, CTEs, and windowing functions to write advanced Transact-SQL queries
  • Learn about tools that let you monitor SQL Server database performance
  • Identify and troubleshoot blocks or deadlocks that might slow down a system
  • Design, create, and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers
  • Use SQL Server 2014's structured error handling blocks to handle errors that occur in the Transact-SQL batches and programmable objects

Table of contents

  1. SQL Server 2014 Development Essentials
    1. Table of Contents
    2. SQL Server 2014 Development Essentials
    3. Credits
    4. About the Author
    5. Acknowledgments
    6. About the Reviewers
    7. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
        3. Instant updates on new Packt books
    8. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    9. 1. Microsoft SQL Server Database Design Principles
      1. Database design
        1. The requirement collection and analysis phase
        2. The conceptual design phase
        3. The logical design phase
        4. The physical design phase
        5. The implementation and loading phase
        6. The testing and evaluation phase
        7. The database design life cycle recap
      2. Table design
        1. Tables
        2. Entities
        3. Attributes
      3. Relationships
        1. A one-to-one relationship
        2. A one-to-many relationship
        3. A many-to-many relationship
      4. Data integrity
      5. The basics of data normalization
        1. The normal forms
          1. The first normal form (1NF)
          2. The second normal form (2NF)
          3. The third normal form (3NF)
        2. Denormalization
      6. The SQL Server database architecture
        1. Pages
        2. Extents
        3. The transaction log file architecture
          1. The operation and workings of a transaction log
        4. Filegroups
      7. The importance of choosing the appropriate data type
        1. SQL Server 2014 system data types
        2. Alias data types
          1. Creating and dropping alias data types with SSMS 2014
          2. Creating and dropping alias data types using the Transact-SQL DDL statement
            1. Creating an alias data type using CREATE TYPE
            2. Dropping an alias data type using DROP TYPE
        3. CLR user-defined types
      8. Summary
    10. 2. Understanding DDL and DCL Statements in SQL Server
      1. Understanding the DDL, DCL, and DML language elements
        1. Data Definition Language (DDL) statements
        2. Data Manipulation Language (DML) statements
        3. Data Control Language (DCL) statements
      2. Understanding the purpose of SQL Server 2014 system databases
        1. SQL Server 2014 system databases
          1. The master database
          2. The model database
          3. The msdb database
          4. The tempdb database
          5. The resource database
          6. The distribution database
      3. An overview of database recovery models
        1. The simple recovery model
        2. The bulk-logged recovery model
        3. Full recovery
      4. Creating and modifying databases
        1. Create, modify, and drop databases with T-SQL DDL statements
          1. Creating a database with T-SQL DDL statements
          2. Example 1 – creating a database based on a model database
          3. Example 2 – creating a database that explicitly specifies the database data and the transaction log file's filespecs properties
          4. Example 3 – creating a database on multiple filegroups
        2. Modifying a database with T-SQL DDL statements
          1. Example – adding a secondary data file to an existing database
        3. Dropping a database with T-SQL DDL statements
        4. Create, modify, and drop databases with SSMS 2014
          1. Creating a database with SSMS 2014
          2. Modifying a database with SSMS 2014
        5. Dropping a database with SSMS 2014
      5. Creating and managing database schemas
        1. Managing schemas using T-SQL DDL statements
        2. Managing schemas using SSMS 2014
      6. Creating and managing tables
        1. Creating and modifying tables
        2. Creating and modifying tables with T-SQL DDL statements
          1. Creating a table with T-SQL DDL statements
          2. Modifying a table with T-SQL DDL statements
          3. Dropping a table with T-SQL DDL statements
        3. Creating and modifying tables with SSMS 2014
          1. Creating a table with SSMS 2014
          2. Modifying a table with SSMS 2014
          3. Deleting a table with SSMS 2014
      7. Grant, deny, and revoke permissions to securables
        1. Grant, deny, and revoke permissions to securables with T-SQL DCL statements
          1. Granting permissions to securables with T-SQL DCL statements
          2. Denying permissions to securables with T-SQL DCL statements
          3. Revoking permissions to securables with T-SQL DCL statements
        2. Managing permissions using SSMS 2014
      8. Summary
    11. 3. Data Retrieval Using Transact-SQL Statements
      1. Understanding Transact-SQL SELECT, FROM, and WHERE clauses
        1. The SELECT statement
        2. The FROM clause
        3. The WHERE clause
      2. Using T-SQL functions in the query
        1. Aggregate functions
        2. Configuration functions
        3. Cursor functions
        4. Date and time functions
        5. Mathematical functions
        6. Metadata functions
        7. Rowset functions
        8. Security functions
        9. String functions
        10. System statistical functions
      3. Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs
        1. The UNION operator
        2. The EXCEPT operator
        3. The INTERSECT operator
        4. The JOIN operator
          1. Using INNER JOIN
          2. Using outer joins
            1. Using LEFT OUTER JOIN
            2. Using RIGHT OUTER JOIN
            3. Using FULL OUTER JOIN
            4. Using CROSS JOIN
            5. Using self joins
      4. Subqueries
        1. Examples of subqueries
      5. Common Table Expressions
      6. Organizing and grouping data
        1. The ORDER BY clause
        2. The GROUP BY clause
          1. The HAVING clause
        3. The TOP clause
        4. The DISTINCT clause
        5. Pivoting and unpivoting data
      7. Using the Transact-SQL analytic window functions
        1. Ranking functions
        2. PERCENT RANK
        3. CUME_DIST
        4. PERCENTILE_CONT and PERCENTILE_DISC
        5. LEAD and LAG
        6. FIRST_VALUE and LAST_VALUE
      8. Summary
    12. 4. Data Modification with SQL Server Transact-SQL Statements
      1. Inserting data into SQL Server database tables
        1. The INSERT examples
          1. Example 1 – insert a single row into a SQL Server database table
          2. Example 2 – INSERT with the SELECT statement
          3. Example 3 – INSERT with the EXEC statement
          4. Example 4 – explicitly inserting data into the IDENTITY column
      2. Updating data in SQL Server database tables
        1. The UPDATE statement examples
          1. Example 1 – updating a single row
          2. Example 2 – updating multiple rows
      3. Deleting data from SQL Server database tables
        1. The DELETE statement examples
          1. Example 1 – deleting a single row
          2. Example 2 – deleting all rows
      4. Using the MERGE statement
        1. The MERGE statement examples
      5. The TRUNCATE TABLE statement
      6. The SELECT INTO statement
      7. Summary
    13. 5. Understanding Advanced Database Programming Objects and Error Handling
      1. Creating and using variables
        1. Creating a local variable
        2. Creating the cursor variable
        3. Creating the table variable
      2. Control-of-flow keywords
        1. BEGIN…END keywords
        2. The IF…ELSE expression
        3. A CASE statement
        4. WHILE, BREAK, and CONTINUE statements
        5. RETURN, GOTO, and WAITFOR statements
      3. Creating and using views
        1. Creating views with Transact-SQL and SSMS 2014
          1. Creating, altering, and dropping views with Transact-SQL DDL statements
            1. The CREATE VIEW statement
            2. The ALTER VIEW statement
            3. The DROP VIEW statement
          2. Creating, altering, and dropping views with SSMS 2014
            1. Creating views with SSMS 2014
            2. Altering and dropping views with SSMS 2014
          3. Indexed views
            1. Indexed view example
      4. Creating and using stored procedures
        1. Creating a stored procedure
        2. Modifying a stored procedure
        3. Dropping a stored procedure
        4. Viewing stored procedures
        5. Executing stored procedures
      5. Creating and using user-defined functions
        1. Creating user-defined functions
          1. Creating a user-defined scalar function
            1. Using a user-defined scalar function
          2. Creating a user-defined table-valued function
            1. Inline table-valued function example
            2. Multistatement table-valued function example
        2. Modifying user-defined functions
          1. Using a user-defined table-valued function
        3. Dropping user-defined functions
        4. Viewing user-defined functions
      6. Creating and using triggers
        1. Nested triggers
        2. Recursive triggers
        3. DML triggers
          1. Inserted and deleted logical tables
          2. Creating DML triggers
          3. Modifying a DML trigger
          4. Dropping a DML trigger
        4. Data Definition Language (DDL) triggers
          1. The EVENTDATA function
          2. Creating a DDL trigger
          3. Modifying a DDL trigger
          4. Dropping a DDL trigger
        5. Disabling and enabling triggers
        6. Viewing triggers
      7. Handling Transact-SQL errors
        1. An example of TRY...CATCH
        2. An example of TRY...CATCH with THROW
        3. An example of TRY...CATCH with RAISERROR
      8. Summary
    14. 6. Performance Basics
      1. Components of SQL Server Database Engine
        1. The SQL Server Relational Engine architecture
          1. Parsing and binding
          2. Query optimization
          3. Query execution and plan caching
          4. Query plan aging
          5. The improved design in SQL Server 2014 for the cardinality estimation
          6. Optimizing SQL Server for ad hoc workloads
          7. Manually clearing the plan cache
      2. The SQL Server 2014 in-memory OLTP engine
        1. The limitations of memory-optimized tables
      3. Indexes
        1. The cost associated with indexes
        2. How SQL Server uses indexes
          1. Access without an index
          2. Access with an index
        3. The structure of indexes
        4. Index types
          1. Clustered indexes
            1. When should you have a clustered index on a table?
          2. Nonclustered indexes
          3. Single-column indexes
          4. Composite indexes
          5. Covering indexes
          6. Unique indexes
          7. Spatial indexes
          8. Partitioned indexes
          9. Filtered indexes
          10. Full-text indexes
          11. XML indexes
          12. Memory-optimized indexes
          13. Columnstore indexes
            1. The architecture of columnstore indexes
            2. Creating and managing columnstore indexes
        5. Guidelines for designing and optimizing indexes
          1. Avoid overindexing tables
          2. Create a clustered index before creating nonclustered indexes when using clustered indexes
          3. Index columns used in foreign keys
          4. Index columns frequently used in joins
          5. Use composite indexes and covering indexes to give the query optimizer greater flexibility
          6. Limit key columns to columns with a high level of selectability
          7. Pad indexes and specify the fill factor to reduce page splits
          8. Rebuild indexes based on the fragmentation level
      4. Query optimization statistics
        1. Database-wide statistics options in SQL Server to automatically create and update statistics
        2. Manually create and update statistics
        3. Determine the date when the statistics were last updated
          1. Using the DBCC SHOW_STATISTICS command
          2. Using the sys.stats catalog view with the STATS_DATE() function
      5. The fundamentals of transactions
        1. Transaction modes
        2. Implementing transactions
          1. BEGIN TRANSACTION
          2. COMMIT TRANSACTION
          3. ROLLBACK TRANSACTION
          4. SAVE TRANSACTION
        3. An overview of locking
          1. Basic locks
          2. Optimistic and pessimistic locking
          3. Transaction isolation
      6. SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance
        1. Activity Monitor
        2. The SQLServer:Locks performance object
        3. Dynamic Management Views
        4. SQL Server Profiler
        5. The sp_who and sp_who2 system stored procedures
        6. SQL Server Extended Events
      7. Summary
    15. Index

Product information

  • Title: SQL Server 2014 Development Essentials
  • Author(s): Basit A. Masood-Al-Farooq
  • Release date: July 2014
  • Publisher(s): Packt Publishing
  • ISBN: 9781782172550