TSQL Tips | Ado Examples and Best Practices

Team-Fly

SQL Server 7.0 added a number of new operators and functions to the Transact SQL dialect to make returning what you wanted easier, and to give the SQL Server engine a better idea of what you were trying to accomplish. Here are a few tips to make your Transact SQL queries work a little smoother—and faster! Remember that the new SQL Server 7.0 features aren't turned on unless you're executing queries against a SQL Server 7.0 database and it's not in 6.5 Compatibility mode. If you import your SQL Server 6.5 database into SQL Server 7.0, the server starts out in 6.5 Compatibility mode, so you'll have to turn this off manually before the new syntax is activated.

Using the TOP Clause

The TOP clause is one of these new SQL Server 7.0 features that's designed to limit the number of rows returned by your query to the first n or n percent of qualifying rows. For example, return the top 120 rows of the Titles table like this:

 SELECT TOP 120 Title From Titles

or return the top 15 percent of the Authors table like this:

 SELECT TOP 15 PERCENT Author From Authors

If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned. Using this technique, you can return the first n rows from the sales table in an attempt to show the best salesperson. But if you simply executed the following, you'd get the lowest 10 sales because the default sequence in an order by clause is ascending.

 SELECT TOP 10 Sale, SalesPerson from Sales Order by Sale

To get the "top" (best) sales, you'll have to make a little change:

 SELECT TOP 10 Sale, SalesPerson from Sales Order by Sale DESC

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

The SET ROWCOUNT limit applies to building the rows in the result set before an ORDER BY is evaluated. Even if ORDER BY is specified, the SELECT statement is terminated when n rows have been selected. The n rows are selected, then ordered and returned to the client. Of course,if this is not done in a stored procedure, the setting is persisted on the connection and remains in place until the connection is actually closed—not just released back to the connection pool. This means other applications or components using this connection from the pool (even you) can inherit the SET ROWCOUNT (or any other SET value). Try to debug that!
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.

Using SELECT INTO Instead of Brute Force

When you find it necessary to move rows in droves from one server to another, you might find that SQL Server (or many other data providers) can do this far more efficiently than constructing INSERT statements and submitting them one at a time. This assumes, of course, that the data is already on the server. If the data is currently living elsewhere and your only portal to the data is your application, then by all means consider these options:

The SQL Server Bulk Copy Program (BCP) excels at moving data from place to place—even fixed-length file data.
With SQL Server Data Transformation Services (DTS), SQL Server can be set up to access anything with an ODBC driver or OLE DB provider or any other server that participates in standard data transformation services—most do.
If all else fails, construct multiple INSERT statements into a batch and submit them all at once. I described how to do this in Chapter 8.

If, however, you merely want to move data from one table to another, use SELECT INTO. This operation is not logged (i.e., transacted), so it not only saves time, it's very easy to code.

 Select Author, Year_Born into NewAuthorsTable from Authors

However, SQL Server won't let you do this until all of your duckies are in a row.

You have to activate this option before you start. Get your SA to turn on "Select into/bulkcopy" using sp_configure or by selecting the bulk copy/select into option in the database property sheet.
The statement shown above creates a NewAuthorsTable, so that table cannot exist before you run the query.
Because the table does not exist, don't worry about indexes—these have to be created after the SELECT INTO is done.
After a SELECT INTO, the Dump transaction operation will work only after you tell SQL Server to dump the database. Don't know how? Ask the SA.

Using UNION to Combine Identical Recordsets

If the multiple queries have identical column results and you want to process them as one Recordset, you could also look at using the UNION statement. It's not right for all situations, but does come in handy from time to time.

 select X "FeedDesc" from mytable1 UNION select Y "FeedDesc" from mytable2 UNION select Z "FeedDesc" from mytable3

Assigning and Testing for NULL

Remember in ANSI SQL (TSQL, too) to assign a NULL to a variable, you code this:

 SELECT @Variable = NULL

However, when testing for NULL, you need to use the IS NULL syntax:

 WHERE YearDied IS NULL

The Visual Basic 6.0 Visual Database Tools have not picked up on this nuance, so you have to be careful when constructing your queries using the GUI tools.

Team-Fly