MS2073A-Study Notes

See also my 2072 Study Notes

Back Home Up Next

Each explicitly named stored procedures, extended stored procedures, or command line utilities, relevant T-SQL commands, and, where referenced:
 

Module     1    2    3    4    5    6    7    8    9    10    11   12   13   14   15

Name of Tool, Command, or System Object Purpose of Tool, Command, or System Object Where referenced in MS2073 by Module
[Revision "A"]		 Where referenced in MS2073 by Module
[Revision "B"]
Module 1, SQL Server Overview  

 

 
[See Study Notes for Course 2072, as the two modules are indentical or nearly identical]      
       
       
Module 2, Overview of Programming SQL Server This is Module 1 in 2073B    
This module is largerly a review from course 2071      
osql Command prompt execution of T-SQL statements p. 8 p. 8
SETUSER function An "RunAs" like function p. 12 p. 12
sp_changeobjectowner changes the owner of an object p. 12 p. 12
sp_executesql Executes dynamically constructed SQL statements   2073B, Mod. 1, pp. 44-46
FOR XML AUTO|RAW|EXPLICIT How to generate XML output from SELECT statements pp. 38-40 pp. 38-40
Module 3, Creating and Managing Databases      
CREATE DATABASE      
ALTER DATABASE      
Setting database options Each database has a huge number of options pp. 7-8 pp. 7-8
sp_helpdb, or sp_helpdb dbname   p. 9 p. 9
sp_spaceused [objectname] Where objectname is a table.

When used w/o a parameter it shows space consumption for the current DB.

 p. 9 p. 9
sp_helpfile [[@filename=]'name'] Shows all the files in their respective filegroups, file system locations, FileIDs, and more...    
sp_helpfilegroup [filegroupname]      
DBCC SQLPERF (LOGSPACE) Monitor Log space, gives log space used percentages from all DBs. p. 15 p. 15
Suggested SQL Server:Database counters to monitor      
DBCC SHRINKDATABASE (DBx, targetpercentfreespace) i.e., dbcc shrinkdatabase(northwind,15) p. 17 p. 17
DBCC SHRINKFILE i.e., dbcc shrinkfile(Nwind-FG1-F1,15) p. 18 p. 18
DROP DATABASE   p. 19 p. 19
Module 4, Creating Data Types and Tables      
sp_addtype To add a user-defined data type to a database pp. 6-7 pp. 6-7
sp_tableoption To enable the text in row option for a table 2073B, p. 13 2073B, p. 13
sp_depends Run this before attempting to drop a table to see if any objects depend on this table

i.e., sp_depends Table1

 p. 16 p. 16
IDENT_SEED
IDENT_INCR
SCOPE_IDENTITY
IDENT_CURRENT
@@IDENTITY
IDENTITY_INSERT
DBCC CHECKIDENT		 Functions related to IDENTITY columns such as determining initial value, seed value, and max value p. 19 p. 19
NewID() function Automatically, randomly generates a unique identification number (GUID) for a column with data type uniqueidentifier. p. 20 p. 20
       
Module 5, Implementing Data Integrity      
sp_ help and sp_helpconstraint For help with constraints p. 8 p. 8
information_schema.check_constraints

"

"

"
information_schema.referential_constraints "

"

"
information_schema.table_constraints "

"

"
syscomments, sysreferences, and sysconstraints system tables "

"

"
DEFAULT constraint Auto-supply a value during an INSERT p. 10 p. 10
List of system-supplied values USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, or CURRENT_TIMESTAMP p. 10 p. 10
CHECK constraint Restricts to particular values (kind of like a WHERE clause) p. 11 p. 11
DBCC CHECKCONSTRAINTS Used to returns rows that violate the CHECK constraint. p. 11 p. 11
PRIMARY KEY constraint Imposes unique values. p. 12 p. 12
UNIQUE constraint Helpful when you already have a primary key. p. 13 p. 13
FOREIGN KEY constraint A referencing table REFERENCES another or the same table. p. 14 p. 14
CREATE DEFAULT default AS constant_expression A default object must then be bound to a column or user-defined data type. p. 21 p. 21
sp_bindefault and sp_unbindefault Used to bind/unbind a default to a column p. 21 p. 21
CREATE RULE rule AS condition_expression A rule object must then be bound to a column or user-defined data type. p. 22 p. 22
sp_bindrule and sp_unbindrule Used to bind/unbind a rule to a column p. 22 p. 22
DROP DEFAULT DefaultObjectName Drop a default p. 22 p. 22
DROP RULE RuleObjectName Drop a rule p. 22 p. 22
       
       
Module 6, Planning Indexes  

 

 
AUTOSHRINK A database option used to automate the DB shrinking process p. 24 p. 24
[again] sp_help      
       
       
       
Module 7, Creating and Maintaining Indexes      
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX Can specify the DROP_EXISTING option (p.23) p. 4 p. 4
DROP INDEX      
dbcc showcontig(tablenameX) Displays fragmentation information on the data and indexes of a table. p. 18-20 p. 18-20
dbcc indexdefrag Compacts the pages in an index.

i.e., dbcc indexdefrag (databaseX, TableX, IndexNameX)

 p. 21-22 p. 21-22
CREATE STATISTICS Manually creates distribution statistics p. 39-40 p. 39-40
UPDATE STATISTICS Manually update statistics p. 41-42 p. 41-42
dbcc SHOW_STATISTICS(table,target) View statistical information for an index or column p. 45-46 p. 45-46
       
Module 8, Implementing Views  

 

 
CREATE VIEW some available options:
WITH ENCRYPTION (p. 14)
WITH SCHEMABINDING
WITH VIEW METADATA
WITH CHECK OPTION (p. 15)		 P. 2 P. 2
Restrictions on view creation   p. 7 p. 7
ALTER VIEW   p. 9 p. 9
DROP VIEW   p. 10 p. 10
INFORMATION_SCHEMA.TABLES or sysobjects
(to view names)

INFORMATION_SCHEMA.VIEW_TABLE_USAGE or sysdepends (displays for your username only)
(base object names)

INFORMATION_SCHEMA.VIEWS or syscomments
(view definition)

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE or syscolumns (displays for your username only)
(columns that are defined in a view)

 [All of these are for
Locating View Definition Information]

Per esempio...
Select * from
northwind.invormation_schema.columns
where table_name = N'Customers'

or

Use DatabaseX
Go
Select * from sysobjects

 p. 12 p. 12
sp_helptext objectname ...where objectname could be the name of a VIEW

Shows the text used to create a VIEW

 p. 13 p. 13
sp_depends objectname ...where objectname could be the name of a VIEW

Retrieves the tables and/or views on which a VIEW depends

 p. 13 p. 13
When a procedure is created the state of the options SET QUOTED_IDENTIFIER and SET ANSI_NULLS are retained for the procedure Query the OBJECTPROPERTY function to determine whether these options were set in a procedure or not.    
sp_executesql Executes a dynamically constructed T-SQL statement.    
xp_cmdshell      
xp_logevent      
xp_sendmail      
       
       
Module 9, Implementing Stored Procedures      
CREATE PROC(EDURE) also WITH RECOMPILE keyword    
@@NESTLEVEL This system function tells you how far nested a stored procedure is p. 12 p. 12
sp_help objectname ...where objectname could be the name of a procedure

Shows the text used to create a VIEW

 p. 12

 

 p. 12

 
sp_helptext objectname ...where objectname could be the name of a procedure

Shows the text used to create a procedure

 p. 12

 

 p. 12

 
sp_depends objectname ...where objectname could be the name of a procedure

Retrieves the objects on which a procedure depends

 p. 12

 

 p. 12

 
sp_stored_procedures To print a list of stored procedures and their owners p. 12 p. 12
sysobjects Gather information about stored procedures p. 12 p. 12
syscomments Gather information about stored procedures p. 12 p. 12
sysdepends Gather information about stored procedures p. 12 p. 12
EXEC(UTE) also WITH RECOMPILE keyword ??? ???
ALTER PROC(EDURE)      
DROP PROCEDURE      
Using parameters in stored procedures The syscolumns system table stores parameter information    
sp_recompile if @objname specifies a table or view all procs and triggers that reference the named object are recompiled on next use    
DBCC FREEPROCCACHE Clears all stored procedure plans from the cache    
RETURN statement A return value of "0" is success

Return values of 0-14 are currently in use, and values -15 through -99 are reserved.

RETURN exits from a query or procedure unconditionally.
 

    
sp_addmessage Allows for the creation of custom error messages within stored procedures    
@@rowcount Returns the number of rows in a query    
@@error System function to detect a specific error number    
       
INFORMATION_SCHEMA.TABLES or sysobjects
(to view names)

INFORMATION_SCHEMA.VIEW_TABLE_USAGE or sysdepends (displays for your username only)
(base object names)

INFORMATION_SCHEMA.VIEWS or syscomments
(view definition)

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE or syscolumns (displays for your username only)
(columns that are defined in a view)

 [All of these are for
Locating View Definition Information]

Per esempio...
Select * from
northwind.invormation_schema.columns
where table_name = N'Customers'

or

Use DatabaseX
Go
Select * from sysobjects

 p. 12 p. 12
Module 10, Implementing User-Defined Functions  

 

 
CREATE FUNCTION      
List of built-in, non-deterministic funtioncs that are not allowed in creating user-defined functions @@ERROR
@@IDENTITY
@@ROWCOUNT
@@TRANCOUNT
APP_NAME
CURRENT_TIMESTAMP
CURRENT_USER
DATENAME
FORMATEMESSAGE

...And a whola bunch of other built-in functions

    
WITH SCHEMABINDING One of the CREATE FUNCTIONS options. If used, ALTER and DROP statements cannot be performed on database objects that the function depends on.

Some restrictions apply as to implementing the SCHEMABINDING option.

    
ALTER FUNCTION      
DROP FUNCTION      
       
       
       
Module 11, Implementing Triggers  

 

 
CREATE TRIGGER   p. 10 p. 10
Trigger information is inserted into the sysobjects and syscomments system tables      
List of the following statements that cannot be used in triggers ALTER DATABASE
CREATE DATABASE
DISK INIT, and DISK RESIZE
DROP DATABASE and LOAD DATABASE
...several others		    
sp_depends tablename To show triggers on a table p. 11 p. 11
sp_helptext triggername To view a trigger definition p. 11 p. 11
sp_helptrigger tablename To determine triggers that exist on a table AND their actions p.11 p.11
ALTER TRIGGER To change the definition of the trigger, or to enable/disable a trigger or all triggers p. 13 p. 13
ALTER TABLE You can also ENABLE or DISABLE a specific trigger or all triggers without deleting them with this command.    
DROP TRIGGER   p. 14 p. 14
INSTEAD OF (used with a trigger) Executes conditions instead of the Triggers' Update, Delete or Insert p. 20 p. 20
@@NESTLEVEL Checks the nest level within a trigger p. 22 p. 22
sp_configure Can alter trigger nesting capability p. 22 p. 22
@@ROWCOUNT Use this to determine how many rows a trigger might be affecting    
SET RECURSIVE TRIGGERS ON

sp_dboption		 An ALTER DATABASE option.

This can also be done with sp_dboption dbname, 'recursive triggers', True

    
sp_settriggerorder Specifies the first AFTER trigger and/or the last AFTER trigger to fire within a table, but cannot specify other trigger order. p. 24 p. 25
If nested triggers are off, so are recursive triggers, no matter what the DB setting is      
       
Module 12, Programming Across Multiple Servers      
OPENROWSET Includes all information required to access a remote database using an OLE DB Provider. Use when you don't expect to access the data source repeatedly. Does not require a linked server. p. 2 p. 2
OPENDATASOURCE Provides ad hoc connection information as part of a four-part object name without using a linked server name. p. 2 p. 2
OPENQUERY Executes a pass-through query on a given linked server. p. 3 p. 3
ANSI_NULLS
ANSI_WARNINGS		 These settings MUST BE "ON" to perform distributed queries. They ARE "on" by default in SQL QA and through ODBC, but are not "on" by default in osql.    
sp_addlinkedserver
sp_addserver (for bkwrd compat)		 Adds linked server definitions for distributed queries and transactions p. 8 p. 8
sp_addlinkedsrvlogin Maps login accounts between local and remote servers pp. 10-11 pp. 10-11
sp_serveroption Sets options for linked servers, i.e.,
Collation Compatible
Collation Name
Use Remote Collation		 p. 12 p. 12
sp_linkedservers Returns a list of linked servers defined on the local server p. 14 p. 14
sp_catalogs Returns a list of catalogs and descriptions for a specified linked server p. 14 p. 14
sp_indexes Returns index information for the specified remote table p. 14 p. 14
sp_primarykeys Returns the primary key columns, one row per key column for the specified table p. 14 p. 14
sp_foreignkeys Returns the foreign keys defined on the specified remote table p. 14 p. 14
sp_tables_ex A version of sp_tables, gives table info on tables from the specified linked server p. 14 p. 14
sp_columns_ex A version of sp_columns, gives column info (for all or a specified column) for the specified linked server table p. 14 p. 14
sp_helpntgroup Lists Windows 2000 groups and specifies the DBs to which they have access p. 21 p. 21
sysservers Information about linked servers is stored in this system table.
        Select * from sysservers		    
BEGIN DISTRIBUTED TRANSACTION      
       
       
       
Module 13, Optimizing Query Performance      
sp_recompile   p. 10 p. 10
SET QUERY_GOVERNOR_COST_LIMIT value The value can be zero (unlimited) or a positive integer to limit query duration for a particular connection. Use sp_configure to select behavior for all connections. pp. 11-12 pp. 11-12
sysindexes Supposedly there is information in this system table about execution plans. p.13 p.13
SET STATISTICS TIME on | off The following SET statements cannot be specified in a stored procedure. Use within QA.

Time in ms required to parse, compile, and execute each statement.		 p. 14 (See also course 2072 Mod. 8 notes p. 14 (See also course 2072 Mod. 8 notes
SET STATISTICS IO on | off Reflects number of pages read from cache and disk.    
SET STATISTICS PROFILE on | off      
SET SHOWPLAN_ALL on | off Does not execute the query.    
SET STATISTICS_TEXT on | off      
       
       
Module 14, Analyzing Queries      
       
include query hints?      
       
Module 15, Managing Transactions and Locks     Mod. 14
BEGIN TRAN[SACTION] [transaction_name] |@tran_name_variable [WITH MARK ['description']]]   p. 5 p. 6
SAVE TRAN[SACTION] {savepoint_name|@savepoint_variable}   p. 5 p. 6
BEGIN DISTRIBUTED TRAN[SACTION] [transaction_name] |@tran_name_variable]   p. 5 p. 6
COMMIT [TRAN[SACTION] [transaction_name] |@tran_name_variable]]   p. 5 p. 6
ROLLBACK [TRAN[SACTION] [transaction_name] |@tran_name_variable| savepoint_name|@savepoint_variable]]   p. 6 p. 7
@@TRANCOUNT A global variable to determine whether any open transactions exist and how deeply they are nested. p. 9 p. 10
DBCC OPENTRAN Retrieve information on transactions within your session.    
SET IMPLICIT TRANSACTIONS ON|OFF option     p. 11
TABLOCK hint Used to invoke a Bulk Table Lock. p. 16  
Table lock on bulk load Set using sp_tableoption, also used to invoke a Bulk Table Lock p. 16  
DBCC USEROPTIONS Used to set transaction isolation levels for a statement.   p. 20
As an alternate to DBCC USEROPTIONS Locking Hints can be specified using SELECT, INSERT, UPDATE, & DELETE statements for Table-level locking hints. See "Locking Hints" in B.O.L.   p. 20
SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE} These are session-level locking options (all statements in a session).   p. 21
SET LOCK_TIMEOUT timeoutperiod     p. 22
@@LOCK_TIMEOUT A global variable one can query to determine the current session value.   p. 22
SET DEADLOCK_PRIORITY LOW Use this with OLAP activities to predispose this transaction to be killed.   p. 27
KILL {spid | UOW} [WITH STATUSONLY} Terminates a user process based on the server process ID (spid).   p. 27
sp_lock Returns information about active locks in SQL Server.   p. 28