SQL Server Magazine UPDATE, September 26, 2002

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine LIVE! http://www.sqlmagLIVE.com

THIS ISSUE SPONSORED BY

Is Your SQL Server Data Center on Cruise Control?
http://www.hp.com/servers/proliantessentials

More Sessions, Best Quality, Unbeatable Value!
https://secure.win2000mag.com/events/sql_register.asp
(below COMMENTARY)

Increase Server Uptime and Stability - Free
http://diskeeper.com//downloads/survey.asp?PId=15&ad=sqltextbx
(below NEWS AND VIEWS)

SPONSOR: IS YOUR SQL SERVER DATA CENTER ON CRUISE CONTROL?

It can be with HP ProLiant Essentials... a portfolio of modular, flexible software tools that expand the capabilities of ProLiant servers. The ProLiant Essentials Foundation Pack contains the essential software needed to quickly and easily deploy a ProLiant server into a managed infrastructure. ProLiant Essentials Value Packs, optional software offerings, let you selectively extend and enhance the potential of your current IT infrastructure. Using Adaptive Infrastructure technologies, value packs facilitate an array of advanced provisioning, deployment, availability and management capabilities within your SQL Server environment. For more information, visit
http://www.hp.com/servers/proliantessentials

September 26, 2002—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. ANNOUNCEMENTS

4. HOT RELEASES (ADVERTISEMENTS)

5. RESOURCES

6. NEW AND IMPROVED

7. CONTACT US

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])Last week, Microsoft announced availability of SQL Server 2000 Windows CE Edition (SQL Server CE) 2.0. The new release includes updates that will improve your query capabilities and let you set security and connectivity options more quickly. But the most important change is that SQL Server CE is now integrated with the .NET Compact Framework and Visual Studio .NET. Combined, these tools provide a desktop and mobile-device programming model that's unified with the .NET Framework and that makes building applications for mobile devices easier than ever.

Pocket computing for business applications hasn't taken off as many folks expected. One of the biggest hurdles that pocket computing has faced is that few people are writing native applications for Windows CE devices. But the advent of unified programming models for desktop (i.e., connected) and mobile applications will make it easier for people to write once and deploy twice. In other words, a developer can use a mainstream desktop as an application's initial and primary development platform, but by making a relatively simple port, the developer can also deploy the application on mobile devices. I think this integrated development environment will boost the adoption of mobile applications in corporate America. And Microsoft agrees with me. The company says it expects consistency among mobile technologies to encourage more Visual Studio programmers to develop mobile applications. And Microsoft expects the increased developer engagement to "translate into more [and] better mobile applications for enterprise users."

With the demand for mobile applications expected to increase, mobile database computing might well be a high-growth specialization for SQL Server professionals to consider. To learn more about this exciting technology, you can download SQL Server CE 2.0 from a link on the product home page at http://www.microsoft.com/sql/CE/default.asp . This site also includes links to product overviews and a marketing-oriented evaluation guide. You'll find more technical information on the SQL Server CE Technical Resources site at http://www.microsoft.com/sql/ce/techinfo/default.asp . This site includes complete product documentation, an FAQ section, and a variety of white papers to help you get started with SQL Server CE.

MORE SESSIONS, BEST QUALITY, UNBEATABLE VALUE!

SQL Server Magazine LIVE! provides you with an in-depth menu of sessions to choose from covering the most advanced topics down to the basics. On Oct. 27-30, 2002, at the Hyatt Grand Cypress Resort in Orlando, Florida, we will provide relief for those who hunger for the latest insights and how-to information necessary to build and maintain scalable and reliable databases that drive mission-critical applications. Increase your productivity with shortcuts, tips and tricks you'll learn only at this conference; discover the best ways to integrate SQL Server with Web applications; solve tough interoperability issues; enhance database administration with new tools; benefit from third-party gurus and magazine authors who'll share real-world experiences; and much more! For additional information and to register, go to
https://secure.win2000mag.com/events/sql_register.asp

