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

Content preview from SQL Server 2014 Development Essentials

Using the Transact-SQL analytic window functions

SQL Server 2014 supports several analytic functions. With the help of these window analytic functions, we can perform common analyses, such as ranking, percentiles, moving averages, and cumulative sums that can be expressed concisely in a single SELECT statement.

Before the advent of analytic functions, the solution for performing complex analytical tasks was to use self joins, correlated subqueries, temporary tables, or some combination of all three. This solution was inefficient and highly resource intensive. Expressing queries with analytic functions simplifies complex tasks by eliminating programming self joins and correlated subqueries. It also uses fewer temporary tables.

In this section, we ...