Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Sql 70-761 Certification > Session 7 - Transactions, Indexes, Optimize Queries, dynamic views, cursors > Flashcards

Session 7 - Transactions, Indexes, Optimize Queries, dynamic views, cursors Flashcards

1
Q

What is the term for when a database searches an indexed value?

A

SEEK

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

What is the term for when a database searches for a non indexed value?

A

SCAN

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

What are the characteristics of a transaction?

A
  • Atomicity - Single unit of work
  • Consistency - Everything has to be done
  • Isolation - Each transaction must be isolated (database locking)
  • Durability - Once a transaction is done, it is committed - Permanent
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is it called when you write a statement that starts and ends without the user telling the computer that it’s a transaction?

A

Implicit Transaction.

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

What is it called when you tell the computer when you start and end a transaction?

A

Explicit Transaction.

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

What are the two ways to end a transaction?

A

COMMIT TRANSACTION

ROLLBACK TRANSACTION

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

How do you assign in the log the name of a transaction?

A

BEGIN TRANSACTION tranName WITH MARK

ROLLBACK TRANSACTION tranName

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

What is the net effect if you rollback a transaction that has nested transactions?

A

Any transactions that were committed within the parent transaction will be rolled back as well.

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

What happens if you rollback a child nested transaction and try to commit the parent transaction?

A

It will fail because a rollback transaction rolls back EVERYTHING.

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

How do you find out the nested level of a transaction?

A

SELECT @@TRANCOUNT

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

What are the scope and types of locks?

A
  • Intent Shared (IS)
  • Shared (S)
  • Update (U)
  • Intent Exclusive (IE)
  • Shared with intent exclusive (SIX)
  • Exclusive (X)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What are the elements of shared locks? (SELECT query)

A
  1. Allows concurrent transactions to read (SELECT)
  2. No other transactions can modify the data
  3. Released as soon as the read operation is completed
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What are the elements of Update Locks?

A
  1. Used to prevent deadlock
  2. Transactions reading data - Shared Lock
  3. To update data, must convert to exclusive
  4. Cannot convert to Exclusive if there is a Shared Lock.
  5. Solution - Use Update Lock; then convert to Exclusive Lock.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the elements of Exclusive Locks?

A
  1. Prevents access by concurrent transactions

2. Used by INSERT, UPDATE, DELETE

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

What are the elements of Intent Locks?

A
  1. Signal intent to place locks at a lower level.
  2. Prevent other transactions from modifying higher level resources
  3. Detecting lock conflicts
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What are the scopes where locks are used?

A

Rows, Pages and Tables

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

What is the order that locks go?

A

Shared -> Update -> Exclusive

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

How can you delay a transaction from running for a specified amount of time (EX. 10 Seconds)?

A

WAITFOR DELAY ‘00:00:10’

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

How can you specify when to run a transaction at a specific time (EX. 10am)?

A

WAITFOR TIME ‘10:00:00’

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

What are the problems with no concurrency control (ie: no locks, etc)

A
  1. Lost Updates
  2. Uncommitted dependency (Dirty Read)
  3. Inconsistent analysis (nonrepeatable read)
  4. Phantom Reads
  5. Missing and double reads caused by row updates
  6. Missing one or more rows that were not the target of update
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What are the transaction isolation levels?

A
  1. Read Committed (Default Isolation Level)
  2. Read Uncommitted
  3. Repeatable Read
  4. Snapshot
  5. Serializable
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are the transaction isolation level effects for:

Read Uncommitted

A

Dirty Read - YES
NonRepeatable Read - YES
Phantom - YES

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

What are the transaction isolation level effects for:

Read Committed

A

Dirty Read - NO
NonRepeatable Read - YES
Phantom - YES

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

What are the transaction isolation level effects for:

Repeatable Read

A

Dirty Read - NO
NonRepeatable Read - NO
Phantom - YES

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

What are the transaction isolation level effects for:

Snapshot

A

Dirty Read - NO
NonRepeatable Read - NO
Phantom - NO

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

