I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Sorting data with row size greater than 8094 works in SQL Server 2005

When selecting data from a table we can select rows which contain more than 8094 bytes.

The problem arises when trying to sort those rows.

 

in SQL Server 2000 this code:

CREATE TABLE t1 (id INT IDENTITY(1,1), c1 VARCHAR(8000))
CREATE TABLE t2 (id INT IDENTITY(1,1), c2 VARCHAR(8000)) 

INSERT INTO t1 (c1)
SELECT REPLICATE('a', 8000) UNION ALL 
SELECT REPLICATE('a', 8000) UNION ALL 
SELECT REPLICATE('a', 8000) UNION ALL 
SELECT REPLICATE('a', 8000) UNION ALL 
SELECT REPLICATE('a', 8000) UNION ALL 
SELECT REPLICATE('a', 8000) UNION ALL 
SELECT REPLICATE('a', 8000) 

INSERT INTO t2 (c2)
SELECT REPLICATE('b', 8000) UNION ALL 
SELECT REPLICATE('b', 8000) UNION ALL 
SELECT REPLICATE('b', 8000) UNION ALL 
SELECT REPLICATE('b', 8000) UNION ALL 
SELECT REPLICATE('b', 8000) UNION ALL 
SELECT REPLICATE('b', 8000) UNION ALL 
SELECT REPLICATE('b', 8000) 

SELECT    *
FROM    t1
        JOIN t2 ON t1.id = t2.id
ORDER BY c1, c2 desc 

DROP TABLE t1
DROP TABLE t2

results in this error:

Cannot sort a row of size 16018, which is greater than the allowable maximum of 8094.

 

When trying this same thing in SQL Server 2005 it works OK.

It was a nice surprise i must say.

 

Note that the acctual length of the row is considered and not the defined one.

So for varchar(8000) with only 1000 characters, the row size is 1000 bytes and not 8000 bytes.

Print | posted on Tuesday, June 26, 2007 4:12 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Sorting data with row size greater than 8094 works in SQL Server 2005

Excellent !!! great investigation. Thanks.
10/25/2007 6:47 AM | SRIVI
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET