Differences between SQL Server 2000 and SQL Server 2005

Significant Differences

Sl No. Topic SQL 2000 SQL 2005 Any More Comments.
1 UI Differences -Tools *Analysis Services Manager (ASM)

*Query Analyzer (QA)

*Enterprise Manager (EM)

*Report Manager
*Business Intelligence Development Studio (BIDS)

*SQL Server Management Studio (SSMS)
2 UI Differences -Organizing Grain
*ASM "Database" *BIDS "Solution"and "Project"

3 UI Differences - Data Modeling *Must be connected to do anything

*Single Data Source, Single Fact Table per Cube

*Cannot directly rename entities -must resort to database views to rename entities and for logical abstraction

*Must resort to virtual cubes for mixed fact granularity

*No support for arbitrary SQL as a Data Source
*Data Source Views (DSVs) provide an abstraction layer

*Between Database and Cubes, DSV can contain tables drawn from multiple heterogeneous

*In data source , once DSV established, need not be connected to work with cube models

*Can rename entities, provide annotations

*Named Queries -arbitrary SQL as Data Source

*DSVs can be shared between cubes and DTS for improved consistency and administration

4 UI Differences -Cube Construction *Manually intensive, even with Wizard

*Must know the underlying schema well
5 UI Differences -Deployment *Archive/Restore Database
*CAB files
*No configuration variables for Data Source connectivity, etc.
*Elaborate Configuration and Deployment Model

6 UI Differences - Metadata Storage
*There isa repository

*Defaults to Access -can upgrade to SQL Server

*Cannot really be source-controlled
*There is not a repository

*Metadata stored in XML documents, can easily be source-controlled

*Team development is facilitated

7 Architecture -Unified Dimensional Model
*Distinct difference between flat file, relational, cube and web-service data sources
*Unified Dimensional Model (UDM) integrates all types of data sources -data consumer sees single, multi-dimensional interface

*Allows the user model to be greatly enriched

*Provides high performance queries supporting interactive analysis, even over huge data volumes

*Allows business rules to be captured in the model to support richer analysis

*Supports writeback and 'closing the loop'

8 Architecture –Dimensions
*Role-playing requires multiple Dimensions
*Role-playing is a new Dimension type

*Reference Dimension

*Many-to-many Dimension

9 Architecture –Hierarchies *One and only one Hierarchy per Dimension

*Attributes are either part of Hierarchy or are Properties
*Zero to multiple Hierarchies in same Dimension


Author: Vasudevan Deepak Kumar10 Oct 2007 Member Level: Gold   Points : 0

There is an edition called MSDE which does not come with any GUI. However, there is a tool called QueryExpress from http://www.albahari.com/ and I also have a review at

Author: Pradeep Joe Felix06 Nov 2007 Member Level: Gold   Points : 0

Differences between SQL Server 200 and SQL Server 2005

It should be

Differences between SQL Server 2000 and SQL Server 2005!!

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: