70-761 Flashcards Preview

CAE > 70-761 > Flashcards

Flashcards in 70-761 Deck (93)
Loading flashcards...
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.