070-433 Study Notes

MCTS Sql Server 2008

EXAM 070-433

These are my study notes for 070-433.  They're based off the book below - MCTS SQL Server 2008.  It's the best one available.  Go buy it.  Use the link.

Full Exam guide is in progress.

 

Locate all columns in a database matching a specified name

For SQL 2005+.   This finds any columns that have Status somewhere in the column name.


Select SCH.Name as SchemaName, OBJ.Name as ObjectName, COL.Name as ColumnName from Sys.Objects OBJ
Inner join Sys.Schemas SCH on OBJ.Schema_ID = SCH.Schema_ID
Inner join Sys.Columns COL on OBJ.Object_ID = COL.Object_ID
	Where COL.Name like '%Status%'
		Order by SchemaName, ObjectName, ColumnName

 

Order By: Dynamic syntax and other advanced usage

While ORDER BY is one of the most commonly used keywords in SQL server, there are some helpful tricks that are not as well known.

The following is a simplified table for use in the subsequent examples:

SQL Server - Version Information

  
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Select @@Version
 

Audit Transformation

SSIS's Audit Transformation is just a highly specific version of the derived column transformation.  It performs the same function - adding a new column to a data set.  However, it's limited to a few package and task-specific options, namely the following (The internal SSIS variable name is listed after each description):

Execution instance GUID - Insert the GUID that uniquely identifies the execution instance of the package.  Variable = @[System::ExecutionInstanceGUID].

Primary Key and Foreign Key Constraints

Primary Key during table creation:


	CREATE TABLE PKExample (PKCOL INT IDENTITY PRIMARY KEY)
 

Primary Key after table has already been created:


	CREATE TABLE PKExample (PKCOL INT IDENTITY) GO ALTER TABLE PKExample ADD CONSTRAINT PK_CONSTRAINTNAME PRIMARY KEY (PKCOL)

 

Foreign Key during table creation:

Syndicate content