70-761 Exam: Querying Data With Transact-SQL (SQL Server: Chapter 3)

• (a) a reusable, named query or table expression that can't contain transactions, error handling, nor parameters
• (b) routines that support modifying data, underlying structures, error handling, transactions, input/output parameters, temporary tables, and more
• (c) UDF is a routine that accepts parameters, applies calculations and returns either a scalar or table-valued result.

• USE TSQL4
• GO
• CREATE OR ALTER VIEW Sales.OrderTotals 
• WITH SCHEMABINDING
• AS
• XXX
• GO

Relative ONLY to views, this option prevents structural changes to dependent tables and columns while the view exists. It's not required by default, but sometime required (ie, when a view is used with an Index). Using it makes things more complex, such as when dealing with upgrades. Such changes as this requires dropping and recreating schema-bound objects before and after the change.

• (1) No parameters
• (2) all columns must have names
• (3) all column names must be unique
• (4) not allowed to have an ORDER BY clause unless used with TOP or OFFSET-FETCH filter (in either case, order not guaranteed)

SELECT OBJECT_DEFINITION(OBJECT_ID(N'OrderTotals'));

It means the optional ENCRYPTION keyword was used when creating the view.

Benefits include easy name to remember and query, security via permissions, hides underlying tables, provides computed columns. It has minimal disadvantages of being slower than a stored procedure and if a table is updated, so too will the view need to be.

(a) No you can't...however, you can outside of the view try to update or insert a value. (b) Similar to a check constraint, it validates that the view update doesn't contradict the view query, that is, it must pass the filter criteria otherwise the update is not allowed

• (c) CREATE OR ALTER VIEW
• Sales.USACustomers
•  custid, companyname, address, city, state
• FROM Sales.Customes
• WHERE country = N'USA'
• WITH CHECK OPTION;
• GO

• CREATE UNIQUE CLUSTERED INDEX [idx_cl_empid] ON [dbo].[vw_Test]([empid]);
• CREATE NONCLUSTERED INDEX [idx_nc_orderdate] ON [dbo].[vw_Test]([orderdate]);

• (a) Only one clustered index but numerous nonclustered
• (b) view must contain SCHEMABINDING option
• (c) if query contains grouping, then (1) you must add the BIG_COUNT(*) AS numberoflines element to the select query and (2) the query can't contain any manipulation statements such as CAST / CONVERT.

• (a) no error handling
• (b) no modifying underlying data
• (c) can use dynamic SQL, temporary tables, and DDL's
• (d) when calling, you must always use the schema (ie, dbo.udf_GetStuff(NULL))
• (d) complex types can be returned including XML, GEOMETRY, CLR types
• (e) optional and default parameters can be used

• CREATE OR ALTER FUNCTION dbo.udf_Square(@input AS INT) 
• RETURNS INT
• WITH SCHEMABINDING
• AS BEGIN

• return @input * @input;
• END;
• GO

When you invoke a nondeterministic function in a query, SQL executes it once for the entire query.

SELECT orderid, SYSDATETIME(), RAND(), NEWID();

This would return unique items for first and last, but the two middle values would be same throughout all records (NEWID() is an exception).

Finally, if you attempted to put RAND or NEWID in their own function, it would fail; a work-around would be to put these functions in a view and then call the view.

• DROP TABLE IF EXISTS dbo.T1;
• GO 
• CREATE TABLE dbo.T1
• (
•      id INT NOT NULL IDENTITY CONSTRAINT pk_t1 PRIMARY KEY,
•      dt DAE NOT NULL,
•      dtEndOfYear AS dbo.EndOfYEar(dt) PERSISTED
• );

In order to use a UDF in a persisted, computed column or in an index, the function must be deterministic, so to change, in the function creation, we would need to add SCHEMABINDING to the definition and make sure it returns a unique, deterministic value.

A common table expression which is a temporary table result using the WITH statement and lasting only for the duration of the current query.

• WITH Sales_CTE (SalesPersonID, NumberOfOrders)
• AS
• (
• SELECT SalesPersonID, COUNT(*)
• FROM Sales.SalesOrderHeader
• WHERE SalesPersonID IS NOT NULL
• GROUP BY SalesPersonID
• )
• SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
• FROM Sales_CTE;
• GO

• USE AdventureWorks
• GO
• WITH Emp_CTE AS (
• SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
• FROM HumanResources.Employee
• WHERE ManagerID IS NULL
• UNION ALL
• SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
• FROM HumanResources.Employee e
• INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
• )
• SELECT *
• FROM Emp_CTE
• GO

• WITH cte_name ( column_name [,...n] )
• AS
• (
•      cte_query_definition -- Anchor member is defined.
•      UNION ALL
•      cte_query_definition -- Recursive member is defined referencing cte_name.
• )
• SELECT * FROM cte_name

Differences is that a multi-statement function must (a) first declare a table expression to be returns, (b) populate the table, (c) use a BEGIN/END statement, (d) simple RETURN 

The inline table valued function is quicker because it can leverage SQL optimization whereas MTVF cannot.

