Results 1 to 2 of 2

Thread: table Design

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: table Design

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make two tables, give the second one a primary key that is not an identity, but rather, is a foreign key to the first table

    this implements a one-to-many relationship, but you will just use it as a one-to-one relationship

    put the seldomly used fields into the second table

    queries that need fields from both tables will require a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •