Microsoft SQL Server
MCITP : Database Developer 2008
exam 70-433 - MCITP certification
Implementing tables and views (14%)
Create and alter tables
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
WITH ENCRYPTION; WITH SCHEMABINDING; WITH CHECK OPTION; manage permissions (GRANT, DENY, REVOKE)
Create and alter indexes
Filtered, included columns, unique, clustered, non-clustered, FILL FACTOR; CREATE STATISTICS; indexing views, compression
Create and modify constraints
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK; SET IDENTITY_INSERT
Implement data types
FILESTREAM; spatial, structured, and semi-structured; collations
Implement partitioning solutions
Partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers)
Implementing programming objects (16%)
Create and alter stored procedures
Table-valued parameters (TVPs), EXECUTE AS, RECOMPILE, parameter direction (output); WITH ENCRYPTION; manage permissions (GRANT, DENY, REVOKE)
Create and alter user-defined functions (UDFs)
WITH SCHEMABINDING, EXECUTE AS; manage permissions (GRANT, DENY, REVOKE)
Create and alter DML triggers
INSERTED, DELETED, INSTEAD OF, EXECUTE AS
Create and alter DDL triggers
Enabling/disabling; return event data
Create and deploy CLR-based objects
Permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS), SET TRUSTWORTHY
Implement error handling
TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @@ERROR
Manage transactions
BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL, named transactions
Working with query fundamentals (21%)
Query data by using SELECT statements
LIKE, WHERE, ORDER BY, INTO
Modify data by using INSERT, UPDATE, and DELETE statements
Row constructors, DELETE FROM, UPDATE FROM, TRUNCATE TABLE
Return data by using the OUTPUT clause
INSERTED, DELETED, INTO
Modify data by using MERGE statements
INSERTED, DELETED, OUTPUT
Implement aggregate queries
Built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING
Combine datasets
CROSS APPLY, OUTER APPLY, all join types; UNION, UNION ALL, INTERSECT, EXCEPT
Apply built-in scalar functions
CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX
Applying additional query techniques (15%)
Implement subqueries
Simple, correlated, scalar, list, table valued
Implement CTE (common table expression) queries
Recursive, non-recursive
Apply ranking functions
RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE
Control execution plans
Table hints, query hints
Manage international considerations
Collations, defining custom errors, filtering data, sort order, nvarchar, database collation, column collation
Working with additional SQL Server components (10%)
Integrate Database Mail
Implement full-text search
CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST
Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs)
cmdlets
Implement Service Broker solutions
Services, queues, messages, message types, message validation, contracts, activation procedures, routes
Track data changes
Change tracking; database audit specification; CHANGETABLE, Change Data capture
Working with XML data (12%)
Retrieve relational data as XML
FOR XML
Transform XML data into relational data
OPENXML, sp_xml_preparedocument, sp_xml_removedocument
Query XML data
XQUERY, XPATH
Manage XML data
XML datatype, XML indexes, XML schema collections
Gathering performance information (11%)
Capture execution plans
Graphical execution plans; SHOWPLAN, index details in Query Plans
Gather trace information by using the SQL Server Profiler
Collect output from the Database Engine Tuning Advisor
Prepare a workload
Collect information from system metadata
Dynamic Management Views (DMVs), catalog views
source – microsoft learning MCITP certification
0 التعليقات:
إرسال تعليق