2. SQL SERVER NEWS AND VIEWS

Sponsored by BMC
http://ad.doubleclick.net/clk;4600781;7448876;v?http://www.bmc.com/offers/sqlexplorer_trial/

The voting has closed in SQL Server Magazine's nonscientific Instant

Poll for the question, "How do you prefer to get how-to or best-practices information from Microsoft?" Here are the results (+/- 1 percent) from the 180 votes:

The next Instant Poll question is "What types of production applications are you creating through the .NET Framework?" Go to the SQL Server Magazine Web site and submit your vote for 1) Web or ASP.NET applications, 2) Windows-based Visual Basic .NET applications, 3) Windows-based C# applications, 4) Windows-based C++ applications, or 5) I'm not using .NET for my production applications.
http://www.sqlmag.com

SPONSOR: INCREASE SERVER UPTIME AND STABILITY - FREE

Diskeeper(R) Lite, the high-speed manual introduction to the popular automatic defragmenter Diskeeper 7.0, is available for immediate download.

Fragmentation is the root cause of many system problems, server slowdown being only one of them. Eliminating fragmentation is the key to increasing network performance. Diskeeper Lite was built with the same advanced defragmentation technology as Diskeeper 7.0 and can be used on Windows(R) 98 on up.

Find out what you're missing. Download Diskeeper Lite now. Free!
http://diskeeper.com//downloads/survey.asp?PId=15&ad=sqltextbx

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

Fine-tune your SQL Server performance at the next SQL Server Magazine Web seminar, "Analyzing Locking and Blocking," taught by Kalen Delaney, noted SQL Server author and Microsoft MVP, on October 1 at 1:00 PM Eastern time. For more information or to register for this event, sponsored by Precise Software Solutions, go to
http://secure.duke.com/nt/sqlmag/webseminar/index.cfm?code=&pc=kdei322i14

Exclusive in-depth articles, tips, tricks, and code samples all at your fingertips. Content you can't get anywhere else—brought to you by the SQL Server experts you trust such as Kalen Delaney, Itzik Ben-Gan, and others. Increase your productivity today! Go to the following URL.
http://www.tsqlsolutions.com

4. HOT RELEASES (ADVERTISEMENTS)

OpalisRendezVous offers inbound collection and outbound distribution of files to ensure directories have the most up-to-date data. It complements disaster recovery and data protection strategies by reducing data loss. Transfer files across the network using copy, Internet and FTP methods. Download and get FREE online training:
http://www.opalis.com?source=sql-09-2002rdv

Get better performance for Microsoft SQL Server databases on a Unisys ES7000 server. The Unisys ES7000 database server couples Microsoft/Intel economics with a robust and scalable server architecture. Delivering more headroom for growth, faster response times and superior availability.
http://www.unisys.com/simplify/?b=01nl01ss

SQL Server Magazine LIVE! will co-locate with Microsoft ASP.NET Connections and VS.NET Connections. Register now, save $2,990. Access all three events—160+ sessions. Chock-full of real-world tips and new ideas to give you the edge.
https://secure.win2000mag.com/events/sql_register.asp

5. RESOURCES

Have you tried making .NET and ADO MD work together? SQL Server Magazine contributing editor Russ Whitney has—and he found that it's not straightforward. In "Using ADO MD with .NET," he tells you about some of the problems he ran into and some ways you can avoid similar complications when you try to integrate .NET with your existing COM applications. This article appears in the September 2002 issue of SQL Server Magazine and is available online at
http://www.sqlmag.com/articles/index.cfm?articleid=25800

Kel_best's organization has purchased a SQL Server 7.0 database, but the database supplier has encrypted the stored procedures. Kel_best wants to know if you can unencrypt stored procedures once they're encrypted. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=8&threadid=8892

(contributed by Brian Moran, [email protected])

Q. I have a table that includes a single-column primary key called IdValue. Given a particular value for IdValue, I want to find the table rows that are immediately before and after my target value (assuming the result set is sorted by IdValue). How can I avoid using cursors and get the result I want with a set-based approach?

A. T-SQL is a set-based language and doesn't easily lend itself to solutions that assume positioning of particular rows within a result set. However, the performance of server-side ANSI T-SQL cursors is usually much worse than the performance of a set-based solution, so learning different techniques for solving problems such as this is important.

Let's use the Northwind database's Orders table for an example. We'll restate the problem like this: How can I find the row immediately before and immediately after a particular row in the Orders table? We'll assume we're ordering the result set on the OrderId column.

Creative use of SQL Server's MIN(), MAX(), and TOP capabilities can help you deal with positional result set questions such as this one. Listing 1 and Listing 2, at the end of this answer, show two similar but slightly different approaches for solving the problem. Listing 1 provides a general solution because it works with MIN() and MAX() end points for @TargetOrder. However, I wanted to demonstrate the flexibility that TOP can provide in some cases. Note that the query in Listing 2 returns an empty set when you provide the minimum OrderId (10248) from the Orders table as an argument because the query assumes a row will always exist before the @TargetOrder row.

You typically have more than one way to generate a result set, and one approach is usually more efficient than the others. When you evaluate SHOWPLAN and SET STATISTICS IO information for both of these examples, you'll see that Listing 2, which uses the TOP statement, is slightly more efficient than Listing 1. The differences are small because the sample data sets are small, but it's important to test the performance of different query approaches when more than one option exists.

LISTING 1: Using the MIN() and MAX() Functionswith the OR Keyword to Find Target RowsDECLARE @TargetOrder int SET @TargetOrder = 10330SELECT OrderId FROM OrdersWHERE OrderId = @TargetOrder          OR OrderId = (SELECT MAX(OrderId)             FROM orders WHERE OrderId < @TargetOrder)         OR OrderId = (SELECT MIN(OrderId)            FROM orders WHERE OrderId > @TargetOrder)

LISTING 2: Using the TOP Keyword to Find Target RowsSELECTTOP 3*FROM ordersWHERE OrderId >= (SELECT MAX(OrderId) FROM ordersWHERE OrderId < @TargetOrder)ORDER BYOrderId

Send your technical questions to [email protected].

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Ecora Software announced Configuration Auditor for SQL Server. The software discovers, documents, and tracks changes to configuration settings on servers. You can use Configuration Auditor to enforce configuration compliance by comparing the configuration of a standard server against other servers in the environment. The software lets you see which users are assigned to which server roles. And you can collate information about all your SQL Server databases, tables, and permissions. For pricing, contact Ecora Software at 603-436-1616 or 877-923-2672.
http://www.ecora.com

TheDevShop announced dbQwikSite 2.0, software that can create Web pages by using your SQL Server database data. The software can produce Web catalogs, lists, and online store sites. The program connects to any database through ADO or ODBC. The preview feature lets you see how the final generated pages will appear with the data. The Standard version produces static HTML sites. The Pro version adds Active Server Pages (ASP) code to search, add, edit, and delete data and let you manage server data remotely. DbQwikSite runs on Windows XP, Windows 2000, Windows NT, Windows Me, and Windows 9x systems. The Standard version costs $79, and the Pro version costs $129. Contact TheDevShop at [email protected].
http://www.dbqwiksite.com

7. CONTACT US

Here's how to reach us with your comments and questions:

(please mention the newsletter name in the subject line)

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

SQL Server Magazine LIVE!'s full conference schedule is now online.

This real-world conference, packed with best practices, is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job. Register now and access concurrently run Microsoft ASP.NET Connections and VS.NET Connections for FREE!
http://www.sqlmagLIVE.com

Receive the latest information about the Windows and .NET topics of

your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email

Thank you for reading SQL Server Magazine UPDATE.