Table Size

[TheConeHead]

Is there a suggested way to handle a large table... I have a need for about a 3,000 column table, I could break it into smaller tables I guess, but does anyone know why a 3,000 column table would not work- would it be real slow?

 

[MeanGreen]

This was taken directly out of BOL:

SQL Server can have as many as two billion tables per database and 1,024 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.


So the maximum number of columns is 1024.

Hope this helps.

 

[SQLSister]

PAy attention to that part about the number of bytes per rwo too, it would be very hard to get 1024 columns and not violate that size limit either. Just curious, why on earth do you need 3000 columns?

 

[TheConeHead]

converting an excel spreadsheet to web based has like 300 rows and 10 columns for info to be filled out in....

 

[MeanGreen]

For your conversion, you only need a table with 10 columns. Then the 300 rows would be imported into the table just like they are in Excel. No need to create 3000 columns.

Hope this helps!

 

[TheConeHead]

No that would not work... imagine an excel spread sheet with 300 rows of stuff like "carnival" then 10 columns with stuff like "fun", "practical",... and so on and you had to enter a rating for each... that would be 3000 entries (300 items carnival, circus, ... and 10 things to evaluate for each fun, practical... = 3000 entries) a row in the db qould represent 1 persons entries- each person would be inputting 3000 entries...

hope this help it make more sense

 

[MeanGreen]

I guess I would need a few more details. Sounds like your current Excel design does not meet your business requirements. If you are redesigning, you really need to ask a lot of questions. For instance, "Will every user have to evaluate 300 items?" "Are there ALWAYS 10 things to evaluate i.e. fun, practical...?" This is really a database design issue instead of an excel to MS Sql issue, since it sounds like you are trying to alter the input data thought process. That is just my opinion.
For a basic design, you might create 1 table with 2 columns called "Evaluate". Column 1 would be an integer primary key and column 2 would be the choice. Then create another table called "Stuff" with 3 columns. Column 1 would be item i.e. carnival, circus, etc. as a primary key and column 2 would be a reference to Evaluate.column1 for the evaluation. Column 3 could be the UserID of the person who provided the evaluation. Finally, you would create a table called "Users" that had 2 columns. Column 1 would be the UserID column and be the primary key on that table. Column 2 would have the users name. This is just a basic design off the top of my head, so I have not fully tested or designed this thought process. I am just trying to give you some input that might help you get started in the right direction for your design.

Evaluate
PK EV_ID Integer
Choice Varchar(25)

Stuff
PK Item Varchar(25) - integer is faster for lookups
EV_ID Integer - references Evaluate
User_ID Integer - references Users

Users
PK User_ID Integer
User_Name Varchar(50)

Hope this helps!

 

[SQLBill]

Why not three tables, somthing like this:

Rater
RaterID, FirstName, LastName
ex. 1 SQL Bill

ItemRated
ItemId, ItemName, ItemDescription
ex. 1 Carnival Annual Local

Rating
RaterID, ItemID, AreaRated, GivenRating
ex. 1 1 FUN 8

-SQLBill

 

[TheConeHead]

Excellent- that sounds like the way I want to go- I am new to sql so could you spell it out for me a little more? how would I "link" these tables. Users would not fill out all the sections but need to have the option to fill them all out. It is taking the place of the said excel spreadsheet and instead of emailing everyone their spreadsheet and having them email it back filled out and ending up with 100's of spreadsheets, this web based form would take its place... thanks for any more detail

 

[iowtech]

Try this:

Create:

An 'item' table (in which you would put carnival, fair etc)

A 'description' table ( in which you would put fun, practical etc)

A 'user' table in which you define each user and give them a unique ref (eg 1. Ted, 2. John etc)

Then create an instance table which would contain each instance. Eg:

user: 1
item: carnival
description: fun
value: 10

user: 1
item: carnival
description: practical
value: 8

Get your referntial integrity right and you're away.

 

[iowtech]

Sorry for the duplication, I guess everyone had the same idea and posted at the same time!

 

[clapag22]

How about this....

Three tables:

Table1 ("Stuff&quot:
StuffID (1, 2, 3, 4, etc)
StuffName (Carnival, etc)

Table2 ("Ratings&quot:
RatingID (101, 102, 103, etc)
RatingName (fun, practical, etc)

Table3 ("StuffRating&quot:
StuffID
RatingID
Rating (1 - 10)

Then do a pivot table in Excel/Crystal/Access to get the data out...

 

[TheConeHead]

Could somone spell it out more for me: how would it work exactly? How would one table reference another? etc....

 

[MeanGreen]

Using my earlier table structure:

Evaluate
PK EV_ID Integer
Choice Varchar(25)

Stuff
PK Item Varchar(25) - integer is faster for lookups
EV_ID Integer - references Evaluate
User_ID Integer - references Users

Users
PK User_ID Integer
User_Name Varchar(50)

The quick and dirty query would be:

select * from
Evaluate Join Stuff
on evaluate.EV_ID = Stuff.EV_ID
join Users
on Stuff.User_id = Users.User_ID


You might try creating the tables and populate them with data. Then play with it and see what you get.

Hope this helps.

 

[TheConeHead]

I think my puny little brain is finally starting to grasp the concept- much thanks....