SQL Server Magazine UDPATE, October 30, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections

THIS ISSUE SPONSORED BY

2004 Date Announced for SQL Server Magazine Connections

Get High-Speed Access to Article Archives
(below COMMENTARY)

FREE SQL Application Performance eBook from VERITAS!
(below NEWS AND VIEWS)

SPONSOR: 2004 DATE ANNOUNCED FOR SQL SERVER MAGAZINE CONNECTIONS

Spring 2004 SQL Server Magazine Connections will be held April 18-21 at the Hyatt Grand Cypress in Orlando, Florida. Register early and receive free access to Microsoft ASP.NET Connections and Visual Studio Connections.
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0ggP0AX

October 30, 2003—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. ANNOUNCEMENTS

4. RESOURCES

5. HOT RELEASES (ADVERTISEMENTS)

6. NEW AND IMPROVED

7. CONTACT US

See this section for a list of ways to contact us.

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

As you've read in this column before, I have two major complaints about Microsoft's certification programs. First, the certification process doesn't guarantee high-level expertise. Second, Microsoft certifications are too generalized. Having one process that simultaneously attempts to certify many people and demonstrate a high level of expertise is impossible. For example, Microsoft has only one MCDBA certification, and it doesn't differentiate between competencies in development, administration, or business intelligence (BI) areas. Yet, those areas require completely different skill sets.

The marketing benefits of Microsoft (or any vendor) having many certified professionals are clear. You can spin large numbers of certified professionals to indicate significant market penetration, a reliable workforce, industry excitement, or almost any other message. Unfortunately, I believe Microsoft's certification process will continue to be seen as a marketing exercise until the company invests in building a certification process that differentiates between competence and expertise.

