How to Reindex a Heap Table
In the SQL database context, a heap table is a table that organizes data without a clustered index. This means that the data is not sorted and is instead stored in an order based on their nonclustered keys. These keys, in turn, are used internally by SQL to retrieve the data. Data rows are therefore stored randomly throughout the heap table. As data rows are added, modified and removed from a heap table, however, the underlying data structure may become fragmented. To avoid this, you should periodically reindex the heap table to improve the database's performance.
Other People Are Reading
Things You'll Need
- PC with Microsoft AdventureWorks 2008R2 Sample Database and Microsoft SQL Server 2008 installed
Instructions
-
-
1
Click the “Start” menu and select “All Programs.” A menu will appear. Select “Microsoft SQL Server 2008 R2” to open a sub-menu. Click “SQL Server Management Studio” from this sub-menu. The “SQL Server Management Studio” program will launch.
-
2
Log in to the “SQL Express.” This is found at the “SQL Server Management Studio” login window.
-
-
3
Right-click “Databases” in the “Object Explorer” located in the left-hand column to open a menu. Select “Attach” from this menu. Browse to where you installed the AdventureWorks sample database and select the file with the “mdf” extension.
-
4
Click “Database Engine Query” on the “SQL Server Management Studio” toolbar. The “Query Editor” will open as a result.
-
5
Type the following code into the “Query Editor:”
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO -
6
Click the “Execute” button on the “SQL Server Management Studio” toolbar to rebuild the index.
-
1
References
Resources
- Photo Credit Creatas/Creatas/Getty Images
