DELETE Statements - Microsoft SQL Server Black Book pdf

This is the most dangerous statement in your SQL arsenal. You can remove a record from a table permanently with the DELETE statement. Like SELECT and UPDATE statements, DELETE statements use the WHERE clause. Once a record has been deleted, it no longer exists in a recoverable state in any location other than a backup of either the transaction log or database. I always use transactional processing when deleting records. If an error occurs in the execution of the command, I can roll back the deletion and restore my records.

If you omit the WHERE clause from your query, the entire table will be cleaned out; each and every record will be removed. This is a logged

operation, so if your table has many rows, you will generate a lot of traffic in the transaction log. If you are trying to remove all the records in a table and no longer need to log the transactions, use the TRUNCATE TABLE statement instead of DELETE. TRUNCATE TABLE is not logged and gives you the same results much faster than DELETE. For more information on the TRUNCATE TABLE statement, see Microsoft SQL Server Books Online.

Both statements leave the schema of a table intact and do not drop existing indexes from the target table. Use this feature with great caution. At least once a month I get an email asking me to explain how to recover from a situation like this without affecting other tables and the relational integrity within a database. You can recover from it, but it takes time and disk space, and necessitates a sound backup of your data. See Listing 5.17 for a sample DELETE statement.

http://www.itknowledge.com/reference/standard/1576101495/ch05/170-172.html (2 of 3) [1/27/2000 6:16:22 PM]

Listing 5.17 Sample DELETE statement.

/* Remove myself from the Authors table */

DELETE FROM authors

WHERE au_id = '123-45-6789'

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch05/170-172.html (3 of 3) [1/27/2000 6:16:22 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book (Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Batches

An SQL script is a file that contains one or more batches of SQL code that you wish to run against your server. SQL statements are inherently implied transactions and will be executed one by one unless you specify otherwise. A batch is a section or grouping of SQL statements that you wish to execute together and treat as a unit of execution. They are not transactions, although you can use transactional processing inside of batches to maintain the integrity of data.

Up to this point I have used single statements executed one at a time. If I wanted to insert, display, or modify data, I could create a text file with a batch in it and open that to run when needed. I create many of these for installation of systems, day-to-day reports, or troubleshooting.

A script can contain many batches. A batch is delimited by the word GO on a line by itself. See Listing 5.18 for a sample batch of SQL code. For clarity, I have used queries that you have already seen.

Listing 5.18 Sample batch script.

/* Sample batch to add remove and display authors */

/* Dated: 07/15/97 */

/* Batch 1 - insert */

BEGIN

BEGIN TRANSACTION INSERT INTO authors VALUES

http://www.itknowledge.com/reference/standard/1576101495/ch05/172-175.html (1 of 3) [1/27/2000 6:16:23 PM]

Go!

Keyword

---Go!

'Shepherdsville',

DECLARE @oldPhone CHAR(12), @newPhone CHAR(12) DECLARE @tmpString VARCHAR(255)

BEGIN TRANSACTION

/* Get the current phone number */

SELECT @oldPhone = phone FROM authors

WHERE au_id = '123-45-6789'

/* Change the phone number and contract status */

UPDATE authors

SELECT @tmpString = 'Error performing update!' ROLLBACK

http://www.itknowledge.com/reference/standard/1576101495/ch05/172-175.html (2 of 3) [1/27/2000 6:16:23 PM]

PRINT @tmpString END

I have introduced some control-of-flow statements here with local and global variables to help illustrate just what can be done with batches. Local variables are only available inside a single batch, so be careful not to use them across batches without re-initializing them and assigning new values to them. The IF, BEGIN, and END statements help define the conditional nature and flow of my code. I do not have to use the BEGIN and END statements in all the areas, but I do it out of habit and for clarity. The only time I have to use BEGIN and END statements is after a conditional or looping statement that has more than one line of code.

I also used some string manipulation again to illustrate how to build a string inside your code. The CHAR() function evaluates 10 to be the new line character for me and places that in my string. The use of the global @@error variable to check for an error state is standard practice. @@error holds the return value for the previous SQL statement only. A value of zero means successful execution with no warnings or errors.

The GO statement at the end of the batch tells the server that I am finished and want this section of my code to be executed as a group. I have included some transactional processing in these batches to show how you can “undo” a modification or group of modifications in the event of an error on the server. For more information on batches, control-of-flow statements, and variables, see Microsoft SQL Server Books Online.

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch05/172-175.html (3 of 3) [1/27/2000 6:16:23 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Cursors

This is the subject most SQL programmers master last. Using cursors requires intimate knowledge of your table structures and the ability to visualize what is happening with your code on the server. Cursors are very powerful and provide a great deal of flexibility.

Cursors are very versatile. From overnight processes to table-driven procedures and queries, their usefulness covers many areas of a production environment. Cursors allow you to manipulate data on a row-by-row basis rather than with result set-based queries. This method of processing is in most cases slower than result set-based queries are, and it typically takes up more memory on the server. Depending on your needs, you can implement cursors at the client or at the server. I will focus on the server-side cursors here since each client has different requirements.

Cursors require you to use local variables for each column value that you load into a cursor. They also require the definition and manipulation of the cursor as if it were a pointer definition. When you use cursors in your scripts or stored procedures, five keywords will play important roles: DECLARE,

OPEN, FETCH, CLOSE, and DEALLOCATE. See Table 5.5 for the purpose of each of these keywords.

Table 5.5 Keywords for cursors.

Word Use

DECLARE Defines the cursor through a SELECT statement that shows the columns and defines the rows that will be included in this cursor. A standard SELECT statement is used after the cursor name and type is given.

Allocates memory for the cursor on the server. Must be supplied before an OPEN statement can be issued.

OPEN Physically opens the cursor and gets the defined record set as it exists in the table at the time of the statement execution.

FETCH Retrieves the specified record and places the column values for that row into local variables for further manipulation.

CLOSE Closes the cursor and releases any locks that may have been placed automatically on any data pages due to the cursor definition.

http://www.itknowledge.com/reference/standard/1576101495/ch05/175-177.html (1 of 3) [1/27/2000 6:16:25 PM]

Go!

Keyword

---Go!

DEALLOCATE Removes the data structure from memory on the server. You may reopen a cursor after it has been closed, but not after deallocation. You must redefine the cursor once it has been released with this statement.

A cursor can also be created through SQL-passthrough and ODBC function calls. See Microsoft SQL Server Books Online for more details.

On any system that places structures into memory, it is a good idea to properly dispose of those

structures. The CLOSE and DEALLOCATE statements should be issued when you have completed the manipulation of data. Leaving them open holds those resources and limits others’ ability to perform tasks on the server.

Microsoft SQL Server Books Online contains a very good example of how to use a cursor to perform an update of the statistics. See Listing 5.19 for the code from Microsoft SQL Server Books Online. I have modified and formatted the code only slightly, in accordance with my preferences. Previously, there was no CLOSE statement at the end of the run, just a DEALLOCATE statement. While this will still run okay, it is against my practices to assume things will always work as advertised. Better safe than sorry.

In addition, I have added the SET statement to turn off the display of the number of rows affected by the query. This is strictly for personal preference and could be removed at no cost to the actual performance of the procedure.

Listing 5.19 introduces the syntax needed to create a stored procedure. We’ll cover stored procedures in detail in the next chapter, but I just want to illustrate here that in some situations, code in Microsoft SQL Server Books Online can be both copied and pasted right into your server and be highly useful. If you would like to copy this procedure instead of the one in the online documentation, see the

upd_all_stats.sql document on the CD-ROM accompanying this book.

Listing 5.19 Microsoft SQL Server Books Online code illustrating cursor uses.

CREATE PROCEDURE update_all_stats AS

/* This procedure will run UPDATE STATISTICS against */

/* all user-defined tables within this database. */

/* Turn display text off */

FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2) BEGIN

SELECT @tablename_header = "Updating: " + RTRIM(UPPER(@tablename)) PRINT @tablename_header

http://www.itknowledge.com/reference/standard/1576101495/ch05/175-177.html (2 of 3) [1/27/2000 6:16:25 PM]

EXEC ("UPDATE STATISTICS " + @tablename) END

FETCH NEXT FROM tnames_cursor INTO @tablename END

PRINT " "

SELECT @tablename_header = "************* NO MORE TABLES" + " *************"

PRINT @tablename_header PRINT " "

PRINT "Statistics have been updated for all tables."

CLOSE tnames_cursor

DEALLOCATE tnames_cursor GO

Previous Table of Contents Next

http://www.itknowledge.com/reference/standard/1576101495/ch05/175-177.html (3 of 3) [1/27/2000 6:16:25 PM]

Brief Full Advanced Search

Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

The key to using cursors well is understanding the FETCH statement. You can fetch rows in any direction as well as absolute positions into variables for manipulation. The global variable

@@fetch_status tells you if there are no more rows to be returned and helps you control flow within the WHILE loop construct. Fetching NEXT or PREVIOUS inside of a loop allows you to move through the records one at a time and exit the loop when finished. Beware of missing that FETCH statement and variable check when writing your code. You can place yourself in a continuous loop with no exit point if you are careless and miss a logic check.

You can nest cursors inside other cursors to add to the feature and function of your code. Nesting cursors requires a bit of planning and some good naming conventions with regard to your local variables. Tackle nested cursors only after you have become comfortable with a standard cursor and can visualize what is being done on the server. You can add PRINT statements to your cursors to check the values of variables at different stages in the cursor.

NOTE: The PRINT statement will only allow character data types, so use the CONVERT statement to change any noncharacter data to text before printing. You cannot use functions or expressions inside a PRINT statement. Load a local variable with the string you wish to print, then print that string.

Summary

• Comment your SQL code clearly.

• Specify the name of the columns you wish returned in your queries.

• Always use a WHERE clause in production-based queries.

• NOT is not optimizable!

• Joining tables requires N = T – 1 join conditions in the WHERE clause.

• Use the (NOLOCK) optimizer hint on highly active data for read-only queries.

• Use transactional processing when modifying data for safety and recoverability.

• Do not omit the WHERE clause from an SQL statement unless you intend to affect an entire table.

• Use PRINT statements inside your cursors to debug your code.

• Use Microsoft SQL Server Books Online to familiarize yourself with SQL and

http://www.itknowledge.com/reference/standard/1576101495/ch05/177-182.html (1 of 3) [1/27/2000 6:16:26 PM]

Go!

Keyword

---Go!

troubleshooting your queries. It is very useful—get in the habit!

In document Microsoft SQL Server Black Book pdf (Page 162-171)