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.
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.
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.
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.
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.
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.
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.