Irtsgbr

Dropping list elements from nested list after evaluation

Why are there uneven bright areas in this photo of black hole?

How to translate "being like"?

Why can't wing-mounted spoilers be used to steepen approaches?

Why isn't the circumferential light around the M87 black hole's event horizon symmetric?

Can withdrawing asylum be illegal?

A word that means fill it to the required quantity

Word to describe a time interval

Geography at the pixel level

What information about me do stores get via my credit card?

Short story: child made less intelligent and less attractive

What is the meaning of Triage in Cybersec world?

What is the motivation for a law requiring 2 parties to consent for recording a conversation

Is an up-to-date browser secure on an out-of-date OS?

Is there a way to generate a uniformly distributed point on a sphere from a fixed amount of random real numbers?

Is it okay to consider publishing in my first year of PhD?

Falsification in Math vs Science

How much of the clove should I use when using big garlic heads?

How do you keep chess fun when your opponent constantly beats you?

Can there be female White Walkers?

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

How to support a colleague who finds meetings extremely tiring?

Why don't hard Brexiteers insist on a hard border to prevent illegal immigration after Brexit?

A female thief is not sold to make restitution -- so what happens instead?

Using `min_active_rowversion` for global temporary tables

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

6

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 1 hour ago

asgerhallas

asked 1 hour ago

asgerhallasasgerhallas

1585

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.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Could you add your version of SQL Server as a tag please
  
  – George.Palacios
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @George.Palacios sure! I could go up to SQL Server 2017 too, if needed to make it work.
  
  – asgerhallas
  1 hour ago
  
  

  
  
  
  

add a comment | 

6

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 1 hour ago

asgerhallas

asked 1 hour ago

asgerhallasasgerhallas

1585

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.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Could you add your version of SQL Server as a tag please
  
  – George.Palacios
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @George.Palacios sure! I could go up to SQL Server 2017 too, if needed to make it work.
  
  – asgerhallas
  1 hour ago
  
  

  
  
  
  

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 1 hour ago

asgerhallas

asked 1 hour ago

asgerhallasasgerhallas

1585

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 1 hour ago

asgerhallas

asked 1 hour ago

asgerhallasasgerhallas

1585

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 1 hour ago

asgerhallas

asked 1 hour ago

asgerhallasasgerhallas

1585

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 1 hour ago

asgerhallasasgerhallas

1585

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 1 hour ago

asgerhallasasgerhallas

1585

1 Answer
1

active

oldest

votes

6

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 1 hour ago

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great, thanks a lot :)
  
  – asgerhallas
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And thanks for showing me dbfiddle... really useful.
  
  – asgerhallas
  1 hour ago
  

  
  
  
  

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

6

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 1 hour ago

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great, thanks a lot :)
  
  – asgerhallas
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And thanks for showing me dbfiddle... really useful.
  
  – asgerhallas
  1 hour ago
  

  
  
  
  

add a comment | 

6

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 1 hour ago

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Great, thanks a lot :)
  
  – asgerhallas
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And thanks for showing me dbfiddle... really useful.
  
  – asgerhallas
  1 hour ago
  

  
  
  
  

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 1 hour ago

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826

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 1 hour ago

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826

answered 1 hour ago

George.PalaciosGeorge.Palacios

2,601826