Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Databases > 5052 EXAM > Flashcards

5052 EXAM Flashcards

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
Q

When specifying parameters by name in an execute statement, what side of the ‘=’ is the parameter?

A

Left

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

What is the best practice regarding conditional statements?

A

Use BEGIN and END the same way you would braces

27
Q

How do we short-circuit a stored procedure?

28
Q

How do we handle errors with stored procedures?

A

SET XACT_ABORT ON

29
Q

What is an inline view?

A

Subquery in a FROM clause

30
Q

What keyword is used to create a CTE?

31
Q

What does CTE stand for?

A

Common Table Expression

32
Q

What are the 3 categories of WINDOW functions?

A
  1. Rank
  2. Aggregate
  3. Analytic
33
Q

What keyword is used to define WINDOW functions?

34
Q

What is an issue when using RANK()?

A

Causes gaps if identical values (1, 1, 3)

35
Q

How do you solve the problems associated with RANK()?

A

DENSE_RANK()

36
Q

What are the 3 analytic functions?

A
  1. LEAD
  2. LAG
  3. FIRST_VALUE
37
Q

What does SSRS stand for?

A

SQL Server Reporting Services

38
Q

What does bottom-up dimensional modelling entail?

A

Start by building data marts for specific functions, then integrate into data warehouse

39
Q

Are backups important for a data warehouse?

A

No, it isn’t the primary source of the data

40
Q

What is different with the queries in an OLTP vs a Data Warehouse?

A

Data Warehouse queries are much larger and more complex

41
Q

What are data warehouses optimized for?

42
Q

What is a major difference between a star-schema dimensional model and a snowflake-schema?

A

Snowflake-schema dimensions can reference other dimensions

43
Q

What is a table value function?

A

A function that returns a table

44
Q

What kind of data is held in a fact table?

A

Quantitative

45
Q

What kind of data is held in a dimension table?

A

Qualitative

46
Q

What is another word for quantitative data in a fact table?

47
Q

Which table in a data warehouse helps define granularity?

A

Dimension Table

48
Q

What granularity is more detailed?

A

Fine grain

49
Q

What granularity is aggregated data?

A

Coarse grain

50
Q

What does increasing granularity mean for rows?

A

They increase as well

51
Q

What is a major constraint of granularity?

A

Data sources

52
Q

What are the 4 steps in creating a dimensional model?

A
  1. Identify process to be tracked
  2. Define granularity
  3. Define dimensions
  4. Define facts and measures
53
Q

What is the key for a fact table?

A

Alternate key consisting of all the foreign keys together

54
Q

What is the most common and most fundamental dimension?

A

Date Table

55
Q

What kind of key does the date dimension use?

56
Q

What is a slowly changing dimension?

A

Any non-key attribute can change over time

57
Q

What is a Type 0 SCD?

A

No attributes may ever change over time

58
Q

What is a Type 1 SCD?

A

Non-key attributes can overwrite the original value when changed

59
Q

What is a Type 2 SCD?

A

Non-key attributes change by adding a new record

60
Q

What must be added to a Type 2 SCD dimension table?

A

Start and End date

61
Q

What does ETL stand for?

A

Extract, Transform, Load

62
Q

What is SSIS?

A

SQL Server Integration Services

63
Q

What is a control table?

A

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