SQL Server Magazine UPDATE, June 23, 2005--GUIDs As Primary Keys

Subscribe to SQL Server Magazine:

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
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

FREE SQL Server Performance Monitor From Idera

FREE .NET Training CD-ROM!

Double-Take From NSI Software

June 23, 2005

1. Perspectives

  • Performance Effects Of Using GUIDs as Primary Keys
  • 2. SQL Server 2005 Watch

  • Beyond SQLXML
  • 3. News & Views

  • Have You Created an Innovative SQL Server Solution?
  • Results of Previous Instant Poll: Benchmarks and TPC-C Scores
  • New Instant Poll: SQL Server Magazine
  • 4. Events and Resources

  • Celebrate the Launch of SQL Server 2005 Nov 7-10, 2005 at Mandalay Bay in Las Vegas
  • Microsoft SQL Sever 2005--For Every Tier Of Your Business Intelligence Application
  • Token Authentication: Getting It Right
  • Back By Popular Demand--SQL Server 2005 Roadshow in a City Near You
  • 5. Featured White Paper

  • Converting a Microsoft Access Application to Oracle HTML DB
  • 6. Peer to Peer

  • Hot Tip: Differential Backups Protect and Simplify
  • Hot Article: Maintaining Partitions
  • In a Nutshell: AppSec, Security, and White Papers
  • Hot Threads
  • 7. Announcements

  • SQL Server Performance Tips, Articles, & Forums
  • Check out the SQL Server Magazine Master CD
  • 8. New & Improved

  • Create Dashboards for Reporting Services
  • Assess and Manage Database Risks
  • Standardize Enterprise Configuration to Reduce Risk
  • Use Unified Modeling Language to Develop Applications
  • Sponsor: Free SQL Server Performance Monitor From Idera
    Do you know what's going on with your SQL Servers right now? Are you sure they are running at optimum performance? Idera's FREE SQLcheck will give you real-time performance monitoring for SQL Server, the hardware, and operating system. Run it on demand or as a secure screensaver--you'll have critical SQL diagnostic info at your fingertips. Start managing SQL Server more effectively today! Download Idera's SQLcheck now!

    1. Perspectives

  • Performance Effects Of Using GUIDs as Primary Keys

  •     by Brian Moran, [email protected]

    I get asked time and again whether globally unique identifiers (GUIDs) are good candidates for non-clustered primary keys. Is there a performance difference between using GUIDs and integer-identity columns as non-clustered primary keys? I have readers who've had no problems using GUIDs as primary keys in tables that have millions of rows. I know that I ask for trouble by attempting a short answer to a question that has many facets, but I'll jump in with both feet and say that I avoid using GUIDs as primary keys and tell my customers to avoid it as well.

    The most compelling reason to use a GUID as a primary key is that it's extremely simple from a programming and design perspective. Simple is often the best answer, but I don't think so in this case. We'll ignore how frustrating it can be to work with a GUID when you're debugging and writing code. And I won't discuss the relative value of a surrogate key that has some meaning and can be exposed to a user, because I don't want to broach two heavily controversial topics in a single week, so let's pretend I didn't even bring it up. I'm a performance-tuning geek at heart, so I'll focus on that aspect.

    Compared to integers, GUIDs are big, fat data types that have several performance characteristics that make them sub-optimal for indexes. To avoid delving too deep here, I'd like to point you to Jimmy Nillson's article "The Cost of GUIDs as Primary Keys" at http://lists.sqlmag.com/t?ctl=CFBD:7B3DB . This article points out some significant performance penalties that you might experience when creating indexes on a GUID. The article also discusses the pros and cons and offers helpful suggestions for creating an alternative surrogate-key strategy, based in part on GUIDs, that doesn't suffer the same performance penalties you get when you use a native GUID.

    I'll also point you to Gert Drapers's published resources about this topic. Drapers is a long-time member of Microsoft's SQL Server team and is one of the most knowledgeable SQL Server professionals in the world. Drapers publishes some material on his own Web site ( http://lists.sqlmag.com/t?ctl=CFC9:7B3DB ) rather than as an official Microsoft resource. I usually avoid recommending code like this, but I make an exception for Drapers because his material is always top notch. Drapers's set of procedures and functions, XPGUID, provides GUID-related procedures to "improve the usage of GUIDs inside SQL Server 2000." In addition, the code on the site provides a procedure for generating sequential GUIDs and a procedure for testing whether a string represents a valid GUID without generating an error that will abort your T-SQL batch. I hope you find the material useful. Of course, as with any untried resource, you should never use third-party resources on your production systems.

    SQL Server 2005 will make this whole question easier to answer. I've never been a big fan of the tools that Microsoft provides for automatically creating surrogate keys for the following reason: I want something with decent performance--the IDENTITY() function for example--but with the ease of use that a GUID provides. SQL Server 2005 addresses many of the current performance problems associated with using a GUID-based index by adding the newsequentialid() function. Newsequentialid() addresses some of the performance problems inherent in the current implementation of newid() by generating a GUID that will sort in an increasing order, making it more suitable for indexing. I haven't had a chance to do performance testing with this function, but I hope that it will help us resolve the question about whether to use GUIDs as primary keys.

    FREE .NET Training CD-ROM!
    Become more efficient and effective with AppDev's award-winning training. Get a FREE training CD ($95 value) and see why developers have voted AppDev THE BEST! Choose from VB.NET, ASP.NET 2.0, ASP.NET, C# .NET, and SQL Server. Get your FREE CD at

    2. SQL Server 2005 Watch

    Beyond SQLXML
        by Matt Nunn, [email protected]

    The native XML data type in SQL Server 2005 lets you combine XML functionality and relational-data storage in the systems you build so that your applications can work with data that might change frequently or vary in type. Another XML-related SQL Server 2005 feature that adds flexibility is native support for Web services, which lets DBAs access SQL Server by using SOAP through HTTP. This connection method enables interoperability with a variety of platforms--a great benefit to any SQL Server DBA working in a heterogeneous environment and supporting users on Linux or UNIX platforms. Web services support also simplifies development of client applications that connect to SQL Server through Visual Studio .NET or other smart IDEs. And using SOAP/HTTP connectivity also simplifies development of applications for mobile devices. For Microsoft's SQL Server development team, enabling Web services for SQL Server 2005 was important enough to warrant native support for the feature. Learn how you can use Web services support to improve interoperability and application connectivity. Read this article today at

    Sponsor: Double-Take From NSI Software
    Webinar: Protect Your MS-SQL Servers: Complete protection of SQL Server requires a high-availability solution as well as a disaster-recovery plan. Please join us for this Webinar, which will identify the business needs and challenges facing companies today in a SQL Server environment. We will uncover the latest trends in data protection for SQL Server to help ensure your business is protected at all times. Additionally, we will share some tips on enhanced backup and improving business continuity.

    3. News & Views

  • Have You Created an Innovative SQL Server Solution?

  • If so, you qualify to enter this awards program. SQL Server Magazine is organizing its 4th annual Innovator Awards Program. An awards committee, designated by SQL Server Magazine, will review all entries and select one grand-prize winner, 3 runners up, and honorable mentions if appropriate. The grand-prize winner will win a free trip to the 2005 SQL Server Magazine Connections Conference in Las Vegas, Nevada, a special trophy, and a write-up in the January 2006 issue. Contest ends August 1, so fill out an entry form today to get the recognition you deserve for your innovative solution. Click here:

    Results of Previous Instant Poll: Benchmarks and TPC-C Scores "Do you pay attention to public benchmarks and TPC-C scores?" Here are the results from the 48 votes (deviations from 100 are due to a rounding error):

  • 35%   Yes, I find them valuable
  • 15%   Yes, but I don't find them valuable
  •  2%   No, but I plan to
  • 48%   No, I don't think they're valuable
  • New Instant Poll: SQL Server Magazine
    "Do you subscribe to SQL Server Magazine?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=CFD0:7B3DB ) and submit your vote for

  • Yes
  • No, but I plan to
  • Not personally, but I read a pass-around copy at work
  • No
  • 4. Events and Resources

  • Celebrate the Launch of SQL Server 2005 Nov 7-10, 2005 at Mandalay Bay in Las Vegas

  • Microsoft will give every attendee a copy of SQL Server 2005 and Visual Studio 2005. SQL Server Magazine Connections is co-located with Microsoft ASP.NET & Visual Studio Connections.

  • Microsoft SQL Sever 2005--For Every Tier Of Your Business Intelligence Application

  • In this free Web seminar, you'll learn about the anatomy of business intelligence and how each portion maps to the SQL Server 2005 toolset. Plus, get all you need to know about Integration Services, the relational engine, Analysis Services, Reporting Services, and Notification Services, as well as sharing steps to integrate all components with third-party applications, Microsoft Office, and Microsoft SharePoint.

  • Token Authentication: Getting It Right

  • Perhaps you need tokens for management or mobile workers or your only applications that need token support are VPN, extranet access, or PC security. In this free Web seminar, join industry guru Randy Franklin Smith and learn how you can make a solid business case to management that justifies tokens. You'll also discover what the right combination of token devices and middleware can do. Plus, receive checklists of key evaluation and testing points for rollout time. Register now!

  • Back By Popular Demand--SQL Server 2005 Roadshow in a City Near You

  • Get the facts about migrating to SQL Server 2005. SQL Server experts will present real-world information about administration, development, and business intelligence to help you implement a best-practices migration to SQL Server 2005 and improve your database-computing environment. Attend and receive a 1-year membership to PASS and 1-year subscription to SQL Server Magazine. Register now!

  • See the complete Windows IT Pro Network guide to Web and live events.

  •     http://lists.sqlmag.com/t?ctl=CFC8:7B3DB

    5. Featured White Paper

  • Converting a Microsoft Access Application to Oracle HTML DB

  • Get the most efficient, scaleable, and secure approach to managing information using an Oracle Database with a Web application as the user interface. In this free white paper learn how you can use an Oracle HTML Database to convert a Microsoft Access application into a Web application that can be used by multiple users concurrently. You'll learn how to improve the original application by adding hit highlighting and an authorization scheme to provide access control to different types of users.

    6. Peer to Peer

  • Hot Tip: Differential Backups Protect and Simplify

  •     by Microsoft's SQL Server Development Team, [email protected]

    Let's say I have a Sunday full backup and Monday through Friday differential backups, and I restore to Monday's differential backup by restoring the Sunday full backup followed by a Monday differential. If some of the transaction log backups between Monday and Friday are corrupted or lost, something goes wrong on Saturday, and I need to restore to the Friday state, do I still need to do a full restore plus a Friday differential?

  • Read the answer to this question today at

  •     http://lists.sqlmag.com/t?ctl=CFB7:7B3DB

  • Hot Article: Maintaining Partitions

  • SQL Server 2005 introduces support for native table and index partitioning. Itzik Ben-Gan's first two articles in his T-SQL 2005 partitioning series covered creating partitions and querying metadata information related to partitions. In his third article in this series, "Maintaining Partitions," he shows you how to maintain partitioned tables and indexes and when you need to add or remove partitions. Read this article today and post your comments at

  • In a Nutshell: AppSec, Security, and White Papers

  • Kevin Kline recently discussed security issues with Karen Watterson, editor of Pinnacle's SQL Server Professional newsletter and author of what has become--for its volume and the sheer value of the information she posts--his favorite blog ( http://lists.sqlmag.com/t?ctl=CFCB:7B3DB ). Watterson told Kevin that AppSec briefed her about the company's new (and free) SOX and FISMA policies. AppSec also posted the white paper "Comprehensive Database Security Requires Native DBMS Features And Third-Party Tools," by Forrester's Noel Yuhanna on its site ( http://lists.sqlmag.com/t?ctl=CFAD:7B3DB ). In the white paper, Yuhanna points out that security is rapidly becoming more important to database vendors such as Microsoft. He gives an interesting deposition about the state of the industry with regard to databases and their security capabilities. He concludes (much to Kevin's surprise) that third-party tools can be a big help, especially when applied to specific security issues. SQL Server 2000 has, admittedly, weaker security than SQL Server 2005. However, Kevin's wondering what you think of the shift in the SQL Server security model? How much do you think it will help? Let Kevin know what you think today at

  • Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.

  •     http://lists.sqlmag.com/t?ctl=CFC0:7B3DB

    Administration: Backup Directory Is Invalid
    T-SQL: Question Related to Cursors
    Performance: Monitoring Stored Procedures
    Security: Data Scrubbing
    Reporting Services: Installing Reporting Services Error 1603
    Data Access: Converting Data Types In a View

    7. Announcements

  • SQL Server Performance Tips, Articles, & Forums

  • Get Hundreds of free tips and articles about SQL Server performance tuning and clustering. And get quick, accurate answers to your performance- and cluster-related questions in our forum. All from the SQL Server performance and clustering authority: SQL-Server-Performance.com.

  • Check out the SQL Server Magazine Master CD

  • The SQL Server Magazine Master CD goes a step further by offering portable access to the entire SQL Server Magazine article database--more than 2300 articles! The newest issue includes BONUS SQL Server 2005 content and if you sign up now, you'll SAVE 25%. This is a limited-time, risk-free offer, so subscribe now:

    8. New & Improved

        by Dawn Cyr, [email protected]

  • Share Your Story and Get a T-Shirt!

  • Have you used a product that saved you time or made your job easier? Tell us how your favorite product solved a SQL Server problem for you, and if we print your submission in the magazine's Hands On department we'll send you a SQL Server Magazine t-shirt. Send your email today to [email protected]!

  • Create Dashboards for Reporting Services

  • Infommersion announced Xcelsius XRS, software that lets you create enterprise dashboards for SQL Server 2000 Reporting Services. With the product's data-visualization capabilities, companies can take advantage of Reporting Services features such as full role-based security, server-side caching, and proactive notification. Using these features lets you deliver crucial business data in a secure, easy-to-read graphical format. Users can create forward-looking dashboards that use visual sliders and dials so that decision-makers can manipulate underlying assumptions about data to see how different choices will affect business performance. Xcelsius XRS integrates with common Microsoft desktop and server applications, including Microsoft SharePoint Portal Services and Microsoft Office. The product also offers Xcelsius' Dynamic Documents, a one-click integration of live, interactive dashboards and scorecards into Microsoft PowerPoint slides, PDF documents, the Internet, and corporate intranets. Xcelsius XRS will be available in August of 2005. For pricing, product availability, and other information, contact Infommersion at 858-552-6672 or [email protected]

  • Assess and Manage Database Risks

  • BindView announced that bv-Control for Microsoft SQL Server has been updated for the upcoming release of SQL Server 2005. The product's next release includes extended security configuration, access control, and audit capabilities to support the security improvements in SQL Server 2005. Bv-Control for Microsoft SQL Sever lets information security personnel, auditors, and database administrators assess and manage the risk to their database server environment by using non-intrusive scans. Security administrators can assess compliance with security best practices, determine who has effective access to SQL Server services, what users can do with that access, and what they have done. Administrators can use hundreds of predefined reports based on industry benchmarks and Microsoft security guidelines. Updated features in the next release of the product include separation of users and schema, execution context on programmable modules, and password policy for standard logins. For pricing, product availability, and other information, contact BindView at 713-561-4000 or [email protected]

  • Standardize Enterprise Configuration to Reduce Risk

  • Configuresoft announced Enterprise Configuration Manager (ECM) 4.7, software that centralizes and automates the tasks of monitoring, managing, and auditing the hardware and software configurations deployed in large enterprise networks or Web-server farms. By standardizing server and client configurations, ECM ensures operational compliance to regulatory, industry, and corporate standards throughout your infrastructure. The product collects configuration settings from every machine in an organization and puts them into a comprehensive configuration database (CCDB) to provide an enterprise view of the current state of operations. By gathering all available asset, security, and configuration data and centralizing it in a CCDG, IT managers can make sure that the policies they develop are appropriate for the IT infrastructure they have. In addition, the product enforces security policies by automatically resetting configurations to their predefined standards when an unauthorized change occurs. These capabilities help IT organizations keep their systems properly configured while ensuring security compliance with regulatory requirements such as HIPAA, GLBA, Sarbanes-Oxley, and FISMA. Pricing for Enterprise Configuration Manager 4.7 starts at $995 per server for Windows and Solaris licenses. For more information, contact Configuresoft at 719-447-4600 or [email protected]

  • Use Unified Modeling Language to Develop Applications

  • Altova announced UModel 2005, a software-development tool that lets programmers and project managers use Unified Modeling Language (UML) to visualize, specify, construct, and document object-oriented software systems. The product gives managers and developers who have code-writing backgrounds a UML tool that's easy to learn and use, visually attractive, and cost-effective. The product provides helpful design aids and concentrates on the UML diagrams that are most useful early in the development process and that yield immediate benefits to project team members, including the Use Case, Class, Object, Component, and Deployment diagrams. The easy-to-use visual interface helps level the UML learning curve so that developers who are new to software modeling can quickly use UML to enhance their productivity. Users can draw up an application strategy and generate standard Java source code from their plans or reverse engineer Java programs into accurate UML diagrams. Users can also amend source code or graphical models and customize code through user-defined templates. UModel-generated code is compatible with popular Java-development environments. Pricing for UModel 2005 is $259 for a single-user license, and a special half-price introductory offer is available for UModel purchases made by August 21, 2005. For other information, contact Altova at 978-816-1600 or [email protected]

    Contact Us

  • About the [email protected]
  • About technical questions--http://www.sqlmag.com/forums
  • About product [email protected]
  • About your [email protected]
  • About sponsoring SQL Server Magazine UPDATE--Richard Resnick, [email protected] or Lisa Kling, [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.

    View the SQL Server Magazine Privacy Policy

    SQL Server Magazine is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

    Copyright 2005, Penton Media, Inc. All Rights Reserved.

    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.