Solved

Best sql data type to store comma delimited lists

Posted on 2006-11-11
11
619 Views
Last Modified: 2008-01-09
What's the best data type and data length to store large comma delimited lists? I'm using SQL 2000 and CF5. The list is stored into a column named POP_KEYWORDS. It's always accessed as one list, not individual elements.

I may be using cfquery to transfer the entire list from POP_KEYWORDS to another database table where the new list in the new table will then be broken down into individual elements. POP_KEYWORDS is accessed rarely and only by myself to manually  transfer that list elsewhere. It's just a storage of popular keywords.

Each POP_KEYWORDS list can hold around 6000 characters, including commas.  But this may be longer in the future. Should I use a VARCHAR data type or can I use TEXT as a data type and will it accept comma delimited text? Remember, this is just storage. The list will not be modified from the original storage column.

And if I decide to simply save POP_KEYWORDS into a text file on the hard drive, what format should that file be, CSV? And how does one access a text file and copy it into ar database, like this?

<cfset newpoplist = "c:\lists\keywords\list1.csv">

Is that the correct way to get it into a list ready for a query?

 - Georgia

0
Comment
Question by:RollinNow
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17922617
SQL Server 2000 has VARCHAR to store update to 8000 characters.
in your future, with existing sql server 2005, varchar(max) can go up to 2GB...

so, your choices are this:
§ use varchar, but limit to max 8000 characters
§ use text, but be aware that handling of that column will be less trivial than varchar
§ upgrade to sql server 2005
§ use varchar, but eventually split the value up into several ROWS...
0
 

Author Comment

by:RollinNow
ID: 17922643
I cannot upgrade. Not my business.


I'm aware of the 8000 character limit for varchar so, that leaves saving to a file or using TEXT data type.

> handling of that column will be less trivial than varchar

What do you mean? Remember, I'm the only person using that column and only once in a great while. It's just a storage and it will not be modified. Please let me know what you mean here as I can't decide unless I understand you.

Also, you did not comment on my idea of saving to a text file, what format, and how best to bring it into a list for a database, i.e,:

<cfset newpoplist = "c:\lists\keywords\list1.csv">

  - Georgia

0
 

Author Comment

by:RollinNow
ID: 17922667

I used <cffile action="read" file="c:\lists\keywords\list1.txt">

Does that seem about right to set the keyword variable to that file?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17922692
I don't use coldfusion, so I cannot help in that point.
you might post a pointer question in the coldfusion topic area to attract those experts to your question
0
 

Author Comment

by:RollinNow
ID: 17922747
Okay, fair enough, but that's just an alternative if I can't or shouldn't use a database for storage.

The problem is that you still did not answer my previous question. Here it is again:



I'm aware of the 8000 character limit for varchar so, that leaves saving to a file or using TEXT data type.

> handling of that column will be less trivial than varchar

What do you mean? Remember, I'm the only person using that column and only once in a great while. It's just a storage and it will not be modified. Please let me know what you mean here as I can't decide unless I understand you.

 - Georgia
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17922990
I see I forgot to post that.
you cannot directly write or update text data using the INSERT or UPDATE statements.
Instead,  you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT.

it has nothing to do about if you are the only one to use the column, but about the syntax you have to use.
now, possibly coldfusion can handle the difference transparently, but CF is simply a black box for me, so I cannot tell you about that.
I only know that from vb, php etc... I cannot work the same with TEXT than with VARCHAR columns.

0
 

Author Comment

by:RollinNow
ID: 17923047
Okay, thanks for your help, but I'm no closer to having an answer I can use.

Will someone else answer the rest of this question so I can close it and share the points?  

If I use TEXT data type, will I be able to access that table, and copy or append the entire contents to another table, using a CF Query, or would I be better to stay with VARCHAR and hope I don't go over the 8000 length limit?

Or better yet to use a text file for storage because I'm just storing this data for later use and not accessing it?

  - Georgia
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17923144
>>If I use TEXT data type, will I be able to access that table<<
Yes.
>>copy or append the entire contents to another table, using a CF Query<<
You need to ask in a more appropriate Topic Area such as:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/

>>would I be better to stay with VARCHAR and hope I don't go over the 8000 length limit?<<
Only you know the answer to that question.

>>Or better yet to use a text file for storage because I'm just storing this data for later use and not accessing it?<<
Again, you should ask this question in a more appropriate TA
0
 

Author Comment

by:RollinNow
ID: 17923290
Thanks for your in-depth analysis. Unfortunately, it was not helpful.

I'll just sit this one out in hopes for the more appropriate, conscientious type. Could be a long shot, I admit, but my fingernails are drying ...
 
    - Georgia
0
 
LVL 21

Accepted Solution

by:
pinaldave earned 500 total points
ID: 17923401
Hello,

If I use TEXT data type, will I be able to access that table, and copy or append the entire contents to another table, using a CF Query, or would I be better to stay with VARCHAR and hope I don't go over the 8000 length limit?

You will be able to use data for sure from table. Copy and append to another table using CF query. YES. You can use right SQL between
<cfquery name="myquery" datasource="yourdb">
 your correct sql statement.
</cfquery>

Regards,
---Pinal
0
 

Author Comment

by:RollinNow
ID: 17923438
Thanks for the clarity, and without the attititude. You get the points. Best I can do, except for my appreication ...

  - Georgia
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question