Is there a way around the 8k row length limit in SQL Server?

Question

First off, I know that in general having large numbers of wide columns is a bad idea, but this is the format I'm constrained to.

I have an application that imports CSV files into a staging table before manipulating them and inserting/updating values in the database. The staging table is created on the fly and has a variable number of NVARCHAR colums into which the file is imported, plus two INT columns used as row IDs.

One particular file I have to import is about 450 columns wide. With the 24 byte pointer used in a large NVARCHAR column, this adds up to around 10k by my calculations, and I get the error Cannot create a row of size 11166 which is greater than the allowable maximum row size of 8060.

Is there a way around this or are my only choices modifying the importer to split the import or removing columns from the file?

Solution 1

If you are using SQL Server 2005, 2008 or 2012, you should be able to use NVARCHAR(max) or NTEXT which would be larger than 8,000 characters. MAX will give you 2^31 - 1 characters:

http://msdn.microsoft.com/en-us/library/ms186939(v=sql.90).aspx

Solution 2

You can use text/ntext which uses 16 bytes pointer. Whereas varchar/nvarchar uses 24bytes pointer.

NVARCHAR(max) or NTEXT can store the data more than 8kb but a record size can not be greater than 8kb till SQL Server 2012. If Data is not fitted in 8kb page size then the data of larger column is moved to another page and a 24 bytes(if data type is varchar/nvarchar) pointer is used to store as reference pointer in main column. if it is text/ntext data type then 16 bytes poiner is used.

Solution 3

For Details you can Visit at following links :

Work around SQL Server maximum columns limit 1024 and 8kb record size

or

http://msdn.microsoft.com/en-us/library/ms186939(v=sql.90).aspx