Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 24th, 2002, 01:47 AM
ChuckieT ChuckieT is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2002
Posts: 18 ChuckieT User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 33 m 59 sec
Reputation 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.

Reply With Quote
  #2  
Old July 24th, 2002, 08:57 AM
tron's Avatar
tron tron is offline
SwollenMember
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2000
Location: the master control
Posts: 264 tron User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 h 14 m 57 sec
Reputation Power: 13
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.

Reply With Quote
  #3  
Old July 24th, 2002, 09:34 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 8th Plane (8500 - 8999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,527 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 1 Day 3 h 19 m 49 sec
Reputation Power: 537
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 09:37 AM.

Reply With Quote
  #4  
Old July 24th, 2002, 06:34 PM
ChuckieT ChuckieT is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2002
Posts: 18 ChuckieT User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 33 m 59 sec
Reputation 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?

Reply With Quote
  #5  
Old July 25th, 2002, 09:39 AM
tron's Avatar
tron tron is offline
SwollenMember
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2000
Location: the master control
Posts: 264 tron User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 h 14 m 57 sec
Reputation Power: 13
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.

Reply With Quote
  #6  
Old July 25th, 2002, 03:14 PM
Waltjp's Avatar
Waltjp Waltjp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2002
Location: NJ, USA
Posts: 91 Waltjp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 1 m 8 sec
Reputation Power: 11
Why not just store a pointer to your file?
__________________
- Walt

Will code for food.

Reply With Quote
  #7  
Old July 25th, 2002, 03:40 PM
tron's Avatar
tron tron is offline
SwollenMember
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2000
Location: the master control
Posts: 264 tron User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 h 14 m 57 sec
Reputation Power: 13
Quote:
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.

Reply With Quote
  #8  
Old July 25th, 2002, 07:49 PM
Waltjp's Avatar
Waltjp Waltjp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2002
Location: NJ, USA
Posts: 91 Waltjp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 1 m 8 sec
Reputation Power: 11
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.

Reply With Quote
  #9  
Old July 25th, 2002, 08:28 PM
tron's Avatar
tron tron is offline
SwollenMember
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2000
Location: the master control
Posts: 264 tron User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 h 14 m 57 sec
Reputation Power: 13
Quote:
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...

Quote:
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

Reply With Quote
  #10  
Old July 26th, 2002, 07:30 AM
Waltjp's Avatar
Waltjp Waltjp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2002
Location: NJ, USA
Posts: 91 Waltjp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 1 m 8 sec
Reputation Power: 11
So, basically, you got it covered. I guess my work is done here.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > Database Data Types Question

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap