SQL Server 2000 vs 2005

[LonnieJohnson]

I asked this about a year ago when we were considering 2005. Now we are going to go ahead and get it.

Does anyone know of a site that has a good comparison of the features of the two?

What is new? What will no longer be available and such?

Thanks.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[rjoubert]

Have you tried Google? Try typing in "SQL Server 2000 vs 2005" as your search term. There's a couple of sites offering performance metrics between the two versions. Also, try typing "What's new in SQL Server 2005" as your search term in Google. The first matches are articles from Microsoft.

 

[harebrain]

What's new? It's a painful transition for the developer. All the tools you know are gone and a "new, improved," integrated environment is what you get instead. DTS is replaced by SSIS, which does both more and less than DTS.

As you can tell, I wasn't happy with 2005 because all it really brought was change for change's sake... and a lot of $change to Microsoft.

 

[SQLDenis]

I don't agree with you
First of all if you have DTS packages that are stable and don't need to change them SSIS can run them them as an Execute DTS 2000 Package Task

Have you taken advantage of any of the new things?


Improved error handling
snapshot isolation
online rebuilding of indexes
Native partitioning
PIVOT/UNPIVOT and windowing function (RANK, DENSE_RANK etc etc)
CTE's
updateless updates don't change the index anymore (so it is much faster)
about 20 -30% overal speed improvment
db_mail
SQL_CLR

of cource there is a lot more

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

And I forgot VARCHAR(MAX)

and probably the best thing are the dynamic managment views
no longer do you need to run DBCC USEROPTIONS
SP_WHo etc etc

This stuff is so much easier in 2005

instead of DBCC USEROPTIONS we can run this

SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

and instead of sp_who and then filtering SPID that are greater than 50 we can run this

SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =0

you want to know your own transaction isolation level? no problem

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
WHERE session_ID =@@SPID

try doing that in SQL 2000

a lot more about sys.dm_exec_sessions can be found here

http://sqlservercode.blogspot.com/2006/08/sysdmexecsessions.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[LonnieJohnson]

Can I install the 2005 Express and still have a 2000 Local version at the same time?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[mrdenny]

Yes you can install SQL 2000 and SQL 2005 side by side.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com