February 1, 2006 at 11:03 pm
Hi,
I have worked on SQL server 2000 but never got a chance to work on SQL server 2005.Can anyone help me understand the basic differences between these two?
Thanks
-Seemita
February 2, 2006 at 1:36 am
Hi,Check this site for articles,also microsoft.com,databasejournal.com for new featues.
February 3, 2006 at 9:36 am
Here is an article that is an Introduction to SqlServer 2005
http://qa.sqlservercentral.com/columnists/fFinney/introductiontosqlserver2005.asp
Steve
February 12, 2008 at 7:51 am
hi please tell me The Differences Between SQL Server 2000 and 2005
please.......................................
February 12, 2008 at 8:14 am
I'm sorry that old link doesn't work now. Try this one.
http://qa.sqlservercentral.com/articles/Administration/2988/
Steve
July 21, 2009 at 6:27 am
1.sql server 2005 include interprise manger and query analyser together in same window we can open many window tabs in same place but in 2000 it is in different.
2. sql server 2005 support more new datatype like xml
3. we can make more database 2(paw(20))-1 in 2005 in compare to 2000 where not possible so much database
4. in storeprocedure we can write try catch statemenet in 2005 not in 2000
hariom
October 13, 2010 at 3:46 am
[font="Verdana"][/font]
sql2005 use some new data types
xml(extended markup language) & sclr(server common language runlime)
November 11, 2010 at 10:20 pm
[font="Arial"]--Reference:
http://stackoverflow.com/questions/198478/advantages-of-ms-sql-server-2008-over-ms-sql-server-2005
SQL SERVER 2000:
1.Query Analyser and Enterprise manager are separate.
2.No XML datatype is used.
3.We can create maximum of 65,535 databases.
4.Nill
5.Nill
6.Nill
7.Nill
8.Nill
9.Nill
10.Nill
11.Nill
12.Nill
13.cant compress the tables and indexes.
14.Datetime datatype is used for both date and time.
15.No varchar(max) or varbinary(max) is available.
16.No table datatype is included.
17.No SSIS is included.
18.CMS is not available.
19.PBM is not available.
20.PIVOT and UNPIVOT functions are not used.
SQL SERVER 2005:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.
20.PIVOT and UNPIVOT functions are used.
SQL SERVER 2008:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Can encrypt the entire database introduced in 2008.
--check it(http://technet.microsoft.com/en-us/library/cc278098(SQL.100).aspx)
(http://qa.sqlservercentral.com/articles/Administration/implementing_efs/870/)
(http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx)
(http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx)
(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/encrypt-documents-with-sql-server.aspx)
13.Can compress tables and indexes.
-http://www.mssqltips.com/tip.asp?tip=1582
14.Date and time are seperately used for date and time datatype,geospatial and timestamp with internal timezone
is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
-http://msdn.microsoft.com/en-us/library/bb934126.aspx
-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx
19.Policy based management(PBM) server is Introduced.
-http://www.mssqltips.com/tip.asp?tip=1492
-http://msdn.microsoft.com/en-us/library/bb510667.aspx
20.PIVOT and UNPIVOT functions are used.
-http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
-N.S.SATHISH
[/font][/color]
--
Thanks & Regards,
N.S.Sathish(SQL DBA)
December 29, 2010 at 11:17 pm
SQL Server 2000
Security:Owner = Schema, hard to remove old users at times
Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.
High Availability Clustering or Log Shipping require Enterprise Edition. Expensive hardware.
Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.
SQL Server 2005
Security Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.
Encryption Encryption and key management build in.
High Availability Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.
Read more: http://wiki.answers.com/Q/Difference_between_SQL_Server_2000_2005#ixzz19Zb0r6Xs
December 30, 2010 at 12:27 am
eature SQL Server 2000 SQL Server 2005
Server Programming Extensions Limited to extended stored procedures, which are difficult to write and can impact the server stability. The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling Limited to checking @@error, no much flexibility. Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language SQL Language enhanced from previous versions providing strong data manipulation capabilities. All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing Limited support using triggers to audit changes. Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments. Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents. Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET v1.1 of ADO.NET included enhancements for client development. v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging No messaging built into SQL Server. Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services An extremely powerful reporting environment, but a 1.0 product. Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
January 28, 2011 at 2:40 am
1)In Sql server 2005 Enterprise Manager and Query analizer combined as a sqlserver Management Studio
2)CLR Integration Avaliable
3)XmL Integartion
4)SSIS ,SSRS,SSAS services Avliable
5)DatabaseMail Integration,Notifications Services Avilable
6)Mirroring Avliable
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply