Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 30 April 2013

Differences between sqlserver 2000, 2005 and 2008 versions



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