We use Ingres (I know, I know we're in a minority) however some basic rules apply across all RDBMS.
For maximum portability (if this is important to you) stick to core data types and avoid any that are vendor specific (or may be implemented differently by different vendors) Chose the appropriate integer size for the range of values you will need integer (4 byte) smallint (2 byte) Select float if you need to store and calculate to a high precision float (8 byte) Select decimal if you wish to calculate to a fixed precision decimal(a,b) (personally I prefer to use decimal rather than money so that I have more control of precision and formatting of output esp. in these days of multi-tiered applications!) As a general rule use char for small strings <= 10 bytes and use varchar for strings >10 bytes. However different RDBMS's treat varchars in slightly different ways - we have had some issues with searches that compare chars and varchars in a where clause, if the char contains trailing blanks so that's a small note of caution. date - store your dates as date type for maximum benefit from all the date functions out there. long varchar - store long variable text as long varchar - remember to set the buffer size in the CF data source if applicable. That's what I would consider the core types and issues. Michael Traher Systems Manager ICLP (London) Tel: UK +44 (0) 20 8256 9072 Fax: UK +44 (0) 20 8681 0234 This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of ICLP. -----Original Message----- From: Michael Hodgdon [mailto:[EMAIL PROTECTED] Sent: 20 August 2003 17:28 To: CF-Talk Subject: WOT: SQL Data types I am curious how religiously people stay true to using data types for MS SQL Server. I know some of these stretch to other RDBMS's, however, I would like to know what people in industry think. What type of general rules of thumb do people follow when planning data types for a data store? For instance Money, Numeric, varchar, int, bigint ... I think you get the point. I am looking for response such as; never use that type, it won't convert to Oracle. Never use that type, the space you save is not worth it, etc.... If this needs more elaboration, please let me know Mike. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
