Implementing Tables and Views (14 percent) Create and alter tables.
This objective may include but is not limited to: computed and persisted columns; schemas; scripts to deploy changes to multiple environments, for example, dev, test, production; manage permissions (GRANT, DENY, REVOKE) Create and alter views.
This objective may include but is not limited to: WITH ENCRYPTION; WITH SCHEMABINDING; WITH CHECK OPTION; manage permissions (GRANT, DENY, REVOKE) Create and alter indexes.
This objective may include but is not limited to: filtered, included columns, unique, clustered, non-clustered, FILL FACTOR; CREATE STATISTICS; indexing views Create and modify constraints.
This objective may include but is not limited to: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK; SET IDENTITY_INSERT Implement data types.
This objective may include but is not limited to: FILESTREAM; spatial, structured, and semi-structured; collations Implement partitioning solutions.
This objective may include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers)
Implementing Programming Objects (16 percent) Create and alter stored procedures.
This objective may include but is not limited to: table-valued parameters (TVPs), EXECUTE AS, RECOMPILE, parameter direction (output); WITH ENCRYPTION; manage permissions (GRANT, DENY, REVOKE) Create and alter user-defined functions (UDFs).
This objective may include but is not limited to: WITH SCHEMABINDING, EXECUTE AS; manage permissions (GRANT, DENY, REVOKE) Create and alter DML triggers.
This objective may include but is not limited to: INSERTED, DELETED, INSTEAD OF, EXECUTE AS Create and alter DDL triggers.
This objective may include but is not limited to: enabling/disabling; return event data Create and deploy CLR-based objects.
This objective may include but is not limited to: permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS), SET TRUSTWORTHY Implement error handling.
This objective may include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @@ERROR Manage transactions.
This objective may include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL
Working with Query Fundamentals (21 percent) Query data by using SELECT statements.
This objective may include but is not limited to: LIKE, WHERE, ORDER BY, INTO Modify data by using INSERT, UPDATE, and DELETE statements.
This objective may include but is not limited to: row constructors, DELETE FROM FROM, UPDATE FROM, TRUNCATE TABLE Return data by using the OUTPUT clause.
This objective may include but is not limited to: INSERTED, DELETED, INTO Modify data by using MERGE statements.
This objective may include but is not limited to: INSERTED, DELETED, OUTPUT Implement aggregate queries.
This objective may include but is not limited to: built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING Combine datasets.
This objective may include but is not limited to: CROSS APPLY, OUTER APPLY, all join types; UNION, UNION ALL, INTERSECT, EXCEPT Apply built-in scalar functions.
This objective may include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX
Applying Additional Query Techniques (15 percent) Implement subqueries.
This objective may include but is not limited to: simple, correlated, scalar, list, table valued Implement CTE (common table expression) queries.
This objective may include but is not limited to: recursive, non-recursive Apply ranking functions.
This objective may include but is not limited to: RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE Control execution plans.
This objective may include but is not limited to: table hints, query hints Manage international considerations.
This objective may include but is not limited to: collations, defining custom errors, filtering data, sort order, nvarchar, database collation, column collation
Working with Additional SQL Server Components (11 percent) Integrate Database Mail. Implement full-text search.
This objective may include but is not limited to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs).
This objective may include but is not limited to: cmdlets Implement Service Broker solutions.
This objective may include but is not limited to: services, queues, messages, message types, message validation, contracts, activation procedures Track data changes.
This objective may include but is not limited to: change tracking; database audit specification; CHANGETABLE
Working with XML Data (12 percent) Retrieve relational data as XML.
This objective may include but is not limited to: FOR XML Transform XML data into relational data.
This objective may include but is not limited to: OPENXML, sp_xml_preparedocument, sp_xml_removedocument Query XML data.
This objective may include but is not limited to: XQUERY, XPATH Manage XML data.
This objective may include but is not limited to: XML datatype, XML indexes, XML schema collections
Gathering Performance Information (11 percent) Capture execution plans.
This objective may include but is not limited to: graphical execution plans; SHOWPLAN Gather trace information by using the SQL Server Profiler. Collect output from the Database Engine Tuning Advisor.
This objective may include but is not limited to: prepare a workload Collect information from system metadata.
This objective may include but is not limited to: Dynamic Management Views (DMVs), catalog views
|
