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