SQL 2008 DB Dev 70-433 XML Data – 6th objective Code Crackin #26

Hey Now Everybody,

This is a review of the sixth objective on the Microsoft exam 70-433 ‘Working with XML Data’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

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

Chapter 7.1 Key Concepts
XML can be generated using SELECT in four different modes:
For XML Raw
, For XML Auto
, For XML PATH
, For XML Explicit
For XML Path is typically the preferred mode used to generate XML
XML Data type can be either untyped or typed (validated by an XML schema collection)
Untyped XML data type all values are always interpreted as strings
We can use value, query, exist, nodes & modify methods to query & alter XML data types
Code
Query in ASP.NET Membership database querying aspnet_users table

 /****** Script for SelectTopNRows command from SSMS ******/  
SELECT [UserId]      
     ,[UserName]    
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    
FOR XML AUTO, ELEMENTS    

Here are some of the results:

Here is another example using For XML Path

/****** Script for SelectTopNRows command from SSMS ******/   
SELECT [UserId] as uid    
  ,[UserName] as "info/@userName"    
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    
FOR XML PATH('K8Users');    

Here is a sample node from the results:

Example of SHOWPLAN XML to show execution plan

/****** Script for SelectTopNRows command from SSMS ******/   
SET SHOWPLAN_XML ON;    
GO    
SELECT [UserId] as uid    
 ,[UserName] as "info/@userName"
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    
FOR XML PATH('K8Users');     
Go    
SET SHOWPLAN_XML OFF; 

Review Questions Concepts:

Create XML Schema Collection imports schema components to database

For XML Path determines the shape of resulting XML

Below are the skills measured in detail:

orking 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

Next Up section four ‘Additional Query Techniques’.
That is all there will be more,

As always all comments welcome,

Catto

SQL 2008 DB Dev 70-433 Query Fundamentals – 3rd objective Code Crackin #25

Hey Now Everybody,

This is a review of the second objective on the Microsoft exam 70-433 ‘Query Fundamentals’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

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

