Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

What's new in Sql Server 2005

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

 

   
CTE
Pivot/UnPivot
Apply/except/intersect
TOP %
Output
Exception Handling (try… catch)
New data types - xml, varchar(max)
Xquery
     
     
 
Thanx.
Vinay
Reference:
 
 
 
 
 
 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.