SQL Server
(Last updated: Sep 24th 2005)

Abbreviations
End To End
 

Services

Service

Description

MSSQLService

Database Engine

SQLServerAgent

Alerts & jobs

MS DTC?

Distributed Transaction Coordinator

msfte

Full Text Engine. Maintains indexes.

End To End

  1. Client sends request through one of several APIs.

  2. API creates TDS (Tabular Data Stream) packets from the request.

  3. Client Net Library wraps the TDS packets in network packets.

  4. Client Net Library sends the network packets through IPC to the server

  5. Sever Net Library receives the network packets, reconstitutes the TDS stream and passes it to the Open Data Services.

  6. Open Data Services extracts the client request from the TDS stream and passes it the Relational Engine.

  7. The Relational Engine compiles the query and generates a optimized execution plan.

  8. The Relation Engine executes the plan, calling features of the Storage Engine as necessary.

  9. The result are returned to the client by reversing the 6 steps.

Transaction Log

  1. Client sends change

  2. Server loads relevant pages from disk (unless they are in the buffer cache already)

  3. Each change is written to the log first and the log is written to the disk

  4. The checkpoint process (running in the background) writes completed transactions to the database

Stored Procedures

System Databases

Database

Description

master

User database definitions, user accounts, configuration settings and the system (sp_) and extended (xp_) stored procedures. Includes the System Catalog (not to be confused with the Database Catalog, which appears in every database and holds metadata about that database)

System Tables in master:

  • syslogins
    One entry per login accounts on this server

  • sysmessages
    One entry per error and warning message this server can return.

  • sysdatabases
    One entry per database on this server

model

Template for new database

tempdb

Temporary tables and working storage

msdb

Scheduling and jobs (?? & alerts ??)

distribution

Replication

 

System Tables in all databases:

  • sysusers
    Account and roles information

  • sysobjects
    One entry for each object in the database

Databases Objects

Object

Description

Data Type

 

Table

 

Constraint

 

Default

 

Rule

 

Index

 

View

 

User-Defined function

 

Stored Procedure

 

Trigger

 

Brain-dead Syntax

Action

Syntax

Create Table

CREATE TABLE tableName (fieldName type, ...)

Help

Help on a database: sp_helpdb [database]
Help on a database object: sp_help [object]
Help on an index: sp_helpindex [index]

CREATE VIEW CREATE VIEW viewName AS sqlSelect [WITH CHECK OPTION]

(WITH CHECK OPTION prevents the addition of rows that would not meet the selection criteria of the view)
SQL Server Views cannot include ORDER BY, or update more than one base table at once.
CREATE PROC[EDURE] CREATE PROCEDURE procName @param datatype [=defaultValue] [OUTPUT], ... AS sqlCode

Calling:
EXEC [@returnInt=]procName @param=value [OUTPUT], ...
CREATE FUNCTION CREATE FUNCTION functionName (@param type ...) RETURNS returnType
BEGIN
    RETURN returnValue
END
Note: returnType cannot be [n]text, image, cursor & timestamp, but can be TABLE (columnName columnType [,...])

CREATE FUNCTION functionName (@param type ...) RETURNS returnType
AS sql

CREATE TRIGGER CREATE TRIGGER triggerName ON tableName
FOR|AFTER|INSTEAD OF INSERT|UPDATE|DELETE
AS sqlStatments

IF UPDATE(columnName) ...

SELECT * FROM Deleted|Inserted

ALTER TABLE tableName [DIS]ABLE TRIGGER triggerName [,...]
ALTER TABLE tableName [DIS]ABLE TRIGGER ALL

BEGIN TRANS[ACTION] transactionName  
WHILE WHILE condition
   [BREAK]
   [CONTINUE]
NULLIF NULLIF(expr1, expr2) -- returns NULL if expr1 = expr2
COALESCE COALESCE(expr1, expr2, ...) -- returns the first non-null value
   

Roles

Cross Database Roles

Rights

bulkadmin BULK INSERT execution rights
dbcreator Create & alter databases
diskadmin Disk Administration
processadmin Process Administration
securityadmin Administer (and audit) logins
serveradmin Server Administration
setupadmin Replication Administration
sysadmin System Administration (do anything)

 

Database Roles

Rights

public Default permissions
db_owner All database roles
db_accessadmin Manage database users, groups and roles
db_ddladmin Execute DDL (including creation of stored procedures)
db_securityadmin Execute DCL
db_backupoperator Backup
db_datareader Read from any table
db_datawriter Write to and delete from any table
db_denydatareader Cannot read from any table
db_denydatawriter Cannot write or delete from any table
user defined  

Command Line

