I understand how to use the TOP clause within a SELECT statement to retrieve the first n rows of a resultset, but I have a business need to return the second set of n rows. For example, rather than returning the first 10 rows, I want to return rows 11 through 20. What's a good way to retrieve a second set of rows?
SQL is a set-based language, which means that it doesn't support working with specific rows within a resultset well. Using a server-side T-SQL cursor to solve the problem of get rows 1120 is easy, but I don't recommend that solution. Most SQL experts agree that server-side T-SQL cursors are horribly inefficient. Although T-SQL cursors aren't a problem when you're working with a query that you run infrequently and that doesn't operate on many rows, I recommend that you avoid server-side T-SQL cursors in favor of set-based solutions whenever possible.
A set-based solution is a solution that you can express by using single T-SQL commands that operate on a range of rows to return one resultset. With this solution, you don't need to rely on the use of a T-SQL cursor.
Sometimes, taking a problem that lends itself to a row-by-row, iterative cursor-based solution and creating a set-based T-SQL solution is difficult. However, you can usually find a creative way to solve the problem. The T-SQL code in Listing 1 shows one example of how you can return rows numbered 1 through 10 from a resultset without using a T-SQL cursor.
The query in Listing 2 shows the 10 orders with the lowest total order amounts from a specific vendor. This query might be a bit complex to follow if you're a SQL novice, so let's walk through it quickly. You reuse the query from Listing 1 twice in Listing 2. You use it the first time to generate a list of the 10 order IDs with the lowest total order amounts, which you then use as the subquery that the NOT IN clause operates on. The outer query in Listing 2 is essentially the same query as in Listing 1 except that you filter out the order IDs that you've already counted within the subquery.
You can choose from a variety of set-based operations for accomplishing this same task. For this answer, I didn't worry about query optimization, and the NOT IN clause isn't always the most efficient query construction. However, this construction makes the logic and query easier to follow than some alternative query formulations; it also makes demonstrating the primary point of this answer—avoiding row-by-row T-SQL cursor operations—easier.
I need to programmatically drop SQL Server logins associated with Windows authentication. I have a login named PACIFIC\VNE1B that uses Windows NT security authentication. When I supply the sp_droplogin procedure with PACIFIC\VNE1B or VNE1B, I get an error message that says the login doesn't exist. What's wrong?
I love the simple questions! You should spend some time in SQL Server Books Online (BOL) reading about the differences between Windows-authenticated and SQL Serverauthenticated logins. The simple answer is that sp_droplogin is intended for managing SQL Serverauthenticated logins only. Sp_revokelogin is the correct command to use when you want to drop logins that use Windows authentication.
Under the covers, different sets of T-SQL commands control Windows-authenticated logins and SQL Serverauthenticated logins. But don't worry—it's easy to get confused if you primarily work through SQL Server Enterprise Manager's GUI tools, which mask many low-level details.
I work on a payroll-management database and suggested to the development team that I use a datetime column as a table's primary key. Team members told me not to do this, but when I pressed for a reason, no one could provide a good answer. Why shouldn't I use a datetime column as a primary key?
I agree with your development team. In general, you should avoid using datetime as a primary key for a couple of reasons.
Prev. page
1
[2]
3
next page 
