Solved

Best sql data type to store comma delimited lists

Posted on 2006-11-11
11
617 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now