Solved

Best sql data type to store comma delimited lists

Posted on 2006-11-11
11
620 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
Need help with T-SQL on SQL Server 2014 9 37
HTML <font style="color:red"> 9 32
SQL Group By Question 4 20
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.
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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