Syntax for querying a timestamp column
-
How do I write the syntax to query a column with a data type of timestamp? I want to know how many records have been added/updated since the my last recorded timestamp (part of the DW process).
Select
Count(*) From Fact_Transaction_Sales_Entry Where [timestamp] > 00000000338F7B3A
This gives the following error.
Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'F7B3A'.
How do I represent the timestamp data type in T-SQL?
Thanks
Daniel
-
You can't use timestamp for that purpose.
The timestamp value is a random unique binary value, you cannot assume that there is any particular sequence to all generated timestamps from a given value. It's guranteed to be unique, but not sequential.
It has no other properties than for the purpose of being equal or not equal, never greater than or less than..
/Kenneth
-
Kenneth,
I just got this from books online because my understanding of the timestamp is slightly different.
'Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column'
This seems to indicated that each new timestamp is greater than the previous used even though it has no real meaning.
Thanks Daniel
-
AFAIK, timestamp has never been intended to be an 'incementor', but rather a 'uniqifier'.. In my version of BOL (2k updated) the wording is slightly different:
'The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated.'
I think that the main problem is that, although timestamp indeed is just the binary form of a number, I believe it can be risky at best to rely on it to be ever-incrementing. I mean, who knows if it suddenly decides to generate 'numbers' that are lower than those yesterday..
The documentations ambiguity about words like 'increment' and 'update' between versions also makes this a bit dodgy to rely on. The only gurantee I can find is that of uniqueness, no promises about incrementation. That makes me weary of using it as a counter, anyway.
/Kenneth
-
Thanks Kenneth,
I think I should do some more digging into timestamp fields to get a clear idea of how they work. I say this because I believe we have an incremental loading proceedure for our data warehouse which is based on timestamps. If this isn't incrmental then we could have major problemos!
Cheers,
Daniel
-
From Microsoft's website:
timestamp (Transact-SQL)
Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes.
Remarks
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.
You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. To return the current timestamp value for a database, use @@DBTS.
Reference: http://msdn2.microsoft.com/ms182776.aspx
That says that timestamp IS incremented and to use @@DBTS to find the current value.
-SQLBill
-
Thanks, that confirms my understanding of a how a timestamp works. Does this mean I can query the table using the timestamp in T-SQL as I have tried above?
Thanks Daniel.
-
Daniel,
Although I also agree with Kenneth that it is not the proper use for a timestamp you still can use the syntax of your query as:
Select Count(*) From Fact_Transaction_Sales_Entry Where [timestamp] > 0x00000000338F7B3A
--0x is used to denote binary numbers
Cheers,
* Noel -
Thanks, that worked perfectly!
Being a little concerned I've looked into my data warehouse and I can see from the load control tables that over the last 4 months an average 400,000 records have been incrementally loaded every day into the warehouse from the largest of the source tables. I've just run some quick queries comparing the data in the live system and the data warehouse and they match perfectly. Either we have been extremely lucky or timestamps are incremented in a uniform manor as I believe/hope they should be!
Thanks for all of your comments.
Daniel.
-
>> Either we have been extremely lucky or timestamps are incremented in a uniform manor as I believe/hope they should be! <<
My point is that they may have been behaving like that so far but there is no guarrantie on MS side to maintain such behaviour.
From BOL:
A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.
Cheers,
* Noel -
Daniel,
The other thing you may not be aware of is that if you update ANY column in the table that has a timestamp column, the TimeStamp will change. I think you've been extremely fortunate that you've been using the table as a "read only" table otherwise the sequence of the TimeStamp would not be as expected.
If I could be so bold as to make a suggestion... you might want to consider "fixing" the problem to prevent future catastrophies... if your current table has an IDENTITY (property)column, then you're all set... use that column instead of the TimeStamp column for your "sequencing" because it numbers things in an "auto-magic" fashion.
If you don't have an IDENTITY column in the table, create one. This will be a little tricky because, as I said previously, if you make an update to a row, the TimeStamp will change and THAT will destroy the original order. Instead, we need to make a new table in the correct order with an IDENTITY column... then you can drop the indexes and keys on the old table (if you have any, should at least be a Primary Key there), rename the old table, rename the new table to the same as the old table, and reapply the indexes and keys. Yes, the proper way to do this through Enterprise Manager (it'll automagically handle all indexes and keys) but you'll need to tweek the code EM generates before applying it to ensure the order is not disturbed.
If you could post the schema for the table in question, perhaps also posting some sample data in the form of INSERT statements to make it easier on us, we could create a test table and walk you through the whole process.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply