70-029 Revision Notes

Developing a Logical Data Model

MS Terminology:

• Entities, Attributes
  Tables, Columns
   
• Relationships
  1-to-1, 1-to-many, many-to-many (via link table)

MS Standards:

• Always define a Primary key, even if not strictly required

Normalisation Rules:

1. Each table describes a single entity (e.g. no car data in people table)
2. Each table has a Primary Key
3. No composite columns . Each column describes only a single attribute. For examine, surname and forename rather than a single name column.
4. No repeated data (row or column)
5. No multi-value data (i.e. no comma delimited data instead of a child table)

Normalisation Benefits:

1. Narrower tables = Quicker sorting and indexing
2. More tables = More clustered indexes
3. Fewer indexes per table (as less attributes per table) = Better UPDATE performance
4. Fewer blanks and NULLs and less repeated data = Smaller database size
5. Table locks have smaller scope

MS book also lists: "Compact and narrow indexes". Which, presuming it is different to (1)  is meaningless.

xx

xx

xx

MS says: 

"Denormalisation is acceptable when it creates definitive benefits and appropriate steps are taken to ensure data integrity."

"... reasonable normalisation often helps rather than hinders performance."

"As a rough rule-of-thumb, extensive normalisation is recommended unless it causes many queries to join in excess of four ways."

To ensure data integrity use constraints, rules and triggers.

Deriving the Physical Design

xx

Creating Data Services

Dynamic SQL

Options, table and variables created by dynamically constructed SQL are automatically destroyed at the end of the execution of the statement. 

• sp_executesql [@var =] [@command | N'command']
                            [N'param type list', param value list]
  e.g.:
      EXEC sp_execute N'select * from people'
      exec sp_executesql N'select * from people where substring(surname,1,1)=@P1 ', N'@P1 varchar(1)', 'B'
      exec sp_executesql N'select * from people where substring(surname,1,1)=@P1 or
           substring(former_surname,1,1)=@P2 ', N'@P1 varchar(1),@P2 varchar(1)', 'B', 'B' 
  
  Notes:
  1. While the first two parameters are Unicode strings (hence the N'...' format) the third is not (although sticking a N in front appears to do no harm). This is presumably because the data is not Unicode data.
  2. This use of N'...' means that any variable that is going to hold the statement to be exercised would have to be type nvarchar or nchar.
  3. sp_executesql generates parameterised query plans which are much more likely to be reused than ad hoc plans generated by EXEC[UTE] statements
  4. command cannot be concatenated from other strings (e.g. @str1+'mytable'+@str2 is not allowed) within the sp_executesql statement.
      
• EXEC[UTE] (command)
  e.g.:
      EXECUTE('Select * from ' + @cTable)
   
  Notes:
  1. Generates ad hoc (and therefore not particularly re-usable) query plans
  2. command can be concatenated from other strings within the EXECUTE statement, but functions cannot be used. All parts of command must be of type character. It is not possible, for example, to convert numeric to character within the EXECUTE command.

Formatting:

1. CHAR(13) [note the extra 'A'] can be used to insert a CR for readability.
2. Transact-SQL has no "end of statement" character, so multiple statements can be included in a single command parameter
3. sp_executesql /  EXECUTE can be nested (i.e. called from within sp_executesql / EXECUTE)

<---- Wrong place?

• Batches
  A set of Transact-SQL statements submitted together and executed in sequence on the server. Batches are separated by the "GO" command.
   
  Batches cannot be sent though ODBC / OLE DB (although technically you can create a stored procedure though  ODBC / OLE DB and of course that can include batches). Trying is issue a "GO" through ODBC / OLE DB will produce an error because "GO" is not a SQL command. Batches can be used both with the Query Analyzer and the oSQL command line utility.
   
  Batches are not encased in a transaction (unless you include the required commands in the batch or are already in a transaction of course)
   
  If any line in a batch contains a detectable syntax error it, none of the lines are executed.
   
  There are limitations of using DML commands in batches:
  • New columns added in a batch (e.g. via ALTER TABLE, CREATE VIEW etc.) cannot be referenced within that batch (a "GO" must be issued before they can be referenced)
     
  • CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER and CREATE VIEW cannot be combined in the same batch or used multiple times in the same batch.
     
• Scripts
  Scripts are simply text files containing one or more Transact-SQL commands. Default extension is .SQL.

xx

Cursor Support
For ADO, OLE DB, ODBC & DB-Library

Transact-SQL Cursors

• Transact SQL cursors are always server-side cursors
• Can increase the readability of code
• Wasteful of resources if large
• Are DECLAREd, OPENed, FETCHed, CLOSEd and DEALLOCATEd
• Come in 4 types: Forwards-only, Static, Dynamic and Keyset-driven
• Cursor with static properties perform faster but can take time to set up whereas cursors with dynamic properties are fast to set up but slower to perform.

DECLARE CURSOR (ANSI)
DECLARE name [INSENSITIVE] [SCROLL] CURSOR FOR SQLSelectStatement
        [FOR {READ ONLY | UPDATE [OF col [, col2, ..., colN]]}]

DECLARE CURSOR (SQL Server)
DECLARE name CURSOR [LOCAL | GLOBAL] [FORWARD ONLY | SCROLL]
        [STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
        [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING]
        FOR SQLSelectStatement
        [FOR UPDATE [OF col [, col2, ..., colN]]]

Parameter	Notes
SQLSelectStatement	Keywords such as DISTINCT, TOP, UNION, GROUP BY and HAVING force the cursor type to static
LOCAL	The cursor is local to the stored procedure, trigger or batch in which it is defined. When that unit is exited, the cursor is destroyed on exit and cannot be referenced externally.
GLOBAL	The cursor is not limited to the stored procedure, trigger or batch in which it is defined and can be referenced until the connection in which it was defined is dropped (or the cursor is closed/deallocated) Note: Although global is the default if neither local or global was specified, this behaviour can be overridden with SET CURSOR_DEFAULT LOCAL and SET CURSOR_DEFAULT GLOBAL
FORWARD_ONLY	Data can only be read sequentially and by FETCH NEXT. If neither FORWARD_ONLY or SCROLL is specified FORWARD ONLY is the default only if the cursor type clause (static/keyset/etc.) is not used. FORWARD_ONLY cannot be used with SCROLL in either the SQL Server or ANSI 92 definitions.
SCROLL	All fetch options are available
STATIC	Cursor is created in tempdb and changes to it are not reflected in the base tables
KEYSET	Key fields for records in the cursor are stored in tempdb, but not other values. Changes made by other users are visible, but inserts are not (because the keyset records in tempdb are static). Records that have been deleted (or have had their key fields changed) by other users set @@FETCH_STATUS to -2.
DYNAMIC	Changes made by other users, including inserts and deletes, are visible to every fetch.
FAST_FORWARD	Combination of FORWARD_ONLY, READ_ONLY and some additional internal performance optimisations.
READ_ONLY	Prevents updates through the cursor even if another clause indicates writability
SCROLL_LOCKS	??Locks the row as they are read into the cursor to guarantee the ability to update those rows. (?? this is the MS Help file version, the MS Book says something else)
OPTIMISTIC	Rows are not locked as they are read, instead the update fails if modified outside of the cursor.
TYPE_WARNING	Tell the user if the selected cursor type was changed (for example, by use of one of the keywords listed in SQLSelectStatement).
UPDATE	Entire cursor is writable
UPDATE OF col [, col...]	Only the specified columns are writable

OPEN
OPEN
OPEN [GLOBAL] name
OPEN @variableName

Parameter	Notes
GLOBAL	If there are both local and global cursors with the same name, the local one one will be opened unless the GLOBAL clause is specified.
@variableName	If the cursor was generated through SET @variableName = cursorName or SET @variableName = CURSOR [FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] ...
<NONE>	?? Help file and MS book says this syntax is legal, but neither says what it means.

• If the cursor requires a record set or keyset to be created in tempdb, this occurs during the OPEN process.
• After opening a cursor, @@CURSOR_ROWS returns the number of qualifying rows.

FETCH
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM] {[GLOBAL] cursorName | @variableName}
        [INTO @variableName [, @var2 ...]]

Parameter	Notes
GLOBAL	As abovce
n	A number (can be a @variable)
ABSOLUTE	Cannot be used with dynamic cursors
NEXT	If this is the first fetch performed on the cursor it effectively performs a FETCH FIRST. FETCH NEXT returns the current record before moving forward
PRIOR	If performed on first record in cursor, leaves us on a fictional BOF record. FETCH PRIOR moves then returns the record.
<NONE>	??Presumable if NEXT, PRIOR, etc is not specified the record is returned without moving the pointer. Need to test 'cos its not covered by the help file or book.

• Fetch does not fail, you have to examine @@FETCH_STATUS to know whether your fetch succeeded

@@FETCH_STATUS

Value	Notes
0	Success
-1	Failed, BOF or EOF
-2	Row is missing (e.g. deleted by another user)

CLOSE
CLOSE
CLOSE {[GLOBAL] cursorName | @variable}

• Releases any locks the cursor is holding on tables
• Closed cursor can be reopened

DEALLOCATE
DEALLOCATE
DEALLOCATE {[GLOBAL] cursorName | @variable}

• Releases the cursor definition if no more reference exist to it (you can have multiple references if it is pointed to by several variables. When a variable has been deallocated the link to the cursor is deleted - i.e. if you have two variables and deallocate one the deallocated variable is no longer valid even thought the cursor still exists. Similarly, deallocating cursorName is breaking the relation between the name and the actual cursor definition, so deallocating using cursorName does not release the cursor definition if variable still reference it.)

Updating Cursors
The current record can be updated using the WHERE CURRENT OF ... clause, provided the cursor has been declared updateable. For example:

UPDATE table SET field = newValue WHERE CURRENT OF cursorBasedOnTable

?? Need more detail

?? Need detail on passing local cursors out of stored procedures (p280)

Cursor Stored Procedures

sp_cursor_list	Returns a list of open cursors (on the current connection) and their attributes
sp_describe_cursor...	Returns attributes of a cursor
sp_describe_cursor_columns...	Returns attributes of columns in a cursor
sp_describe_cursor_tables...	Returns information about the base tables used by the specified cursor

Transactions

Full syntax is:

• BEGIN [DISTRIBUTED] TRAN[SACTION] [transactionName | @transactionVar [WITH MARK]]
• SAVE TRAN[SACTION] [saveName | @saveVar]
• COMMIT [TRAN[SACTION]] [transactionName | @transactionVar]
• ROLLBACK [TRAN[SACTION]] [transactionName | @transactionVar | saveName | @saveVar]

where transactionName makes the code more readable.

WITH MARK is used to mark the transaction log with string that can be referred to in restore options.

@@TRANCOUNT returns the transaction level. DBCC OPENTRAN can be used to return more information on active transactions.

ROLLBACK rolls back all outstanding transactions, not just the last one begun.

Not all stored procedures (e.g. sp_dboption) can be issues in a transaction

Not all commands can be issued in a transaction (including, but not limited to: ALTER DATABASE, BACKUP LOG, CREATE DATABASE, DROP DATABASE, RECONFIGURE, RESTORE DATABASE, RESTORE LOG, UPDATE STATISTICS)

Isolation Levels

The translaction isolation level is set at the connection level and specifies the default locking behaviour for that connection. The Transaction Iolation Level is set by the SET TRANSACTION ISOLATION LEVEL level command (note that it is level not to level.). level can have the following values:

READ UNCOMMITTED	Dirty reads are allowed, no share locks are issued and exclusive locks are ignored. Equivalent to stating the NOLOCK hint for every table in the select statement.
READ COMMITTED	The default. Shared locks are issued and all locks are honoured. Dirty reads cannot occur.
REPEATABLE READ	As per READ COMMITTED, except that shared locks caused by reads are not released until the transaction completes. Thus a read operation is "repeatable" since another connection cannot change the data while another has it locked. Note that this does not apply to new rows added by another connection (i.e. REPEATABLE READ does not prevent phantoms.)
SERILIZABLE	As per REPEATABLE READ except prevents other connections adding rows that would be returned by WHERE clauses issues in statements by the locking connection.

Value in the above cursor are shown with the least restrictive locking mechanism first through to the mot restrictive mechanism last.

DBCC USEROPTIONS returns (amongst other things) the current transaction isolation level. If the default isolation level has not been overridden, it will not be listed by DBCC USEROPTIONS.

Recovery and Checkpoints

When the system recovers from a crash the following occurs:

Transactions completed before last checkpoint	No action required, database already contains this information.
Transactions completed after last checkpoint, but before system failure	Read from transaction log and apply to database (aka "Roll Forward")
Transactions completed (or outstanding) after system failure	No action is taken, database is left at its checkpoint (plus above changes) state. Aka "Roll Back"

Note that this does mean than that a short transaction started after after a long slow transaction can be rolled forwards while the slow transaction is rolled back. Since inner transactions are never committed until the outermost transaction is committed, this is not a problem.

SET IMPLICIT_TRANSACTIONS ON | OFF

• Really for the benefit of systems not originally designed for SQL Server.
• Automatically starts a transaction when a ALTER TABLE, INSERT, CREATE, OPEN, DELETE, REVOKE, DROP, SELECT, FETCH, TRUNCATE TABLE, GRANT or UPDATE are issued
• Prevents use of nested transactions
• User must commit before dropping connection else an automatic rollback takes place
• Is OFF by default

Levels of Locks (finest granularity first)

Object	Scope
Row Identifier	Single row in a table
Key	Single row in an index ?? doesn't sound right
Page	Single Page
Extent	Single Extent (8 pages)
Table	Entire table and all it's indexes
Database	Entire database

 

Concurrency Problems Prevented by Locks

Name	Description
Lost Update	Two users update the same record and where only the second set of changes is saved.
Uncommitted Dependency / Dirty Read	Where one transaction reads the uncommitted changes of another transaction
Inconsistent Analysis / Non-repeatable Read	Where one transaction has to read the same record several times, but another transaction has  updated the record in the meantime.
Phantoms	Where one transaction adds records that would be visible to a concurrent transaction

Types of Lock

Name	Description
Shared Locks	Applied automatically by SQL during operations that do not update data. A shared lock prevents other transaction updating the locked data.  Additional transactions can acquire shared locks on the same data even while the first transaction is still active.
Exclusive Locks	Applied automatically by SQL during INSERT, DELETE & UPDATE statements. Once a transaction has an exclusive lock no other transactions can obtain a lock of any type on that data. Exclusively locked data cannot be read by other transactions.
Intent Locks	Applied automatically by SQL to prevent long winded searches for locks. For example, if a transaction acquires a row-lock it will also obtain a table level intent lock. While another transaction holds an Intent lock, no other transaction can acquire an exclusive lock at that level. This means that if another table required an exclusive table level lock it would be refused by just checking for Intent locks rather than scanning the entire table looking for row-locks.  SQL supports the following level of Intent locks: Intent Share (IS), Intent Exclusive (IX) and Shared with Intent Exclusive (SIX). ?? Need to amplify SIX
Update Locks	Applied at the page level automatically by SQL when it plans to update a page later. When the time to update the the page actually arrives the update lock is upgraded to an exclusive lock. ?? This somehow prevents the presence of lots of shared locks blocking an exclusive lock, but I don't understand how
Schema Locks	A schema lock prevents a table or index being dropped or modified by another transaction.  SQL supports two types of Schema: Schema Stability (Sch-S) which prevents dropping and Schema Modification (Sch-M) which prevents modification.

Lock Compatibility Matrix

		Existing Lock In Another Transaction
		Intent Shared (IS)	Intent Exclusive (IX)	Shared With IX (SIX)	Shared (S)	Update (U)	Exclusive (X)
R e q   u   L e   o s   c t   k e   d	Intent Shared (IS)	Y	Y	Y	Y	Y	N
	Intent Exclusive (IX)	Y	N	N	N	N	N
	Shared With IX (SIX)	Y	N	N	N	N	N
	Shared (S)	Y	N	N	Y	Y	N
	Update (U)	Y	N	N	Y	N	N
	Exclusive (X)	N	N	N	N	N	N

Notes:

1. Table shows diagonal symmetry. I.e. opA -> opB is same as opB -> opA
2. Sch-M is not compatible with any other type of lock
3. Sch-S is compatible with all locks except Sch-M

Optimistic & Pessimistic Concurrency

MS book's terms for standard Optimistic and Pessimistic locking. Under SQL Server you (or the client interface you are using) is responsible for implementing both sorts of locking.

SET LOCK_TIMEOUT timeInMilliseconds

By default SQL Server will wait forever for a required resource to be unlocked. This default behaviour can be overridden by SET LOCK_TIMEOUT n and restored by a SET LOCK_TIMEOUT -1. The current value of SET LOCK_TIMEOUT can be obtained from @@LOCK_TIMEOUT.

Locking Hints

• Locking hints can be applied to the FROM clause in SELECT, INSERT, DELETE and UPDATE statements.

• Take the form FROM table WITH (hint [, hint ...])
  where hint can be:

ROWLOCK	Use Row Locks
PAGLOCK	Use Page Locks (Note: PAGLOCK is correct (no E))
TABLOCK	Use Shared Table Lock
TABLOCKX	Use Exclusive Table Lock
NOLOCK	Equivalent to READ UNCOMMITTED
HOLDLOCK	Equivalent to SERIALIZABLE. These locks remain applied until the end of the transaction.
UPDLOCK	Use update locks instead of shared locks when reading. These locks remain applied until the end of the transaction.

 

Deadlocks

SQL Server terminates (and rolls back) which ever transaction has had the least time invested in it (that's elapsed time, not CPU time ??confirm). Error 1205 is sent to tbhe terminated transaction's connection.

Reduce deadlocks by:

• Using resources in the same order in all transactions.
• Minimise the number of steps in a transaction
• Minimise transaction times (avoid queries that update many rows, no user I/O etc.)
• If possible, use a low isolation level.
• Using Bound Connections. Bound Connections share transactions and locks.

Misc. Locking Information

• SQL Enterprise Manager -> Management node -> Current Activity Window
  Show info:
  • By User
  • By Connection
  • By Object
• sp_lock stored procedure
  Shows info: spid, dbid, ObjId, IndId, Type (DB, EXTent, TABle, KEY, PAGe, RID, FILe or IDX), Resource, Mode (lock type: S, X, U, IS, IU, IX, Sch-S or Sch-M) & Status (GRANT - lock obtained, WAIT - blocked by another transaction, CNVT - blocked by another transaction while trying to update an existing lock)
• SQL Server Profiler
• NT Performance Monitor -> SQL:Server:Locks object
  (historic rather than active information)
• syslockinfo, sysprocesses, sysobjects & syslogins system tables
• sp_who

INSERT [INTO] table SELECT ...

• table must already exist
• data types for each column must be compatiable
• columns not covered by the select must either allow NULLs or have a default value

DELETE [FROM] tableOrView [FROM tableOrView2 [FROM ...]] [{WHERE expr | WHERE CURRENT OF cursorName}]

• Second (and subsequent FROM clauses) are join conditions. For example "DELETE FROM a FROM a INNER JOIN b ON a.x=b.x" deletes all records in "a" that were successfully linked with b. A better example might be "DELETE FORM orders FROM orders INNER JOIN badcustomers ON orders.customerID = badcustomers.customerID".

UPDATE tableOrView SET field = expr [FROM tableOrView2 [FROM ...]] [{WHERE expr | WHERE CURRENT OF cursorName}]

• expr can be DEFAULT which means ??
• expr cannot include fields from other tables or views. It can have a table/view prefix, but this must be tableOrView
• Again, second and subsquence FROM clauses are join conditions. Be careful with joins as SQL will only update the same row once. If the join results in multiple rows for a single row in tableOrView the results are undefined.

Join Abbreviations

• JOIN == INNER JOIN
• LEFT JOIN == LEFT OUTER JOIN == *=
• RIGHT JOIN == RIGHT OUTER JOIN == *=
• FULL JOIN == FULL OUTER JOIN

Cross Join = Cartensian product = no "ON" clause

Select InTo

• SELECT exprList INTO newTable FROM ...
• SELECT ... INTO #tempTable | ##globalTempTable
• Cannot select into an object that already exists (even if it a # or ## table)

Subqueries

• Can be used with SELECT, UPDATE, INSERT and DELETE statements
• Are enclosed in round brackets
• Can only include one expression / fieldname in the subquery's select statement, and it's datatype can't be image or text. That said, when using EXISTS the expression can be *.
• Unlike VFP there is no limit to the level to which subqueries can be nested.
• Subqueries that return only a single value (as opposed to a list of single values) can be used wherever an expression is used
• Nested subqueries are evaluated once only
• Correlated Subqueries involve a reference from the inner query to the outer query. Correlated Subqueries are evaluated once per record in the outer query.

    Examples (silly examples because they could all be done better without a subselect):
        SELECT EmployeeID, (SELECT COUNT(*) FROM Orders o WHERE o.EmployeeID = p.EmployeeID) FROM Orders p 
            GROUP BY EmployeeID

        SELECT DISTINCT EmployeeID FROM Orders Where EmployeeID IN 
            (SELECT EmployeeID FROM Orders WHERE CustomerID ='HANAR')
  

Standard Identifiers

1. Up to 128 characters in length, except for temporary objects which should not exceed 29 characters including the leading # or ##. This is because SQL internally adds a numeric suffix to the supplied name.
2. Can consist of letters, numbers and the characters @, # and _.
3. First character cannot be numeric
4. Can include spaces or be a reserved word if the identifier in included in double quotes or square bracket. By default only square brackets are allow, to use double-quotes first issue a SET QUOTED_IDENTIFIER ON. If deliminators are used, any characters can be used in an identifier name.

Naming Guidelines

1. Keep names short and meaningful
2. Use a naming convention. Use an object type prefix (e.g. tbl for table, vw for view, etc). 
3. Make names unique across objects. For example, do not create a table with the same name as a role.

Object References

Are of the form server.database.owner.object. All 4 elements together represent a fully qualified name. All elements except object can be omitted and will be defaulted as follows:

server	Defaults to local server
database	Defaults to current database
owner	Defaults to the user name associated with the user login in that database.

Elements in the middle of references can be omitted by just including the colons, for example: server...object

During creation the user's role can be specified in place of owner thereby making that role the owner. This greatly simplifies administration. For example, a member of the dbo group might issue CREATE TABLE MyDatabase.dbo.MyTable (...)

Local Variables

T-SQL Syntax:	DECLARE  @variable_name  data_type SET @variable_name = expression
T-SQL Example #1:	DECLARE @cWelcome char(20) SET @cWelcome = 'Hello World' SELECT * FROM messages WHERE msgtxt = @cWelcome
T-SQL Example #2:	DECLARE @nCount int SELECT @nCount=Count(*) FROM messages Note: If the above SQL returned more than one record (obviously not possible in the above example) the variables is set to the value returned in the last row.
T-SQL Example #3:	DECLARE @myspid integer SELECT @myspid=@@spid EXECUTE  sp_who @myspid

Global Variables

No longer used in Transact-SQL. Now called functions (after all, if they were a variable you should have been able to assign a value to them). Note that although the terminology has changed the syntax has not. For example, it is still SELECT @@VERSION and not Select Version(). Global Variables are still used in DTS.

Operators

Logicals: The usual (=,+,-,*, /, %, <, >, <>, AND, NOT) plus the unusual (!=, !<, !>).
Binaries: ^ (XOR), | (OR), & (AND)
SQLs: ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

Note that Logical, Binary, SQL is the operator precedents order except than AND and NOT actually belong between Binaries and SQLs and multiplication/division/modulus has a higher preference than addition/subtraction.

Functions

In SQL speak there are three types of function:

• Rowset,
  Return an object that can be used in place of a table object in another statement. e.g. ???
• Aggregate
  Return a single summary value from multiple input values. e..g SUM
• Scalar
  Return a single value from none, one or multiple input values. e.g. SQRT    

Useful functions:

Advanced (or unique to SQL) functions:

Notes:

1. CASE can be used in SQL SELECT statements, for example:
   SELECT name, age, ' Age Group' =
       CASE
           WHEN (age < 1) THEN 'Baby'
           WHEN (age < 2) THEN 'Toddler'
           WHEN (age < 11) THEN 'Brat'
           WHEN (age < 18) THEN 'Teenager'
           WHEN (age < 65) THEN 'Adult'
           ELSE 'OAP'
       END
       FROM people
2. System functions return 0 unless the document say otherwise. Non-zero values are normally indicate errors.

Control Structures

Notes:

1. command can be a single command or a BEGIN...END block
2. In a WHILE block the command block can include BREAK (equ VFP's EXIT) and CONTINUE (equ. to VFP's LOOP) statements. 
3. RETURN can be exit a procedure at any point.

Multiple commands

There is no line terminator. Multiple commands can be included on a single line. For example:

     EXECUTE sp_who (select @@spid)

doesn't call sp_who with the result of select @@spid as the parameter, but instead executes "execute sp_who" and then executes "select @@spid" as a separate command. The follow achieves the desired result on a single line, but is unreadable:

    DECLARE @myspid integer select @myspid=@@spid EXECUTE sp_who @myspid

Comments

Single line comments: Double hyphen (--). Can be used at the end of an existing line, just like VFP's &&
Multiline comments: As per C (/* & */)

xx C2L2

VIEWS

• View definitions are stored in system tables. If you change the structure of the underlying tables this information is NOT automatically updated.
• A view's dependencies can be shown using sp_depends viewName
• A view's definition can be shown using sp_helptext viewName. The WITH ENCRYPTION option prevent this (and otherways) from displaying the code. Attempting to run sp_helptext on a view created with the WITH ENCRYPTION option returns the message "The object comments have been encrypted".
• Views:
  • Mask Complexity
  • Simplify Permissions
    Can specify permissions for a view containing a series of columns rather than individual permissions for those columns at the base table level
  • Simplify Export
  • Require sysadmin, db_owner or db_ddladmin role, or specifically granted rights
  • Cannot include ORDER BY, COMPUTE [BY] or INTO
  • Can include GROUP BY and UNION
  • Cannot have more that 1,024 fields
  • Cannot be based on temporary tables
  • Can be based on other views (this is called a nested view)

CREATE VIEW

• CREATE VIEW [owner.]viewName [(columns)] [WITH ENCRYPTION] AS SELECT .... [WITH CHECK OPTION]
• If the select portion of the statement uses expressions as output fields columns must be supplied
• If the select portion of the statement has output fields with the same name, columns must be supplied
• Columns is just a list of names in order. No type or other information is required
• Require sysadmin, db_owner or db_ddladmin role, or specifically granted rights
• Must have SELECT rights on base tables (else view will be empty)
• If executed in a batch, must be executed alone

ALTER VIEW

• Not ANSI SQL-92 standard (drop & recreate instead)
• ALTER VIEW syntax is identical to CREATE VIEW, all we are really saving is the effort of dropping the view and recreating the permissions

DROP VIEW

• Only the owner can drop a view directly. Members of sysadmins & db_owner can drop the view, but only if they prefix the name with the real owners details.
• Dropping base table(s) does not drop the view definition. These must be dropped specifically.

Updateable View

• Only one table can be updated
• Cannot update calculated fields
• Updates must comply with underlying data restrictions (such as NULL acceptance). For example, inserting a row into a view where the view has less fields than the base table and where the missing fields require a value / do not accept nulls.
• WITH CHECK OPTION checks that every insert will be visible to the view and reject those updates which are not.

SQL Server supports 5 types of stored procedure:

• System
  Prefixed by "sp_"
  Stored in master database
   
• User-Defined
  Created in individual user databases
   
• Temporary
  Prefixed by "#" or "##"
  Created in tempdb
  Automatically destroyed when the session that created it is closed (applied to both # and ## stored procedures)
   
• Remote
  Called from a remote server or client
   
• Extended
  Prefixed by "xp" (generalisation, not  a rule)
  Stored in external DLL
  Must be prefixed with "Master" when calling from a database other than Master (e.g. EXEC Master..xp_cmdshell 'dir').
  Example Extended stored procedures include xp_cmdshell, xp_logevent, xp_sendmail and xp_readmail.
  Use the Open Data Services API to talk to SQL Server, not ODBC or OLE DB.

Procedural Cache

The procedural cache holds the query plans for stored procedures. Its size isn't fixed, but when it runs out of room the least recently used procedure is dropped from it to make room.

Recompile

Stored procedures are automatically recompiled when:

• The Schema changes
• When the environment in which the stored procedure is being executed is different from the one in which it was originally compiled
• After statistics for a table or index used by the stored procedure change

Why use Stored Procedures?

• Cross application validation and standardisation (i.e. Business Rules)
• Allow users to access tables and objects they don't normally have access to in a controlled manner
• Since the query plans for stored procedures are cached, this can result in a performance benefit
• Reduce network traffic. Complex queries or operations often send more bytes in the request than they receive in the answer. Calling a stored procedure holding which contains a series of transact statements can dramatically reduce network traffic.

CREATE PROCEDURE

• CREATE PROC[EDURE] procName [;number] [@paramName paramType] [= defaultValue] [OUTPUT] [, @...]
                 AS transactSQL
  where:
  • Store Procedures can be local by prefixing procName with # or ##
  • number allows several stored procedures to share the same name so that they can all be dropped in one DROP PROCEDURE procName command
  • paramName is automatically defined as being local
  • paramType is any data type except image, text & ntext
  • defaultValue is the default value for the parameter. If defaultValue is specified the parameter does not have to be specified in the calling code. defaultValue must be a constant and can be NULL.
  • OUTPUT indicate the value is to be returned to the caller
  • transactSQL is a series of one or more Transact-SQL statements. A BEGIN...END wrapper is not required. The simplest useful stored procedures often exist as nothing more than ".... AS SELECT ...".
  • The WITH ENCRYPTION option can be used to hide implementation details from users. If not encrypted the stored procedure code can be displayed with sp_helptext procName.
  • The WITH RECOMPILE option prevents the query plan being cached and each call to the stored procedure will create its own plan.
• Must appear alone in a batch file
• Can include any Transact-SQL statements except CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW, CREATE DEFAULT and CREATE RULE.
• Can return early by using the RETURN [nValue] command. A nValue of 0 indicates success, -1 through -14 are allocated for known errors,-15 through -99 is reserved for future use. If nValue defaults to 0 if not specified. Returning a number between -1 and -99 inclusive does not trigger an error handler (you still have to look at the returned value in the calling code) nor does it set @@ERROR. nValue is of type int.
• Maximum size is 128K
• Parameter information is stored in the syscolumns table.
• Requires syadmin, db_owner, db_ddladmin or specifically granted permissions.

ALTER PROC[EDURE] ...

• As expected

DROP PROC[EDURE] procName [, procName2 ...]

• As expected, except you can specify more than one stored procedure to be dropped at a time.
• Good old sp_depends can be used to check nothing is referencing the stored procedure you are about to drop.

EXEC[UTE]

• [EXEC[UTE]] [@returnVar = ] {procName [;number] | @procVar}
          [[@param = ] {value | @paramVar  [OUTPUT] | DEFAULT]}]
          [WITH RECOMPILE]
  where
  • procName, number, value are as above except the procName can now include full dot format (e.g. [[[LinkedServer.]OtherDatabase.]OtherOwner.]procName)
  • @procVar is simply the name of a local variables holding the name of the function to call
  • @paramVar: A parameter
  • @paramVar OUTPUT A return parameter. Must be declared and initialise before the call and also specified in the CREATE PROCEDURE. If OUTPUT is specified in the CREATE but not the EXECUTE the stored procedure is still run, but no value is returned ?? This is based on testing. MS Book says this "...the stored procedure still executes, but it produces an error condition." ??
    If OUTPUT is not specified in the CREATE but is specified in the EXECUTE the error "Formal parameter '@param' was defined as OUTPUT but the actual parameter not declared OUTPUT." results.
  • [DEFAULT] Use the default value specified in the called stored procedure definition
  • [EXEC[UTE]] reflects that if the first line in a batch is a call to a stored procedure EXEC is not required
  • @returnVar is set to the (numeric) value specified in a RETURN statement in the called stored procedure. If no RETURN statement is hit when executing the stored procedure this variable is set to 0.
  • WITH RECOMPILE forces a new query plan. Useful if an atypical parameter value is being passed, but computationally expensive.
• The results of an EXEC can be used as the input for a INSERT statement. For example, INSERT INTO NewCustomers EXEC GetNewCustomers. Obvious the GetNewCustomers would have to issue a select that returned the format in a compatible format.
• Using the @param =  X syntax is reference to as passing by reference. Note that SQL definition of passing by reference refers to the inclusion of the parameter name in the calling code and has nothing to do with VFP/C++ style references / pointers. If the parameter name is included the parameters can be specified in any order. If the parameter name is not supplied the parameters are positional based and this is referred to as by position in SQL parlance. When passing by position optional parameters can be excluded, but if parameter N is omitted parameters N+1 and later cannot be specified by the calling code (no "DO x With 1,,3,4,5" here)

Other stuff

• Stored procedure are created in the currently selected database
• Non-global temporary tables (i.e. #temp not ##temp) created in a stored procedure are automatically deleted at the end of that stored procedure. Temporary tables (and all other objects) created by a stored procedure are accessible to sub procedures.
• The sp_proc option can be used to set the startup option for a stored procedure to true. When set to true the stored procedure will be executed automatically on start up of SQL Server (after all databases have been recovered). Each start up stored procedures uses a separate thread so where possible chain auto-startup procedures rather than running them in parallel.
• Prefixing the procedure name in CREATE PROCEDURE with dbo. is a good idea to prevent problem with security relating to the owner of the stored procedure differing from the owner of the underlying tables.
• Store Procedures can be nested to 32 levels. The current level is can be read from @@NESTLEVEL
• sp_stored_procedures displays summary information about stored procedures in the current database
• sp_recompile object can be used to mark a specific stored procedure for recompile next time it is run (its object because the same command can be used to recompile triggers or everything dependent on the named object (e.g. a table).). Note that (a) object has to be specified - we can't simply recompile all objects in the current database and (b) things are not recompiled until they are next called.

Example

CREATE PROCEDURE dbo.MyExample
    @var1 int, @var2 int, @result varchar(30) = NULL OUTPUT
AS
    IF @var1 > @var2
        SET @result = 'First number is bigger'
    ELSE IF @var2 > @var1
        SET @result = 'Second number is bigger'
    ELSE
        SET @result = 'Numbers are the same'
GO

DECLARE @returnVar varchar(30)
EXEC MyExample 1,2,@returnVar OUTPUT
SELECT @returnVar

> Second number is bigger

Error Handling

• @@ERROR holds the error number for the last transact statement executed. It is cleared down with every command, so if you want to check for errors you have to check directly after the statement in question. If no error occurred @@ERROR is zero.
   
• RAISERROR ( { msgID | msgStr } { , severity , state } [ , arg1 [ , argN ... ] ] ) [ WITH option1 [ , optionN ... ] ]
  where:
  • msgID is an existing message ID for the message. Custom msgID can be created using the sp_addmessage stored procedure (see later)
  • msgStr is the string to write to the NT application log and SQL Server error log.
  • argN
    msgStr can include printf style tokens which are filled in by arg1-argN. If msgID is supplied it also can refer to a message string containing tokens.
  • severity
    0 - 18 indicates a failure and can be issues by any user
    19-25 indicates a fatal error and can only be issued by owners of the sysadmin role. Errors in the range 20 - 25 cause the user to be automatically disconnected. If severity is set in the range 19 - 25 the WITH LOG option must be specified.
    0 - 10 automatically sets @@ERROR to 0 and does not stop execution.
    
    10 indicates "Status Information"
    11 - 16 indicates that the error can be corrected by the user
    17 indicates insuffient resources
    18 indicates "Nonfatal Internal Error Detected"
    19 indicates "SQL Error In Resource" and the rest of the batch job is terminated, but the user is not disconnected
     
  • state is an arbitrary value from 1 to 127. Values less than 1 are treated as being 1.h
  • option
    LOG             Writes the message to the NT application log and the SQL Server Error log as well as to the client.
                         Logged messages are limited to about 440 bytes in length.
    NOWAIT     Writes the message to the client immediately
    SETERROR  Sets @@ERROR to msgID (or 50,000 if msgID wasn't supplied) 

  Note that RAISERROR contains only one E.

  User defined message can be created in master.sbo.sysmessages by using the sp_addmessage stored procedure and removed using the sp_dropmessage.
   

• sp_addmessage [@msgnum = ] msgID , [@severity = ] severity , [@msgtext = ] 'msgStr'
      [ , [ @lang = ] 'language' ] [ , [ @with_log = ] 'withLog' ] [ , [ @replace = ] 'replace' ]
  where
  • msgID, severity and msgStr are as above
  • withLog is a string 'TRUE' or 'FALSE'. If 'TRUE' the message is always written to the logs when raised. If 'FALSE' it is only written when the RAISERROR command includes the WITH LOG option.
  • replace is the string 'REPLACE' if a message with the existing msgID should be replaced

Triggers

• {CREATE | ALTER} TRIGGER triggerName ON tableName [WITH ENCRYPTION] FOR {[INSERT] [,] [UPDATE] [,] [DELETE]}
      [WITH APPEND] [NOT FOR REPLICATION] AS sql
• Attached to a specific table (and one table only, but a table can have multiple triggers for the same event) and to insert, update or delete event
• Cannot be called directly
• Cannot include any of the following commands: CREATE, ALTER, TRUNCATE TABLE, UPDATE STATISTICS, DROP, GRANT, REVOKE, DENY, RECONFIGURE or SELECT INTO.
• ALTER TRIGGER replaces the the INSERT, UPDATE & DELETE settings (i.e. is non-additive). For example, if a trigger call trgFred created FOR INSERT is the subject of an ALTER TRIGGER trgFred ....FOR UPDATE, DELETE then trg will fire only for UPDATE and DELETE.
• DROP TRIGGER trgName [, trgName2...] requires sysadmin, db_owner, db_ddladmin or table ownership. If the dropper isn't the owner, the owner must be specified as part of trgName.
• Do not take parameters or return values (i.e. implement low-level integrity, not a function call)
• If the command that triggered the trigger is not in a transaction, a transaction is begun. This transaction includes the triggering update, insert or delete.
• If the trigger rolls the transaction back then any commands left in the batch will not be executed
• Better than CHECK clauses because you can validate using information from other tables, but more overhead than a CONSTRAINT
• Triggers are evaluated after constraints, so if the constraint is violated the trigger will not fire.
• Only triggers can raise customised error messages (constraints, rules & defaults just generate the appropriate system error message)
• If a number of rows are effected, the trigger only fires once and the number of rows effected are stored in @@ROWCOUNT. Inserted rows are duplicated in a cursor called insert, deleted rows are moved to a cursor called delete. Updates creates both cursors with the inserted cursor holding the post update image and the deleted cursor hold the pre update image..
• Triggers can be enabled or disabled (that is, still defined but not called by the system) using ALTER TABLE tableName {ENABLE | DISABLE} TRIGGER {ALL | trgName [, trgName2...]}
• The TRUNCATE TABLE command does not cause FOR DELETE triggers to run.
• UPDATE(column) returns true if the column in question has been modified
• SQL Server supports up to 32 levels of nested triggers. Attempting to go beyond 32 levels will cause the trigger to fail and roll back. The current nesting level is held in @@NESTLEVEL.
• Nested triggers are not the same as recursive triggers: a nested trigger will not fire more than once in a transaction. For example, if a trigger modifies a record for which the trigger was run in the first place, this won't cause the trigger to be run again.
• Nested triggering can be enabled / disabled at the server level. Recursive triggering can be enabled / disabled at the database level
• Trigger are normally fast because the records they operate on are already in memory, as are the inserted and deleted cursors. The slowest triggers are those that access other tables.

Stored Procedures

sp_helptext triggerName	Displays trigger definition (provided its not defined with the WITH ENCRYPTION clauses)
sp_depends triggerName	Displays details of objects referenced by the trigger
sp_helptrigger tableName	Displays all triggers for the specified table
sp_configure 'nested triggers', N	Enables nested triggers (N=1) or disables nested triggers (N=0)
sp_dboption 'database', 'recursive triggers', {TRUE | FALSE}	Enableds (TRUE) or disables (FALSE) recursive triggering for database database

 Aggregate Functions

• With the exception of COUNT(*), if no records pass the filter conditions the returned value is NULL.
• Indexing commonly aggregated columns is usually a good idea
• Field listed in the select's output list must either be the aggregate functions or also present in the Group By statement. ?? Check.

Group By

• If both GROUP BY and ORDER BY are used in the same query, ORDER BY must occur after GROUP BY otherwise a syntax error is generated.
• NULLs are considered to be equal to other NULLs for the purposes of grouping.
• SQL Server requires the WHERE clause (when present) to be before the GROUP BY, else it generates an error.

Group By ALL fieldList

• All distinct values generate a record even if no records for a particular value passed the where clause.
• If no records pass the where clause for a distinct value, NULLs are inserted in the aggregate fields for that distinct value.
• (Therefore) the ALL clause is pointless if no where clause is specified.

Group By fieldList WITH ROLLUP

• Places a summary record for everything in the result set. This record contains NULLs for all the grouped by fields.
• Create summary records in the result set of each group by sequentially  removing fields from the RHS of  fieldList. Removed fields appear as NULLs in the summary records.
• GROUP BY ALL and WITH ROLLUP are incompatiable
• When using WITH ROLLUP fieldList is limited to 10 expressions.
• Use ORDER BY and the fact that NULLs order first to order the results set

For example:

Code	Output			Output Notes
Create Table #Test (f1 integer, f2 integer, f3 integer) Insert Into #Test Values (1,1,1) Insert Into #Test Values (1,1,2) Insert Into #Test Values (1,1,3) Insert Into #Test Values (1,2,1) Insert Into #Test Values (1,2,2) Insert Into #Test Values (1,2,3) Insert Into #Test Values (2,1,1) Insert Into #Test Values (2,1,1) Select f1, f2, sum(f3) from #Test group by f1,f2,f3 with rollup      order by f1, f2
	NULL	NULL	14	Grand Summary
	1	NULL	12	Summary record for f1=1
	1	1	1
	1	1	2
	1	1	3
	1	1	6	Summary record for f1 = 1, f2 = 1
	1	2	1
	1	2	2
	1	2	3
	1	2	6	Summary record for f1 = 1, f2 = 2
	2	NULL	2	Summary record for f1 = 2
	2	1	2
	2	1	2	Summary record for f1 = 2, f2 = 1

Group By fieldList WITH CUBE

• As per "WITH ROLLUP" except all combinations of fields in fieldList are used rather than just removing a field at a time.

Code	Output			Output Notes
Select f1, f2, sum(f3) from #Test group by f1,f2,f3 with cube      order by f1, f2
	NULL	NULL	14	Summary Record for everything
	NULL	NULL	4	Summary Record for something
	NULL	NULL	6	Summary Record for something
	NULL	1	8	Summary Record for f1 = *, f2 = 1
	NULL	1	3	Summary Record for something
	NULL	1	2	Summary Record for something
	NULL	1	3	Summary Record for something
	NULL	2	1	Summary Record for something
	NULL	2	2	Summary Record for something
	NULL	2	3	Summary Record for something
	1	NULL	6	Summary Record for something
	1	NULL	4	Summary Record for something
	1	NULL	12	Summary Record for something
	1	1	1
	1	1	2
	1	1	6
	1	2	1
	1	2	2
	1	2	3
	1	2	6
	2	NULL	2	Summary Record for something
	2	NULL	2	Summary Record for something
	2	1	2
	2	1	2

Code	Output			Output Notes
Select f1, f2, sum(f3) from #Test group by f1,f2 with cube      order by f1, f2  (f3 removed from grouping)
	NULL	NULL	14	Summary record for everything
	NULL	1	8	Summary record for f2 =1
	NULL	2	6	Summary record for f2 = 2
	1	NULL	12	Summary record for f1 = 1
	1	1	6	Aggregated result
	1	2	6	Aggregated result
	2	NULL	2	Summary record for f1 = 2
	2	1	2	Aggregated result

GROUPING(fieldName)

The GROUPING function makes WITH ROLLUP and WITH CUBE usable with data that may control NULLs. If the row is generated as the result of a WITH ROLLUP or WITH CUBE clause the function returns 1, otherwise it returns 0.

Code	Output					Output Notes
Select grouping(f1), f1, grouping (f2), f2, sum(f3)       from #Test       group by f1,f2 with cube       order by f1, f2
	1	NULL	1	NULL	14	Summary record for everything
	1	NULL	0	1	8	Summary record for f2 =1
	1	NULL	0	2	6	Summary record for f2 = 2
	0	1	1	NULL	12	Summary record for f1 = 1
	0	1	0	1	6	Aggregated result
	0	1	0	2	6	Aggregated result
	0	2	1	NULL	2	Summary record for f1 = 2
	0	2	0	1	2	Aggregated result

Normally, you are only interested in whether the record is a summary record or not, in which case the number of columns can be reduced using "|". E.g SELECT GROUPING(f1) | GROUPING (f2) as IsSummary.

Having

• HAVING supports up to 128 conditions and can reference the aggregate columns (whereas obviously the WHERE clause cannot)
• HAVING is evaluated after GROUP BY ALL fieldList so it removes groups preserved by ALL that don't meet HAVING.

Compute / Compute By

• Same field type and must-be-in-field-list restrictions as per Cube
• Can't include Count(*)
• Cannot alias sub-headings that are a result of aggregation, i.e. can't say Compute Sum(x) as SumOfX
• Cannot use Group By with Compute, use Compute expr By expr2 [,expr2 ...]
• Compute goes at the end of the statement
• Compute By can only contain terms from the Order By clause, and must be in the same order

Example:
    Select Product, Qty, OrderNo From OrderHistory Order By Product, OrderNo
    Compute Sum(Qty)

    > Product        Qty        OrderNo
    > --------------------------------
    > beans            10          1
    > beans            5            1
    > oranges         7            1
    >
    >                      sum
    >                      ----
    >                      22

Top n [PERCENT] [WITH TIES]

• When calculating n% of the table, SQL rounds up
• An Order By is normally required to make TOP meaningful
• Specify WITH TIES to include records that are equally tied with the last value, but would otherwise cause more than n (or n%) records to be returned. For example, "SELECT TOP 5 WITH TIES x From (table containing values 1,2,2,3,4,4,4,5,6) ORDER BY qty" would return 1,2,2,3,4,4,4.

SQL default is "not null", ANSI default is allow nulls.

SET ANSI_NULLS controls interpretation of null values. When ON, expr = NULL and expr <> NULL always returns false. ANSI_NULLS is ON by default. (Note: This handling of nulls is not the same as VFP which handles NULLs correctly, i.e. expr = NULL and expr = NULL returns NULL)

xxC2L3

MSDTC (Microsoft Distributed Transaction Coordinator)

• Invoked with BEGIN DISTRIBUTED TRAN[SACTION [name]]
• Requires a high-speed network as all updates must be committed to all databases in real-time.
• Requires that all of the distributed databases be available, else no updates are allowed. Therefore MSDTC is vulnerable to network and hardware faults.
• Works by sending transactions to every server and only commits if every server commits
• Can only be nested if OLE DB provider supports nested transactions
• Savepoints are not supported. If a save point is reference in the ROLLBACK command, it is simply ignored.

Replication

• Is really Transactional Replication.
• Provides data consistency, but not real-time consistency.
• Updates data at intervals on multiple databases.
• Allows resynchronisation of data following server/network failure.
• Works by sending transactions to one servers, then replicating to the other servers.

Distributed Queries

• Uses OLE DB
• Require the ANSI_NULLS and ANSI_WARNINGS options to be turned on. (Note: Query Analyzer has these on by default, but osql doesn't)
• ad hoc queries are "one-offs", reading data from remote source you do not expect to query repeatedly. ad hoc queries are supported via the OPENROWSET function.
• Linked Server queries are used when data from remote sources needs to be accessed repeated over time. A Linked Server is an OLE DB data source that has been registered with SQL Server. By default, queries are processed on the local machine, but can be performed on the remote machine by using pass-through queries. Information about linked servers is stored in the sysevers and sysoledbusers system tables.

OPENROWSET

• Typically used for adhoc queries as it is simply placed in the FROM clause
• Syntax is FROM OPENROWSET('providerName', connectionString, objectOrQuery)
  where:
  • providerName is the OLE DB provider friendly name.
  • connectionString has two possible formats: 'datasource';'userId';'password' (as they sound) or 'providerString' (basically, whatever the OLE DB provider wants it to be). Note that in the first format, the semi-colons are between the quotes.
  • objectOrQuery is either a object name (normally a tablename) such as x.y.Employee, or a query (normally a select statement). Note that the object name is not enclosed in quotes, while a query is. When using object names this must be fully qualified if the data source provides multiple schemas. For example, Employee is enough for an access OLE DB provider, but a SQL OLE DB provided would require Nothwind.dbo.Employee (note the 4th reference is not required as we've already limited it to a particular server in the datasource parameter)

providerName	datasource	Description
SQLOLEDB	SQL Server Network Name	SQL Server 7.0
Microsoft.Jet.OLEDB.4.0	Full path and filename of an access database	Access
MSDAORA	SQL* Net alias	MS provided OLE DB for Oracle
MSDASQL	System DSN or ODBC connection string	Any ODBC datasource

Examples:

SELECT  SomeRemoteTable.*
    FROM OPENROWSET('SQLOLEDB', 'SPIKE_NT';'sa';'',
    'select productid from northwind.dbo.products') as SomeRemoteTable
 

SELECT SomeRemoteTable.*
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb';'Admin';'',
    Orders) AS SomeRemoteTable

Linked Servers

• Links are established through sp_addlinkedserver and dropped through sp_dropserver (note: sp_dropserver will fail unless all the logins mapped through sp_addlinkedsrvlogin have been dropped through sp_droplinkedsrvlogin first)
   
• sp_addlinkedserver syntax is:

  sp_addlinkedserver  @server='NameToCallLinkedServerWhenCreated',
                                  @srvproduct = 'OLE DB Product Name',
                                  @provider = 'OLE DB Provider Name (as per OPENROWSET)',
                                  @datasrc = 'Data Source Name (as per OPENROWSET)',
                                  @location = '
                                  @provider_string = '
                                  @catalog = 'Catalogue (SQL Database Name)'

  Most parameters are optional, as is the "@xxx=" portion of commands (if dropped, position must be maintained). When connecting to SQL Server, only the server & srvproduct names are required.

  Examples:

sp_addlinkedserver 'LONDON', 'SVR_LDN_02'

sp_addlinkedserver 'test', 'Microsoft Access', 'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb'

• By default, the system will attempt to log the user on to the remote data source using the users current ID & password. A different user name and password can be specified using the sp_addlinkedsrvlogin stored procedure:

  sp_addlinkedsrvlogin @rmtsrvname = 'LinkedServerName',
          @useself = 'TRUE|FALSE',        -- True = use userId/pwd supplied in this command, False = use current credentials
          @locallogin = 'LocalUserName', -- If NULL, the userId/pwd supplied will be used by all users, otherwise just the specified user
          @rmtuser = 'UserName',
          @rmtpassword = 'Password'

  Example:

          sp_addlinkedsrvlogin 'test', 'false', NULL, 'Admin', ''
   

• Configuration Options for Linked Servers are set through sp_serveroption @server = 'server', @optname = 'optionName', @optvalue = optionValue (no quotes on last parameter). Valid optionName values are:
  • collation compatible
    Tells SQL Server that the linked server uses the same collation sequences as the local server and therefore it can ask the provider to perform comparison operations rather than performing them all locally.
     
  • connection timeout
    Time-out value for connecting to a linked server. If 0, uses the sp_configure default ?? uses the local or the remote ??
     
  • query timeout
    Time-out value for queries passed to a linked server. If 0, uses the sp_configure default ?? uses the local or the remote ??
     
  • data access
    Required to make a SQL Server installation act as a linked server. If this extra step is not done, the remote server cannot be used for distributed transactions.
     
  • rpc
    Enables RPC from the local server
     
  • rpc out
    Enables RPC to a linked server
     
• sp_linkedservers returns a list of servers linked from the local server.
   
• sp_catalogs 'LinkedServer' returns a list of databases (for SQL Server)
   
• sp_indexes 'LinkedServer' returns a list of indexes. Similar stored procedures include: sp_foreignkeys, sp_tables_ex & sp_columns_ex
   
• Objects in linked servers are referenced using a four part conventions: Server.Database.Owner.Object (or more generical terminology: Server.Catalog.Schema.Object). Once linked, this convention can be used directly, for example:
  
          SELECT * From LONDON.Northwind.dbo.Orders
  
          EXEC LONDON.Northwind.dbo.sp_help
  
  The usual ".." syntax can be used to default parameters, or where parameters do not exist. For example, Access does not support database and owner, so would say SELECT * From test...Orders.
   
• Not all Transact-SQL commands can be used with Linked Servers. Specific exclusions include:
  • CREATE, ALTER and DROP statements
  • Specifying a remote table as the table to be created as the result of a SELECT INTO.
  • Using ORDER BY on data received from a remote table if the return field list contains any text or image (or non-SQL Server equivalent large objects) fields.
  • The READTEXT, WRITETEXT and UPDATETEXT commands
  • Anything else a specific OLE DB providers picks to exclude.
     
• OPENQUERY(LinkedServer, 'Query') performs a pass-through query. The results of a OPENQUERY can be used in place of a table name in a select statement (e.g. SELECT * FROM OPENQUERY(test, 'SELECT * FROM Orders')). If the OLE DB provider supports it, OPENQUERY can also replace tables in INSERT, UPDATE and DELETE statements (e.g. DELETE FROM OPENQUERY(LONDON, 'Northwind.dbo.Orders')).
   
• A transaction begun with "BEGIN TRANSACTION" is upgraded to "BEGIN DISTRIBUTED TRANSACTION" automatically if data is updated on a linked server.

Creating a Physical Database

There are 3 types of database files:

• Primary Data File (*.mdf)
  One per database.
  Contains all the system tables
• Secondary Data File (*.ndf)
  Zero to many per database
• Log Files (*.ldf)
  One to many per database

Sizes:

• Data is allocated in 8K pages (as opposed to 2K in SQL 6.5) rasing the rowsize limit from 1962 to 8060 (8094 is the maximum space used by multiple rows)
• 128 page = 1MB
• 8 pages = 1 extent (therefore 16 extents = 1MB)
• Objects and indexes are stored in extents. Up to 8 small objects can share an extent (in which case the extent is called a mixed extent).
• When a table hits 8 pages / 1 extent it gets it own extents, called Uniform extents
• When a new database is created the model database (and all it's settings) are copied into it automatically. This means the minimum size of a new database is at least the size of the model database.

Misc:

• Normally all data modifications go through the transaction log, but it is possible to turn off logging for SELECT INTO... and bcp.
• Watch out for write-catching disk controllers
• If not using RAID disks place data and transaction log on separate physical disks. 
• Databases cannot be shrunk in size lower than the original creation size.
• Use DBCC SHRINKFILE (or the GUI) to shrink a specific file.

Creating Databases

• Must have been granted creation rights or be a member of sysadmin or dbcreator.
• Required info about databases is stored in master.sysdatabases. Therefore backup master whenever a database is added, modified or dropped.
• Syntax:
  
  CREATE DATABASE DatabaseName
      [ON [PRIMARY]
          (NAME=mdf/ndf name, FILENAME = filename [, SIZE = size] [, MAXSIZE = size] [, FILEGROWTH = growth] ) x N
      [LOG ON [
          (NAME=ldf named, FILENAME = filename [, SIZE = size] [, MAXSIZE = size] [, FILEGROWTH = growth] ) x N ]
      [FOR RESTORE]
  
  Where:
  • filename is a local OS filename (network are names not allowed)
  • size is in units dependent on a suffix of KB, MB (the default) & GB.
    If size is not supplied for MAXSIZE, no limitation on growth are set.
    xx are GB&TB valid in 7?
  • growth type is determined by the suffix. KB/MB specify a size while % indicates a percentage. KB specifications are rounded to the nearest 64K boundary above 0. 10% is the default.
    

  Note that just about everything is options. For example, "CREATE DATABASE naff" creates (on my test system) a 0.75MB datafile called naff.mdf and a 0.25MB datafile called naff_log.ldf.

Deleting Databases

• DROP DATABASE name [, name2 [, name...]]
  Note that it is possible to drop more than one database when dropping through SQL. You can only drop one at a time though Enterprise Manager.
• If you drop a database that is the default database for a particular login, that login will no longer have a default database.
• You cannot drop master, model or tempdb. You can drop msdb, but don't! (see below)
• A database cannot be deleted if:
  • It's being RESTOREd
  • It is open for reading or writing by any user
  • It has published any of it's tables (replication)
• You can drop msdb, but if you cannot continue to use:
  • SQL Server Agent
  • Replication
  • SQL Server Web Wizard
  • DTS
• When a database is dropped its database files are automatically deleted.

<--- Wrong section?

Viewing Database Information

xx Need V7 for GUI instructions

Database Options

Syntax:

sp_dboption ['database' [, 'option_name' [, 'value']]]

Where:

When value is "on/off" its true/false rather than 1/0. e.g. sp_dboption 'Northwind', 'trunc. log on chkpt.', true

---->

ALTER DATABASE

Alters a database file rather than properties of the database itself.

ALTER DATABASE database command

where:
database is the name of the database the file being modified is associated (or will be associated) with.
command is one (and only one per ALTER DATABASE statement) of:

• ADD FILE (filespec) [TO FILEGROUP filegroup][FOR RESTORE]
  Adds a database file to the database. xx doc filegroup and FOR RESTORE
• ADD LOG FILE (filespec)
• REMOVEFILE logicalFilename
• MODIFY FILE (filespec)
  
• CREATE FILEGROUP filegroupName
• DROP FILEGROUP filegroupNane
  Note that for files its "REMOVE" but for groups its "DROP"

filespec is always enclosed in round brackets and has the syntax:

• (NAME = 'logicalFilename' [, FILENAME = 'physicalFilename'] [, SIZE = size] [, MAXSIZE = {size | UNLIMITED}] [, FILEGROWTH = growth])

Note that SIZE is really "minimum size" and that this figure cannot be decreased by ALTER DATABASE (or DBCC SHRINKFILE). Unlike previous size parameters to change an existing file MAXSIZE to unlimited use the keyword UNLIMITED rather than omitting the parameter.

 

Log File Performance

1. Use Performance Monitor:

2. Watch out of instructions or processes that:

1. Performed (logged) bulk loads
2. Modify large amounts of data (especially data that is indexed or involves text and image fields (e.g. WRITETEXT & UPDATETEXT))

3. You can quickly reduce the amount of space used in the log file by backing up or truncating the log, but you cannot force the log file to be shrunk there and then (it is a deferred operation).

DBCC SHRINKFILE (fileNameOrID [, targetSize] [, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}]]

• Can shrink a data or log file, but not below the figure expressed in the initial CREATE DATABASE or last ALTER DATABASE.
• Can be used to empty a datafile (i.e. move all the data into the remaining data files) so that the data file can be drop with ALTER DATABASE
• Alternatively, use the sp_dboption autoshrink to automatically shrink files.
• targetSize (in MB) is the size to which the system should attempt to shrink the file. If targetSize is not specified the file is shrunk as much as possible
• EMPTYFILE move all data out of the nominated file into other files in the same filegroup. Empty files are locked such that no new data can be added to them. Empty files can be dropped through ALTER DATABASE database REMOVE FILE file.

DBCC SHRINKDATABASE ('databaseName' [, targetFreeSpace%] [, {NOTRUNCATE | TRUNCATE])

• targetFreeSpace% means "reduce the physical size of the database so that there is only targetFreeSpace% free space left"
• targetFreeSpace% is ignored when used with NOTRUNCATE
• targetFreeSpace% causes data to be move from the end to the front of files
• With only databaseName specified DBCC returns a cursor containing current size (in pages), minimum size (in pages. This is the minimum the database could ever be shrunk to (i.e. the page equivalent of the SIZE argument in CREATE/ALTER DATABASE), used pages and estimated pages (the estimated minimum number of pages required to hold the database)
• A database cannot be shrunk under the size of the modal database.

Filegroups

• Filegroups only apply to data files and not to log files.
• All system tables are created in the primary filegroup
• By default the default filegroup is the same as the primary filegroup, but they can be different.
• Members of dbowner can change the default filegroup

sp_helpfile ['logicalFileName']	Returns information on the named data file or all data files (in the current database)
sp_helpfilegroup ['fileGroup']	Returns information on the filegroup or all file groups (in the current database)

<---- Wrong place?

Database Catalog

Metadata contained in each database containing information about that database. Even master contains a database catalog

System Catalog

Metadata contained in master that is about or is for all databases. All system tables are prefixed with "sys".

• sysxlogins (master only)
  One record per login account that can connect to SQL Server. Accessed through syslogins view.
   
• sysmessages (master only)
  One record for each error message SQL can produce
   
• sysdatabases (master only)
  One record per database on the server
   
• sysusers (in every database)
  One record per user, group or role
   
• sysobjects (in every database)
  One records for each object (table, rules, stored procedures etc.) in the database

To ensure future compatibility (and SQL 92 compatibility) access the system catalog tables through the information schema views only:

• Information_schema.tables
• Information_schema.columns
• Information_schema.table_privileges
• etc.

---->

Pre-defined Data Types

Note: (length) is actually optional. For example, in the case of char it defaults to 1 character

User-defined Data Types

• sp_addtype userDefinedType, 'systemType' [, NULL | NOT NULL]
  e.g. sp_addtype postcode, 'char(8)', NULL
  Note that the NULL | NOT NULL setting can be overridden when the user defined type is used in column definitions
  NOT NULL is the default for SQL (but can be changed)
   
• sp_droptype userDefinedType
  e.g. sp_droptype postcode
   
• Like everything else, user-defined data types in the modal database are copied to all new databases
   
• Existing user defined data types can be listed using sp_help or by querying the information_schema.domains view.

Tables

• CREATE TABLE tablename (field, field, ...)
  where field = fieldname datatype [NULL | NOT NULL]
   
• DROP TABLE tablename
  MS says a table cannot be dropped if other objects depend on it. For example, a base table cannot be dropped while views or triggers exist on it. However, when I try it, it drops just fine. It dfoe
  Use the sp_depends 'tablename' stored procedure to see if dependents exist.
   

• IDENTITY
  When using IDENTITY as part of field include (start, increment). e.g. CREATE TABLE test (id int IDENTITY(1,1) NOT NULL)
  Note that using IDENTITY does not guarantee uniqueness (that requires an index)
  Only one IDENTITY column is allowed per table
  IDENTITY columns cannot be declared NULL
  IDENTITY columns can only be used with numeric datatypes (int, smallint, tinyint, numeric & decimal). Numerics and decimals can only be used with 0dp.
  IDENT_SEED('tablename') returns the seed (start) number
  IDENT_INCR('tablename') returns the increment
  @@IDENTITY returns the identity value of the last row inserted
  IDENTITYCOL can be used as a synonym for the field name with the IDENTITY property. e.g. select IDENTITYCOL from mytable.
   
• uniqueidentifier
  when the datatype uniqueidentifier is is often auto-populated using the NEWID() function (which returns a 128-bit GUID). For example: CREATE TABLE myTable (id uniqueidentifier NOT NULL DEFAULT NEWID())
   
• ALTER TABLE tablename ADD fieldname datatype [NULL | NOT NULL]
   
• ALTER TABLE tablename DROP COLUMN  fieldname datatype
  Note:
  • Its ADD (nothing) but DROP COLUMN!
  • You can't drop a column that is indexed or has constraints
     
• ALTER TABLE tablename ALTER COLUMN fieldname newDatatype [NULL | NOT NULL]
  Note: newDatatype is still required even if you just want to change the acceptance of NULLs
   

 Integrities (RUDE)

• Referential (Inter-table)
  Foreign Key manipulations
  Associated Constraints: FOREIGN KEY
   
• User-defined (Everything else)
  Business Rules implemented via constraints, triggers & stored procedures
  Associated Constraints: CHECK
   
• Domain (Field)
  Are nulls allowed
  Datatype restrictions
  Format restrictions
  Value lists / ranges
  Associated constraints: DEFAULT, CHECK, FOREIGN KEY
   
• Entity (Row)
  Primary Keys manipulations
  Is row deletion allowed
  Associated Constraints: PRIMARY KEY, UNIQUE

plus:

Data (All of the above as one group)
Declaratively by constraints and field definitions and
Procedural with defaults, rules, triggers and stored procedures.

Constraints

• MS "preferred method of enforcing data integrity"
   
• Are SQL92 compliant
  
• Column-level constraints are defined on a single column (e.g. DEFAULT constraints)
   
• Table-level constraints are based on multiple columns
   
• Constraints (except FOREIGN KEY presumably) cannot reference columns from other tables
    
• Constraints must have database-unique names. Information about a particular constraint can be obtained through the sp_helpconstraint tablename or the sh_help constraintName stored procedure.
   
• Are more efficient than triggers as trigger are run only after the data modifying statement has executed and therefore have to rollback on problem detection.
    
• ALTER TABLE table ADD CONSTRAINT name DEFAULT value FOR field
  E.g. ALTER TABLE myTable ADD CONSTRAINT myConstraint1 DEFAULT 'myValue' FOR myField
  Note that value can be a expression such as NEWID() and default clause can be used on field directly (e.g. ...myField int DEFAULT '12'...)
  Only one DEFAULT clause can be specified per column and cannot reference other columns.
  Only apply to insert statements
   
• ALTER TABLE table CONSTRAINT name CHECK(bExpr)
  E.g.:
     ALTER TABLE myTable CONSTRAINT myConstraint2 CHECK (myfield > 10)
     ALTER TABLE myTable CONSTRAINT chkPhone CHECK (localPhoneNo Like '(01454)[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
  CHECK constraints are evaluated with every INSERT and UPDATE statement.
  CHECK constraints can include SQL, but not subqueries or select statements. Think WHERE clause.
  CHECK constraints cannot be used with timestamp, uniqueidentifier or identity columns.
   
• ALTER TABLE table CONSTRAINT name PRIMARY KEY [CLUSTERED | NON CLUSTERED] (field [, field...])
  E.g. ALTER TABLE myTable CONSTRAINT myConstraint3 PRIMARY KEY (myKeyField)
  Adding a PRIMARY KEY constraint automatically creates an associated unique index (which is dropped when the constraint is dropped).
  A PRIMARY KEY column must be declared NOT NULL (else the constraint cannot be added)
   
• ALTER TABLE table CONSTRAINT name UNIQUE [CLUSTERED | NON CLUSTERED] (field [, field...])
  E.g. ALTER TABLE myTable CONSTRAINT myConstraint4 PRIMARY KEY (myCustomerNumberField)
  Adding a UNIQUE constraint automatically creates an associated unique index (which is dropped when the constraint is dropped) A unique constraint allows nulls (but only one, obviously).
  
   
• ALTER TABLE table CONSTRAINT name FOREIGN KEY (field [, field...]) REFERENCES otherTable (field [, field...])
  E.g. ALTER TABLE myTable CONSTRAINT myConstraint5 FOREIGN KEY (customerID) REFERENCES customer (ID))
  The number of fields and field datatypes must match in both tables.
  No indexes are automatically created for FOREIGN KEY validation but indexes on those fields are normally a food idea.
  If you obmit the "FOREIGN KEY (...)" section, the REFERENCES section must refer to the current table.
  The fields listed in otherTable must fulfil primary key or candidate key restrictions (i.e. be uniquely indexed)

There is no "ALTER TABLE... ALTER CONSTRAINT...". You have to drop the old constraint then add the new one.

Examples above are restricted to ALTER TABLE for clarity. They can be used in other statements, such as CREATE TABLE:

CREATE TABLE myTable (id int NOT NULL, name char(50) NOT NULL, dob datetime, CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT chk CHECK(name IN ('Dave', 'John')), CONSTRAINT dob CHECK (dob <= GetDate()))

When used in a CREATE TABLE statement the CONSTRAINT clause can either follow on from the field definition or be listed separately (in which case it needs to be comma delimited to prevent accidental connection to the last field). For example:

CREATE TABLE myTable (id int NOT NULL CONSTRAINT pk PRIMARY KEY(id), name char(50) NOT NULL CONSTRAINT chk
        CHECK(name<>''))

is legal and:

CREATE TABLE myTable (id int NOT NULL CONSTRAINT pk PRIMARY KEY(id), name char(50) NOT NULL CONSTRAINT chk
        CHECK(name<>''))

is legal (and achieves exactly the same results), but:

CREATE TABLE myTable(id int NOT NULL, name char(50) CONSTRAINT pk PRIMARY KEY(id) CONSTRAINT chk CHECK(name<>''))

has two errors (firstly the pk constraint is trying to place a constraint on the name field based on the id field and secondly the two constraints need to be separated with a comma)

For performance purposes, checking of existing data can be skipped when adding a new constraint using the WITH NOCHECK option. WITH NOCHECK can only be used with CHECK and FOREIGN KEY. WITH NOCHECK should be placed in the command before the constraint definition.

Constraints can be turned off (for example, before a big data upload) by ALTER TABLE table NOCHECK CONSTRAINT constrantName. They can be re-enabled later using the ALTER TABLE table CHECK CONSTRAINT constrantName command.

Defaults

• Are mainly for backwards compatibility (use DEFAULT constraints instead)
   
• Are attached to a user-defined datatype or to specific columns. Define once, use many
    
• CREATE DEFAULT default AS constantExpression
  sp_bindefault default, object_name
  sp_unbindefault object_name
  DROP DEFAULT default
  
  E.g.:
      CREATE DEFAULT def_phone_no AS '(01454) 012345678'
      GO
      EXEC sp_bindefault N'def_phone_no', N'personel.phone_no'
   
• If a default is bound to a user-defined datatype it is bound to all column instances of the user-defined datatype.
   
• Only one default can be bound to a specific user-defined datatype at any time
   
• Only one default can be bound to a specific column at any time

Rules

• Are mainly for backward compatibility (use CHECK constraints instead)
   
• Are attached to a user-defined datatype or to specific columns. Define once, use many
   
• Can contain any expression valid in a SQL WHERE clause
   
• Can refer to the new value for the column by using @anything
   
• CREATE RULE rule AS sqlWhere
  sp_bindrule rule, objectName
  sp_unbindrule rule, objectName
  DROP RULE rule
  
  E.g.:
  CREATE RULE ruleCity AS @city IN ('Bristol','Birmingham','Cardif')
  GO
  EXEC sp_bindrule N'ruleCity', N'typeCity'
   
• If a rule is bound to a user-defined datatype it is bound to all column instances of the user-defined datatype.
   
• Only one rule can be bound to a specific user-defined datatype at any time
   
• Only one rule can be bound to a specific column at any time

Physical Storage of Data

Tables that have a clustered index store the data in key order. Pages are stored in a doubly-linked list (i.e. each page has a reference to the page in front and the page after). By altering the links of existing pages new pages can be inserted into the sequence. The tables always have associated index pages because of the clustered indexes.

Data pages for tables that do not have a clustered index are stored in  heaps. Data is stored in no particular order and new pages are just added to the heap. Heaps only have index pages if indexes are specifically added to the heap.

When a page is full and SQL is asked to add more data to it it creates a new page and move approximately half the records from the old page to the new page. This process is called a page split. Page splitting occurs for both data and index pages.

Table Scan

A table-scan is a data location method that simply starts at the first data page in a table and works through the pages in sequence. Every row of ever page is examined and those that meet the search criteria extracted. Table scans are efficient when dealing with small tables or when a large proportion of the table is to be returned.

Indexed Access

Indexed access navigates the index tree to locate the index entries for the records to be returned. The corresponding rows from data pages are then read and returned. Indexed Access is efficient when dealing with single rows or small percentages of the entire file. ?? For reasons I don't fully understand, if a clustered index exists on the table then all further (non-clustered) indexes are based on that index.

Query Optimiser

Is the component responsible for determining whether to use a table scan or indexed access. The decision to use Indexed Access depends on more than whether the corresponding index exists or not.

Composite Indexes

• A query where only the fields covered by a compound index are referenced. Such queries do not need to access data pages and the values are instead read directly from the index.
   
• Up to 16 fields can be included in a composite index but the total length cannot exceed 900 bytes.
   
• First field in a compound index has the highest significance (i.e. 1st column is highest order column) therefore should be the term that most reduces the number of records.

Are Indexes Worthwhile?

Indexes generally accelerate queries that join tables, sort data or group data. Indexes are required to support column uniqueness. However, indexes also consume disk space, incur overheads and require automatic updating when modifying indexed data. In fact, on really small tables, it can take longer to read the index and locate the record (Indexed Access) than to read the entire table (Table Scan).

In short:

• Index Primary Keys, Foreign Keys and any other columns used to extract data
• Index columns that are searched by key ranges
• Index columns that often used to sort returned data by
• Do not index columns that are seldom used to select, sort or group by
• Do not index columns that contain few unique values (I think this is dubious recommendation by MS). 
• In a table with a clustered index, all other indexes are based "on-top" the clustered index. As such, if the clustered index key is large the repeated values in the non-clustered indexes will also be large. Therefore, pick small values for clustered indexes.

Miscellaneous Information about Indexes

• Indexes are always in ascending order
• Bit, text, ntext and image fields cannot be indexed.
• Execute sp_helpindex indexName for index information

Index Creation

• CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX indexName ON table (column [, column...])
  [WITH [PAD_INDEX] [,FILLFACTOR=fillFactor] [,IGNORE_DUP_KEY] [,DROP EXISTING] [STATISTICS_NORECOMPUTE]]
  [ON fileGroup]
• User must be table owner to issue CREATE INDEX
• Views cannot be indexed
• Definitions stored in sysindexes and include information like when the index was last rebuilt.
• FILLFACTOR= specifies the percentage of a index leaf page that should be filled when an index is created or rebuilt. By leaving space in the index free more inserts and updates can be performed before a page split is necessary, improving performance at the cost of an initially larger number of index pages. Note that:
  • Because the leaf pages in an clustered index are actually data pages, fill factor actually effects the table size in these cases.
  • Low FILLFACTOR= for OLTP (more efficient inserts & updates), high for DSS (more efficient reads)
  • A FILLFACTOR= of 0 is like 100 except some room is left in the upper parts of the index tree and room is left for one new record in each non-leaf index page.
  • Default FILLFACTOR= can be set through sp_??
  • When calculating the number of records that can be fitted into a page given a particular FILLFACTOR SQL rounds the number of records up.
• PADINDEX is:
  • Only used in conjunction with FILLFACTOR=. It cannot be used on it's own
  • Tells SQL to apply the value specified for the FILLFACTOR= for non-leaf as well as leaf pages
• Irrespective of whether PADINDEX is specified or how high FILLFACTOR= is, SQL always leaves room for one row of the maximum index row size in each non-leaf page.
• There are never less than two rows in a non-leaf index page irrespective of how low FILLFACTOR= is set. ??What if there is only one record in the table!
• SQL requires 1.2 times the table size in temporary space to create a clustered index.

Index Destruction

• DROP INDEX indexName [, indexName...]
• Cannot drop a index that was created as part of a constraint. Dropping the constraint automatically drops the index.
• Because non-clustered indexes are based on clustered indexes (if there is one), dropping the clustered index causes all other indexes to be rebuilt.
• You can only drop indexes from the current database (i.e. you must change database first)
• DROP INDEX cannot be used on system tables.

BCP

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-C code_page] [-t field_term] [-r row_term]
    [-i input_file] [-o output_file] [-a packet_size]
    [-S server_name[\instance_name]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

-m	Max errors before bcp if terminated. Defaults to 10
-f	Format file
-e	Error file where more details error information is logged
-F	Row number in source file to start from
-L	Row number in source file to stop after
-b	Batch size (in rows). If not specified, all rows are read and committed in a single transaction
-n	Use native datatypes ?? No idea
-c	Use character datatype. \t is used for field delimiting, \n for row terminator
-w	Use UNICODE datatype. \t is used for field delimiting, \n for row terminator
-N	Use native datatypes, except for char where UNICODE will be used instead. ?? No idea
-V x	Use datatype from a particular version of SQL Server (x = 60, 65 or 70). ?? Used with dates /ODBC ??
-6	Equivalent to -V 60 / -V 65
-q	Equivalent to SET QUOTED_IDENTIFIERS ON
-C x	Specify code page (obsolete. Use -f and specify in format file instead). x= ACP, OEM, RAW or code page number
-t	Specify field delimitator
-r	Specify row delimitator
-i	File containing responses to prompts from bcp
-o	File to contain output from bcp
-a	Packet size. Default is 4096
-S	Server Name
-U	User Name
-P	Password
-T	Use Trusted connection
-v	Display version info for bcp
-R	Use PC's Regional Settings when interpreting data
-k	Retain NULLs and don't use any default values
-E	Use values in import file for identity columns rather than SQL Server automatically generating them, ?? SQL2K only ??
-h	Import hint.ORDER(...), ROWS_PER_BATCH = x, KILOBYTES_PER_BATCH = x, TABLOCK, CHECK_CONSTRAINTS, FIRE_TRIGGERS.

Non-logged bulk copying can only occur when:

• "select into/bulk copy" options is set to true
• destination table has no indexes OR destination table has indexes, but no data
• destination table is not replicated
• destination table can be locked (in the case of bcp, specify the -h"TABLOCK" flag)

SQL7 BCP uses ODBC (previous versions used DB-Library)

DTS

• Transfers data between any OLE DB (inc. SQL Server, obviously) and OLE DB, ODBC or text format.
• Consists of: DTS Import and Export Wizards, DTS Designer, dtswiz & dtsrunCLIs, DTS node in Enterprise Manager
• Can be scheduled
• Triggers, Stored Procedures, Rules, Defaults, User-defined data types and Constraints (i.e. all DML entities bar table schemas) can only be transferred between SQL7 databases.
• Can export the results of queries, including multi-join queries.

Overview

• Although its actually the same beast, its called Index Server under IIS and MS Search Service under SQL 7.
   
• Only installable on Windows NT Server (not true for SQL 2000) and then only via the custom install option.
   
• Is separate to SQL Server. Full-text indexes are not stored in SQL Server (not in tables or any other way) but in the ftdata directory under the installation location. Normally this would be c:\mssql\ftdata. Although the files are stored separately they are administered through SQL Server.
   
• Full-text indexes are not automatically populated or updated. Updates are normally scheduled via xx and typically require significantly more time to update than SQL indexes. This is normally justified along the lines of "full-text searches are less accurate and aren't primary keys so it don't matter that much" (no I don't agree with that excuse!)
   
• One catalogue can contain multiple indexes from the same database,  but it cannot contain indexes from multiple databases. If you want, you can have multiple catalogues for the same database (but an index cannot be shared between catalogue)
   
• A table can only have one full-text index
   
• A table must have an uniquely indexed field (effectively a primary key)
   
• Data outside of SQL Server tables (for example, documents) can also be indexed
   
• A list of "noise" words to be ignored in stored in plain text under the ftdata director in sqlserver\config\noise.dat. 
   
• If a table has been set up for incremental population, full population will occur if the table structure or configuration has been modified or the table lacks a timestamp column
   
• Full-text indexes can be deactivated, but are not actually deleted until the next full population procedure.
  
• Data from deleted columns is still matched in queries that specify an all-columns (*) search.
  
• MS Search service must be running for use of full-text functions. This can be done through Enterprise Manager (via the Full-Text Catalogues object)h, SQL Server Service Manager, NET START mssearch or Control Panel -> Services.

The following stored procedures are used to configure full-text indexing:

Name	Description
sp_fulltext_database	Create or remove full-text catalogues for a database
sp_fulltext_catalog	Create or remove a full-text catalogue Start / Stop indexing a catalogue
sp_fulltext_table	Add or remove a table from full-text indexing
sp_fulltext_column	Add or remove a column from a table for full-text indexing
sp_fulltext_service	Control the properties and behaviour of the MS Search service

The following stored procedures are used to examine full-text indexing:

Name	Description
sp_help_fulltext_catalogs	Returns info on a catalogue
sp_help_fulltext_tables	Lists tables involved in full-text indexing
sp_help_fulltext_columns	Lists columns involved in full-text indexing

Full-text Queries

• CONTAINS(field, 'SearchExpr') / CONTAINSTABLE(table, field, 'SearchExpr')
  Where field is the field to be searched or * if all full-text indexed fields should be checked (table prefixes can be used e.g. Employees.*)
  CONTAINS returns .T./.F. (i.e. suitable for a where clause) while CONTAINSTABLE returns a table (i.e. suitable for a from clause). When a table is returned it contains two columns, KEY and RANK. Key contains the full-text key values and RANK contains a score from 0 to 1000 (with 1000 being high).
  
  SearchExpr can either be simple (e.g. just a word to be matched) or complex:
  

  SearchExpr	Description
  "Phrase"	Phrases can be searched for by enclosing the text in quotes. e.g. WHERE CONTAINS(*, '"A fist full of dollars"') Phrase searches are case insensitive and noise words and punctuation are ignored. For example, the above will match "A fist? Full of dollars!".
  "Phrase*"	Phrases beginning with Phrase followed by zero or more additional characters. Each word of Phrase is considered a separate prefix so "fist full*" really means "fist* full*". Example: WHERE CONTAINS(*, "fi*")
  FORMSOF(INFLECTIONAL, "Phrase")	Matches plurals and different forms of verbs.
  AND, AND NOT, OR	As you would expect. Note that AND is evaluated before OR and NOT can only be included as part of AND NOT (i.e. it is impossible to say CONTAINS(*, 'NOT "Dave"').
  "Phrase1" NEAR "Phrase2" "Phrase1" ~ "Phrase2" "Phrase1" NEAR "Phrase2" NEAR "Phrase3"...	Returns a higher score the nearer each term is the next term.
  ISABOUT(term WEIGHT(value))	Where term is any of the above and WEIGHT is the associated weighting. For example: CONTAINS(Description, 'ISABOUT (good WEIGHT (.8), bad WEIGHT (.4), ugly WEIGHT (.2) )' )

   

• FREETEXT(field, 'text') / FREETEXTTABLE(table, field, 'text')
  Where field is the field to be searched or * if all full-text indexed fields should be checked.
  FREETEXT returns .T./.F. (i.e. suitable for a where clause) while FREETEXTTABLE returns a table (i.e. suitable for a from clause) 
  
  text is broken up by SQL into individual words and phrases. Each word and phrase is given a weighting then searched for in all its forms (i.e. in all its inflections). text is just text. Keywords included in text are just treated the same as all other words.

Maintaining a Database

Data Fragmentation & Rebuilding indexes

As page splits occur data and index pages no longer appear sequentially on disk. Because not all pages are updated equally, the FILLFACTOR per page rapidly becomes different between pages. This increases the number of reads required to complete a query. This is called data fragmentation.

The extent of data fragmentation can be seen by issuing the DBCC SHOWCONTIG([tableID [, indexID]) statement. Example output from Northwind database using "declare @objid int / set @objid = OBJECT_ID('Order Details') / dbcc showcontig(objid))" (because "dbcc showcontig(OBJECT_ID('Order Details'))" doesn't work and dbcc showcontig('Order Details') is SQL2K only):

DBCC SHOWCONTIG scanning 'Order Details' table...
Table: 'Order Details' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 33.33% [2:6]
- Logical Scan Fragmentation ..................: 11.11%
- Extent Scan Fragmentation ...................: 16.67%
- Avg. Bytes Free per Page.....................: 673.2
- Avg. Page Density (full).....................: 91.68%

Where:

• Pages Scanned
  Number of pages in the table or index
• Extents Scanned
  Number of extents in the table or index
• Extent Switches
  Number of times DBCC had to change the extent while following pages links through the table or tindex.
• Avg. Pages per Extent
  Number of pages per extent
• Scan Density [Best Count:Actual Count]
  Scan Desity: Best Count / Actual Count. A figure of 100% indicates no fragmentation and minimum extent usage.
  Best Count: The number of extent switches that would occur if the data was not fragmented and only the minimum possible number of extents had been created.
  Actual Count: The number of extent switches that actually occurred (?? why isn't this the same as the figure for Extent Switches??)
• Logical Scan Fragmentation
  Percentage of pages that are not in sequential order.
• Extent Scan Fragmentation
  Percentage of extents that are not in sequential order.
• Avg. Bytes Free per Page
  Free space per page. Note that large record size can make this figure large because the remaining space is insufficient to store a whole record.
• Avg. Page Density (full)
  Percentage full. This is based on actual row size.
• Index ID
  If no index id is supplied in the DBCC statement, 0 if table does not have a clustered index (i.e. heap), 1 if it does

Rebuilding indexes reorganizes the pages. If its a clustered index, this includes the data pages. Pages and records are reallocated such that the required FILLFACTOR is restored.

Indexes can be rebuilt by:

• Using CREATE INDEX... DROP EXISTING (although you can't change a clustered index into a non-clustered index this way). For example CREATE INDEX myIndex On table1(field2) WITH DROP EXISTING, FILLFACTOR=65
• Using DBCC DBREINDEX('tableName' [, 'indexName' [, fillFactor]]) [WITH NO_INFOMSGS] to either rebuild a single index or all indexes for the specified table. WITH NO_INFOMSGS 
• Dropping the indexe and recreate it. This is not recommended due to the additional overhead.

System indexes (i.e. those on system tables) cannot be rebuilt.

Index Tuning

• Indicates an optimal index configuration for the database for a particular workload which can be implemented immediately, later (via SQL Server Agent), written out to a script or ignored.
• Provide Cost Analysis reports
• If there is a USE statement in the workload a GO must follow else no indexes will be recommended
• Does not recommend indexes for:
  • Cross-database reference (e.g. database..otherTable)
  • System tables
  • Columns that already have PRIMARY KEY or UNIQUE constraints

Users have to be in the sysadmin role to use the Index Tuning Wizard.

The Index Tuner can produce the following report:

• Index Usage (Recommended Configuration)
  Shows the percentage of queries that use each recommended index and the size of each recommended index
• Index Usage (Current Configuration)
  Shows the percentage of queries that use each existing index and the size of each existing index
• Table Analysis
  ??Top 100 most heavily used tables with percentage of workload usage both with recommended and existing indexes.
• Query Cost
  Estimated changes in the cost of the 100 most expensive queries using the recommended indexes rather then the existing indexes.
• Workload Analysis
  ??
• Tuning Summary Report
  ??

Statistics

• Column statistics are used by the query optimiser to determine whether a table scan or index scan is best for the query plan under construction
• Statistics are updated automatically (when a volume of records decided by SQL have changed), but can be created / maintained manually if desired (for example, to use a specific sample size when calculating the statistics)
• Statistics are generated automatically for indexed fields except that if an index includes multiple fields on the first field has statistics automatically created for it.
• MS book says "When you are creating statistics on larger tables, use the SAMPLE option if specific index usage by the query optimiser decreases" ["When creating statistics on large table, use the SAMPLE option if use of a specific index by the query optimiser decreases"]
• Both columns and indexes can have statistics. ??Whats the difference?
• Existing statistic information can be displayed using: DBCC SHOW_STATISTICS(table, target) where target is an index or column name

CREATE STATISTICS statsName ON table(field1 [, field2 [...]])
    [ WITH [FULLSCAN | SAMPLE sampleSize {PERCENT | ROWS}] [,] [NORECOMPUTE]

where

• FULLSCAN
  All rows are read to create statistics
   
• SAMPLE sampleSize PERCENT
  Only sampleSize% of rows are read to create statistics
   
• SAMPLE sampleSize ROWS
  Only sampleSize rows are read to create statistics
   
• NORECOMPUTE
  Prevents statistics being updated automatically later. This can also be achieved using the sp_autostats 'table', 'OFF' [, 'index']

If neither FULLSCAN or SAMPLE percentage PERCENT is included, SQL decides for itself how many rows to use.

DROP STATISTICS statsName removes statistics that you have finished with.

To update statistics either use the:

UPDATE STATISTICS table 
    [index | (statsName [, statsName2 [, ...]]]
    [WITH [FULLSCAN | SAMPLE sampleSize {PERCENT | ROWS},]
        [ALL | COLUMNS | INDEX ,]
        [NO RECOMPUTE]

After populating a table which had indexes prior to being populated it is recommended to manually update that tables statistics.

PROCE:

• Parse (Syntax Check)
• Resolve (Validate names and ownership permissions)
• Optimise (Index selection & join strategies)
• Compile (Generate executable form)
• Execute (Do it)

Ad hoc / Uncached queries follow PROCE. Cached queries PROCE only the first time, thereafter skipping PRO entirely, reads the Compile stage from the Procedure Cache and performs the Execute as before.

Query Plans

Form the PROC of PROCE. Defines access method and order (i.e. which tables first). Query plans can be seen graphically in Query Analyzer (options under Query menu) or programmatically using the following SET options (these can only be used in batch commands, i.e. not via ODBC or in Stored Procedures, and only one can be active at a time) :

• SET SHOWPLAN_TEXT {ON | OFF}
  Returns human readable (barely) explanation of the query plan
   
• SET SHOWPLAN_ALL {ON | OFF}
  Much expanded version of SHOWPLAN_TEXT designed for reading by graphical query tools but still (not really) readable by humans
   
• SET STATISTICS IO {ON|OFF}
  Returns IO information about the query, namely the following page counts: scan count, logical reads, physical reads and read-ahead reads

Caching

Compiled queries are stored in the procedure cache. Memory in the cache is freed based on least usage and compilation cost. SQL uses three forms of caching:

• Ad Hoc
  If the text of the submitted query exactly matches the text of a previously query that is still in the cache the cached query plan is used. To be considered an exact match both the data and the datatypes must match (for example "... and salary > $20000..." does not match "... and salary > 20000...".
• Parameterised (aka Caching by Specification)
  Query parameters can be specified when using the sp_executesql stored procedure. If another parameterised query of the same form (but the same or difference values in the parameters) is submitted the existing parameterised query plan will be used.
• Auto-Parameterisation
  SQL automatically tries to identify parameters in queries. If it can, it generates a parameterised query plan which obviously matches more flexibly to later queries than straight text match.

Optimiser Hints

Optimiser hints allow overriding of specific decision normally made by the query optimiser. For example:

    SELECT ... FROM ... WITH (INDEX(indexNumberOrName))

tells SQL to use the nominated index. (Specifying indexNumber as 0 tells SQL to use a table scan. If a clustered index is present specifying 1 tells SQL to use a clustered index scan. Specifying 1 if no clustered index is present will generate an error).

xx

SQL Server Performance Monitor

• Stored Procedures -> Look at SQLServer:Cache Manager
  Cache Hit Ratio, Cache Object Counts, Cache Pages, Cache Use Counts/sec
• Stored Procedures -> Look at SQLServer:Buffer Manager
  Cache Size (pages) ?? this is called "Procedural cache pages" on mine ??
• Stored Procedures -> Look at SQLServer:SQL Statistics
  SQL Compilations / sec, SQL Re-Compilations / sec

SQL Profiler

• ?? Need to look at machine with 7.0 (rather than 7.0 & 2000 dual installation)

Other Notes (AKA Important Stuff not in the Exam)

Transact-SQL fully supports ANSI SQL-92

Division of labour:

Command Prompt tools:

Net Libraries:

Not all Net Libraries are equal. For example, the Multiprotocol Net-Library supports data encryption but not server name enumeration.

APIs:

SQL Server supports two APIs natively: OLE DB (COM based) & ODBC (Call Level Interface based)

ADO is the wrapper for OLE DB, RDO is the wrapper for ODBC. As far as MS is concerned ADO is "in", RDO is "old hat".

SQL-DMO (Distributed Management Objects) is a COM wrapper for administrative Transact-SQL statements. SQL's  graphical admin tools use SQL-DMO internally.

ODBC Connections

ODBC connections with the following options set:

• SET QUOTED_IDENTIFIER ON
• SET TEXTSIZE 2147483647
• SET ANSI_DEFAULTS ON*
• SET CURSOR_CLOSE_ON_COMMIT OFF
• SET IMPLICIT_TRANSACTIONS OFF

* Equivalent to setting on: ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, QUOTED_IDENTIFIER. Yes, there is OFF overlap.

Security:

Two layers:

• Login Authentication
  Ensures the user is known and valid. Previous versions of SQL Server supported 3 login authentications methods (WinNT, Mixed and SQL), SQL has now been dropped. When running SQL Server in Win98 (i.e. as an application) WinNT authentication is not supported.
  
• Permission Validation
  Ensures the user is authorised to use a particular object or command

Roles:

• Fixed Server
  Groups representing administrative privileges at the server level (e.g. system administrator & database creators). Fixed Server roles are independent of databases.
• Fixed Database
  Groups representing administrative privileges for a database (e.g. backup and restore). Fixed Data roles are specific to a database (e.g. having backup role on one database does not automatically grant a backup role on other databases)
• User-defined
  Basically like an group on a NT domain. i.e. a way to apply a set of privileges / restrictions on a group of accounts without having to set each user's settings individually.

Query Analyser:

• If a selection is highlighted when the execute button is clicked, only that selection is executed.
• There is a "Parse" option that sends the command to the server for validation but doesn't actually cause the statement to be executed.
• Pressing F1 always brings up the same help window (on the Query Analyser topic). To obtain help on Transactional-SQL command, highlight the command and press Shift-F1.

Master Databases

• sysdatabase
  List of all databases on server

Statement Types:

• DCL (Data Control Language)
  Used to change permissions associated with users and roles
  GRANT SELECT ON MyTable To MyUser
  DENY
   
• DDL (Data Definition Language)
  As per VFP
  CREATE, ALTER, DROP etc.
  Note that objects by default belong to the creator and access rights have to be changed to allow normal users access. Consider using sp_changeobjectowner to consolidate ownership.
   
• DML (Data Manipulation Language)
  As per VFP
  SELECT, UPDATE etc.

By default, members of the sysadmin, dbcreator, and db_owner roles can issue DDL and DML statements. Additionally, members of the db_securityadmin role can also issue DCL statements, members of the db_ddladmin role can all issue DDL statements and members of the db_datareader and / or db_datawriter roles can issue DML statements.

Miscellaneous Useful Stored Procedures

Miscellaneous Useful Settings

<---- Move as some of this is in the exam

Capacities

---->

Glossary

Presentation Layer / Services formats and presents data to users and provides an interface for data entry. For example as a layer between a data server and a internet browser client.

Business Layer / Services implements business rules such as data validation and calculations.

Data Layer / Services implements data storage, retrieval, security and integrity.

OLTP On-Line Transaction Processing. High change, low query systems.

DSS Decision Support Services. Low change, high query systems.

Positioned Update Jargon for when a change to a row in a cursor is reflected in the base table(s)

Atomicity Units of code that are indivisible. For example, a transaction that debits an amount from one bank account and credits the same amount to another.

Niladic functions Functions that allow default values to be inserted into tables when no value is supplied. For example, the CREATE TABLE's DEFAULT clause

Heterogeneous Query A query that is executed using the resources of only one server.

DCL Data Control Language

Batch A set of SQL commands issues and executed as a single group. Batches are separated by the GO command.

Script A collection of batches.