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
- More About Designing for Extreme Performance
2. SQL SERVER NEWS AND VIEWS
- Microsoft Seeks Beta Testers for 64-bit SQL Server 2000, SQL Server CE 2.0, and SQL Server 2000 SP3
- Privilege-Elevation Vulnerability in SQL Server and MSDE
- Results of Previous Instant Poll: Data Provider for Oracle
- New Instant Poll: Data Warehousing Hurdles
3. ANNOUNCEMENTS
- Need to Keep Your Servers Running 24/7?
- Worldwide SQL Server Users Group, www.sswug.org
4. HOT RELEASES (ADVERTISEMENTS)
- Free Trial -Lumigent Log Explorer 3.0
- NetOp Remote Control CrossTec
- Microsoft ASP.NET Connections
5. RESOURCES
- What's New in SQL Server Magazine: Overcoming OpenXML's Hangups
- Hot Thread: Index Tuning Wizard Recommendations
- Tip: Using CASE to Order a Result Set
6. NEW AND IMPROVED
- Connect VB and the .NET Languages to SQL Server
- Monitor Servers
7. CONTACT US
- See this section for a list of ways to 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 that process 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 Extreme Performance," 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
- assuming system demands will eventually be greater than you ever anticipated.
- attempting to design scalability into the system by letting the system scale out across multiple servers when possible.
- realizing that scaling out the Web farm is much easier than scaling out the database layer.
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 on the 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, the database 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."
- Consider breaking up a procedure and distributing it to the Web if the procedure is getting too big. What's too big? It depends on your environment.
- Consider moving processing to the Web if a) the move doesn't add additional round-trips to the server and b) the incremental cost in response time for a single user isn't significant.
- Consider moving logic to the Web tier if the procedure performs a lot of non-data-access processing (that is, something other than an INSERT, UPDATE, DELETE, or SELECT statement).
- Consider moving logic to the Web tier if the procedure is consuming huge amounts of CPU time and could be run by large numbers of users concurrently as the system scales. Database servers have four main hardware resources: disk, network, memory, and CPU. The first three are reasonably easy to scale out for an online transaction processing (OLTP) system. But scaling out CPUs quickly becomes impossible without buying a new server.
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 users will 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 programs
on 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 SQL Server 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 the beta 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
- 10% Yes—I'm already using it
- 29% Yes, but I haven't used it yet
- 61% No
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 availability and 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
Download free trial. Request free technical poster -"DTS Object Model".
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ04GJ0AU
NETOP REMOTE CONTROL CROSSTEC
NetOp, PC Magazine Editor's Choice, provides fast & secure remote support & management. Control PCs over the Internet, LANs or modems as if you were in front of them. Click to download a FREE evaluation!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ03VC0Ab
MICROSOFT ASP.NET CONNECTIONS
Microsoft ASP.NET Connections and VS.NET Connections will co-locate with SQL Server Magazine LIVE! this October. Early Bird discount expires soon -register today to save $2,990 and access all three
events for the price of one!
http://lists.sqlmag.com/cgi-bin3/flo?y=eNCr0COfyP0BRZ03kX0AJ
5. RESOURCES
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 reference expressions 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, so SQL 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:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
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
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
