Tjyylli

How does ice melt when immersed in water?

Simulating Exploding Dice

Do working physicists consider Newtonian mechanics to be "falsified"?

How to split my screen on my Macbook Air?

Semisimplicity of the category of coherent sheaves?

What is this lever in Argentinian toilets?

How to stretch delimiters to envolve matrices inside of a kbordermatrix?

Is above average number of years spent on PhD considered a red flag in future academia or industry positions?

Can the DM override racial traits?

Am I ethically obligated to go into work on an off day if the reason is sudden?

Can smartphones with the same camera sensor have different image quality?

Format single node in tikzcd

The following signatures were invalid: EXPKEYSIG 1397BC53640DB551

University's motivation for having tenure-track positions

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?

He got a vote 80% that of Emmanuel Macron’s

How to copy the contents of all files with a certain name into a new file?

Is there a writing software that you can sort scenes like slides in PowerPoint?

Derivation tree not rendering

Make it rain characters

Cooking pasta in a water boiler

How should I replace vector<uint8_t>::const_iterator in an API?

Would an alien lifeform be able to achieve space travel if lacking in vision?

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 yesterday

asgerhallas

asked yesterday

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 yesterday

asgerhallas

asked yesterday

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 yesterday

asgerhallas

asked yesterday

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 yesterday

asgerhallas

asked yesterday

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 yesterday

asgerhallas

asked yesterday

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 yesterday

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 yesterday

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 yesterday

answered yesterday

George.PalaciosGeorge.Palacios

2,696826

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 yesterday

answered yesterday

George.PalaciosGeorge.Palacios

2,696826

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 yesterday

answered yesterday

George.PalaciosGeorge.Palacios

2,696826

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 yesterday

answered yesterday

George.PalaciosGeorge.Palacios

2,696826

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 yesterday

answered yesterday

George.PalaciosGeorge.Palacios

2,696826

answered yesterday

George.PalaciosGeorge.Palacios

2,696826

answered yesterday

George.PalaciosGeorge.Palacios

2,696826