Differences between sqlserver 2000, 2005 and 2008 versions

Introduction

The table shows the differences between sqlserver different versions. This can be helpful in interview questions.

Sql server 2000	Sql server 2005	Sql server 2008
Query analyser and enterprise manager are seperate	Both are combined into SSMS(sql server management studio)	Both are combined as SSMS
No XML datatype used	XML datatype is introduced	XML datatype is used
We can create maximum of 65,535 databases only	We can create maximum of (2^20)-1 databases	We can create maximum of (2^20)-1 databases
Nil	Exception handling	Exception handling
Nil	Varchar(max) datatype	Varchar(max) datatype
Nil	DDL triggers	DDL triggers
Nil	Database mirroring(sql DBA)	Database mirroring
Nil	Row number function for paging	Row number function for paging
Nil	Table fragmentation	Table fragmentation
Nil	Full text search	Full text search
Nil	Bulk copy insert	Bulk copy update
Nil	Can't encrypt	Can encrypt the entire database introduced in 2008
Can't compress the tables and indexes	Can compress tables and indexes(introduced in 2005 SP2)	Can compress tables and indexes
Datetime datatype used for both date and time	Datetime datatype used for both date and time	Date & time are seperately used for date & time datatype, geospatial and timestamp with internal timezone is used
No varchar(max) or varbinary(max) is available	varchar(max) and varbinary(max) is used	varchar(max) and varbinary(max) is used
No table datatype is introduced	Table datatype is introduced	Table datatype is introduced
No SSIS is included	SSIS is started using	SSIS available in this version
CMS(centralized management server) is not available	CMS is not available	CMS is introduced
PBM(policy based management) is not available	PBM is not available	PBM server is introduced