Cramsession For The SQL 6.5 Implementing & Design Exam

SQL 6.5 Implementing a database design

Storage Units

Devices - Database devices are operating system files. They can store multiple databases and parts of databases.

Databases - Databases are created on one or more devices. When more than one device is used, the devices can exist on separate hard drives. Minimum of 1 MB in size.

Allocation Unit - When a database is created, space is allocated in 1/2 MB increments (256 pages or 32 extents) known as an allocation unit.

Extents - An extent is 16K (8 contiguous 2K pages). When a table or extent is created, SQL Server allocates an extent. Each object is allocated its own extent. Extents are never shared. Because there are 32 extents per allocation unit, there can be up to 32 objects per allocation unit.

Page - The basic unit of storage is a 2K (2048 bytes) page.

SYSTEM Tables

When SQL Server is installed, there are four system databases created:

Master	Controls the user databases and the operation of SQL Server as a whole (default size 25 MB). No user is allowed direct access to it.
Model	Provides a template or prototype for new databases. Contains the system tables which belong in every database. Items that are to appear in all new databases should be placed here.
Tempdb	Provides storage for temporary tables and other temporary storage needs such as intermediate results of GROUP BY, ORDER BY, DISTINCT and cursors.
Msdb	Supports the SQL Executive Service. This service is a scheduler that enables such activities as replication, task scheduling, and alert management.

There are thirteen tables found only in master which are collectively known as the system catalog or data dictionary:

syscharsets	Character set or sort order
sysconfigures	Configurable environmental variables
syscurconfigs	Configurable environmental variables
sysdatabases	Databases on SQL Server
sysdevices	Available database and disk devices
syslanguages	Languages known to the server
syslocks	Active locks
syslogins	User accounts
sysmessages	System error messages
sysprocesses	Ongoing processes
sysremotelogins	Remote user accounts
sysservers	Remote servers
sysusages	Disk space allocated to each database

There are eighteen tables found in every database (including master) collectively known as the database catalog:

sysalternates	Has one row for each SQL Server user mapped to a database user
syscolumns	Has one row for each column in a table or view, and for each parameter in a stored procedure
syscomments	Has one or more rows for each view, rule, default, trigger, and stored procedure with a SQL definition statement
sysdepends	Has one row for each procedure, view or table that is referenced by a procedure, view, or trigger
sysindexes	Has one row for each clustered index, nonclustered index, and table with no indexes, plus an extra row for each table with text or image data
syskeys	Has one row for each foreign, primary, or common key
syslogs	Contains the transaction log
sysobjects	Has one row for each table, view, stored procedure, rule, trigger, default, log, and (in tempdb only) temporary object
sysprocedures	Has one row for each view, rule, default, trigger, and stored procedure
sysprotects	Contains user permissions information
syssegments	Has one row for each segment
systypes	Has one row for each default system-supplied and user-defined datatype
sysusers	Has one row for each user allowed in the database
sysreferences	Has one row for each reference constraint created
sysconstraints	Contains information on all constraints created
sysarticles	Contains article information for each article created for replication
syspublications	Contains one row for each publication created
syssubscriptions	Contains one row for each subscription from a subscribing server

Many tables contain the ID for DB objects, but only the sysobjects table contains the name of objects.

All information regarding scheduling, alerts, and SQL Executive tasks are stored in the msdb database.

SELECT NAME FROM sysobjects WHERE TYPE='U' - All tables in DB

SELECT USER_NAME(uid), DB_NAME(dbid) FROM master..sysprocesses - All users and DB connection

Database Objects

Table - Collection of columns and rows

View - An alternate way to look at data from one or more tables

Default - Value that SQL Server inserts into a column if the user does not enter a value

Rule - Specifies and controls what data can be entered into a table

Stored Procedure - Precompiled collection of SQL statements

Trigger - Special form of a stored procedure that is executed automatically when a user modifies data in a table

SYSTEM Stored procedures

sp_helpconstraint [table_name]	Returns constraints created on a table
sp_helptext [rule_name]	Returns the script used to create a rule
sp_help [object_name]	Returns information on objects and types
sp_helpdb [dbname]	Provides information about the database
sp_helpindex tabname	Reports information on indexes on a table
sp_spaceused [objname]	Reports space used by an object or all database objects (Must set DB with USE db_name if not the current DB)
sp_droptype	Drops a user-defined datatype
sp_dboption db_name, 'single user', true	Sets the db to single user mode. Users in DB are not disconnected, however after users have disconnected -- only one user at a time will be able to reconnect.

