New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • Physical Design

    The process of producing a detailed database specific data model to meet end user requirements.

    Conceptual Database Design

    Constructing a data model for dealing with a real world issue without giving any physical considerations.

    ER Modeling

    Developing a visual representation of the real world problem as an ERD and have the details modeled in terms of entities, attributes and relations.

    ERD stands for

    Entity Relationship Diagram

    Logical Database Design

    Design that requires the construction of a model of information that can be mapped into the various SQL Server objects.

    Data Cardinality

    The uniqueness of data values contained in a column

    High Data Cardinality

    Values are very uncommon, uniqueness more likely

    Normal Data Cardinality

    Data values are uncommon and never unique

    Low Data Cardinality

    Very limited values

    Normalizing

    Process that involves deploying methods to separate data into multiple related tables.

    OLTP

    Online Transaction Processing, requires fast write, fast update

    OLAP

    Online Analytical Processing, requires fast read and computation on the fly.

    OLTP

    ____ is optimized for handling transactions

    OLAP

    ____ is optimized for adhoc result aggregation and summarization of large amounts of data.

    First normal form (1NF)

    ____ require that you eliminate duplicative columns from the same table and create separate tables for each group of related data, and identify a primary key

    Second normal form (2NF)

    ____ 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 foreign keys

    Third normal form (3NF)

    ____ requires that you remove all those columns that are not dependent on the primary key.

    Fourth normal form (4NF)

    ____ requires that all multi-valued dependencies be removed.

    Denormalization

    ____ requires data redundancy by can reduce the number of joins to be use during query

    Partitioning

    ____ means breaking a table into multiple smaller ones

    Horizontal Partitioning

    Partition the table by group rows based on a particular criteria

    Vertical Partitioning

    Table is portioned based on the frequency of access of columns

    Hardware
    Network infrastructure
    OS configuration
    Server side applications
    Client side applications

    ____ are factors that can affect SQL Server performance.

    Database Engine Tuning Adivisor DTA

    Toll that can examine how queries are processed and then recommend how you may improve the query processing performance.

    dta

    ____ is what the command prompt utility for Database Engine Tuning is known as.

    Active User Tasks

    Displays information for those active user connections made to the server

    Resource Waits

    Provides the wait state information

    Data File I/O

    Reveals /O information for the database data and log files

    Recent Expensive Queries

    Identified the most expensive queries

    Activity monitor

    ____ monitors activities in real time.

    Master Database

    The ____ is for recording system-level information for an instance.

    MSDB database

    Used by the SQL Server Agent to schedule alerts and jobs.

    Model database

    The template for all databases created on the instance.

    tempdb database

    A workspace for holding temporary objects and result sets.

    Resource database

    Use for holding system objects that are included with SQL Server and is read only.

    User database

    You should have the master database backed up whenever a ____ is created, changed or dropped.

    Catalog views

    Designed for returning information used by the SQL Server Database Engine.

    sys.backup_devices
    sys.database_files
    sys.database_mirroring
    sys.database_recovery_status
    sys.databases
    sys.master_files

    The views related to databases and files are ____.

    full, model database

    Create only ____ database backups of the ____ as required since it rarely changes.

    MSDB, updated

    ____ should be backed up whenever it is ____.

    Resource database, tempdb

    The ____ and the ____ cannot be backed up.

    Instance, master database

    To restore, the ____ must be running and the ____ must be accessible and partly or full usable.

    ALTER DATABASE database_name MODIFY FILE
    Stop the instance
    Move the file
    Restart the instance

    To move a system database data or log file to a new instance you:
    1. Run ____
    2. Then ____ (NAME = logical name, FILENAME = 'path')
    3. ____
    4. ____

    SQL Server's Filegroups

    A feature that uses a proportional fill mechanism across all the files within each filegroup.

    Database Engine, expand one file

    When all the files in a filegroup are full, the ____ will automatically ____ at a time.

    File, single filegroup

    A ____ can only be a member of a ____.

    Transaction log files

    ____ can never join any filegroup.

    File or filegroup, database

    A ____ can be used only by a single ____.

    FILENAME SIZE MAXSIZE

    The options that can be used with the CREATE DATABASE statement.

    Primary file, model database

    When size is not supplied, the size of the ____ will be set to the size of the file in the ____.

    Secondary data file, log file

    When size is not supplied for the ____ or ____ the default will be 1 MB.

    UNLIMITED

    Means the file can grown until the disk is full (or reaches it's maximum size)

    2 TB

    The maximum size of a log file.

    16 TB

    The maximum size of a data file.

    Primary file group

    Serves as the default group when database is created. Should only be used to keep system objects, and not as the default file group.

    Create a new filegroup
    Mark as Default
    Create new data file (.ndf)

    To use a secondary data file you need to ____, ____, and then ____.

    Tables and Indexes

    ____ should be in separate file groups.

    Logs, another disk

    ____ should be on ____.

    ALTER DATABASE statement options

    ADD FILE
    ... TO FILEGROUP
    REMOVE FILE
    MODIFY FILE
    ADD FILEGROUP
    REMOVE FILEGROUP
    MODIFY FILEGROUP

    May not, BACKUP

    You ____ add or remove files when ____ is running.

    32,767

    Max number of files and filegroups each for each database

    Host one file per disk.

    When using physical disk drives you should create multiple physical files per file group and ____ if possible.

    Separate, indexes

    A ____ file group should be created for ____.

    Text, image columns

    ____ and ____ should be placed in a different file group on a different physical drive since they are I/O heavy.

    Single User Mode

    Database mode that allows for only a single user to be connected. All other users will be cut off without warning when set.

    AUTO_UPDATE_STATISTICS_ASYNC, OFF

    Before setting SINGLE_USER mode you must ensure that ____ option is ____.

    ALTER DATABASE, SET SINGLE_USER

    Set single user with T-SQL using ____ and then ____.

    Local temporary tables

    Maintained only in the current session and it's name should always be prefixed with a #. Dropped automatically at the end of the current session.

    Global temporary tables

    Maintained across all the active sessions and it's name should always be prefixed with ##. Dropped automatically when session that created the table is ended.

    116 characters

    The specified temporary table name should not exceed ____.

    stored procedure, DROP TABLE

    All temporary tables will be automatically dropped when the ____ is finished or via the ____ command.

    Size, Object Explorer

    You can adjust the ____ of the database via ____.

    ALTER DATABASE, MODIFY FILE

    You can adjust the file sizing by using ____ and then ____ T-SQL statements.

    SET OFFLINE, SET ONLINE

    ____ and ____ options of ALTER DATABASE allows you to take the instance offline or bring it back online.

    Data, Transaction log

    ____ and ____ files can be detached and attached to another instance. This makes a database easier to move.

    Read-only database

    Detaching a ____ will cause you to lose information about the differential bases of your differential backups.

    System, never

    ____ databases can ____ be attached.

    sp_detach_db

    Used to detach a database currently not in use from an instance.

    CREATE DATABASE, FOR ATTACH

    To attach a database make sure all MDF and NDF files are available and use the ____ with the ____ close.

    Contained Database

    A database that is isolated from other databases and the instance of SQL Server that hosts it.

    Partially Contained Database

    A database that allows some but not all features that cross the database boundary.

    2012, partially

    SQL Server ____'s contained database feature only supports a ____ contained state.

    Contained database users, Windows users

    Contained 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.

    Media set

    An ordered collection of backup media, tapes or disk files

    Media family

    Backups created on a single non-mirrored device or a set of mirrored devices within a media set.

    Backup set

    Has the backup contents added to the media set by backup process.

    BACKUP

    Statement used to make a full backup.

    Backup media

    A file, device or a tape used for backups.

    Cannot, earlier

    Backups created by recent version of SQL Server ____ be restored to an ____ version.

    Full, differential

    You should supplement a ____ database back up with a series of ____ database backups.

    Differential backup

    Captures the data that has changed since the last full backup.

    Differential, modified

    The ____ backup is useful when a subset of data is ____ more often then the rest of the database.

    sp_spaceused

    Stored procedure that allows you to estimate the size required for a full backup.

    Backup compression

    A server-level configuration option that is set to off by default. Works to compress the backups.

    Cannot, uncompressed, media set

    You ____ keep compressed and ____ backups in the same ____.

    Full
    Simple
    Bulk-logged

    The available types of recovery models.

    Recovery models

    These determine how much data loss is considered acceptable after a failure and what types of related functions are allowed.

    Simple model

    Recovery model that does not have log backups. Log space is reclaimed to keep requirements small. Can only recover to the end of a backup.

    Full model

    Recovery model that requires log backups and there will be no data loss. Possible to restore to a specific point in time.

    Bulk-logged model

    Recovery model that requires log backups. Permits high-performance bulk copy operations. It is not possible to restore to a specific point in time.

    Complete database restore

    The whole database is made offline for the duration and all data is recovered to a point where no uncommitted transactions exist.

    Tail-log backup

    Special 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.

    Maintenance Plan

    Used to create Integration Services package to be run by an SQL Server Agent job. Must be in the sysadmin role to create or manage.

    Maintenance Plan Tasks

    Back up Database
    Check Database Integrity
    Execute SQL Server Agent Job
    Execute T-SQL Statement
    History Cleanup
    Maintenance Cleanup
    Notify Operator
    Rebuild Index
    Reorganize Index
    Shrink Database
    Update Statistics

    Multiserver, master

    To create a ____ maintenance plan there must be one ____ server with one or more target servers and it must be created and maintained on the master server.

    Import/Export Wizard

    Tool to help with importing and exporting data.

    BULK INSERT

    Used to import a data file into a table or view.

    DATAFILETYPE

    A value that can be char, native, widechar, widenative

    Char, default

    ____ is the ____ value for DATAFILETYPE.

    TABLOCK

    Option that specified that a table-level lock is used for the duration of the entire bulk-import operation.

    ERRORFILE

    Used to specify the file that is for collecting rows that have formatting errors.

    BCP Utility

    Used to bulk copy data between an instance and a data file in a separate user-specified format.

    Queryout, bcp utility

    With the ____ option ____ does not make use of T-SQL at all.

    Do not, schema, format

    BCP data files ____ include any ____ or ____ information.

    Character format, third party program

    You should use ____ when bulk exporting data to a text file, or bulk importing data from a text file generated by a ____.

    SQL Server Profiler

    Records events as they occur. Through a trace you can look at the commands that have been executed against the database.

    Event Classes

    Broken down into categories, they provide different monitoring abilities in the traces.

    Locks event category

    Has event classes used to monitor locking activity in an instance

    Database event category

    Has event classes produced when data or log files grow or shrink.

    Errors and Warnings event category

    Has event classes produced when a SQL Server error or warning is raised.

    Performance event category

    Has event classes produced when DML operators are running.

    Scans event category

    Has event classes dealing with the scanning of tables and indexes

    Sessions event category

    Has event classes produced when clients connect to and disconnect from an instance

    Stored Procedures event category

    Has event classes dealing with the execution of stored procedures.

    TSQL event category

    Has event classes produced by executing T-SQL statements in an instance.

    Security Audit event category

    Has event classes useful for auditing server activity.

    Events Selection

    Tab in Profiler to review the events to be monitored with your trace.

    Trace, template

    You can base a ____ on a ____.

    Standard template

    Collects general information on SQL Server connections, stored procedures and T-SQL Statements.

    Tuning template

    Collects information relevant for tuning SQL Server's performance.

    TSQL_Replay template

    Gathers information of each T-SQL statement for recreating the same activity in the future.

    Database Engine Tuning Advisor

    Used to select and create an optimal set of indexes, indexed views, and partitions.

    Limit, heavy

    You should ____ tuning time to stop during periods of ____ server workload.

    Workload

    A set of T-SQL statements that execute against a database using trace files, trace tables, and/or T-SQL scripts.

    sysadmin fixed, db_owner

    The ____ 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.

    Workload analysis

    ____ can be performed against the master, model, msdb, ReportServer, ReportServerTempDB, and tempdb databases.

    Advanced, max space

    In the ____ options box you can define the ____ for recommendations or by the physical design structures.

    3 times, free

    If 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.

    Default, 1023

    Max columns per index file has a ____ value of ____.

    Session Monitor

    Shows information about sessions that are opened in the Advisor.

    Constraints

    Used to define how the integrity of your database can be enforced automatically.

    DML Triggers, defaults

    According to MS using constraints is perfered over ____, rules and ____.

    NOT NULL

    Used to specify that a column does not take NULL values (which are unknown or undefined).

    Primary keys

    Used to enforce uniqueness but they do not allow for NULL

    Foreign keys

    A constraint linked to a primary key in another table. Can also use columns that use UNIQUE.

    Foreign key, self-referencing

    When a ____ constraint references columns in tables in the same database or table, the table becomes a ____ table.

    Check

    Used to enforce domain integrity. Use when you want to limit the values that can be entered into a column.

    Row level, reject

    CHECK constraints work at the ____ to ____values that evaluate to FALSE.

    Unique

    Use to not allow two rows in a table to have the same value for the columns.

    See More

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature