#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    18
    Rep Power
    0

    Database Data Types Question


    Quick question, I will probably sound like a newb...but I don't have any good reference for these kind of questions.

    I want to store a rather long piece of text in one of my tables in Microsoft SQL Server 2000. I looked over all the data types, char, varchar, text, ntext, and none of them seem to be able to hold this piece of text. The piece of text, according to Word, is 7,185 characters long, counting spaces etc. Is there a good way to store this in a database field? Or am I doing something stupid? If so, what's the alternative? Thanks.
  2. #2
  3. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    15
    i am not that familiar with SQL server, however i think that Oracle could handle this (however don't quote me). one solution might be to store the data in text files and the column that was supposed to have the data in it can now just have the name of the text file where the data resides. since the data is so large, this might be useful since you were probably not going to index the column anyway.
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    I think that ntext, text and image should be the answer.
    Note ntext A Unicode text field that can hold up to 1,073,741,823 characters. These fields cannot be indexed or used in a WHERE clause.
    Last edited by pabloj; July 24th, 2002 at 10:37 AM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    18
    Rep Power
    0
    That's interesting because I tried to copy and paste something that has substantially less characters into the field when it was of type ntext and it would not let me.

    The Text File sounds like a good thing although I would have wanted to have users be able to search in the field where all the text would reside...

    But according to you, pabloj, it won't work? I couldn't search for a specific word using a where clause even if I was able to put all the text in there?
  8. #5
  9. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    15
    well, the hard part comes at indexing something that large. a search on each row for this field would take a long time and your queries would be very expensive. if you want to search for key words in this field, then you might want to consider redesigning your table structure and/or relationships. you could have 1 record id join to another table that has that record id many times for different key words. then you could have another table that has an id and the complete sentence (or your large amount of data). this table could join to the bridge as well, seeing as how 1 sentence would have many key words as well.
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    Why not just store a pointer to your file?
    - Walt

    Will code for food.
  12. #7
  13. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    15
    Why not just store a pointer to your file?
    This was already posted...also he explained that he wanted to be able to have some search functionality using the where clause.
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    The problem in trying to seach something so large is that it's not efficient. It would be better to add some keywords to search on.
    - Walt

    Will code for food.
  16. #9
  17. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    15
    The problem in trying to seach something so large is that it's not efficient. It would be better to add some keywords to search on.
    hrm...sounds a lot like...

    well, the hard part comes at indexing something that large. a search on each row for this field would take a long time and your queries would be very expensive. if you want to search for key words in this field, then you might want to consider redesigning your table structure and/or relationships.
    please read the posts first
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    NJ, USA
    Posts
    91
    Rep Power
    13
    So, basically, you got it covered. I guess my work is done here.
    - Walt

    Will code for food.

IMN logo majestic logo threadwatch logo seochat tools logo