Need Assistance: Clustering and constraints in a table

Need Assistance: Clustering and constraints in a table

Post by irfa » Sun, 08 Sep 2002 06:54:47



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 Farooq Mahmud [M » Sun, 08 Sep 2002 22:24:05


1. To view the PK/FK relationships in Enterprise Manager, you can create a
Diagram for the database. It is probably easier to see the PK/FK
relationships in Query Analyzer by running sp_help 'table name' .

2. The text file you attached is a script of all the tables in a particular
database. This can be done using Enterprise Manager as follows:

1. Right-click a database. Choose All Tasks -> Generate SQL Script.
2. Click Show All
3. Select the objects you want to script.
4. Click the Formatting and Options tab to set additional scripting
parameters.
5. Click OK and save the file.

3. CLUSTERED and FILLFACTOR has nothing to do with a SQL Server/Windows
cluster rather how the indexes in a table are structured. The CLUSTERED
keyword shows that the index is clustered, meaning that  the physical order
of the rows in the table is the same as the logical (indexed) order of the
key values. In other words, the lowest level of the tree are actually the
data pages of the table itself. This is why there can be only one clustered
index per table. On the other hand, a NONCLUSTERED index, the lowest level
of the tree contains a pointer to that tells SQL Server where to find the
data corresponding to that index key. Since a non-clustered index does not
affect the way data pages are organized, multiple non-clustered indexes can
exist for a given table.

FILLFACTOR specifies a percentage that indicates how full SQL Server should
make the leaf level of each index page during index creation. This
percentage is no maintained. It is based on the data already present when
the index is built.

SQL Server 2000 Books Online Topic "Create Index"
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsql...
_create_64l4.asp) has more information reagrding the CLUSTERED and
FILLFACTOR keywords.

Hope this helps,
Farooq Mahmud [MS SQL Support]

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

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. WHICH INFORMIX VERSION??

3. Remembering form positions

4. Need Assistance: Constraints in a table specific

5. Is it possible to read a SGA's memory architecture ?

6. Need Assistance: Constraints in a table

7. Why did they take away the good old Alias in Paradox?

8. Clustering and Constraints in a table

9. Urgently need assistance, Corrupt table!

10. REPOST - Assistance needed with clearing out Child tables

11. Packing Tables - Need a little assistance

12. Assistance needed with creating tables in oracle.