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?
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.
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
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.
Can you also tell me in terms of index maintenence. Is it going to be overhead in compariosn to varchar(255)
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.
15 People are following this question.
