varchar performance

Well -- A while back I put a memo out concerning chars vrs varchars and performance issues. I guess that I didn't do a very good job making my point. - must have been too sleepy at the time. Had a lot of people to question my sainity. Anyway, let me further explain what I should have said. First of all - I feel pretty strongly that cpu bottlenecks should be avoided at all costs. There is a tendency to design systems so that more cpu cycles are required than are absolutly needed. While this is no big deal with single datum and small amouts of data, it can become a real drag when the system is enlarged and we start trying to process millions of rows. Next - varchars have a built-in overhead. In order to process any row containing varchars, the row must be normalized. That means that we have to calculate the start and end of each varchar, as well as any datum beyond the varchar field. While this additional overhead is no big deal for small populations, it adds up for larger populations. Now -- does this mean that varchars should never be used? Absolutly not. However, it does mean be careful where they are used. 1) I'd be really hard pressed to see the advantage of varchars when the max size is realitively small. For instance, if the max size is only 20 characters, then I'd be rather hard pressed to use a varchar. There might be some reason that I might chose a varchar, but I doubt it. 2) If I'm going to use varchars, then I'd do everything I could to ensure that the varchar field was static. When a varchar field is expanded, we try to store that row on the same page, but it might no longer fit. Since we have to maintain the rowid of that row, what we have to do is to store that row on a different page where it will fit, and then mark the original page with a "forward pointer" marker. That means that any future access to that row will actually require two page accesses, one on the original page and then one on the new page containing the row. If the varchar field is going to change, then I'd consider using a partition text field instead of a varchar. 3) If I'm going to used varchars, then I'd do everything that I could to avoid a sequential scan of the datapages by any query that I was doing. This is to avoid the cost of expanding and calculating the location of any filters in a query against the table. That also means that I might need to have more indexes on the varchar table than on a table without varchars. We've had several cases where customers would use varchars where they really weren't desirable. I know of some where everything that could be a char field was always defined as varchar(255,0). They never had a char field. Needless to say, they had performance problems. Madison Pruet