Need Assistance: Clustering and Constraints in a table

Need Assistance: Clustering and Constraints in a table

Post by irfa » Sun, 08 Sep 2002 06:56:23



1.  The tables for which the constraints (e.g. pk, fk) are
created.  
How can I check them in the enterprise manager?  I am able
to see the columns in the tables and their indexes, but
not the constraints. Would you know how I can check them?

2.  My customer's DBA purged their all tables from a db
instance into a txt file, such as the one that I attach
here.  Would you happen to know how to do this as well?

3.  For the constraint which looks like:
CONSTRAINT [pk_Ptnr_PtnrId] PRIMARY KEY  CLUSTERED
        ([PartnerID]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]

What implications do CLUSTERED and FILLFACTOR have on the
key itself, given that the db server is set in a clustered
db environment.

  TN46_DB_DEFS.txt
34K Download
 
 
 

Need Assistance: Clustering and Constraints in a table

Post by Peter A. Schot » Wed, 11 Sep 2002 02:10:17


Inline answers:


> 1.  The tables for which the constraints (e.g. pk, fk) are
> created.  
> How can I check them in the enterprise manager?  I am able
> to see the columns in the tables and their indexes, but
> not the constraints. Would you know how I can check them?

From what it sounds like, your best bet would probably something like
sp_helpconstraint in Query Analyzer.  I believe the syntax is
sp_helpconstraint 'Tablename'

Quote:> 2.  My customer's DBA purged their all tables from a db
> instance into a txt file, such as the one that I attach
> here.  Would you happen to know how to do this as well?

Sounds like he just scripted the database using Enterprise Manager or some
similar tool.  If you've got Enterprise Manager, right-click the database,
select All Tasks -> Generate SQL Scripts.  You'll have to play around to get
the settings you want, but this usually works for me.

Quote:> 3.  For the constraint which looks like:
> CONSTRAINT [pk_Ptnr_PtnrId] PRIMARY KEY  CLUSTERED
>    ([PartnerID]
>    ) WITH  FILLFACTOR = 90  ON [PRIMARY]

> What implications do CLUSTERED and FILLFACTOR have on the
> key itself, given that the db server is set in a clustered
> db environment.

Fillfactor - how full the index should be when it's created
Clustered - for indexes, this refers to whether or not the table should be
ordered by the column(s) in this index physically.  You're allowed one
clustered index per table (as that will determine the sorting of the table).
Non-clustered indexes just contain pointers to the current location of the
data.  Most of the time, I've seen clustered indexes correspond to the primary
key, but not always.  I'd recommend picking up a good book on DB design for
more details.

Of course, if you're curious about the workings internally to MS SQL Server,
I'd recommend "Inside SQL Server 2000" by Delaney from MS Press.  It's not a
beginner's book, but it does go into pretty good detail inside the database
engine itself.  If you're still curious, check out


there.

-Pete

 
 
 

1. Need Assistance: Clustering and constraints in a table

1.  The tables for which the constraints (e.g. pk, fk) are
created.  
How can I check them in the enterprise manager?  I am able
to see the columns in the tables and their indexes, but
not the constraints. Would you know how I can check them?

2.  My customer's DBA purged their all tables from a db
instance into a txt file, such as the one that I attach
here.  Would you happen to know how to do this as well?

3.  For the constraint which looks like:
CONSTRAINT [pk_Ptnr_PtnrId] PRIMARY KEY  CLUSTERED
        ([PartnerID]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]

What implications do CLUSTERED and FILLFACTOR have on the
key itself, given that the db server is set in a clustered
db environment.

  TN46_DB_DEFS.txt
34K Download

2. SQL System DSN reverts to Windows authentication mode

3. Need Assistance: Clustering and Constraints in a table

4. Help! FileMaker/Word conversion

5. Need Assistance: Constraints in a table

6. how to retrieve the first 10 rows of an sybase sql table ?

7. Need Assistance: Constraints in a table specific

8. Temp Table???

9. Clustering and Constraints in a table

10. Need some assistance creating a table in an 8i database

11. REPOST - Assistance needed with clearing out Child tables

12. Urgently need assistance, Corrupt table!

13. Assistance needed with creating tables in oracle.