question

rudrapbiswas avatar image
rudrapbiswas asked

How to suppress rowversion?

I am migrating one column data to another column. The requirement was to alter the data type of a column of a large table. I have created a new NULL column and migrating the data in batches. While doing that the rowversion column is getting updated and a large amount of data qued up for log shipping. Log shipping job looks up the rowversion column. 1. Is there any other way to alter a column from varchar(255) to nvarchar(255) without passing 'ALTER TABLE...' statement? 2. Is there any way I can stop/suppress the updates in rowversion column. 3. How good it is to add an index on nvarchar(255) field? Whether the search criterias are going to be degraded on this column?
log-shippingindexalter-tablenvarchar
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
1. Nope 2. Nope 3. Adding an index to an nvarchar(255) field won't degrade performance. It might not help performance. It might. It really depends on the types of queries you're writing against the column to whether or not the statistics will be used to take advantage of the index. For example WHERE Col LIKE '%Ravi%' won't benefit from the index, but LIKE 'Ravi%' will.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant for the answer. Can you please elaborate this:"For example WHERE Col LIKE '%Ravi%' won't benefit from the index, but LIKE 'Ravi%' will" Regards, --Rudra
0 Likes 0 ·
There are arguments that are searchable. These are referred to as "Search Argument Able" or sargeable for short. An argument like '%x%' is not sargeable because it will have to scan the index/table to find values because it's searching in the middle. While an argument like 'x%' only has to search a specific range of values, anything after W and before Y, so it's sargeable. There are more examples if you search the internet or read my book.
0 Likes 0 ·
Can you also tell me in terms of index maintenence. Is it going to be overhead in compariosn to varchar(255)
0 Likes 0 ·
An index with a larger key will have fewer rows per page and might be more subject to fragmentation, possibly requiring more frequent defrags, but that's about it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.