The Differences Between SQL Server 2000 and 2005 - Part 2
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/sjones/3036.asp
-
The one difference I was hoping for was an auto-complete facility like you get in Visual Studio. In my opinion, it's so long overdue that people might forget about even doing it!
Otherwise, 2005's a good improvement over 2000 with all the features you mentioned, but with the brand new GUI it still feels like an incremental update and not a complete revolution that I was expecting. Though, I'll hold my final judgement until I get a chance to play with it properly.
Paul
-
I think the addition of varchar(max) and the xml data types are pretty compelling. Not exciting stuff, but they can really clean up the way you solve problems.
I'll also suggest a different method. Try solving (or at least imagining) problems using the new features in SQL 2005, then go back to SQL 2000 and see what you miss having.
-
Very nice article. There was not much mentioned about Visual Studio 2005 and SQL 2005 integration. From your experiences as a developer, aside from the new features, what can to do with the VS 2005 and SQL 2005 that you can't do with SQL 2000?
Thanks
Doug -
This set of articles was very informative in a concise manner.
I would like to see an article on migration issues and things to consider. We have been running SQL 2000 and I have been using the 2005 Management Studio alternately with the 2000 Enterprise Manager and doing my research into how best to proceed to a migration now that we have made the decision to do so.
There are always gotchas when migrating depending on setup and configuration. I am going to be migrating SQL 2000 (running on Win 2003 servers) to SQL 2005. I have done a great deal of research on it. There are still some questions to hardware configuration for optimization of 2005 that I have found difficult to find any reference to.
If you were of a mind to do so, I would personally appreciate some Dos and Don'ts for migrating.
Thank you..cherie
-
Don't forget User-Defined Aggregate Functions! (CREATE AGGREGATE) I haven't tried them yet but they sure sound cool!
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I'm a little confused about the excitement behind varchar(max). In the article it states, "TEXT is hard to work with in programming environments." Now maybe it's just the environment I've been programming in for the past several years (Visual Studio .NET, mostly C#), but I haven't found text (or ntext) to be hard at all. Through ADO.NET I can SELECT, INSERT, and UPDATE text/ntext columns just like any other column.
What am I missing?
-
Craig, Try to manipulate a TEXT variable on the server ... you'll understand what "difficult to manipulate" means
* Noel -
The things that have not been mentioned, are where 2005 is a REGRESSION from 2000 ...
To the shame of M$ there are *many*.
Here are some:
1.Replication Management: Replication monitor is way usless when compared to the 2000 interface (lots and lots of screens and can not even handle pull subscriptions entirely )
2. Debugging stored Procedures. From Query Analyzer we could do it in 2000. 2005 requires visual studio ... Hmmmm
3.Script Generation: Although on SP2 they fixed some issues there are *still* role membership problems ... In 2000 it worked "beautifully" since day one.
there are more but those are definitely at the top of my list
* Noel -
I'd love to do a "upgrade gotchas", but I haven't experienced enough. Maybe someone that's done a bunch or has a large environment can write me one!
The CREATE AGGREGATE seems more like a BI enhancement, but maybe I'm wrong. Haven't really thought of any I needed yet.
There's definitely some gotchas and a nice list above from Noel.
-
One of the biggest requests on SQL Server forums is how to aggregate string values. The answer was always "you can't", with the work around that you can roll strings together by assigning them to a variable à la:select @STR = coallesce(@str + ',', '') + value
from table
Now all the forums have at least one thread of someone looking for the best implementation of a string concatenation user defined aggregate.
While I'm posting, here are a few of my favourites additions to SSMS from SP2:
- Opening a file uses the currently active connection (I know, SQL 2000 did that, and then SQL 2005 up to SP1 didn't! What were they thinking?!?)
- How you can now copy the column headers with the rows from the Grid Results.
- That nulls show up with a yellow background in the grid results (no more wondering if it's a null or an emty string!)
- Ability to have string values quoted when saving to CSV, so commas in your results don't skew your CSV (this was sort of around before SP2, but didn't work correctly.)
Rick
townsends.ca -
This may be off topic, but Snapshot isolation is a really big deal. Hopefully the tempdb involvement needed does not take away from this new feature.
-
Hi.
I am very interested in the row-level security in 2005, i.e. where you can assign rights not only to a table, but also to individual rows.
This is very powerfull, when administration- and user rights are different for different data.
Security on instances has allways been taken for granted in file systems but not in the databases. How strange?
Any experiences of this ?
/PG
-
What are the product technical differences between SQL 2K and SQL 2005 ? For example, I have recently learned that the product version of the SQL Instance I'm running is MSDE 8.0.760 SP3. I've been told on this forum that this refers to SQL 2000 engine. HOWEVER, I have the following properties existing on my system:
On the physical server, under Add/Remove Programs, there is a program "Microsoft SQL 2005". Then under it, a hyperlink which says "Click here for more information", which when clicked, brings me to a general SQL 2005 information web site: http://support.microsoft.com/ph/2855.
When I open up the SQL 2005 program icon (Either SQL , it identifies it as such (Not as SQL 2000).
When I goto Start | All Programs, there is the expected SQL 2005 Programs icon Folder.
How can ensure I have that I DO NOT HAVE sql 2005 engine? For example, what is the product version for sql 2005 and where can I find this in the application? Also, if I do not have the SQL 2005 engine, then what exactly is the definition of SQL 2005 which is using a SQL 2000 Database engine? In other words, then what is SQL 2005 application I'm using? If its not the engine, then what is the core component of this SQL 2005 app?
This goes back to a post at : http://www.sqlservercentral.com/Forums/Topic490333-131-2.aspx
Where I was under the impression I was using SQL 2005. The responder was most helpful, but I need find "proof" that I have the SQL 2000 engine, and "proof" or some exact description / relationship between the SQL 2005 and the MSDE engine, or some cogent description of their relationship as "engine" and "application" (For example, is there a document which describes how a SQL 2005 application uses a MSDE engine?)
once again, thanks,
-
When connected to that instance of SQL Server you can run: SELECT @@VERSION which will tell you what version of SQL Server you are connected to.
To identify what is available on that server, launch the SQL Server Configuration Manager from the Microsoft SQL Server 2005 program group. This will show you all instances installed on that node.
Using SSMS - you can validate the version you are connected to by looking at the version number that shows in Object Explorer. SQL Server 2005 is 9.0.xxxx and SQL Server 2000 will be 8.0.xxx.
You may just have the SQL Server 2005 Workstation/Client components installed. The client tools can connect to both 2000 and 2005 and manage both.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply