Snippet Name: Add_Compressed_PK_Index

Tags:

Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:32:05

Dependencies:

DBA Notes:

Sample Output:

Title: Add Compressed PK Index
Author: user
In SQL Server, i have this command  to add a new primary key to a heap table, but i want the index to be compressed as well. cna i do it by modifying this commnad? ALTER TABLE [GYMT_ODB].[GYMT_Appointment_User] ADD CONSTRAINT PK_VI_GYMT_Appointment_User_ForAppointment PRIMARY KEY CLUSTERED (ForAppointment,ForUser);


Author: ChatGPT
Yes, you can modify your `ALTER TABLE` statement to include **data compression** for the newly created **clustered primary key index**. In SQL Server, you do this by using the `WITH (DATA_COMPRESSION = PAGE)` or `ROW` option within the `WITH` clause.

### Modified Command with Compression:
```sql
ALTER TABLE [GYMT_ODB].[GYMT_Appointment_User] 
ADD CONSTRAINT PK_VI_GYMT_Appointment_User_ForAppointment 
PRIMARY KEY CLUSTERED (ForAppointment, ForUser)
WITH (DATA_COMPRESSION = PAGE);
```

### Explanation:
- **`DATA_COMPRESSION = PAGE`**: Enables **page-level compression**, which includes row-level compression and additional compression of repeated values.
- **`DATA_COMPRESSION = ROW`**: Enables only **row-level compression**, reducing storage by storing fixed-length data types more efficiently.

### Considerations:
- **Compression benefits**: Can save space and improve performance for read-heavy workloads.
- **CPU overhead**: Can increase CPU usage during inserts/updates.
- **Enterprise Edition required**: `DATA_COMPRESSION` is only available in **SQL Server Enterprise Edition** (and some Developer/Evaluation editions).

Would you like me to check if compression is already enabled for your table? You can use:
```sql
SELECT 
    i.name AS IndexName, 
    p.data_compression_desc
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('[GYMT_ODB].[GYMT_Appointment_User]');
```
Let me know if you need further modifications! ????