Get sum of data in all columns of a table.

  • We are in process of consolidation of 2 databases. We get an error loading data into one of the table as it says:

    data size exceeds the max limit 8060 bytes.

    Is there any way or does anyone have a script to find out which row has more then the max defined. Basically the table has columns defined which sum upto 19000 bytes. The table we are trying to insert is identical to the source. What i need is script to find the max value of data in row.We have PK's defined to identify unique record. Any help would be greatly appriciated.

    Thanks.

  • This was removed by the editor as SPAM

  • Are you consolidating 2 SQL Server databases?

    You can find the total length of the record by executing:

    SELECT [PrimaryKeyField], (LEN([Field1]) + LEN([Field2]) ... all fields) AS "DATA_LEN" FROM [TableName]

    optionally putting in a WHERE CLAUSE:

    WHERE (LEN([Field1]) + LEN([Field2]) ... all fields) > 8000

    ps. Is it a neccessity to store all the fields in the same table? Perhaps vertically partioning the data into separate tables, or throwing data into TEXT field type would be a better storage design. The TEXT field type would simply store a 16-byte pointer to the data, instead of all the actual data in the table extent itself.

    HTH,

    Jay

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply