Max row size problem

[habneh]

I am working on sql server 2000,
I am facing an exceeding max row size problem, Is there a way, I can mitigate this problem?

I am selecting certain columns, and when I am tring to sort them, it is complaing that it is not sorting because it exceeded the 8K limit.
what should I do

thanks,

 

[SQLDenis]

from BOL

The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. For more information about the maximum ORDER BY clause size


do you really need to sort by the whole column? is the data not different in the first 100 bytes?

try doing something like this

order by left(col1,100),left(col2,100) etc etc etc just grab the first 100 bytes



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[AlexCuse]

Can you show your table design? You should probably split it (or consider usint text columns, I'd split)

Here is the definitive thread on the subject

http://www.tek-tips.com/viewthread.cfm?qid=1350342&page=1

Hopefully you can take the advice better than the last guy ;-)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Sorry for the 'Worthless Opinion' Habneh. I thought your table was over 8060 bytes wide. My mistake

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

Mmmmm

select replicate ('a',50000) col1,replicate ('b',50000) col2
union all
select replicate ('z',50000),replicate ('b',50000)
union all
select replicate ('a',50000),replicate ('t',50000)
union all
select replicate ('c',50000),replicate ('i',50000)
union all
select replicate ('g',50000),replicate ('p',50000)
union all
select replicate ('a',50000),replicate ('q',50000)
order by col2,col1

select * from (select replicate ('a',50000) col1,replicate ('b',50000) col2
union all
select replicate ('z',50000),replicate ('b',50000)
union all
select replicate ('a',50000),replicate ('t',50000)
union all
select replicate ('c',50000),replicate ('i',50000)
union all
select replicate ('g',50000),replicate ('p',50000)
union all
select replicate ('a',50000),replicate ('q',50000)) z
order by col2,col1

no problem here ;-)


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[AlexCuse]

Denis - I think your test is flawed. Try this:

[COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (di [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), col1 [COLOR=blue]varchar[/color](5000))
[COLOR=blue]declare[/color] @t2 [COLOR=blue]table[/color](di [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), col2 [COLOR=blue]varchar[/color](5000))


[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'a'[/color],5000) 
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'z'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'a'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'c'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'g'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'a'[/color],5000)

[COLOR=blue]insert[/color] @t2
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'a'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'z'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'a'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'c'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'g'[/color],5000)
union all
[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color] ([COLOR=red]'a'[/color],5000)

[COLOR=blue]select[/color] a.Col1, b.Col2
[COLOR=blue]from[/color] @t a [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @t2 b
[COLOR=blue]on[/color] a.di = b.di
[COLOR=blue]order[/color] [COLOR=blue]by[/color] a.Col1, b.Col2

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

interesting, these 2 don't work either

--order by left(a.Col1,100), left(b.Col2,100)
--order by convert(varchar(100),a.Col1),convert(varchar(100),b.Col2)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

>>I have tried the
order by left(col1, 100)
left(col2, 100)

I believe you wanted to post here.

Yest it doesn't work because SQL server still has to create a work table to sort this :-(

it will work if your data is text, take a look at this

declare @t table (di int identity(1,1), col1 text)
declare @t2 table(di int identity(1,1), col2 text)


insert @t
select replicate ('a',5000) 
union all
select replicate ('z',5000)
union all
select replicate ('a',5000)
union all
select replicate ('c',5000)
union all
select replicate ('g',5000)
union all
select replicate ('a',5000)

insert @t2
select replicate ('a',5000)
union all
select replicate ('z',5000)
union all
select replicate ('a',5000)
union all
select replicate ('c',5000)
union all
select replicate ('g',5000)
union all
select replicate ('a',5000)


select a.Col1,b.Col2
from @t a inner join @t2 b
on a.di = b.di
order by convert(varchar(100), a.Col1),convert(varchar(100),b.Col2)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions