Jeremy Grand


I'm getting ready to enable fti on sql 2k5. The tables I care about do not have single-field indexes, so I'm planning to add them. I've tested this in a limited way, and do not see any effects that would require changing the applications running against these tables, but I'd really like a sanity check on this. Is there something I should be looking at and haven't

The tables have primary indexes now. For example:

invoice table has 2 fields in its unique primary key:

- invoice ID

- customer ID

invoice items table has 3rd columns:

- invoice ID

- customer ID

- item ID

I've simply added a single auto-increment bigint column to each table, and changed the primary index to that column.

The possible issues that come to mind are that adding, deleting or updating records would somehow find the wrong row, or add a duplicate. but since the programs didn't do that before (and sql server would have thrown a fit if they did), surely there is no reason to believe they would start doing so now.

All this sounds way to easy, and I'm worried I'm missing something really big. Any thoughts or suggestions

Jeremy




Re: Sanity check -- adding single field index for fulltext indexing

Dishant


just wondering if your application at no palce has any insert statement where field names are not specified coz you have added a new column and it could thru an error at the time of insertion.

PK as identity will not be giving any errors, PK creates cluster index so no issues again. SQL 2k5 has the lower version compatiblity and i already migrated my database to it and never faced any issues yet.

If you have any complex stored procedures using system tables, system procedures, views etc you might want to test those once in 2k5

I would suggest one round of complete regression testing after you migrate to SQL 2k5 in a simulated environment and then doing the same exercise in production database.







Re: Sanity check -- adding single field index for fulltext indexing

Jeremy Grand

Good point about insert statements. I don't have any without specific fieldnames, so I got lucky there.

As for going to sqk2k5, we've already done that & have been up and running for almost 6 mos. The biggest issue is that we're dropping connections, whereas sql2k did not do that.

Thanks!

Jeremy