SQL Server 2005 offers a way to easily and consistently manage large tables and indexes through partitioning, which allows you to manage subsets of your data outside of the active table. This provides simplified management, increased performance, and ab...

**Data Collections** The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational databas...

When Table Column is dropped, we can reclain space usning DBCC CLEANTABLE; DBCC CLEANTABLE ('DBNAME','TABLENAME') TABLENAME is the table whose column is deleted. **DBCC SHRINKDATABASE or DBCC SHRINKFILE** can also be the options but shrink...

**1. GETDATE()** Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. It is a nondeterministic function. **2. SYSDATETIME()** Return...

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete re...

Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such...

**Fill Factor** The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reser...

**Database mirroring** Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-log...

**Piecemeal Restore** In SQL Server 2005 and later versions, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore. Piecemeal restore involves a series of restore sequences, st...

**Index Enable / Disable** When ever an index is created, it is Enabled by default. To Disable an index we use; ALTER INDEX <IndexName> on <schema>.<tableName> DISABLE To Enable an index we use; ALTER INDEX <IndexName> on <schema>...