Have you ever noticed unexpected gaps in sequences in IDENTITY columns?  Even though you’ve got transactions set up for your inserts and a no-deletion policy, you find that there are missing values in what should be an unbroken sequence of numbers.  The problem could be partially related to transaction rollbacks.

Conventional wisdom would lead one to believe that a rolled back transaction would undo the logged data changes to a table. While a rollback will remove the data rows included in a transaction, it does not reset the identity value to its previous setting.  We can see this demonstrated in a brief example.

First, let’s create a table with an identity column and insert some data:

CREATE TABLE #idtest
(
      theID INT IDENTITY(1,1)
      , theValue VARCHAR(20) NULL
)

INSERT #idtest
VALUES ('Plane')
      , ('Train')
      , ('Automobile')

Now we’ll check the identity value by running:

DBCC CHECKIDENT(#idtest)

Which should return 3, the current identity value of the table.  Next, we’ll start a transaction, insert a few rows, and the roll back our changes.

BEGIN TRAN 

INSERT #idtest
VALUES ('Hop')
      , ('Skip')
      , ('Jump')

ROLLBACK TRAN

We just inserted three rows but rolled back the transaction, so the new rows were never committed.  However, if you check the identity value again, you’ll see it’s been incremented to 6 even though no new rows have been committed to the table.

This is actually intended behavior and not a bug in the product.  If you think through some concurrency scenarios, you can understand why identity columns would be handled in this manner.  Just an FYI, you can reset the identity value using the same DBCC command listed above, but you should make sure you understand the potential effects of doing so before you roll it out to your production systems.

For those in or around the Dallas area tomorrow (Thursday) evening, I'll be speaking at the North Texas SQL Server User Group meeting at the Microsoft campus in Irving at 7pm.  I'll be giving my talk on Scripting in SSIS, one of my favorite topics.  Sean McCown will also be continuing his series on PowerShell in SQL Server at 6pm.  If you're a reader of this blog, please catch me after the presentation and say hello.

I received my notification from the PASS Program Committee on Friday evening, and found out that I will not be speaking at the PASS summit this year.  It’s a little disappointing but not entirely surprising; it’s only been in the past year or so that I’ve committed to speaking professionally, and there are many others who submitted sessions who are far more experienced at speaking than I.  To that end, I’d rather have the bar set very high, even if it excludes me for this year, to maintain the quality of the material and presenters at the PASS summit.

I do appreciate that the Program Committee now offers feedback for the sessions that are not selected.  Mine was marked as “Insufficient speaker experience”, which sounds like a bummer but it’s good information and something I can work on between now and next year’s selection.

In the meantime, I’m adding more speaking engagements to my agenda.  I’m speaking this week at the North Texas SQL Server User Group meeting, and I’m going to submit sessions to one (or perhaps both) of the SQL Saturday events coming up in August.

So for this November, I’ll simply be an attendee/volunteer at the PASS Summit, and will continue to accumulate outside engagements to build my street cred for next year’s selection.

I was tagged by Scary DBA and recent MVP awardee Grant Fritchey with the latest viral question:

“So You’re On A Deserted Island With WiFi and you’re still on the clock at work.  Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island.  Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?”

Yes, it’s a bit silly, but think about it – a full month to do the stuff you’ve wanted to do all along.  No ringing phone.  No midnight SPID-killing.  No “I accidentally deleted all of our customers, can you drop what you’re doing and fix it?” support tickets.  Thirty days to show off how smart you are, or to get even smarter.  A month to work on real projects with real value.

With that in mind, the first thing I’d do is to remove any visible signs that I’m on the island, which should keep the rescue craft from easily finding me.  This might buy me 2, maybe 3 more months on the island.

Seriously, with that kind of unallocated time, I’d round out my Analysis Services knowledge and learn everything I could – including MDX, data mining, and DMX.  I believe business intelligence is the future of SQL Server and of the data profession as a whole.  Those who are well-versed in these technologies will bring enormous value to any organization.

With any remaining time I had left, I’d finally get around to writing some tools to make my job easier.  I’ve had many moments of pause throughout my SQL Server career when I’d tell myself, “Someday, I’m going to create an application/script/add-in to make this task easier".  With my time in isolation, I’d spend the time to streamline those tedious task that take up too many mindless clicks or keystrokes.

Now to keep this rolling, I’m going to tag my friend Jack Corbett, who I’ve “known” online for a couple of years but only met in person last weekend in Pensacola.  I’ll also reach out to Ken Simmons, whom I also met at the same SQL Saturday event.

I received my evaluation summary from Karla Remail for this weekend's SQL Saturday event.  The results are as follows:

Expectations:  0 Did not Meet, 2 met, 7 Exceeded. 
 
Overall quality:  Zero 1's, Zero 2's, Zero 3's, 2 4's, 7 5's.
 
Comments:
"Great session!"  "Very knowledgable presenter. Kept on his schedule and presented well." "I really haven't used SSIS, so it was hard not to pick up on some new tips and tricks."

All of the evals were on the right (positive) side of the scale, a good sign to be sure.  I solicited some feedback from a few fellow speakers who attended my session, and received some good comments and a few suggestions as well.

The SQL Saturday Pensacola pics are up!  Visit the SQL Saturday Facebook group at http://www.facebook.com/home.php#/group.php?gid=58052797867&ref=ts
and tag or leave your comments.

I’ve just arrived home from a quick trip to Pensacola to speak at SQL Saturday 14 in Pensacola, FL.  I’m quite happy with the event; the planning and organization was handled very well, and I believe the event was a big success in the eyes of the attendees.

I flew into Pensacola on American Eagle, arriving at 3:30pm on Friday.  I usually dread flying American, but this time wasn’t so bad; the flight was on time and the flight attendant (just one – it was a small plane) was polite and attentive.  My rental car company, which shall remain nameless, was less impressive, since I had to deal with the cigarette residue from the previous renter, and I had to return all the way to the ticket counter to report some undocumented damage to the car before leaving.  Nevertheless, I did get one good surprise upon arrival: Thinking that I had crossed into Eastern Time, I subconsciously added an hour to the time, only to realize upon arrival that western Florida is actually on Central Time.  A free hour!

I got settled into my hotel room, which was about 5 miles from the event location.  Note to self: Don’t be cheap, spend the extra $50/night next time and stay nearby.  In a nice hotel.  Andy Warren and I had tentatively planned to meet before the speaker reception, so I called him and we met up at McGuire’s Irish Pub.  It’s a neat little place, and I recommend it if you find yourself near downtown Pensacola.  One word of caution: read the signs on the outside of the restrooms carefully.  By the way, the ladies room at McGuire’s is quite nice.

The speaker’s reception at the Fish House was a big hit.  I don’t know if anyone kept count, but I’m guessing that we had 35-40 people that showed up over the course of several hours.  It got a little loud when the peripheral crowds arrived, but a good time nonetheless.

The event itself went well, in my opinion.  It got off to a slow start since the building was still locked at 7:45, but there were a lot of volunteers to help out with the heavy lifting.  Traffic flow was very good and there were few bottlenecks during registration.  According to the last figures I heard, there were 180 or so registered, and 170+ showed up (this included a number of walk-ups).  I heard of one speaker that didn’t make it, but it was due to a family issue and Karla was able to adjust the schedule beforehand to allow for this.  The book giveaway was done throughout the day rather than at the end, which helped to thin out the end-of-the-day swag bottleneck.  Among the items given away was a pass to go on a deep sea fishing trip the day after (Sunday), which I personally would have loved to do but just couldn’t fit it into my Sunday schedule.

I spoke again on SSIS Scripting, and the session was full and seemed to be well-received.  I neglected to plug in my laptop during setup – and believe me, a warning message stating “Your battery is at 7% – plug into a power source immediately” will throw a kink into the best presentation.  I had an issue with the resolution on the projector which kept me from going full-screen on my VM, and this slowed me down during the demos.  Despite these minor glitches, everything else went well, and we had some excellent questions and good discussion during and after the presentation.  If you’re interested, I’ll be publishing the slide deck and code on my website in just a while.

I talked to several attendees during and after the event, and all of them that I spoke with spoke very highly of this SQL Saturday event.  Those who attended were gracious, attentive, and polite, and I got the sense that they felt their time was well spent.

I did get to meet a number of people whom I’ve “known” for months or years but had never had a face-to-face chat, including Jack Corbett, Steve Jones, Brad McGehee, and Ken Simmons.  I also got to visit with several folks that I had met at previous events, including Rodney Landrum, Karla Remail, Nathan Heaivilin, Pam Shaw, Jessica Moss, Joe Healy, and a number of others.  I also shared a flight home on Sunday with Brad McGehee, so he and I got to visit for a while as we waited to fly back to Dallas.  It’s great to share the company of these smart folks, because it 1) makes for interesting conversation and 2) gives me hope that I’ll collect a few IQ points by osmosis.  For me, this event was also an experiment in personal networking, but that will be covered in another post.

