Table design

Question

Table design

sahana

SSCrazy

Points: 2530
More actions
October 26, 2004 at 8:45 am

#61352

CREATE TABLE [dbo].[table1] (
[aaa] [bigint] IDENTITY (10000, 1) NOT NULL ,
[bbb] [int] NOT NULL ,
[ccc] [int] NOT NULL ,
[ddd] [bigint] NOT NULL ,
[eee] [int] NOT NULL ,
[ffff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[gggg] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hhh] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iii] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jjj] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[kkk] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lll] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mmm] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nnnn] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ooo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ppp] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qqqq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rrrr] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssss] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tttt] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uuuuu] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vvvvv] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wwwww] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xxxxx] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[yyyyy] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zzzzz] [int] NULL ,
[abc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[def] [datetime] NULL ,
[ghi] [datetime] NOT NULL ,
[jkl] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mno] [bigint] NULL
) ON [PRIMARY]
I have created a table with above column width. The rowsize is more than 8kb.And the table holds millions of rows of data. So is it a correct way of designing the table?
Or how can I redesign this table.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

How about posting the DDL for the real table?

What is it representing? Is it normalized? Can you split up the data in any way based on some business rules within the domain? Since I don't know what an aaa or bbb are, the only suggestion I can make is possibly storing the uuuuu as TEXT...

--
Adam Machanic
whoisactive

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Depending on the data you should rewrite the table and make sure normalized, or setup a seperate table to split data to ensure 8k never an issue. Although with variable length columns you can go over 8K in design it is still a bad thing to do. If values come in and go over the 8k limit for the row then it will throw an error and not allow insert.

sahana SSCrazy Points: 2530 More actions · Answer 3

Unfortunately the table cannot be normalised. all the columns should be in one table.So if the rowsize is greater than 8k is it going to affect the performance of my queries and stored procedures on this table?

Thanks.

Adam Machanic SSCoach Points: 15370 More actions · Answer 4

Test it and tell us. You haven't posted sample data or queries so there's no way to answer that question... If the rowsize is greater than 8k, you won't have that row in the table, so that's not really a problem

--
Adam Machanic
whoisactive

Antares686 SSC Guru Points: 125444 More actions · Answer 5

You can always split the table into two and join in a view to cover lookups and since using an SP you will not have any issue being able to insert in one for an identity column get the value and insert the rest into the other. As for if the rowsize is greate than 8 k, it will blow up any record trying to fill the row beyond 8k and do nothing else. We had one like this until I made the person go thru and consolidate some columns and add a control code to deal with what the record represented.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 6

Hi sahana,

I prefer application reliability to query performance. I would first ensure that there are no time bombs in the DB (like having a table with row size > 8K) that may explode ocassionally, and after that I would care for performance.

If there are so strong reasons for keeping all these columns into a single table, why don't you split the table into several tables, use a view to join them and put INSTEAD OF triggers on that view, to distribute INSERTs, UPDATEs and DELETEs accross those tables ?

I hope this helps,

Goce Smilevski.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Just out of curiosity:

Is it for an OLAP or OLTP application?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65141 More actions · Answer 8

I did read somewhere in all that Microsoft bumph online that Microsoft's solution for this type of problem is to split the table into smaller tables and join using a common key (identity etc) as mentioned above. They (Microsoft) state that the overhead of doing this in performance (doing joins to bring data together) is generally not an issue.

But then that is Microsoft spin

If as you say there is a risk of the data going beyond 8k then you have no choice but to split the table or suffer the consequences of a system that sometime in it's lifetime will fail and be unusable.

Far away is close at hand in the images of elsewhere.
Anon.

Johnny Doyle Right there with Babe Points: 745 More actions · Answer 9

Can't you use 'text' or 'ntext' columns? This'll allow you to have all of your large character columns in a single table, but of course reading/writing the data will be a little more hassle.

HLGEM SSC Journeyman Points: 94 More actions · Answer 10

Since you are asking this wquestion now after you have placed millions of records into the table, what prompted it? THe circumstances you now face could have bearing on our best advice to you.

I'm of the split the table into more tables camp. These tables would have a one to one relationship. YOu could create a view to see all of your fields in one place. Give it the name your current table has and then all your selects will still work. If you change the structure though, you will probably need to readdress your insert and update procedures for the table.

When you have a table defined with too many characters it simply won't insert a record when the time comes that you need more characters in a record. Murphy's Law being what it is, this will happen when the user needs the record inserted urgently and then you have a major nightmare. Best to design so that your application won't unexpectedly break.

Now the exception to this is when you know based on your business rules that you will never exceed the limit. This could be if you have circumstances whereby if col c has a value columns d, e and f by definition must be null. It is best if you have table constraints and triggers to ensure the business rules are not violated.

The idea to change some of these long fields to text certainly has merit as well. Again you may have to revisit your insert and update procedures especially if you want to continue to limit the number of characters in these fields.