We have a few tables in our SQL Server (2005 & 2008) database with columns defined as nvarchar(4000). We sometimes need to be able to store more data than that and consider to use nvarchar(max) instead. Now to the questions.
我们的SQL Server(2005和2008)数据库中有一些表，列定义为nvarchar(4000)。我们有时需要能够存储更多的数据，并考虑使用nvarchar(max)。现在的问题。
- Are there any performance implications we should know of?
- 我们应该知道哪些性能影响?
- Is it safe to use "alter table" to do the actual migration or is there another way?
- 使用“alter table”进行实际迁移安全吗?
- Anything else we should know before we convert?
- 在转换之前我们还需要知道什么?
Regards Johan
认为约翰
2 个解决方案
#1
8
We've had to do this as well in a few spots
我们在一些地方也必须这样做
- We didn't see any performance hits, its been 5 weeks since the change
- 我们没有看到任何表现上的突破，这是5个星期以来的变化
- 'Alter table' is fine as long as the table is not getting written to during the conversion
- “Alter table”是可以的，只要表在转换期间没有被写入。
- Be sure to have a backup just in case and depending on the amount data in the table it could take a while
- 一定要有备份以防万一，根据表中的数据量，可能需要一段时间
#2
1
If the text does not fit on the page with the rest of the data, the text will be stored in another page. This causes an extra database page read. So performance can/will be slower. On systems with a heavy load, and a very restricted response time, maybe.
如果文本与其他数据不匹配，则文本将存储在另一个页面中。这会导致额外的数据库页面读取。所以性能会变慢。在一个负载很重，响应时间很有限的系统上。
The SQL Server Management studio will probably rename the current table, create a new one with the new layout, copy data, remove old table. This can be slow.
SQL Server Management studio可能会重命名当前表，使用新的布局创建一个新的表，复制数据，删除旧表。这可能是缓慢的。
I don't know if the alter table will work.
我不知道修改表是否有效。
#1
8
We've had to do this as well in a few spots
我们在一些地方也必须这样做
- We didn't see any performance hits, its been 5 weeks since the change
- 我们没有看到任何表现上的突破，这是5个星期以来的变化
- 'Alter table' is fine as long as the table is not getting written to during the conversion
- “Alter table”是可以的，只要表在转换期间没有被写入。
- Be sure to have a backup just in case and depending on the amount data in the table it could take a while
- 一定要有备份以防万一，根据表中的数据量，可能需要一段时间
#2
1
If the text does not fit on the page with the rest of the data, the text will be stored in another page. This causes an extra database page read. So performance can/will be slower. On systems with a heavy load, and a very restricted response time, maybe.
如果文本与其他数据不匹配，则文本将存储在另一个页面中。这会导致额外的数据库页面读取。所以性能会变慢。在一个负载很重，响应时间很有限的系统上。
The SQL Server Management studio will probably rename the current table, create a new one with the new layout, copy data, remove old table. This can be slow.
SQL Server Management studio可能会重命名当前表，使用新的布局创建一个新的表，复制数据，删除旧表。这可能是缓慢的。
I don't know if the alter table will work.
我不知道修改表是否有效。