During creation and or altering of a stored procedure, syntax/semantics are pre-parsed for proper use. Once completed and saved, it isn't technically compiled...it is compiled only after it's first execution and then it's query plan cached for later use, making them quicker than other query objects.

Faster, compiled, and cached query plans, ability to use error handling, transactions and parameters, improved security by permissions, hiding of complexity, ability to use all CRUD statements, typically less network traffic since SP handles almost all work and simply receives and sends minimal values via resultset and parameters.

• CREATE OR ALTER PROC dbo.GetOrders
• (@orderid AS INT = NULL, @empid AS INT)
• AS
• SET XACT_ABORT, NOCOUNT ON

• SELECT * FROM Sales.Orders O
• WHERE O.orderid = @orderid AND O.empid = @empid;
• GO

XACT_ABORT option determines the effect of run-time errors raised by T-SQL when executing this procedure. If OFF (default), some errors cause an open transaction to roll back and the execution of the code to be aborted whereas others leave the transaction open. To get more reliable errors, best practice says to keep this ON at all times so that an error will be properly rolled back and the execution of code aborted. NOCOUNT option suppresses messages indicating the number of rows were affected by data manipulation statements.

• EXEC dbo.GetOrders 37, DEFAULT, NULL;
• EXEC dbo.GetOrders @id = 37;
• Order is important only when not passing values by name.

Depends on their use...both are cached and the execution plan for the query is reused by subsequent executions. That being said, use of parameters on a dynamic query means its use may not be optimized by SQL cuz it may be using an older version of the query plan compared to the new parameters...to resolve this issue, use WITH RECOMPILE directly after parameter list or with OPTION (RECOMPILE) at end of query ...this will take additional CPU work but make the query optimized for the input parameters provided.

• DECLARE @query AS NVARCHAR(500);
• SET @query = 'SELECT * FROM Orders WHERE State = 'CA'';
• EXECUTE(@query);

• Directly after the parameter list, add 'WITH EXECUTE AS OWNER' option. Alternatively, you should provide permissions not just to the SP but also to the underlying tables. Such as:
• GRANT EXEC ON Sales.Orders TO user1;
• GRANT EXEC ON dbo.GetOrders TO user1;

• DECLARE @invoiceid AS INT;
• EXEC dbo.GetSeqValue @seqname = 'seq', @val = @invoiceid OUTPUT;

• CREATE OR ALTER PROCEDURE dbo.GetSeqValue (@seqname AS VARCHAR(128), @val AS INT OUTPUT)
• AS
• SET XACT_ABORT, NOCOUNT ON;
• UPDATE dbo.MySequences
• SET @val = @val + 1 
• WHERE seqname = @seqname;

• IF @@ROWCOUNT = 0 THROW 51001, 'Sequence not found', 1;
• GO

• DECLARE @name VARCHAR(50) -- database name;
• DECLARE @path VARCHAR(256); -- path for backup files  
• DECLARE @fileName VARCHAR(256); -- filename for backup  
• DECLARE @fileDate VARCHAR(20); -- used for file name 

SET @path = 'C:Backup'; 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);

• DECLARE db_cursor CURSOR FOR  
• SELECT name 
• FROM MASTER.dbo.sysdatabases 
• WHERE name NOT IN ('master','model','msdb','tempdb');  

• OPEN db_cursor   
• FETCH NEXT FROM db_cursor INTO @name;

• WHILE @@FETCH_STATUS = 0   
• BEGIN   
•        SET @fileName = @path + @name + '_' + @fileDate + '.BAK';  
•        BACKUP DATABASE @name TO DISK = @fileName;  

•        FETCH NEXT FROM db_cursor INTO @name;   
• END   

• CLOSE db_cursor;   
• DEALLOCATE db_cursor;

• On-Line Transaction Processing and On-Line Analytical Processing. OLTP is the typical online database used to manage most every business whereas OLAP is different type of database (data warehouse) which exists as
• layer on top of another database and is optimized and dedicated to analytics. You typically use a database to insert and manage transactions in a single application while a data warehouse is used to PULL data
• (analytics), often from numerous applications such as reports, external 3rd parties, internal analysis, etc.

• A transaction is a unit of work with one or more manipulative tasks to data or structure. ACID: A stands for Atomocity (either completes in entirety or not at all), C for consistency (transition from one consistent
• state to another), I for isolation (transaction is isolated and transparent), and D for Durability (results are persisted, even in case of system failure).

When ON, entired transaction is rolled back and processing stops; If OFF, depending on the severity of the error, the transaction MAY BE rolled back and processing MAY stop, but not always.

• Non-DML (Data Manipulation Lanauge) elements such as local variables, modifying table schema, Deleting of tables or objects, and other statements which don't include the INSERT, UPDATE, DELETE, TRUNCATE, or MERGE
• statements. If no BEGIN/END is provided in a transaction, then EVERY statement is considered a complete transaction as if it had a BEGIN/END followed by an immediate COMMIT TRAN statement.

• It implies that a BEGIN/END occurs around the entire transaction without explicitly saying so. It's a bad idea to leave this ON since it increases likelyhood of transaction staying on for long periods and degrading
• performance or blocking other users.

No. The BEGIN/END statement only increments, from zero, a counter...such that the user can determine where he/she is, but a new transaction technically isn't being created.

(A) 3 (B) 2 (C) No (D) 0 (E) 1

• A lock on the Orders table would have been issued and not lifted until after the transaction was either committed or rolled back. If neither were explicitly called and the procedure left...and open transaction
• would remain.

• WHILE @@TRANCOUNT > 0 COMMIT TRAN; This is considered bad practice because an open transaction may exist (say, from an open transaction from another user which was never closed). Similarly, while XACT_ABORT may be
• ON, the command to ROLLBACK TRAN may be called but not rolled back properly because an open transaction may have already existed.

• It's possible by having XACT_ABORT set to OFF and attempting to roll back a transaction that isn't the outermost transaction (first one called). Correctable by turning option to ON resulting in all uncommitted
• transactions to be rolled back and code processing ending.

• BEGIN TRAN T1;
• UPDATE table1 ...;
• BEGIN TRAN M2 WITH MARK;
• UPDATE table 2 ...;
• The WITH MARK option causes the transaction name to be placed in the transaction log and allowing it to be used by name (as opposed to date/time) when restoring the database).

ERROR_NUMBER(); ERROR_MESSAGE();ERROR_SEVERITY(): 0-9 informational and passed to client, 11-19 catch-able, and 20+ are so severe connectivity is terminated;ERROR_STATE(): 1-255;ERROR_LINE() line number where error occurred; ERROR_PROCEDURE():name of stored procedure; Rules state that (a) called outside of a CATCH block returns NULL, (b) these only list last error in the chain, (c) only within current stored procedure.

• CREATE OR ALTER PROC dbo.PrintErrorInfo
• AS
• PRINT 'Error Number: ' + CAST(ERROR_NUMER() AS VARCHAR(10));
• PRINT 'Error Message: ' + ERORR_MESSAGE();
• PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
• PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10));
• PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
• PRINT 'Error Procedure: ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc');
• GO

• THROW error_number, message, state;
• RAISERROR ({error_id | error_message}, severity, state [,argument [ ,...n ] ]) [WITH options];
• Both can be re-thrown, but THROW will correctly persist accurate value, is newer, easier to use, and is the one suggested to use by Microsoft.

• THROW error_number, message, state; -- THROW 501, 'This is an error', 1;
• RAISERROR error_number, message, severity, state, WITH OPTION; -- RAISERROR('This is an error', 0, 1) WITH NOWAIT;

The error will not wait until the buffer is full (occurs when statement is complete), it will log the result, and since it's severity is 20, it will terminate connectivity. With logs and in transactions, make sure to write to the log only AFTER rolling back transaction and BEFORE the THROW command.

• Turning on XACT_ABORT and by using TRY-CATCH constructs within a transaction. For example, with XACT_ABORT off, division by zero still continues processing the transaction whereas if a CAST error occurred, then
• processing would stop.

• A transaction could be in 3 possible states in a TRY-CATCH block error: open and committable[1], open and uncommittable (doomed)[-1], and no open transaction[0]. You can use this function which returns each of
• these states in a CATCH block. With XACT_ABORT, the XACT_STATE() returns doomed (-1) which is consistent and suggestable behavior.

(A) 1921 (B) 1921 (C) 1234567890 (D) 123 (E) msg: conversion error (F) '1A2B' (G) Highest precedence is an INT so conversion attempt to INT

• ISNULL: 2, not a standard, type of return type OR if two NULLS then INT, if either null then NOT NULL otherwise NULL, execute only once
• COALESCE: >2, is a standard, highest precedence data type if not NULL otherwise if all are nulls, you get an error, if ALL input null then NOT NULL otherwise NULL, may be executed more than once

(A) msg: conversion error (B) NULL, INT (C) NULL, INT (D) 'abcd', CHAR(4)

Each are their own "beast" and have their own benefits (ie, a view can only express a single SELECT statement). However, if we exclude minor fringe cases, then we could reasonably conclude that a single query is likely the slowest while the stored procedure and view are essentially equivalent with a view maybe lightly improving performance. The reason is that both SP and view's are compiled and cached (making them faster than straight queries), and while both can contain cluster and nonclustered indexes, the view benefits from creating the result set immediately and persisted it, saving the overhead of performing this costly operation at execution time.

• If a single comparison is made such that A.id = B.id then the NULL value logic states these are 'UNKNOWN' and their results discarded; to prevent this, compare then using one of two ways (1) A.id = B.id OR A.id IS
• NULL AND B.id IS NULL) or method (2) COALESCE(A.id, 'N/A') = COALESCE(B.id, 'N/A'). The later method is slower since optimization can't take advantage of indexing, so always use method #1. Yes, set operators behave
• differently--they match based not on equalness, but on distinctness, therefore two NULLS equal true and would be returned in the result set.