What are the transaction isolation level effects for:

Serializable

A

Dirty Read - NO
NonRepeatable Read - NO
Phantom - NO

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

What is the syntax for changing the isolation level?

A

SET TRANSACTION ISOLATION LEVEL [Read Uncommitted, Read Committed, Repeatable Read, Snapshot, Serializable]

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

What is a heap?

A

A set of rows contained in pages that are connected through an Index Allocation Map, not indexed and not ordered.

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

What is the estimated size of a page within a heap?

A

8000 characters.

30
Q

When would a heap be a better way to store data?

A

If you have to input large amounts of data at one time and sort through it later.

31
Q

What is the better way to store data than a heap?

A

B-Tree (Balance tree)

32
Q

How is a B-Tree organized?

A

It’s a hierarchal design with equal rows being stored in pages and balanced all the way around.

33
Q

What is the syntax to create a clustered index?

A

CREATE CLUSTERED INDEX indexName ON tableName (RowsToSortBy)

34
Q

What’s the syntax to drop an index?

A

DROP INDEX IndexName ON tableName

35
Q

What’s the difference between a clustered index and a primary key (Regarding Indexes)?

A

A clustered index will just sort the data, but it doesn’t care about duplicates, whereas a primary key does care about duplicates.

36
Q

What are the two ways to convert a heap to a B-Tree?

A

Create a primary key or a clustered index on the table.

37
Q

What is the syntax to create a non clustered index?

A

CREATE NONCLUSTERED INDEX indexName ON tableName(Columns)

38
Q

What is the syntax to create a non clustered index that is filtered on specific data? (Filtered Index)

A

CREATE NONCLUSTERED INDEX indexName ON tableName(Columns) WHERE ColumnToFilter = ‘filter’

39
Q

What are the different nodes in a B-Tree

A

Root level/node
Intermediate Level Branch Node
Leaf level/node

40
Q

What node contains actual data in a B-Tree?

A

Leaf Level/Node

41
Q

What do the Root level/nodes and Intermediate Level branch nodes contain?

A

The Key Fields that the index was created on.

42
Q

What is the syntax for creating a non clustered index that also contains other data columns, but not storing them as key fields?

A

CREATE NONCLUSTERED INDEX indexName ON tableName(KeyColumn) INCLUDES(columnsToInclude)

43
Q

What is the benefit to including columns in a nonclustered index, but not as key fields?

A

The index doesn’t have to store the data in the Root and intermediate nodes, only in the leaf node.

44
Q

What are the different join types regarding query plans?

A

Hash Match - Least Efficient
Nested Loop Match (One Small Table to One Large Table) - Efficient
Merge Match (Larger Tables, sorted on join) - Very Efficient

45
Q

Do Hash Match and Nested Loop Match require indexes?

46
Q

How does a nested loop work?

A

It will query the small table first, then for the rows that are returned, it will do the select query for every row returned. Much more efficient if the bigger table is indexed.

47
Q

When can you get a merge join?

A

When the two larger tables that are queried are indexed and the data is found from a clustered index scan.

48
Q

What is a SARG and why is it important?

A

A SARG is a Search ARGument. It is a where clause which can use an index. (Do not use functions in the where clause)

A proper SARG will improve your query performance immensely.

49
Q

What are the query cost implications of sorting your query?

A

It will slow down your query significantly. Do not sort unless you absolutely have to.

50
Q

What are the query cost implications when you run a stored procedure? How can you overcome this?

A

The first time you run the procedure, the system creates an execution plan and then will reuse that every time after.

Use CREATE PROC procName WITH RECOMPILE

That will recompile the procedure and create a plan every time the procedure is called.

51
Q

What are all of the hints that you can use with a query?

A

WITH () –separated with comma:

  • NOLOCK
  • READUNCOMMITTED
  • UPDLOCK
  • REPEATABLEREAD
  • SERIALIZABLE
  • READCOMMITTED
  • TABLOCK
  • TABLOCKX
  • PAGLOCK
  • ROWLOCK
  • NOWAIT
  • READPAST
  • XLOCK
  • SNAPSHOT
  • NOEXPAND
  • FORCESEEK
  • FORCESCAN
52
Q

How would you force SQL to use a different join type (Hash, Merge, Loop)

A

put it before JOIN:

LEFT HASH JOIN
RIGHT MERGE JOIN
INNER LOOP JOIN

53
Q

What is the syntax used to show statistics about the disk usage that happens from a query?

A

SET STATISTICS IO ON

GO

54
Q

What does STATISTICS IO show you when you run a query?

A

The tables read
Scan (or seek) count (after leaf level in any direction)
Logical Reads - Number of pages from cache
Physical reads - How often gone to disk
read-ahead reads - Pages placed in cache
lob logical reads - Large object blocks of data
lob physical reads
lob read ahead reads

55
Q

What is the syntax used to see the query plan in a table format?

A

SET SHOWPLAN_ALL ON

GO

56
Q

What are the other SHOWPLANS that can be used?

A

SHOWPLAN_XML
SHOWPLAN_TEXT

Both are for use outside of SSMS

57
Q

What does Client Statistics show you?

A

The number of statements, rows affected, select statements, transactions run by the client.
Network statistics
Time Statistics

58
Q

What is the syntax to show the CPU time used for a query?

A

SET STATISTICS TIME ON

GO

59
Q

What is the stored procedure used for running dynamic SQL? What are the parameters?

A

SYS.sp_executesql
@statement, @params, @ParamName = ‘value’, @Param2 = ‘value2’

@statement and @params both need to be NVarchar datatypes

60
Q

What is a DMV?

A

Dynamic Management View or Dynamic Management Function. (SYS.)

61
Q

How can you run a dynamic SQL without running sys.sp_executesql?

A

Just run:

exec (‘query statement’)

Drawback is that this is prone to SQL injection. DO NOT USE!

62
Q

What is the DMV used for seeing information about how an index is used, including seeks, scans, lookups and so forth?

A

SYS.dm_db_index_usage_stats

63
Q

What is the function used to get a database name?

A

db_name(database_id)

64
Q

What is the function used to get the database id you are currently using?

65
Q

What is the function used to get the object name from an object ID?

A

object_name(object_id)

66
Q

What is the DMV for getting physical statistics for an index? What are the parameters?

A

sys.dm_db_index_physical_stats

DatabaseID
ObjectID (or null if you want all)
IndexID (or null if you want all)
PartitionNumber (or null if you want all)
Mode (Detailed, null, limited or sampled) – scan level

This shows tons of information about indexes including fragmentation, usage, disk and so forth

67
Q

What DMV would I use to be able to show Indexes that the system suggests should be created?

A

sys.dm_db_missing_index_details

Shows Key columns to include as well as columns that should be included

68
Q

What DMV can you use that shows you indexes that are missing columns?

A

sys.dm_db_missing_index_columns(indexName)

69
Q

What is a cursor?

A

It is a row-based operator (allowing you to go RBAR)

FOR or WHILE loop

70
Q

What are the steps for using a cursor?

How do you know when the cursor is finished?

A
  1. Declare cursorName CURSOR FOR SELECT statement FROM table
  2. OPEN cursorName
  3. FETCH NEXT FROM cursorName INTO @VAR
    WHILE @@FETCH_STATUS = 0
    BEGIN
    STATEMENT
    FETCH NEXT FROM cursorName INTO @VAR
    END
  4. CLOSE cursorName

@@FETCH_STATUS - returns 0 if it is still returning data, anything else if it’s failed

71
Q

What is the drawback to scalar functions and what is one way to resolve that?

A

They can be a large drag on resources.

Don’t use the functions unless there isn’t an alternative

72
Q

What are ways that you can combine multiple DML operations?

A 
Merge
Use of OR and AND in WHERE clause
Other uses of WHERE clause
JOINs
Procedures

Sql 70-761 Certification (9 decks)

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