- A transaction is a set of actions that make up an atomic unit of work and must succeed or fail as a whole
- By default, implicit transactions are not enabled. When implicit transactions are enabled, a number of statements automatically begin a transaction. The developer must execute a COMMIT or ROLLBACK statement to complete the transaction.
- Explicit transactions start with a BEGIN TRANSACTION statement and are completed by either a ROLLBACK TRANSACTION or COMMIT TRANSACTION statement.
- Issuing a ROLLBACK command when transactions are nested rolls back all transactions to the outermost BEGIN TRANSACTION statement, regardless of previously issued COMMIT statements for nested transactions.
- SQL Server uses a variety of lock modes, including shared (S), exclusive (X), and intent (IS, IX, SIX) to manage data consistency while multiple transactions are being processed concurrently.
- SQL Server 2008 supports the READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE isolation levels.
Working with Tables and Data Types
- Creating tables is about more than just defining columns. It is very important to choose the right data type and to implement data integrity.
- You need to know the details of how the different data types behave before you can use them correctly.
- Data integrity needs be a part of your table definition from the beginning to make sure that you protect your data from faults.
Common Table Expressions (CTE)
- A recursive CTE contains two SELECT statements within the WITH clause, separated by the UNION ALL keyword. The first query defines the anchor for the recursion, and the second query defines the data set that is to be iterated across.
- If a CTE is contained within a batch, all statements preceding the WITH clause must be terminated with a semicolon.
- The outer query references the CTE and specifies the maximum recursion.
- Noncorrelated subqueries are independent queries that are embedded within an outer query and are used to retrieve a scalar value or list of values that can be consumed by the outer query to make code more dynamic.
- Correlated subqueries are queries that are embedded within an outer query but reference values within the outer query.
- ROW_NUMBER is used to number rows sequentially in a result set but might not produce identical results if there are ties in the column(s) used for sorting.
- RANK numbers a tie with identical values but can produce gaps in a sequence.
- DENSE_RANK numbers ties with identical values but does not produce gaps in the sequence.
- NTILE allows you to divide a result set into approximately equal-sized groups.
- A stored procedure is a batch of T-SQL code that is given a name and is stored within a database.
- You can pass parameters to a stored procedure either by name or by position. You can also return data from a stored procedure using output parameters.
- You can use the EXECUTE AS clause to cause a stored procedure to execute under a specific security context.
- Cursors allow you to process data on a row by row basis; however, if you are making the same modification to every row within a cursor, a set-oriented approach is more efficient.
- A TRY. . .CATCH block delivers structured error handling to your procedures.
User Defined Functions
- You can create scalar functions, inline table-valued functions, and multi-statement table-valued functions.
- With the exception of inline table-valued functions, the function body must be enclosed within a BEGIN. . .END block.
- All functions must terminate with a RETURN statement.
- Functions are not allowed to change the state of a database or of a SQL Server instance.
- Triggers are specialized stored procedures that automatically execute in response to a DDL or DML event.
- You can create three types of triggers: DML, DDL, and logon triggers.
- A DML trigger executes when an INSERT, UPDATE, or DELETE statement for which the trigger is coded occurs.
- A DDL trigger executes when a DDL statement for which the trigger is coded occurs.
- A logon trigger executes when there is a logon attempt.
- You can access the inserted and deleted tables within a DML trigger.
- You can access the XML document provided by the EVENTDATA function within a DDL or logon trigger.
- A view is a name for a SELECT statement stored within a database.
- A view has to return a single result set and cannot reference variables or temporary tables.
- You can update data through a view so long as the data modification can be resolved to a specific set of rows in an underlying table.
- If a view does not meet the requirements for allowing data modifications, you can create an INSTEAD OF trigger to process the data modification instead.
- You can combine multiple tables that have been physically partitioned using a UNION ALL statement to create a partitioned view.
- A distributed partitioned view uses linked servers to combine multiple member tables across SQL Server instances.
- You can create a unique, clustered index on a view to materialize the result set for improved query performance.
- Understanding how queries are logically constructed is important to knowing that they correctly return the intended result.
- Understanding how queries are logically constructed helps you understand what physical constructs (like indexes) help the query execute faster.
- Make sure you understand your metrics when you measure performance.
- Indexes typically help read performance but can hurt write performance.
- Indexed views can increase performance even more than indexes, but they are restrictive and typically cannot be created for the entire query.
- Deciding which columns to put in the index key and which should be implemented as included columns is important.
- Analyze which indexes are actually being used and drop the ones that aren’t. This saves storage space and minimizes the resources used to maintain indexes for write operations.
Working with XML
- XML can be generated using a SELECT statement in four different modes: FOR XML RAW, FOR XML AUTO, FOR XML PATH, and FOR XML EXPLICIT.
- FOR XML PATH is typically the preferred mode used to generate XML.
- The XML data type can be either typed (validated by an XML schema collection) or untyped.
- In an untyped XML data type, all values are always interpreted as strings.
- You can use the value, query, exist, nodes, and modify methods to query and alter instances of the XML data type.
SQLCLR and FileStream
- To use user-defined objects based on SQLCLR, SQLCLR must be enabled on the SQL Server instance.
- The objects most suitable for development using SQLCLR are UDFs and user-defined aggregates.
- If you create UDTs based on SQLCLR, make sure that you test them thoroughly.
- Consider using Filestream if the relevant data mostly involves storing streams larger than 1 MB.
Spatial Data Types
- The geography and geometry data types provide you with the ability to work with spatial data with system-defined data types rather than having to define your own CLR data types.
- You can instantiate spatial data by using any of the spatial methods included with SQL Server 2008.
- SQL Server 2008 provides fully integrated full-text search capabilities.
- Full-text indexes are created and maintained inside the database and are organized into virtual full-text catalogs.
- The CONTAINS and FREETEXT predicates, as well as the CONTAINSTABLE and FREETEXTTABLE functions, allow you to fully query text, XML, and certain forms of binary data.
Service Broker Solutions
- Service Broker provides reliable asynchronous messaging capabilities for your SQL Server instance.
- You need to configure the Service Broker components for your solution. These components might include message types, contracts, services, queues, dialogs, and conversation priorities.
- You use the BEGIN DIALOG, SEND, and RECEIVE commands to control individual conversations between two services.
- Database Mail was introduced in SQL Server 2005 and should be used in place of SQL Mail.
- Database Mail is disabled by default to minimize the surface area of the server.
- You should use the sp_send_dbmail system stored procedure to integrate Database Mail with your applications.
- A wide variety of arguments allows you to customize the e-mail messages and attachments sent from the database server.
- SQL Server PowerShell is a command-line shell and scripting environment, based on Windows PowerShell.
- SQL Server PowerShell uses a hierarchy to represent how objects are related to each other.
- The three folders that exist in the SQL Server PowerShell provider are SQLSERVER:SQL,SQLSERVER:SQLPolicy, and SQLSERVERSQLRegistration.
- You can browse the hierarchy by using either the cmdlet names or their aliases.
Change Data Capture (CDC)
- Change tracking is enabled first at the database and then at the table level.
- Change tracking can tell you what rows have been modified and provide you with the end result of the data.
- Change tracking requires fewer system resources than CDC.
- CDC can tell you what rows have been modified and provide you with the final data as well as the intermediate states of the data.
- SQL Server Audit allows you to log access to tables, views, and other objects.