Check out our Angular Book Series.

How do you add an identity to an existing column in SQL Server?

I was helping a client change to a new web host. During this process I discovered an Access database was powering part of their web site. This had to go!

I imported the database into SQL Server. Unfortunately, this process removed the identify specifications on the primary key column for one of the tables. This means that the primary key column would not automatically fill in with an automatically incrementing integer. I opened up the table design, and tried to change the field back to an ID. Unfortunately, SQL Server does not allow you to add an identity specification to an existing column on an existing table.

How do I fix that?

What is the Process?

The process was tricky to figure out, but not all that too hard. Basically, do this:

  1. Create a new table with all the same columns as the old table. Make sure this new table has an identity column.
  2. Enable Identity Insert on the new table.
  3. Copy all data from the first table into the new table.
  4. Disable Identity Insert on the new table.
  5. Delete the original table.
  6. Rename the new table to the original table's name.

Then you're done.

What is the Code?

This whole process can be done quickly with a SQL Server script.

First, create the new table:

CREATE TABLE dbo.newtable_temp
textField nvarchar(100) NULL,
bitField bit not NULL
/* other fields */

Then enable the identity insert:

SET IDENTITY_INSERT dbo. newtable_temp ON

Next, use this script to insert all the rows from the original table into the new table:

FROM dbo.newtable )
INSERT INTO dbo. newtable _temp ( Id, textField, bitField /* other fields */)
SELECT Id, textField, bitField /* other fields */
FROM dbo. newtable TABLOCKX

Then turn off the identity insert:

SET IDENTITY_INSERT dbo.newtable_temp ON

Delete the original table:

DROP TABLE dbo.newtable

Finally rename the old table:

Exec sp_rename 'newtable_temp', 'newtable'

And you should be good to go!

Much thanks to this Stack Overflow question for helping me out.

Moving a database from one server to another

One of my clients is moving their site to a new host. As their web developer, I'm doing most of the work (As it should be).

It's easy to ftp down (and up) the HTML files or CFM templates, but moving the database is a bit tricky.

In an ideal world I could get a database backup from the current host and have it restored at the new host. Unfortunately, the current host is unresponsive to phone calls, e-mails, and Instant Messages.

It is worth nothing that it is this un-responsiveness that prompted the search for a new host. They aren't being difficult because we're moving. We're moving because they were being difficult.

So, how do you move a bunch of data from one SQL Server database to another SQL Server database? You could use DTS. I've used it before and it is acceptable approach. But, I think I found something better.

I'm using ApexSQL Diff, which is a sync tool for databases. It can work on both structure and data. First, I used the tool to move the table structure. Then I created a data sync file for each table. Since the new database is completely empty, the data sync file contains a ton of insert statements.

The problem I'm having is that a ton of data inserts will inflate the transaction log file and prevent you from inserting any more data. So, I've only finished half the job. I got on the phone w/ tech support at the new host and asked them to bump up the log file size.

The tech support rep told suggested that I move less data. And he said the transaction log has ~7 megs free which is plenty of space. I told him I'm getting this error, and usually it is because the transaction log does not have enough space. He suggested moving data in smaller chunks, which I suppose is valid. But, I really don't want to spread this process out over the next week.

Eventually he escalated my request and said someone would get back to me.

I've run into these type of "transaction log" problems before, but don't have an ideal solution for it.

How do you handle this?

Alternatives to Database Recursion

Sean Corfield recently posted an entry about recursionin the database. This got me thinking. I once had a teacher who was adamantly against recursion for performance reasons, saying that Towers of Hanaoi was the only example where recursion was beneficial from a performance standpoint.

I have been unable to prove him wrong; can anyone else do so?

As such, I've always been cautious of recursion.

I recently spoke to a client about an application with a similar structure (to what Sean describes in his post). It was storing part information in a self-referencing table. (One part can be made to build other parts, etc.. ). It turned out that this application was a drag, because in some cases it was recursing up to 300 times to get all the data in the 'recursive tree'. This is a situation where recursion was just not working. We spoke, and I suggested this type of solution.

For simplicity, I'll use a table like this:

RecurisveExample(PKID, Data, RecurseOrder)

PKID is the primary key. Data is whatever data we are storing (whether it is part numbers or cat pedigree) RecurseOrder is a text string

For each element in the table, you give it a 3-character identifier (In my example, I'll use 3 characters, but the identifier can actually be 4 or more). The RecurseOrder field contains 'Parent's identifier' + 'Current identifier'

So, to put some data in this table:
1 Root 1 001
2 Root 2 002
3 Root 3 003
4Child 1.1 001001
5 Child 1.2 001002
6 Child 1.3 001003
7 Child 1.4 001004
8 Child 2.1 002001
9 Child 2.2 002002
10 Child 2.3 002003
11 Child 2.3.1 002003001
12 Child 2.3.2 002003002
13 Child 002003002001
14 Child 002003002002
15 Child 2.2.1 002002001
16 Child 2.2.2 002002002

The root is assigned '001', '002', '003'. The children of root 1 are assigned '001001','001002', '001003'. The children of child 1 are assigned '001001001', '001001002', '001001003', and so on.

Given any root, you can get the tree in recursive order just by sorting on the recurseorder column. To get the recurseorder on root 2, for example, you can do this:

select * from recursiveexample
where recurseorder like '002%'
order by recurseorder

To get the tree, starting at the third child of root 2, you can do this:

select * from recursiveexample
where recurseorder like '002003%'
order by recurseorder

In some cases, such as discussion groups (where you want to retrieve a whole thread in post order) I have found this to be an elegant solution.

With a three-character identifier, you are limited to 1000 elements before you run out of thread strings. But you use a larger identifier string if you need more.

All Content Copyright 2005, 2006, 2007, 2008, 2009 Jeffry Houser. May not be reused without permission
BlogCFC was created by Raymond Camden. This blog is running version