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.

 

New features in SQL Server 2008

Backup compression (COMPRESSION keyword)

Change data tracking: Track insert/update/deletes to tables

pass tables/table variables as parameters

Filestream

Sparse columns (organize/compress NULLs)

transparent data encryption

Hot-swap of memory/CPU

performance data warehousing

resource governor

query plan freezing

policy-based management

MERGE statement to synchronize tables

spatial data types

IIS not required for SSRS

 

Objects

DML = Data Manipulation Language

DDL = Data Definition Language

Useful system views: Sys. ... tables, columns, databases, constraints, views, procedures, indexes, triggers, objects

Object Types and abbreviations:

C = CHECK constraints

D = Default constraint

F = Foreign Key

FN = scalar function (SQL)

FS = scalar function (CLR)

IT = Internal table (not user-accessible - see MSDN for more info.)

P = Proc

S = System table

SQ = service queue

TF = Table-valued function

TR = Trigger

U = User table

UQ =Unique constraint

V = view

Computed columns: don't store a value, but store a formula to calculate a value - can include functions but not subqueries

PERSISTED computed columns: physically store the results of the formula (must be deterministic)

PERSISTED computed columns can be indexed

Foreign Key options ON UPDATE/ON DELETE:

No action (violation = roll back)

CASCADE

Set Null = Set all children to NULL

Set Default

Creating a FK with NOCHECK : doesn't verify existing data - not trusted

 

Index Options

PAD_INDEX: Leaves free space in each page for inserts

FILLFACTOR: Determines how much free space to leave

SORT_IN_TEMPDB

IGNORE_DUP_KEY: ignore dupe errors when creating a unique index.  Only useful when merging data on test/reporting servers.

ONLINE: Leave underlying table available to users

ALLOW_ROW_LOCKS

ALLOW_PAGE_LOCKS

MAXDOP

DATA_COMPRESSION

 

Full-Text Indexes - FREETEXT: search on meaning, not just character matching

Partitioning

Horizontal: Split a table into smaller tables by separating rows

vertical: map columns of one table into several subtables

Views

Updateable: update data in underlying tables

Indexed views: must be schemabound

partitioned view: data from multiple sources presented in one view

Functions WITH "RETURNS NULL ON NULL INPUT": if any parameters are null, will return null without executing function

 

Transactions and concurrency

Dirty Read: Process reads data that is being updated and may be rolled back, making the read invalid.

Phantom read: row read by process that is being deleted

nonrepeatable read: process gets different values because of a transaction updating it elsewhere

READ UNCOMMITTED: Statements can read rows that are being modified

READ COMMITTED: can't read modified but uncommitted data

REPEATABLE READ: other transactions can't change data read by first transaction until transaction is finished

snapshot: other transactions get a snapshot of data prior to first transaction

serializible: repeatable read + transactions can't insert new rows into data range being read by first transaction

 

Triggers: DML, DDL, Logon

DML: After, Instead OF

INSERTED, DELETED: Available only to trigger logic

Trigger Order: @order, @Stmttype (Ie. UPDATE)

 

CLR (Common language runtime)

- Define DB objects using any .NET framework language

CLR can retrieve web service info, AD, registry

managed code = code access security

XSPS are unmanaged and written in C++

SP_Configure 'clr enabled', 1

Base class library: system.IO, system.Data, many more

Permission sets:

SAFE - no external access

EXTERNAL_Access -some access like network services, dns

UNSAFE - same as External_access, but can also call unmanaged code directly

assembly code runs as sql service account

TRUSTWORTHY: Specifies whether SQL objects can access resources outside the database when running as the SQL Service

 

ERROR HANDLINH

sp_addmessage: add error messages to sys.messages

sp_dropmessage, sp_Altermessage

Severity: 1-10 Info, 11-16 User, 17-19 Software, 20-25 System

SQL Server does not use 1-9, they are for user-defined messages

RAISERROR can take arguments and display DBName, user, etc

WITH LOG: Log to application log; NOWAIT: deliver message immediately; SETERROR sets @@ERROR to error ID instead of success/failure

 

TRY CATCH >> @@ERROR

Error_Number(), Error_Severity(), Error_State(), ERROR_Procedure(), Error_Line(), Error_Message()

 

TRANSACTIONS

MSDTC coordinates multi-instance transactions issues with BEGIN DISTRIBUTED TRANSACTION

WITH MARK: Tag a description to a transaction - can be restored to this point using name

XACT_STATE: State of a transaction

1: transaction is running that can be committed

0: no transaction

-1: transaction running that must be rolled back

XACT_ABORT: any error in a transaction will roll it back (default: non-critical errors continue)

SAVE points can be issues within transactions, but not in distributed transactions

 

LOCKS

Basic: Shared (s), Update (U), Exclusive

Extended: Intent (I), Schema (Sch), Bulk Update (BU), Key range (KR)

Shared: During reading; other shared locks can be established; no updates

Update: Taken prior to Exclusive when modifying data

Exclusive: blocks all access except with NOLOCK hint or in read uncommited TL

Intent Shared, Intent Exclusive, Shared with Intent, Intent Update, Shared Intent Update, Update Intent Exclusive

Sys.dm_Tran_Locks, SP_LOCK

Sch-M: Schema Mod Locks

Schema Stability: only blocks schema locks

Bulk Update Lock: Blocks all non-bulk updates

Key Range: Protects a set of records during SERIALIZIBLE Transactions only

