SQL Server Magazine UPDATE, August 12, 2004--SQL Server Profiler

To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This Issue Sponsored By

AppDev Training

Win a Harley at SQL Server Magazine Connections

1. SQL Server Perspectives

  • SQL Server Profiler: Bringing Teams Together
  • 2. News and Views

  • Results of Previous Instant Poll: SA Rights
  • New Instant Poll: Position Level
  • 3. Reader Challenge

  • Winners of the August Reader Challenge: A Bulk-Copy Procedure
  • September Reader Challenge: Foreign Punctuation
  • 4. Announcements

  • Enter the 2004 SQL Server Magazine Innovator Awards
  • Online Resource for SQL Server Users
  • 5. Resources

  • What's New in SQL Server Magazine: Back Doors with a View
  • Hot Thread: Shrinking a Database
  • Tip: Store Integers as Decimal Data Type
  • 6. Events Central

  • New SQL Server Web Seminar: Performance Breakthroughs
  • 7. New and Improved

  • Add Enhancements to Your Analytic Platform
  • Solve Database-Migration Problems

  • Sponsor: AppDev Training
    Free SQL Server 2000 Training! Become a more efficient, effective developer with AppDev's award-winning self-study SQL Server training! See AppDev quality for yourself--download 3 hours of training immediately or request a CD-ROM by mail (each a $95 value) FREE! Click the link below for your FREE SQL Server 2000 Training!

    1. SQL Server Perspectives

  • SQL Server Profiler: Bringing Teams Together

  • by Brian Moran, [email protected]

    I've been writing about SQL Server Profiler frequently over the past few weeks, so you're probably aware that I think Profiler is a great tool that's under-utilized by development and DBA teams. Several readers have sent me interesting ideas about integrating Profiler into the development environment, so this week I want to share two of those ideas with all of you.

    The first suggestion comes from a reader who is a DBA for a busy eBusiness test-and-production environment in which granting sa privileges to developers isn't permitted because the organization's management team isn't willing to take the risk. The reader writes, "Granting developers sa privileges on their own server, as you suggested, is a valid solution, but it's not cost effective for us. So, I'd like to suggest a compromise that's worked for us. We run a Profiler trace all day, every day, during business hours. Profiler stores the data in a table that anyone can view. This lets everyone see the high CPU/read transactions that occur throughout the day. Of course, events, data columns, and filters for tracing should be decided upon within each environment. Our trace is run in both production and test environments. When a specific stored procedure requires further traces, a developer asks a DBA to start another trace that specifies that stored procedure--also storing the results in a table for easy querying."

    In "SQL Server Profiler: For Developers, Too" ( http://www.winnetmag.com/article/articleid/43268/43268.html ), I argued that Profiler should become an integral part of the development and unit-testing process. I still hold that the risks involved with granting developers sa access are eclipsed by the benefits of giving developers access to trace data. However, this reader's solution is a reasonable compromise for shops that can't, or won't, grant sa access to developers.

    Another interesting idea comes from a reader who writes, "In my company, we go out of our way to blur the lines between development, administration, and support. All our development leads have full administrative access to both our production and development environments, with the understanding that developers have as much responsibility for the well being of those systems as the administrators. Proper communication and coordination between everyone on the team brings many benefits to the table, including more robust applications, better testing scenarios, quicker problem resolution, improved relationships between development and administration, and a deeper resource pool to pull from when major support problems arise. We also require all developers to directly support any application they develop for 6 weeks after moving the application to production. When you have to clean up your own messes and interact with the users affected by those messes, accountability and responsibility improve. Creating silos of authority only leads to silos of communication, and I would rather risk a developer overstepping his bounds with a production system than consistently rolling out applications that under-perform--or don't perform at all--because a developer lacks knowledge about how users implement the applications they develop."

    I love this idea! I completely agree that blurring the lines between traditional support, development, quality assurance, and administration roles is bound to create a better end product. And I suspect that developers become more vigilant when they know they're going to eat their own dog food in production for 6 weeks.

    On a different note, in "In the Express Lane with SQL Server" (http://www.winnetmag.com/article/articleid/43234/43234.html ), I told you about SQL Server 2005 Express Technical Preview. Until a product manager at Microsoft pointed it out, I didn't realize that the Express Technical Preview came out 2 weeks before SQL Server 2005 Beta 2 was released to Microsoft Developer Network (MSDN) subscribers. Now, SQL Server 2005 Express Technical Preview is no longer available--there's only Express Beta 2. The bits of the two versions are different, but the Microsoft Web site doesn't spell that out clearly. If you have the SQL Server 2005 Express Technical Preview, you should uninstall, and download Express Beta 2. Microsoft doesn't support a direct upgrade from Technical Preview to Beta 2.

    Win a Harley at SQL Server Magazine Connections
    SQL Server Magazine Connections conference is coming to Las Vegas, Nevada, November 7 - 10. With more than 45 sessions covering data warehousing and business intelligence, database development, database administration, and more, this is the premier SQL Server event! Don't miss the insightful opening keynote "The Evolution of SQL Server" by Microsoft's Thomas Rizzo. Registration includes an exclusive day of presentations about SQL Server 2005 (formerly code-named Yukon) by the Microsoft SQL Server 2005 development team including sessions about T-SQL, Common Language Runtime (CLR) integration, security enhancements, management tools, best practices, and more. Attendees will receive the latest build of Visual Studio 2005, a beta copy of SQL Server 2005, and a chance to win a Harley Davidson motorcycle. Get the complete conference brochure online or call 800-438-6720 or 203-268-3204.

    2. News and Views

  • Results of Previous Instant Poll: SA Rights
  • The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you grant sa rights to your developers?" Here are the results (+/- 1 percent) from the 293 votes:

  • 24% Yes
  • 32% Yes, but only under controlled circumstances
  •  3% No, but I'll consider it
  • 41% No, and I don't plan to
  • New Instant Poll: Position Level
  • The next Instant Poll question is "What level is your position?" Go to the SQL Server Magazine Web site and vote for 1) management level, 2) senior level, 3) mid-level, 4) entry level, or 5) other.

    3. Reader Challenge

  • Winners of the August Reader Challenge: A Bulk-Copy Procedure

  • contributed by Umachandar Jayachandran, [email protected]

    Congratulations to Alejandro Mesa, a database programmer for Simplex Medical in Fort Myers, Florida, and Marcos Kirchner, student at the University of Blumenau (FURB) in Santa Catarina, Brazil. Alejandro won first prize of $100 for the best solution to the August Reader Challenge, "A Bulk-Copy Procedure." Marcos won second prize of $50. You can find a recap of the problem and the solution to the August Reader Challenge at

  • September Reader Challenge: Foreign Punctuation

  • Now, test your SQL Server savvy in the September Reader Challenge, "Foreign Punctuation" (below). Submit your solution in an email message to [email protected] by August 19. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

    Here's the challenge: David is a database programmer for an advertising company that deals with international data. The database that holds data for each customer contains a table that stores names in Unicode format. The following script contains the table and sample data:

    CREATE TABLE #Temp ( nc nvarchar(30) COLLATE Latin1_General_CI_AS )
       CREATE CLUSTERED INDEX Idx_Temp_nc on #Temp( nc )
       INSERT INTO #Temp VALUES( N'MRKT' + nchar(0x3000) + N'Name')
       INSERT INTO #Temp VALUES( N'TEST String' )
          nchar(0x3000) + N'Name' + nchar(0x3000) )

    In the sample data, the Unicode character 0x3000 represents a punctuation symbol in different languages, such as Chinese. When David writes a query using an equality search condition, he notices that the query doesn't work as expected. For example, the following query returns two rows instead of one:

     SELECT * FROM #Temp WHERE nc = N'MRKT' +
          nchar(0x3000) + N'Name' + nchar(0x3000)

    What is incorrect in the query? And how can David modify the query to return the correct data without ignoring punctuation or white-space characters in any language?

    4. Announcements

  • Enter the 2004 SQL Server Magazine Innovator Awards

  • This year's competition is calling for a cutting-edge SQL Server solution that solves a real-world problem and/or improves performance. One grand-prize winner will win a free trip to the 2004 SQL Server Magazine Connections Conference in Las Vegas, Nevada. Fill out an entry form today to get the recognition you deserve! Deadline is September 1.

  • Online Resource for SQL Server Users
  • Visit the SQL Server Magazine Web site and experience a resource that offers the easy-to-find SQL Server solutions, news, guidance, and how-to information you're looking for. Reference lists of active forums, hot-topic discussions, keyword searches, free Web seminars, FAQs, and much more. The site also features a Web-exclusive column by Itzik Ben-Gan. Check it out:

    5. Resources

  • What's New in SQL Server Magazine: Back Doors with a View

  • Back doors are undocumented features of an application that let you do things the application wasn't intended to support. In his August T-SQL Black Belt column, "Back Doors with a View," Itzik Ben-Gan concludes his series on T-SQL back doors by discussing three back doors to SQL Server views: rolling your own information_schema views, creating sorted updateable views, and circumventing update limitations that views impose. He also tells readers which undocumented techniques he thinks could be useful for T-SQL programmers if SQL Server supported them. Read this article today at

  • Hot Thread: Shrinking a Database
  • Newbie_SQL can't shrink some of his SQL Server 7.0 databases. Enterprise Manager shows the database size at 15931MB with 5073MB of space available. The properties of sysfiles read: size 2039144. Newbie_SQL wants to know why there's still space available after he's shrunk the file and the database. Offer your advice and see what other people have said on SQL Server Magazine's Administration forum at

  • Tip: Store Integers as Decimal Data Type

  • by Brian Moran, [email protected]

    Q. I need to store integers--account numbers and other IDs--that have 20 or more digits. Storage and retrieval of these integers needs to be accurate, with no rounding, so I can't use the float data type. I've seen recommendations to others in this situation to store the integers in a CHAR(20) field. But I don't want to store my number in a character field because I want them to remain numeric values. How can I store integers that have 20 or more digits as numeric values?

    A. I suggest using the decimal or numeric data type to store the integers. In SQL Server, the numeric and decimal data types are equivalent. Both data types let you store integers that have up to 38 digits. The syntax for defining a decimal data type is:

     DECIMAL\[(p\[, s\])\]

    The first number after the DECIMAL keyword is the precision setting, which defines the total number of digits the integer can have. The second number is the scale setting, which defines the total number of digits to the right of the decimal place...

    To read the complete answer to this question and download the code that shows how to store a 20-digit number as both a decimal data type and a character data type, go to

    6. Events Central

    For a complete guide to Web and live events, see

  • New SQL Server Web Seminar: Performance Breakthroughs

  • Meeting database-performance demands with larger amounts of data and more complex retrieval requirements is a constant challenge for SQL Server users. SQL Server Magazine invites you to attend a free, interactive Web seminar on September 16. Learn about the foundation of common issues and tactics for solving SQL Server performance dilemmas. Register today:

    7. New and Improved

    by Dawn Cyr, [email protected]

  • Add Enhancements to Your Analytic Platform

  • ProClarity announced the availability of several new analytic tools for the ProClarity Analytic Application Development Platform, a customizable development environment that lets organizations create sophisticated analytic applications. The new tools are add-ins available through the ProClarity Community, a Web-based group of developers, customers, and partners. Chief among the new add-ins is the Bubble-up Exception key performance indicator (KPI) template, which lets users visually analyze a hierarchy of information and identify potentially important variances. The Quadrant Analysis tool serves as a visual template for grouping data according to predefined parameters. The Cache Warmer improves query performance by "pre-loading" data views. The Usage Monitor add-in lets you comprehensively monitor your analytic applications. And new parameterization customization features for the ProClarity Analytic Server let you supply a specific value as a data slicer to get more personalized views of information. For more information, you can attend the ProClarity Web seminar "ProClarity Accessories: Extending the Value of the ProClarity Platform" on Tuesday, August 24 at 10:00 a.m. Mountain time; or contact ProClarity at 208-344-1630.

  • Solve Database-Migration Problems

  • WhiteTown Software announced WhiteTown Wizards, applications that convert DBF files to Microsoft Access, SQL, XML, Microsoft Excel, HTML, or comma-separated value files. To convert outdated DBF files, you simply select the source file you want to convert, and the wizard interface guides you through the steps of converting the file to the format you need. The wizards keep the original database structure, tables, and headers whenever possible. You can use the wizards to Web-enable your databases by converting them to HTML. And a special application is available for converting older, outdated DBF files to new DBFs while fixing their structure. WhiteTown Wizards are available individually for download. Each converter costs $19.95 for a single license. For more information, contact WhiteTown software by voice mail at 38-067-9105301, by fax at 206-984-3919, or at [email protected]

    SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

    Contact Us:

    Manage Your Account

    You are subscribed as #EmailAddr#.

    To unsubscribe from this email newsletter, send an email message to mailto:#Mailing:UnsubEmail#.

    To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.

    Copyright 2004, Penton Media, Inc.

    Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.