Voice of the DBA

Author: way0utwest

  • Are There That Many GUIDs?

    Do a lot of people actually use GUIDs as Primary Keys? I haven’t used them much, and I would have thought that more people chose identity keys. It seems that most of the demos and examples I see from bloggers and speakers are constantly using identities.

    However an informal survey from Peter Bromberg showed that four times as many people actually had GUIDs as their primary keys. The blog actually says that GUIDs are not a good choice, but I’m not sure I agree with that. You can use sequential GUIDs, and you can avoid making them the clustered key, so I think they can work as well as anything.

    There’s nothing inherently wrong with GUIDs, and they should be unique across all of your rows. There have been some reported cases of duplicates, but for most practical purposes, especially in database work, you ought to be able to count on a GUID as unique. They even have the nice capability of being generated by clients, removing the need for an extra round trip when a client needs to insert multiple rows.

    I typically don’t use them because they’re long, hard to remember and type, and hard to view on the screen. I can’t easily compare rows in multiple tables, and it’s easier for me to work with integers.  I don’t recommend them, but if you are going to use them, be sure you understand the pros and cons, and use them appropriately.

    Steve Jones

  • Downtime

    Someone sent me a note recently asking about how to handle unexpected downtime. In an editorial I mentioned that a 7TB database isn’t that large, and this person told me that they had an issue with a 1TB database and running DBCC resulted in a 4 hours of unexpected downtime. The person asked if small businesses needed to accept downtime for serious maintenance.

    I think that it’s rare you run into DBCC issues that require downtime, but when you do, then that downtime is needed. I’m not sure what else you can do since DBCC repair issues often indicate some fundamental hardware issue. I suppose that you could move to new hardware, but you still need to ensure that your database is intact and working.

    A restore from backup might be quicker in these situations, but that will requires some downtime, and it’s something that you have to live with. We still have systems that will fail and need rebuilding, and probably always will.

    I know some companies can afford redundant hardware and failover to secondary systems when they need to check their primary servers. A second copy of the database may or may not allow you to continue to function if you had corrupt pages on your primary, depending on how the data is moved over.

    Most of us, however, just have to live with downtime. If anyone has other ideas or stories on dealing with unexpected maintenance downtime, I would be interested in hearing them in the discussion below.

    Steve Jones

  • Data Portability

    It seems that there is a never ending list of formats that data professionals need to deal with. As I read through posts and articles talking about Integration Services, I’m struck by how many unique solutions people come up with to move data in and out of Excel. They know how to handle multiple worksheets, headers, practically anything that people put into Excel. If there’s a pattern in there, chances are some SSIS worker has a solution.

    This week I saw that Facebook had announced a data portability initiative, giving users the ability to download all their Facebook data into one file. The file can include images, movies, posts, events, etc. and gives you a way to not only back up your data, but perhaps use it for other applications. I’m sure it’s only a matter of time before we have an SSIS package that can load up your information into a SQL Server database. I suspect someone will do it just for the challenge, and not because they have a practical use for it.

    Facebook seems to have been under attack constantly from groups that are concerned about the privacy policies and features of a company that has so much data about individuals. However this new feature seems to show that the company isn’t looking to lock you and your data into their own system. You can now backup your data and shut down your account if you with to move on to another service.

    Recognizing that data is important to users and allowing some level of portability is a feature that I wish more companies would embrace. I’d love to be able to easily move my medical records, my financial investment history, or even my credit data to my own systems as a backup. Or even as a way to document the current state and perhaps ensure that my data is correct.

    Steve Jones

  • Return Values from XP_CMDSHELL

    Let’s be clear: I don’t recommend the use of xp_cmdshell as a general tool. It ought to be used when you have no alternatives, and you should carefully control access and what this can do. Opening a shell from SQL Server can be dangerous for your server.

    That being said, I do think there are places that xp_cmdshell works great and if you need to use it, how do you tell if the commands you executed were successful?

    For those of you that have worked in command lines, or remember DOS, there is a thing called an Errorlevel, that is returned by programs when they exit. For many of us that used to program in DOS, we always checked ERRORLEVEL when exiting a program, and we made sure that our program always returned a 0 if it exited without an error. That’s a standard that has been in place as long as I can remember working with computers.

    How can we use this? If I execute this in a command window:

    errorlevel

    I get an instance of Notepad on my screen. When the CD.exe application completes, it sets the errorlevel to 0. Technically it returns a 0 to cmd.exe, which sets the errorlevel to that value.

    What about this:

    errorlevel2

    In this case, nothing happens. However if I switch the command to:

    errorlevel3

    Then I get notepad again.

    How can we use this in SQL Server? When xp_cmdshell exits, it returns the errorlevel from it’s shell. Let’s test it. First, enable xp_cmdshell

    -- To allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1
    GO
    -- To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    -- To enable the feature.
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    -- To update the currently configured value for this feature.
    RECONFIGURE
    GO

    Now execute

    DECLARE @i INT
    EXEC     @i = xp_cmdshell 'dir'
    SELECT @i

    This returns the current directory (\Windows\System32 in this case) and a 0.

    errorlevel4

    Now if we change to our other example:

    DECLARE @i INT
    EXEC     @i = xp_cmdshell 'cd tim'
    SELECT @i

    We get these results

    errorlevel5

    Since that’s not a valid path on my system, the DOS error is returned as a result, and the errorlevel is returned in the variable assignment.

    You can use this to test and see if your commands execute before you do something, like try to parse a directory listing that isn’t there.