book

SQL Server 2014 Development Essentials

Name: SQL Server 2014 Development Essentials
Author: Basit A. Masood-Al-Farooq
ISBN: 9781782172550

by Basit A. Masood-Al-Farooq

July 2014

Intermediate to advanced

214 pages

4h 47m

English

Packt Publishing

Read now

Unlock full access

SQL Server 2014 Development Essentials
Table of Contents
SQL Server 2014 Development Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and moreWhy subscribe?Free access for Packt account holdersInstant updates on new Packt books
Preface
What this book covers
What you need for this book

Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example codeErrataPiracyQuestions
1. Microsoft SQL Server Database Design Principles
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
Table design
TablesEntitiesAttributes
Relationships
A one-to-one relationshipA one-to-many relationshipA many-to-many relationship
Data integrity
The basics of data normalization
The normal formsThe first normal form (1NF)The second normal form (2NF)The third normal form (3NF)Denormalization
The SQL Server database architecture
PagesExtentsThe transaction log file architectureThe operation and workings of a transaction logFilegroups
The importance of choosing the appropriate data type
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
Summary
2. Understanding DDL and DCL Statements in SQL Server
Understanding the DDL, DCL, and DML language elementsData Definition Language (DDL) statementsData Manipulation Language (DML) statementsData Control Language (DCL) statements
Understanding the purpose of SQL Server 2014 system databases
SQL Server 2014 system databasesThe master databaseThe model databaseThe msdb databaseThe tempdb databaseThe resource databaseThe distribution database
An overview of database recovery models
The simple recovery modelThe bulk-logged recovery modelFull recovery
Creating and modifying databases
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
Creating and managing database schemas
Managing schemas using T-SQL DDL statementsManaging schemas using SSMS 2014
Creating and managing tables
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
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
Summary
3. Data Retrieval Using Transact-SQL Statements
Understanding Transact-SQL SELECT, FROM, and WHERE clausesThe SELECT statementThe FROM clauseThe WHERE clause
Using T-SQL functions in the query
Aggregate functionsConfiguration functionsCursor functionsDate and time functionsMathematical functionsMetadata functionsRowset functionsSecurity functionsString functionsSystem statistical functions
Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs
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
Subqueries
Examples of subqueries
Common Table Expressions
Organizing and grouping data
The ORDER BY clauseThe GROUP BY clauseThe HAVING clauseThe TOP clauseThe DISTINCT clausePivoting and unpivoting data
Using the Transact-SQL analytic window functions
Ranking functionsPERCENT RANKCUME_DISTPERCENTILE_CONT and PERCENTILE_DISCLEAD and LAGFIRST_VALUE and LAST_VALUE
Summary
4. Data Modification with SQL Server Transact-SQL Statements
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
Updating data in SQL Server database tables
The UPDATE statement examplesExample 1 – updating a single rowExample 2 – updating multiple rows
Deleting data from SQL Server database tables
The DELETE statement examplesExample 1 – deleting a single rowExample 2 – deleting all rows
Using the MERGE statement
The MERGE statement examples
The TRUNCATE TABLE statement
The SELECT INTO statement
Summary
5. Understanding Advanced Database Programming Objects and Error Handling
Creating and using variablesCreating a local variableCreating the cursor variableCreating the table variable
Control-of-flow keywords
BEGIN…END keywordsThe IF…ELSE expressionA CASE statementWHILE, BREAK, and CONTINUE statementsRETURN, GOTO, and WAITFOR statements
Creating and using views
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 and using stored procedures
Creating a stored procedureModifying a stored procedureDropping a stored procedureViewing stored proceduresExecuting stored procedures
Creating and using user-defined functions
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
Creating and using triggers
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
Handling Transact-SQL errors
An example of TRY...CATCHAn example of TRY...CATCH with THROWAn example of TRY...CATCH with RAISERROR
Summary
6. Performance Basics
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 SQL Server 2014 in-memory OLTP engine
The limitations of memory-optimized tables
Indexes
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
Query optimization statistics
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
The fundamentals of transactions
Transaction modesImplementing transactionsBEGIN TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTIONSAVE TRANSACTIONAn overview of lockingBasic locksOptimistic and pessimistic lockingTransaction isolation
SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance
Activity MonitorThe SQLServer:Locks performance objectDynamic Management ViewsSQL Server ProfilerThe sp_who and sp_who2 system stored proceduresSQL Server Extended Events
Summary
Index

Content preview from SQL Server 2014 Development Essentials

Creating and using triggers

A trigger is a special type of stored procedure that fires (executes) in response to an event. We typically use triggers to maintain data integrity rules that are too complicated to implement through constraints and referential integrity. We also use triggers to:

Implement referential actions, such as cascading deletes
Maintain an audit trail of changes
Perform administrative tasks such as auditing and regulating database operations
Compare data before and after modification
Implement custom error messages

Triggers cannot support parameters and should not return values or result sets.

Microsoft SQL Server 2014 has two basic trigger types: DML triggers and DDL triggers.

Note

You can write DDL and DML triggers as Transact-SQL ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781782172550

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Server 2014 Development Essentials

by Basit A. Masood-Al-Farooq

Creating and using triggers

Note

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.