SQL Server Magazine UPDATE, August 22, 2002

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

THIS ISSUE SPONSORED BY

Next Generation of Business Intelligence Is Here
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GE0AP

Head Back to School Online with SSMU!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GF0AQ
(Below COMMENTARY)

Get FREE IT Training and WIN a Dell!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GG0AR
(below NEWS AND VIEWS)

SPONSOR: NEXT GENERATION OF BUSINESS INTELLIGENCE IS HERE

Do you want to learn about the architecture behind the 'Best in Business Intelligence?' Your competitors know that a solid business intelligence platform is the foundation of all analytical applications within a corporation. Learn how MicroStrategy has developed the next-generation business intelligence platform to support the full range of BI applications: reporting, analysis, and ad-hoc queries. Order your copy of "An Architecture for Next-Generation Business Intelligence" today.
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GE0AP

August 22, 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])

Three weeks ago, I described a SQL Server system-design philosophy that I call "designing for extreme performance." Many of you asked me to clarify my thoughts in a few key areas, and I'm going to start thatprocess this week. I'll continue to share my extreme-performance strategies—and those you send me—over the next few months. You can reread the original commentary, "Designing Databases for ExtremePerformance," at http://www.sqlmag.com/articles/index.cfm?articleid=26139 , but here's a quick recap if you're pressed for time: Designing for extreme performance means

The logical conclusion of those three strategies often leads me to place complex business logic and processing on the Web tier rather than the database tier. I sometimes place this logic and processing onthe Web tier even when response time during below-peak conditions would actually be faster if I moved more logic to the database tier. Some of you asked why. And the short answer is that, eventually, thedatabase server might run out of horsepower. If that happens, scaling out the stateless Web farm, as I said earlier, is much easier than scaling out the database server.

Here is another set of comments and questions from a reader who cut directly to the heart of the matter and summed up several other people's responses:

"You gave the impression that it's more scalable to place processing in components in the Web server instead of in stored procedures. I agree for some functions, but I don't agree for all functions. It depends upon what the function is. Does it need more data from the database to do its job? If so, it will probably not help scalability to move the work from the database. The database will have to work hard anyway. It might even hurt scalability. I guess we agree that 'it depends,' and [you probably] tried to provoke [us] a bit by giving 'one single truth' for all situations."

I'll let you in on a little secret. The only absolute truth in the world of database performance tuning is "it depends." Anyone who tells you that something is always the correct answer is probably a) lying or b) ignorant. (Notice that I said "probably"—a tricky way of saying "it depends." ) The nice thing about testing a tuning hypothesis is that you can often easily observe a before and after result. Unfortunately, we typically have to make educated guesses about the best answer early in the design phase, before we can fully test a specific theory. In those cases, we have to rely on rules of thumb that have served us well in past projects. So, when should you put processing logic in the Web tier rather than the database? It depends. However, the following rules of thumb are valuable to keep in your bag of tricks:

Incorporate these design principles into your applications. Live by the law of "it depends." Always test a tuning hypothesis with your own data, from your own applications, on your own servers. Your end userswill thank you for it.

HEAD BACK TO SCHOOL ONLINE WITH SSMU!

Introducing SQL Server Magazine University a virtual classroom environment where you receive quality technical training on your schedule. Microsoft Certified Trainers help you acquire practical SQL Server skills and prepare for your certification exam at the same time. 24x7 access to our Virtual Computer Lab allows you the flexibility to learn new applications while keeping up with your day-to-day job duties. Our virtual computers are specifically configured for each training session no need to set up the programson your personal system saving you valuable time. Move to the head of the class enroll today!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GF0AQ

2. SQL SERVER NEWS AND VIEWS

Microsoft is now accepting nominations for beta testers for the 64-bit version of SQL Server 2000 (code-named Liberty), SQL Server 2000 CE Edition 2.0, and SQL Server 2000 Service Pack 3 (SP3). The SQLServer development team will use the feedback from beta testers to help refine and enhance product features. Any SQL Server customer can apply to participate. Microsoft manages newsgroups for each of thebeta programs, sends status email messages, and uses its BetaPlace Web site to provide content associated with the programs. If selected to participate in a beta program, you'll receive download instructions for the Beta Kit. Microsoft asks that you install the beta software in your development and test environments, run your test suites, and provide feedback to the newsgroups on any problems you encounter. You're also encouraged to participate in the beta program online chat sessions and WebCasts. To register for any of these beta programs, go to the following URL:
http://www.microsoft.com/sql/evaluation/betanominations.asp

David Litchfield of NGS Software discovered vulnerabilities in SQL Server and Microsoft Desktop Engine (MSDE) that could result in an unprivileged user gaining control of the database. These vulnerabilities stem from weak default permissions on certain extended stored procedures that let unprivileged users run these stored procedures with Administrator privileges. Microsoft has released Security Bulletin MS02-043 (Cumulative Patch for SQL Server) to address this vulnerability and recommends that affected users download and apply the patch mentioned in the security bulletin.
http://www.secadministrator.com/articles/index.cfm?articleid=26292

Sponsored by BMC Software

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Are you interested in the new .NET Framework Data Provider for Oracle?" Here are the results (+/1 percent) from the 272 votes

Sponsored by Sybase & Sun Microsystems
iForce Solution for Economical Data Management: Enterprise Strength at PC Prices. For details & to win $500...
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GH0AS

