70-761 Flashcards
Get ready for the Microsoft 70-761 exam (93 cards)
1
Q
What are the types of rank functions?
A
Rank
Dense Rank
NTILE
Row Number
2
Q
APPLY operatiors
A
CROSS APPLY (similar to inner join) OUTER APPLY (similar to left outer join)
3
Q
Stored procedure options
A
Encryption
Execute as: owner, self, caller, and user
Recompile
4
Q
Why would you use recompile?
A
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.
5
Q
Differences between WHERE and HAVING
A
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
6
Q
Differences between scalar and multi-valued subqueries
A
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
7
Q
Guidelines for UNION and UNION ALL
A
- Data types must be compatible
- Column aliases must be expressed in the first query
- Number of columns must be the same
8
Q
Which value is returned by this select? SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value') ...
A
The third value because the third value is the first value that is not null.
9
Q
What will these select statements return?
NULLIF (‘Hello’, ‘Hello’)
NULLIF (‘Hello’, ‘World’)
A
NULLIF (‘Hello’, ‘Hello’) -> NULL
NULLIF (‘Hello’, ‘World’) -> Hello
10
Q
What does OUTPUT clause do?
A
Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.
11
Q
When would you use $action?
A
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.
12
Q
What does RAND() Function do?
A
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.
13
Q
What is the result of this statement?
SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;
A
ExtractString
SQL
SUBSTRING(string, start_pos, number_of_chars)
14
Q
What does STUFF function do?
A
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
15
Q
What does REPLACE function do?
A
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
16
Q
What does RTRIM do?
A
Removes trailing spaces from a string.
Example:
SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString;
Result:
RightTrimmedString
SQL Tutorial
17
Q
What is columnstore?
A
Data that’s logically organized as a table with rows and columns, and physically stored in a column-wise data format.
18
Q
What is rowstore?
A
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.
19
Q
What does @@ROWCOUNT do?
A
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';
20
Q
What does @@IDENTITY do?
A
Returns the last-inserted identity value.
21
Q
What are the differences between IDENT_CURRENT, SCOPE_IDENTITY, and @@IDENTITY?
A
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.
22
Q
Steps to use a cursor
A
- Declare cursor
- Open cursor
- Fetch cursor
- Create loop
- Use data
- Close cursor
Note: cursors involve row based logic. It generally slows performance.
23
Q
What is heap?
A
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
24
Q
What is the difference between clustered and non-clustered indexes?
A
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.
25
How to check a table's index?
Execute sp_helpindex Table_Name
26
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.
27
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.
28
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.
29
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
30
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.
31
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
32
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
33
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.
34
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.
35
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.
36
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') ;
```
37
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.
38
What does SOME do?
SOME is an ISO standard equivalent for ANY.
39
Which are the modes often used in a FOR XML clause?
- RAW
- AUTO
- EXPLICIT
- PATH
40
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.
41
Can stored procedure be called from a view?
No.
| You can call function within a view.
42
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.
43
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.
44
Can change database objects: SP or function?
SP.
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
45
Can be used in SELECT, WHERE, HAVING statements: SP or function?
Function.
| SP cannot be used in SELECT, WHERE, HAVING statements
46
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.
47
___ cannot be utilised in a SELECT statement
SP.
| Function can be embedded in a SELECT statement
48
Which one can use temporary table, SP or function?
SP.
| Function cannot use temporary tables
49
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
50
Which one supports transaction management? SP or function?
SP.
| We can’t go for transaction management in function.
51
Can procedures be called from functions?
No.
| But functions can be called from procedure.
52
What kinds of statements do SP allow?
SP allow SELECT as well as DML(INSERT/UPDATE/DELETE) statement.
53
What kinds of statements do functions allow?
Functions allow only SELECT statement in it.
54
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.
55
How to SP and functions differ regarding parameters?
SP: do not have to have a parameter.
Functions: must have at least one parameter.
56
In which one can non-deterministic functions be used? SP or functions?
SP.
| Non-deterministic functions cannot be used in UDFs.
57
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).
58
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...
59
CTE
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.
60
Difference between CTE and derived tables
Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
61
What are the differences between global temporary table and local temporary table?
Global temporary table: ## prefix,
are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Local temporary table: # prefix,
are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.
62
Difference between view and user-defined functions
VIEWS: are limited to a single SELECT statement.
UDF: can contain additional statements that allow more powerful logic.
63
The main characteristics of views are:
- Can affect only one of the underlying tables (when inserting, updating or updating data)
- No ORDER BY (unless a TOP exists)
- No INTO and OPTION keywords
- No OPTION clause
- No reference to a temporary table or table variable
- Allows functions and multiple SELECT statements separated by UNION or UNION ALL
- It’s persisted (stored in the database)
- Do not support input parameters
64
View (syntax):
CREATE VIEW view_name AS SELECT…
65
The main characteristics of temporary tables are:
- # prefix (local and ## for global)
- Scoped to the session
- Created as temporary object in the tempdb
- Involve recompilation of queries
66
The main characteristics of table variables are:
- @ prefix
- Scoped to the batch, not to the session
- Use less system resources than temporary tables
67
The main characteristics of derived tables (subqueries) are:
- Must have an alias, unique names for all columns, not use ORDER BY (only if there is a TOP or OFFSET/FETCH), not be referred multiple times in the same query
- Scope: the query in which it is defined
- Not persistent (is a virtual table)
- Also called query expressions
- May use internal or external aliases for columns
- May refer to parameters and/or variables
- May be nested within other derived tables
68
The main characteristics of table-valued function are:
- Syntax: CREATE FUNCTION (@variablename) RETURNS TABLE AS RETURN (SELECT…)
- Unlike views, it supports input parameters
- It’s persisted, stored in the database
- Returns a virtual table to a query that call it
- The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot
- Can also replace stored procedures that return a single result set
- While views are limited to a single SELECT statement, user-defined functions can contain additional statements
- Powerful alternatives to views
69
The main differences between XACT_STATE and @@ TRANCOUNT are:
XACT_STATE:
- Can be used to detect whether the current request has an active user transaction.
- Cannot be used to determine whether there are nested transactions.
- Indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
@@TRANCOUNT:
- Can be used to detect whether the current request has an active user transaction.
- Cannot be used to determine whether that transaction has been classified as an uncommittable transaction.
- Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
70
Sequence (syntax)
CREATE SEQUENCE Sales.OrderNumbers AS INT
START WITH 1 INCREMENT BY 1;
...
SELECT NEXT VALUE FOR Sales.OrderNumbers;
71
How to create columns with default values?
```
CREATE TABLE SalesLT.Callog
(
...
CallTime datetime NOT NULL DEFAULT GETDATE()
)
```
INSERT INTO SalesLT.Callog
VALUES
(..., DEFAULT)
72
How override identity?
SET IDENTITY_INSERT TableName ON;
...
SET IDENTITY_INSERT TableName OFF;
73
Using BREAK
WHILE ((SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300)
BEGIN
UPDATE DimProduct
SET ListPrice = ListPrice * 2;
IF ((SELECT MAX(ListPrice) FROM dbo.DimProduct) > $500)
BREAK;
END
74
THROW syntax
THROW 51000, 'The record does not exist.', 1;
| --The severity is set to 16.
75
Can SP be called from a SELECT statement?
You cannot use Stored procedure in a SELECT statement. You can use User defined functions in a select statement.
76
ALTER TABLE syntax
ALTER TABLE table_name
ADD column_name column_definition;
Example:
ALTER TABLE employees
ADD last_name VARCHAR(50);
ALTER TABLE table_name
ALTER COLUMN column_name column_type;
77
CREATE TRIGGER (syntax)
```
CREATE TRIGGER Triger_name
ON Table_Name
AFTER INSERT
AS
BEGIN
...
```
78
Isolation levels:
- Read Uncommitted: fast but no locks
- Read Committed (is the default): select only locks during execution
- Repeatable Read: select locks data that has been returned
Serialize: select locks the range (may incur a table lock)
79
Snapshot isolation:
- Uses versioning: everyone gets a sandbox
- Additional TempDB overhead
- Must be enabled at the database level
- READ_UNCOMMITTED_SNAPSHOT: converts read committed to snapshot
80
THROW (syntax)
THROW 51000, 'The record does not exist.', 1;
81
MERGE (syntax)
```
MERGE [AS TARGET]
USING [AS SOURCE]
ON
[WHEN MATCHED
THEN ]
[WHEN NOT MATCHED [BY TARGET]
THEN ]
[WHEN NOT MATCHED BY SOURCE
THEN ];
```
82
Example of using PIVOT
```
WITH PivotInput AS
(
SELECT shipperid, shipcity, freight
FROM Sales.Orders
WHERE shipcountry = N'Spain'
)
```
SELECT *
FROM PivotInput
PIVOT( SUM(freight)
FOR shipcity IN (Barcelona, Madrid, Sevilla) ) AS PivotOutput;
83
Another example of using PIVOT
```
SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
) AS pvt
```
84
Example of how to UNPIVOT data
```
-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt;
```
85
Exemple of how to use OUTPUT and $ACTION in MERGE
MERGE manoj AS TARGET
USING (SELECT sn, ename FROM manoj2) AS SOURCE
ON (TARGET.sn = SOURCE.sn)
WHEN MATCHED THEN
UPDATE SET TARGET.ename = SOURCE.ename
WHEN NOT MATCHED BY TARGET THEN
INSERT (sn, ename) VALUES (sn, ename)
OUTPUT $action, DELETED.*, INSERTED.*;
86
TABLOCK
Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction
87
EOMONTH (syntax)
```
DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
```
Result:
This Month
-----------------------
2011-12-31
Next Month
-----------------------
2012-01-31
Last Month
-----------------------
2011-11-30
88
How can NEWSEQUENTIALID be faster than NEWID?
NEWSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.
When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
89
You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to
reduce fragmentation.
You need to achieve this goal without taking the index offline.
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
Rebuild will drop and recreate the existing index therefore locking and taking the table “offline”.
90
You need to design tables and other database objects.
You need to store media files in several tables.
Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently.
Besides using full-text indexes on SQL
Server character data, you can store whole documents in binary or XML columns, and use
full-text queries on those documents. Columns of data type VARBINARY(MAX), IMAGE, or
XML require an additional type column in which you store the file extension (such as .docx,
.pdf, or .xlsx) of the document in each row.
You need appropriate filters for documents.
91
Do not use parenthesis in SP
```
CREATE PROCEDURE usp_Customers @Count int
AS
SELECT TOP(@Count) Customers.LastName
FROM Customers
ORDER BY Customers.LastName
```
92
DATENAME parameters:
```
MONTH: mm, m
DAYOFYEAR: dy, y
WEEK: wk, ww
WEEKDAY: dw, w
HOUR: hh
MINTUE: mi, n
SECOND: ss, s
```
93
Creating temporal tables
, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
