270 Multiple choice questions
- Used to lock the table.
- A ____ that has a unique clustered index is an ____.
- Secondary XML index useful for when the path to the value is known.
- Tells the number of lock requests per second that timed out, not including NOWAIT locks.
- The available types of recovery models.
- Captures the data that has changed since the last full backup.
to insert a defined value into the column whenever there is not value
specified. Can be used with INSERT to explicitly insert a default value.
- ____ requires that all multi-valued dependencies be removed.
- Option that specified that a table-level lock is used for the duration of the entire bulk-import operation.
- A workspace for holding temporary objects and result sets.
- Displays information for those active user connections made to the server
- ____ date types such as ntext, varchar(max), xml and image ____ be used as the key columns for an index.
index that has a logical order of key values which determines the
physical order of the corresponding rows. Each table or view can have
- Has event classes produced when a SQL Server error or warning is raised.
- The ____ percentage is used only at the time of ____.
- The view must reference ____ tables in the ____ as the view.
you ____ a ____ view it will be disabled, and the associated
nonclustered indexes with get automatically deleted until index is
dropped and rebuilt.
- Very limited values
- To move a system database data or log file to a new instance you:
1. Run ____
2. Then ____ (NAME = logical name, FILENAME = 'path')
- Shows deadlock information by processes and then by resources.
- The result set produced by a ____ is not stored ____.
- You can adjust the ____ of the database via ____.
- You can ____ index fragmentation through ____ or reorganizing an index.
- Shows information about sessions that are opened in the Advisor.
- When size is not supplied, the size of the ____ will be set to the size of the file in the ____.
- Max number of files and filegroups each for each database
- An object that provides counters for monitoring internal SQL Server resource locks.
worker threads, memory, parallel query execution-related resources and
Multiple Active Results Sets resources can all cause ____.
- Reveals /O information for the database data and log files
- Used for preventing phantom reads.
- Used when compiling and executing queries.
- The SET SHOWPLAN statements ____ be used inside a ____ since they must be the only statement in a batch.
- Event class that populates the TextData data column of the trace with data on process and objects involved in the deadlock.
- Takes place when SQL Server needs to scan the index pages to find the records
- Designed for returning information used by the SQL Server Database Engine.
- To use a secondary data file you need to ____, ____, and then ____.
- Back up Database
Check Database Integrity
Execute SQL Server Agent Job
Execute T-SQL Statement
- If a view is ____ referenced, you should create a ____ index on the view so the result can be stored like a table.
- Set single user with T-SQL using ____ and then ____.
- Has event classes used to monitor locking activity in an instance
- A table can have a maximum of ____ indexes.
across all the active sessions and it's name should always be prefixed
with ##. Dropped automatically when session that created the table is
OFF, shared locks will be used to prevent other transactions from
modifying rows while the current transaction is reading, and block the
statement from reading row modified until completed. If ON, row
versioning will be used to present each statement with a consistent
snapshot of the data.
- ____ should be in separate file groups.
- Has event classes dealing with the scanning of tables and indexes
- You ____ keep compressed and ____ backups in the same ____.
- You should supplement a ____ database back up with a series of ____ database backups.
- Use to not allow two rows in a table to have the same value for the columns.
- SQL Server ____'s contained database feature only supports a ____ contained state.
only in the current session and it's name should always be prefixed
with a #. Dropped automatically at the end of the current session.
- You should ____ tuning time to stop during periods of ____ server workload.
- ____ can be performed against the master, model, msdb, ReportServer, ReportServerTempDB, and tempdb databases.
- Used to specify the file that is for collecting rows that have formatting errors.
- The specified temporary table name should not exceed ____.
- ____ should be on ____.
- ____ and ____ should be placed in a different file group on a different physical drive since they are I/O heavy.
- A mechanism that synchronizes access by users to the same data at the same time.
____ server role is required to initialize it. After the initialization
users who are members of the ____ fixed role can use it to tune the
databases they own.
- Used to configure the isolation level
- When size is not supplied for the ____ or ____ the default will be 1 MB.
on, each executed query will return a regular result set and then an
additional result set that details the profile of the query execution.
- Has the backup contents added to the media set by backup process.
- A set of T-SQL statements that execute against a database using trace files, trace tables, and/or T-SQL scripts.
- ____ are factors that can affect SQL Server performance.
- ____ requires that you remove all those columns that are not dependent on the primary key.
- SQL Server will return the execution information for each statement without actually executing it.
- The options that can be used with the CREATE DATABASE statement.
to evaluate filter expressions that are intended to match a prefix set
of columns. Always put the most selective columns leftmost in the key.
- To attach a database make sure all MDF and NDF files are available and use the ____ with the ____ close.
- ____ can never join any filegroup.
that have few ____ or joins would not be suitable for indexed view, and
____ intensive transactions would not be appropriate.
- A constraint linked to a primary key in another table. Can also use columns that use UNIQUE.
- Backups created on a single non-mirrored device or a set of mirrored devices within a media set.
- With the ____ option ____ does not make use of T-SQL at all.
- Has event classes dealing with the execution of stored procedures.
- Online Transaction Processing, requires fast write, fast update
- A ____ can only be a member of a ____.
- Design that requires the construction of a model of information that can be mapped into the various SQL Server objects.
- Process that involves deploying methods to separate data into multiple related tables.
- ____ means breaking a table into multiple smaller ones
- The deadlock XML file extension
- Indicated on a result set. Means the query optimizer determined the statistic needs to be updated.
- Provides the wait state information
- Recovery model that requires log backups and there will be no data loss. Possible to restore to a specific point in time.
- Constructing a data model for dealing with a real world issue without giving any physical considerations.
- The template for all databases created on the instance.
- You should have the master database backed up whenever a ____ is created, changed or dropped.
- Overrides SQL Server's locking scheme by forcing locks on a table.
- The process of producing a detailed database specific data model to meet end user requirements.
- Used to have locks placed on all data used in a query
- Before setting SINGLE_USER mode you must ensure that ____ option is ____.
- Used to define how the integrity of your database can be enforced automatically.
- A database that allows some but not all features that cross the database boundary.
- Used to single an intent to place locks at lower level. They can be intent shared, intent exclusive, or shared intent exclusive.
- Used during DDL operation.
- Tab in Profiler to review the events to be monitored with your trace.
to create Integration Services package to be run by an SQL Server Agent
job. Must be in the sysadmin role to create or manage.
- Tells the number of lock requests per second that resulted in deadlock.
- ____ and ____ files can be detached and attached to another instance. This makes a database easier to move.
- A non-clustered index optimized with a filter predicate to index only a portion of rows in a table.
- Rebuilds a clustered index but does not rebuild the associated nonclustered indexes unless you use the keyword ALL.
- Used when you are bulk copying data into a table while the TABLOCK hint is there.
- ADD FILE
... TO FILEGROUP
- The ____ execution plan is the same as using the SET ____ statement.
- Supposed to have the greatest selectivity since only one row can match.
- If two or more transactions are trying to convert to ____ locks, a ____ will occur.
- Partition the table by group rows based on a particular criteria
concurrent transactions to SELECT a resource under pessimistic
concurrency control. During an update a transaction will attempt to
change to an exclusive lock.
- How SQL Server attempts to determine the best way to execute a submitted query.
- ____ view is good for views with data that is ____ updated (read intensive).
create a ____ maintenance plan there must be one ____ server with one
or more target servers and it must be created and maintained on the
- Used to NOT issue any shared locks or recognize exclusive locks. Use only with the SELECT statement.
- Tell the number of latch request that were not granted immediately.
- ____ is the ____ value for DATAFILETYPE.
to evaluate the selectivity of an index. Shows the current distribution
statistic for a particular table and index, or for all tables and
- Has event classes produced by executing T-SQL statements in an instance.
a partition function and specifies the number of partitions, partition
column and the range of partition column values for each partition.
- You can adjust the file sizing by using ____ and then ____ T-SQL statements.
- Takes place when SQL Server needs to scan the data to find the records.
- ____ monitors activities in real time.
- Records events as they occur. Through a trace you can look at the commands that have been executed against the database.
- Online Analytical Processing, requires fast read and computation on the fly.
- ____ is what the command prompt utility for Database Engine Tuning is known as.
- A value between 5% and ____ is when you should use ____ to reorganize the index
- Used to specify the non-key columns to be added to the leaf level of a nonclustered index.
- The uniqueness of data values contained in a column
- Tells the total latch wait time for latch requests in the last second.
- ____ is optimized for adhoc result aggregation and summarization of large amounts of data.
- The ____ is for recording system-level information for an instance.
- A database level permission that is required on all databases referenced in order to execute SHOWPLAN statements.
- Gathers information of each T-SQL statement for recreating the same activity in the future.
- Tell the number of lock requests per second that timed out including NOWAIT locks.
- A value above ____ is when you should rebuild the index entirely using ____ WITH(ONLINE = ON)*.
- An index build on the same partition scheme as its corresponding table.
- Applications seldom request locks directly as locks are managed by the ____.
- The default for index ____ is ____.
- Same as HOLDLOCK: Used to hold a shared lock until the transaction is completed.
- Has event classes produced when clients connect to and disconnect from an instance
- Has event classes useful for auditing server activity.
- The ratio of qualifying rows to total rows. When low the index is highly selected.
- The process of making room for new records.
- Backups created by recent version of SQL Server ____ be restored to an ____ version.
- Identified the most expensive queries
- An ordered collection of backup media, tapes or disk files
- A file, device or a tape used for backups.
- When using physical disk drives you should create multiple physical files per file group and ____ if possible.
- Used to produce more in-depth details.
model that requires log backups. Permits high-performance bulk copy
operations. It is not possible to restore to a specific point in time.
- The maximum size of a log file.
- Values are very uncommon, uniqueness more likely
- Uses an index to pinpoint a record. Faster than scan.
databases can be accessed by ____ with passwords authenticated by the
database, and authorized ____ and Windows groups who connect directly to
the database without logins in the master database.
- Toll that can examine how queries are processed and then recommend how you may improve the query processing performance.
- ____ databases can ____ be attached.
- All temporary tables will be automatically dropped when the ____ is finished or via the ____ command.
- You can base a ____ on a ____.
to use update locks instead of shared locks when you read a table, and
then hold the locks until the end of the transaction.
- You should use ____ when bulk exporting data to a text file, or bulk importing data from a text file generated by a ____.
- The ____ and the ____ cannot be backed up.
- Exists when your indexes have pages in which the logical ordering does not match the physical ordering inside the physical file.
Management Studio GUI lets you see the Query Execution Plan by
selecting either the Display ____ Plan or ____ Execution Plan.
- When you have ____ and Foreign key constraints SQL Server will ____ create indexes for them.
- The maximum size of a data file.
- The views related to databases and files are ____.
- When all the files in a filegroup are full, the ____ will automatically ____ at a time.
whole database is made offline for the duration and all data is
recovered to a point where no uncommitted transactions exist.
- Secondary XML index useful for performing value-based queries when the full path is not known.
- These determine how much data loss is considered acceptable after a failure and what types of related functions are allowed.
- Same as NOLOCK: Used to NOT issue any shared locks or recognize exclusive locks.
other transactions can change data and read operations can carry on
only with the presence of a NOLOCK hint or read uncommitted isolation
- To create an indexed view ____ and ____ options must be ON when you run CREATE VIEW using WITH SCHEMABINDING.
enabled the percentage of free space that is specified by the fill
factor is applied to the intermediate level pages of an index. It
represents the same thing as fill factor for non-leaf levels.
- Entity Relationship Diagram
- In the ____ options box you can define the ____ for recommendations or by the physical design structures.
- A database that is isolated from other databases and the instance of SQL Server that hosts it.
- System function used to detect index fragmentation.
- ____ is optimized for handling transactions
require that you eliminate duplicative columns from the same table and
create separate tables for each group of related data, and identify a
- A server-level configuration option that is set to off by default. Works to compress the backups.
- CHECK constraints work at the ____ to ____values that evaluate to FALSE.
- To change the fill factor you should be a member of the ____ fixed server role, or the ____ and ____ fixed database roles.
- Tells the average latch wait time for latch requests that had to wait.
- Used to hold a shared lock until the transaction is completed.
- Database mode that allows for only a single user to be connected. All other users will be cut off without warning when set.
- If you create a ____, a unique clustered index on the column will be ____ created.
- Max columns per index file has a ____ value of ____.
- The categories for hints.
- Only one transaction can obtain this to a resource at any point in time.
- Describes the percentage of logical fragmentation.
- ____ and ____ options of ALTER DATABASE allows you to take the instance offline or bring it back online.
- A value that can be char, native, widechar, widenative
- Used to have those rows locked by other processes skipped.
- Has event classes produced when data or log files grow or shrink.
- Used to produce readable output that is to be used by Win32 command prompt applications.
- Suitable when a query is optimized to get the first row of the result set.
no value is specified, the Advisor will assume a space limit of ____
the current raw data size, OR the ____ space on all the attached drives
in addition to raw data size.
- Used to specify that a column does not take NULL values (which are unknown or undefined).
- Used by the SQL Server Agent to schedule alerts and jobs.
model that does not have log backups. Log space is reclaimed to keep
requirements small. Can only recover to the end of a backup.
- Used to use an exclusive lock on all the involved resources until the transaction is ended.
- You may have a maximum of ____ hint from each ____ at the same time.
- A ____ file group should be created for ____.
- SET SHOWPLAN_XML ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS XML ON
SET STATISTICS PROFILE ON
- ____ requires data redundancy by can reduce the number of joins to be use during query
- Used to enforce domain integrity. Use when you want to limit the values that can be entered into a column.
- Used to detach a database currently not in use from an instance.
- Statement used to make a full backup.
- Creates a partition scheme and maps the partitions to a set of filegroups.
- You can have a maximum of ____ per table.
upgrades the Database Engine will automatically disable an index, but
to use T-SQL statements you would use ____ with the ____ option.
- Used to lock data at a row level
- Broken down into categories, they provide different monitoring abilities in the traces.
- Creates a secondary XML index.
- Detaching a ____ will cause you to lose information about the differential bases of your differential backups.
- ____ columns are stored as binary large objects and ____ be given an index. Use ____ to produce a primary XML index.
- Table is portioned based on the frequency of access of columns
DBCC DBREINDEX is an ____ operation, when a nonclustered index is being
rebuilt a ____ will be held on the table preventing modifications. When
a clustered index is being rebuilt an ____ lock will be held,
preventing table access entirely.
- ____ should be backed up whenever it is ____.
- Tells the total wait time for locks in the last second.
as the default group when database is created. Should only be used to
keep system objects, and not as the default file group.
- Shows deadlock information formatted by each node involved.
- The arguments of the partition function must have the same ____, define the same number of ____ and use the same ____ values.
- It's behavior depends on the value of READ_COMMITTED_SNAPSHOT.
to allow SQL Server to execute the T-SQL statements while generating
in-depth information on how the statements were executed.
a visual representation of the real world problem as an ERD and have
the details modeled in terms of entities, attributes and relations.
- Used to enforce uniqueness but they do not allow for NULL
- A ____ can be used only by a single ____.
- You ____ add or remove files when ____ is running.
- Showplan XML
Showplan XML Statistics Profile
Showplan Statistics Profile
- You can use SQL Server ____ to identify the cause by tracing ____.
- BCP data files ____ include any ____ or ____ information.
- Create only ____ database backups of the ____ as required since it rarely changes.
- Tells the average amount of wait time for each lock request.
- According to MS using constraints is perfered over ____, rules and ____.
to specify the percentage of space on each leaf-level page for data
when rebuilding or creating an index. Server-wide default value is 0.
- Used to import a data file into a table or view.
- A feature that uses a proportional fill mechanism across all the files within each filegroup.
- Used to select and create an optimal set of indexes, indexed views, and partitions.
- Collects general information on SQL Server connections, stored procedures and T-SQL Statements.
- An index where the physical order of the rows is totally independent of their indexed order. Can be unique or non-unique.
requires that you remove all subsets of data that apply to multiple
rows of a table and relocate them in other separate tables, and create
- The ____ backup is useful when a subset of data is ____ more often then the rest of the database.
- An index which is partitioned independently from it's corresponding table.
- Secondary XML index useful for the .exist() methods
- Has event classes produced when DML operators are running.
- Used to create a relational index on a specified table or view.
Pages (collection of rows)
Extents (collection of pages)
- You should ____ create a clustered index prior to creating any ____.
backup that captures the log records that have not been backed up in
other ways. Is needed to recover SQL Server to the latest point in time.
- Stored procedure that allows you to estimate the size required for a full backup.
- Tool to help with importing and exporting data.
- Put the queries in a _____ and SQL Server can usually come up with an ____ execution plan.
- SET STATISTICS PROFILE is useful for ____ queries, ____ and ____.
- Means the file can grown until the disk is full (or reaches it's maximum size)
- The ____ created on a view must be a ____ clustered one.
- When you have ____ indexes, ____ queries will run faster, but INSERT, UPDATE, and DELETE will run slower.
- Use ____ with the ____ option to have an index rebuild using the same columns, index type, uniqueness attribute and sort order.
- Data values are uncommon and never unique
- The ____ execution plan is the same as using the ____ XML.
- The values that represents the percentage to fill the data page files on index creation at the index leaf-layer.
- Means it will be a unique index. Cannot be created on columns with duplicate values.
- An object that provides information about SQL Server locks on resources that prevent concurrent use by different transactions.
- Collects information relevant for tuning SQL Server's performance.
- To restore, the ____ must be running and the ____ must be accessible and partly or full usable.
- Used to bulk copy data between an instance and a data file in a separate user-specified format.
- Use for holding system objects that are included with SQL Server and is read only.
- Used to read only data from those already-committed transactions.
- When a ____ constraint references columns in tables in the same database or table, the table becomes a ____ table.