However, Microsoft seems to be making progress in specializing its certification programs. In August, the company announced new specializations (http://www.microsoft.com/traincert/highlights/windows2003.asp) that apply to the Microsoft Certified Systems Administrator (MCSA) and Microsoft Certified Systems Engineer (MCSE) tracks. Each track has two new specializations that focus on messaging and security. For example, the MCSE message specialization focuses on designing, planning, and implementing messaging infrastructure based on Microsoft Exchange Server. The MCSE security specialization focuses on building secure computing environments on the Windows platform. Each specialization track claims that candidates successfully passing the tests would have at least one year's experience in the specialty, which doesn't necessarily equate to true expertise. Still, having a 2-year-old and a 6-month-old at home, I recognize that everyone starts with baby steps.

I've also run across some interesting information about database certifications. You might have seen the database certification poll that Microsoft is running through SurveySite at http://web.survey-poll.com/email/2432mtb7.html . I encourage you to respond to the survey. Microsoft plans to use the information it gathers from this survey to redesign the certification process for SQL Server. I don't have the right to complain about Microsoft's certification programs unless I'm willing to help improve them, so I responded to the survey—and so should you. The URL will be active only until November 7, so don't procrastinate!

The certification process will never be credible or reflect true expertise as long as Microsoft continues to market a one-size-fits-all skill level. Although Microsoft seems to be making progress in the area of specialization, it isn't emphasizing expertise enough. Being an expert implies a skill level beyond average, which means that, by definition, most people aren't experts. Perhaps the company should offer different tests and tracks geared toward intermediate-level and expert-level certifications. As a starting point, I'd like to see Microsoft make the test score part of your certification record, then base your certification level on that score. I don't have all the answers, but the current system doesn't provide a credible way of testing advanced levels of expertise.

Let me know what you think. Does Microsoft need better certification programs? What concrete steps would you like to see Microsoft take? Share them with me—and with Microsoft.

GET HIGH-SPEED ACCESS TO ARTICLE ARCHIVES

The SQL Server Magazine Master CD provides useful SQL Server information in realtime. By becoming a subscriber today, you will have high-speed access to all articles, code, and expertise published in SQL Server Magazine and T-SQL Solutions. In addition, you can find fast answers to your SQL Server questions by searching for keyword, subject, author, or issue. Let this helpful resource save you time. Subscribe today!
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0BDUB0AW

2. SQL SERVER NEWS AND VIEWS

Microsoft developers looked ahead to Yukon, Whidbey, and Longhorn at a packed Microsoft Professional Developers Conference (PDC) 2003 in Los Angeles this week that featured the release of early code bits and standing-room-only sessions. California wildfires played with flight schedules to PDC, but they didn't stop an estimated 7,000 developers from flooding the Los Angeles Convention Center to learn about the next releases of SQL Server (Yukon), Visual Studio .NET (Whidbey), Windows (Longhorn), Web services (Indigo), and other upcoming technologies.

Longhorn took center stage in Microsoft's first PDC in 2 years, with the long-anticipated Windows File System (WinFS) positioned as the key to increased user productivity and ease of use in the next Windows release, expected in 2006. At his opening keynote Monday morning, Microsoft Chief Software Architect Bill Gates highlighted WinFS, a unified storage system based on XML and relational database technology that the SQL Server team has developed. "Some of you here have heard me talk about unified storage for more than a decade," Gates noted. "The idea of taking the XML flexibility, database technology, getting it into the file system—that's been a Holy Grail for me for quite some time. And here it is. ... Thank goodness we have the evolution around XML and user interface capabilities so that this can all come together." Here are some other tidbits from PDC.

Yukon:
Microsoft Vice President for SQL Server Gordon Mangione said that about 2000 people had the private Yukon beta before PDC. PDC attendees received slightly different Yukon code bits, with most of the changes reportedly to help integrate with the Whidbey PDC code bits. (According to Microsoft, the PDC bits are available to MSDN subscribers upon request at no charge and will be shipped to them.) Mangione cited three areas of Yukon focus: enterprise data management, developer productivity, and business intelligence (BI). He said that the crucial Whidbey integration built into Yukon would give data developers the long-overdue tools they need. PDC presenters spoke of Yukon and Whidbey as being "joined at the hip," with Microsoft expecting to synchronize the release of both products. Although Microsoft has been pointing to the end of 2004 for Yukon and Whidbey availability, rumors of slipping deadlines circulated at PDC. Mangione also told SQL Server developers that with Yukon, they wouldn't need to—and shouldn't—write extended stored procedures; instead they should use managed code.

Sessions about the new SQL Server Reporting Services product, which is in beta 2 and scheduled for release before the end of the year, overflowed into hallways. Reporting Services lets you define a report once and render it in a variety of ways, blending traditional production reporting and interactive reporting into one platform for relational, hierarchical, and multidimensional reporting needs. Because of customer demand for the product, Microsoft is releasing the first version as a SQL Server 2000 add-on, but it will build the service into the Yukon release. The Yukon version of Reporting Services will also include functionality that you can embed into WinForms and other clients.

Longhorn:
Longhorn will be the biggest Windows release since Windows 95 and the biggest Windows release this decade, according to Gates. Longhorn features three pillars: Presentation (Avalon), Data (featuring WinFS), and Communication (Indigo Web services). Unlike today, where data is trapped in applications, relationships are buried, and shell views are tied to your folder hierarchy, Longhorn's WinFS will provide extensible XML schema, logical views, programmable relationships, synchronization services, and information agents to pull your data together. In addition, Longhorn will usher in a new well-structured programming framework called WinFX, which will introduce XAML, a new declarative markup language for Windows that will separate application code and content.

Whidbey:
The next release of Visual Studio .NET will provide a comprehensive and complete development environment that includes deep support for and integration with the data tier. Whidbey is where rapid application development (RAD) meets the database, featuring seamless cross-language debugging and deployment, procedural logic, deep Common Language Runtime (CLR) integration into the SQL Server engine, high performance, and component reuse. Whidbey will introduce flexible and productive data source controls, which will handle your ADO.NET code under the covers, and data bound controls. Whidbey will also introduce SQL paging functionality to walk through large data sets.

Application Security discovered that a stack-overflow vulnerability in DB2 Universal Database 7.2 for Windows can result in the execution of arbitrary code on the vulnerable server. To exploit this vulnerability, an attacker can issue a carefully crafted Invoke command. IBM has released Fixpak 10a for DB2 7.2 to fix this vulnerability.
http://secadministrator.com/articles/index.cfm?articleid=40647

Sponsored by EMC

The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you think Microsoft's 'Secure the Perimeter' strategy will significantly reduce the company's security problems?" Here are the results (+/- 1 percent) from the 177 votes:

The next Instant Poll question is "Do you subscribe to the Microsoft Developer Network (MSDN)?" Go to the SQL Server Magazine Web site and vote for 1) Yes, and I find it valuable, 2) Yes, but I don't find it valuable, 3) No, but I plan to, or 4) No, and I don't plan to.
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0KQO0Ag

SPONSOR: FREE SQL APPLICATION PERFORMANCE EBOOK FROM VERITAS!

Get inside your Application Performance. Download the FREE eBook: The Definitive Guide(tm) to SQL Server Performance Optimization, full of useful techniques and best practices based on real world challenges you face every day! Plus, learn how you can guarantee the performance of your SQL Server-based applications with i3 for SQL Server solutions from VERITAS Software! Go to:
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0BDUC0AXd=4048

3. ANNOUNCEMENTS

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

Visit the SQL Server Magazine Web site and take advantage of the search box and navigation toolbar to access new articles, active forums, archived articles, associated code, and more! The site features columns by experts such as Brian Moran and Itzik Ben-Gan. Click here to visit this helpful SQL Server resource:
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0KQO0Ag

Use the Database Performance Portal to stress test your ADO-accessible client/server database servers. This how-to guide walks you through the process of building and executing a new Database Portal workload simulation. Inquire today:
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ07hf0A7

4. RESOURCES

