Optimization Category
Table Organization
Triggers
Misc
Organization and Optimization
100
This is indexed by default
What is the primary key
100
This type of table organization is usually stored in order by primary key
What is sequential
100
How do I select a variable that has just been inserted into a database?
Select variable FROM inserted
100
What is the highest priority of file organization?
What is fast data retrieval
100
This is the result of a relational engine
What is a query plan
200
Your query will run the slowest if you have one of these.
What is cartesian product
200
Uses an algorithm to store the primary key value into a record address
What is hashed
200
What key word can I use to pull 1 value from a table?
Top 1 or Limit 1 Bonus: How can I pull the last value?
200
ETL stands for this
What is extract, transform, load
200
This can be thought of as the step by step instructions for a query
What is a query plan
300
This clause is the thing that causes a query to run the second slowest.
What is an Order By Clause
300
Distributes the rows or columns of a logical relation into several separate tables
What is partitioning
300
When do triggers execute?
In response to an event or after a certain amount of time passes
300
TRUE or FALSE: Queries on the data warehouse have an online system impact
FALSE
300
Of the 3 types of file organizations, which one allows you to update records the easiest?
What is hashed
400
What are some limits a dbms can have on indexing?
It can limit the number of indexes per table or The number of bytes per indexed field
400
Records could be stored sequentially or non-sequentially. Used for locating individual records
What is indexing
400
What are some reasons triggers are used?
Ensure Referential Integrity, Enforce Business Rules, Create Audit Trails, Replicate Tables, Activate a Procedure
400
WHat key words begin a transaction? What key words end a transaction?
Begin Transaction Commit What is the other keyword for transactions and what does it do?
400
Which file organization has the least space wasted?
What is sequential
500
What are the 3 ways to improve select statement performance?
Denormalize, Query Optimization, Table Organization
500
Why does denormalization improve select statement performance?
Because you don't need to do joins.
500
What goes after each part of the trigger statement? CREATE TRIGGER ON AFTER AS
CREATE TRIGGER (Table Name) On (table or view) AFTER (insert, delete, or update) As (sql statement) Notice the 3 parts (the name, the action, and the execution)
500
What symbol do we use to create a variable in a procedure?
What is an @
500
Which file organization is the fastest for multiple-key retrieval
What is Indexed (especially with multiple indexes)
M
e
n
u