Sometimes you just gotta take a broader view

Database pilot fish is about to leave work when a co-worker catches him as he's walking out of his cubicle.

"He says that he and another colleague found an issue with a table that's not inserting unique IDs -- the primary key -- for records." says fish. "And it's a table I remodeled a few years back."

That means it's probably fish's fault if there's an issue. But fish is also intrigued by the problem -- after all, it's unlikely that SQL Server would fail completely by inserting the same IDs.

So they go to the co-worker's desk, where SQL Server Management Studio is on his screen, showing a table containing about 150 records. Sure enough, toward the end of the table is a bunch of records all showing an ID of 1 instead of a unique ID.

And that's very odd indeed.

"When my company remodels old tables, we create views with triggers to do new inserts so we don't have to sweep tons of lines of code against the new tables," fish says.

"In this case we actually took one table, remodeled it into several tables and created a view that inserts records into different tables based on certain criteria. That first table -- call it Table A -- is by far the largest of the several tables we created."

Fish decides to start his debugging by performing a single insert against the SSMS view that will create a new record in another table -- call that one Table B.

After the insert, he checks Table B. It now has a new record with a correctly auto-incremented primary ID, so that table is functioning correctly and SQL Server isn't completely broken.

Next, he tries inserting into Table A, the one supposedly inserting duplicate primary IDs. He executes the insert and checks Table A. There it is at the bottom: the new record, with an ID of 1.

"That's when I look at the ID column in SSMS and realize it's not wide enough to show the full ID," says fish. "So I expand it, and the IDs are now correctly shown as numbers larger than 100 -- no longer getting chopped off as 1.

"It probably took me too long to figure that out, but I was not nearly as embarrassed as my two colleagues were."

Don't be embarrassed -- Sharky will file off the identifying marks from your story. So send me your true tale of IT life at sharky@computerworld.com. You can also comment on today's tale at Sharky's Google+ community, and read thousands of great old tales in the Sharkives.

Get Sharky's outtakes from the IT Theater of the Absurd delivered directly to your Inbox. Subscribe now to the Daily Shark Newsletter.

How AI will change enterprise mobility
  
Shop Tech Products at Amazon