Service
Description
MSSQLService
Database Engine
SQLServerAgent
Alerts & jobs
MS DTC?
Distributed Transaction Coordinator
msfte
Full Text Engine. Maintains indexes.
Client sends request through one of several APIs.
API creates TDS (Tabular Data Stream) packets from the request.
Client Net Library wraps the TDS packets in network packets.
Client Net Library sends the network packets through IPC to the server
Sever Net Library receives the network packets, reconstitutes the TDS stream and passes it to the Open Data Services.
Open Data Services extracts the client request from the TDS stream and passes it the Relational Engine.
The Relational Engine compiles the query and generates a optimized execution plan.
The Relation Engine executes the plan, calling features of the Storage Engine as necessary.
The result are returned to the client by reversing the 6 steps.
Client sends change
Server loads relevant pages from disk (unless they are in the buffer cache already)
Each change is written to the log first and the log is written to the disk
The checkpoint process (running in the background) writes completed transactions to the database
On first execution the generated execution plan is placed in the procedure cache.
Can be nested to 32 level (current level returned by @@nestlevel).
Nested procedures can access temporary items created by their callers.
Terminology: System, Remote, Extended, Local, Temporary
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 serversysmessages
One entry per error and warning message this server can return.sysdatabases
One entry per database on this servermodel
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 informationsysobjects
One entry for each object in the database
Object
Description
Data Type
Table
Constraint
Default
Rule
Index
View
User-Defined function
Stored Procedure
Trigger
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 sqlCREATE TRIGGER CREATE TRIGGER triggerName ON tableName
FOR|AFTER|INSTEAD OF INSERT|UPDATE|DELETE
AS sqlStatmentsIF UPDATE(columnName) ...
SELECT * FROM Deleted|Inserted
ALTER TABLE tableName [DIS]ABLE TRIGGER triggerName [,...]
ALTER TABLE tableName [DIS]ABLE TRIGGER ALLBEGIN 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
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
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 servernameThen enter commands like through Query Analyzer (i.e. GO to execute batch)
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 LayerOLTP 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]
ENDEXECUTE 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??)
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
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 keyUNIQUE CONSTRAINT constraintName UNIQUE [NON]CLUSTERED (column [, column...])
Side effect: Creates a unique keyFOREIGN KEY CONSTRAINT constraintName FOREIGN KEY (localColumn [, localColumn...]) REFERENCES remoteTable(remoteColumn [, remoteColumn...]) Referential ON DELETE|UPDATE CASCADE
ON DELETE|UPDATE NO ACTIONNO 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.
Can return scalar or table only
Do not support OUTPUT parameters
Views & temporary tables cannot have AFTER triggers associated with them, but can have INSTEAD OF triggers
Triggers can return result sets (bad practice)
Can include a ROLLBACK TRANSACTION without a BEGIN TRANSACTION
CHECK is generally better than TRIGGER for validation purposes as TRIGGER requires expensive rollback, but TRIGGERs can look at columns in other tables while CHECK cannot. Constraints (such as CHECK) run before triggers.
TRIGGERs run after the change.
Triggers can't do anything that the user doesn't have rights to do
Multiple triggers can be assigned to the same event
You have control over which is the first trigger to run and which is the last via sp_settriggerorder, but no control over the triggers in the middle.
What the world would be like without locks:
Lost update (two users update the same record)
Uncommitted data (user 1 reads uncommitted from user 2, user 2 then rolls back. AKA dirty read)
Non-repeatable read (query reads same record more than once, but record has changed between reads
Phantom records (user 1 updates while user 2 adds)
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 /
READUNCOMMITTEDAs 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
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.
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
If an object's owner no longer exists, sysadmin / dbowner can impersonate them through SETUSER or change the ownership through sp_changeobjectowner.
While standard identifiers can be up to 128 characters in length, those used for temporary objects cannot exceed 116 as SQL Server adds an internal suffix to these names.
SET QUOTED_IDENTIFIER ON allows quotes to be used in place of square braces
Supports modulus % operator
Not Equal operator: <>
Row IDs are constructed from fileNumber:pageNumber:RowNumber
Updates to data pointed to by non-clustered
index (that is not based on a clustered index):
If the change to the data won't fit on the page rather than incurring an
expensive page split (which would require updating other index records as
well), the data is moved to a new page (within the heap) and a forwarding
point left at the original data's location. Thus, no changes to the index
are required at all.
If the data moves again, the original forwarding point is updated. If the
data shrinks enough to return to the original location, it is moved back and
the forwarding pointer deleted.
The presence of an update trigger or replication, or a change to a clustered index value makes SQL delete and re-insert the row.
Composite indexes are limited to 16 columns
and WHERE clause must include first column (in the index) in order to use
that index at all. The most specific column should should be listed first.
Total compound column length is limited to 900 bytes.
Note: Composite index (a,b) is not the same thing as a compound index (a +
b)
Expressions can be used for index expressions, but obviously only deterministic ones, and only when a bunch of settings (e.g. ANSI_NULLS sets) are correctly set.
FillFactor 0 and 100 are the same (fill completely).
You can manually create statistics for columns that don't have indexes in order to benefit from improved execution plans. Also, statistics are not automatially gathered for secondary columns in compound index (unless they've been indexed in their own right)
View cannot update more than one base table at a time
Views can be indexed, with the following restrictions:
First index must be a unique clustered index.
The view cannot be based on other views.
The SCHEMABINDING option must be used.
The view cannot be a partitioned view
Partitioned Views: UNION between tables on different disks or servers
xp_logevent
RASIERROR(??, ??, ??) [WITH LOG]
"With Log" echoes to the event log. RAISERROR also sets @@error
Query Optimizers 'Standardizes' the query. For example, IN get converted to a series of ORs.
Don't use write-caching drives with SQL unless they know about SQL!
When resolving deadlocks, SQL rolls back the transaction in which its invested the least time. The error code associated with this is 1205, which client can typically interpret as 'please retry later'.
As per Win32, minimize deadlocks by requesting resources in the same order
View active locks from all sessions through sp_lock
Isolating columns helps the optimizer produce better code (e.g. ytdColumn / 12 > @x should be ytdColumn > @x /12)
Default date parsing is determined by the client
Indexed views - resultsets stored in the database
Requires COUNT_BIG(*) and WITH SCHEMABINDING