Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Databases > 5052 EXAM > Flashcards

5052 EXAM Flashcards

(63 cards)

Start Studying
1
Q

What are the 5 advantages of stored procedures?

A
  1. Reusable and Consistent
  2. Security and Control
  3. Modular
  4. Speed
  5. Network
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are 3 disadvantages of stored procedures?

A
  1. Server load
  2. Single Query Plan
  3. Not portable
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What symbol is used to denote a variable or parameter?

A

@

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What keyword is used to call a stored procedure?

A

EXECUTE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How do you suppress row count information in a stored procedure?

A

SET NOCOUNT ON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Is it possible to have multiple outputs in a stored procedure?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a scalar function?

A

A function that returns a single value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the rule with brackets around the parameter list regarding procedures and functions?

A

Stored Procedures = Optional
Functions = Required

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How do you specify the return in a function?

A
  • RETURNS (DataType) after the parameter list
  • RETURN (variable) at end of function body
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Which can be used as an expression, stored procedure or function?

A

Function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What does ACID stand for?

A

Atomicity
Consistency
Isolation
Durability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What does atomicity mean?

A

Entire transaction either succeeds or fails

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What does a consistent transaction ensure?

A

The system will return to the state it was in before the transaction

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which ACID principle(s) apply to transactions still executing?

A

Isolation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Which ACID principle(s) apply to completed transactions?

A

Atomicity, Consistency, and Durability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How do you start a transaction?

A

BEGIN TRANSACTION

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How do you specify to reverse a transaction?

A

ROLLBACK TRANSACTION

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How do you specify to save the changes made by a transaction?

A

COMMIT TRANSACTION

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are 2 types of locks?

A
  1. Exclusive Lock
  2. Shared Lock
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

When does the system use an exclusive lock?

A

INSERT, UPDATE, DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

When does the system use a shared lock?

A

Read functions, like SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are 3 ways to prevent deadlocks?

A
  1. Control the flow (stored procedures)
  2. Keep transactions small
  3. Consolidate code
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the rule for default values for stored procedure parameters?

A

Must be constants or NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What does SCOPE_IDENTITY() return?

A

Last Identity value created in scope

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
When specifying parameters by name in an execute statement, what side of the '=' is the parameter?
Left
26
What is the best practice regarding conditional statements?
Use BEGIN and END the same way you would braces
27
How do we short-circuit a stored procedure?
RETURN
28
How do we handle errors with stored procedures?
SET XACT_ABORT ON
29
What is an inline view?
Subquery in a FROM clause
30
What keyword is used to create a CTE?
WITH
31
What does CTE stand for?
Common Table Expression
32
What are the 3 categories of WINDOW functions?
1. Rank 2. Aggregate 3. Analytic
33
What keyword is used to define WINDOW functions?
OVER
34
What is an issue when using RANK()?
Causes gaps if identical values (1, 1, 3)
35
How do you solve the problems associated with RANK()?
DENSE_RANK()
36
What are the 3 analytic functions?
1. LEAD 2. LAG 3. FIRST_VALUE
37
What does SSRS stand for?
SQL Server Reporting Services
38
What does bottom-up dimensional modelling entail?
Start by building data marts for specific functions, then integrate into data warehouse
39
Are backups important for a data warehouse?
No, it isn't the primary source of the data
40
What is different with the queries in an OLTP vs a Data Warehouse?
Data Warehouse queries are much larger and more complex
41
What are data warehouses optimized for?
Reads
42
What is a major difference between a star-schema dimensional model and a snowflake-schema?
Snowflake-schema dimensions can reference other dimensions
43
What is a table value function?
A function that returns a table
44
What kind of data is held in a fact table?
Quantitative
45
What kind of data is held in a dimension table?
Qualitative
46
What is another word for quantitative data in a fact table?
Measure
47
Which table in a data warehouse helps define granularity?
Dimension Table
48
What granularity is more detailed?
Fine grain
49
What granularity is aggregated data?
Coarse grain
50
What does increasing granularity mean for rows?
They increase as well
51
What is a major constraint of granularity?
Data sources
52
What are the 4 steps in creating a dimensional model?
1. Identify process to be tracked 2. Define granularity 3. Define dimensions 4. Define facts and measures
53
What is the key for a fact table?
Alternate key consisting of all the foreign keys together
54
What is the most common and most fundamental dimension?
Date Table
55
What kind of key does the date dimension use?
Smart Key
56
What is a slowly changing dimension?
Any non-key attribute can change over time
57
What is a Type 0 SCD?
No attributes may ever change over time
58
What is a Type 1 SCD?
Non-key attributes can overwrite the original value when changed
59
What is a Type 2 SCD?
Non-key attributes change by adding a new record
60
What must be added to a Type 2 SCD dimension table?
Start and End date
61
What does ETL stand for?
Extract, Transform, Load
62
What is SSIS?
SQL Server Integration Services
63
What is a control table?
Logs start and finish of ETL tasks

Databases (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions