Clustering and Constraints in a table

Clustering and Constraints in a table

Post by irfa » Sun, 08 Sep 2002 07:05:18



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
 
 
 

Clustering and Constraints in a table

Post by lindawi » Tue, 10 Sep 2002 01:51:23


irfan,

Quote:> 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?

Right click on a table, select Design Table from the menu,
click the Manage Relationships... button.

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?

In Enterpriase Manager, right click on the database name,
select All Tasks, Generate SQL Scripts...

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.

Clustered index has nothing to do with server clustering.

The clustered index stores rows in the order of the
key values. The leaf level of the index is actually
the data page.

A table can have only one clustered index (but many
nonclustered indexes), so you should pick it wisely.
Think about how the data will be accessed when deciding
on the clustered index. Using the Northwind..Orders table
as an example, if orders are usually accessed by customer,
then these queries will benefit from a clustered index
on the CustomerID. If however, orders are always accessed
by OrderID, then that clustered index is not much help.
A clustered index on OrderID would be more useful.
A clustered index can speed up range queries.

By default, SQL Server creates the primary key as a
clustered index if the keyword "nonclustered" is omitted.
However, a primary key may not always be the best choice
for a clustered index (see above).

Fill factor means that when the index is created, SQL
Server only fills the leaf-level index page to the
percentage indicated, instead of filling it up. In
your example, it means that leaf index pages will be 90%
filled, leaving 10% of the space on the page for future
inserts. If no fill factor is specified, SQL Server
leaves room for one insert on each index leaf page.

Fill factors can be helpful on tables that experience
many inserts in a short period of time. Start out
without specifiying any fill factors and then, after
thorough testing, only use them if there is a
demonstrated benefit.

If you work with fill factors, you need to rebuild
your indexes frequently to restore the free space
on each page.

Linda

 
 
 

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. Web-Informix Interface

3. Need Assistance: Clustering and Constraints in a table

4. P.I Is Boiling Mad

5. Err on loop

6. Table constraints and Column constraints

7. Code in exe file ..

8. Change table check constraint to column check constraint?

9. Changing a table-level constraint to a column-specific constraint

10. Column check constraint vs table check constraint

11. Table constraints vs column constraints

12. Updating tables with non-clustered and clustered primary key indexes