Solved

Best sql data type to store comma delimited lists

Posted on 2006-11-11
11
621 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

685 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