Converting Access TEXT Col to SQL Server

[JohnBates]

Hi,

We have "upsized" an Access db to SQlServer 2000.

Now, I'm tweaking the data types. If the Access col was Text len 140, which data type in the SQL Server table would be best: Text or varchar? Is there a diff in performance between text vs. varchar. For the tables I've modified so far, I'm using varchar 140. (I know Text can go beyond 8000 chars) I'm curious as to what most of you would use - text or varchar.

Thanks, John

(on another subject, a real pet peeve of mine is that the Access upsizer sometimes converts cols to the unicode type (nText, nvarchar) in the SQL table... these are inefficient and can cause performance problems)

 

[donutman]

I don't do Access, but I think the equivalent of SQL text is memo in Access. So varchar is better.
-Karl

 

[krizsy]

I use both Access and SQL, varchar goes up to 8,000 characters where test max is 2,147,483,647 characters. If you don't need that much it a waste of memory. Text, space is allocated in 8K pages.

 

[nigelrivett]

There are a lot of operations that cannot be performed on text datatypes. Don't use it unless necessary.
You will need to choose between fixed and variable length (use variable if you're not sure) and ascii / unicode.
i.e char vs varchar
and varchar vs nvarchar.

Again I would suggest varchar fo everything if you don't know.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.