Can we create a field with length of string(16000)?

[mallikharjunasw]

Can we create a field with length of string(16000)?

[ARG645]

Q: Can I create or make a string data type field's length as 8000?

A: Yes you can

But here's the thing, I highly encourage you NOT to create such a gigantic string field. few months ago we have encountered a problem with one of our tables where we had too many large string fields. Each field was around 1000. Below is the error that we were receiving 

Syntax Error or Access Rule Violation detected by database (Row size too large (> 8126).

Below is what we concluded after discussing with ServiceNow. 

You can use only a fraction of real physical row size available to you in MySQL / MariaDB (depending what instance version). 

Basically any field you add will use up some space depending on the field size - e.g. string with max. length 40 will be mapped as varchar(40) and will use up exactly the number of bytes stored in it for each record (max. 40).

If you use true/false - this maps to tinyint(1) and should use only 1 byte of space - but I have to confirm this with SN as I'm not 100% sure.

If you add bigger fields, such as string field >255 lengths or any HTML-type field, it is a bit more complex. Every such field will use up to 768 bytes of your limit (less if the text in specific field is shorter), and if there is more content, all the remaining data is kept in a BLOB via reference - but you don't have to care how it works as it happens in the DB layer. You only have to know that if you put 10 HTML fields on a table, you are basically screwed as this eats up 7680 bytes potentially.

Solutions officially proposed by SN, and to be honest, I don't see too many alternatives:

1. Remove unused fields (especially strings) and try to reuse existing fields if possible

2. Reduce max field length for stings (quite often you change the max length only so that the field appears bigger in the UI but is it really necessary)?

3. what we had to do in one of our projects - change big string fields and HTML fields into reference fields. Create a custom table where all that content will be stored and use it in your references. This basically means you reduce the size needed for every field from 768 to 32 bytes. It is not an easy process though:

• check for existing data - you will have to write a background script to copy existing data to the new table automatically if you want existing data to work.
• check for any scripts that are using the data from those fields - you will have to update all scripts not to get the value from the field directly, but rather by reference. eg. previously it was your table.yourfield --> now it will be your table.yourreferencefield.newtablecontentfield
• you may have some issues with changing field types - we did it via script and I remember there was some funny stuff we had to do to make it work. You can do it manually one by one which takes more time but is more secure.

Hope this clarifies some of it. I wish ServiceNow released official documentation on field sizes and mapping as the current docs are not really useful for architects.

You do not have to worry about fields on your base table such as task. Those are not eating up space on the table you extend from task.

Thank you,

A.R.G.

Please mark as Correct Answer/Helpful, if applicable.

References:

Row size too large (> 8126) - Working with string heavy requirements