AutoNumber Column Question

[smbarney]

I know this is a simple procedure but I cannot figure it out. I need to insert a new column into an existing table. The column needs to be an auto number, starting at 1 and counting up by 1. In addition, this field needs to be a primary key for the table. Currently, the table has 150 million rows in it.

The table is called “PEOPLE” and the new column that I need to add is called “PeopleID”.

Thanks

 

[SQL_Hell]

Hi,

How did you get to the situation where you have a table 150 million rows and no primary key?

 

[smbarney]

It was a data dump from an old system we have. Actually, this is the smallest of four tables. The biggest has over 950 million rows.

 

[SQL_Hell]

Ok, you will need to do this change at a time where there is not much activity on the server. The best way to do this will be to create a new table with the new primery key field and all the other fields from the table with 150 million rows... then insert the data into the new table in stages otherwise you will cause the transaction log to grow too big and the operation will fail.

Once you have loaded all the data then rename your old table and rename your new table to the name of the old one.

If there is no transcation log backups then you can set the database to simple recovery mode during this process which will help it log less stuff to the transaction log

 

[smbarney]

Okay, would the code look like this:

CREATE TABLE PEOPLE_TEMP
(
people_key BIGINT IDENTITY,
other columns...
PRIMARY KEY (people_key)
);

To copy the data from people to people_temp, would I use a BULK INSERT command, or is there a better way to do it?

 

[SQL_Hell]

Yes the code would look like that,

What version of SQL server are you using?

 

[smbarney]

SQL Server 2005.

 

[SQL_Hell]

Do you have access to management studio?

I would create a script for creating the table with 150 millions rows and change the table name as mentioned defore, add the primary key via mangement studio or via the alter table t-sql command. (can script for you if you need)

Then load your data into this table via import export wizard (SSIS) in increments (as small as possible) maybe 5 million at a time? Or via bulk insert if you dont have the tools

Is there a date field or something like that you could use? to break up the data

I think honestly if you try to create an indentity primary key on the fly then your database is going to lock up big time, this why I suggest moving data slowly, creating the new table and doing it during non production hours.

 

[smbarney]

I do have access to mangement studio. What about full-text indexing? The problem I am dealing with is that I have a huge amount of data that needs to be searched in a number of different ways. While I could create indexes for everything, it seems that full-text indexing might be better. What are the pros and cons of full indexing? I've never used it before.

 

[SQL_Hell]

It depends what you are searching for, if you are things like the following:

SELECT * FROM MyTable WHERE Notes = 'Deliver Tuesday'
SELECT * FROM MyTable WHERE Notes LIKE '%caution%'

Then you don't need full text indexing, but if you are doing the following:

Two words near each other
Any word derived from a particular root (for example run, ran, or running)
Multiple words with distinct weightings
A word or phrase close to the search word or phrase


If you are worried about creating too many indexes on one table, then the data is probably quite wide and you should normalise it

*edit had some editing issues, hope you can read this now

 

[smbarney]

The later is the case. We need to be able to search multiple weighted words, and words derived from a root.

Is there a way to determine how big the full-index catalog file will be?

 

[SQL_Hell]

I don't know if it's possible to determine this before hand, but you can use this query to find out how big it is

select FullTextCatalogProperty(‘catalogName’, ‘IndexSize’)

 

[smbarney]

Sorry two last questions:

1. For full indexing, you need a primary key field for each table. Is there any value in keeping the non-clustered indexes once you have created the catalog file? Or should I just drop them?

2. In my data set, I have a field that is just time in the this format: HHMM. What would be the best data type to import it into to Datetime or smalldatetime?

Thanks for your help on this.

 

[SQL_Hell]

I afraid you've got me on a subject I am not very well versed in, so basically I don't know the answer to your questions:

Here are some articles on fulltext indexing

http://www.codeproject.com/KB/architecture/sqlfulltextindexing.aspx

http://www.sql-server-performance.com/tips/full_text_search_p1.aspx

concerning the datetime issue, if it's only HHMM and you don't need seconds then I would probably just use a VARCHAR(4)