SQL data types - best practices

[Bill Parker]

I don't find anything in the wiki about this, and not much on the message board. It would sure be helpful to get experienced input.

The 'export to sql' feature automatically converts existing DBF types to SQL types. User can change the type if desired. Obviously there is not one Best, but my 2 SQL books and the web have not given me much enlightenment so far.

The default type conversion in v11 are

DBF		SQL
Char255		char(255)
Char1		char(1)
Num16_2		numeric(15,2)
Num17_7		numeric(16,7)
Num1		numeric(1,0)
Date		datetime
Logical		bit
Datetime 	datetime
Shorttime	smalldatetime
Memo		text
Bitmap		image
Rtf		image
Jpeg		image
Image_Ref	text
Html		text
Uuid		uniqueidentifier

My initial questions are
1. in Num 16, 2 conversion Alpha includes the decimal point in the 16. So I assume that SQL does not, which is why it went down to 15?
2. SQL Text data type has been depricated in SQL Server. Should we be using varcar()?
3. Image data type has been depricated. What should be used?
4. Date converts to datetime. If only the date portion is used, are queries,etc., simpler if SQL Date type is used?
5. Logical converts to bit. I have seen posts about only using tinyint(1) for logical. What are the implications?
6. In general is there any reason not to use varchar in place of char.
7. At what point is nvarchar needed. If app has only English and Spanish is varchar OK? Is there any penalty other than disk space for nvarchar?

Bill.

[Bill Parker]

Re: SQL data types - best practices

Any thoughts?

[glenschild]

Re: SQL data types - best practices

Hi Bill

Are you talking SQL Server or MySQL. For example a dbf logical field is a tinyint in mysql and bit in sql!

I tend to use nvarchar rather than varchar, there is a slightly greater overhead but not enough that I have needed to worry about. I also tend to use nvarchar(max) for memo equivalents.

Will think about this more tomorrow after some zzzzzzz's

[Bill Parker]

Re: SQL data types - best practices

Thanks Glen. In my curent case I am talking about sql server, but I think it is useful to collect info for at least these 2 sql flavors.

After some other reading it seems like there is little/no storage penalty with nvarchar if western european languages are being used.

Bill.

[Pat Bremkamp]

Re: SQL data types - best practices

Hi Bill,

I use MySQL and the discussion of char vs varchar is an interesting one. Based on the references I've read, it seems to come down to a question of speed vs storage space.

Upside of char: indexes based on char are faster that varchar. For the best possible speed, all the character fields in the record should be char so all the records are the same length. But then, until you get to millions of records, does it really matter that the query time is .08 seconds instead of .02?

Downside of char: takes more space on the hard drive. Also, you have to trim off extra spaces.

Upside of varchar: less space and no trimming.

Downside of varchar: slower queries.

[glenschild]

Re: SQL data types - best practices

Also on a related issue concerning speed and indexes this article is worth a read.

[Bill Parker]

Re: SQL data types - best practices

Thanks guys. Next step is unicode vs. not. Latin-General is current collation and that should cover all the languages I would need. But it also seems that UTF8 will still just use 1 byte for those same characters. So is there any real penalty for going with nchar and nvarchar? If minimal penalty, I may as well add the option to go world-wide! Then if multi-byte characters are needed it is easy.

Bill.