3.`1 Query Data by using Select statements c1.2

Select Statement can be used to retrieve data from tables & views.
Select results sets can be filtered by adding a WHERE clause
Select results sets can be sorted by usinge the ORDER BY
Aliases & string literals can be used to manipulate & format result sets.

3.2 Modify Data by using Insert, Update & Delete statements c2.1
The Join clause allows us to retrieve columns from related tables
JOIN types include INNER< LEFT OUTER, RIGHT OUTER, FULL OUTER & CROSS
Join operators can combine 2 or more tables
Tables can be joined to itself by defining different aliases for each table
3.3 Return data by using the OUTPUT clause c2.2
The OUTPUT clause allows us to redirect info to the calling app, or to an object such as a table, about the insert, update or delete statement
3.4 Modify data by using MERGE statement c2.2
MERGE statement allows us to perform DML actions on a table based on matches found on a source table
3.5 Implement aggregate queries (LINQ) c1.3
Aggregate functions perform calculations on expressions
Use the GROUP BY clause when aggregates should be applied based on the data in rows not the whole table
Include all columns listed in a SELECT WHERE or ORDER by clause in the GROUP By clause
ROLLUP & CUBE to provide summary info
Use the GROUPING function to show which rows holds summary data provided by the rollup & cube operators
Grouping Sets to provide improvements to our Group by queries
3.6 Combine datasets c1.4
UNION operator combines result sets from 2 or more Select statements
EXCEPT operator returns rows that are in the left Select statements
Intersect operator returns only rows that are shared by the two select operators
The APPLY operator uses the results from a query as input to apply a function to each row in the results.
Outer apply returns all rows from the outer table along with the results returned by the function when rows match.
CROSS Apply returns only the rows from the outer tables which matches the function results.
3.7 Apply built in scalar functions c1.5
Built in functions to provide more meaningful results sets
date & time functions to return date info
String functions to format or return info about string expressions.

Code:

 
/****** Script for SelectTopNRows command from SSMS ******/   
SELECT TOP 000 [ApplicationId]     
    ,[UserId]    
    ,[UserName]    
    ,[LoweredUserName]    
    ,[MobileAlias]    
    ,[IsAnonymous]    
    ,[LastActivityDate]    
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]    

INSERT

INSERT  INTO dbo.aspnet_Membership    
 ( ApplicationId
    , UserId 
    , Password
    , PasswordSalt
    , Email
     )    
VALUES ( @ApplicationId 
    , @UserId
    , @Password 
    , @PasswordSalt 
    , @Email 
    ) 
 

Update

UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId

Order by

SELECT TOP (200) ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate
FROM dbo.aspnet_Users
ORDER BY UserName

Question Concepts
LEFT JOIN specifies that all rows from the left table are returned whether they meet the join criteria or not. If the join criteria are not met, output columns that correspond to the right table are set to NULL.

The OUTPUT INTO clause is a simple way to insert deleted rows into another new table. A temporary table can be populated
CONVERT(char, GETDATE(), 102) retrieves the current date with the GETDATE() function & converts it to ANSI format.
The MERGE statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a JOIN with a source table.
The SELECT INTO statement creates a new table & populates it with the result set of the SELECT statement.

AVG is a built-in aggregate function that calculates the average value for a column. Tyou
The LEN() function returns the number of characters in a string expression

Here are the skills measured in detail:

Working with Query Fundamentals (21 percent)

• Query data by using SELECT statements.
  LIKE
  , WHERE
  , ORDER BY
  , INTO
• Modify data by using INSERT, UPDATEUPDATE, 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

Next Up section four ‘Additional Query Techniques’.
That is all there will be more,

As always all comments welcome,

Catto

SQL 2008 DB Dev 70-433 2nd objective Programming Objects Code Crackin #24

Hey Now Everybody,

This is a review of the second objective on the Microsoft exam 70-433 ‘Programming Objects’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

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

Book Chapters Concepts

2.1 Stored Procedures

A stored procedure is a batch of T-SQL code that has a name & stored in db
We can pass parameters to a proc either by name or by position. We can also return data from procs using output params.
We can use the EXECUTE AS clause to cause a proc to execute under a specific security context
Cursors allow us to process data on a row by row basis. However they may not be the most efficient
Try .. Catch blocks provide error handling.

2.2 UDF’s User-Defined Functions
We can create scalar functions, inline table-valued functions & multi –statement table-valued functions.
The function body must be encloded w/in a Begin END block with the exception of inline table-valued functions
Return statement terminates all functions
Functions are not allowed to change the stae of a db or a SQL Server instance.

2.3 DML Triggers – execute when we Add, modify, or remove rows
2.4 DDL Triggers
Triggers are sps that automatically execute in response to DDL or DML events
We can create 3 types of triggers DML, DDL & logon triggers
DML execute when an Insert, update or delete statement occurs
DDL triggers execute when a DDL statement for which the trigger is coded for occurs.
Logon triggers execute when there is a logon attempt
We can access the Interted & deleted tables with a DML Trigger
We can access the XML document provided by the EVENTDATA function w/in a DDL or logon trigger.

2.5 CLR-Based Objects C7.2
SQLCLR must be enabled on the SQL Server Interface when using user-defined objects based on SQLCLR
Objects for development using SQLCLR are UDFs & user-defined aggregates
If we create UDTs based on SQLCLR make sure we test
Filestream can be used when the relevant data mostly involves storing streams larger than a meg (1MB)

2.6 Error Handling
Try Catch blocks
2.7 Manage Transactions

Code –

Stored Procedure Example

USE [ccatto_aspnetdb]  
GO /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 05/11/2010 21:01:07 ******/   
SET ANSI_NULLS ON    
GO 
SET QUOTED_IDENTIFIER< OFF
    
GO    
ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]    
@ApplicationName  nvarchar(256)
    , @UserName nvarchar(256)
    , @RoleName nvarchar(256)    
AS    
BEGIN    
DECLARE @ApplicationId uniqueidentifier    
SELECT @ApplicationId = NULL    
SELECT @ApplicationId = ApplicationId 
FROM aspnet_Applications 
WHERE LOWER(@ApplicationName) = LoweredApplicationName     
IF (@ApplicationId IS NULL)    

   RETURN(2)    
DECLARE @UserId uniqueidentifier    
SELECT @UserId = NULL    
DECLARE  @RoleId uniqueidentifier    
SELECT @RoleId = NULL    
   SELECT @UserId  = UserId     
   FROM dbo.aspnet_Users    
   WHERE LoweredUserName  = LOWER(@UserName) 
   AND ApplicationId = @ApplicationId     
IF (@UserId IS NULL) 
    RETURN(2)    
SELECT @RoleId = RoleId     
FROM dbo.aspnet_Roles    
WHERE LoweredRoleName = LOWER(@RoleName) 
    AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)  
RETURN(3)    
IF (EXISTS( 
    SELECT *
    FROM dbo.aspnet_UsersInRoles 
    WHERE UserId = @UserId 
        AND RoleId = @RoleId ))    
      RETURN(1)    
ELSE    
   RETURN(0)    
END  

Function Generic Example

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO

Trigger Generic example from MSDN library

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO

A few Questions Concepts:

1 Instead of Delete Trigger

2 Create a DDL Trigger to populate a table

3 Rollback & Commit

BEGIN TRANSACTION;
DECLARE @success int;
EXEC @success = spName;
IF @success = 0
ROLLBACK;
ELSE
COMMIT;

4 Alter a trigger –

5 A user-defined function (UDF) can be used directly within a SELECT statement.

6 create a UDF with the SCHEMABINDING option

7 Using Try Catch Blocks

Next Up section three Query Fundamentals.
That is all there will be more,

As always all comments welcome,

Catto

DB Dev 70-433 Implementing Tables & Views Code Crackin #23

Hey Now Everybody,

This is a review of the first objective on the Microsoft exam 70-433 ‘Implementing Tables & View’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

Objective: Implementing Tables & Views

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

During this series I plan on using the ASP.NET membership database for many of the examples. The reason for choosing this db is since it’s a public database & used in real world apps more so than the sample db’s such as northwind or AdventureWorks.

1. Implementing Tables & Views (14 percent)
1.1 Create & Alter Tables 3.1

Creating tables is not just defining columns. We have to choose data types correctly & implement data integrity.
Data types & how they behave is an important skill so we can use them correctly.
Data integrity is important to protect your data.

1.2 Create & Alter Views c5.4

A view is a select statement stored in the db
Views return a single result & cannot use temp tables
We can update data though a view
If a view doesn’t meet requirements for data alters, we can create an INSTEAD OF trigger to process the data modification instead.
A portioned view can be created by using a UNION ALL on two tables
Distributed partitioned views use linked servers to combine multiple tables across servers.
We can create a unique, clustered index on a view to improved performance.

1.3 Create & Alter Indexes 6.2

Indexes typically help read performance but hurt writing performance
Indexed views can increase performance.
It’s important to determine which columns to put the index key.
Analyze which indexes are being used so we can drop ones that aren’t will save storage space.

1.4 Create & modify Constraints c3.2

Implement constraints to verify data integrity
Implement constraints to support the optimizer

1.5 Implement Data Types C3.1 & 8.1
Attribute that specifies the type of data.

1.6 Implement partitioning solutions c6.2

CODE examples in a test database named dbTomato

Let’s check out a script for creating a table such as the simple aspnet_users

Use dbTomato 
CREATE TABLE [dbo].[aspnet_Users]
(
[ApplicationId] [uniqueidentifier] NOT NULL
, [UserId] [uniqueidentifier] NOT NULL
, [UserName] [nvarchar](256) NOT NULL
, [LoweredUserName] [nvarchar](256) NOT NULL
, [MobileAlias] [nvarchar](16) NULL
, [IsAnonymous] [bit] NOT NULL
, [LastActivityDate] [datetime] NOT NULL
)

Create two new data types Code example:

Create two new data types Code example:

 01 
 
Use dbTomato
02 CREATE TYPE dbName.NAME FROM NVARCHAR(60);
03  
04 CREATE TYPE dbName.CURRENCYVALUE FROM DECIMAL(12,5);
  
Create View 
  
create view v_aspnet_Users_anonymous 
05 as  
06 SELECT UserId, UserName, IsAnonymous 
07 FROM dbo.aspnet_Users
08 WHERE (IsAnonymous = 1)
09 GO
  

Clustered indexes & non-clustered indexes differ.
10 ALTER TABLE dbTomato.aspnet_User
11 ADD CONSTRAINT PKUserID
12 PRIMARY KEY NONCLUSTERED (UserID);

Code to Modify existing table this example alters the aspnet_Users table by changing the datatype to navarchar(17) & allow not null:

ALTER TABLE aspnet_Users
13 ALTER COLUMN MobileAlias nvarchar(17) NOT NULL;
14 Constraints, a real constraint from the aspnet_Users table is below:
15 ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (0) FOR [IsAnonymous]
16

---------------------------------------

GO

Question Concepts Examples

1.1 When we have a field that we want to be unique such as two rows not having the same value we can right click in the design view of the table on the column & add a key with the Is Unique property set to True.

1.2 Adding a foreign key constraint

1.3 Alter table to use less space

1.3.1 Changing the data type of the Volume column from bigint to int has the potential to truncate data, but the conversion is valid

1.3.2 Nothing in the table declaration prevents it from being compressed; therefore, REBUILD WITH (DATA_COMPRESSION = ROW) is a valid alteration.

1.4 Storing videos Microsoft SQL Server 2008 introduces the FILESTREAM storage attribute for binary data stored in a varbinary(max) column, which stores binary data on the local file system rather than in the database file. This optimizes read performance for large binary objects, which makes it the best option for the application being developed.

1.5 Minimize execution time --: Indexing the monthlySalary column of the view minimizes the execution time of the GROUP BY clause of the statement in question. To create an index on a view, it must also include a unique clustered index.

1.6 Prevent users from coping a view The WITH ENCRYPTION option of the CREATE VIEW command encrypts the view definition in sys.syscomments so that it cannot be viewed by anyone, even the view's owner

1.7 A row is deleted from a table w/ an identity column . to reinsert the data we : SET IDENTITY_INSERT Products ON; allows explicit values to be entered into an identity column for the remainder of the current session or until it is turned off again.

1.8 The correct syntax for creating this alias data type is CREATE TYPE salary FROM decimal (8). The data type should be based on the decimal type rather than the float type because the values included do not exceed 10 million and a decimal column with a precision of 8 uses less storage space than does a float column

1.9 Decrease time it takes statement to execute: CREATE INDEX expertise_index ON Contractors (expertise) WHERE lastUpdated > '20080101'; is the best choice because it limits the index to only the rows relevant to the statement in question, which minimizes both the size of the index and the time to search the index.

1.10 Grant persmissions The view grants users the access they need while denying them access to any other portions of the database. It also provides flexibility for the users to work with the data as they see fit.

1.11 Add column to same table on multiple servers we create a server group

1.12 Improve perf by partitioning table

1.13 Reduce storage: Setting FILLFACTOR to 70 leaves 30 percent of the space on each leaf-level page empty, allowing for future growth and reducing page splits.

1.14 Xml file data -- > create a table

1.15 Improve select speed Add the PERSISTED option to the Profitability column. The PERSISTED option increases performance when a calculated column is retrieved at the expense of performance when the column is inserted or altered. This is achieved by performing the calculation when the data is entered and storing the result in the database. When the Profitability column is not PERSISTED, the nested CASE statements must be parsed each time the column is queried. Because retrieving calculated data is faster than calculating both CASE statements, making the Profitability column PERSISTED results in the largest performance gain.

1.16 Insert row with view & where clause

1.17 Transfer data from partition

1.18 Altering table not success due to WITH SCHEMABINDING When a view is created with the WITH SCHEMABINDING option, none of the rows used in the view can be altered without dropping or altering the view first.

Next up the other objectives in the exam!

That is all there will be more,

As always all comments welcome,

Catto

SQL2008 Database Dev 70-433 Skills Measured Code Crackin #22

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.

Catto

MCPD 4 How to Study & Take Beta Exam for Free - Code Crackin #21

Hey Now Everybody,

Do you think Microsoft exams & certifications are a good use of time or a waste?

On Friday April 30th 2010 I took the MCPD 4 Web Dev 71-519 beta exam for free. First I heard about the beta exam by an RSS feed, then I called Prometric, registered for two exams the technical specialist & pro web dev exams. Then I had a date set & location for each exam. I prepared by reviewing the skills measured & content in the MSDN library. The previous exam for the .NET 3.5 framework is what I used as for a guide since there wasn’t much public content about the new exam.

Beta exams are really great since they are free & if you pass you get the cert. What I really enjoyed about the exams is by having a date with the exam scheduled I created a plan to study & it motivated me. It’s something to work for. I spent about 3 weeks preparing for each exam. Next week a local user group I enjoy is going to have a meeting on an exam review for another exam I’m interested in which is the SQL Server 2008 Developer exam 70-433. So now I’ve been preparing for that exam too. The content is good to study & will only help improve development skills.

Now again I ask you, Are MS Exams good to take? Is the content good to study? What do you think?

As Always all comments welcome,

That is all there will be more,

Catto