to many columns or to many tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

to many columns or to many tables

Author

Topic

smallrinilady
Starting Member

4 Posts

Posted - 2007-02-06 : 13:46:51

my supervisor is trying to help move our existing database in IBM U2 into SQL Sever

he has a file/table that as 12001 columns,

1 column is the key

300 columns are different field errors that can be checked true or false

and for each of those 300 columns there are three extra columns where extra information can be stored

is there a size limitation when it comes to columns in a SQL table?

what is an efficient way of doing this?

create one giant table with 12000 columns?

create 300 tables, each table associated with a error association? which will then need to be joined?

any suggestions? comments? tips?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 13:52:44

MS SQL Server can only have 1024 columns in a table.

1)
Create one table with the 300 "field errors that can be checked true or false". I would add an IDENTITY column as PRIMARY KEY.

Then I would have a second table to keep all the extra information.

2)
Create one table with the 3 columns; IDENTITY column as PRIMARY KEY, "column name" column and value column.

Then I would have a second table to keep all the extra information.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 14:35:43

Was that a mainframe database?

Smacks of a VSAM Programmer trying to build a database

I would convert it to



CREATE TABLE Error_Log (
		[id]		int		IDENTITY(1,1)
	, 	ErrorType	varchar(255)
	,	Status		bit)

CREATE TABLE ErrorLogProperties (
	, 	[id]		int
	,	PropertyType	varchar(255)
	,	PrpoertyValue	varchar(1000))

And you may or may not need the bit field?

Existance would be a yes, non existanece would be a no

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-02-06 : 16:06:37

"is there a size limitation when it comes to columns in a SQL table?"

I believe it is 1,024 columns / table

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources