Author: way0utwest

I saw someone post a question recently about running out of identity values in an INT column. It happens, it’s happened to me, and I’m not surprised. They couldn’t go to a bigint because of their application, so they asked if they could “renumber” the identity column.

Of course you can, and it’s simple with DBCC CHECKIDENT and the RESEED parameter. As I  and others explained that, the person seemed to think that this would renumber his data and he’d be fine.

Uh,  no.

There are two things you need to know about the identity.

1. They only work on the next value being inserted.

2. They have nothing to do with current values in the table.

A lot of people think that auto number means that the server tracks what’s in the table and then bases the next number on that. Not so. Just as many people have done when they’ve stored the “next” value in another table and used that in their own autonumber scheme, that’s what SQL Server does. They have a better implementation than you get in T-SQL and they handle large sets of rows inserted at once better, but they still just track what the next value to be inserted is. Try this:

CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
DBCC CHECKIDENT( mytable, RESEED, 1)
GO
INSERT mytable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
DROP TABLE dbo.MyTable

What are the results?

You might expect 2 1s, but you get this:

If I change the reseed parameter to 0, then I get this:

So you can see that the “last” value is being stored for the identity column. However note that there’s no checking of what’s in the table? The reseed merely changes that “last” value and so when the database engine goes to insert a new row, it increments this value, regardless of what’s in the table.

If I wanted to prevent duplicates, I’d need a unique index. Nothing to do with the identity property.

Awhile ago I set up a separate site for my editorials. They are copyrighted by me, and I lost some of them in a migration by Red Gate to new servers a few years ago. From 2005 and earlier, it appears that the editorials I wrote are gone, which really annoys me. It’s my fault for not saving them, and I am still hoping to track them down at some point.

That experience had me worried about trusting another IT staff with my own writings, so I started keeping a copy at https://voiceofthedba.wordpress.com/. I’ve been posting copies of the editorials there, at the same time they publish on SQLServerCentral, so I have an online backup.

However when I started, I began using the “categories” of WordPress as the “tags” that I use on SQLServerCentral. That’s not what I intended, and since this was a backup, it wasn’t until I realized that I had a few dozen menu links at the top of the page that I decided to fix it.

Since it’s a hosted service, I had to go back and manually start cleaning categories and adding tags. At the time, I had 70 editorials up there, so this wasn’t a pleasant process. I kept it in the background, slowly working my way through old posts and fixing them. A lesson to be learned here, mess around with the features quickly, and fail early. Make your changes when you have 10 posts, not 70.

My plan is to also move this SQL blog over there, using the export/import features, and take advantage of a single place for the “Steve Jones” publishing world. It’s a part of cleaning up my brand publishing, and I will add it to my Modern Resume presentation as an example of how I work on my brand, while not depending on my employer.

I’ve had a few people ask me this question. It’s the new year, many people are making New Year’s resolutions, and in the technical world I know a lot of people have said they’ll start blogging, or blogging more.

Let me say that a lack of a blog is not a negative.

I don’t think it counts against you in an HR search, an employer doesn’t look down on you, and it doesn’t mean that you’re not a good potential employee, just because you don’t blog. From the questions I’ve asked of many people, it doesn’t hurt you to not blog. I know, double negative, but I wanted to make the point.

However

The power of a blog is that it enhances your reputation. Ultimately you have to impress a potential employer with your work. You have to demonstrate the knowledge in the interview, so why not do a little of it ahead of time? Put some of that knowledge in a blog and show them ahead of time that you’re someone they might want to hire.

A blog makes you stand out, it gets you moving up the Long Tail. That’s what you want, to stand out a bit, to increase the chances that your resume stands out among all the other ones.

We have a lot of administrative tools for SQLServerCentral that are available online. Most of the work I do can be done from the web, which is cool. It means that I can manage things from anyplace, and any computer. That’s been handy at times when I’ve been away from my desk, even at conferences, and needed to get things done.

However we don’t have a tool for everything. There are some one-off issues, like awarding points back for the QOD, that I rarely do. Despite the fact that it seems to me I’m doing this often, it’s probably 10 times a year, and so it’s not really worth building a tool. Instead it’s easier to just hit the database and make a correction.

I answer a lot of threads on the site, posting 300+ times a month, and I’ve set my notifications to email as a default. It’s a good way for me to see when someone responds, and go check the thread to see if I need to provide more info or add another comment. However that means I’m subscribed to a lot of threads. In fact, here was my list the other day, after I’d pruned out  a couple of pages (I think 20-25 a page).

Everyone has this page in their forum Control Panel, at the bottom where there’s a “topic subscriptions” link. There’s also a forum subscriptions link for those that subscribe with that method. I don’t do forums, only topics. Next to each subscription there’s that checkbox, and then at the bottom a button to “unsubscribe” from all the items checked on that page. It’s not a big deal to be subscribed to old threads, but it does become a hassle when I want to search one out. For most people they might have a dozen, but for a few of us, we have thousands. So I try to trim them periodically.

This is the bottom of the page, where there’s an extra check box. I would assume that checking that one would check all boxes on the page, and if that worked, it would be easy to remove a few hundred subscriptions a day and keep things manageable.

It doesn’t.

In fact, as near as I can tell, it doesn’t do anything, which means that I must manually check all boxes on a page, which is slow and error prone with a mouse. Then I click the unsubscribe button, and repeat, 70 times in this case, which isn’t something I want to do. Or think I should do.

The solution, for me, is to back door this. It won’t work for others, but this is a case, where it makes sense to me. I connect to the database servers, and load up a script. I built the script by poking around in the tables, looking for something that seemed like it stored this data. In this case, the table instantforum_topicsubscriptions seemed likely, and digging through it, I found my user had 1560 rows in there, which matched up with the first image above. We bought the forum code, so we don’t have a good ER diagram of Data Dictionary of where things go. I’ve poked around over the years to find some things, and in this case, wrote a simple delete.

DELETE s

from instantforum_topicsubscriptions s

  inner join instantforum_topics t

    on s.topicid = t.topicid

where t.lastposteddate > dateadd( d, –90, getdate())

This kills any threads which haven’t seen a post in 90 days, and that’s pretty generous. I might cut it down to 45 days, but for now I’ll stick with 90. That reduces me down to a more manageable level. It isn’t worth engaging a developer for a couple days to build me a better tool since it’s very few people that need to do something like this. It’s also not worth complaining to instantASP since how many people on a typical board have that many subscriptions? I bet even most active members have a few hundred at most. Not necessarily trying to develop a tool to deal with. As you can see, it worked.

Wow, I’ve been busy. Might need to rethink that 90 day thing.