Best way to store data in table

Question

Best way to store data in table

vchauhan_me

SSC Rookie

Points: 41
More actions
February 21, 2013 at 4:15 am

#274459

hello,
I have aprox 70,000 words and want to store this data in DB, for this there are two ways as below:
1. add a single row that will have comma separated list of that 70,000 words. So the total records in the table will be 1
2. add all the 70,000 words as single single rows in table so total 70,000 rows in the table
what will be the best way to do this and why the solution is best. 🙂
Please explain... Thanks,
VinC 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

It depends on your business requirement( What are you going to do with this 70,000 words? )

If you intend to handle each word differently by joining them individually to some other table, then its better to store them as 70,000 rows

But as I said, we can't decide without knowing the business requirement.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Hi,

It depends on your requirement. If you to perform any operation on those words later like update etc. then its better you store those 70K words in 70K rows else you can store in a single rows of table with comma separated values.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

vchauhan_me SSC Rookie Points: 41 More actions · Answer 3

My requirement is like i have to search the word from this 70,000 words. so 1st i have to store that in the table then execute select statement with like operator. Also there will not be only 70,000 records it will be increased to may be 70,00,000 + too 🙂

Abu Dina SSChampion Points: 14155 More actions · Answer 4

You may want to consider Full-Text Search if your list is going to grow to 70 million+ words.

From MSDN:

A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

vchauhan_me (2/21/2013)
My requirement is like i have to search the word from this 70,000 words. so 1st i have to store that in the table then execute select statement with like operator. Also there will not be only 70,000 records it will be increased to may be 70,00,000 + too 🙂

In that case its better if you store these rows in 70,000 different rows so that you can do a JOIN

You cannot do a JOIN with the word if you store all the words in a single comma separated row.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

Ok

then my suggestion is to store words in separate rows so that you can perform operation LIKE as you mentioned.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

vchauhan_me SSC Rookie Points: 41 More actions · Answer 7

Thanks for the reply from all of you, but i need the specific reason why separate rows (70,000 rows) are more preferable to store in DB in place of comma separated list, its like i need to search the keyword from the list but that is also done quite easily with comma separated record.

Is there any documentation for such thing then it will be good for me to clear this view.

but thanks for the reply 🙂

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

vchauhan_me (2/21/2013)
Thanks for the reply from all of you, but i need the specific reason why separate rows (70,000 rows) are more preferable to store in DB in place of comma separated list, its like i need to search the keyword from the list but that is also done quite easily with comma separated record.
Is there any documentation for such thing then it will be good for me to clear this view.
but thanks for the reply 🙂

Can you replicate your problem with the help of a CREATE TABLE statement, some sample data( say 5 rows ) and the query you will be running on the table?

It will then be easier for us to give you better suggestions.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Pearce SSCoach Points: 15999 More actions · Answer 9

INSERT INTO PseudoTable VALUES ('Tear,Bike,Monkey')

SELECT * FROM PseudoTable WHERE Item LIKE '%tea%'

1 row returned.

INSERT INTO PseudoTable VALUES ('Tear')

INSERT INTO PseudoTable VALUES ('Bike')

INSERT INTO PseudoTable VALUES ('Monkey')

SELECT * FROM PseudoTable WHERE Item = 'tea'

0 row returned.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 10

Sean Pearce (2/21/2013)
INSERT INTO PseudoTable VALUES ('Tear,Bike,Monkey')
SELECT * FROM PseudoTable WHERE Item LIKE '%tea%'
1 row returned.
INSERT INTO PseudoTable VALUES ('Tear')
INSERT INTO PseudoTable VALUES ('Bike')
INSERT INTO PseudoTable VALUES ('Monkey')
SELECT * FROM PseudoTable WHERE Item = 'tea'
0 row returned.

Is this related to the question in the thread?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

DiverKas SSCrazy Points: 2049 More actions · Answer 11

vchauhan_me (2/21/2013)
Thanks for the reply from all of you, but i need the specific reason why separate rows (70,000 rows) are more preferable to store in DB in place of comma separated list, its like i need to search the keyword from the list but that is also done quite easily with comma separated record.
Is there any documentation for such thing then it will be good for me to clear this view.
but thanks for the reply 🙂

Ok, lets look at the 2 solutions:

1) All words in one row, one field. Well, lets start with a size of the field. 8k is your practical limit, though a blob will work. Page splits will be your bane and functions like PATINDEX and CHARINDEX wont be useful.

2) Each word in a row, single column. Now you have options like Full Text Search or index the column. At least when using the LIKE operator and a right side wildcard you can use the index. And it scales. And with compression, you may be able to reduce your logical reads.

Sean Pearce SSCoach Points: 15999 More actions · Answer 12

Kingston Dhasian (2/21/2013)
Sean Pearce (2/21/2013)
INSERT INTO PseudoTable VALUES ('Tear,Bike,Monkey')
SELECT * FROM PseudoTable WHERE Item LIKE '%tea%'
1 row returned.
INSERT INTO PseudoTable VALUES ('Tear')
INSERT INTO PseudoTable VALUES ('Bike')
INSERT INTO PseudoTable VALUES ('Monkey')
SELECT * FROM PseudoTable WHERE Item = 'tea'
0 row returned.
Is this related to the question in the thread?

Not at all. I normally reply to different threads.

I'm simply pointing out a flaw in searching a string for a string. Matching a string to a string is another matter.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]