Tables & Dictionary - Frequently asked Questions (FAQ) - Support and Troubleshooting

Tables & Dictionary - Frequently asked Questions (FAQ) Issue  Overview The goal of this article is to answer a few frequent requests/questions Customer Support receives in relation to Tables & Dictionary. Please append any other questions in the comments so that we can keep this article updated.

This information is generic and if you have follow-up questions, please contact Customer Support.

FAQ

What table extensions models are used? Why is my column label showing as blank or (empty)? What is storage alias and how is it used? Why can I not delete my own custom table in a scoped application? Why can I not add 'update_synch' to my custom table? How many columns can be stored within a physical table Design considerations when creating table/adding columns I am using a calculated field. Why is the order incorrect when applying a sort on this field? I received the error 'Max column count per table reached'. What does this mean?

What table extensions models are used? The Now Platform offers these extension models. Table per class - Creates a separate database table for the parent class and each child class. Table per hierarchy - Creates one database table for the parent class, which stores all records for the parent and child classes. Child classes do not have separate database tables. Table per partition - Creates one database table for the parent class, which stores all records for the parent and child classes. Child classes do not have separate database tables. As the database table reaches a storage limit, the system dynamically adds storage tables ( partitions ) to store additional records. For more information about these extension models and how they differ more information is here https://docs.servicenow.com/csh?topicname=table-extension-and-classes.html&version=latest Why is my column label showing as blank or (empty)? Normally when a column label is showing as blank or empty it means something related to that column is broken, it could be a number of things, If you view the XML of the dictionary record you should see the label is populated but just not displaying in the UI. There is a few things that could cause this: The field was not created correctly in the database The storage alias was not created correctly or is being used by another column. Column is still in progress of being created. If you believe you are encountering this issue, please review KB0727729 and/or contact customer support and we will be able to assist you with this. What is a storage alias and how is it used? A storage alias entry is created for every single field created on a table within an instance. Before getting into what purposes storage aliases serve there are a few important fields that admin/users should know of:

Element Name - This value reflects how the field looks like from the front end. i.e. if a user were to look at the column_name field in sys_dictionary or if a user/admin were to write a script to manipulate values in a field they would base this off of element name.

Storage Alias - This value tells us exactly where the data for a particular element is stored. When fields are being manipulated from the backend database, the platform looks at the storage_alias value to understand what data should be manipulated based off of the storage alias value as well as the sys_class_name which is the actual value of the table class where data is being manipulated. The storage alias value is the actual physical column on the base table.

Storage table name - This value tells us what physical table the element is part of. For all logical elements in task, the storage table name will always be task. If this is a TPC table the storage table name value will be the name of the physical table where the physical element lives.

Storage Alias are used for: 1. Mapping logical TPH)/physical (TPC) elements to actual physical columns in the backend database. In simple terms, this means it tells us where the actual data in the element lives from the column in the physical storage tables.

2. Enable multiple sibling elements to share one physical column (also known as glomming but only for TPH)

3. Mapping sys_documentation (label) records to their respective elements which is what users/admins see from the application front end on forms, reports, and list views.

Additional things to know:

- Two logical elements within the same logical class can never share the same physical column (i.e. If a user created two string fields on incident they would not map to the same physical column in the database).

- A parent element and a child element can never share the same physical column (ie. If a field is created on incident it cannot be mapped to a physical column where a field on task is already using the physical column).

- Only sibling elements can share the same physical column (i.e A user can create a reference field on change_request and incident and map to the same physical column).

- If a field is created directly on the task table (where sys_class_name is task) it can never be glommed.

Why can I not delete my own custom table in a scoped application? When trying to delete a custom table from a scoped application the "Delete Table" button is not available. This is because of a known problem: PRB762839 which has an intended fix version of Orlando

We have restrictions in our system so you do not delete specific tables in your instance and currently you are blocked from deleting custom tables in scope applications. Currently the workaround is to contact Customer Support and they will assist you in removing the customer table from the scoped application.

More Information here: KB0623901

Why can I not add 'update_synch' to my custom table? The reason for this is stated in our documentation here

Warning: Do not add the update_synch attribute to a dictionary record. When improperly used, this attribute can cause major performance issues or cause the instance to become unavailable. Adding this attribute is not supported.

We do however have an OOB UI Action on sys_db_object "Track in Update Sets" which can be used to get a custom table to be tracked in update-sets by getting this re-parented to sys_metadata. But this should be used with caution and used in legitimate limited circumstances.

To use this UI action there is a list of criteria that it needs to pass to use this UI action so please review all the information on this KB before proceeding: KB0726953

How many columns can be stored within a physical table The number of columns within a table is defined by the database used and not by the ServiceNow platform

MYSQL

MySQL limit, not controlled by ServiceNow: There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size.

Oracle

Oracle supports up to 1000 columns per physical table. Please refer to Oracle documentation regarding this:

https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits003.htm#i288032

Design considerations when creating table/adding columns It is recommended at the design stage to look at the normalization of your data to reduce white spaces and ensure your data is organized efficiently

There are numerous external articles regarding database normalization as this is not a ServiceNow process i.e.

https://en.wikipedia.org/wiki/Database_normalization

Please find a simplistic example of something considered bad design:

Table: u_research

column_name Type Max Length u_finding_1 string 40 u_finding_2 string 40 ... ... ... u_finding_9 string 40 In this example, there is a table with 9 fields named u_finding followed by a prefix of 1-9

If a record was inserted which only populated 2 of the 9 fields, it would leave 7 fields unpopulated. These 7 fields are what is considered white spaces

To resolve this issue you could create a separate table to store all the finding which references the u_research table i.e.

Table: u_finding

column_name Type Max Length u_finding details string 40 u_research reference 32 Every finding would be created in the table u_finding and link to the originating table u_reserach. This way a u_research record could have multiple findings. This approach would eliminate white space

I am using a calculated field. Why is the order incorrect when applying a sort on this field? With calculated fields, the data is returned in real-time when accessing the data via a list view. When sorting on the field the sort will be applied based on the values saved in the database

The values stored in the database will be from when the record was last saved which can differ from real-time data

If you really need to sort on this field you should consider removing the calculated option and creating business rules to ensure the field is updated when related records are updated.

I received the error "Max column count per table reached". What does this mean? The system can only have a maximum of 1000 columns per table. If you receive this error it is because you have reached this limit.

Please note that although it is defined that 1000 columns is a specified limit, this does not mean you can physically have 1000 columns within a table. See section: "How many columns can be stored within a physical table"