Its very difficult to write what all new in Sql server 2005, almost everything is new... the complete engine has changed and great improvement.each point require a complete chapter but I try to make it simple....
| Sql Server 2000 | Sql Server 2005 | |||||||||
| Server | Good | Major Redesign/changes in the architecture | ||||||||
| Manageability | Enterprise manager/ Query Analyzer | Management studio | ||||||||
| Memory | Hot add memory | |||||||||
| NA | NUMA/ Sql OS | |||||||||
| NA | Resource database new | |||||||||
| Torn page | Torn page and Checksum (included for backup restore as well) | |||||||||
| Improve in Status of database (suspect…) | ||||||||||
| Access to system tables/view | Compatibility view,Catalog view,DMV and DMF | |||||||||
| Max 16 instances | Max 50 Instances | |||||||||
| Could not get idea when DBCC will complete | DBCC Check* progress report | |||||||||
| Max size of rows 8k | Row can be longer then 8k (8060) - varchar | |||||||||
| Not possible | Profiler and Perfmon can combine | |||||||||
| Availability | NA | Database Mirroring* | ||||||||
| Failure clustering | Improved and support AS/Replication and max nodes is 8 | |||||||||
| NA | Database snapshot* | |||||||||
| recovery require redo and undo | FAST Recovery -database is available after redo* | |||||||||
| NA | Restore with continue error | |||||||||
| NA | DAC | |||||||||
| Database is not accessible/responsive | Online Index and Restore* | |||||||||
| All files required | backup/restore - Availability of Primary file will make db available | |||||||||
| Full text search was separate | Backup/Restore includes Fulltext search | |||||||||
| Full and Tlog Backup can not run together | Full and Tlog Backup can be run together | |||||||||
| Ok | Restore Verify improved with checksum … | |||||||||
| Attach with rebuild log | ||||||||||
| Parallel index operation (MaxDOP) | ||||||||||
| Replication…. | New -Peer to Peer replication | |||||||||
| Scalability | NA | New Replication Monitor separate tool | ||||||||
| NA | Table and index partition* | |||||||||
| NA | Snapshot Isolation | |||||||||
| Support for 64 bit system | ||||||||||
| Index on xml | ||||||||||
| Include Index | ||||||||||
| Service Broker | ||||||||||
| Query Hints | Recompile/Optimize for/use plan/Parameterization | |||||||||
| Simple parameterization only | Simple and Force parameterization - database level, simple default | |||||||||
| Asynchronous statistics updating… | ||||||||||
| Computed column | Computed column more handling like index | |||||||||
| XML query plan | ||||||||||
| They were separate | Improved DBCC CheckDB included dbcc check_catalog design change /uses database snapshot | |||||||||
| Tempdb store only temp data | TempDB is special/Important now -row versioning/reindex… | |||||||||
| Security | NA | Policies | ||||||||
| NA | Schema | |||||||||
| Development | DTS | SSIS | ||||||||
| MARS | ||||||||||
| only DML triggers | DDL trigger | |||||||||
| random GUID | Sequential GUID | |||||||||
| CLR | ||||||||||
|
New T-SQL
|
|
|||||||||
Thanx.
Vinay
Reference:
