| |
|
|
|
Da Notes -
70-029 - D
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
|
|
|
Database Objects
|
Check constraint - prevents invalid data in columns - cannot assign a value like a default
Data type - column data values = binary, character, unicode, date/time
Default definition - if no column value supplied this will use the default
Index - fast access for data retrieval
Rules - specific column values - cannot assign a value
Stored procedure - collection of T-SQL that execute together
Table - collection of rows
Trigger - special stored procedure - executed when a user modifies data in a table
Unique constraint - prevents non-unique values from being entered
View - look at data from one or more tables
|
Databases - System
|
Master - tracks user accounts, configuration, system error messages, user databases, etc. (always backup when adding or dropping databases)
Model - template for new user databases
Tempdb - storage for temp tables
Msdb - SQL server agent (if database is deleted, agent will be crippled)
Distribution (replication)
|
Databases - User
|
2 sample user databases created - pubs and Northwind
|
Data transfer
|
see transfer data
|
DATEADD
|
DATEADD (YY,4,GETDATE())
datepart = yy, increment number = 4, date = getdate()
|
DATEDIFF
|
DATEDIFF (datepart, startdate, enddate) : datepart = yy
|
DBCC DBREINDEX
|
Rebuild all indexes on the table in one step without dropping the primary key.
Or CREATE INDEX with the DROP_EXISTING clause.
Both allow a fill factor to be specified.
If you use DROP INDEX and CREATE INDEX, nonclustered indexes are rebuilt twice.
|
DBCC
Showcontig
|
*DBCC SHOWCONTIG (table id [,index_id])
declare @tbl int - set @tbl = OBJECT_ID('Orders') - DBCC SHOWCONTIG (@tbl)
Shows the level of fragmentation for the data and indexes on a table.
Scan density = 100 = contiguous, < 100 = some fragmentation
Average page density = higher is better than lower - shows fullness of a page
|
DBCC SHRINKDATABASE
|
DBCC SHRINKDATABASE (sample, 25)
|
Deadlocks
|
Deadlocks - see locks
|
Default
|
Default - see Database Objects
|
Distributed Queries
|
Access data from multiple data sources on local or remote computers. OLE DB supported by Microsoft API for universal data access.
Ad hoc - OPENROWSET function used to get data from an OLE DB provider (MS Access)
Linked Server query - OLE DB data source that is pre-registered on the local SQL Server.
sp_addlinkedserver - refer to BOL, sp_linkedservers - a list of linked servers
Executing linked server queries - use fully qualified names to reference objects, query is run on the linked server remotely.
Executing Pass-Through Queries - use OPENQUERY function, in select statement in place of a table name, use result as the target table of an insert, update or delete statement. (e.g. SELECT * from OPENQUERY (Asiaserver, 'SELECT id, royalty from Northwind.dbo.Productinfo'))
Executing a stored proc on a linked server - EXECUTE servername.dbname.owner.procname (e.g. EXEC accounting.master.dbo.sp_helpntgroup)
Modifying data on a linked server - BEGIN DISTRIBUTED TRAN (cannot be nested) or Call API functions. Rollback will roll back the entire transaction. SET XACT_ABORT ON must be used BEGIN TRAN will not work on a linked server.
BEGIN DISTRIBUTED TRAN or using ODBC / OLE DB - invokes DTC - Distributed Transaction Coordinator. Replication can be used instead of this method.
|
Domain Integrity
|
see integrity
|
DSS
|
Decision-support system databases
Add pre-aggregated or summarized columns to satisfy common queries and improve response times.
Heavy indexing. DSS databases have low update requirements but large volumes of data. Use many indexes to improve query performance.
De-normalization of the database - Use of a star or snowflake schema to organize the data within the database.
|
DTS
|
Data Transformation Services - see TRANSFER DATA
|
|
|
|
