Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to r2r@sqlmag.com. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.
Ordering by SQL Views
SQL Server usually doesn't allow an ORDER BY clause in views—a situation that both astounds and outrages me. However, you can use this workaround: Just add a TOP 100 PERCENT clause to the front of the SELECT list.
Here's an example of how to use TOP 100 PERCENT to order data in views. The Northwind database contains a view erroneously called Alphabetical list of products. If you open this view, you'll see that it doesn't show the products in alphabetical order. To put the products in alphabetical order, you modify the SQL code as follows:
SELECT TOP 100 PERCENT Products.*,
Categories.CategoryName AS CategoryName
FROM Categories INNER JOIN
Products ON
Categories.CategoryID =
Products.CategoryID
WHERE (Products.Discontinued = 0)
ORDER BY ProductName
Microsoft Access will complain when you try to save the query (ORDER BY not allowed in this type of query). Ignore the warning, and save the view. When you open the view, you'll see the results in order of ProductName.
I researched this workaround's possible overhead by using Query Analyzer to compare the runtimes and execution plans of two SELECT statements: one with the TOP clause and one without. I used the SQL Server version of FoodMart, which has more data than Pubs or Northwind, to select all 10,000-plus rows from the customer table in order of lname,fname. On my system after cache preloading, each statement executed in 28 seconds. The graphical query plan showed the TOP statement as being almost 0 percent of the statement's total processing cost. I examined another multitable SELECT, and again, the TOP clause imposed virtually no penalty.
—Brad Aisa
baisa@brad-aisa.com
Storing SQL Server 7.0 Server Registrations on a Centralized System
Do you manage multiple SQL Server machines and find yourself accessing these servers from more than one location? SQL Server Enterprise Manager's Store user independent option lets you set up a SQL Server server registration on one server, then share that registration with Enterprise Manager on other servers. Whenever you need to administer your servers from a new location, you can point at the centralized server's registration profile.
To set up a central store for server registration information, first click the server name in Enterprise Manager on the remote server. Click Tools, Options, then select the General tab. Clear the Store user independent check box. You can now use Enterprise Manager to create server groups and to register servers.
To set up a local server to access this central registration information, click the server name in Enterprise Manager. Click Tools, Options, select the General tab, then select Read from remote. In the Server name box, enter the name of the remote server that contains the registration information.
—Peter Rawsthorne
peter.rawsthorne@netperform.com