Otdfbt

Amount of permutations on an NxNxN Rubik's Cube

old style "caution" boxes

Is there such thing as an Availability Group failover trigger?

What do you call the main part of a joke?

How do I make this wiring inside cabinet safer? (Pic)

How to react to hostile behavior from a senior developer?

Why wasn't DOSKEY integrated with COMMAND.COM?

Should I use a zero-interest credit card for a large one-time purchase?

Crossing US/Canada Border for less than 24 hours

Would "destroying" Wurmcoil Engine prevent its tokens from being created?

また usage in a dictionary

Why are there no cargo aircraft with "flying wing" design?

Is there a kind of relay only consumes power when switching?

What font is "z" in "z-score"?

What is the longest distance a player character can jump in one leap?

Why didn't Eitri join the fight?

Using audio cues to encourage good posture

Around usage results

Is it a good idea to use CNN to classify 1D signal?

Do wooden building fires get hotter than 600°C?

First console to have temporary backward compatibility

Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?

Is CEO the profession with the most psychopaths?

Is there any way for the UK Prime Minister to make a motion directly dependent on Government confidence?

Using `min_active_rowversion` for global temporary tables

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

7

I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

sql-server sql-server-2016 temporary-tables

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

New contributor

asgerhallas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

7

I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

sql-server sql-server-2016 temporary-tables

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

New contributor

asgerhallas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?

sql-server sql-server-2016 temporary-tables

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

New contributor

asgerhallas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))

 connection.Open();

 connection.Execute("create table ##mytable ( Id int, rv rowversion )");

 var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

 var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

 var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

New contributor

asgerhallas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Apr 12 at 8:12

asgerhallas

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

New contributor

asgerhallas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

New contributor

asgerhallas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Apr 12 at 8:08

asgerhallasasgerhallas

1635

1 Answer
1

active

oldest

votes

9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

asgerhallas is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234637%2fusing-min-active-rowversion-for-global-temporary-tables%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

add a comment | 

9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

add a comment | 

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level.

The min_active_rowversion() function seems to work when you use the following code

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

dbfiddle

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

USE [db]

CREATE table ##mytable ( Id int, rv rowversion )

USE [tempdb]

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

insert into ##mytable (Id) values (1)

select min_active_rowversion()

share|improve this answer

edited Apr 12 at 8:26

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826

answered Apr 12 at 8:19

George.PalaciosGeorge.Palacios

2,726826