Help with wide SQL Server tables

Ali Shah
Ali Shah used Ask the Experts™
Hi Folks,
I have written a SQL code generator where end users can define their own tables independent of developers. These are financial tables and so the structure can be quite weird and row size can exceed 8k. To tackle this for these tables i am using NVARCHAR(MAX) as data types for the columns and also using  
EXEC sys.sp_tableoption 'dbo.table_names','large value types out of row','ON'

Open in new window

but still i get the following error message.
Cannot create a row of size 9307 which is greater than the allowable maximum row size of 8060.

Open in new window

Can you please help?

I have attached the file of one of the tables

regardsTable.sql
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
There really is no silver bullet here to allow table creation to be completely dynamic and to guarantee that row size / column count falls under certain limitations, unless you are willing to have multiple tables for a given table, perhaps with a _1, _2, etc. suffix, and primary keys in each.   I had a previous client that created SalesForce.com tables of 1300+ columns, and that was the only solution.

HIGHLY recommend that at minimum you add to your tool the ability of the user to identify numbers, dates, and bit columns, and for the column data types to appropriately reflect that.

Also keep in mind that by defining numbers and dates as a char column that sorting will be done by character and not by numeric/date, which may lead to incorrect sorts.  And you're opening up the possibility of somebody entering invalid data, such as '2019-02-42', '2019-02-20 and 21', or 'banana' into an otherwise date column that would cause any date logic function to fail.
Ali ShahSQL Developer

Author

Commented:
Thanks Jim,
You are right table split is the only option here.
Regards and thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial