Course content
Designing a Database Strategy
-
Identify which SQL Server components to use to support business requirements:
SQL Server Agent, DB mail, Service Broker, Full-Text Search, Distributed Transaction Coordinator (DTC), linked servers -
Design a database model:
normalization, entities, entity relationships -
Design a data model by using the Entity Framework:
defining and maintaining mapping (query vs. stored proc), defining a data model, entity SQL -
Design an application strategy to support security:
application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles -
Design a solution by using Service Broker:
designing services, contracts, activation, routes, message types, queues, remote service binding, priorities -
Design a Full-Text Search strategy:
CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE
Designing Database Tables
-
Identify the appropriate usage of new data types:
geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary (max) filestream -
Design tables:
table width, sizing data types, IN_ROW_DATA (BLOBs), overflow data, sparse columns, computed columns, persisted computed columns -
Design data integrity:
primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers
Designing Programming Objects
-
Design T-SQL stored procedures:
execution context (EXECUTE AS), table-valued parameters, determining appropriate way to return data, WITH RECOMPILE/OPTION (RECOMPILE), error handling, TRY/CATCH -
Design views:
common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING -
Design T-SQL table-valued and scalar functions:
inline table-valued functions vs. views, multi-statement table-valued functions, determinism -
Design Common Language Runtime (CLR) table-valued and scalar functions:
assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered -
Design CLR stored procedures, aggregates, and types:
assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered, executing static methods on user-defined types, multi-parameter aggregations -
Evaluate special programming constructs:
prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protecting against SQL injection
Designing a Transaction and Concurrency Strategy
-
Design the locking granularity level:
locking hints, memory consumption -
Design for implicit and explicit transactions:
nested transactions, savepoints, TRY/CATCH -
Design for concurrency:
hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes
Designing an XML Strategy
-
Design XML storage:
determining when to use XML for storage, untyped vs. typed (XML schema collection) -
Design a strategy to query and modify XML data:
when to use appropriate XPath and XQuery expressions, .query vs. .value, XML indexes for performance, typed vs. untyped, .exist, .modify -
Design a query strategy by using FOR XML:
views, FOR XML PATH and EXPLICIT, FOR XML…TYPE -
Design a strategy to transform XML into relational data:
.nodes, .value, .query, XQuery and XPath
Designing Queries for Performance
-
Optimize and tune queries:
optimizer hints, common table expressions (CTEs), search conditions, temporary storage, GROUP BY [GROUPING SETS|CUBE|ROLLUP] -
Analyze execution plans:
execution order, logical and physical operators, join operators, minimize resource costs, compare query costs -
Evaluate the use of row-based operations vs. set-based operations:
row-based logic vs. set-based logic, batching, splitting implicit transactions
Designing a Database for Optimal Performance
-
Optimize indexing strategies:
table-valued function, views, filtered indexes, indexed views, clustered and non-clustered, unique -
Design scalable database solutions:
scale up vs. scale out, federated databases, distributed partitioned views, scalable shared databases, replication, offloading read-only query (database mirroring) -
Resolve performance problems by using plan guides:
object plan guides, SQL plan guides, templates plan guides, dynamic management views -
Design a table and index compression strategy:
row vs. page, update frequency, page compression implementation, compressing individual partitions -
Design a table and index partitioning strategy:
switching partitions, merging, splitting, staging, creating, schemes and functions
Social Media
Live Contact