|
| |
| |
|
|
|
Da Notes -
70-029 - O
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
|
|
|
One-to-one
|
One row with particular primary key value in the parent table corresponds to
one row in the child table with the same value as a foreign key.
|
One-to-many
|
One row with a particular primary key value in the parent table corresponds to multiple rows in the child table with the same value as a foreign key.
Ex a customer(parent) may place zero or more orders.
|
OPENQUERY and OPENROWSET
|
see distributed query
|
Optimize performance I/O
|
DSS (warehouse) vs OLAP (on-line) type systems will require different methods.
DSS systems - Create more tables with fewer columns in each table (most used and rarely used columns). Each table needs a clustered index and multiple non-clustered indexes.
OLAP - normalize and reduce redundant data
Tables and indexes should be distributed across as many different physical disk as possible, especially Tables that can and are likely to participate in joins so both can be read in parallel.
Fewer indexes can improve performance
Place the transaction log on its own disk also improves performance, because SQL writes data to the transaction log sequentially.
Partition the data either horizontally or vertically.
|
OSQL
|
OSQL
-d db_name - Issues a USE db_name statement when osql is started.
-E - Uses a trusted connection instead of requesting a password.
-i input_file - Identifies the file that contains a batch of SQL statements or stored procedures. The less than (<) comparison operator can be used in place of -i.
-o output_file - Identifies the file that receives output from osql. The greater than (>) comparison operator can be used in place of -o.
-Q "query" - Executes a query and immediately exits osql. Use double quotation marks around the query and single quotation marks around anything embedded in the query.
-q "query" - Executes a query when osql starts, but does not exit osql when the query completes. (Note that the query statement should not include GO). If you issue a query from a batch file, use %variables, or environment %variables%. For example: SET table = sysobjects then osql /q "Select * from %table%"
Use double quotation marks around the query and single quotation marks around anything embedded in the query.
-? Displays the syntax summary of osql switches.
-P password - Is a user-specified password. If the -P option is not used, osql prompts for a password. If the -P option is used at the end of the command prompt without any password, osql uses the default password (NULL). Passwords are case-sensitive. The OSQLPASSWORD environment variable allows you to set a default password for the current session. Therefore, you do not have to hard code a password into batch files. If you do not specify a password with the -P option, osql first checks for the OSQLPASSWORD variable. If no value is set, osql uses the default password, NULL. The following example sets the OSQLPASSWORD variable at a command prompt and then accesses the osql utility:
C:\>SET OSQLPASSWORD=abracadabra then C:\>osql
-S server_name - Specifies the SQL Server installation to which to connect. The server_name is the name of the server computer on the network. This option is required if you are executing osql from a remote computer on the network.
-U login_id - Is the user login ID. Login IDs are case-sensitive.
|
Outer
Joins
|
see joins
|
|
|
|
|
