Re: OT: SQL Data Type
What are the benefits on using a CHAR field versus VARCHAR? The basic idea, back to the first database systems, is that all records have the same length and use the same amount of space in the database. Thus finding a record is just a matter of multiplying this record length by the record number, and read a bunch of bytes: pretty fast. The problem is that database may grow pretty big if large fields are needed, and use much empty space, especially if many of the fields are empty, an empty field taking exactly as much space than a full field. Then came the VARCHAR, AKA memo field. The idea is to store this field in a special area where all fields have a specific length and occupy only the space they need. The database then stores the address of the data in the record, instead of the data itself, but the record still has the same length. The advantage of memo fields is more efficiency in terms of disk space used, but it also implies a two step process to retrieve data, then it is less efficient in terms of speed. It is then up to the programmer to decide what type of efficiency is the best for his application. General rules are: - the larger the field must be, - the more often this field can be empty, then the better it is to use VARCHAR. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293911 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Data Type
On Wednesday 28 Nov 2007, Claude Schneegans wrote: It is then up to the programmer to decide what type of efficiency is the best for his application. However, almost no programmers will ever have to care about the tiny performance changes betwen the two, in which case VARCHAR is better because you don't need to TRIM() everything all the time. -- Tom Chiverton Helping to completely restore best-of-breed markets on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293913 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Data Type
ColdFusion wrote: What are the benefits on using a CHAR field versus VARCHAR? Check your database manual, the differences between implementations are such that for every other answer is pure speculation. Jochem ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293914 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: SQL Data Type
What are the benefits on using a CHAR field versus VARCHAR? I know with a CHAR(10) field that if the data is: START then the actual value is: START_ _ _ _ _ rather than a VARCHAR(10) of START. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293905 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Data Type
What are the benefits on using a CHAR field versus VARCHAR? Well, using char can be a bit speedier when querying, but takes up more disk space. You want to use char when all the data in that field will be close to the same length. the db will pad it, so it ends up taking up more space than if you used varchar and it set the length to the exact length of the data. I just converted some varchars to char as a small optimization to an app. Will ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293906 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Data Type
But when using CHAR within a SQL Statement don't you need to use a REPEATSTRING or a TRIM statement for proper validation so ensure that the value in the database matches the same length of the variable itself? I mainly use VARCHAR set to a size of the largest value expected. I only use CHAR when I know the length will always be the same such as: Where sex = 'M' or sex = 'F' This case sex would have a CHAR(1). Other than a small optimization within the query, coding would be a bit more to account for the complete length of the string? -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 8:53 PM To: CF-Talk Subject: Re: SQL Data Type What are the benefits on using a CHAR field versus VARCHAR? Well, using char can be a bit speedier when querying, but takes up more disk space. You want to use char when all the data in that field will be close to the same length. the db will pad it, so it ends up taking up more space than if you used varchar and it set the length to the exact length of the data. I just converted some varchars to char as a small optimization to an app. Will ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293907 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql data type wierdness
greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql data type wierdness
Sorry its a float length of 8 dataype, -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:22 PM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql data type wierdness
That's pretty vague. What exactly is your question? -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 8:22 AM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: sql data type wierdness
Both numbers are the same. Enterprise manager is displaying it cleanly, where as you probably need a NumberFormat around the cf output of the variable. Floating point numbers are generally depicted with the E-x notation as you never know quite where that decimal point is going to be. 0.03027778 ~ 3.02777801E-10 regards Stephen Sorry its a float length of 8 dataype, -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:22 PM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: sql data type wierdness
If you mean the difference in value: Enterprise manager is rounding the number off slightly, while CF displays the number with a larger number of significant digits. Computer floating point numbers almost always have some sort of 'inexactness', or rounding error, whatever you'd like to call it. This just has to do with the mathematics of storing floating point decimal numbers in a binary format. If you mean the displayed format: (3.02...E-2 vs 0.32...), the first is scientific notation, while the second is the more commonly recognized represtentation. Scientific notation permits more signifigant digits to be displayed in a shorter form when dealing with very small or very large numbers. Use NumberFormat() to get CF to display the number in a more recognizable format. Jim - Original Message - From: John McCosker [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, March 11, 2002 9:25 AM Subject: RE: sql data type wierdness Sorry its a float length of 8 dataype, -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:22 PM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql data type wierdness
Sorry for seeming hectic, some latitudes and longitudes are coming intp SQL server from server agents listening to GSM and GPS satellites, CF is displaying the data on some occaissions which seems to be wrong, e.g. In a vehicle reports page, I'm just outputting that data straight, no manipulation one or two longitude and latitudes now and again appear in this numeric format 3.02777801E-2, not in this exact format but with E-2 on the end, in SQL SERVER through enterprise manager it says its 0.03027778, I am also showing this vehicle on a map, on one occaision the vehicle should have been driving up the M6 from Birmingham, but instead was somewhere in Egypt. I can quite easily catch these rogue longitudes and latitudes from causing errors, I just quite simply wondered, which is showing me true data representation, CF or SQL SERVER, you see the problem is I don't know!! I only hope you can be as quick to advise as you are to snap. -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:28 PM To: CF-Talk Subject: RE: sql data type wierdness That's pretty vague. What exactly is your question? -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 8:22 AM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql data type wierdness
Thanks folks, understand better, J -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:48 PM To: CF-Talk Subject: Re: sql data type wierdness If you mean the difference in value: Enterprise manager is rounding the number off slightly, while CF displays the number with a larger number of significant digits. Computer floating point numbers almost always have some sort of 'inexactness', or rounding error, whatever you'd like to call it. This just has to do with the mathematics of storing floating point decimal numbers in a binary format. If you mean the displayed format: (3.02...E-2 vs 0.32...), the first is scientific notation, while the second is the more commonly recognized represtentation. Scientific notation permits more signifigant digits to be displayed in a shorter form when dealing with very small or very large numbers. Use NumberFormat() to get CF to display the number in a more recognizable format. Jim - Original Message - From: John McCosker [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, March 11, 2002 9:25 AM Subject: RE: sql data type wierdness Sorry its a float length of 8 dataype, -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:22 PM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql data type wierdness
I have a fair amount of exp with tens of millions of record SQL DBs with lat/lon fields. We chose to normalize all values into whole numbers. We had some of our own data, data from 3rd parties, and services from still more 3rd parties. It was critical to maintain the data in a consistent format on our end. Since we did a myriad of things with the data, we would've wound up converting the format anyway, so we didn't introduce any real additional work by normalizing the disparate lat/lon formats into one format. Of course, that's assuming the datafeeds you're getting lat/lon values from are all using the same datum as reference ;). -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 8:52 AM To: CF-Talk Subject: RE: sql data type wierdness Sorry for seeming hectic, some latitudes and longitudes are coming intp SQL server from server agents listening to GSM and GPS satellites, CF is displaying the data on some occaissions which seems to be wrong, e.g. In a vehicle reports page, I'm just outputting that data straight, no manipulation one or two longitude and latitudes now and again appear in this numeric format 3.02777801E-2, not in this exact format but with E-2 on the end, in SQL SERVER through enterprise manager it says its 0.03027778, I am also showing this vehicle on a map, on one occaision the vehicle should have been driving up the M6 from Birmingham, but instead was somewhere in Egypt. I can quite easily catch these rogue longitudes and latitudes from causing errors, I just quite simply wondered, which is showing me true data representation, CF or SQL SERVER, you see the problem is I don't know!! I only hope you can be as quick to advise as you are to snap. -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:28 PM To: CF-Talk Subject: RE: sql data type wierdness That's pretty vague. What exactly is your question? -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 8:22 AM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql data type wierdness
Thanks Dylan, Repectfully, -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 5:02 PM To: CF-Talk Subject: RE: sql data type wierdness I have a fair amount of exp with tens of millions of record SQL DBs with lat/lon fields. We chose to normalize all values into whole numbers. We had some of our own data, data from 3rd parties, and services from still more 3rd parties. It was critical to maintain the data in a consistent format on our end. Since we did a myriad of things with the data, we would've wound up converting the format anyway, so we didn't introduce any real additional work by normalizing the disparate lat/lon formats into one format. Of course, that's assuming the datafeeds you're getting lat/lon values from are all using the same datum as reference ;). -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 8:52 AM To: CF-Talk Subject: RE: sql data type wierdness Sorry for seeming hectic, some latitudes and longitudes are coming intp SQL server from server agents listening to GSM and GPS satellites, CF is displaying the data on some occaissions which seems to be wrong, e.g. In a vehicle reports page, I'm just outputting that data straight, no manipulation one or two longitude and latitudes now and again appear in this numeric format 3.02777801E-2, not in this exact format but with E-2 on the end, in SQL SERVER through enterprise manager it says its 0.03027778, I am also showing this vehicle on a map, on one occaision the vehicle should have been driving up the M6 from Birmingham, but instead was somewhere in Egypt. I can quite easily catch these rogue longitudes and latitudes from causing errors, I just quite simply wondered, which is showing me true data representation, CF or SQL SERVER, you see the problem is I don't know!! I only hope you can be as quick to advise as you are to snap. -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:28 PM To: CF-Talk Subject: RE: sql data type wierdness That's pretty vague. What exactly is your question? -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 8:22 AM To: CF-Talk Subject: sql data type wierdness greetings, CF outputs this value from MS SQL SERVER 7.0 as 3.02777801E-2 , in enterprise manager the value looks like this 0.03027778 any help would be appreciated, respectfully, J __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
