[dba-SQLServer] Carving out one table

John,

For creating an index...

CREATE INDEX tblRatingScoreRange_IX01 ON dbo.tblRatingScoreRange
(
        LowScore                       ASC
)
go

Primary key constraint...

ALTER TABLE dbo.tblRatingScoreRange
        ADD CONSTRAINT tblRatingScoreRange_PK PRIMARY KEY CLUSTERED (
               RatingScoreRangeID ASC)
go

Unique index...

CREATE UNIQUE INDEX tblRatingScoreRange_UI01 ON dbo.tblRatingScoreRange
(
        LowScore                       ASC,
        HighScore                      ASC,
        RatingScoreText                ASC
)
go

Allow null...

ALTER TABLE dbo.tblRatingScoreRange
     LowScore tinyint NULL

(I think this is correct.  Try it on a small table first.)


As far as carving out the data, yes it is a great idea.  It is what
data warehouses are made out of.  If you want help modeling it, let
me know.  At Waste Management, I was modeling databases that were
between 5 and 10 TB for their data warehouse.

Robert

At 03:18 AM 11/3/2006, you wrote:
>Date: Fri, 3 Nov 2006 01:26:56 -0500
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Subject: [dba-SQLServer] Carving out one table
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <008001c6ff11$0febdb00$657aa8c0 at m6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>It took the system 56 minutes to carve just the address fields (and the PK)
>out into a new table - 64 million records.  I have a second query running
>right now to carve out the boating.  More fields.  I'm going to bed so I
>will tell you the time to do this tomorrow.
>
>I need to know how to run a query to add an index so that I can see the time
>required to accomplish this.  I have been going in to table design to do
>this and it "just goes away" and comes back when it is done.  I can't really
>tell how long it takes.
>
>Man am I going to learn SQL!  Alter table, add constraint, primary key...
>And I need to find the syntax to make a field non-nullable.
>
>Which brings up an interesting point.  These tables are being created with a
>"PK" which is also an "FK", essentially it is a one to one child table to
>the master address table.  I will probably eventually create the
>relationships, though I am not sure whether they are truly necessary.  The
>master table is never SUPPOSED to have records deleted.
>
>Well, stuff for tomorrow.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com