My hat is off to Karla and the volunteers from the Pensacola SQL Server User Group.  This event was well planned, organized, publicized, and executed.  I’m hopeful that we can replicate this event, and its success, in the Dallas area sometime next calendar year.

For those who are interested, I've got a couple of speaking engagements coming up in the next two weeks. I'll be speaking about one of my favorite topics, scripting in SSIS.

The first event is this coming Saturday, June 6, at the SQL Saturday in Pensacola. This free all-day event has dozens of different sessions on various SQL Server-related topics.

Also, I'll be speaking at the North Texas SQL Server User Group in Dallas (Irving) on Thursday, June 18th at 7pm.

If you're able to make it to either event, please catch me afterward and say hello.

I just finished reading Success Is A Choice: Ten Steps to Overachieving in Business and Life.  Written by Rick Pitino, a highly successful collegiate and professional basketball coach, this book was a good read in that it was well organized, eloquent, and included many stories from the author's own experiences.

As the title suggests, the author offers ten rules that must be adhered to to achieve maximum success.  Like other self-help/motivational books, there's nothing here that would be considered groundbreaking.  However, Pitino and co-author Bill Reynolds have a way of relating to the reader so that one recognizes his own shortcomings without feeling like a scolded puppy. 

Chapter 8, entitled "Be Ferociously Persistent", was, in my opinion, the zenith of this book.  With a well proportioned mix of timeless quotes, common sense, and personal experiences, Pitino reinforces to the reader that continued hard work is necessary to achieve and maintain success.  If you buy into the whole "4 Hour Workweek" thing, you won't enjoy Chapter 8 - the author correctly illustrates that success requires that you work harder, longer, and with greater intensity than the next guy.  I also enjoyed the final chapter, "Survive Your Own Success", which serves as a reminder that success itself can be poisonous if you allow yourself to become complacent in your achievements.

If you're looking for a clever book to guide you to success shortcuts, this is not the book for you.  However, I do recommend this book as a concise motivational tool, a brief but useful set of reminders to those who are willing to go above and beyond.

If you're like me, you've probably typed in LTRIM(RTRIM([value])) hundreds, if not thousands, of times during your travels through SQL Server.  I've long wondered why there was no integrated function that would serve this purpose.

Pinal Dave (@pinaldave) has submitted an item on Microsoft Connect to suggest the TRIM() function as an addition to Transact-SQL.  He correctly points out that most programming languages have this feature baked in.

So if you're on board with the movement to add TRIM() to T-SQL, log in to Microsoft Connect and add your vote and comments.

... and other times, they are stop-you-in-your-tracks funny.  Here's an example of the latter:

http://msmvps.com/blogs/brianmadsen/archive/2009/04/21/funniest-code-comment-ever.aspx 

Over the past 2 days, I’ve gone full bore into Windows 7.  I’ve installed RC0 on 2 of my laptops, and so far I’m happy with the results.

The first installation was very quick and easy.  I had an unrecoverable operating system error on my XP installation, according to the hardware guys, and needed to reformat my machine.  I had just pulled down the bits for Win7 RC0, so I replaced the defective XP install on that machine with the 32 bit version of Windows 7.  This machine, a 2 year old off-the-shelf Dell with 2gb of RAM, performed quite well during and after the installation.  The install process only took 40 minutes, and Windows 7 had drivers for everything but my video card which, oddly enough, had to be configured using the 64 bit Vista driver.  Performance is excellent; the time from login to desktop readiness seems a little longer than on XP, but apart from that, it performs as well as the older OS.  My install of SQL Server 2008 Dev was easy and uneventful.

The second install was done as an upgrade to my Vista 64-bit machine.  I’ve only had this laptop for about 6 months, and have been disappointed in the performance despite having invested in hefty hardware.  I installed the 64-bit version of Windows 7 on this box, performing an upgrade rather than a clean install.  The upgrade took much longer than the clean install; it had run for over 2 hours when I finally gave up and left it to run overnight.  However, once completed, I could tell an immediate difference in performance.  It boots and loads my profile at least 30% faster than Vista, and so far I’ve found no compatibility problems.  I do have a message that warns me of a problem with my video driver management software, but the driver itself still loads up without error.  I had to reinstall the sound driver, but the Vista version on HP’s website installed quite nicely.  Both versions of SQL Server (2005 and 2008 Dev) seem to run fine in the upgraded OS.