Program

Notes

bcp Bulk copy program (in and out)
isql  
osql ODBC command line connectively:
   use trusted connection (Windows Autherization): -E
   use SQL connection: -U username -P password
  
specific connection: -S servername

Then enter commands like through Query Analyzer (i.e. GO to execute batch)

Jargon

Jargon

Notes

Layer

  • Data Presentation - User I/O, manipulation
  • Application Logic - Business Rules
  • Data Services - Storage, access logic
Two-Tier Client - Data Presentation Layer
Server - Data Services Layer
OLTP Online Transactional Processing. Typically normalized relation data where real-time transactional handling is important. SQL Server supports both OLTP and OLAP databases.
OLAP Online Analytical Processing. Typically large data with duplicated pre-processed results where real-time analysis of existing data is important. OLAP makes extensive use of the MDX syntax.     SQL Server supports both OLTP and OLAP databases.
Relational Engine Parses T-SQL, builds execution plans and enforces security.
Storage Engine Manages and accesses the physical files on disk, including back up, logging and recovery.
FNQ server.database.owner.object
(and yes, it is possible to have objects with the same name in the same database provided the owner is different). Owner can be dbo, which includes everyone in the db_owner and db_ddlamin roles. Owner can be skipped using the double-dot (database..object) syntax.

Datebase.owner.object pretty much maps to the SQL-92 catalog.schema.object.

ADO ActiveX based access to OLE DB.
DCL Data Control Language. Controls permissions (GRANT, DENY, REVOKE). E.g. GRANT SELECT ON someTable TO public

GRANT: Additive
REVOKE: Subtractive
DENY: Absolute (doesn't matter what GRANTs you have)

DDL CREATE, ALTER, DROP
DML SELECT, INSERT, UPDATE, DELETE.
Scalar Function Functions that operate on a single value that return a single value.
Primary Filegroup
(CREATE DATABASE)
The file in a mulit-file database that contains the system tables and all objects not assigned to user filegroups.
Page 8k (although max row size is only 8060 bytes)
Extent 8 pages (64k)
Uniform Extent An Extent which is allocated entirely to the same object.
Row data
  • Header (4 bytes). Offset to end of Fixed data, plus whether there is any Variable data.
  • Fixed data (data from fixed sized columns)
  • Null block (2 bytes to say how many columns followed by a binary bitmap of columns containing nulls)
  • Variable block (2 bytes to say how many variable columns, followed by 2 bytes per column giving the offset into Variable block or that column)
  • Variable data (data from variable sized columns)

If the row contains no Variable data there is no Variable block or Variable data block.

Clustered Index A B-Tree where the leaf node is the data page containing the record (as opposed to a non-Clustered Index where the leaf node is a pointer to the data page).
Clustered Index require the data to be stored in same order as the index. If the data contains the same indexed value then a 4 byte 'uniquifier' value is appended to it.
When a table has both clustered and non-clustered indexes the leaf nodes of the non-clustered index contain the clustered index values for the those record instead of the normal fileNumber:PageNumber:RowNumber pointers.

?? While I can see this speeds up searches that involve multiple indexes and clustered index page splits, hasn't it got to suck for performance when updates to fields in the clustered index occur ??

Ghost Records Deletion doesn't occur immediately (mainly because this would often required record locks that might affect adjacent records). Instead, records are marked for deletion and deleted by a house keeping thread at a more convenient time. Such records are called Ghost Records.
SELECT @.. SELECT @variable = expression
PRINT ... PRINT @myVariable + 'Hello world'
EXISTS  
CASE CASE [expression]
    WHEN matchingValueOrBooleanExpression THEN result
[ELSE defaultResult]
END
EXECUTE EXECUTE(string)
Note: Although string can be concatenated (EXECUTE(@a + @b)), it cannot include function calls (EXECUTE(concatenateThis(@a, @b)).

sp_executesql(string) is more efficient as SQL is more likely to reuse the generated execution place.

FOR XML AUTO Tables become elements and sub-elements, fields become attributes.
Side effect: Reorders columns to group columns from same table together.
FOR XML RAW Rows become elements (with an ID attribute). There are no sub-elements and columns are not reordered.
IDENTITY CREATE TABLE tableName (field int IDENTITY(start, step))
Note: IDENTITY does not force uniqueness
@@identity  
DBCC SHOWCONTIG Fragmentation details for everything. DBCC SHOWCONTIG (tableName [, indexName])
DBCC INDEXDEFRAG DBCC INDEXDEFRAG(database, table, index)
Note: Doesn't help if the index is fragmented at the disk level.
sp_depends sp_depends object
Lists the objects upon which the named object depends.
SET QUERY_GOVERNOR_COST_LIMIT Prevents queries estimated to take longer than this time in seconds from being run. 0 (the default) means no limit.
SET LOCK_TIMEOUT timoutInMs  Sets @@lock_timeout. Defaults to -1 (??no timeout??)
   

Build in Functions

Constraints

Notes

datediff datediff(yy, '9/9/1980', '1/1/2000') returns 20 not 19 (because we're only looking at the datepart specified (year here, and 2000 - 1980 = 20)).
host_name()  
suser_sname()  
user_name()  
CONVERT CONVERT(newtype, value [, style])
CAST  
GetDate() Current Date

Constraints

Constraints

Notes

CHECK CONSTRAINT constraintName CHECK(booleanExpression)
Note: Can reference other columns in the current table.
DEFAULT CONSTRAINT constraintName DEFAULT defaultValue
Note: Can included certain functions such as CURRENT_USER() and CURRENT_TIMESTAMP()
PRIMARY KEY CONSTRAINT constraintName PRIMARY KEY [NON]CLUSTERED (column [, column...])
Side effect: Creates a unique key
UNIQUE CONSTRAINT constraintName UNIQUE [NON]CLUSTERED (column [, column...])
Side effect: Creates a unique key
FOREIGN KEY CONSTRAINT constraintName FOREIGN KEY (localColumn [, localColumn...]) REFERENCES remoteTable(remoteColumn [, remoteColumn...])
Referential ON DELETE|UPDATE CASCADE
ON DELETE|UPDATE NO ACTION

NO ACTION: Operation fails if it would update / delete a column that's that target of another table's foreign key constraint.

CASCADE: Foreign keys pointing to this column are updated automatically.

UDFs

Triggers

Locking

Lock

Notes

Row Data row level lock
Key Index row lock
Page  
Extent  
Table Includes indexes
Database Normally only present during restore operations

Lock Type

Notes

Shared Basic r/o operation lock (SELECT, etc.)
Exclusive Basic write operation lock (UPDATE, DELETE, INSERT, etc.)
Intent (IS, IX, SIX) Prevents locks being made at a higher level. For example: user 1 taking an exclusive lock on table 3 prevents also creates an intent lock (IX?) on the database so that user 2 cannot gain an exclusive lock on the database.
Update ?? SQL issues update locks against pages containing rows that will be updated. These are Update locks are compatible with Shared locks and are updated to exclusive locks for the second part of the update process.??
Schema Prevents table and indexes being dropped while used by another connection
Bulk Update TABLOCK
   

Table Locking Hint

Notes

HOLDLOCK  
SERIALIZABLE /
REPEATABLE READ /
READCOMMITTED /
READUNCOMMITTED
As per Transaction Isolation Level of same name
NOLOCK  

Row Locking Hint

Notes

ROWLOCK Row lock
PAGLOCK Page lock
TABLOCK Table lock
TABLOCKX ??
READPAST Skip locked rows
UPDLOCK Use update locks instead of shared locks

 

Transaction Isolations Levels

SET TRANSACTION ISOLATION LEVEL level

Level

Record level notes

READ COMMITTED Use shared locks when reading. Locks are maintained until the next record is processed, preventing uncommitted reads. READ COMMITTED is the default.
READ UNCOMMITTED Allow dirty reads. Exclusive locks are not honoured and shared locks are not issued
REPEATABLE READ As per READ COMMITTED, except locks are maintained until the transaction is completed preventing non-repeatabled reads.
SERILIZABLE Prevents updates from other users that would match where clause of the current transaction. Prevents phantom reads.

 

Cursors

  1. DECLARE cursorName  CURSOR FOR [READONLY|UPDATE] sqlSelect
  2. OPEN cursorName
  3. FETCH NEXT FROM cursorName INTO @previouslyDeclaredVariable1 [,...]
  4. WHILE (-1 <> @@FETCH_STATUS) -- 0 = record found, -1 EOF, -2 row  deleted (dynamic cursors only)
  5. IF (-2 = @@FETCH_STATUS ) CONTINUE
  6. DO STUFF
  7. FETCH NEXT ... (as 3)
  8. CLOSE cursorName -- releases record set, which can be reopened
    OR
  9. DEALLOCATE cursorName -- release record set and all resources. Cursor cannot be reopened

Default is forward only, read-only.

Cursor Type

Notes

Forward-only Values, record order and record selection are determined dynamically
Static Value and records are obtained when the cursor is opened
Dynamic Values, record order and record selection are determined dynamically
Keyset-driven Values are determined dynamically, but record order and record selection is determined when the cursor is opened

Bits & Pieces