Bumps in the Road to Certification

I went through both of the study guides I mentioned in my previous post and looked up all the answers but a few of them threw me for a loop.  I'm going to go over a few of the head scratchers and show what I found.

Disable/Enable Unique

This one might seem simple but I have never disabled a unique constraint before.  After digging for a while I found that is was simpler than I thought.

ALTER TABLE testtable NOCHECK CONSTRAINT UN_test

This works for other constraints as well.  To enable you just take the no out.

ALTER TABLE testtable CHECK CONSTRAINT UN_test

I was expecting the syntax to be tailored to UNIQUE itself so this one threw me for a loop.

Split Merged table into two, Merge back together

 I got confused about this one because I thought it was referring to the MERGE command.  After realizing that this was in a group about partitioned tables, I found this nice little tutorial.  Basically the first thing you have to do is create a partition function.

CREATE PARTITION FUNCTION PFPartition(DataType)
AS
RANGE LEFT FOR VALUES (DATA)

Then alter the partition using the function.

ALTER PARTITION FUNCTION [PFPartition]() SPLIT RANGE (testrange)

ALTER PARTITION FUNCTION [PFPartition]() MERGE RANGE (testrange)

I work in a pretty small instance of SQL Server (Less than 100GB) so I don't use partitioned tables but this was still interesting to learn.  This also seems like something a DBA should take care of but I guess that line between Dev and DBA is becoming fuzzier as time goes on.

 Explore Security on Triggers (EXECUTE AS)

I'm not really sure on this one but I think the author means to use it in the syntax of the trigger itself.  Here is an example I came up with but if you think I'm incorrect on this please leave a comment.

 CREATE TRIGGER tr_test
ON Person.Address
FOR INSERT
AS
    EXECUTE AS LOGIN = 'sa'
   
    SELECT * FROM INSERTED;

So basically before the select statement runs in this trigger, I designate the user I want it to run as.  Like I said, I'm not 100% sure on this answer but it seems pretty logical.

 Others

Here are few ones that require a little more study on there own and I may post in the future about them specifically.

• Use Powershell and SMO (SQL Server Managment Objects) to create a .NET cmdlet
•  I have not used Powershell at all but I do occasionally help our web developer to create .NET applications so SMO would be good to know.  I found a tutorial on it here.
• Service Broker
• Working at a smaller IT shop I really don't have a need for this service but I did run into a good tutorial on MSDN 
• Run through DTA (Database Engine Tuning Advisor)
• I usually just play with queries until they work correctly, but this tool might save me some time.  I found a tutorial on MSDN. 
• Review Catalog Views
• I use the systems table quite a bit but usually only sys.objects and sys.columns.  This tutorial gives a good explanation and  shows all the information schema views.

 Well I'm going to get back to studying but if you have any feedback please post in the comments.  Good luck with your studying!