Force locks: SP_GETAPPLOCK, SP_RELEASEAPPLOCK

Deadlocks: Trace flags 1204 and 1222 output deadlock info to ERRORLOG

SET_DEADLOCK_PRIORITY: -10 to 10

blocking: check sys.dm_exec_requests DMV, sys.dm_exec_sql_text

@@TRANCOUNT: Number of open transactions within your session

 

DML

INSERT <TableName> TOP X % [WITH TIES] Select Y from OtherTable

WiTH TIES: Result set may contain more than top X

Update Statement: .WRITE: Insert text directly into an existing field at an offset

.WRITE (new_text, @offset, @length)

starting at @offset, @length number of characters are replaced with new_text

--> Only Varchar(MAX), Nvarchar(MAX), Varbinary(MAX)

Delete: DELETE TOP N

MERGE DML statement: new in SQL 2008

for table synchronization

MERGE INTO <Table>

Using <Table2>

WHEN MATCHED

WHEN NOT MATCHED BY TARGET (records that don't exist in source)

WHEN NOT MATCHED BY SOURCE (records that don't exist in target)

 

IDENTITY functions: @@IDENTITY (current session), SCOPE_IDENTITY (session and scope), IDENT_CURRENT (no scope/session limitations)

DBCC CHECKIDENT: can reset seeds

MERGE with OUTPUT: use $action variable

NONLOGGED DML operations: TRUNCATE TABLE, WRITETEXT/UPDATETEXT, SELECT INTO

 

Linked Servers

Provider: OLE DB Data source

Product Name: Relevant text field, if applicable

Data Source: FileName if relevant, like XX.mdb in access

Provider String

Impersonate: Use mapped user's login/pwd

Not be made: users must be mapped

no security context: for providers without security

login's current context: like impersonate but login must be exact same account

"this security context": specify

sys.Linked_logins, sys.servers, sys.remote_logins

 

DTC: Distributed transaction coordinator handles two-phase commit

OPENDATASOURCE: specify server in the command

OPENQUERY: uses an existing linked server

OPENROWSET: specify server in command

 

New Data Types

Date: Date Only field

Time: time-only  field

Datetimeoffset: time-zone aware date/time

datetime2: 24-hour clock datetime

HierarchyID - position in a hierarchy

Geometry - planar data

Geography - elliptical data

 

CAST: ansi compliant

CONVERT: More flexible

LEN: Length

RIGHT, LEFT, REPLACE

Charindex: locate an expression within another expression

Patindex: starting position of first occurrence of a pattern in an expression

Computed columns

FILESTREAM: VarBinary(MAX) columns, stored on NTFS file system but integrated into SQL Server backup/restore operations


CTEs: Require a semicolon terminator, can be recursive (MAXRECURSION option prevents infinite recursion) - usually for presenting hierarchical information

Ranking Functions:

IDENTITY(): Number rows; no functionality

ROW_NUMBER: assign a number to each column; can differ from the order by ordering and include a GROUP BY

ROW_NUMBER() OVER (Order by X) as rowNumber

RANK: values for rows will be the same if values are the same (1, 1, 1, 4, 4, 4)

DENSE_RANK: no gaps (1, 1, 1, 2, 2, 2)

NTILE: Subdivide into groups

Query Hints (OPTION)

MAXRECURSION

FAST: Return X rows quickly

FORCE ORDER: Keep join order

KEEP PLAN, KEEP FIXED PLAN, RECOMPILE

GROUP, UNION, JOIN

USE PLAN

Table Hints:

WITH INDEX/FORCESEEK

NOWAIT, READPAST

NOLOCK, ROWLOCK, PAGLOCK, TABLOCK

TABLOCKX, UPDLOCK, XLOCK

<Transaction levels>

 

Query Governor: Prevent queries from costing over a certain  threshold

Server Properties--> connections or SP_Configure

Session level: SET QUERY_Governor_Cost_Limit

 

Resource governor:

classifier function: a UDF that defines what workload groups a session should belong to.  Can include stuff like HOST_NAME(), SUSER_NAME(), etc.  to determine which workload group to assign a session.

workload group: a policy that is allocated resources from a resource pool

resource pool: controls memory/cpu allocations

 

Sparse Columns: consume no data for NULL but more for Non-Null.  64% NULL is break-even point for INT

Work with all sparse columns on a table as a set: Add XML column as COLUMN_SET FOR ALL_SPARSE_COLUMNS

 

Compression: ROW and PAGE - only on Enterprise, Eval, DEV editions

ROW: treat all columns are variable-length  --> Not that intensive on CPU

PAGE: identifies repeating patterns on each page - compression information dictionary referenced by queries

sp_estimate_data_compresssion_savings

 

XML

XML Auto, Explicit, RAW

OPENXML

SP_XML_PREPAREDOCUMENT

XQuery (Case sensitive)

Query(): return XML that matches a query

Value(): get a value from XML document

Exist(): See if value exists in XML document

Nodes(): relationalize XML data into rows

Modify(): Insert/Update/Delete DML inside XML

 

Performance

Re-compliation triggers: STATISTICS modifications, SP_RECOMPILE/WITH RECOMPILE, ALTER TABLE/ALTER VIEW, index modifications, mass inserts/deletes

Execution Plans: Display Estimated Execution Plan

Include Actual Execution Plan

SHOWPLAN_TEXT : text plan