Books Online for SQL Server 2014. Microsoftvirtualacademy. Exam 70-461: Querying Microsoft SQL Server 2012. SQL Server: Understanding Logging and Recovery in SQL Server. SQL Server Understanding Logging and Recovery in SQL Server Paul S.
Randal At a Glance: How logging and recovery function in SQL Server How the transaction log works and what you need to know about managing it Recovery models and their effect on logging Some of the most misunderstood parts of SQL Server are its logging and recovery mechanisms. These are all questions I see repeatedly on SQL Server forums and newsgroups, so in this article I want to provide an overview of the logging and recovery system and explain why it is such an integral part of the SQL Server Storage Engine. What Is Logging? Logging and recovery are not concepts that are unique to SQL Server—all commercial relational database management systems (RDBMSs) must have them to support the various ACID properties of transactions.
Operations in an RDBMS are logged (or recorded) at the physical and logical level in terms of what happens in the storage structures of the database. The following operations take place: Recovery Models. XACT_STATE (Transact-SQL) Is a scalar function that reports the user transaction state of a current running request.
@@TRANCOUNT (Transact-SQL) The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.
ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1. A. Showing the effects of the BEGIN and COMMIT statements The following example shows the effect that nested BEGIN and COMMIT statements have on the @@TRANCOUNT variable. PRINT @@TRANCOUNT -- The BEGIN TRAN statement will increment the -- transaction count by 1. B. The following example shows the effect that nested BEGIN TRAN and ROLLBACK statements have on the @@TRANCOUNT variable. Transaction Statements (Transact-SQL) A transaction is a single unit of work.
If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased. SQL Server operates in the following transaction modes. Autocommit transactions. TRY...CATCH (Transact-SQL) A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error. A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct. If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. THROW (Transact-SQL) THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] error_number Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.
RAISERROR (Transact-SQL) The errors generated by RAISERROR operate the same as errors generated by the Database Engine code.
The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. Stored Procedures (Database Engine) Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects.
The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers. The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table.
WHILE (Transact-SQL) Sets a condition for the repeated execution of an SQL statement or statement block.
BEGIN...END (Transact-SQL) Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed.
BEGIN and END are control-of-flow language keywords. IF...ELSE (Transact-SQL) Variables (Transact-SQL) MERGE (Transact-SQL) WITH <common_table_expression> Specifies the temporary named result set or view, also known as common table expression, defined within the scope of the MERGE statement. The result set is derived from a simple query and is referenced by the MERGE statement. For more information, see WITH common_table_expression (Transact-SQL). TOP ( expression ) [ PERCENT ] Specifies the number or percentage of rows that are affected. expression can be either a number or a percentage of the rows. The TOP clause is applied after the entire source table and the entire target table are joined and the joined rows that do not qualify for an insert, update, or delete action are removed. Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches.
Database_name. UPDATE (Transact-SQL) Changes existing data in a table or view in SQL Server 2016. For examples, see Examples. Transact-SQL Syntax Conventions -- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse UPDATE [ database_name . [ schema_name ] . | schema_name . ] table_name SET { column_name = { expression | NULL } } [ ,...n ] [ FROM from_clause ] [ WHERE <search_condition> ] [ OPTION ( LABEL = label_name ) ] [;]
Sequence Numbers. IDENTITY (Property) (Transact-SQL) Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. Transact-SQL Syntax Conventions IDENTITY [ (seed , increment) ] seed. Using PIVOT and UNPIVOT. A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2008R2 sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor. USE AdventureWorks2008R2; GO SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [250], [251], [256], [257], [260] ) ) AS pvt ORDER BY pvt.VendorID;
GROUP BY (Transact-SQL) Expressions in the GROUP BY clause can contain columns of the tables, derived tables or views in the FROM clause. The columns are not required to appear in the SELECT clause <select> list. WITH common_table_expression (Transact-SQL) Specifies a temporary named result set, known as a common table expression (CTE). Table-Valued User-Defined Functions. User-defined functions that return a table data type can be powerful alternatives to views. Table (Transact-SQL) Table variables does not have distribution statistics, theywill not trigger recompiles.
CREATE VIEW (Transact-SQL) A view can be created only in the current database. Using APPLY. The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. Correlated Subqueries. Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.
This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query. This query retrieves one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables. USE AdventureWorks2008R2; GO SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID) ; GO. Subquery Fundamentals. A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.
Ranking Functions (Transact-SQL) HAVING (Transact-SQL) GROUP BY (Transact-SQL) Built-in Functions (Transact-SQL) SQL Server provides many built-in functions and also lets you create user-defined functions. The categories of built-in functions are listed on this page. SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values. For more information, see Deterministic and Nondeterministic Functions Functions that take a character string input and return a character string output use the collation of the input string for the output.
EXCEPT and INTERSECT (Transact-SQL) When the data types of comparable columns that are returned by the queries to the left and right of the EXCEPT or INTERSECT operators are character data types with different collations, the required comparison is performed according to the rules of collation precedence.
UNION (Transact-SQL) Using Self-Joins. Using Cross Joins. Using Outer Joins. Transact-SQL Reference (Database Engine) Books Online for SQL Server 2014. Microsoft SQL Server 2012 T-SQL Fundamentals. Join Fundamentals. Using Inner Joins.