|
|
You're in the
sql server programming
group:When does @@DBTS value change?
sql server programming:
However I am finding that that is not true. I can do multiple inserts of rows into a table, and all of the rows will have the same value in the varbinary(8) column that is populated with the value of @@DBTS at the time of insert. Single updates to a value in each of these rows also does not change the varbinary(8) column value either, using the Update stored procedure below. I have seen the varbinary(8) column value change (on occasion) but I would expect it to change immediately upon each insert and update. Please correct my expectation, if wrong. Thanks, Dean S Here is the insert stored procedure: CREATE Procedure dbo.InsertJusticeClientDrugTimeStamp @ClientID Int, @DrugName varchar(15), @DrugRank tinyint, @DrugRoute varchar(10), @DrugAmount varchar(20), @DrugFrequency varchar(15), @DrugAgeBegan varchar(5), @DrugLastUsage VarChar(10), @DrugLastUsageAmount VarChar(10), @DrugOtherName varChar(25), @MaintUser VarChar(10), @MaintDate SmallDateTime, @AddUser VarChar(10), @AddDate SmallDateTime AS SET NOCOUNT ON IF EXISTS (SELECT ClientID FROM tblJusticeClientDrug WHERE ClientID = @ClientID AND DrugName = @DrugName) BEGIN RETURN -1 END ELSE BEGIN INSERT INTO tblJusticeClientDrug (ClientID, DrugName, DrugRank, DrugRoute, DrugAmount, DrugFrequency, DrugAgeBegan, DrugLastUsage, DrugLastUsageAmount, DrugOtherName, MaintUser, MaintDate, AddUser, AddDate, [TimeStamp]) VALUES (@ClientID, @DrugName, @DrugRank, @DrugRoute, @DrugAmount, @DrugFrequency, @DrugAgeBegan, @DrugLastUsage, @DrugLastUsageAmount, @DrugOtherName, @MaintUser, @MaintDate, @AddUser, @AddDate, @@DBTS) RETURN @@ROWCOUNT END GO Here is the update stored procedure: CREATE Procedure dbo.UpdateJusticeClientDrugTimeStamp @ClientID Int, @DrugName varchar(25), @DrugRank tinyint, @DrugRoute varchar(10), @DrugAmount varchar(20), @DrugFrequency varchar(15), @DrugAgeBegan varchar(5), @DrugLastUsage VarChar(10), @DrugLastUsageAmount VarChar(10), @DrugOtherName varChar(25), @MaintUser VarChar(10), @MaintDate SmallDateTime, @AddUser VarChar(10), @AddDate SmallDateTime, @TimeStamp varbinary(8) AS SET NOCOUNT ON IF NOT EXISTS (SELECT ClientID FROM tblJusticeClientDrug WHERE ClientID = @ClientID AND DrugName = @DrugName AND [TimeStamp] = @TimeStamp) BEGIN RETURN -1 END ELSE BEGIN UPDATE tblJusticeClientDrug SET DrugRank = @DrugRank, DrugRoute = @DrugRoute, DrugAmount = @DrugAmount, DrugFrequency = @DrugFrequency, DrugAgeBegan = @DrugAgeBegan, DrugLastUsage = @DrugLastUsage, DrugLastUsageAmount = @DrugLastUsageAmount, DrugOtherName = @DrugOtherName, MaintUser = @MaintUser, MaintDate = @MaintDate, AddUser = @AddUser, AddDate = @AddDate, [TimeStamp] = @@DBTS WHERE ClientID = @ClientID AND DrugName = @DrugName RETURN @@ROWCOUNT END GO
The @@dbts value is pretty much useless,though it is interesting. It shows
what the next rowversion (timestamp) will be whenever you do an insert or update to any table in the database with a rowversion (timestamp) column. For example: set nocount on drop table testRowversion go create table testRowversion ( value varchar(10), version rowversion ) go select @@dbts as lastRowversion go insert into testRowversion (value) values ('one') go select * from testRowversion select @@dbts as lastRowversion go update testRowversion set value = 'changed' go select * from testRowversion select @@dbts as lastRowversion go Returns: lastRowversion ------------------ 0x0000000000000066 value version ---------- ------------------ one 0x0000000000000067 lastRowversion ------------------ 0x0000000000000067 value version ---------- ------------------ changed 0x0000000000000068 lastRowversion ------------------ 0x0000000000000068 -- ---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) [quoted text, click to view] "Dean Slindee" <slindee@charter.net> wrote in message news:JBi5f.19000$1X5.6392@fe05.lga... >I have read that the value of @@DBTS changes on every insert and update. > However I am finding that that is not true. I can do multiple inserts of > rows into a table, and all of the rows will have the same value in the > varbinary(8) column that is populated with the value of @@DBTS at the time > of insert. > > > Single updates to a value in each of these rows also does not change the > varbinary(8) column value either, using the Update stored procedure below. > > I have seen the varbinary(8) column value change (on occasion) but I would > expect it to change immediately upon each insert and update. Please > correct > my expectation, if wrong. > > Thanks, > > Dean S > > Here is the insert stored procedure: > > CREATE Procedure dbo.InsertJusticeClientDrugTimeStamp > > @ClientID Int, > > @DrugName varchar(15), > > @DrugRank tinyint, > > @DrugRoute varchar(10), > > @DrugAmount varchar(20), > > @DrugFrequency varchar(15), > > @DrugAgeBegan varchar(5), > > @DrugLastUsage VarChar(10), > > @DrugLastUsageAmount VarChar(10), > > @DrugOtherName varChar(25), > > @MaintUser VarChar(10), > > @MaintDate SmallDateTime, > > @AddUser VarChar(10), > > @AddDate SmallDateTime > > AS > > SET NOCOUNT ON > > IF EXISTS (SELECT ClientID FROM tblJusticeClientDrug > > WHERE ClientID = @ClientID > > AND DrugName = @DrugName) > > BEGIN > > RETURN -1 > > END > > ELSE > > BEGIN > > INSERT INTO tblJusticeClientDrug > > (ClientID, > > DrugName, > > DrugRank, > > DrugRoute, > > DrugAmount, > > DrugFrequency, > > DrugAgeBegan, > > DrugLastUsage, > > DrugLastUsageAmount, > > DrugOtherName, > > MaintUser, > > MaintDate, > > AddUser, > > AddDate, > > [TimeStamp]) > > VALUES (@ClientID, > > @DrugName, > > @DrugRank, > > @DrugRoute, > > @DrugAmount, > > @DrugFrequency, > > @DrugAgeBegan, > > @DrugLastUsage, > > @DrugLastUsageAmount, > > @DrugOtherName, > > @MaintUser, > > @MaintDate, > > @AddUser, > > @AddDate, > > @@DBTS) > > RETURN @@ROWCOUNT > > END > > GO > > > > Here is the update stored procedure: > > CREATE Procedure dbo.UpdateJusticeClientDrugTimeStamp > > @ClientID Int, > > @DrugName varchar(25), > > @DrugRank tinyint, > > @DrugRoute varchar(10), > > @DrugAmount varchar(20), > > @DrugFrequency varchar(15), > > @DrugAgeBegan varchar(5), > > @DrugLastUsage VarChar(10), > > @DrugLastUsageAmount VarChar(10), > > @DrugOtherName varChar(25), > > @MaintUser VarChar(10), > > @MaintDate SmallDateTime, > > @AddUser VarChar(10), > > @AddDate SmallDateTime, > > @TimeStamp varbinary(8) > > AS > > SET NOCOUNT ON > > IF NOT EXISTS (SELECT ClientID FROM tblJusticeClientDrug > > WHERE ClientID = @ClientID > > AND DrugName = @DrugName > > AND [TimeStamp] = @TimeStamp) > > BEGIN > > RETURN -1 > > END > > ELSE > > BEGIN > > UPDATE tblJusticeClientDrug > > SET DrugRank = @DrugRank, > > DrugRoute = @DrugRoute, > > DrugAmount = @DrugAmount, > > DrugFrequency = @DrugFrequency, > > DrugAgeBegan = @DrugAgeBegan, > > DrugLastUsage = @DrugLastUsage, > > DrugLastUsageAmount = > @DrugLastUsageAmount, > > DrugOtherName = @DrugOtherName, > > MaintUser = @MaintUser, > > MaintDate = @MaintDate, > > AddUser = @AddUser, > > AddDate = @AddDate, > > [TimeStamp] = @@DBTS > > WHERE ClientID = @ClientID > > AND DrugName = @DrugName > > RETURN @@ROWCOUNT > > END > > GO > >
The global variable @@DBTS changes whenever a row is actually inserted into
a table in the current database that has a rowversion column. If the INSERT statement has @@DBTS in the SELECT clause, then @@DBTS is only read once prior to the actual insert. Don't depend on @@DBTS. In a concurrent environment, it has even more volatility than @@IDENTITY, and there isn't any SCOPE_DBTS() function in Transact-SQL that works like SCOPE_IDENTITY(). [quoted text, click to view] "Dean Slindee" <slindee@charter.net> wrote in message news:JBi5f.19000$1X5.6392@fe05.lga... >I have read that the value of @@DBTS changes on every insert and update. > However I am finding that that is not true. I can do multiple inserts of > rows into a table, and all of the rows will have the same value in the > varbinary(8) column that is populated with the value of @@DBTS at the time > of insert. > > > Single updates to a value in each of these rows also does not change the > varbinary(8) column value either, using the Update stored procedure below. > > I have seen the varbinary(8) column value change (on occasion) but I would > expect it to change immediately upon each insert and update. Please > correct > my expectation, if wrong. > > Thanks, > > Dean S > > Here is the insert stored procedure: > > CREATE Procedure dbo.InsertJusticeClientDrugTimeStamp > > @ClientID Int, > > @DrugName varchar(15), > > @DrugRank tinyint, > > @DrugRoute varchar(10), > > @DrugAmount varchar(20), > > @DrugFrequency varchar(15), > > @DrugAgeBegan varchar(5), > > @DrugLastUsage VarChar(10), > > @DrugLastUsageAmount VarChar(10), > > @DrugOtherName varChar(25), > > @MaintUser VarChar(10), > > @MaintDate SmallDateTime, > > @AddUser VarChar(10), > > @AddDate SmallDateTime > > AS > > SET NOCOUNT ON > > IF EXISTS (SELECT ClientID FROM tblJusticeClientDrug > > WHERE ClientID = @ClientID > > AND DrugName = @DrugName) > > BEGIN > > RETURN -1 > > END > > ELSE > > BEGIN > > INSERT INTO tblJusticeClientDrug > > (ClientID, > > DrugName, > > DrugRank, > > DrugRoute, > > DrugAmount, > > DrugFrequency, > > DrugAgeBegan, > > DrugLastUsage, > > DrugLastUsageAmount, > > DrugOtherName, > > MaintUser, > > MaintDate, > > AddUser, > > AddDate, > > [TimeStamp]) > > VALUES (@ClientID, > > @DrugName, > > @DrugRank, > > @DrugRoute, > > @DrugAmount, > > @DrugFrequency, > > @DrugAgeBegan, > > @DrugLastUsage, > > @DrugLastUsageAmount, > > @DrugOtherName, > > @MaintUser, > > @MaintDate, > > @AddUser, > > @AddDate, > > @@DBTS) > > RETURN @@ROWCOUNT > > END > > GO > > > > Here is the update stored procedure: > > CREATE Procedure dbo.UpdateJusticeClientDrugTimeStamp > > @ClientID Int, > > @DrugName varchar(25), > > @DrugRank tinyint, > > @DrugRoute varchar(10), > > @DrugAmount varchar(20), > > @DrugFrequency varchar(15), > > @DrugAgeBegan varchar(5), > > @DrugLastUsage VarChar(10), > > @DrugLastUsageAmount VarChar(10), > > @DrugOtherName varChar(25), > > @MaintUser VarChar(10), > > @MaintDate SmallDateTime, > > @AddUser VarChar(10), > > @AddDate SmallDateTime, > > @TimeStamp varbinary(8) > > AS > > SET NOCOUNT ON > > IF NOT EXISTS (SELECT ClientID FROM tblJusticeClientDrug > > WHERE ClientID = @ClientID > > AND DrugName = @DrugName > > AND [TimeStamp] = @TimeStamp) > > BEGIN > > RETURN -1 > > END > > ELSE > > BEGIN > > UPDATE tblJusticeClientDrug > > SET DrugRank = @DrugRank, > > DrugRoute = @DrugRoute, > > DrugAmount = @DrugAmount, > > DrugFrequency = @DrugFrequency, > > DrugAgeBegan = @DrugAgeBegan, > > DrugLastUsage = @DrugLastUsage, > > DrugLastUsageAmount = > @DrugLastUsageAmount, > > DrugOtherName = @DrugOtherName, > > MaintUser = @MaintUser, > > MaintDate = @MaintDate, > > AddUser = @AddUser, > > AddDate = @AddDate, > > [TimeStamp] = @@DBTS > > WHERE ClientID = @ClientID > > AND DrugName = @DrugName > > RETURN @@ROWCOUNT > > END > > GO > >
Why are you doing this? Look up ROWVERSION in Books Online. The rowversion
column is maintained by the system, you do not need (better: cannot) affect its value - it's set by the system at insert and on each update.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |