Hello and welcome!
Thank you for dropping by to read our latest How To Blog Post.
This one is quite simple.
In many databases today you will find a lot of character fields set to varchar or nvarchar.
They are often set to lengths such as 255 because in virtually all applications it makes no difference that the field is defined to be much longer than the longest character string placed in it.
However, in the Meta5 query tool it allocates the maximum string length in the output window as individual columns are not scrollable or truncatable.
So these varchar 255 fields tend to take up much more space than is needed.
So what we do to solve this is to run a simple query to set the length of character fields to be the length of the longest field found.
Obviously if you are adding longer fields you need to re-run this sql.
SELECT
concat ('update WT_COLUMNS set PRECISIO = ' ,'(select
max (len ( ',COLUMNNAME,')) from ' ,TABLENAME, ' )
where TABLENAME =
''', TABLENAME , ''' and COLUMNNAME = ''' , COLUMNNAME , ''' ;' )
FROM [dbo].[WT_COLUMNS]
where not tablename like 'WT%'
and datatype = 2 ;
The sql statement above can be run on the WTDD tables in SQL Server.
Obviously other databases have similar but different syntax to achieve the same result.
On SQL Server this will produce update statements similar to the following:
update WT_COLUMNS set PRECISIO
= (select max (len (
promotion_name)) from
dbo.Promotion ) where TABLENAME = 'dbo.Promotion' and
COLUMNNAME = 'promotion_name' ;
update WT_COLUMNS set PRECISIO
= (select max (len (
price_reduction_type)) from dbo.Promotion )
where TABLENAME = 'dbo.Promotion' and
COLUMNNAME = 'price_reduction_type' ;
And then you simply run those statements which will set the length of the field in the WTDD.
We hope this saves you some time and trouble figuring this one out for your self!
With that?
I would like to say thank you very much for reading our blog post today!
We really appreciate your time and attention!
Best Regards.
Mihai Neacsu.