book

SQL Server 2014 Development Essentials

by Basit A. Masood-Al-Farooq

July 2014

Intermediate to advanced

214 pages

4h 47m

English

Packt Publishing

Read now

Unlock full access

Support files, eBooks, discount offers, and moreWhy subscribe?Free access for Packt account holdersInstant updates on new Packt books
What this book covers

Downloading the example codeErrataPiracyQuestions
Database designThe requirement collection and analysis phaseThe conceptual design phaseThe logical design phaseThe physical design phaseThe implementation and loading phaseThe testing and evaluation phaseThe database design life cycle recap
TablesEntitiesAttributes
A one-to-one relationshipA one-to-many relationshipA many-to-many relationship
The normal formsThe first normal form (1NF)The second normal form (2NF)The third normal form (3NF)Denormalization
PagesExtentsThe transaction log file architectureThe operation and workings of a transaction logFilegroups
SQL Server 2014 system data typesAlias data typesCreating and dropping alias data types with SSMS 2014Creating and dropping alias data types using the Transact-SQL DDL statementCreating an alias data type using CREATE TYPEDropping an alias data type using DROP TYPECLR user-defined types
Understanding the DDL, DCL, and DML language elementsData Definition Language (DDL) statementsData Manipulation Language (DML) statementsData Control Language (DCL) statements
SQL Server 2014 system databasesThe master databaseThe model databaseThe msdb databaseThe tempdb databaseThe resource databaseThe distribution database
The simple recovery modelThe bulk-logged recovery modelFull recovery
Create, modify, and drop databases with T-SQL DDL statementsCreating a database with T-SQL DDL statementsExample 1 – creating a database based on a model databaseExample 2 – creating a database that explicitly specifies the database data and the transaction log file's filespecs propertiesExample 3 – creating a database on multiple filegroupsModifying a database with T-SQL DDL statementsExample – adding a secondary data file to an existing databaseDropping a database with T-SQL DDL statementsCreate, modify, and drop databases with SSMS 2014Creating a database with SSMS 2014Modifying a database with SSMS 2014Dropping a database with SSMS 2014
Managing schemas using T-SQL DDL statementsManaging schemas using SSMS 2014
Creating and modifying tablesCreating and modifying tables with T-SQL DDL statementsCreating a table with T-SQL DDL statementsModifying a table with T-SQL DDL statementsDropping a table with T-SQL DDL statementsCreating and modifying tables with SSMS 2014Creating a table with SSMS 2014Modifying a table with SSMS 2014Deleting a table with SSMS 2014
Grant, deny, and revoke permissions to securables with T-SQL DCL statementsGranting permissions to securables with T-SQL DCL statementsDenying permissions to securables with T-SQL DCL statementsRevoking permissions to securables with T-SQL DCL statementsManaging permissions using SSMS 2014
Understanding Transact-SQL SELECT, FROM, and WHERE clausesThe SELECT statementThe FROM clauseThe WHERE clause
Aggregate functionsConfiguration functionsCursor functionsDate and time functionsMathematical functionsMetadata functionsRowset functionsSecurity functionsString functionsSystem statistical functions
The UNION operatorThe EXCEPT operatorThe INTERSECT operatorThe JOIN operatorUsing INNER JOINUsing outer joinsUsing LEFT OUTER JOINUsing RIGHT OUTER JOINUsing FULL OUTER JOINUsing CROSS JOINUsing self joins
Examples of subqueries
The ORDER BY clauseThe GROUP BY clauseThe HAVING clauseThe TOP clauseThe DISTINCT clausePivoting and unpivoting data
Ranking functionsPERCENT RANKCUME_DISTPERCENTILE_CONT and PERCENTILE_DISCLEAD and LAGFIRST_VALUE and LAST_VALUE
Inserting data into SQL Server database tablesThe INSERT examplesExample 1 – insert a single row into a SQL Server database tableExample 2 – INSERT with the SELECT statementExample 3 – INSERT with the EXEC statementExample 4 – explicitly inserting data into the IDENTITY column
The UPDATE statement examplesExample 1 – updating a single rowExample 2 – updating multiple rows
The DELETE statement examplesExample 1 – deleting a single rowExample 2 – deleting all rows
The MERGE statement examples
Creating and using variablesCreating a local variableCreating the cursor variableCreating the table variable
BEGIN…END keywordsThe IF…ELSE expressionA CASE statementWHILE, BREAK, and CONTINUE statementsRETURN, GOTO, and WAITFOR statements
Creating views with Transact-SQL and SSMS 2014Creating, altering, and dropping views with Transact-SQL DDL statementsThe CREATE VIEW statementThe ALTER VIEW statementThe DROP VIEW statementCreating, altering, and dropping views with SSMS 2014Creating views with SSMS 2014Altering and dropping views with SSMS 2014Indexed viewsIndexed view example
Creating a stored procedureModifying a stored procedureDropping a stored procedureViewing stored proceduresExecuting stored procedures
Creating user-defined functionsCreating a user-defined scalar functionUsing a user-defined scalar functionCreating a user-defined table-valued functionInline table-valued function exampleMultistatement table-valued function exampleModifying user-defined functionsUsing a user-defined table-valued functionDropping user-defined functionsViewing user-defined functions
Nested triggersRecursive triggersDML triggersInserted and deleted logical tablesCreating DML triggersModifying a DML triggerDropping a DML triggerData Definition Language (DDL) triggersThe EVENTDATA functionCreating a DDL triggerModifying a DDL triggerDropping a DDL triggerDisabling and enabling triggersViewing triggers
An example of TRY...CATCHAn example of TRY...CATCH with THROWAn example of TRY...CATCH with RAISERROR
Components of SQL Server Database EngineThe SQL Server Relational Engine architectureParsing and bindingQuery optimizationQuery execution and plan cachingQuery plan agingThe improved design in SQL Server 2014 for the cardinality estimationOptimizing SQL Server for ad hoc workloadsManually clearing the plan cache
The limitations of memory-optimized tables
The cost associated with indexesHow SQL Server uses indexesAccess without an indexAccess with an indexThe structure of indexesIndex typesClustered indexesWhen should you have a clustered index on a table?Nonclustered indexesSingle-column indexesComposite indexesCovering indexesUnique indexesSpatial indexesPartitioned indexesFiltered indexesFull-text indexesXML indexesMemory-optimized indexesColumnstore indexesThe architecture of columnstore indexesCreating and managing columnstore indexesGuidelines for designing and optimizing indexesAvoid overindexing tablesCreate a clustered index before creating nonclustered indexes when using clustered indexesIndex columns used in foreign keysIndex columns frequently used in joinsUse composite indexes and covering indexes to give the query optimizer greater flexibilityLimit key columns to columns with a high level of selectabilityPad indexes and specify the fill factor to reduce page splitsRebuild indexes based on the fragmentation level
Database-wide statistics options in SQL Server to automatically create and update statisticsManually create and update statisticsDetermine the date when the statistics were last updatedUsing the DBCC SHOW_STATISTICS commandUsing the sys.stats catalog view with the STATS_DATE() function
Transaction modesImplementing transactionsBEGIN TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTIONSAVE TRANSACTIONAn overview of lockingBasic locksOptimistic and pessimistic lockingTransaction isolation
Activity MonitorThe SQLServer:Locks performance objectDynamic Management ViewsSQL Server ProfilerThe sp_who and sp_who2 system stored proceduresSQL Server Extended Events

Overview

This book is your ultimate guide to mastering database development using Microsoft SQL Server 2014. By diving into this hands-on resource, you will explore the essentials of database design, implementation, and deployment to create robust solutions that meet modern enterprise needs.

What this Book will help me do

Gain a deep understanding of SQL Server 2014's new features and enhancements.
Master database design principles for scalable and efficient solutions.
Develop and optimize SQL queries for robust data retrieval and manipulation.
Understand advanced database object topics and effective error handling.
Learn performance optimization techniques for maintaining database efficiency.

Author(s)

None A. Masood-Al-Farooq is a seasoned database professional with extensive experience in SQL Server development and administration. They have worked on numerous critical projects in enterprise data management and have a practical, results-driven approach to database solutions. As an author, they focus on equipping readers with actionable insights and techniques through clear explanations and real-world examples.

Who is it for?

This book is ideal for database developers, administrators, and architects who work with Microsoft SQL Server and wish to expand their expertise in its 2014 version. Beginners to intermediate-level professionals will find it accessible and straightforward, while advanced users can discover new features and optimizations. It caters to anyone looking to design or optimize database solutions effectively. Whether you manage databases or are diving into database software development, this book will enhance your SQL Server 2014 skills.