You've heard about the new ability in the upcoming Yukon release of SQL Server to develop programmable objects through Common Language Runtime (CLR)-based languages such as Visual Basic .NET and C#. You might have thought that as a result, Microsoft would put less effort into enhancing T-SQL, but that's far from the case. Microsoft has improved many aspects of T-SQL in Yukon, providing long-awaited responses to programmers' needs and requests. Read Itzik Ben-Gan's November article, "What's New in Yukon T-SQL," to see how you'll be able to write less code to achieve the same results and get better-performing solutions.
http://www.sqlmag.com/articles/index.cfm?articleid=40206

Dav_A has a mystery on his hands: A query that shouldn't return a result is returning a Data Set. Dav_A has a main query, then a subquery within brackets. When he runs the inner query separately, it doesn't return a result. He can bypass the problem by removing the DISTINCT keyword in the query and by changing the order of the JOIN statements, but he'd like to get to the bottom of the problem. See the full query, read what other people have said, and offer your suggestions on SQL Server Magazine's Development Forum at
http://www.sqlmag.com/forums/messageview.cfm?catid=9&threadid=19966

(contributed by Brian Moran, [email protected])

The internal structure of a nonclustered index in SQL Server varies based on whether the table also has a clustered index. Tables that have a clustered index are clustered tables, and tables that don't have a clustered index are heap tables. Nonclustered indexes based on a heap table contain a row ID (RID) that lets SQL Server find a data page from the leaf level of the nonclustered index. This process generally improves performance because it dramatically reduces the overhead associated with maintaining a nonclustered index when you update data. (For more information about nonclustered indexes, see Kalen Delaney's articles "Time for a Tune-Up," August 2001, InstantDoc ID 21480, and "The Big Cover-Up," September 2001, InstantDoc ID 21729).

However, because the nonclustered index contains the clustering key, if you drop a clustered index on a table that has nonclustered indexes, SQL Server must also drop the nonclustered indexes and recreate them by using a RID rather than the clustering key as a record locator. SQL Server must drop and recreate the nonclustered index once again if you add back a clustered index because the nonclustered index will need to shift from using a RID back to using the clustering key. This dropping-and-recreating process can take a tremendous amount of time and I/O and CPU resources when you're dealing with large tables.

Changing your clustered indexes by using the CREATE INDEX statement's DROP_EXISTING clause is faster. The DROP_EXISTING clause tells SQL Server that the existing clustered index is being dropped but that a new one will be added in its place, letting SQL Server defer updating the nonclustered index until the new clustered index is in place. (Note that you can use DBCC DBREINDEX to rebuild existing indexes because it won't cause SQL Server to rebuild a nonclustered index. But you can't use DBCC DBREINDEX to change the columns in an existing index.) With DROP_EXISTING, you save one complete cycle of dropping and recreating nonclustered indexes. Additionally, SQL Server won't rebuild the nonclustered index at all if the clustered index key doesn't change and is defined as UNIQUE, which isn't an obvious performance benefit of defining a clustered index as UNIQUE. Using the DROP_EXISTING clause can be a huge time-saver when you need to change the clustered index on a table that also has nonclustered indexes.

Send technical questions to [email protected].

5. HOT RELEASES (ADVERTISEMENTS)

Learn how you can monitor and audit database activity without the use of triggers and application modification. Automatically capture changes in permissions, schema and data mods. Free white paper at:
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0BCWh0AB

Subscribe to SQL Server Magazine and receive 12 issues of valued SQL Server content. In addition, you will receive access to the Web article archive and get the newest SQL Server 2000 System Table Map Poster. Subscribe today!
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0BDBs0A1

6. NEW AND IMPROVED

(contributed by Dawn Cyr, [email protected])

Lumigent announced Log Explorer 4.0, software that provides access to transaction-log information so that you can solve application problems and recover or salvage data. The software lets you locate changes in database transactions; selectively recover modified, deleted, dropped, or truncated data; and export data for analysis and reporting. Enhancements in the latest release include a new alerting functionality, which notifies you when a deadlock or rollback occurs; new user-based filtering options, which let you filter information by Windows workstation username and SQL Server login name or by client host name and SQL Server application name; a new "Restoring Tables from Backup" functionality, which lets you quickly find a dropped table transaction; and new undo and redo commands for stored procedures, triggers, views, and functions. Log Explorer 4.0 supports SQL Server 2000 and 7.0. Pricing starts at $1400. For more information, contact Lumigent at 866-586-4436 or
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0BC4c0AV

VERITAS Software announced VERITAS i3 for SQL Server, application performance management (APM) software that lets IT departments offer their service as a utility. In utility computing, IT departments manage applications at agreed-upon levels of availability and performance and adapt to changing business demands. VERITAS i3 for SQL Server lets application managers monitor transactions in realtime so that they can quickly detect, diagnose, and correct application-performance problems. The software, which works with SQL Server 2000, supports business applications that run on SQL Server, including ERP applications such as SAP and PeopleSoft. For pricing and information, contact VERITAS Software at 650-527-8000, [email protected], or
http://lists.sqlmag.com/cgi-bin3/DM/y/edQx0FgQMn0BRZ0KaT0A2

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