Rowversion Duplication

Question

Post reply

Rowversion Duplication

Krishna1

Hall of Fame

Points: 3666
More actions
January 14, 2013 at 10:07 pm

#393938

Hi All
Most off our tables have rowversion columns. We whant this row version to be unique through out the DB. But as per msdn site it syas that rowverison can get deuplicated if "SELECT INTO " command is used?
Is there any way that we can use "SELECT INTO" and still avaoid rowversion dupllication?
Regards
Shilpa

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

It's only duplicated if the rowversion column is included in the column list of the select into statement.

Don't do that, and your values will stay unique.

Cheers

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

shilpaprele (1/14/2013)
Is there any way that we can use "SELECT INTO" and still avaoid rowversion dupllication?

YEs you can try like

select "all coumns except rowversion column " into newtable from oldtable

then

alter table newtable add rowverison column

The above is just code hint not the actual sql code

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

BrainDonor SSCoach Points: 19252 More actions · Answer 3

SELECT INTO creates the target table, and the only exception along the lines that you are after is with the IDENTITY column.

You could leave the Rowversion column out, as already suggested but we don't know the size of the tables concerned so altering the schema may be a large overhead.

Why not create the target table first and then use INSERT INTO? That will allow what you require.

CREATE TABLE Test1(

Col1 rowversion,

col2VARCHAR(20)

);

CREATE TABLE Test2(

Col1 rowversion,

col2VARCHAR(20)

);

INSERT INTO Test1(col2)

VALUES( 'test1'),( 'test2')

INSERT INTO test2(col2) SELECT Col2 FROM Test1

SELECT * FROM test1;

SELECT * FROM test2;

Steve Hall
Linkedin
Blog Site

Krishna1 Hall of Fame Points: 3666 More actions · Answer 4

Thanks all for your replies.

I agree that we should exclude row version column but there my be many developers working on the DB. And if sombody uses the select into then we will have dupliction.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

shilpaprele (1/16/2013)
there may be many developers working on the DB. And if sombody uses the select into then we will have dupliction.

and this cant be get restricted or stopped , so you should make them understand that why and what needs to follow ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)