For me, the great advance for SQL Server 2000 was the introduction of UDFs. Both the table functions but the scalar functions. I also remember feeling very relieved to have Varchar(8000) rather than Varchar(255). Those three things paid for the upgrade

User-defined functions did it for me. I never used the fancy OLAP stuff.

I like the new TRY ... CATCH blocks for error handling in SQL Server 2005. Now there is no need to check the @@ERROR  variable after each statement thus decreasing the size of stored procedures and making the code much more readable.

I also like the new CLR Integration feature in SQL Server 2005. With CLR Integration I can execute managed .NET code within SQL Server.

I think the two main advantages of SQL Server 2000 over SQL Server 7 are Windows Authentication (integrated authentication) and cascading updates/deletes via foreign key constraints.

The former has significantly lifted the security level of SQL Server, and the latter has greatly helped conserve data integrity.

Just my 2 cents :-)

In SQL Server 2005, my top two favorite new features/changes came to mind with relative ease.  It was the other features, places 3-10, that gave me the most trouble.  I know we were only asked for our top two advances, but I just couldn’t help myself from thinking further on the topic.

The number one feature in SQL 2005, for me, is CLR integration.  I like T-SQL and I like to solve complex problems, but the marriage of the two is not always smooth. Needless to say, writing nested case statements have never been one of my favorite activities. But, all the pain and heartache is gone now that I can write a library of commands in C# and import my assemblies into 2005.  For me, it brings a new level of intractability to SQL and to the data.  With .NET integration, there is nothing I cannot do in SQL server 2005.  My limitations are not bound by T-SQL anymore, but by my own knowledge and capability (and of course the rarely used Google search).

Number two on the countdown was a much needed improvement for that I think all DBAs can appreciate.  The ability to create DDL triggers in SQL Server 2005 is such a relief.  I can track table definition changes, creations and drops with a relatively simple database trigger (which is now being put through its trials in QA) and monitor database changes on the server level with a lone trigger.  The auditing and monitoring possibilities are endless with this long overdue addition.

Just as a side note, my honorable mention goes to one small change from 2000 to 2005.  The definition change made in MSDB..dbo.sysjobsteps came in at a close third in my contest. Moving the command data type from nvarchar(3200) to nvarchar(MAX) made my day.  That simple limitation, to this day, drives me nuts when I have to go back and work on 2000 machines.

SQL Server 2005 has been impressing me since I began using it.  The steps forward that have been made are not only making my job easier, but more enjoyable.  I only hope that SQL Server 2008 is as big of an improvement as 2005 was.

Many good things alread mentioned - I really like TRY/CATCH, I can see some uses for CLR (although I've already had issues with 3rd party products wanting to remove the middle tier, and deploy it all as CLR, unsafe assemblines in the DB - and I think in years to come we are going to see some horrible things get done with CLR).

SQLCMD mode
If you've worked at a site where you've had to look at several servers, or you've got a server with mutiple instaces, then I think you'll recognize the benefits that SQLCMD mode in the SSMS brings, as you want to do the same thing on several servers... oh, say:
select * from sys.sysprocesses where blocked <> 0 or spid in (select blocked from sys.sysprocesses where blocked <> 0) order by blocked
--yes, I know it shouldn't be *, but for previty in the post, grant me that.
That SQL, by the way, should return blocking list from sysprocesses, with root(s) at the head (since they are unblocked, and thus have a blocked value of 0

Now, you have to run that against all your servers ... or you could do the following:

:connect MyServer
select @@Servername,* from sys.sysprocesses where blocked <> 0 or spid in (select blocked from sys.sysprocesses where blocked <> 0) order by blocked
go
:connect MyServer\MyInstance1
select @@Servername,* from sys.sysprocesses where blocked <> 0 or spid in (select blocked from sys.sysprocesses where blocked <> 0) order by blocked
go
:connect MyServer\MyInstance2
select @@Servername,* from sys.sysprocesses where blocked <> 0 or spid in (select blocked from sys.sysprocesses where blocked <> 0) order by blocked
go
:connect MyServer2\MyIstance
select @@Servername,* from sys.sysprocesses where blocked <> 0 or spid in (select blocked from sys.sysprocesses where blocked <> 0) order by blocked
go

btw, from SSMS, you could also connect to a SQL 2000 machine - you just have to change the sys.sysprocesses to sysprocesses

Dedicated Admin connection
I hope you never need it, but if you need it, you'll love it. Ever had to restart SQL service, or even a SQL server, because you cannot kill that D"£%$" user query? enter (if you've enabled it) the dedicated admin connection, and now you can connect to the machine, and run your SQL to find the bugger, and KILL it off.
Oh - FYI - admin doesn't have to be a command line adventure - when connecting to a machine, you can specificy the admin connection for the GUI - when you connect, change the server name from MYSERVER to admin:MYSERVER - and as long as you are sysadmin, you should get in.

Final (3rd , but I had to mention it) one - DMVs and the reports built into SSMS. Especially my favorite Object Execution Statistics (Database level report). Wandering what does most of the work on your box? Wondering which SP gets called 1000000's of times? Wondering, in a SP, which statement is costing you all the time? Yes you can trace - but it's all in this report, as well :-)

By the way, the above is all SQL 2005, in case it needed to be said...

Regan,  I wouldn't dismiss CLR because of a few lazy developers.  There are best practices to follow when creating assemblies, and some people will always ignore those to take the easy route.

I have seen plenty of great technologies abused for no real reason other than they could.  I think we are going to see a lot of advancements come out of the .NET community in the SQL assembly area.

Also, the dedicated admin connection is one of my favorites as well.  It came in my top 5.

While I agree that it shouldn't be dismissed for a few L(cr)azy developers, I fear people overloading a DB server with significant amounts of CLR code, which increases workload on the DB server. App server's, traditionally, are easier to scale out than DB servers, so I think the CLR approach should be carefully considered, and used where the gains have been carefully, explicitly understood.

But finally, yes, there will be HUGE gains for some CLR solutions, and truly, I do think it is probably one of the best enhancements to SQL - as long as it is used wisely, and correctly.

Well, mine are not as 'snazzy' as some of those offered up above - but here goes...
For all those folks who, in the midst of severe crises on SQL Server 2000, found themselves cursing out loud when they clicked on EM and watched it go through its death-throes whilst crashing ... Thank the LORD for SQL Server 2005 Management Studio!  It has yet to belly-up on me.

My other ... SQL Server BI studio.  At first I wasn't thrilled with this at all having pretty much mastered the DTS Packages, but after going through a bit of a learning curve, I find the abilities offered by the BI studio objects to greatly increase the power of what I can do.  It is 'most excellent'.

Regards.

Randy
(jvolters@sc.rr.com)

Hey, cool. Just got through my backlog of "e-zines", and saw this:
[quote]
Once again, we depart with the announcement of the winners of last issue's competition. Asked to nominate the most significant advances in SQL Server, we award Simple-Talk goodie bags to ShawnNWF, Regan Galbraith and Mikkel Toudal Kristiansen. The new TRY/CATCH error handling in SQL 2005 certainly seems to have hit the spot for many people!
[/quote]

anything we need to do to 'collect our prize' ?