Flashcards in 70-761 Deck (93)
What are the types of rank functions?
CROSS APPLY (similar to inner join)
OUTER APPLY (similar to left outer join)
Stored procedure options
Execute as: owner, self, caller, and user
Why would you use recompile?
When a stored procedure is executed, its execution plan is cached. If you want the system to come up with a new execution plan, use recompile.
Differences between WHERE and HAVING
- Filters input
- Before “Group By”
- No aggregate
- Applies to individual records
- Filters results
- After “Group By” (required when using 'having')
- Applies to summarised group records
Differences between scalar and multi-valued subqueries
- Returns a single value
- Used with =, , <>
- Returns multiple values as a single column set to the outer query
- Used with IN predicate
Guidelines for UNION and UNION ALL
- Data types must be compatible
- Column aliases must be expressed in the first query
- Number of columns must be the same
Which value is returned by this select?
SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value') ...
The third value because the third value is the first value that is not null.
What will these select statements return?
NULLIF ('Hello', 'Hello')
NULLIF ('Hello', 'World')
NULLIF ('Hello', 'Hello') -> NULL
NULLIF ('Hello', 'World') -> Hello
What does OUTPUT clause do?
Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.
When would you use $action?
When using OUTPUT with MERGE.
The OUTPUT returns a row for every row in target_table that is updated, inserted, or deleted, in no particular order. $action can be specified in the output clause. $action is a column of type nvarchar(10) that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row.
What does RAND() Function do?
Returns a random decimal number. It returns a value between 0 (inclusive) and 1 (exclusive).
It will return a completely random number if no seed is provided, and a repeatable sequence of random numbers if a seed value is use.
What is the result of this statement?
SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;
SUBSTRING(string, start_pos, number_of_chars)
What does STUFF function do?
Deletes a part of a string and then inserts another part into the string, starting at a specified position.
STUFF(string1, start, length, add_string)
Example: SELECT STUFF('SQL Tutorial', 1, 3, 'HTML');
Result: HTML Tutorial
What does REPLACE function do?
Replaces all occurrences of a substring within a string, with a new substring. This function performs a case-sensitive replacement.
REPLACE(string1, string_to_replace, replacement_string)
SELECT REPLACE('SQL Tutorial', 'T', 'M');
Result: SQL MuMorial
What does RTRIM do?
Removes trailing spaces from a string.
SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString;
What is columnstore?
Data that's logically organized as a table with rows and columns, and physically stored in a column-wise data format.
What is rowstore?
Data that's logically organized as a table with rows and columns, and physically stored in a row-wise data format. This format is the traditional way to store relational table data.
What does @@ROWCOUNT do?
Returns the number of rows affected by the last statement.
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
What does @@IDENTITY do?
Returns the last-inserted identity value.
What are the differences between IDENT_CURRENT, SCOPE_IDENTITY, and @@IDENTITY?
All these functions return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. Returns the value generated for a specific table in any session and any scope. IDENT_CURRENT ('TableName').
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope;
@@IDENTITY is not limited to a specific scope. The last identity generated in the session.
Steps to use a cursor
- Declare cursor
- Open cursor
- Fetch cursor
- Create loop
- Use data
- Close cursor
Note: cursors involve row based logic. It generally slows performance.
What is heap?
A table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order
What is the difference between clustered and non-clustered indexes?
A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one.
A Non-Clustered Index defines a logical order that does not match the physical order on disk. You can have several non-clustered indexes, but using them in excess might also cause overhead as they require more space.
How to check a table's index?
Execute sp_helpindex Table_Name
What does datetimeoffset do?
Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
INSTEAD OF triggers fire in place of the triggering action and before constraints are processed
If the constraints are violated, the AFTER trigger is not executed.
If there are AFTER triggers on the table, they will fire after constraint processing.
What is a computed column?
A virtual column that is not physically stored in the table, unless the column is marked PERSISTED.
PERSISTED specifies that the SQL Server 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.
Example of view
—create a view named uv_CustomerFullName—
—view must be created in the Sales schema—
CREATE VIEW Sales.uv_CustomerFullName
—view must prevent the underlying structure of the customer table from being changed
—Columns must only be referenced by using one-part names—
—view must return the first name and the last name—
SELECT FirstName, LastName
—view must be able to resolve all referenced objects—