SQL Server 2005 - Database Engine Enhancements

Several new database-specific enhancements have been added to SQL Server 2005. These changes are focused primarily on the database storage engine. The following are some of the most important enhancements:

. Instant file initialization—New or expanded database files are made available much faster now because the initialization of the file with binary zeros is deferred until data is written to the files.

. Partial availability—In the event of database file corruption, the database can still be brought online if the primary filegroup is available.

. Database file movement—You can now use the ALTER DATABASE command to move a database file. The physical file must be moved manually. This feature was available in SQL Server 2000, but it only worked on tempdb.In addition, many new table-oriented enhancements are available with SQL Server 2005. This includes features that define how the data in the tables will be stored in the database.

The following are two of the key enhancements:
. Large rows—SQL Server 2005 now allows for the storage of rows that are greater than 8060 bytes. The 8060-byte limitation that existed with SQL Server 2000 has been relaxed by allowing the storage of certain data types (such as varchar and nvarchar) on a row overflow data page.

. Stored computed columns—Computed columns that were calculated on-the-fly inprior versions can now be stored in the table structure. You accomplish this by specifying the PERSISTED keyword as part of the computed column definition.