Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Productivity Tips for Transact-SQL 2005 : Page 3

SQL Server 2005 offers T-SQL language features that can improve your productivity.




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Scenario: You are inserting rows into a table and want to immediately retrieve the entire row, without the need for another SELECT statement, or a round trip to the server, and without using SCOPE_IDENTITY. Additionally, when you UPDATE rows in a table, you want to immediately know the contents of the row both before and after the UPDATE—again, without the need for another set of queries.

Have you ever needed to do a SELECT statement after an INSERT or UPDATE, just to retrieve a fresh copy of the row you added/changed? Related question: Have you ever been frustrated that you can't access the INSERTED and DELETED system tables outside of a database trigger?

OUTPUT and OUTPUT INTO are nice capabilities to get immediate feedback on what has been added or changed. But don't use them in place of audit trail triggers.
If you answered yes to either question, then you may like the next tip. SQL Server 2005 introduced a programming shortcut called OUTPUT that you can include in any INSERT/UPDATE/DELETE statement. OUTPUT allows you to specify values from the INSERTED and DELETED system tables so that you can include any or all "old value - new value" pieces of data.

Here are two examples that show how to use OUTPUT for both INSERT and UPDATE statement:

DECLARE @Invoice TABLE (PK int identity, InvDate DateTime, InvAmt Decimal(14,2)) DECLARE @InvDate DATETIME SET @InvDate = GETDATE() INSERT INTO @Invoice OUTPUT INSERTED.* VALUES ( GETDATE(),1000) DECLARE @InsertedRows TABLE (PK int, InvDate DateTime, InvAmt Decimal(14,2)) INSERT INTO @Invoice OUTPUT INSERTED.* INTO @InsertedRows VALUES ( GETDATE(),2000) INSERT INTO @Invoice OUTPUT INSERTED.* INTO @InsertedRows VALUES ( GETDATE(),3000) SELECT * FROM @InsertedRows DECLARE @tTest1 TABLE ( TestCol int,Amount decimal(10,2)) INSERT INTO @ttest1 VALUES (1, 100) INSERT INTO @ttest1 VALUES (2, 200) INSERT INTO @ttest1 VALUES (3, 300) UPDATE TOP(2) @tTest1 SET Amount = Amount * 10 OUTPUT DELETED.*,inserted.* -- This allows you to access the DELETED and INSERTED system tables -- that were previously available only inside triggers

For an INSERT, you just add the OUTPUT statement before the VALUES statement. If you are inserting into a table with an identity column, the OUTPUT result will include the new identity column value, as well as any default values or calculated columns from the table. This is much simpler than using SCOPE_IDENTITY to retrieve the full record after the insert:


If you are inserting several records, you can use OUTPUT INTO to insert the results to a table variable, one insert at a time:

DECLARE @InsertedRows TABLE (PK int, InvDate DateTime, InvAmt Decimal(14,2)) INSERT INTO @Invoice OUTPUT INSERTED.* INTO @InsertedRows VALUES ( GETDATE(),2000) INSERT INTO @Invoice OUTPUT INSERTED.* INTO @InsertedRows VALUES ( GETDATE(),3000)

As for UPDATE statements, The second OUTPUT example above updates the first two rows in a table and outputs both the DELETED and INSERTED system tables:

UPDATE TOP(2) @tTest1 SET Amount = Amount * 10 OUTPUT DELETED.*,inserted.*

A note about OUTPUT: If you're using database triggers for audit trail logging, you may be tempted to think that the new OUTPUT functionality can replace audit trail logging. Strong piece of advice: resist the temptation. Database triggers provide the best mechanism for logging database changes because they will fire regardless of how an INSERT/UPDATE occurs. So restrict the use of OUTPUT to instances where you want immediate feedback on changes.

Tip 5: Isolation Levels (part 1 of 2)
Scenario: You've just started using SQL Server and want to understand how SQL Server locks rows during queries and updates. This is a two-part tip: the first will cover the four options that developers had prior to SQL Server 2000, and the next tip will cover an option that is specific to SQL Server 2005.

Some developers who come from different database environments (e.g. Visual FoxPro) ask how locking works in SQL Server 2000. Some developers who have started writing SQL queries later discover that implementing those queries leads to deadlocks.

The SQL Server paradigm uses the concept of isolation. In other words, when you query against data, when you perform transactions, how "isolated" (hence the name) are you from other processes that are working with the same data or logically related data? (Keep a mental note of the term logically related data—I'll come back to that later.)

SQL Server 2000 contains four isolation levels, each progressively more restrictive. They are as follows:

  • READ UNCOMMITTED (dirty read)
  • READ COMMITTED (the default level)
You can take advantage of isolation levels to avoid or minimize instances of deadlocks. SQL Server 2005 adds a fifth isolation level that I'll cover in the next tip, but it's important to understand the first four.

The code examples in this section, as well as Listing 9 and Listing 10, show examples of each isolation level. Each presents a "User A", "User B" scenario to show what happens when one user wants to update data while the other user queries it.

The first level, READ UNCOMMITTED, offers the least amount of isolation against other actions on the same row. If User A begins to update a record, and User B queries the record before User A commits the transaction, User B will still pick up the change. Why? Because READ UNCOMMITTED is just that—the ability to read uncommitted changes. This isolation level offers the best protection against deadlocks but also carries the greatest risk of querying bad data. If User A's update does not commit correctly (violates a constraint or a system error, etc.), then User B will be looking at not only "dirty" but also invalid data. The following code shows an example of this level:

-- USER A USE AdventureWorks GO BEGIN TRAN -- change from Trey Research to 'Trey & Wilson Research, Inc.' UPDATE Purchasing.Vendor SET Name = 'Trey & Wilson Research, Inc.' WHERE VendorID = 10 -- USER B USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT Name FROM Purchasing.Vendor WHERE VendorID = 10 -- will return 'Trey & Wilson Research, Inc' -- even before USER A does a 'COMMIT TRANSACTION' -- because we are doing a dirty read (reading uncommitted)

Normally you would use this first isolation level for maximum query performance in situations where there is little or no risk of invalid uncommitted data.

The second level, READ COMMITTED is the default SQL Server isolation level. As the name implies, you can query only against committed data. So if another user is in the middle of a transaction that updates data, your query against the same record will be locked out until the original transaction completes. So in the example above, if User A updates the record and then User B tries to query before User A's transaction is completed, User B's query will be in deadlock until User A is done. At the end, User B's query will return the newly committed value. Here's an example of this level:

-- USER A USE AdventureWorks GO BEGIN TRAN -- change from Trey Research to 'Trey & Wilson Research, Inc.' UPDATE Purchasing.Vendor SET Name = 'Trey & Wilson Research, Inc.' WHERE VendorID = 10 -- USER B USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT Name FROM Purchasing.Vendor WHERE VendorID = 10 -- This query will remain in DEADLOCK, because USER A -- has an exclusive lock on the ROW -- The query will finish when USER A does a COMMIT or a ROLLBACK

The third level is REPEATABLE READ. In some stored procedures, a developer may need to query the same row or set of rows more than once. It's possible that another user may do something to update a row in between, which means that the original query might report a different value for the row. The process querying the data wants to ensure that the data remains the same between the two queries, even if someone tries to change the data during the process—in other words, the query wants to REPEAT the same read. So the user trying to update the record will be deadlocked out until the stored procedure finished. Listing 3 shows an example of this level.

The REPEATABLE READ level would seem to protect a long stored procedure that queries and re-queries data from any other processes that change the same data. However, there's one fly in the ointment. Remember earlier when I asked to make a mental note about "logically related data"? Suppose you have a long stored procedure that queries a set of data based on a condition, and then re-executes the same or similar query later in the procedure. Suppose someone INSERTS a new row in between, where the row meets the condition of the stored procedure?

This newly inserted record is known as a "phantom" record and you may not wish to see it until your stored procedure finishes. In this case the REPEATABLE READ doesn't protect you; the stored procedure would return a different result the second time.

However, the forth transaction level (SERIALIZABLE) protects this, by using "key-range locks" to lock out any insertions that would otherwise affect the conditions of queries performed inside a SERIALIZABLE transaction. SERIALIZABLE offers you the greatest level of isolation from other processes—l;and as you also can see-runs greater risk of other processes being locked out until you finish. Listing 4 shows an example of this level.

Granted, the enhancements with common table expressions and other query constructs will reduce the number of instances where a stored procedure will have to query the same data twice.

Many developers requested a finer level of granularity with isolation levels for SQL Server 2005, which I'll cover in the next tip.

Tip 6: Isolation Levels (part 2 of 2)
Scenario: You've reviewed the four isolation levels available in Tip #5 You're looking for the ability to query against committed data, but you don't want the query to implement any shared locks against the data.

In some situations, you may want to execute a series of queries in a stored procedure and be able to guarantee:

  • That you're querying against committed data only.
  • That you'll return the same results each time.
  • That another user can update the data, or insert related data, while your stored procedure is querying the same data, without the user experiencing a deadlock.
  • That even when the user commits the update or insert transaction, your query will still continue to use the same version of the data that existed when your stored procedure queries begin.
So, no deadlocks, and everyone is reasonably happy. Does it sound too good to be true? This scenario describes the SNAPSHOT isolation level in SQL Server 2005.

The key word in the scenario above is "version" because SNAPSHOT isolation works by internally creating a snapshot version of data into the system tempdb database. Listing 5 shows an example of the new SNAPSHOT isolation level. Note that you must configure the database to support this level:

-- First, you must configure the database to -- support SNAPSHOT isolation ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON GO

Of course, any option with this much power can come at a cost. You should use this isolation level (or any isolation level) only judiciously—abusing the SNAPSHOT level can become very resource intensive with the tempdb database.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date