The next Instant Poll question is, "What is your largest data warehousing issue?" Go to the SQL Server Magazine Web site and submit your vote for 1) Time to market/deployment, 2) Scalability, 3) Performance, 4) Hardware/software costs, or 5) Ease of use.
http://www.sqlmag.com

SPONSOR: GET FREE IT TRAINING AND WIN A DELL!

Visit SmartCertify Direct's web site NOW and get FREE IT training! Over 100 FREE titles are available on our web site, including MCSE, CIW, Cisco, Linux, A+, .NET, MCP and MORE! Our courses come with hands-on interactive exercises, Test Prep exams, 24-hour online mentoring and a money-back certification GUARANTEE! Click here for FREE IT training and for your chance to WIN a new Dell PC:
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GG0AR

3. ANNOUNCEMENTS

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

Join Morris Lewis for SQL Server Magazine's next Web Seminar, "Planning Highly Available Database Server Environments," on August 27. This seminar will explain methods for achieving high availabilityand detail the criteria you must evaluate to determine which options will best suit your tolerance for risk and your budget. Register today!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ03qd0Ab

If you're looking for a source of daily articles from around the world, how-tos, reviews, and more for your SQL Server, Oracle, XML, and other database responsibilities, SSWUG is the place. With a daily newsletter, product reviews, list servers, special member discounts, local user group calendar and support, and more, SSWUG is the place to be!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GI0AT

4. HOT RELEASE (ADVERTISEMENT)

Restore truncated data and dropped tables easily and quickly

Before you put T-SQL's OpenXML functionality to work inserting, updating, and deleting data in a real-world application, you need to fix a couple of glitches. In his Exploring XML column "Overcoming OpenXML Hangups," Rich Rollman shows you how to solve the problem of undeclared namespace prefixes and how to deal with storing unmapped data. The column appears in the August 2002 issue of SQL Server Magazine and is available online at
http://www.sqlmag.com/articles/index.cfm?articleid=25572

Verbose is curious about how much he should rely on the Index Tuning Wizard's recommendations. 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=5&threadid=8123

(contributed by SQL Server MVP Brian Moran, [email protected])

Q. I'm trying to use a CASE expression in an ORDER BY clause to return a result set in different sort orders based on a parameter passed to the procedure. How can I get the result set ordered as I want?

A. Dynamically ordering a result set based on the evaluation of a CASE expression is a powerful technique for ordering your data. The following example shows some possible gotchas and explains how using multiple CASE statements can help you get the results you want.

The following SQL script shows how you might try to use a CASE expression to dynamically order a result set:

DECLARE @OrderByOption int   SET @OrderByOption = 2   SELECT  ProductId           ,ProductName   FROM        products   ORDER BY          CASE               WHEN @OrderByOption = 1 THEN ProductId               WHEN @OrderByOption = 2 THEN ProductName          END

Conceptually, the query offers the ability to order by either the ProductId column or the ProductName column based on the current value of @OrderByOption. The above statement attempts to order by ProductName, but produces the error, "Server: Msg 245, Level 16, State 1, Line 4 Syntax error converting the nvarchar value 'Alice Mutton' to a column of data type int." However, the script works if the value of @OrderByOption is set to 1.

To understand why the query works when the value for @OrderByOption is set to 1 but doesn't work when the value is set to 2, you need to recognize that the two THEN conditions of the CASE statement referenceexpressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence. (For information about data-type precedence and conversion, see the SQL Server Books Online (BOL) topic "Data Type Precedence.") In this example, the CASE statement has two possible values that follow a THEN clause: ProductId, which is an integer data type, and ProductName, which is an nvarchar data type. The integer data type has a higher precedence than the nvarchar data type, so SQL Server attempts to cast the ProductName expression as an integer if you try to order by that column. Such a conversion isn't allowed, soSQL Server generates the above error.

You can work around this problem by using multiple CASE statements, as the following example shows:

   DECLARE @OrderByOption int   SET @OrderByOption = 2   SELECT  ProductId           ,ProductName   FROM    products   ORDER BY           CASE   WHEN @OrderByOption = 1 THEN ProductId    END           ,CASE  WHEN @OrderByOption = 2 THEN ProductName  END

Send your technical questions to [email protected].

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Harry von Borstel Computer Engineering released blueshell Active Tables 3.0, software that connects Visual Basic (VB) 6.0 and 5.0 and the .NET languages to SQL Server and MySQL databases. The software is a control suite that can handle all aspects of database client development. The software uses table controls to support a grid view. The connected table controls observe the database's entity relationships. Blueshell Active Tables works in the IDE of Visual Studio .NET or VB to let the developer redefine anything at any time. Pricing is $250 for a license. Contact Harry von Borstel Computer Engineering at [email protected].
http://www.blueshell.com

Tools4ever released MonitorMagic, software that lets you monitor servers, workstations, and SNMP devices locally or over the Internet. The software provides database logging and support for SQL Server, Microsoft Access, and all ODBC-compliant databases. You can view and graphically analyze real-time performance and historical data. Pricing starts at $569. Contact Tools4ever at 516-482-4414.
http://www.tools4ever.com

7. CONTACT US

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

SQL Server Magazine UPDATE is brought to you by SQL ServerMagazine, 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

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