Solved

Best sql data type to store comma delimited lists

Posted on 2006-11-11
11
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 143

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 143

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 143

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

635 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