Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
115 Cards in this Set
- Front
- Back
- 3rd side (hint)
|
The acronym ETL stands for?
|
Extract, Transform, Load
|
|
|
|
The acronym BI stands for?
|
Business Intelligence
|
|
|
|
What information does the Master database contain?
|
The meta data for the SQL server.
|
|
|
|
What information does the MSDB database contain?
|
Information about SQL jobs and other automated functions.
|
|
|
|
What is the Model database used for?
|
It is the basic template for all user created databases.
|
|
|
|
List the database objects that fall under the category of a "securable".
|
Databases, Tables, Indexes, Triggers, Views, Etc.
|
|
|
|
What is the difference between the "grant" and "with grant" permissions?
|
"Grant" allows access to an object or function and "with grant" allows the user access to an object or function and the ability to grant others access to the object or function.
|
|
|
|
What is the "Direct Admin Connection" and how do you access it?
|
It is the last line of connectivity to a server having issues and is access able via the SQLCMD prompt.
|
|
|
|
What is the tempdb database used for?
|
The tempdb database stores temporary tables and information.
|
|
|
|
When does the tempdb database dump its contents?
|
Upon server restart and when instructed to do so by the server or administrator.
|
|
|
|
What is the major difference between schemas in SQL 2000 and SQL 2005?
|
Schemas in SQL 2000 where associated exclusively with a particular database user acccount where schemas in SQL 2005 are not.
|
|
|
|
List the different database logging recovery models.
|
Simple, Bulk-Logged & Full
|
|
|
|
How does the "Full" recovery model allow for?
|
Logs all transactions occurring within a particular database.
|
|
|
|
How does the "Bulk-Logged" recovery model allow for?
|
Logs all transactions occurring within a particular database except those occuring as part of a BULK INSERT.
|
|
|
|
What types of operations are not logged via the "Bulk-Logged" recovery model?
|
BCP, BULK INSERT, WRITETEXT, UPDATETEXT, SELECT INTO, CREATE; ALTER; DBCC DBREINDEX index commands.
|
|
|
|
What happens to transactions in the transaction log when the "Simple" recovery model is in place?
|
Transactions are deleted from the transaction log upon being committed into the database.
|
|
|
|
How many bytes are in a Page?
|
Eight Thousand One Hundred Ninty-Two (8,192)
|
|
|
|
How many pages are in an Extent?
|
Eight (8)
|
|
|
|
How many bytes are in an Extent?
|
Sixty-Four Thousand (64K) aka 65,536 Bytes
|
|
|
|
What is the acronym for the management studio?
|
SSMS
|
|
|
|
How much space is required for the BIT datatype?
|
One (1) bit is required per value reserved in one (1) Byte increments for every 8 values.
|
|
|
|
What is the name of the naming convention that capitolizes the first character of each individual word?
|
Camel Case
|
|
|
|
What are the names of the different data types in SQL server?
|
1) Exact Numerics
2) Approximate Numerics 3) Date and Time 4) Character Strings 5) Unicode Character Strings 6) Binary Strings 7) Other (not the official name) |
|
|
|
What is the name of the sample database included with SQL 2005?
|
AdventureWorks
|
|
|
|
What is the name of the sample database included with SQL 2000?
|
NorthWind
|
|
|
|
What data types are contained within the Exact Numerics group?
|
BIGINT, INT, SMALLINT, TINYINT, BIT, DECIMAL, NUMERIC, MONEY & SMALLMONEY
|
|
|
|
How many bytes are required for the BIGINT datatype?
|
8 Bytes
|
|
|
|
What is the numeric range for the BIGINT data type?
|
Negative to positive nine (9) followed by eighteen (18) zeros (roughly)
|
|
|
|
What is the numeric range for the INT data type?
|
Just over negative to positive two (2) billion
|
|
|
|
How many bytes are required for the INT data type?
|
Four (4) Bytes
|
|
|
|
How many bytes are required for the SMALLINT data type?
|
Two (2) Bytes
|
|
|
|
How many bytes are required for the TINYINT data type?
|
One (1) Byte
|
|
|
|
What is the numeric range for the SMALLINT data type?
|
-32,768 to 32,767
|
|
|
|
What is the numeric range for the TINYINT data type?
|
0 - 255
|
|
|
|
What is the maximum percision of the DECIMAL data type?
|
thirty-eight (38) digits to the left or right of the decimal point.
|
|
|
|
How many bytes are required for the DECIMAL data type?
|
Up to seventeen (17) bytes
|
|
|
|
Which datatypes not going to be supported in future releases of SQL Server?
|
Numeric
|
|
|
|
What are the new data types as of SQL 2005?
|
XML, VARCHAR(MAX), NVARCHAR(MAX)
|
|
|
|
What are the three levels of Principals?
|
Windows, SQL Server, Database
|
OS, Server, Primary Object
|
|
|
What are the Principal objects at the database level?
|
1) Database User
2) Database Role 3) Database Group 4) Application Role |
Four objects
|
|
|
What is a Principal?
|
Anything or anybody that can be given permission to access something.
|
|
|
|
What are the two types of Principals?
|
1) Individual aka Single Identities
2) Collection aka Fixed Server Roles |
Two Objects
|
|
|
What are the Principal objects at the SQL Server level?
|
1) SQL Server Login
2) SQL Server Role |
Two Objects
|
|
|
What are the Principal objects at the Windows level?
|
1) Local User Account
2) Domain User Account 3) Group |
Three objects
|
|
|
What are Securables?
|
Objects that are accessed
|
|
|
|
What are the different scopes of Securables?
|
1) Server
2) Database 3) Schema |
Three objects
|
|
|
What are the Server Securables?
|
1) Logins
2) HTTP Endpoints 3) Certificates 4) Events 5) Databases |
Five objects
|
|
|
What are the Database Securables?
|
1) Users
2) Roles 3) Application Roles 4) Assemblies 5) DDL Events 6) Schemas 7) Services 8) Message Types |
Eight objects
|
|
|
What is the TLA CTE?
|
Common Table Expressions
|
Assists with recrussive queries.
|
|
|
What are the benefits for the "Snapshot Isolation Level" in SQL 2005?
|
1) Better isolates of transactions
2) No locks placed when reading data 3) No dirty reads 4) Uses tempdb to store row versions 5) Can significantly improve concurrency |
|
|
|
What is the SQLCLR?
|
SQL Common Language Runtime
|
|
|
|
What does the SQLCLR allow for?
|
The SQLCLR allows you to run .NET code within Transact-SQL. Improves processor intensive tasks. Allows for regular expressions.
|
|
|
|
What is the TLA SMO?
|
SQL Management Objects
|
|
|
|
What is the TLA RMO?
|
Replication Management Objects
|
|
|
|
What does the acronym BIDS stand for?
|
Business Intelligence Development Studio
|
|
|
|
What is the Business Intelligence Development Studio used for?
|
It is a development environment for:
1) Analysis Services 2) Intergration Services 3) Reporting Services |
|
|
|
What component replaces DTS in SQL 2005?
|
SSIS (SQL Server Intergration Services)
|
|
|
|
What does the acronym SSIS stand for?
|
SQL Server Intergation Services
|
|
|
|
What features are included with SQL Server Intergration Services?
|
1) Graphical tools and wizards
2) Tasks for performing workflow functions 3) Data sources and destinations for extracting and loading data 4) Transformations for cleaning, aggregating, merging, and copying data 5) Intergration Services packages APIs 6) APIs for Intergration Services object model |
|
|
|
What is an aggreagte query?
|
TBD
|
No Printed Answer
|
|
|
What is an aggreagte query?
|
TBD
|
No Printed Answer
|
|
|
What are SQL Reporting Services?
|
Server-based reporting platform containing:
1) Tools for creating, managing and viewing reports 2) Report server to host and process reports 3) API to create application or custom tools |
|
|
|
What type of data can be processed by Reporting Services?
|
Relational and Multidimensional data
|
Two Types
|
|
|
What data sources are supported by Reporting Services?
|
1) SQL Server
2) Analysis Server 3) Oracle 4) >NET Framework data providers (OLEDB & ODBC giving access to MySQL, etc.) |
Four General Sources
|
|
|
What does the acronym OLAP stand for?
|
Online Analytical Processing
|
Three Words
|
|
|
What does the acronym OLTP stand for?
|
Online Transaction Processing
|
Three Words
|
|
|
What does the TLA UDM stand for?
|
Unified Dimensional Model
|
|
|
|
What are two types of multideminsional data schemas?
|
1) Star
2) Snowflake |
|
|
|
What is a Fact Table?
|
TBD
|
|
|
|
SQL Server can run under different user accounts. What are they?
|
1) A "Domain User Account" (generally the best option when accessing other servers/network resources)
2) The "Local System Account" (never use) 3) The "Local Service Account" (okay to use, no access to network resources) 4) The "Network Service Account" (same as local service account with network access support) |
four answers
|
|
|
Does SQL Server 2000 support multiple instances on a single server?
|
No (not totally proven yet)
|
|
|
|
What is the benefit of using multiple instances?
|
Allows you to continue using older versions of SQL as the default instance while installing new SQL 2005 named instances on the system.
|
|
|
|
How many instances of SQL 2005 can exist on a single server?
|
Enterprise Version = 50
All Other Versions = 16 |
|
|
|
What version/edition can you upgrade SQL Server 2000 Enterprise Edition to?
|
SQL Server 2005 Enterprise Edition ONLY
|
|
|
|
What is likely to break when upgrading SQL 2000 to SQL 2005?
|
TBD
|
Some features may just be depricated
|
|
|
What is the purpose of the Full-Text Search Service?
|
TBD
|
|
|
|
What is the purpose of the SQL Browser Service?
|
TBD
|
|
|
|
What is the name of the application that give you an overview and allow you to make GENERAL/SERVICE configuration changes on the SQL Server installation(s)?
|
SQL Server Configuration Manager
|
|
|
|
What is the default communications port number for SQL Server?
|
1433
|
|
|
|
What is the name of the application that give you an overview and allow you to make FEATURE configuration changes on the SQL Server installation(s)?
|
SQL Server 2005 Surface Area Configuration
|
|
|
|
What does the TLA AWE stand for?
|
Address Windowing Extensions
|
|
|
|
What does the AWE setting do?
|
Allows SQL Server to control its physical memory space directly on 32-bit Windows 2000/2003 server systems ONLY.
|
|
|
|
How much physical memory does the AWE setting allow SQL Server to access?
|
Sixty-Four (64) Gigabytes
|
|
|
|
What does the acronym TLA stand for?
|
Three Letter Acronym
|
|
|
|
True or False. The AWE setting will relinquish physical memory to other applications installled on the server as needed?
|
True
|
|
|
|
As it relates to the SQL Server "Maximum Server Memory (in MB)" setting. What is the default value (2147483647) equate to?
|
2048 Terabytes
|
|
|
|
What do the "Configured Values" and "Running Values" radio butttons within various SQL Server configuration dialogs allow you to determine?
|
The allow you to see what settings are currently configured versus what settings are currently running. Helps to determine if a restart of SQL Server is required to implement various configuration changes.
|
|
|
|
How does the "Maximum Worker Threads" setting affect SQL Server?
|
It helps limit the resource utilization. Each connection to the server requires a new thread.
|
|
|
|
How does the "Boost SQL Server Priority" setting affect SQL Server?
|
Instructs the Windows Server operating system to give the SQL Server threads a higher priority
|
|
|
|
How does the "Use Windows fibers (lightweight pooling)" setting affect SQL Server?
|
TBD
|
|
|
|
How does the "Enable Server Proxy Account" setting affect SQL Server?
|
This setting overrides the default user account (typically the SQL Server service account) under which xp_cmdshell commands are executed.
|
|
|
|
How does the "Cross database ownership chaining" setting affect SQL Server?
|
Very dangerous setting. Used when database ownership must be maintained across multiple SQL Server instances.
|
|
|
|
How does the "Use query governor to prevent long-running queries" setting affect SQL Server?
|
Sets the maximum number of seconds that a query is allowed to run. If the query does not finish in the alloted time all transactions associated with the query are rolled-back and the query is shutdown.
|
|
|
|
What are the various options that can be set under the "Default Connection Options" setting?
|
1) Implicit Transactions
2) Cursor Close On Commit 3) ANSI Warnings 4) ANSI Padding 5) ANSI NULLS 6) Arithmetic Abort 7) Arithmetic Ignore 8) Quoted Identifier 9) No Count 10) ANSI NULL Default On 11) ANSI NULL Default Off 12) CONCAT NULL Yields NULL 13) Numeric Round Abort 14) XACT Abort |
|
|
|
How does the "Require Distributed transactions for server-to-server communication" setting affect SQL Server?
|
TBD
|
|
|
|
How does the "Reovery Interval" setting affect SQL Server?
|
TBD
|
|
|
|
What does the acronym RDBMS stand for?
|
Relational Database Management System
|
|
|
|
Who created the relational database design principals?
|
Dr. E.F. Codd at IBM (aka Ted Codd)
|
|
|
|
What is "Set Thoery"?
|
TBD
|
|
|
|
What is "Predicate Logic"?
|
TBD
|
|
|
|
How many levels are contained within the "Normal Forms"?
|
Three (3) basic normal forms followed by four (4) additional advanced forms.
|
|
|
|
What is the term "Relation" mean in set thoery?
|
It refers to a table itself
|
|
|
|
What is the term "Attribute" mean in set thoery?
|
It refers to the table columns
|
|
|
|
What is the term "Tulpe" (ta-ple) mean in set thoery?
|
It refers to the rows in a table
|
|
|
|
What is the term "Candidate Key" mean in set thoery?
|
Means of uniquely identifying a tuple in a relation based on one or more of its attributes.
|
|
|
|
What is the term "Primary Key" mean in set thoery?
|
It is the attribute or combination of attributes by which each row is determined as unique.
|
|
|
|
What is the "Surrogate Key" mean in set thoery?
|
It is "new" unique value (i.e. autonumber, GUID, etc) wen none of the candidate keys are suitable to use as a primary key.
|
|
|
|
What is the "First Normal Form"?
|
"The Key,..."
First normal form requires that each attribute in a relation contain only one piece of information that you care about, and each piece of information that you care about should be held in only one attribute. |
|
|
|
What is the "Second Normal Form"?
|
"...the whole key,..."
The database should be in first normal form, and every attribute should be functionally dependent on the entire primary key. (Each relation should model just one entity or event) |
|
|
|
What is the "Third Normal Form"?
|
"...and nothing but the key, so help me Codd."
The database should be in second normal form, and no attribute should be functionally dependent on an attribute that isn't in the primary key. (Example: Don't store calculated values. Store the raw data, and use queries to perform calculations as needed unless the calculated values are automatically maintained, wich is really a view.) |
|
|
|
What is the minimum number of table to represent a many-to-many entity relationship?
|
Three (3)
(with the middle, reslover, or junction table storing the "pairings") |
|
|
|
What are the two types of classes as it relates to relational data?
|
1) Base
2) Derived |
|
|
|
What is a cascading update?
|
When a change is made to a primary key it will change all the cascade over to any related foreign keys.
|
|
|
|
What are the two new options created for foreign key cascading updates/deletes in SQL 2005?
|
1) Set NULL
2) Set Default |
|
|
|
Relational Database Engines are to assist in the enforcement of data intergrity. What are the three types of "Integrity"?
|
1) Entity Integrity
2) Domain Integrity 3) Referential Integrity (which are all forms of domain integrity) |
Question Not Complete
|
