If you've ever needed to create a ServiceNow table extension for rotating one of your ServiceNow tables (the feature that can be found under System Definition> Table Rotations in the Filter Navigator), then this may help you as I was a little unclear on how to use them after reviewing the ServiceNow documentation and the information I found online.
ServiceNow's documentation on Table Rotation and Extension along with this ServiceNow community article will give you an initial understanding on why you want to do a rotation and/or extension when you have tables that can grow in size and you want to maintain the performance in your instance.
If you don't need to keep the table data forever then a Rotation is a good option and is explained clearly in the above documentation. If you just need the data for historical/auditing purposes and don't plan to access it directly any further after a certain amount of time (i.e. you don't need to reference for viewing in the UI, querying against in your scripting, etc), then Data Archiving is another alternative.
However, if you do want to keep the data around permanently and be able to reference it, you can chose Extension or Shard as the rotation type:
What exactly are these two options?
Extension
With table extension, the table is split into time-based "shards" where a shard is a partition of the table's data for a specific duration of time.
Defining a Table Extension
For example, if you define an extension for the u_demo table of 3 days, it will create the following:
In this case, the extension was defined on January 2, 2025 19:13:56 so what ServiceNow does is make your current table the "base" table (u_demo) with all data in the table up to this point (labeling it to have data from Epoch time until now), and then creates two "shard" tables, one to be used currently and then another to be used after the current one. A shard is a table that has the same structure as your base table, has the same name except with #### appended i.e. u_demo0000 (where #### will start from 0000 and increment by 1 to be unique) and will be used to separate your table into smaller chunks ("shards") to improve performance of your instance.
Note for my example my instance saves into UTC time in the backend database but the UI is configured to display in Pacific timezone which is why you see the Epoch time as December 31, 1969 16:00:00 in the above u_demo Table Extension example image).
Each shard will have a valid date range equal to the duration specified in your extension definition. The date range tells you which shard will be used when new records are created in the u_demo table. In this case, where we specified 3 days as the duration, we have a shard for January 2, 2025 19:13:56 through January 5, 2025 19:13:56 (u_demo0000) and then another similar one for January 5-8 (u_demo0001).
So during the timeframe of January 2, 2025 19:13:56 through January 5, 2025 19:13:56, any new records created in the u_demo table will be saved in the u_demo0000 shard table. Then once you reach January 5, 2025 19:13:56 and until January 8, 2025 19:13:56, any new records during that time will be saved into the u_demo0001 table.
When the starting date and time of the second shard table u_demo0001 is reached at which point it becomes the active shard, the next shard will be created, which in this example would be u_demo0002 for the 3 day duration of January 8, 2025 19:13:56 through January 11, 2025 19:13:56. ServiceNow will create the next shard earlier than it;s needed to ensure no disruptions in adding new records to the table as the next shard becomes the active one.
Updating Table Extension Duration
If you want to change the duration for your table extension, this will only affect new shards created going forward and does not affect the current shards. So in this case, if I were to change my duration to 1 day and made this change on January 3rd, 2025, nothing would happen until January 5 (when the next shard u_demo0001 becomes active), at which point the u_demo0002 shard would be created with a 1 day duration valid from January 8, 2025 19:13:56 through January 9, 2025 19:13:56. The previous shards will keep their durations of 3 days.
This is notable for cases where you may have defined a long duration and now want it to be shorter. For example, say you created a table extension with a duration of 30 days on January 2nd as follows:
Table name | Valid from | Valid to |
u_demo | 1969-12-31 16:00:00 | 2025-01-02 19:13:56 |
u_demo0000 | 2025-01-02 19:13:56 | 2025-02-01 19:13:56 |
u_demo0001 | 2025-02-01 19:13:56 | 2025-03-03 19:13:56 |
Then you decide on January 15 that this duration is too long as performance is still too slow for querying data from this table so you change the duration on the table extension definition to 10 days. Since we already have shards that run until March 3rd, only the next shard will be created as 10 days and this won't happen until the u_demo0001 shard becomes active on February 1st (going by what we mentioned before where the new shard is created only when the next shard becomes active).
You will then end up with shards as follows:
Table name | Valid from | Valid to |
u_demo | 1969-12-31 16:00:00 | 2025-01-02 19:13:56 |
u_demo0000 | 2025-01-02 19:13:56 | 2025-02-01 19:13:56 |
u_demo0001 | 2025-02-01 19:13:56 | 2025-03-03 19:13:56 |
u_demo0002 | 2025-03-03 19:13:56 | 2025-03-13 19:13:56 |
This is worth nothing since your current shard will still be the previously defined larger duration (and as a result a larger shard with more records) until the next shard is created with your updated duration. If you delete the table extension, this will delete all the shards and the data in them, leaving you will only the base table (u_demo) and the records that are in the base table, which are all the records before you defined the table extension.
Shard
The Shard option is basically the same as the Extension option except it does it by sys_id and with 16 specific shards.
Say we have a table named u_2nd_demo and we decide we want to use the Shard type for how to extend the table. This will create shards as follows:
Since ServiceNow uses hexadecimal numbering for their sys_id values, this means sys_id values can only contain characters/digits 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, a, b, c, d, e, f which are 16 different options that can be used for each of the 32 characters in a sys_id. For example see the sys_id b9529b2ac3321210c958d72d05013113.
Because of this, the Shard option will split saving the records by sys_id since there are 16 different options and 16 different shards are created to match this. The benefit of this approach is records created will be saved evenly throughout the 16 shards and may take less time to query when you are querying across multiple time ranges (a potential slowness issue with the Extension time-based approach).
The negative is there will only be 16 shards so if you end up with a table that has a lot of data, the Extension time-based approach may make more sense since it will continue to create shards for you to end up with many smaller sized shards versus the fixed 16 shards of the Shard option that may result in many records in each shard.
Scripting and Accessing Extended Tables
Once you've defined an extension for a table (whether using the Extension or Shard option), if you want to access the table through scripting or through the UI (such as through the list view or creating a new record through a form), you would reference the table as you normally would as though it didn't have an extension on it.
Using our example, you would query the u_demo table in your script:
var dgr = new GlideRecord("u_demo");
and not the shards themselves (u_demo0000, u_demo0001, etc) as the shards are intended to be transparent to users. ServiceNow does note that the performance can be slower when querying for records that are in different time ranges (different shards) since it has to do a union query to get the results together from multiple shards. But this trade-off is better than trying to query one large table that would be slow for any query.
If you are going to make changes to the table structure (such as adding new columns, increasing column lengths, etc), the "Synchronize Shards" UI action on the table extension definition will need to be used so the structure on the table can be copied over to the shards. Otherwise, the structures of the shards will not have these changes and you will lose any data saved into the changed/new columns.
Hopefully this helps provide some more context on table extensions. Feel free to reach out to me if you want to discuss this or anything else further.
Saiborne offers software consulting services to help companies meet their business objectives and get the most value out of their software products. We specialize in ServiceNow solutions with our certified experience including development of apps released to the ServiceNow Store with our previous companies. Contact us to find out how we can help you with any ServiceNow needs.