70-761 Flashcards
Get ready for the Microsoft 70-761 exam
What are the types of rank functions?
Rank
Dense Rank
NTILE
Row Number
APPLY operatiors
CROSS APPLY (similar to inner join) OUTER APPLY (similar to left outer join)
Stored procedure options
Encryption
Execute as: owner, self, caller, and user
Recompile
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
WHERE:
- Filters input
- Before “Group By”
- No aggregate
- Applies to individual records
HAVING:
- Filters results
- After “Group By” (required when using ‘having’)
- Applies to summarised group records
Differences between scalar and multi-valued subqueries
SCALAR SUBQUERY:
- Returns a single value
- Used with =, , <>
MULTI-VALUED SUBQUERY:
- 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;ExtractString
SQL
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)
Example:
SELECT REPLACE(‘SQL Tutorial’, ‘T’, ‘M’);
Result: SQL MuMorial
What does RTRIM do?
Removes trailing spaces from a string.
Example:
SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString;
Result:
RightTrimmedString
SQL Tutorial
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.
UPDATE HumanResources.Employee 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.
Triggers
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
WITH SCHEMABINDING
AS
—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—
FROM Sales.Customers
Using hint to optimize transaction logging and locking for the statement below:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
‘a’ – holdlock is wrong because it lock whole table until operation is done {transaction is commited};
‘b’ – rowlock – you use it to update/delete some rows {not many}, for only insert & select its useless and it has bad performance;
‘c’ – xlock – as far as I know its exclusive lock – also locks everyting;
‘d’ – updlock – you use it to insert new, update or delete statements;
‘e’ – correct {for me 99%}. You should use ‘tablock’ hint to improve performance while insert/select statements. It is share lock.
How to make part of a view updatable?
Use INSTEAD OF triggers.
They can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement
What Makes a Query Non-Sargable (not able to use created indexes effectively)?
- using functions in Where clause conditions (because a function is evaluated against each row which forces the query optimizer not to use the index)
- using LIKE ‘%Proposal%’ in Wild card search queries
- performing arithmetic calculation on an index column in a Where clause
What is the difference between row compression and page compression?
Row compression takes all numeric types and makes the physical storage variable length.
So, storing the value 1 takes less space than storing the value 39482084. For large fact tables that are mostly/all numeric, this is a huge win. For purely text tables, you’d get nothing.
Page compression does a dictionary lookup within the scope of a page to find common prefixes to strings on the page. This doesn’t do much to help numeric data, but can be a huge win for text data.
Page compression actually is a superset, in that it automatically includes row compression.
What does >ALL mean?
Greater than every value. In other words, it means greater than the maximum value.
For example, >ALL (1, 2, 3) means greater than 3.
What does >ANY mean?
Greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.
To which operator is ANY equivalent?
IN.
Example:
...
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels') ;
the same as:
...
WHERE ProductSubcategoryID =ANY
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels') ;
How does <>ANY differ from NOT IN?
The < >ANY operator, however, differs from NOT IN: < >ANY means not = a, or not = b, or not = c.
NOT IN means not = a, and not = b, and not = c.
< >ALL means the same as NOT IN.
