与 SQL Server 2000 相比，SQL Server 2005/2008 的主要优点是什么？
我们的一位客户正在升级他们的服务器，因为旧机器无法再处理数据库的负载。 他们在过去 6 年里一直使用 sql 2000，并且数据库已经增长到可以容纳几 GB 的数据。
值得升级到 2005 或 2008 吗？ 与 2000 相比，新版本的主要优点是什么？
One of our clients is upgrading their servers because the old machines can't handle the load of the database anymore. They have been using sql 2000 for the last 6 years and the db has grown to hold a few GB of data.
Will it be worth upgrading to 2005 or 2008? What are the major benefits of the new versions compared to 2000?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
除了 Galwegian 提到的 CLR 集成之外，对我来说主要优点是：
另一个需要注意的区别是，您在 Sql 2005 中使用的不是 DTS 包，而是使用 集成服务，虽然相似，但完全不同。
In addition to the CLR integration mentioned by Galwegian, the main pluses for me are:
Another difference to note is that instead of the DTS packages that you would have been used to Sql 2005 uses Integration Services, which while similar is a whole different ball game.
根据您使用的 SQL Server 版本，SQL 2005 的硬件限制/上限比 SQL2k 中的相应版本要少。
例如，SQL 2000 标准版不会使用超过 2Gb（实际上是 1.7Gb），而 SQL 2005 标准版则没有上限（允许达到操作系统最大值）。
看：
http://msdn.microsoft.com/en-us /library/aa933149(SQL.80).aspx
...和...
http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
所以：如果您运行的是标准版 + SQL Server 2000 实例目前使用 ~1.6Gb RAM + 您的服务器有 3Gb 或更多物理 RAM，那么可能值得升级，只是为了增加内存使用带来的好处......（更多缓存的表数据、索引、计划等）
Depending on what edition of SQL Server you are using, SQL 2005 have less restrictive hardware limitations/caps than corresponding editions in SQL2k.
For example, SQL 2000 Standard Edition won't use more than 2Gb (in practice 1.7Gb) while SQL 2005 Standard Edition is not capped (allows up to OS max).
See:
http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx
...and...
http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
So: if you're running standard edition + your SQL Server 2000 instance currently uses ~1.6Gb RAM + your server has 3Gb or more physical RAM then it is probably worth upgrading just for the benefits increased memory usage brings... (more cached table data, indexes, plans etc)
如果您计划从 SqlServer 2000 升级，我将跳过 2005 并直接进入 SqlServer 2008
它具有 2005 的所有功能以及一些额外功能（例如将表变量作为参数传递给存储过程的选项、新的日期类型、空间数据处理等）
可以参考MS SQL Server 2008 相对于 MS SQL Server 2005 的优势有关功能综合列表的问题
编辑
我可以看到问题已更新，现在 SqlServer 2008 包含在问题中。
I you are planing to upgrade from SqlServer 2000 I would skip 2005 and go directly to SqlServer 2008
It has all the features of 2005 plus some extras (for example an option to pass a table variable as a parameter to stored procedure, new date types, spatial data handling,etc.)
You can refer to Advantages of MS SQL Server 2008 over MS SQL Server 2005 question for the comprehensive list of features
EDIT
I can see that the question has been updated and now SqlServer 2008 is included in the question.
MS SQL 2005 和 2008 有很多大肆宣传的技术，其中之一就是能够将 CLR 代码填充到存储过程中。 不要这样做！
另一个“功能”是能够将数据库公开为 Web 服务； 不要这样做！
第三个功能是能够再次使用数据库中的“通知”并进入应用程序层； 不要这样做...！
您的数据库是一个存储桶，它应该“存储数据”。 我确信微软在 2005 年和 2008 年推出的许多功能都是因为他们想让 O/RM 库的使用变得复杂，从而抽象出实际的数据库供应商，以便人们可以按照自己的意愿更改数据库。 然后，通过添加大量“愚蠢的功能”，这些功能违背了我们自 70 年代以来学到的有关数据库的每一个最佳实践，他们成功地创建了一个新的锁定，通过让人们使用他们实际上不应该使用的东西来消除供应商锁定无论如何使用...
其中一部分可能是 2005 年和 2008 年有很多很酷的功能（就像这里提到的一个；支持！）以及优化、错误修复等。 但要小心，这样您就不会开始使用那些会破坏您的应用程序、无法使用最佳实践并让您陷入困境的东西......:(
MS SQL 2005 and 2008 have a lot of hyped technologies, one of them is the ability to stuff CLR code into Stored Procedures. DON'T DO THIS!
Another "feature" is the ability to expose your database as WebServices, yet again; DON'T DO THIS!
A third feature is the ability to use "notifications" from your database and into your application layer, yet again; DON'T DO THIS...!
You database is a bucket and it should "store data", period. A lot of the features Microsoft put into 2005 and 2008 I feel sure they did because they wanted to complicate the usage of O/RM libraries which abstracts away the actual database vendor so that people can change databases as they wish. Then by adding a lot of "stupid features" which goes against every single Best Practices we've learned about databases since the 70s they managed to create a new lock-in which removed the vendor locks by making people use stuff they really shouldn't use anyway...
A part from that there might be a lot of cool features in 2005 and 2008 (like one mentioned here; support!) and things like optimalizations, bugfixes and such. But be careful so you don't start using stuff that craps down your app and makes it impossible to use best practices and locks you in... :(
说实话，主要好处是CLR 集成 - 它使您的编码方式更加灵活您的数据库，让您可以选择在过程中包含过程 C# 或 VB.NET，而不是基于集合的 T-SQL。
The main benefit is CLR integration to be honest - it allows you much more flexibility in the way you code your database, giving you the option of including procedural C# or VB.NET in your procedures instead of set-based T-SQL.
有一些有用的新功能，例如服务代理，但就性能而言，从 2000 年到 2005 年，您不会看到巨大的改进。a) 调整数据库和 b) 投资会更好在新硬件中。
There are some new features that are useful, like service broker for example, but in performance terms you aren't going to see huge improvements in moving from 2000 to 2005. You would be much better off a) tuning your DB and b) investing in new hardware.
我认为 Microsoft 不再支持 SQL Server 2000。 如果我错了，那么很快...
I think that SQL Server 2000 is no more supported by Microsoft. If I'm wrong, it will be soon...
用户和模式的分离是另一个好处。 在 SQL 2005 中，如果您希望按逻辑/功能区域或类似区域而不是按数据库中的用户进行模式分离，则可以创建“hr”、“sales”、“accounting”、“product”等模式，然后创建用户表在各自的模式下。
在 SQL 2000 中，架构名称与表所有者/创建者相同。
Separation of users and schemas is another goodie. In SQL 2005, if you want schema separation by logical/functional area or similar rather than by user in your database, you can create schemas such as "hr", "sales", "accounting", "production" and then create user tables under the respective schemas.
In SQL 2000, the schema name was identical to the table owner/creator.
在线索引重建是一个很好的功能。 我认为这可能只是企业版的一个选项。
Online index rebuilds are a nice feature to have. I think it might only be an option in Enterprise edition though.