Hey Now Everybody,
SQL Server Database Development 70-433
Here we are I’m preparing for the SQL Database Dev exam & enjoying reviewing the material. Let’s inspect the skills measured for this exam & read the MSDN library on the topics. This is a good way to start preparing for this exam. Below are the skills measured for the exam along with links to mostly the MSDN library to read more information on each skill:
Official Skills Measured
Implementing Tables and Views (14 percent)
Implementing Programming Objects (16 percent)
Working with Query Fundamentals (21 percent)
Applying Additional Query Techniques (15 percent)
Working with Additional SQL Server Components (11 percent)
Working with XML Data (12 percent)
Gathering Performance Information (11 percent)
70-433 7 Sections & Major Details only
1. Implementing Tables & Views (14 percent)
1.1 Create & Alter Tables 3.1
1.2 Create & Alter Views 5.4
1.3 Create & Alter Indexes 6.2
1.4 Create & modify Constraints 3.2
1.5 Implement Data Types C3.1 & 8.1
1.6 Implement partitioning solutions c6.2
2 Implementing Programming Objects (16 percent)
2.1 Create & Alter Stored Procedures
2.2 Create & Alter User-Defined Functions UDFs
2.3 Create & Alter DML Triggers
2.4 Create & Alter DDL Triggers
2.5 Create & Deploy CLR-Based Objects c7.2
2.6 Implement Error handling c5.1
2.7 Manage Transactions
3 Working with Query Fundamentals (21 percent)
3.`1 Query Data by using Select statements c1.2
3.2 Modify Data by using Insert, Update & Delete statements c2.1
3.3 Return data by using the OUTPUT clause c2.2
3.4 Modify data by using MERGE statement c2.2
3.5 Implement aggregate queries (LINQ) c1.3
3.6 Combine datasets c1.4
3.7 Apply built in scalar functions c1.5
4 Applying Additional Query Techniques (15 percent)
4.1 Implement Subqueries c4.2
4.2 Implement CTE Common Table Expression Queries c4.1
4.3 Apply Ranking Functions c4.3
4.4 Control Execution Plans c6.1
4.5 Manage International Considerations c3.1
5. Working with Additional SQL Server Components (11 percent)
5.1 Intergrate Database Mail c8.1
5.2 Implement Full Text Search c8.2
5.3 Implement Scripts using Powershell & SMOs c9.2
5.4 Implement Service Broker Solutions c8.3
5.5 Track Data Changes LINQ c 9.3
6 Working with XML Data (12 percent)
6.1 Retrieve Relational Data as XML c7.1
6.2 Transform XML data into relational data c7.1
6.3 Query XML data c7.1
6.4 Manage XML data c7.1
7 Gathering Performance Information (11 percent)
7.1 Capture Execution Plans c6.1
7.2 Gather trace info by using the SQL Server Profiler c6.1
7.3 Collect output from the Database Engine Tuning Advisor c6.2
7.4 Collect info from system.metadata c6.1, c6.2
Skills Details
Implementing Tables and Views (14 percent)
- Create and alter tables.
computed and persisted columns
; schemas
; scripts to deploy changes to multiple environments
, 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 - 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 percent)
- 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
Working with Query Fundamentals (21 percent)
- Query data by using SELECT statements.
LIKE
, WHERE
, ORDER BY
, INTO - Modify data by using INSERT, UPDATE, and DELETE statements
. row constructors
, DELETE FROM 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 percent)
- 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 (11 percent)
- 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 - Track data changes.
change tracking
; database audit specification
; CHANGETABLE
Working with XML Data (12 percent)
- 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 percent)
- Capture execution plans.
graphical execution plans
; SHOWPLAN - 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
Whew that is quite a bit of skills measured, glad we went threw them.
As Always All comments welcome
That is all, there will be more.
No comments:
Post a Comment