none
Need Help with TimeStamp Strategy

    Question

  • We'll be moving some data from SQL Server to Azure Table Storage (ATS). In our SQL Server table, we have a field named "EntryTimeStamp" which captures the exact date/time of the entry in UTC and we never edit this field.

    In ATS, there's already a TimeStamp property which, as I understand it, cannot be modified. I was hoping to be able to use the ATS TimeStamp property to store the EntryTimeStamp values coming from our SQL Server table and looks like I can't do it.

    Clearly, I have the option of adding an EntryTimeStamp property to the data I'll save in ATS but then I won't be able to query that property and being able to query our data for EntryTimeStamp is important.

    Any suggestions as to how I should handle this? Am I out of options and simply need to store my EntryTimeStamp values in its own property or is there a way for me to store my values in the ATS TimeStamp property?


    Thanks, Sam

    Friday, November 27, 2015 10:13 PM

Answers

  • So a few things here:

    1. Timestamp is a system defined property and gets set only by Azure Table Service when an entity is created or updated. You as a user can't set the value for this property.
    2. You mentioned that you would like to query on Timestamp field (for the sake of argument let's assume that you're allowed to set the value for this field). It's same as querying on any other field (except PartitionKey and RowKey). Your query would be performing full table scan if your query doesn't include PartitionKey and/or RowKey.

    Since I don't know much about the application design and querying requirements, only recommendation I can give is to see if you can set PartitionKey value to this EntryTimeStamp value (you may need to convert that value into Ticks and pad the ticks value with zeros to get equal length string). PartitionKey value once assigned can't be changed and the data in ATS in always indexed by PartitionKey and RowKey.

    Hope this helps.

    • Marked as answer by imsam67 Saturday, November 28, 2015 4:30 PM
    Saturday, November 28, 2015 4:14 PM

All replies

  • So a few things here:

    1. Timestamp is a system defined property and gets set only by Azure Table Service when an entity is created or updated. You as a user can't set the value for this property.
    2. You mentioned that you would like to query on Timestamp field (for the sake of argument let's assume that you're allowed to set the value for this field). It's same as querying on any other field (except PartitionKey and RowKey). Your query would be performing full table scan if your query doesn't include PartitionKey and/or RowKey.

    Since I don't know much about the application design and querying requirements, only recommendation I can give is to see if you can set PartitionKey value to this EntryTimeStamp value (you may need to convert that value into Ticks and pad the ticks value with zeros to get equal length string). PartitionKey value once assigned can't be changed and the data in ATS in always indexed by PartitionKey and RowKey.

    Hope this helps.

    • Marked as answer by imsam67 Saturday, November 28, 2015 4:30 PM
    Saturday, November 28, 2015 4:14 PM
  • Thank you for your response. As you suggested, I'll have to make the EntryTimeStamp part of my composite key.

    Thanks, Sam

    Saturday, November 28, 2015 4:31 PM