So far, I’ve been pleasantly surprised at the ease in which I was able to upgrade these systems to Windows 7.  I’m hopeful that the RTM version will be as easy a transition.  I’ll be sure to pass along any major problems I find, particularly those that affect SQL Server, Visual Studio, or their derivatives.

I've got four new SSIS videos published on JumpstartTV.com:

Expression Language Basics

Secure FTP File Transfer in SSIS

SSIS Lookup Transformation Basics

SSIS Lookup Transformation - Lookup Failures

If you're not familiar with JumpstartTV.com, I would encourage you to stop by and give it a try.  There are scores of short (3-5 minutes) videos that are all free, and are each targeted toward a specific task.  You can even suggest topics for new videos.  You can leave comments for each video, and we appreciate all comments as this is the best way for us to improve content.

The event schedule for SQL Saturday Pensacola has been published.  This event will be held on Saturday, June 6 at the Pensacola Junior College.  The schedule shows that among the speakers are Brad McGehee, Brian Knight, Plamen Ratchev, and Andy Warren, just to name a few.  I am honored to be among the speakers for this event, where I'll be discussing scripting in SSIS.

I'm looking forward to this event as I'll be able to meet some people that I've been corresponding with for a while but have never met face to face, among them Steve Jones, Jack Corbett, and Brad McGehee.  If you read this blog and happen to attend SQL Saturday Pensacola, please stop by my session and say hello.

Should you ever delete data?  In a production environment, do the benefits of deleting old data outweigh the possible risks?

Data quality is important.  Whether you refer to it as data integrity, permanent retention, or simply maintaining a complete audit trail, it can be effectively argued that deleting data from a production database diminishes the big picture of your data.  After all, any data that is worthy of storing, backing up, optimizing, and mining is worth storing permanently.  Deleting data affects the ability to thoroughly research historical activity, and can impact reports and aggregations on the remaining data.  Storing only the rolled-up data, such as end-of-year financial reports are often not sufficient, because auditors or financial personnel may need to drill down to the lowest level of detail.  Other information, including certain healthcare data, is best kept forever (and in some cases, is legislated so) to ensure a proper legal record should it be necessary for judicial or civil proceedings.

The need to routinely delete data was far more critical when storage was more expensive, in terms of dollars and system time.  Purchasing disks for storage has never been cheaper, and with modern 15000 RPM drives and solid state disks, data access times continue to improve.  Removing data simply for the sake of saving bytes on a platter is not as critical as it was just a few years ago.  Data can be retained indefinitely, in the original store or in a separate archive (another table or a different database altogether).

To be clear, I’m not taking on DBAs who use the DELETE functionality to eliminate data.  A proper data retention policy would involve all levels of an organization, from the CXOs to the technical staff and end users.  And a competent retention policy doesn’t have to mandate that data remains in the RDBMS – information can be stored in the database, database backups, the filesystem, magnetic tape or optical disk, or a combination of several of these.  The specifics of permanent data storage should be dictated by how frequently or quickly the data would need to be accessed.

There are times when deleting data is expected and even commonplace.  When staging data in temp tables or table variables, one would expect deletion of data during that processing.  Any process that writes data out to an archive store would naturally need to delete data from the original location, though this could better be considered a move rather than a delete.  Sensitive data which would never be reported on or reused is expected for the protection of customers or clients – the deleting of credit card numbers after a charge is successfully posted would fall into this category.

Unfortunately, this decision does not reside with database administrators alone, or even with their employing organizations.  Some vendor applications will routinely delete older, less-often used data as part of a purge to better performance or decrease storage requirements.  I recently experienced this with a healthcare vendor during a conversion from their product to a newer system.  It was discovered during the planning phase of the conversion project that this vendor’s system was hard-coded to purge the detail data from old accounts.  Although we were able to reconstruct some of the data using other means, the ability to thoroughly report on that historical data has been permanently and irreversibly diminished.

The bottom line is that you should ask yourself whether you could ever need the data you are deleting.  You shouldn’t just ask whether it is likely that you will need the data again – approaching from this angle will eventually come back to bite you.  A more appropriate question would be whether you can imagine any scenario, however unlikely, that would require you to reference the data in the future.  Eventually your boss/the board/the CFO/the auditors will come calling, and you’ll be glad you have your safety net.