Table Design how to reduce no.of column?

Question

Table Design how to reduce no.of column?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
October 31, 2011 at 5:40 am

#259274

Hi,
Energy Monitoring System- application
There are 3000 Energy meter fixing at power plant, through this application i want to monitor meter reading values.
SQL SERVER 2008.ent.prise edition.
For the Table Design, How to design this table with 3000 meter?
Or create table with 500 column, by this way need to 6 table each table column no.of 500.
Could anyone give suggestion me, is there any alternative way?
thanks
ananda
October 31, 2011 at 7:38 am

This was removed by the editor as SPAM
November 1, 2011 at 2:04 am

This was removed by the editor as SPAM
November 1, 2011 at 2:09 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

okbangas SSChampion Points: 11773 More actions · Answer 1

Completely different I would say, have a table with MeterId, Time and MeasuredValue. You will (over time) get lots of rows, but with proper indexing for your queries this should not be an issue.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Hi okbangas, thanks for reply..

Total 6 Table to be create with as below format, so each table have 500 columns.

Column1 - Timestamps ( for storing date value)

column2 - Meter_Id1 ( for storing current reading value)

..........

.............

column500 - Meter_Id500 ( for storing current reading value)

For the General question. In SQL 2008 totally how many column can be created with one table?

ananda

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

have you considered the following....takes a few seconds to run.

USE tempdb

GO

--===== Conditionally drop the tables

IF Object_id('TEST_METER', 'U') IS NOT NULL

DROP TABLE dbo.TEST_METER;

SELECT TOP 1000000 ---- NOTE 1 MILLION ROWS

Meter_ID = CAST(Abs(Checksum(Newid())) % 3000 + 1 AS INT),

Meter_Reading = CAST(Rand(Checksum(Newid())) * 9 + 1 AS DECIMAL(5, 2)),

Meter_DateTime = CAST(Rand(Checksum(Newid())) * 1096 + 40177 AS DATETIME) --- three years

INTO TEST_METER

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi Campbell, Thanks for your reply and fesible table solution, minimized lot of table by using 3.5 normal form.

Create MeterMonitor table data will be storing huge records over time.

PointInTime DATETIME NOT NULL,

This field Frequently using at WHERE clause for reporting purpose and searching data, so this field can create PRIMARY KEY for CLUSTRED Index,

Create NONCLUSTRED index Instead of creating PRIMARY KEY on date filed column. Please suggestion me.

thanks

ananda