Flashcards in 70-761 Deck (93)
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)?
1. using functions in Where clause conditions (because a function is evaluated against each row which forces the query optimizer not to use the index)
2. using LIKE ‘%Proposal%’ in Wild card search queries
3. 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?
WHERE ProductSubcategoryID IN
WHERE Name = 'Wheels') ;
the same as:
WHERE ProductSubcategoryID =ANY
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.
What does SOME do?
SOME is an ISO standard equivalent for ANY.
Which are the modes often used in a FOR XML clause?
Why should you specify the WITH SCHEMABINDING clause when you are creating a function?
This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.
Can stored procedure be called from a view?
You can call function within a view.
What is Database Engine Tuning Advisor?
When the Database Engine Tuning Advisor tunes databases, it creates summaries, recommendations, reports, and tuning logs. You can use the tuning log output to troubleshoot Database Engine Tuning Advisor tuning sessions. You can use the summaries, recommendations, and reports to determine whether you want to implement tuning recommendations or continue tuning until you achieve the query performance improvements that you need for your Microsoft SQL Server installation.
What does CHECK OPTION do?
Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Can change database objects: SP or function?
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
Can be used in SELECT, WHERE, HAVING statements: SP or function?
SP cannot be used in SELECT, WHERE, HAVING statements
We can call a ___ from a SP
We cannot call a SP from a ___
We can call a function from stored procedure.
We cannot call a SP from function.
___ cannot be utilised in a SELECT statement
Function can be embedded in a SELECT statement
Which one can use temporary table, SP or function?
Function cannot use temporary tables
What is he difference between SP and function with regard to input/output parameters?
SP: can have input, output parameters
Function: can have only input parameters
Which one supports transaction management? SP or function?
We can’t go for transaction management in function.
Can procedures be called from functions?
But functions can be called from procedure.
What kinds of statements do SP allow?
SP allow SELECT as well as DML(INSERT/UPDATE/DELETE) statement.
What kinds of statements do functions allow?
Functions allow only SELECT statement in it.
Which one can handle exceptions? SP or functions?
SP (handled by try-catch block in a procedure).
Try-catch block cannot be used in a function. User-defined functions do not support error handling. RAISEERROR OR @@ERROR are not allowed in UDFs.
How to SP and functions differ regarding parameters?
SP: do not have to have a parameter.
Functions: must have at least one parameter.
In which one can non-deterministic functions be used? SP or functions?
Non-deterministic functions cannot be used in UDFs.
How do SP differ from functions with regard value returned?
SP: can return zero , single or multiple values.
Function: must return a single value, which may be a scalar or a table. In addition, functions cannot alter the data they receive as parameters (the arguments).
Which are the ranking functions?
- RANK: 1, 1, 1, 4, 5 …
- DENSE RANK: 1, 1, 1, 2, 3 …
- NTILE: divide the ranking in blocks.
Ex.: NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
- ROW_NUMBER: 1, 2, 3, 4, 5, 6...
Can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement.