February 26, 2013 at 11:47 pm
We are using SQl server 2008 R2 ,
we have such requirement so one of our table have 500 column .
according to sql server BOL non wide table can have 1024 Column and Nvarchar(max) can store bytes up to 2^30-1
i just took Nvarchar(max) actually in original script length is nvarchar(255)
so i try to simulate situation so i can product error and i am getting error as follows.
"Msg 511, Level 16, State 1, Line 507
Cannot create a row of size 13079 which is greater than the allowable maximum row size of 8060."
can any one tell me what is wrong?
below is code
USE tempdb
Go
IF OBJECT_ID('Temp') IS NOT NULL
DROP TABLE Temp
Go
CREATE TABLE Temp
(
id INT ,
NOs INT ,
VALUE1 NVARCHAR(MAX) ,
VALUE2 NVARCHAR(MAX) ,
VALUE3 NVARCHAR(MAX) ,
VALUE4 NVARCHAR(MAX) ,
VALUE5 NVARCHAR(MAX) ,
VALUE6 NVARCHAR(MAX) ,
VALUE7 NVARCHAR(MAX) ,
VALUE8 NVARCHAR(MAX) ,
VALUE9 NVARCHAR(MAX) ,
VALUE10 NVARCHAR(MAX) ,
VALUE11 NVARCHAR(MAX) ,
VALUE12 NVARCHAR(MAX) ,
VALUE13 NVARCHAR(MAX) ,
VALUE14 NVARCHAR(MAX) ,
VALUE15 NVARCHAR(MAX) ,
VALUE16 NVARCHAR(MAX) ,
VALUE17 NVARCHAR(MAX) ,
VALUE18 NVARCHAR(MAX) ,
VALUE19 NVARCHAR(MAX) ,
VALUE20 NVARCHAR(MAX) ,
VALUE21 NVARCHAR(MAX) ,
VALUE22 NVARCHAR(MAX) ,
VALUE23 NVARCHAR(MAX) ,
VALUE24 NVARCHAR(MAX) ,
VALUE25 NVARCHAR(MAX) ,
VALUE26 NVARCHAR(MAX) ,
VALUE27 NVARCHAR(MAX) ,
VALUE28 NVARCHAR(MAX) ,
VALUE29 NVARCHAR(MAX) ,
VALUE30 NVARCHAR(MAX) ,
VALUE31 NVARCHAR(MAX) ,
VALUE32 NVARCHAR(MAX) ,
VALUE33 NVARCHAR(MAX) ,
VALUE34 NVARCHAR(MAX) ,
VALUE35 NVARCHAR(MAX) ,
VALUE36 NVARCHAR(MAX) ,
VALUE37 NVARCHAR(MAX) ,
VALUE38 NVARCHAR(MAX) ,
VALUE39 NVARCHAR(MAX) ,
VALUE40 NVARCHAR(MAX) ,
VALUE41 NVARCHAR(MAX) ,
VALUE42 NVARCHAR(MAX) ,
VALUE43 NVARCHAR(MAX) ,
VALUE44 NVARCHAR(MAX) ,
VALUE45 NVARCHAR(MAX) ,
VALUE46 NVARCHAR(MAX) ,
VALUE47 NVARCHAR(MAX) ,
VALUE48 NVARCHAR(MAX) ,
VALUE49 NVARCHAR(MAX) ,
VALUE50 NVARCHAR(MAX) ,
VALUE51 NVARCHAR(MAX) ,
VALUE52 NVARCHAR(MAX) ,
VALUE53 NVARCHAR(MAX) ,
VALUE54 NVARCHAR(MAX) ,
VALUE55 NVARCHAR(MAX) ,
VALUE56 NVARCHAR(MAX) ,
VALUE57 NVARCHAR(MAX) ,
VALUE58 NVARCHAR(MAX) ,
VALUE59 NVARCHAR(MAX) ,
VALUE60 NVARCHAR(MAX) ,
VALUE61 NVARCHAR(MAX) ,
VALUE62 NVARCHAR(MAX) ,
VALUE63 NVARCHAR(MAX) ,
VALUE64 NVARCHAR(MAX) ,
VALUE65 NVARCHAR(MAX) ,
VALUE66 NVARCHAR(MAX) ,
VALUE67 NVARCHAR(MAX) ,
VALUE68 NVARCHAR(MAX) ,
VALUE69 NVARCHAR(MAX) ,
VALUE70 NVARCHAR(MAX) ,
VALUE71 NVARCHAR(MAX) ,
VALUE72 NVARCHAR(MAX) ,
VALUE73 NVARCHAR(MAX) ,
VALUE74 NVARCHAR(MAX) ,
VALUE75 NVARCHAR(MAX) ,
VALUE76 NVARCHAR(MAX) ,
VALUE77 NVARCHAR(MAX) ,
VALUE78 NVARCHAR(MAX) ,
VALUE79 NVARCHAR(MAX) ,
VALUE80 NVARCHAR(MAX) ,
VALUE81 NVARCHAR(MAX) ,
VALUE82 NVARCHAR(MAX) ,
VALUE83 NVARCHAR(MAX) ,
VALUE84 NVARCHAR(MAX) ,
VALUE85 NVARCHAR(MAX) ,
VALUE86 NVARCHAR(MAX) ,
VALUE87 NVARCHAR(MAX) ,
VALUE88 NVARCHAR(MAX) ,
VALUE89 NVARCHAR(MAX) ,
VALUE90 NVARCHAR(MAX) ,
VALUE91 NVARCHAR(MAX) ,
VALUE92 NVARCHAR(MAX) ,
VALUE93 NVARCHAR(MAX) ,
VALUE94 NVARCHAR(MAX) ,
VALUE95 NVARCHAR(MAX) ,
VALUE96 NVARCHAR(MAX) ,
VALUE97 NVARCHAR(MAX) ,
VALUE98 NVARCHAR(MAX) ,
VALUE99 NVARCHAR(MAX) ,
VALUE100 NVARCHAR(MAX) ,
VALUE101 NVARCHAR(MAX) ,
VALUE102 NVARCHAR(MAX) ,
VALUE103 NVARCHAR(MAX) ,
VALUE104 NVARCHAR(MAX) ,
VALUE105 NVARCHAR(MAX) ,
VALUE106 NVARCHAR(MAX) ,
VALUE107 NVARCHAR(MAX) ,
VALUE108 NVARCHAR(MAX) ,
VALUE109 NVARCHAR(MAX) ,
VALUE110 NVARCHAR(MAX) ,
VALUE111 NVARCHAR(MAX) ,
VALUE112 NVARCHAR(MAX) ,
VALUE113 NVARCHAR(MAX) ,
VALUE114 NVARCHAR(MAX) ,
VALUE115 NVARCHAR(MAX) ,
VALUE116 NVARCHAR(MAX) ,
VALUE117 NVARCHAR(MAX) ,
VALUE118 NVARCHAR(MAX) ,
VALUE119 NVARCHAR(MAX) ,
VALUE120 NVARCHAR(MAX) ,
VALUE121 NVARCHAR(MAX) ,
VALUE122 NVARCHAR(MAX) ,
VALUE123 NVARCHAR(MAX) ,
VALUE124 NVARCHAR(MAX) ,
VALUE125 NVARCHAR(MAX) ,
VALUE126 NVARCHAR(MAX) ,
VALUE127 NVARCHAR(MAX) ,
VALUE128 NVARCHAR(MAX) ,
VALUE129 NVARCHAR(MAX) ,
VALUE130 NVARCHAR(MAX) ,
VALUE131 NVARCHAR(MAX) ,
VALUE132 NVARCHAR(MAX) ,
VALUE133 NVARCHAR(MAX) ,
VALUE134 NVARCHAR(MAX) ,
VALUE135 NVARCHAR(MAX) ,
VALUE136 NVARCHAR(MAX) ,
VALUE137 NVARCHAR(MAX) ,
VALUE138 NVARCHAR(MAX) ,
VALUE139 NVARCHAR(MAX) ,
VALUE140 NVARCHAR(MAX) ,
VALUE141 NVARCHAR(MAX) ,
VALUE142 NVARCHAR(MAX) ,
VALUE143 NVARCHAR(MAX) ,
VALUE144 NVARCHAR(MAX) ,
VALUE145 NVARCHAR(MAX) ,
VALUE146 NVARCHAR(MAX) ,
VALUE147 NVARCHAR(MAX) ,
VALUE148 NVARCHAR(MAX) ,
VALUE149 NVARCHAR(MAX) ,
VALUE150 NVARCHAR(MAX) ,
VALUE151 NVARCHAR(MAX) ,
VALUE152 NVARCHAR(MAX) ,
VALUE153 NVARCHAR(MAX) ,
VALUE154 NVARCHAR(MAX) ,
VALUE155 NVARCHAR(MAX) ,
VALUE156 NVARCHAR(MAX) ,
VALUE157 NVARCHAR(MAX) ,
VALUE158 NVARCHAR(MAX) ,
VALUE159 NVARCHAR(MAX) ,
VALUE160 NVARCHAR(MAX) ,
VALUE161 NVARCHAR(MAX) ,
VALUE162 NVARCHAR(MAX) ,
VALUE163 NVARCHAR(MAX) ,
VALUE164 NVARCHAR(MAX) ,
VALUE165 NVARCHAR(MAX) ,
VALUE166 NVARCHAR(MAX) ,
VALUE167 NVARCHAR(MAX) ,
VALUE168 NVARCHAR(MAX) ,
VALUE169 NVARCHAR(MAX) ,
VALUE170 NVARCHAR(MAX) ,
VALUE171 NVARCHAR(MAX) ,
VALUE172 NVARCHAR(MAX) ,
VALUE173 NVARCHAR(MAX) ,
VALUE174 NVARCHAR(MAX) ,
VALUE175 NVARCHAR(MAX) ,
VALUE176 NVARCHAR(MAX) ,
VALUE177 NVARCHAR(MAX) ,
VALUE178 NVARCHAR(MAX) ,
VALUE179 NVARCHAR(MAX) ,
VALUE180 NVARCHAR(MAX) ,
VALUE181 NVARCHAR(MAX) ,
VALUE182 NVARCHAR(MAX) ,
VALUE183 NVARCHAR(MAX) ,
VALUE184 NVARCHAR(MAX) ,
VALUE185 NVARCHAR(MAX) ,
VALUE186 NVARCHAR(MAX) ,
VALUE187 NVARCHAR(MAX) ,
VALUE188 NVARCHAR(MAX) ,
VALUE189 NVARCHAR(MAX) ,
VALUE190 NVARCHAR(MAX) ,
VALUE191 NVARCHAR(MAX) ,
VALUE192 NVARCHAR(MAX) ,
VALUE193 NVARCHAR(MAX) ,
VALUE194 NVARCHAR(MAX) ,
VALUE195 NVARCHAR(MAX) ,
VALUE196 NVARCHAR(MAX) ,
VALUE197 NVARCHAR(MAX) ,
VALUE198 NVARCHAR(MAX) ,
VALUE199 NVARCHAR(MAX) ,
VALUE200 NVARCHAR(MAX) ,
VALUE201 NVARCHAR(MAX) ,
VALUE202 NVARCHAR(MAX) ,
VALUE203 NVARCHAR(MAX) ,
VALUE204 NVARCHAR(MAX) ,
VALUE205 NVARCHAR(MAX) ,
VALUE206 NVARCHAR(MAX) ,
VALUE207 NVARCHAR(MAX) ,
VALUE208 NVARCHAR(MAX) ,
VALUE209 NVARCHAR(MAX) ,
VALUE210 NVARCHAR(MAX) ,
VALUE211 NVARCHAR(MAX) ,
VALUE212 NVARCHAR(MAX) ,
VALUE213 NVARCHAR(MAX) ,
VALUE214 NVARCHAR(MAX) ,
VALUE215 NVARCHAR(MAX) ,
VALUE216 NVARCHAR(MAX) ,
VALUE217 NVARCHAR(MAX) ,
VALUE218 NVARCHAR(MAX) ,
VALUE219 NVARCHAR(MAX) ,
VALUE220 NVARCHAR(MAX) ,
VALUE221 NVARCHAR(MAX) ,
VALUE222 NVARCHAR(MAX) ,
VALUE223 NVARCHAR(MAX) ,
VALUE224 NVARCHAR(MAX) ,
VALUE225 NVARCHAR(MAX) ,
VALUE226 NVARCHAR(MAX) ,
VALUE227 NVARCHAR(MAX) ,
VALUE228 NVARCHAR(MAX) ,
VALUE229 NVARCHAR(MAX) ,
VALUE230 NVARCHAR(MAX) ,
VALUE231 NVARCHAR(MAX) ,
VALUE232 NVARCHAR(MAX) ,
VALUE233 NVARCHAR(MAX) ,
VALUE234 NVARCHAR(MAX) ,
VALUE235 NVARCHAR(MAX) ,
VALUE236 NVARCHAR(MAX) ,
VALUE237 NVARCHAR(MAX) ,
VALUE238 NVARCHAR(MAX) ,
VALUE239 NVARCHAR(MAX) ,
VALUE240 NVARCHAR(MAX) ,
VALUE241 NVARCHAR(MAX) ,
VALUE242 NVARCHAR(MAX) ,
VALUE243 NVARCHAR(MAX) ,
VALUE244 NVARCHAR(MAX) ,
VALUE245 NVARCHAR(MAX) ,
VALUE246 NVARCHAR(MAX) ,
VALUE247 NVARCHAR(MAX) ,
VALUE248 NVARCHAR(MAX) ,
VALUE249 NVARCHAR(MAX) ,
VALUE250 NVARCHAR(MAX) ,
VALUE251 NVARCHAR(MAX) ,
VALUE252 NVARCHAR(MAX) ,
VALUE253 NVARCHAR(MAX) ,
VALUE254 NVARCHAR(MAX) ,
VALUE255 NVARCHAR(MAX) ,
VALUE256 NVARCHAR(MAX) ,
VALUE257 NVARCHAR(MAX) ,
VALUE258 NVARCHAR(MAX) ,
VALUE259 NVARCHAR(MAX) ,
VALUE260 NVARCHAR(MAX) ,
VALUE261 NVARCHAR(MAX) ,
VALUE262 NVARCHAR(MAX) ,
VALUE263 NVARCHAR(MAX) ,
VALUE264 NVARCHAR(MAX) ,
VALUE265 NVARCHAR(MAX) ,
VALUE266 NVARCHAR(MAX) ,
VALUE267 NVARCHAR(MAX) ,
VALUE268 NVARCHAR(MAX) ,
VALUE269 NVARCHAR(MAX) ,
VALUE270 NVARCHAR(MAX) ,
VALUE271 NVARCHAR(MAX) ,
VALUE272 NVARCHAR(MAX) ,
VALUE273 NVARCHAR(MAX) ,
VALUE274 NVARCHAR(MAX) ,
VALUE275 NVARCHAR(MAX) ,
VALUE276 NVARCHAR(MAX) ,
VALUE277 NVARCHAR(MAX) ,
VALUE278 NVARCHAR(MAX) ,
VALUE279 NVARCHAR(MAX) ,
VALUE280 NVARCHAR(MAX) ,
VALUE281 NVARCHAR(MAX) ,
VALUE282 NVARCHAR(MAX) ,
VALUE283 NVARCHAR(MAX) ,
VALUE284 NVARCHAR(MAX) ,
VALUE285 NVARCHAR(MAX) ,
VALUE286 NVARCHAR(MAX) ,
VALUE287 NVARCHAR(MAX) ,
VALUE288 NVARCHAR(MAX) ,
VALUE289 NVARCHAR(MAX) ,
VALUE290 NVARCHAR(MAX) ,
VALUE291 NVARCHAR(MAX) ,
VALUE292 NVARCHAR(MAX) ,
VALUE293 NVARCHAR(MAX) ,
VALUE294 NVARCHAR(MAX) ,
VALUE295 NVARCHAR(MAX) ,
VALUE296 NVARCHAR(MAX) ,
VALUE297 NVARCHAR(MAX) ,
VALUE298 NVARCHAR(MAX) ,
VALUE299 NVARCHAR(MAX) ,
VALUE300 NVARCHAR(MAX) ,
VALUE301 NVARCHAR(MAX) ,
VALUE302 NVARCHAR(MAX) ,
VALUE303 NVARCHAR(MAX) ,
VALUE304 NVARCHAR(MAX) ,
VALUE305 NVARCHAR(MAX) ,
VALUE306 NVARCHAR(MAX) ,
VALUE307 NVARCHAR(MAX) ,
VALUE308 NVARCHAR(MAX) ,
VALUE309 NVARCHAR(MAX) ,
VALUE310 NVARCHAR(MAX) ,
VALUE311 NVARCHAR(MAX) ,
VALUE312 NVARCHAR(MAX) ,
VALUE313 NVARCHAR(MAX) ,
VALUE314 NVARCHAR(MAX) ,
VALUE315 NVARCHAR(MAX) ,
VALUE316 NVARCHAR(MAX) ,
VALUE317 NVARCHAR(MAX) ,
VALUE318 NVARCHAR(MAX) ,
VALUE319 NVARCHAR(MAX) ,
VALUE320 NVARCHAR(MAX) ,
VALUE321 NVARCHAR(MAX) ,
VALUE322 NVARCHAR(MAX) ,
VALUE323 NVARCHAR(MAX) ,
VALUE324 NVARCHAR(MAX) ,
VALUE325 NVARCHAR(MAX) ,
VALUE326 NVARCHAR(MAX) ,
VALUE327 NVARCHAR(MAX) ,
VALUE328 NVARCHAR(MAX) ,
VALUE329 NVARCHAR(MAX) ,
VALUE330 NVARCHAR(MAX) ,
VALUE331 NVARCHAR(MAX) ,
VALUE332 NVARCHAR(MAX) ,
VALUE333 NVARCHAR(MAX) ,
VALUE334 NVARCHAR(MAX) ,
VALUE335 NVARCHAR(MAX) ,
VALUE336 NVARCHAR(MAX) ,
VALUE337 NVARCHAR(MAX) ,
VALUE338 NVARCHAR(MAX) ,
VALUE339 NVARCHAR(MAX) ,
VALUE340 NVARCHAR(MAX) ,
VALUE341 NVARCHAR(MAX) ,
VALUE342 NVARCHAR(MAX) ,
VALUE343 NVARCHAR(MAX) ,
VALUE344 NVARCHAR(MAX) ,
VALUE345 NVARCHAR(MAX) ,
VALUE346 NVARCHAR(MAX) ,
VALUE347 NVARCHAR(MAX) ,
VALUE348 NVARCHAR(MAX) ,
VALUE349 NVARCHAR(MAX) ,
VALUE350 NVARCHAR(MAX) ,
VALUE351 NVARCHAR(MAX) ,
VALUE352 NVARCHAR(MAX) ,
VALUE353 NVARCHAR(MAX) ,
VALUE354 NVARCHAR(MAX) ,
VALUE355 NVARCHAR(MAX) ,
VALUE356 NVARCHAR(MAX) ,
VALUE357 NVARCHAR(MAX) ,
VALUE358 NVARCHAR(MAX) ,
VALUE359 NVARCHAR(MAX) ,
VALUE360 NVARCHAR(MAX) ,
VALUE361 NVARCHAR(MAX) ,
VALUE362 NVARCHAR(MAX) ,
VALUE363 NVARCHAR(MAX) ,
VALUE364 NVARCHAR(MAX) ,
VALUE365 NVARCHAR(MAX) ,
VALUE366 NVARCHAR(MAX) ,
VALUE367 NVARCHAR(MAX) ,
VALUE368 NVARCHAR(MAX) ,
VALUE369 NVARCHAR(MAX) ,
VALUE370 NVARCHAR(MAX) ,
VALUE371 NVARCHAR(MAX) ,
VALUE372 NVARCHAR(MAX) ,
VALUE373 NVARCHAR(MAX) ,
VALUE374 NVARCHAR(MAX) ,
VALUE375 NVARCHAR(MAX) ,
VALUE376 NVARCHAR(MAX) ,
VALUE377 NVARCHAR(MAX) ,
VALUE378 NVARCHAR(MAX) ,
VALUE379 NVARCHAR(MAX) ,
VALUE380 NVARCHAR(MAX) ,
VALUE381 NVARCHAR(MAX) ,
VALUE382 NVARCHAR(MAX) ,
VALUE383 NVARCHAR(MAX) ,
VALUE384 NVARCHAR(MAX) ,
VALUE385 NVARCHAR(MAX) ,
VALUE386 NVARCHAR(MAX) ,
VALUE387 NVARCHAR(MAX) ,
VALUE388 NVARCHAR(MAX) ,
VALUE389 NVARCHAR(MAX) ,
VALUE390 NVARCHAR(MAX) ,
VALUE391 NVARCHAR(MAX) ,
VALUE392 NVARCHAR(MAX) ,
VALUE393 NVARCHAR(MAX) ,
VALUE394 NVARCHAR(MAX) ,
VALUE395 NVARCHAR(MAX) ,
VALUE396 NVARCHAR(MAX) ,
VALUE397 NVARCHAR(MAX) ,
VALUE398 NVARCHAR(MAX) ,
VALUE399 NVARCHAR(MAX) ,
VALUE400 NVARCHAR(MAX) ,
VALUE401 NVARCHAR(MAX) ,
VALUE402 NVARCHAR(MAX) ,
VALUE403 NVARCHAR(MAX) ,
VALUE404 NVARCHAR(MAX) ,
VALUE405 NVARCHAR(MAX) ,
VALUE406 NVARCHAR(MAX) ,
VALUE407 NVARCHAR(MAX) ,
VALUE408 NVARCHAR(MAX) ,
VALUE409 NVARCHAR(MAX) ,
VALUE410 NVARCHAR(MAX) ,
VALUE411 NVARCHAR(MAX) ,
VALUE412 NVARCHAR(MAX) ,
VALUE413 NVARCHAR(MAX) ,
VALUE414 NVARCHAR(MAX) ,
VALUE415 NVARCHAR(MAX) ,
VALUE416 NVARCHAR(MAX) ,
VALUE417 NVARCHAR(MAX) ,
VALUE418 NVARCHAR(MAX) ,
VALUE419 NVARCHAR(MAX) ,
VALUE420 NVARCHAR(MAX) ,
VALUE421 NVARCHAR(MAX) ,
VALUE422 NVARCHAR(MAX) ,
VALUE423 NVARCHAR(MAX) ,
VALUE424 NVARCHAR(MAX) ,
VALUE425 NVARCHAR(MAX) ,
VALUE426 NVARCHAR(MAX) ,
VALUE427 NVARCHAR(MAX) ,
VALUE428 NVARCHAR(MAX) ,
VALUE429 NVARCHAR(MAX) ,
VALUE430 NVARCHAR(MAX) ,
VALUE431 NVARCHAR(MAX) ,
VALUE432 NVARCHAR(MAX) ,
VALUE433 NVARCHAR(MAX) ,
VALUE434 NVARCHAR(MAX) ,
VALUE435 NVARCHAR(MAX) ,
VALUE436 NVARCHAR(MAX) ,
VALUE437 NVARCHAR(MAX) ,
VALUE438 NVARCHAR(MAX) ,
VALUE439 NVARCHAR(MAX) ,
VALUE440 NVARCHAR(MAX) ,
VALUE441 NVARCHAR(MAX) ,
VALUE442 NVARCHAR(MAX) ,
VALUE443 NVARCHAR(MAX) ,
VALUE444 NVARCHAR(MAX) ,
VALUE445 NVARCHAR(MAX) ,
VALUE446 NVARCHAR(MAX) ,
VALUE447 NVARCHAR(MAX) ,
VALUE448 NVARCHAR(MAX) ,
VALUE449 NVARCHAR(MAX) ,
VALUE450 NVARCHAR(MAX) ,
VALUE451 NVARCHAR(MAX) ,
VALUE452 NVARCHAR(MAX) ,
VALUE453 NVARCHAR(MAX) ,
VALUE454 NVARCHAR(MAX) ,
VALUE455 NVARCHAR(MAX) ,
VALUE456 NVARCHAR(MAX) ,
VALUE457 NVARCHAR(MAX) ,
VALUE458 NVARCHAR(MAX) ,
VALUE459 NVARCHAR(MAX) ,
VALUE460 NVARCHAR(MAX) ,
VALUE461 NVARCHAR(MAX) ,
VALUE462 NVARCHAR(MAX) ,
VALUE463 NVARCHAR(MAX) ,
VALUE464 NVARCHAR(MAX) ,
VALUE465 NVARCHAR(MAX) ,
VALUE466 NVARCHAR(MAX) ,
VALUE467 NVARCHAR(MAX) ,
VALUE468 NVARCHAR(MAX) ,
VALUE469 NVARCHAR(MAX) ,
VALUE470 NVARCHAR(MAX) ,
VALUE471 NVARCHAR(MAX) ,
VALUE472 NVARCHAR(MAX) ,
VALUE473 NVARCHAR(MAX) ,
VALUE474 NVARCHAR(MAX) ,
VALUE475 NVARCHAR(MAX) ,
VALUE476 NVARCHAR(MAX) ,
VALUE477 NVARCHAR(MAX) ,
VALUE478 NVARCHAR(MAX) ,
VALUE479 NVARCHAR(MAX) ,
VALUE480 NVARCHAR(MAX) ,
VALUE481 NVARCHAR(MAX) ,
VALUE482 NVARCHAR(MAX) ,
VALUE483 NVARCHAR(MAX) ,
VALUE484 NVARCHAR(MAX) ,
VALUE485 NVARCHAR(MAX) ,
VALUE486 NVARCHAR(MAX) ,
VALUE487 NVARCHAR(MAX) ,
VALUE488 NVARCHAR(MAX) ,
VALUE489 NVARCHAR(MAX) ,
VALUE490 NVARCHAR(MAX) ,
VALUE491 NVARCHAR(MAX) ,
VALUE492 NVARCHAR(MAX) ,
VALUE493 NVARCHAR(MAX) ,
VALUE494 NVARCHAR(MAX) ,
VALUE495 NVARCHAR(MAX) ,
VALUE496 NVARCHAR(MAX) ,
VALUE497 NVARCHAR(MAX) ,
VALUE498 NVARCHAR(MAX) ,
VALUE499 NVARCHAR(MAX) ,
VALUE500 NVARCHAR(MAX)
)
INSERT INTO dbo.Temp
( id ,
NOs ,
VALUE1 ,
VALUE2 ,
VALUE3 ,
VALUE4 ,
VALUE5 ,
VALUE6 ,
VALUE7 ,
VALUE8 ,
VALUE9 ,
VALUE10 ,
VALUE11 ,
VALUE12 ,
VALUE13 ,
VALUE14 ,
VALUE15 ,
VALUE16 ,
VALUE17 ,
VALUE18 ,
VALUE19 ,
VALUE20 ,
VALUE21 ,
VALUE22 ,
VALUE23 ,
VALUE24 ,
VALUE25 ,
VALUE26 ,
VALUE27 ,
VALUE28 ,
VALUE29 ,
VALUE30 ,
VALUE31 ,
VALUE32 ,
VALUE33 ,
VALUE34 ,
VALUE35 ,
VALUE36 ,
VALUE37 ,
VALUE38 ,
VALUE39 ,
VALUE40 ,
VALUE41 ,
VALUE42 ,
VALUE43 ,
VALUE44 ,
VALUE45 ,
VALUE46 ,
VALUE47 ,
VALUE48 ,
VALUE49 ,
VALUE50 ,
VALUE51 ,
VALUE52 ,
VALUE53 ,
VALUE54 ,
VALUE55 ,
VALUE56 ,
VALUE57 ,
VALUE58 ,
VALUE59 ,
VALUE60 ,
VALUE61 ,
VALUE62 ,
VALUE63 ,
VALUE64 ,
VALUE65 ,
VALUE66 ,
VALUE67 ,
VALUE68 ,
VALUE69 ,
VALUE70 ,
VALUE71 ,
VALUE72 ,
VALUE73 ,
VALUE74 ,
VALUE75 ,
VALUE76 ,
VALUE77 ,
VALUE78 ,
VALUE79 ,
VALUE80 ,
VALUE81 ,
VALUE82 ,
VALUE83 ,
VALUE84 ,
VALUE85 ,
VALUE86 ,
VALUE87 ,
VALUE88 ,
VALUE89 ,
VALUE90 ,
VALUE91 ,
VALUE92 ,
VALUE93 ,
VALUE94 ,
VALUE95 ,
VALUE96 ,
VALUE97 ,
VALUE98 ,
VALUE99 ,
VALUE100 ,
VALUE101 ,
VALUE102 ,
VALUE103 ,
VALUE104 ,
VALUE105 ,
VALUE106 ,
VALUE107 ,
VALUE108 ,
VALUE109 ,
VALUE110 ,
VALUE111 ,
VALUE112 ,
VALUE113 ,
VALUE114 ,
VALUE115 ,
VALUE116 ,
VALUE117 ,
VALUE118 ,
VALUE119 ,
VALUE120 ,
VALUE121 ,
VALUE122 ,
VALUE123 ,
VALUE124 ,
VALUE125 ,
VALUE126 ,
VALUE127 ,
VALUE128 ,
VALUE129 ,
VALUE130 ,
VALUE131 ,
VALUE132 ,
VALUE133 ,
VALUE134 ,
VALUE135 ,
VALUE136 ,
VALUE137 ,
VALUE138 ,
VALUE139 ,
VALUE140 ,
VALUE141 ,
VALUE142 ,
VALUE143 ,
VALUE144 ,
VALUE145 ,
VALUE146 ,
VALUE147 ,
VALUE148 ,
VALUE149 ,
VALUE150 ,
VALUE151 ,
VALUE152 ,
VALUE153 ,
VALUE154 ,
VALUE155 ,
VALUE156 ,
VALUE157 ,
VALUE158 ,
VALUE159 ,
VALUE160 ,
VALUE161 ,
VALUE162 ,
VALUE163 ,
VALUE164 ,
VALUE165 ,
VALUE166 ,
VALUE167 ,
VALUE168 ,
VALUE169 ,
VALUE170 ,
VALUE171 ,
VALUE172 ,
VALUE173 ,
VALUE174 ,
VALUE175 ,
VALUE176 ,
VALUE177 ,
VALUE178 ,
VALUE179 ,
VALUE180 ,
VALUE181 ,
VALUE182 ,
VALUE183 ,
VALUE184 ,
VALUE185 ,
VALUE186 ,
VALUE187 ,
VALUE188 ,
VALUE189 ,
VALUE190 ,
VALUE191 ,
VALUE192 ,
VALUE193 ,
VALUE194 ,
VALUE195 ,
VALUE196 ,
VALUE197 ,
VALUE198 ,
VALUE199 ,
VALUE200 ,
VALUE201 ,
VALUE202 ,
VALUE203 ,
VALUE204 ,
VALUE205 ,
VALUE206 ,
VALUE207 ,
VALUE208 ,
VALUE209 ,
VALUE210 ,
VALUE211 ,
VALUE212 ,
VALUE213 ,
VALUE214 ,
VALUE215 ,
VALUE216 ,
VALUE217 ,
VALUE218 ,
VALUE219 ,
VALUE220 ,
VALUE221 ,
VALUE222 ,
VALUE223 ,
VALUE224 ,
VALUE225 ,
VALUE226 ,
VALUE227 ,
VALUE228 ,
VALUE229 ,
VALUE230 ,
VALUE231 ,
VALUE232 ,
VALUE233 ,
VALUE234 ,
VALUE235 ,
VALUE236 ,
VALUE237 ,
VALUE238 ,
VALUE239 ,
VALUE240 ,
VALUE241 ,
VALUE242 ,
VALUE243 ,
VALUE244 ,
VALUE245 ,
VALUE246 ,
VALUE247 ,
VALUE248 ,
VALUE249 ,
VALUE250 ,
VALUE251 ,
VALUE252 ,
VALUE253 ,
VALUE254 ,
VALUE255 ,
VALUE256 ,
VALUE257 ,
VALUE258 ,
VALUE259 ,
VALUE260 ,
VALUE261 ,
VALUE262 ,
VALUE263 ,
VALUE264 ,
VALUE265 ,
VALUE266 ,
VALUE267 ,
VALUE268 ,
VALUE269 ,
VALUE270 ,
VALUE271 ,
VALUE272 ,
VALUE273 ,
VALUE274 ,
VALUE275 ,
VALUE276 ,
VALUE277 ,
VALUE278 ,
VALUE279 ,
VALUE280 ,
VALUE281 ,
VALUE282 ,
VALUE283 ,
VALUE284 ,
VALUE285 ,
VALUE286 ,
VALUE287 ,
VALUE288 ,
VALUE289 ,
VALUE290 ,
VALUE291 ,
VALUE292 ,
VALUE293 ,
VALUE294 ,
VALUE295 ,
VALUE296 ,
VALUE297 ,
VALUE298 ,
VALUE299 ,
VALUE300 ,
VALUE301 ,
VALUE302 ,
VALUE303 ,
VALUE304 ,
VALUE305 ,
VALUE306 ,
VALUE307 ,
VALUE308 ,
VALUE309 ,
VALUE310 ,
VALUE311 ,
VALUE312 ,
VALUE313 ,
VALUE314 ,
VALUE315 ,
VALUE316 ,
VALUE317 ,
VALUE318 ,
VALUE319 ,
VALUE320 ,
VALUE321 ,
VALUE322 ,
VALUE323 ,
VALUE324 ,
VALUE325 ,
VALUE326 ,
VALUE327 ,
VALUE328 ,
VALUE329 ,
VALUE330 ,
VALUE331 ,
VALUE332 ,
VALUE333 ,
VALUE334 ,
VALUE335 ,
VALUE336 ,
VALUE337 ,
VALUE338 ,
VALUE339 ,
VALUE340 ,
VALUE341 ,
VALUE342 ,
VALUE343 ,
VALUE344 ,
VALUE345 ,
VALUE346 ,
VALUE347 ,
VALUE348 ,
VALUE349 ,
VALUE350 ,
VALUE351 ,
VALUE352 ,
VALUE353 ,
VALUE354 ,
VALUE355 ,
VALUE356 ,
VALUE357 ,
VALUE358 ,
VALUE359 ,
VALUE360 ,
VALUE361 ,
VALUE362 ,
VALUE363 ,
VALUE364 ,
VALUE365 ,
VALUE366 ,
VALUE367 ,
VALUE368 ,
VALUE369 ,
VALUE370 ,
VALUE371 ,
VALUE372 ,
VALUE373 ,
VALUE374 ,
VALUE375 ,
VALUE376 ,
VALUE377 ,
VALUE378 ,
VALUE379 ,
VALUE380 ,
VALUE381 ,
VALUE382 ,
VALUE383 ,
VALUE384 ,
VALUE385 ,
VALUE386 ,
VALUE387 ,
VALUE388 ,
VALUE389 ,
VALUE390 ,
VALUE391 ,
VALUE392 ,
VALUE393 ,
VALUE394 ,
VALUE395 ,
VALUE396 ,
VALUE397 ,
VALUE398 ,
VALUE399 ,
VALUE400 ,
VALUE401 ,
VALUE402 ,
VALUE403 ,
VALUE404 ,
VALUE405 ,
VALUE406 ,
VALUE407 ,
VALUE408 ,
VALUE409 ,
VALUE410 ,
VALUE411 ,
VALUE412 ,
VALUE413 ,
VALUE414 ,
VALUE415 ,
VALUE416 ,
VALUE417 ,
VALUE418 ,
VALUE419 ,
VALUE420 ,
VALUE421 ,
VALUE422 ,
VALUE423 ,
VALUE424 ,
VALUE425 ,
VALUE426 ,
VALUE427 ,
VALUE428 ,
VALUE429 ,
VALUE430 ,
VALUE431 ,
VALUE432 ,
VALUE433 ,
VALUE434 ,
VALUE435 ,
VALUE436 ,
VALUE437 ,
VALUE438 ,
VALUE439 ,
VALUE440 ,
VALUE441 ,
VALUE442 ,
VALUE443 ,
VALUE444 ,
VALUE445 ,
VALUE446 ,
VALUE447 ,
VALUE448 ,
VALUE449 ,
VALUE450 ,
VALUE451 ,
VALUE452 ,
VALUE453 ,
VALUE454 ,
VALUE455 ,
VALUE456 ,
VALUE457 ,
VALUE458 ,
VALUE459 ,
VALUE460 ,
VALUE461 ,
VALUE462 ,
VALUE463 ,
VALUE464 ,
VALUE465 ,
VALUE466 ,
VALUE467 ,
VALUE468 ,
VALUE469 ,
VALUE470 ,
VALUE471 ,
VALUE472 ,
VALUE473 ,
VALUE474 ,
VALUE475 ,
VALUE476 ,
VALUE477 ,
VALUE478 ,
VALUE479 ,
VALUE480 ,
VALUE481 ,
VALUE482 ,
VALUE483 ,
VALUE484 ,
VALUE485 ,
VALUE486 ,
VALUE487 ,
VALUE488 ,
VALUE489 ,
VALUE490 ,
VALUE491 ,
VALUE492 ,
VALUE493 ,
VALUE494 ,
VALUE495 ,
VALUE496 ,
VALUE497 ,
VALUE498 ,
VALUE499 ,
VALUE500
)
VALUES ( 1 ,
2 ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE' ,
N'VALUE VALUE VALUE VALUE VALUE'
)
February 27, 2013 at 12:10 am
Warning: The table "Temp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
Msg 511, Level 16, State 1, Line 507
Cannot create a row of size 13079 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
The warning and the error message is self explanatory
It says that though the table is created, if you try and insert a row that exceeds 8060 bytes of data in this table, the INSERT/UPDATE will fail
Your INSERT statement tries to insert data which requires 13079 bytes and hence results in an error
You can check the below mentioned link for Maximum Capacity Specifications for SQL Server
http://msdn.microsoft.com/en-us/library/ms143432%28v=sql.105%29.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 27, 2013 at 12:40 pm
Without knowing the specifications of this design, the moment I see columns numbered numerically, I cringe. Then I cry. This design pattern usually indicates a bad design.
I would posit there is a more optimum way to store the data so that you dont hit the limits, you can access the data without a ton of crazy code and you dont use NVARCHAR(MAX), VARCHAR(MAX) or any of the MAX variants, for obvious reasons.
If the original script was for NVARCHAR(255), why convert to NVARCHAR(MAX)? Is it "just in case"? If so, that is NOT enough reason to use VARCHAR(MAX).
October 30, 2014 at 6:38 am
>> SQL Server use blocks of data named page. Each page can store 8kb, and include header and metadata. Therefore, It allows only 8060 bytes of data max to be stored in a row. Hence your row size will alwayas be <= 8060. It will never cross this.
>> some column type can store the data outside the main row's page (like NVARCHAR(MAX))
* you can get more information where physically the data is stored, using: DBCC IND ('<Database name>', '<table name>', 1);
>> using external data like NVARCHAR(MAX) you still need to store the pointers, in the main page.
http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/
>> dont forget the overhead. Each row has headers.
>> compression is somthing that take part in this game. A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. For example, a table that has the columns c1 char(8000) and c2 char(53) cannot be compressed because of the additional compression overhead.
>> The record-size limit for tables that use sparse columns is 8,018 bytes! Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.
>> without any change in a regular situation, you can use about 307 more or less, NVARCHAR(MAX) columns in a table, without seen this alert:
Warning: The table "Temp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
with all this said, This is only an alert since the Server assume that if you have several dynamic suzes (like several NVARCHAR) then you might not use them all or fill them with long data (therefore you might not need any pointer, and the data will fit to the max row size). Trying to insert the data might raise an error if it is not fit to the max row size.
I hope this is useful Smile
Senior consultant and architect, data platform and application development, Microsoft MVP.
July 29, 2019 at 4:37 pm
I know it's a very old thread but I see this error on SQL Server 2017 too. Am surprised because I studied that this error belonged to SQL Server 2000 and since 2005, the SQL Server is sophisticated to handle using ROW_OVERFLOW_DATA internally.
Can someone please clarify me with below questions -
--In 'thoughts'...
Lonely Rogue
July 29, 2019 at 6:39 pm
If using nvarchar(max), in particular, you can simply force the large values off the page into LOB/overflow area.
After creating the table, but before loading it, run the following command:
EXEC sys.sp_tableoption 'dbo.temp', 'large value types out of row', 'ON'
You should then be able to fully load the table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 29, 2019 at 8:37 pm
This isn't going to work even with VARCHAR(MAX) because each VARCHAR(MAX) requires that a 24 byte pointer be stored in the Clustered Index or the Heap and 24*500 = 12,000 bytes at a minimum even if you force "out of row" for LOBs.
I STRONGLY recommend that you "right size" and "right type" all of the columns or you don't stand a chance of making this work.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 29, 2019 at 9:09 pm
Forced-off-row types require only a 16-byte pointer. The 2-byte variable-length overhead per pointer, though, causes even fewer than 500 columns to be possible [I thought SQL would be able to do away with the lengths with "out of row" pointers, being fixed length and data length in the LOB itself, but apparently not). Thus, you can have "only" 443 nvarchar(max) columns in a single table.
Of course it would be much better to restructure the table to just use one value per row.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 30, 2019 at 7:14 am
since 2005, the SQL Server is sophisticated to handle using ROW_OVERFLOW_DATA internally.
Can someone please clarify me with below questions -
- Is it not true? If not, what is the improvement in SQL 2005 that documentation speak.
- Is splitting the table, the only option to resolve this WRITE errors?
Please someone clarify about the improvement that SQL 2005 speak about. Is it that SQL 2000 didn't allow beyond 8060 at all, meaning the tables were needed to be normalised to accommodate larger widths but SQL 2005 allows it using SP_TABLEOPTION?
EDIT: SP_TABLEOPTION didn't work and the INSERT still gave same error.
--In 'thoughts'...
Lonely Rogue
July 30, 2019 at 4:56 pm
Again, no method will allow you to have more than 433 nvarchar(max) values in one row. Just use separate rows. The entire design needs reworked to make it reasonable and functional.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2019 at 2:44 pm
Forced-off-row types require only a 16-byte pointer. The 2-byte variable-length overhead per pointer, though, causes even fewer than 500 columns to be possible [I thought SQL would be able to do away with the lengths with "out of row" pointers, being fixed length and data length in the LOB itself, but apparently not). Thus, you can have "only" 443 nvarchar(max) columns in a single table.
Of course it would be much better to restructure the table to just use one value per row.
Ah, correct. I was in the middle of reading a snippet of Kalen Delaney's book on internals and didn't "scroll up" to read where she says it's 24 bytes only when you have a length filter on it to force anything over a certain length out of row. Normally, it will be 16 bytes.
However, I've just discovered through some testing that there's a hidden rather large cost in In-Row bytes of any MAX datatype that's a whole lot more than 16 or 24 bytes plus the 2 that most people consider (as you pointed out). I'm on my way to work so I have to wait until tonight to cleanup the test code for posting and post it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2019 at 3:17 pm
p.s. We both may be right. According to Paul Randal, "It Depends" and can be 16 or 24 depending on the datatype. Please see the following.
https://www.sqlservercentral.com/forums/topic/lob-pointers-am-i-stupid/#post-1354312
To add to that, I don't actually have to post the code that I was testing because there's a post right below Paul's that explains the hidden cost.
https://www.sqlservercentral.com/forums/topic/lob-pointers-am-i-stupid/#post-1354313
So I'm thinking that 433 columns is a wee bit optimistic.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2019 at 3:25 pm
I created a table with 433 columns: that's where I got the number from. I was hoping that 500 might be possible but the variable-length byte overhead still being required killed that idea.
The very link you cite confirms that it will be 16 bytes for forced-off-of-page values:
So when does it store a regular 16 byte LOB pointer? Only when you set the "large value types out of row" table setting to ON:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
At this point, no matter the size of the value it will be stored as a LOB value using a 16 byte LOB pointer.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 31, 2019 at 4:08 pm
I created a table with 433 columns: that's where I got the number from. I was hoping that 500 might be possible but the variable-length byte overhead still being required killed that idea.
The very link you cite confirms that it will be 16 bytes for forced-off-of-page values:
So when does it store a regular 16 byte LOB pointer? Only when you set the "large value types out of row" table setting to ON:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
At this point, no matter the size of the value it will be stored as a LOB value using a 16 byte LOB pointer.
Agreed if the "large value types out of row" table setting is ON. I didn't realize you were using the option. If it's not, then the pointer will be 24 bytes plus some extra overhead depending on the length of the data. Mark has a great explanation of the overhead if you don't force "out of row" at the following link (took a bit to find it because his link on the SSC post is broken).
http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2019 at 5:06 pm
Agreed. Be nice if MS added the option to the CREATE TABLE statement, so that SQL would "know" that the variable length bytes are not needed at all for the forced-off-row columns, something like:
CREATE TABLE ... ( ... ) WITH ( LARGE_VALUE_TYPES_OUT_OF_ROW = ON );
Then again, with properly normalized tables, I can't imagine ever needing more than 10 (if that many?) max-length columns in one row.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply