|
| |
| |
|
|
|
Da Notes -
70-029 - S
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 693, Questions: 49, Time: 150 Min.
|
Da Notes Index
|
|
|
SELECT
|
SELECT
Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables.
SELECT INTO
Requires unique table name. Temporary local or global tables can be used. Select into/bulk copy database option on to create a permanent table.
|
SERVER
TOOLS
|
SERVER COMMAND PROMPT MANAGEMENT TOOLS
Osql - ODBC connectivity to SQL Sever - primarily for batch files with multiple SQL statements
bcp - batch utility to import and export data to and from SQL Sever
SERVER ENGLISH QUERY SUPPORT
Pose questions in English instead of SQL.
SERVER HELP
Application help - context sensitive
Transact-SQL - select statement and press shift -f1 - also on help menu
SQL Server doc - Books Online
SERVER OLAP SERVICES
Allows summary data to be stored in various formats and locations
|
SERVER SECURITY
|
Login authentication - identifies only ability to connect
SQL Server - login and password required
NT authentication - WIN NT account controls SQL server
Permission validation - on database user accounts and roles
· Fixed server roles - dbcreator, diskadmin, processadmin, securityadmin, serveradmin, setupadmin, sysadmin
· Fixed database roles - public, db_owner, db_accessadmin, db_ddladmin, db_securityadmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter
· User defined database roles - create your own roles
|
SERVER SERVICES
|
MSSQLServer - data management, Transaction and Query Processing, Data Integrity
SqlServerAgent - Jobs, Alerts, operators
MSDTC - Distributed Transaction Coordinator - Distributed transactions, management
Microsoft Search - Full text catalogs and indexes
|
SERVER SOFTWARE
|
SQL Server Client configuration - manage client configuration for communication
SQL Server performance Monitor - integrate SQL server with NT performance monitor
SQL Server profiler - capture continuous server activity and provide auditing
SQL Server query analyzer - graphical query tool to analyze the plan of a query, view statistics, manage queries in different windows
SQL Server service manager - graphical utility to start, pause and stop SQL services
SQL Server setup - install and configure SQL server
SQL Server wizards - collection of tools to guide users through complex tasks
|
SEt
commands
|
*SET ANSI_DEFAULTS_ON: When this is set to ON, SQL-92 compliant behavior. Enables several settings in SQL Server that define SQL-92. Effective only within a connection.
SET ANSI_NULL: When this option is set to ON, a comparison of an equal (=) and not equal (<>) will always return NULL if any of the arguments contain a NULL. If this option is set to OFF then True or False will be returned depending on whether both of the arguments are NULL or only one of the arguments contains NULL.
SET ANSI_NULL_DFLT_ON: When this is set to ON, new columns created with the ALTER TABLE and CREATE TABLE statements allow null values if no nullability status of the column is not explicitly specified. Has no effect on columns created with an explicit NULL or NOT NULL.SET
SET ANSI_WARNINGS: When set to ON: If NULL values are present in aggregates an error message is generated. Divide-by-zero and arthimetric overflow will cause the statement to be rolled back and return an error. When this is set to OFF no error message is generated.
SET ARITHABORT: When this option is set to on then an overflow or divide-by-zero will terminate the query or batch. If this option is set to OFF a warning message will be displayed but the query will complete.
*SET NUMERIC_ROUNDABOUT: When this option is set to on then an error will be generated when a loss of precision occurs(ex. decimals). When this is set to OFF then no errors are reported and the result will be rounded to the column precision
SET SHOWPLAN_TEXT_ON: used to view the execution plans that SQL Server creates to manipulate the data that is required by the subsequent statements.
|
Shrinking
|
Shrinking a database - see DBCC SHRINKDATABASE
Shrinking a file in a database - see DBCC SHRINKFILE
|
SP_WHO
|
sp_who: returns a list of all connections to the server and the process identification of these
processes = spid. You will also get a column named blk(block) which indicates the spid of another process that is blocking another process.
|
Stored Procs
|
· Named collection of T-SQL statements
· Encapsulate repetitive tasks
· Five types = system, local, temporary, remote, extended
· Accept input parms and return values
· Return status to indicate success or failure
· Will nest to 32 levels
· Use sp_help, sp_helptext and sp_depends to display info or tables = sysobjects, syscomments, sysdepends
· Dbo user should own all stored procs
Advantages
Share application logic. Shield database schema details. Provide security. Improve performance. Reduce network traffic.
Alter Stored procs (retains all permissions when using ALTER)
· e.g. ALTER PROCEDURE stored_proc AS …
· Nested stored proc not affected by the alter
· Cannot grant p[ermission to ALTER PROC
DROP Stored procs (use sp_depends to see any dependencies)
· e.g. DROP PROCEDURE stored_proc
Executing Stored procs (need EXECUTE permission)
· stored_proc
· EXEC stored_proc
· In this example it will populate x with the result set from stored_proc
INSERT INTO x
EXEC stored_proc
Stored Procs - Extended
· Implemented as functions inside DLL's
· Executed outside the SQL environment.
· Usually have the XP_ prefix
· Must be in the MASTER database ONLY
· Create a user defined stored proc in any database that call an extended proc. Then you can execute an extended stored proc from any database.
· e.g. EXEC master..xp_cmdshell 'dir c:\mssql7'
Stored Procs - Local
Store in the USER databases
Stored Procs - parameters
Input parameters - trap missing or invalid parms first. Provide default values.
Pass values by reference - EXEC stored_proc @p1 = ' ', @p3 ='x', @p5 = 'x'
Parms in any order, omit parms with nulls or defaults
Pass values by position - EXEC stored_proc 'p1', 'p2, 'p3', 'p4'
Parms in order, can omit only if all at the end of parm list - e.g. p3 and p4 both omitted.
OUTPUT keyword - variable name to receive a return value, must specify OUTPUT, cannot be a text or image type. E.g. EXEC stored_proc 'p1, 'p2' @answer OUTPUT
RECOMPILE keyword - use if widely varying results, new index added, parm value is atypical. Can use: CREATE PROC WITH RECOMPILE, EXEC WITH RECOMPILE, sp_recompile.
Stored Procs - Perfomance
SQL Server CACHE manager
Cache hit ratio, cache object counts, cache pages, cache use count / sec
SQL Statistics
SQL Re-compilations / sec. << Less is better
Stored Procs - Profiler
Can monitor events
Can test each statement in a stored proc
Stored Procs - Remote
Remote stored procs are a legacy feature. Distributed queries support
Stored Procs - System
Store in the MASTER database and hap SP_ prefix
Sp_help - database object
Sp_helpdb - database
Sp_helpindex - table index
Stored Procs - Temporary
Store in the local database. # = temporary (single session), ## = global temporary (all user sessions)
|
SUBQUERIES
|
JOINS run FASTER than subqueries.
Use to break down a complex query into a series of logical steps. Answer a query that relies on the results of another query. No limit on levels. Cannot use on text and image. Must be enclosed in parenthesis. Use in place of expressions as long as a single value or list of values are returned.
|
SUBSTRING
|
SUBSTRING(expression, start, length) - select substring('Daniel', 1, 3) OUTPUT: Dan
NOTE: start can not be 0 or the output will return NULL
|
SYSMESSAGES
|
SQL Server table in the MASTER database with all error messages.
RAISERROR statement can use these messages to returns errors to apps or to the SQL Server and Win NT application error log. The command SP_ADDMESSAGE can be used to add custom error messages to the SYSMESSAGES table in the MASTER database.
|
|
|
|
|