SUSER_ID	Returns user's SQL Server login id across all databases
SUSER_NAME()	Returns user's SQL Server login name across all databases
USER_ID()	Returns user's database id
USER_NAME()	Returns user's database name
DB_ID()	Returns database ID
DB_NAME()	Returns database name
OBJECT_ID()	Returns object id
OBJECT_NAME()	Returns object name

Leaving the parenthesis blank will return information on the current user, database, etc. Providing a specific name (in the ID functions) or ID (in the NAME functions) will return the value for that user/database/object.

SELECT REPLICATE ('*', 5)	'*****'
SELECT SUBSTRING ('Fred',2,2)	're'
SELECT RTRIM (' Fred ')	' Fred'
SELECT LOWER ('Fred')	'fred'
SELECT UPPER ('Fred')	'FRED'
SELECT LTRIM (' Fred ')	'Fred '
SELECT RIGHT ('Fred', 2)	'ed'
SELECT SPACE (5)	' '
SELECT STR (12)	'12'
SELECT CHAR (65)	'65'
SELECT ASCII ('A')	'A'
SELECT GETDATE()	date & time
SELECT CONVERT(char(3), 123)	'123'

User Tables

CREATE TABLE [database.[owner].] table_name
(
   {column_name column properties [constraint[constraint[...constraint]]]
  | [[,] constraint]}
   [[,] {next_col_name | next_constraint}...]
)
[ON segment_name]

ALTER TABLE customer ADD DEFAULT 'unknown' FOR phone

column_name data_type [NULL | NOT NULL] REFERENCES table(column_name)

Table names must be unique within a database.

Column names must be unique within a table.

Tables created with a prefix of ## are temporary and are generated in the tempdb database.

Tables created without specifying a segment are stored in the default segment.

Only one entity type should be described by a table to facilitate relational integrity.

Columns in a table cannot be removed.

It is common to assign a default for fields which are optional, but have a NOT NULL constraint.

On a SELECT *, if a user does not have read permissions on ANY of the columns, they will receive a 'permission denied' error message.

Cannot update-in-place if a table is marked for replication, has an UPDATE trigger, or where affected columns are part of a clustered index. For a multi-row update-in-place, columns must be fixed-length and cannot be part of a unique index and table cannot include a timestamp column.

Rules can be bound to non-compatible datatypes. If the rule is invoked by an insert/update, an error will be returned.

Existing data is not affected by a new rule being applied to a column.

You can replace a rule on a column by binding a new rule to it.

Relationships	Modeled as a:	Examples
one to one	Foreign key column in either table	Employees mailing information to employee tax information
one to many	Foreign key column in the 'many' table	Member to number of books
many to many	Separate 'link' table with foreign key columns to both tables	Students to courses

An IDENTITY column is created with a SEED (starting value) and an increment.

The IDENTITY can not be added to existing columns.

Only one IDENTITY column is allowed per table.

To place values into an IDENTITY column, you must use the SET option with the IDENTITY INSERT activated prior to the INSERT statement.

There are two ways to delete rows from a table:

DELETE [FROM] table_name WHERE where_clause
TRUNCATE TABLE table_name

TRUNCATE and DELETE are functionally identical except that DELETE logs each row deletion.

TRUNCATE is faster because it does not perform logging of each row.

A default cannot be dropped before being unbound from a column or user-defined datatype

Defaults or rules based on user-defined datatype are almost always put into effect when a column is created with the user-defined datatype.

A column or user-defined datatype can be dropped without unbinding defaults.

When a table is dropped, all indexes, triggers, constraints, and permissions on the table are dropped automatically.

Indexes

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner.] table_name (column_name [,column_name]...)
[WITH
[PAD_INDEX,]
[[,] FILLFACTOR=fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] {SORTED_DATA | SORTED_DATA_REORG}]
[[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
[ON segment_name]

ALLOW_DUP_ROW	Allows duplicate rows in the index
IGNORE_DUP_ROW	Deletes duplicate rows from the table on index creation
Neither DUP option	CREATE INDEX fails if there are duplicate rows
SORTED_DATA	Table data is already sorted, do not recreate (faster -- less space on creation)
SORTED_DATA_REORG	Table data is sorted, but is recreated anyway to compact 'holes'
FILFACTOR	High leave little space for new rows -- used for non-volatile data

UPDATE STATISTICS emp	Updates stats on all indexes on emp
UPDATE STATISTICS emp empndx	Updates stats only on the empndx index

Integrity Type	Constraints	Other Implementations
Entity	PRIMARY KEY NULL UNIQUE	UNIQUE indexes IDENTITY datatype
Referential	REFERENCES FOREIGN KEY	Triggers Stored Procedures
Domain	NULL DEFAULT CHECK	Datatypes Rules Defaults User-defined datatypes Triggers Stored Procedures
User-Defined	REFERENCES FOREIGN KEY CHECK	Rules Triggers Stored Procedures

An index used to maintain a primary key must be unique.

Both a UNIQUE index and a PRIMARY KEY will prevent the entry of duplicate records.

You cannot create a unique index on a table with duplicate values.

Only one index can be used per table per query.

A clustered index must be on the same segment as its table.

Clustered indexes are best for:	Nonclustered indexes are best for:
Columns with a limited (but not tiny) number of unique values	Columns that contain a large number of distinct values
Queries that return large result sets	Queries that return small result sets
Queries which return a range of values using comparison operators	Queries that use an ORDER BY clause

Constraints

ALTER TABLE tab_name
[WITH NOCHECK]
ADD CONSTRAINT constraint_name
[CHECK (column_name LIKE check_condition)]
[DEFAULT default FOR columname]
[ [FOREIGN KEY] [(]column_name[)] REFERENCES parent_table(parent_column)]
[UNIQUE [CLUSTERED | NONCLUSTERED] (column_name [,col_name2...]) [ON segment_name]

ALTER TABLE table_name DROP constraint_name

Constraints are not considered to be high-level objects.

Constraints are evaluated before triggers fire.

You can associate multiple columns with a constraint.

You can associate multiple constraints with a column.

Views

CREATE VIEW [owner.] view_name
[(column_name [, column_name...])]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

The WITH ENCRYPTION options encrypts the view creation text in the syscomments table.

The WITH CHECK OPTION clause of the CREATE view verifies that any inserts or changes to the view will meet the criteria of the WHERE clause which created it (i.e. the rows inserted/updated will continue to be visible when selecting from the view).

You cannot insert or update computed or aggregate columns in a view.

You cannot attach indexes, defaults, or triggers to a view.

If the owner of a table revokes SELECT access to a user after that user has created a view on the table, the user can still access table information using the view.

You can grant permissions to views without granting permissions to the underlying tables.

syscomments contains the definitions for views.

Views can be created to show:

A subset of the columns in a table
A subset of the rows in a table
A combination of data from more than one table
A combination of data from more than one view
A combination of data from more than one view and more than one table

Triggers/Procedures

CREATE TRIGGER triggername
ON tablename FOR event
WITH ENCRYPTION
AS action

DROP TRIGGER trigger_name

Statements Disallowed in a trigger:

SELECT INTO	ALTER TABLE and ALTER DATABASE
TRUNCATE	GRANT AND REVOKE
CREATE	DROP
All DISK statements	LOAD DATABASE and LOAD TRANSACTION

On insert, update or deletion of a column, the old and new values are stored in virtual tables. The name of the virtual tables used to store the data are generated as inserted.column_name (for records inserted and new values of an update) and deleted.column_name (for records deleted and *old* values of an update).

IF UPDATE column_name can be used to determin is a column has been updated.

A DELETE trigger cannot use IF UPDATE.

IF UPDATE will return true on an INSERT only if the referenced column is inserted into.

To disable nesting, use sp_configure to set the nested triggers option to 0.

Triggers are not activated when the WRITETEXT statement modifies a column.

Assigning a default to a column will activate an UPDATE trigger.

BEGIN TRAN is always implied in a trigger and is not require to use ROLLBACK TRAN.

Because constraints are evaluated before triggers fire:

A trigger will not run if a constraint is violated.
Triggers can't delete rows in a child table if there are FOREIGN KEY constraints on them

Triggers are ignored during bulk copy operations. To enforce data integrity after a bcp operation you must use query operations after the load.

xp_logevent allows SQL Server errors to be recorded in the event log without returning an error message to clients.

@@ROWCOUNT - Shows the number of rows modified

@@ERROR - Last error raised

Defining a cursor:

DECLARE curemployees CURSOR FOR select * from emps
DECLARE curemployees SCROLL CURSOR FOR SELECT * from emps

FETCH NEXT FROM curemployees (fetch next row)
FETCH PRIOR FROM curemplyees (fetch prior row)
FETCH FIRST FROM curemployees (fetch first row)
FETCH LAST FROM curemployees (fetch last row)
FETCH RELATIVE FROM curremployes (fetch relative from current position)
FETCH ABSOLUTE FROM curremployes (fetch absolute position row)

Multiple variable definitions must be separated by commas

Execute a remote procedure:

EXECUTE Sales.master.dbo.sp_who

Change database context to pubs

EXECUTE ('USE pubs')

EXECUTE sp_who and EXECUTE ('sp_who') are funtionally identical.

sp_addmessage 51000, 16, 'error 51000'	Add user-defined error 51000
RAISERRROR (51000, 16, -1)	Raise error number 51000

RETURN causes a stored procedure to stop executing and return control to the calling procedure.

Stored procedures are not compiled until they are first executed.

Stored procedures are faster than direct execution because statements are pre-compiled.

Users can be given permission to run stored procedures even if the procedure access objects the user does not have permissions on.

WAITFOR DELAY '00:02:00'	Pause for two minutes
WAITFOR '12:20:00'	Pause until 12:20

Batches

You cannot ALTER a table and then refer to its new columns in the same batch.

You cannot utilize rules, defaults, of CHECK constraints from within the same batch where they are defined.

You cannot combine CREATE [DEFAULT | PROCEDURE | TRIGGER | VIEW] with any other statements.

You can combine CREATE TABLE with other statements.

SQL Statements

SELECT [ALL | DISTINCT] select_list
[INTO new_table_name]
FROM table_name [,table_name2 [...., table_name16]]
[WHERE clause]
[GROUP BY [ALL] aggregate_free_expression]
[,aggregate_free_expression...]
[WITH {CUBE | ROLLUP}]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]

The + symbol is used to concatenate text:

SELECT 'This text will be added to ' + col_name FROM table_name

When using SELECT * FROM table_name, columns are displayed in the order they were created or added to the table.

Can use DEFAULT VALUES when inserting into a table even if a column does not have a DEFAULT defined if the column allows NULL values.

If COMPUTE BY is used, must also ORDER BY that column.

WHERE is used to exclude rows before grouping.

HAVING is used to exclude groups after grouping.

SELECT Employee, 'Salary' =
   CASE
    WHEN salary<70000 THEN 'Paid too little'
    WHEN salary>=70000 THEN 'That is more like it'
   END
FROM employees

SUM of all salaries for each department:

SELECT employee, department, salary
FROM emps
ORDER BY department
COMPUTE SUM(salary) BY department

OUTER JOIN -- all rows are displayed from the table nearest the asterisk (emps):

SELECT employee, supplier
FROM emps, suppliers
WHERE emps.city *= suppliers.city

Show all managers who supervise underpaid employees:

SELECT managername
FROM managers
WHERE man_ID IN

(SELECT man_id
FROM emps
WHERE salary<50000);

Delete from one table based on values in a second:

DELETE FROM orders
FROM orders, emps
WHERE employee.firstname = 'Jeff'
AND employee.lastname = 'Jones'
AND orders.employee_id=employee.employee_id

Hints:

SELECT first_name, last_name, empid from emps (INDEX=1)	Use clustered index
SELECT first_name, last_name, empid from emps (INDEX=0)	Use no index
SELECT first_name, last_name, empid from emps (INDEX=empndx)	Use index empndx
SELECT * from (HOLDLOCK)	Force shared lock
SELECT * from emps (UPDLOCK)	Force an update lock
SELECT * from emps (TABLOCK)	Force shared lock on entire table.

Locks

Lock Types

Item	Description
Page	A 2K data page or index page. This is the most common type of lock.
Extent	A contiguous group of eight data pages or index pages. This lock is only used for allocation.
Table	An entire table, including all data and indexes.
Intent	A special type of table lock to indicate the type of page locks currently placed on the table.

Lock Compatibility

	Shared	Update	Exclusive
Shared	Yes	Yes	No
Update	Yes	No	No
Exclusive	No	No	No

Replication

sysarticles	Publications Server	Information for each article created for replication.
syspublications	Publications Server	One row for each publication created.
syssubscriptions	Publications Server	One row for each subscription from a subscribing server.
MSlast_job_info	Subscriptions Server	Tracks the last successful transaction replicated.
MSjob_commands	Distribution Server	Maintains a record for each command associated with a transaction in the MSjobs table.
MSjobs	Distribution Server	Contains transactions from the publisher to replicate to the subscriber DB.

Most replication tables reside in the msdb database.

In central publisher replication, subscription servers will generally treat replicated data as read-only.

Two databases on the same server cannot subscribe to the same article or to the same publication.

Articles can only contain data originating from a single table.

Subscription servers can subscribe to any combination of articles and publications.

Replication using two-phase commit gurantees that all copies of data will be identical to the original.

Transactions are read from the transaction log of the source database and posted to the destination db.

The log reader process moves transactions to the distribution database.

A single distribution server can be used by more than one publication server.

A single distribution server can be used by more than one subscription server.

SQL Transfer Manager is used to copy data between SQL Servers. It saves to the \SQL\BINN directory and creates a new subdirectory by combining the first four letters of the exporting server with the first four letters of the exported database.

When using the SQL Transfer Manager, the source server can be a Windows NT, OS/2, or non-Microsoft SQL Server, but the destination server can only be Microsoft SQL Server.

Replication data files are kept in the \MSSQL\REPLDATA directory of the distribution database by default.

Publication servers should never be connected to remote distribution servers over a slow or unreliable link.

Application Development

SQL Service Manager	Used only to start and stop SQL Server.
SQL Executive Service	Uses Transact-SQL to run scheduled tasks through the command prompt.
SQL Server Distributed Mangement Framework (SQL-DMF)	Encompases all of the functionality provided by SQL-DMO. Includes a set of distributed management tools. Provides services for replication. Has three levels: Lowest - direct access to SQL Server via Transact SQL. Middle - DMO. Highest - SQL Server Enterprise Manager.
SQL Distributed Management Objects	This includes alerting and notification and all other features accesible through Transact-SQL. Applies the Component Object Model to SQL Server functions. Used for managing collections of properties, methods, objects, and events. To use must have installed: SQLOLE32.DLL SQLOLE.TLB SQLOLE.REG SQLOLE60.SQL is required to generate SQL DMO stored procedures. Provides 32-bit OLE Automation objects. Exposes interfaces for all SQL Server management functions to OLE-compliant applications.
SQL Enterprise Manager	A graphical administration tool that simplifies managing a muliple-server environment. Uses SQL-DMO automation objects.
SQLOLE	Allows multiple SQL Servers to be managed from a single user application. When using properties and methods with Visual C++ or Visual Basic, the correct order of operations is: 1. Create the object 2. Connect to the object 3. Release the object. OLE Automation is used most often with SQL-OLE.
Open Data Services (ODS)	Can be used to develop applications that will act as a Windows NT service, a remote stored procedure application, or a gateway application to other database management systems. Used most often to create extended stored procedures.
DB-Library	Functions often use the DBPROCESS parameter as their first argument. The dbopen function initializes the DBPROCESS structure. Can act as a connection between a front-end application and SQL Server. Only DB-Library can be used from MS-DOS environments (ODBC cannot).

You must have a data source name in order to establish an ODBC connection to SQL Server.

Miscellaneous

After using an ALTER DATABASE, you should issue a DUMP DATABASE master.

Cannot use the DUMP TRANSACTION command when 'trunc log on chkpt' is set to TRUE.

DUMP TRANSACTION backs up changes made since the last DUMP DATABASE.

NO LOG	Used when the transaction log is full
NO_TRUNCATE	Used during recovery of a failed device
TRUNCATE_ONLY	Used before backing up DB to speed backup.

Transaction log should be 10-25% of the size of the database.

Explicit allocation of log space is performed either by the LOG ON clause of the CREATE DATABASE command or by using sp_logdevice with the ALTER DATABASE command.

The DISK INIT command is used to create a new transaction log on a device.

SQL Server Setup can configure SQL Server to login to the Windows NT Mail client when SQL Server boots.

ISQL/w - Allows you to enter Transact-SQL statements and stored procedures in a GUI interface.

Special Thanks to Matthew Morris, MCSE+Internet, CNA, for writing the original Cramsession for this exam!