SQL Server 2014 Development Essentials


Book and eBook expected July 2014. Pre-order now!
SQL Server 2014 Development Essentials
eBook: $26.99
Formats: PDF, PacktLib, ePub and Mobi formats
$22.94
save 15%!
Print + free eBook + free PacktLib access to the book: $70.98    Print cover: $43.99
$43.99
save 38%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Overview
Table of Contents
Author
Support
Sample Chapters
  • Discover SQL Server 2014’s new in-memory OLTP engine and performance-related improvements
  • Explore the fundamentals of database planning and the Server Transact-SQL language syntax
  • Gain hands-on experience with the use of scalar and table-valued functions, branching, and more advanced data structures

 

Book Details

Language : English
Paperback : 214 pages [ 235mm x 191mm ]
Release Date : July 2014
ISBN : 1782172556
ISBN 13 : 9781782172550
Author(s) : Basit A. Masood-Al-Farooq
Topics and Technologies : All Books, Enterprise Products and Platforms, Enterprise


Table of Contents

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

Basit A. Masood-Al-Farooq

Basit A. Masood-Al-Farooq is an internationally known Lead SQL DBA, trainer, and technical author with twelve years' experience of the Microsoft technology stack. He is an accomplished development and production SQL Server DBA with a proven record of delivering major projects on time and within budget. He is an expert at evaluating the clients' needs against the capabilities of the SQL Server product set, with the objective of minimizing costs and maximizing functions by making innovative use of advance capabilities. Basit has authored numerous SQL Server technical articles on various SQL Server topics for different SQL Server community sites, which include SQLMag.com, MSSQLTips.com, SQLServerCentral.com, SSWUG.org, SQL-Server-Performance.com, and SearchSQLServer.com.

He has also developed and implemented many successful database infrastructures, data warehouses, and business intelligence projects. He holds a Master's degree in Computer Science from London Metropolitan University and industry-standard certifications from Microsoft, Sun, Cisco, Brainbench, ProSoft, and APM, which include MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications. He also has a good understanding of ITIL principles.

He can be reached via Twitter (@BasitAali), his blog (http://basitaalishan.com), or via LinkedIn (http://uk.linkedin.com/in/basitfarooq).

He was a technical reviewer for SQL Server 2012 Reporting Services Blueprints, Marlon Ribunal and Mickey Stuewe, Packt Publishing and Reporting with Microsoft SQL Server 2012, James Serra and Bill Anton, Packt Publishing.

Sorry, we don't have any reviews for this title yet.

Code Downloads

Download the code and support files for this book.


Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Frequently bought together

SQL Server 2014 Development Essentials +    .NET Compact Framework 3.5 Data Driven Applications =
50% Off
the second eBook
Price for both: €30.40

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

  • 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

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.

Approach

This book is an easy-to-follow, comprehensive guide that is full of hands-on examples, which you can follow to successfully design, build, and deploy mission-critical database applications with SQL Server 2014.

Who this book is for

If you are a database developer, architect, or administrator who wants to learn how to design, implement, and deliver a successful database solution with SQL Server 2014, then this book is for you. Existing users of Microsoft SQL Server will also benefit from this book as they will learn what's new in the latest version.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software