The Storage Engine

by Kalen Delaney

Microsoft Press

204 Inside Microsoft SQL Server 2005: The Storage Engine among other things—the name, object ID, and ID of the schema containing the new table is available through the sys.tables view. Remember that the sys.tables view inherits all the columns from sys.objects (which shows information relevant to all types of objects) and then includes additional columns pertaining only to tables. The sys.columns view shows you one row for each column in the new table, and each row will contain information such as the column name, data type, and length. Each column receives a column ID, which initially corresponds to the order in which you specified the columns when you created the table—that is, the first column listed in the CREATE TABLE statement will have a column ID of 1, the second column will have a column ID of 2, and so on. Figure 6-5 shows the rows returned by the sys.tables

(2009)

eBook €30.77

by Kalen Delaney, Paul Randal, Kimberly Tripp, and Conor Cunnin...

Microsoft Press

Chapter 6 Indexes: Internals and Management 365 Dropping Indexes One of the biggest differences between managing indexes created using the CREATE INDEX command and indexes that support constraints is in how you can drop the index. The DROP INDEX command allows you to drop only indexes that were built with the CREATE INDEX command. To drop indexes that support constraints, you must use ALTER TABLE to drop the constraint. In addition, to drop a PRIMARY KEY or UNIQUE constraint that has any FOREIGN KEY constraints referencing it, you must first drop the FOREIGN KEY constraint. This can leave you with a window of vulnerability if your goal is to drop indexes and immediately rebuild them, perhaps with a new fillfactor. Although the FOREIGN KEY constraint is gone, an INSERT statement can add a row to the table that violates your referential integrity. One way to avoid this problem is to

(2009)

eBook €36.91