General Flashcards
transaction level isolation READ UNCOMMITTED
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
transaction level isolation READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
TRANSACTION ISOLATION LEVEL REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
TRANSACTION ISOLATION LEVEL SNAPSHOT
Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
TRANSACTION ISOLATION LEVEL SERIALIZABLE
- Statements cannot read data that has been modified but not yet committed by other transactions.
- No other transactions can modify data that has been read by the current transaction until the current transaction completes.
- Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
XML is case sensitive
True
How do you generate the XSD schema?
XMLSchema in the For XML Clause
What XML formats data like this:
For XML Raw
What XML formats data like this:
1
Customer A
123
345
For XML Auto, Elements (optional to make it element centric otherwise the items would be rolled up to combine all the elements of oen area like custid and companyname would be one element), Root (‘CustomersOrders’)
Also you can add With XMLNameSpaces(‘TK461-CustomersOrders’ as co) to the top above the Select to add the xmlso:co=”TK461-CustomersOrders”
Where do you place the For XML Auto?
After the Order By Clause
What is the order of execution?
FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY clause
How do you completely define the the elements in a created XML document?
Use XML Path
What does Persisted do?
Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.
How do you format a date into international datetimes for viewing?
Use the format function.
Returns a value formatted with the specified format and optional culture in SQL Server 2012. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.
FORMAT ( value, format [, culture ] )
Format can also be used to format strings and do things like add leading zeros.
How do you limit the data going into a field on a table to certain values?
Use a check constraint
Holdlock
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.
Rowlock
Use row-level locks when reading or modifying data.
xlock
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.
If an analysis of a query plan shows table cans where the estimated row do not match the actual rows in the query, what should you do?
Update the statistics on all the involved tables.
Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
What types of table compression are available?
Page or Row
Page compression includes Row compression.
You add compression when creating or altering a table With (Data_Compression = Page or Row)
What does IAM stand for?
Index Allocation Map
What levels can an index have?
Root, leaf, intermediate
What tracks index usage?
dm_db_index_usage_stats
What does DM stand for?
Dynamic Management view or function
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
How do you know there are no more rows to fetch?
@@fetch_status = -1
tablock
Use a table lock when reading or modifying data.
what are the mathematical branches that the relational model is based on?
Set theory and predicate logic
What is the difference between newid and newsequentialid?
NewID is a unique GUID, but there is no way of knowing the value returned. newsequentialid is also a unique GUID but for the machine and it is sequential.
What does Coalesce do?
It accepts a list of inputs and it returns the first one that is not null.
Coalsece (column1, column2, ‘nothing’)
What is the downside to Coalesce or isNull?
When used in the where clause they both cause a performance issue, since the index will not be used.
What function returns the current date and time value as a datetime2 type?
SysDateTime
When concatenating strings, what is the difference between + and the concat function?
The + operator returns a null if any of the values are null, the concat treats null as an empty string.
What are the wildcard patterns in a like statement?
%
_ underscore marks a single character that can be anything, so ‘_D%’ anything where the second character is D
[character list], like ‘[AC]%’ which is anything that starts with A or C
[character range], like [0-9]%
not in range [^0-9]% is everything that does not start with 0 to 9
What is the form of a filter predicate that can rely on index ordering called?
A search argument or SARG for short.
No wildcards are the start of the clause and no functions, these limit the servers ability to use indexes.
what is the language neutral date format?
yyyymmdd
what is required when using offset fetch?
1) an order by
2) you can have “offset 25 rows”, but you cannot have “fetch next 25 rows only”, there cannot be a fetch without and offset.
How can you help performance on foreign keys?
SQL server creates an index when you make a primary key or unique constraint, it does not make an index with a foreign key. You need to manually create indexes on those fields which will improve performance.
What are the 4 forms of table expressions?
Derived tables
common table expressions (CTE)
Views
Inline table-valued functions
What is the difference between the apply and join operators?
With a join operator, both inputs represent static relations. With apply, the left side is a static relation, but the right side can be a table expression with correlations to the elements from the left table.
What is the set operator precedence?
Intersect precedes Union and Except -
Union and Except are evaluated from left to right
What are the window ranking functions?
row_number
rank
dense_rank
ntile( numeric value)
These are only allowed in the Select and Order By
What are the clauses that the different types of window functions support?
Partitioning, ordering, framing
What do the delimiters Unbound Preceding and Unbounded Following represent?
Unbound Preceding = the beginning of the set
Unbounded Following = the end of the set
Which data type should be used in place of timestamp?
Rowversion
How does NULL work with a unique constraint?
Columns do not need to be NOT NULL, but only one row in the table can be NULL.
How do you obtain a new value from a sequence, if you aren’t using the Identity option?
With the Next Value For function
What is Scope_Identity for?
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
What is @@Identity for?
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
What is Ident_Current for?
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
How many WHEN MATCHED clauses can a single Merge statement have?
Two - one update and one delete
How do you determine which action affected the Output row in a merge statement?
Use the $action function
How many Output clauses can a single statement have?
Two - one with Into and one without Into
What are the ACID properties of transactions?
Atomicity
Atomicity requires that each transaction is “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible (“atomic”), and an aborted transaction does not happen.
Consistency
The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors do not violate any defined rules.
Isolation
The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other. Providing isolation is the main goal of concurrency control.
Durability
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.
How does SQL Server implement transaction durability?
By first writing all changes to the database transaction log before making changes to the database data.
What does @@Trancount values mean?
0 = the code is not within a transaction
>0 indicates the active transaction
What do XACT_State() values mean?
0 = no active transaction 1 = there is an uncommitted transaction -1 = there is an uncommitted transaction but it cannot be committed due to an error
What is the default transaction isolation level?
Read Committed
What should you use instead of NoLock?
ReadUncommited, nolock is being deprecated
What is security level 0 used for?
When you issue a raiseerror with severity level of 0, only an informational message is sent.
What are the two types of triggers?
After = only fires after the event is complete, this can only be used on permanent tables
Instead of = this fires instead of the event, it can be on permanent tables or views
What are the 3 join algorithms?
Nested Loops, Merge, Hash
There is a 4th, but it’s part of the Hash called Bitmap Filtering (also called Star join)
What are the query execution steps?
Parsing -> Binding -> Optimization -> Execution
What are the relational engine and storage engine?
The relational engine is an internal component that works on a logical level. The actual execution is performed by the storage engine.
How would you quickly measure the amount of disk IO a query is performing?
Set Statistics IO
How can you get an estimated execution plan in XML format?
Set Showplan_XML
Which DMO gives you detailed text of the queries executed?
sys.dm_exec_sql_text
What is a heap?
A data heap is data in no logical or organized order.
The only way to find data in a heap is to scan the whole heap.
What is a balanced tree?
A balanced tree is an organized way of storing data. It will have a single root page and at least one or more leaf pages. The data is stored in the logical order of the clustered key. A clustered index, creates the balanced tree.
Which clauses of a query should you consider supporting with an index?
Select, Where, Join, Group By and Order By
What is a nested loop algorithm?
In its simplest form, a nested loops join compares each row from one table (known as the outer table) to each row from the other table (known as the inner table) looking for rows that satisfy the join predicate. (Note that the terms “inner” and “outer” are overloaded; their meaning must be inferred from context. “Inner table” and “outer table” refer to the inputs to the join. “Inner join” and “outer join” refer to the logical operations.)
What is a merge join?
The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. The Merge Join transformation requires that both inputs be sorted and that the joined columns have matching meta-data. User cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.
What is a hash join?
This join type is probably the most common one that you will encounter. It uses a hash table to aid in joining. The join works in two phases, the build phase and the probe phase. We will refer to the two tables to be joined as the build table (commonly the smaller of the two) and the probe table. For a hash join to work, at least one of the join conditions will need to be a equijoin, that is, two columns that are equal (=) to each other.
In the build phase, the server calculates hash values for the build table. Think of this as a temporary index on the join columns. This calculated hash table is then stored in memory or swapped out to physical storage in tempdb, depending on its size. This is the main potential drawback of the hash join – if your tables are really large, this join will consume quite a bit of memory.
When the build phase is complete, the server will enter the probe phase. This phase calculates hash values for each row in the probe table and tries to match those values to corresponding hash value in the build table, completing the join. If the entire build table fits into memory, the join is called an in-memory hash join. If it has to be split to disk, it’s referred to as a grace hash join. In some cases, where you have really large amounts of data, the server will create a number of recursive merge joins, called a recursive hash join.
What does FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) do?
It will return the first value in the selection specified in the over clause.
This:
SELECT Name, ListPrice,
FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product
WHERE ProductSubcategoryID = 37;
Returns:
Name ListPrice LeastExpensive
———————– ——————— ——————–
Patch Kit/8 Patches 2.29 Patch Kit/8 Patches
Road Tire Tube 3.99 Patch Kit/8 Patches
Touring Tire Tube 4.99 Patch Kit/8 Patches
When preparing to query XML-type columns or variables using XML data type methods, what options must be set?
ANSI_PADDING ON
ANSI_NULLS ON
ANSI_WARNINGS ON
QUOTED_IDENTIFIER ON
If these options are not set in this manner, queries and modifications using XML data type methods fail.
What is the capacity of tinyint?
1 byte
0 - 255
What is the capacity of int?
4 bytes
-2,147,483,648 to 2,147,483,648
What is the capacity of smallint?
2 bytes
-32,768 to 32,767
What is the capacity of bigint?
8 bytes
What is check option?
It is a associated to a view.
This option forces all statements that modify data executed against the view to follow criteria set within the statement that defines the view.
Loop Join
This join is also commonly known as nested iteration. This kind of join is composed by an outer loop and an inner loop. When the query runs for each row of the outer loop, the inner loop is executed completely. This join is effective only when the outer loop query is small and the inner loop query has all the proper optimizations applied. This join method is very useful with small transactions.
Merge Join
This join has the unique requirement for tables involved in the operation to be sorted. This join keeps both of the tables sorted in parallel and compares each table row by row simultaneously with each other. It compares one row of the first table with one row of the second table. If they are equal, that row qualifies; otherwise, this join determines which row of each table has the lower value. Once the lowest value of the table is figured out, it moves on to next row of that table and compares that to the original row. This operation keeps going on until all rows from each table are completely examined. This operation can be very expensive when tables are not sorted and it’s required to sort them before they are joined. If tables have non clustered indexes over them and joins are using the same conditions, there are pretty good chances that this join performs better than other kinds of joins.
Hash Join
This is the most complex of all the other joins. There are two major components of this kind of join – build query and probe query. First, a smaller table is assigned as build query and a hash table for the same is created. This hash table is compared with the probe table. This comparison of input table and probe table is done one row at a time. One row of the probe table is hashed and compared against the other row, and qualifying rows are checked.
