Returning the length and IDs of recordsets

Question

Post reply

Returning the length and IDs of recordsets

hot2use

SSCrazy

Points: 2915
More actions
August 27, 2005 at 3:27 am

#168424

Hi there
Was trying to update various columns from varchar(2000) to nvarchar(2000) in a table and received a "...exceeds 8060 byte limit." error message.
I know you can't have more then 8060 bytes of data in a recordset so I need to retrieve the rows that contain the most data from the table.
I tried:
SELECT MAX(LEN(COLUMN1)+LEN(COLUMN2)+LEN(COLUMN3)) FROM TABLE1
This works if the columns don't contain any NULL values. As soon as a column contains NULL then the result is NULL even if I set CONCAT_NULL_YIELDS_NULL to FALSE.
Anybody have an idea how to retrieve the biggest recordset from a table if some of the columns contain NULL values? (It's a table with 40+ columns)
Cheers!

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Try this:

SELECT MAX(LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,''))) FROM TABLE1

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 2

Would you please post your DDL, and the statements you are using to alter the columns.

Thanks

Mike

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 3

You might also want to check BOL for information on varchar and nvarchar. nVarchar has a max length of 4000 characters as it requires 2 bytes for each character. If your varchar(2000) will require nvarchar(4000).

HTH

Mike

hot2use SSCrazy Points: 2915 More actions · Answer 4

Just what the doctor ordered.

Cheers!

I am now running the script like this:

SELECT * FROM TABLE1

WHERE LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,'')) in (

SELECT MAX(LEN(isnull(COLUMN1,''))+LEN(isnull(COLUMN2,''))+LEN(isnull(COLUMN3,''))) FROM TABLE1

)

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

hot2use SSCrazy Points: 2915 More actions · Answer 5

I don't think the DDL for an 80 columns table would be very interesting. I know myself that it's a freaky table.

Thanks anyway.

I've done the ADMIN courses for MS-SQL and did realize that converting from varchar to nvarchar would require double the space. It was all about finding that one recordset or the recordsets that had lengths above 4030 bytes.

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 6

Yep an 80 column table would not be very interesting but I was thinking that the data type of some of the columns would allow you to eliminate them from your select statement.

As your original post stated that you wanted to convert a char(2000) to a varchar(2000) I was not sure that you realized that the varchar required 2 bytes for every byte of a char data type.

Glad you solved the problem.

Mike