Wednesday, February 27, 2013

[SQL Server 2008 issues] Index Selection in SQL Server

[SQL Server 2008 issues] Index Selection in SQL Server

Index Selection in SQL Server

Posted: 26 Feb 2013 04:22 PM PST

Hi, Consider the following example : I have a table Employees: [code="sql"]Create Table Employees ( EmployeeId BigInt Identity(1,1) , EmployeeName Varchar(30) NOT NULL )[/code]I have two indexes: [code="sql"]One Clustered and One NonClustered............both on the same column(EmployeeName). [/code]Now, if I run the following query: [code="sql"]Select EmployeeName From Employees [/code] When I see the Execution plan...I can see that the query used the NonClustered Index. What is the Selection criteria behind the selection of the NonClustered Index. There must be certain factors that SQL Server keeps in mind before selecting an Index. Could someone please explain the technicalities behind Index Selection.....or may be refer a link which would help me get to know this stuff.....Thank You very much.

unexplained lock escalation - please help!

Posted: 26 Feb 2013 01:04 AM PST

Hi,I have a stored procedure in my SQL 2008 database that contains only the following TSQL : WITH TableA_CTE AS ( SELECT TOP (100) ID FROM dbo.TableA WITH (NOLOCK) WHERE TableAStatusTypeID IN (1, 5, 9) AND DATEDIFF(second, LastUpdateTime, @curUtcDate) >= 300 ORDER BY LastUpdateTime ASC ) UPDATE TOP (100) TA WITH (ROWLOCK) SET ServerId = @serverId, TransactionId = @transactionId, LastUpdateTime = @curUtcDate, TableAStatusTypeID = CASE WHEN TableAStatusTypeID = 1 AND RetriesProcess >= @maxRetriesCreatePending THEN 4 WHEN TableAStatusTypeID = 5 AND RetriesProcess >= @maxRetriesActivatePending THEN 8 WHEN TableAStatusTypeID = 9 AND RetriesProcess >= @maxRetriesDisconnectPending THEN 12 ELSE TableAStatusTypeID + 1 END, RetriesProcess = CASE WHEN TableAStatusTypeID = 1 AND RetriesProcess >= @maxRetriesCreatePending THEN 0 WHEN TableAStatusTypeID = 5 AND RetriesProcess >= @maxRetriesActivatePending THEN 0 WHEN TableAStatusTypeID = 9 AND RetriesProcess >= @maxRetriesDisconnectPending THEN 0 ELSE RetriesProcess + 1 END FROM dbo.TableA TA INNER JOIN TableA_CTE CTE ON TA.ID = CTE.ID;now, when I run load on the DB and trace it for lock escalation, i can see lock escalation "LOCK_THRESHOLD" on the table (that comes from the above stored procedure). I running with the defaults of SQL installation and table escalation on "TableA" is allowed.Why do I have lock escalation although I :1) used CTE to fetch only 100 rows2) specified NOLOCK in the CTE select part3) specified ROWLOCK on the update statement 4) used inner join in order to update only 100 rows.PLEASE HELP!!! I want to avoid disallowing lock escalation on the table.Do you know what can be the reason that I run into lock escalation using that SP ?

How many Database can be created in a MSSQL 2008

Posted: 26 Feb 2013 02:39 PM PST

How many Database can be created in a MSSQL 2008 R2. Can any one give me the exact number with an explanation.Does the DB number will differ according to the server spec's and also the bit(32/64) number.Thanks in advance.

Getting minimum of top n rows without using subquery

Posted: 26 Feb 2013 04:18 PM PST

Hi,We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting minimum date from top hundred rows. Is there any other way to do same task in single query without using sub query in IN?Thank you

Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

Posted: 26 Feb 2013 03:47 PM PST

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-1i 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 507Cannot 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[code="sql"] 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' )[/code]

Declared Variable in WHERE clause weird behavior

Posted: 15 Feb 2013 07:34 AM PST

I'm seeing a huge performance difference between two queries that are almost identical. The only SQL difference is in the WHERE clause:Query1: QueryID=@QIDQuery2: QueryID=ISNULL(@QID,A.QueryID)Full Query 1 takes 100x + longer to execute. @QID is never null in this case b/c is set at the beginning. I would expect a declared variable (int) would be quicker than a function wrapping that declared variable. The actual execution plan says Query2 is more intensive, but actual time to process tells a much different story. Can anyone shed light on this?Full Query1:[code="sql"]DECLARE @days INT; SET @days=7; DECLARE @date1 DATETIME; SET @date1=getdate();DECLARE @date2 DATETIME; SET @date2=DATEADD(day,-@days,@date1); DECLARE @date3 DATETIME; SET @date3=DATEADD(day,-@days*2,@date1); DECLARE @QID INT; SET @QID = (SELECT id FROM Queries WHERE Query='abc'); SELECT COUNT(*) as Visits, Query, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date2) AS Pos, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date3 AND date<@date2) AS Pos2 FROM Visits A INNER JOIN Pages B ON A.PageID=B.id INNER JOIN Queries E ON E.id=A.QueryID WHERE date>=@date2 AND QueryID=@QID GROUP BY QueryID,Query,PageID; [/code]Full Query2:[code="sql"]DECLARE @days INT; SET @days=7; DECLARE @date1 DATETIME; SET @date1=getdate();DECLARE @date2 DATETIME; SET @date2=DATEADD(day,-@days,@date1); DECLARE @date3 DATETIME; SET @date3=DATEADD(day,-@days*2,@date1); DECLARE @QID INT; SET @QID = (SELECT id FROM Queries WHERE Query='abc'); SELECT COUNT(*) as Visits, Query, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date2) AS Pos, (SELECT AVG(CAST(Pos AS DECIMAL)) FROM GooglePos WHERE QueryID=A.QueryID AND PageID=A.PageID AND date>=@date3 AND date<@date2) AS Pos2 FROM Visits A INNER JOIN Pages B ON A.PageID=B.id INNER JOIN Queries E ON E.id=A.QueryIDWHERE date>=@date2 AND QueryID=ISNULL(@QID,A.QueryID) GROUP BY QueryID,Query,PageID; [/code]

fetch tables most often queried

Posted: 26 Feb 2013 12:24 PM PST

Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

Need help comparing two execution plans

Posted: 11 Feb 2013 08:16 AM PST

Hi all,We're testing an upgrade from SQL 2005 to SQL 2008 R2 in our data warehouse environment. I've run into an interesting situation with a change in behavior to one query, and I'm looking for advice on how to interpret this.Here's the scenario: this is an insert query which populates a fact table from a staging table, replacing natural keys with surrogate keys from a set of dimension tables along the way. The query is somewhat complex due to the number of joins, but the structure is pretty simple. The source staging table has 268,628,160 rows and is about 40 GB in size. The joins to the dimension table are all outer joins, so after the insert completes, the destination fact table also has 268,628,160 rows. The fact table is much wider than the source staging table, and is about 130 GB in size.The tables, indexes, data, and code are identical between the SQL 2005 and SQL 2008 systems. However, we're seeing a massive increase in tempdb utilization on the new SQL 2008 server. On the old SQL 2005 server, this insert statement doesn't consume more than about 50 GB space. (It may actually be less than that -- 50 GB is simply the size of the autogrowing tempdb database on that server.) On the SQL 2008 server, this insert statement consumes about 300 GB of temp space. That's the size of our tempdb drive, so you can guess how we found this problem. ;-)I've attached the two query plans. They're not identical (unsurprisingly), but they're pretty close in structure. The major changes seem to be simply the order in which SQL is choosing to apply the joins. However, there are two things that caught my eye.1) The SQL 2008 plan begins with the correct Estimated Number of Rows of 268 million, but partway through the plan, a hash match bumps up the estimate to 355 million, and that incorrect estimate remains through the final insert operator. The SQL 2005 plan has the correct estimate of 268 million from start to finish.2) Under SQL 2005, the Table Insert operation at the end of the plan is only 3% of the overall cost. In the SQL 2008 plan, that operation accounts for 85% of the cost.Nearly every table joined in this query is truncated and rebuilt as part of the ETL process, so I can confirm that the statistics are brand new on every table in both systems. DBCC SHOW_STATISTICS even shows very similar heuristics for the columns I've analyzed, which I'd expect, given that the data is the same.What might account for this massive difference in tempdb utilization? Could these execution plan differences be related, or are they red herrings? The hardware is completely different between the two environments, but the SQL 2008 server is superior in every way -- double the memory, double the processors, newer hardware, better SAN, etc.Any help or ideas will be much appreciated -- thanks in advance!

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005

Posted: 23 Jan 2013 06:14 AM PST

This lovely error happens frequently when I need to add articles to my transactional replication setup. After making the changes needed to the publication, I start the Snapshot Agent (needed to replicate the new articles/changes over) an error RANDOMLY occurs with the CLR – and as far as I know, the only way this will be fixed is to restart the MSSQL service on Production[quote]Message: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL Server to use CLR integration features.Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL Server to use CLR integration features. Command Text: if object_id('sys.sp_replcleanupccsprocs') < 0 exec sp_replcleanupccsprocs @publicationParameters: @publication = MyDB[/quote]Found an [url=http://support.microsoft.com/kb/2003681]article[/url] from MS that pertains specifically to the error (my situation is #3) and it basically says to reinstall the .NET Framework 2.0 - Just lovely...as this happens to frequently occur on our busiest, most critical production server :crazy:My question is: Does anyone know any way around this or am I SOL?[b]EDIT[/b]: Also wanted to ask if anyone knows of potential pitfalls/areas of concern with reinstalling this older .Net platform

Stored procedure runs faster than the code it contains...

Posted: 26 Feb 2013 06:38 AM PST

I have a stored procedure which runs in about 1 second. If I take the code out of the stored procedure and run it by itself it takes about 5 minutes. The execution plan for the stored procedure saves the paralellism to the very end and the plan for the code itself runs the paralellism at various points throughout the plan.Why would the plans be different, first off? Does SQL treat the stored procedure with different performance enhancements than it does regular sql?

INSERT SELECT with large XML Dataset - XML DTD stripped...

Posted: 26 Feb 2013 08:24 AM PST

Let me begin by saying I'm not very well versed in pulling XML files into SQL using a straight T-SQL solution so I have pieced together pieces of information from various sites to get this far. The document I am processing is ~2.6 million lines ( icecat free product feed for those interested ). Now, I am able to SELECT the data out of the XML file using the following process:[code="sql"]USE IceCatBizDECLARE @Data XML;CREATE TABLE #Xml ( Data XML );INSERT INTO #Xml ( Data ) SELECT CONVERT(XML,BulkColumn,2) FROM OPENROWSET(BULK N'\icecat\files.xml', SINGLE_BLOB) O;SELECT @Data = Data FROM #Xml ;DROP TABLE #Xml;SELECT filenode.value('@path', 'VARCHAR(300)') AS filepath, filenode.value('@Product_ID', 'INT') AS Product_ID, filenode.value('@Supplier_id', 'INT') AS Supplier_id, filenode.value('@Prod_ID', 'VARCHAR(50)') AS Prod_ID, filenode.value('@Quality', 'VARCHAR(50)') as Quality, filenode.value('@Catid', 'INT') as Catid, filenode.value('@On_Market', 'INT') as On_Market, filenode.value('@Model_Name', 'VARCHAR(255)') as Model_Name, filenode.value('@Product_View', 'INT') as Product_View, filenode.value('@HighPic', 'VARCHAR(400)') as HighPic, filenode.value('@HighPicSize', 'INT') as HighPicSize, filenode.value('@HighPicWidth', 'INT') as HighPicWidth, filenode.value('@HighPicHeight', 'INT') as HighPicHeight, filenode.value('@Updated', 'VARCHAR(50)') as Updated, filenode.value('@Date_Added', 'VARCHAR(50)') as Date_Added, filenode.value('EAN_UPCS[1]/EAN_UPC[1]/@Value', 'VARCHAR(50)') as EAN_UPC, filenode.value('Country_Markets[1]/Country_Market[1]/@Value', 'VARCHAR(10)') as Country_MarketFROM @Data.nodes('ICECAT-interface/files.index/file') files ( filenode );[/code]So that returns 296k rows back and the data looks spot on with what I expect. Now, I'm trying to take that data and throw and INSERT INTO with the select. The code is:[code="sql"]USE IceCatBizIF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'filesindex') BEGIN DROP Table filesindex END CREATE TABLE filesindex ( Product_ID INT NOT NULL Primary Key, filepath VARCHAR(300) NOT NULL, Quality VARCHAR(255) NOT NULL, Supplier_ID INT NOT NULL, Prod_ID VARCHAR(255) NOT NULL, CatID INT NOT NULL, On_Market BIT NOT NULL, Model_Name VARCHAR(500) NOT NULL, Product_View INT NOT NULL, HighPic VARCHAR(500) NULL, HighPicSize INT NULL, HighPicWidth INT NULL, HighPicHeight INT NULL, Date_Added VARCHAR(40) NOT NULL, Updated VARCHAR(40) NULL, EAN_UPC VARCHAR(50) NULL, Country_Market VARCHAR(4) NULL)CREATE INDEX Product_ID on filesindex (Product_ID)CREATE INDEX Supplier_ID ON filesindex (Supplier_ID)CREATE INDEX CatID ON filesindex (CatID)CREATE INDEX prodcat ON filesindex( Product_ID, CatID)DECLARE @Data XML;CREATE TABLE #Xml ( Data XML );INSERT INTO #Xml ( Data ) SELECT CONVERT(XML,BulkColumn,2) FROM OPENROWSET(BULK N'\icecat\files.xml', SINGLE_BLOB) O;SELECT @Data = Data FROM #Xml ;DROP TABLE #Xml;INSERT INTO filesindex (filepath, Product_ID, Supplier_id, Prod_Id, Quality, Catid, On_Market, Model_Name, Product_View, HighPic, HighPicSize, HighPicWidth, HighPicHeight, Date_added, Updated, EAN_UPC, Country_Market)SELECT filenode.value('@path', 'VARCHAR(300)') AS filepath, filenode.value('@Product_ID', 'INT') AS Product_ID, filenode.value('@Supplier_id', 'INT') AS Supplier_id, filenode.value('@Prod_ID', 'VARCHAR(50)') AS Prod_ID, filenode.value('@Quality', 'VARCHAR(50)') as Quality, filenode.value('@Catid', 'INT') as Catid, filenode.value('@On_Market', 'INT') as On_Market, filenode.value('@Model_Name', 'VARCHAR(255)') as Model_Name, filenode.value('@Product_View', 'INT') as Product_View, filenode.value('@HighPic', 'VARCHAR(400)') as HighPic, filenode.value('@HighPicSize', 'INT') as HighPicSize, filenode.value('@HighPicWidth', 'INT') as HighPicWidth, filenode.value('@HighPicHeight', 'INT') as HighPicHeight, filenode.value('@Updated', 'VARCHAR(50)') as Updated, filenode.value('@Date_Added', 'VARCHAR(50)') as Date_Added, filenode.value('EAN_UPCS[1]/EAN_UPC[1]/@Value', 'VARCHAR(50)') as EAN_UPC, filenode.value('Country_Markets[1]/Country_Market[1]/@Value', 'VARCHAR(10)') as Country_MarketFROM @Data.nodes('ICECAT-interface/files.index/file') files ( filenode );[/code]Now I have let this statement execute for several hours and it just keeps cranking away. I never see any files inserted into the filesindex table. When I finally stop the execution it returns: [quote]XML DTD has been stripped from one or more XML fragments. External subsets, if any, have been ignored.(1 row(s) affected)[/quote]I don't know if I'm using outdated techniques or just flat out doing this wrong but any assistance or insights you could provide would be greatly appreciated. As I stated before I'm not very spun up on importing XML so my knowledge of proper terminology is lacking.Before I go a quick sample of the XML data....[code="xml"]<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE ICECAT-interface SYSTEM "http://data.icecat.biz/dtd/files.index.dtd"><!-- source: Icecat.biz 2013 --><ICECAT-interface xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://data.icecat.biz/xsd/files.index.xsd"> <files.index Generated="20130219013338"> <file path="export/freexml.int/US/133.xml" Product_ID="133" Updated="20110801235612" Quality="ICECAT" Supplier_id="5" Prod_ID="CPD-E430" Catid="221" On_Market="1" Model_Name="Multiscan E430 19" Display" Product_View="8747" HighPic="http://images.icecat.biz/img/norm/high/133.jpg" HighPicSize="2014" HighPicWidth="116" HighPicHeight="120" Date_Added="20050627000000"> <EAN_UPCS> <EAN_UPC Value="4901780729524" /> </EAN_UPCS> <Country_Markets> <Country_Market Value="IT" /> </Country_Markets> </file> <file path="export/freexml.int/US/385.xml" Product_ID="385" Updated="20120621143343" Quality="ICECAT" Supplier_id="3" Prod_ID="06P5754" Catid="219" On_Market="1" Model_Name="Ultra 160 SCSI Hot-Swap HS SL HARD DRIVE" Product_View="16470" HighPic="" HighPicSize="0" HighPicWidth="0" HighPicHeight="0" Date_Added="20041201000000"> <EAN_UPCS> <EAN_UPC Value="5019170744060" /> <EAN_UPC Value="4968665597388" /> </EAN_UPCS> <Country_Markets> <Country_Market Value="UK" /> </Country_Markets> </file> <file path="export/freexml.int/US/824.xml" Product_ID="824" Updated="20120207154716" Quality="ICECAT" Supplier_id="2" Prod_ID="PA3156E-1PRP" Catid="152" On_Market="1" Model_Name="Port Replicator" Product_View="13645" HighPic="http://images.icecat.biz/img/norm/high/824-5078.jpg" HighPicSize="7980" HighPicWidth="200" HighPicHeight="200" Date_Added="20051104000000"> <Country_Markets> <Country_Market Value="UK" /> </Country_Markets> </file> <file path="export/freexml.int/US/830.xml" Product_ID="830" Updated="20101217151007" Quality="SUPPLIER" Supplier_id="2" Prod_ID="PA3000U-1BRS" Catid="106" On_Market="1" Model_Name="Battery Pack (Li-Ion Graphite, 6 cell, 3000mAh)" Product_View="13978" HighPic="http://images.icecat.biz/img/norm/high/830.jpg" HighPicSize="15177" HighPicWidth="140" HighPicHeight="140" Date_Added="20051104000000"> <EAN_UPCS> <EAN_UPC Value="4026203287532" /> <EAN_UPC Value="2000004672447" /> </EAN_UPCS> <Country_Markets> <Country_Market Value="UK" /> </Country_Markets> </file> <file path="export/freexml.int/US/837.xml" Product_ID="837" Updated="20111111181015" Quality="SUPPLIER" Supplier_id="2" Prod_ID="PA3005U" Catid="911" On_Market="1" Model_Name="128 MB Memory Expansion" Product_View="28483" HighPic="http://images.icecat.biz/img/norm/high/837-2012.jpg" HighPicSize="11908" HighPicWidth="140" HighPicHeight="140" Date_Added="20051104000000"> <EAN_UPCS> <EAN_UPC Value="3517986100794" /> <EAN_UPC Value="4026203111653" /> <EAN_UPC Value="4026203287662" /> <EAN_UPC Value="7612758146416" /> <EAN_UPC Value="5705965638798" /> </EAN_UPCS> <Country_Markets> <Country_Market Value="NL" /> <Country_Market Value="BE" /> <Country_Market Value="UK" /> </Country_Markets> </file> </files.index></ICECAT-interface>[/code]

index creation issue/question ??

Posted: 26 Feb 2013 06:30 AM PST

Hi i have recently been running the following against one of my tables within a job and it has worked fine for many a month.[b]CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_clinic ON inf.tbl_PS_18W_OP_and_IP_PW_Times(appClinic)[/b]recently though, i have experenced the following messgae and then the only way can get the job to complete is by commenting this particular index creation out via the old "--" method.[b]Warning! The maximum key length is 900 bytes. The index 'IX_tbl__PS_18W_OP_and_IP_PW_Times_clinic' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.[/b]It has serverly reduced performace by excluding this index and i was wonderingif anyone could advise.Many thanks in advance for advice given Ta

Question on how to dynamically change the 'FileConnection' property durning runtime in SSIS

Posted: 26 Feb 2013 03:46 AM PST

Hello,I have an SSIS package with a number of File Connection Managers. These connection managers execute SQL scripts using Execute SQL Task. The way the package currently works is, the user has to explicitly open the SSIS package and make the FileConnection change, save the package, then run it.I'm trying to figure out how to dynamically change this outside the package. I know I can use variables for this, but how can I pass a variable to the FileConnection property during run time? Thank you for your time.

How to create an alias for Excel users to connect to a specific server in our farm? That can be changed ondemand

Posted: 26 Feb 2013 07:35 AM PST

Hi all,I have a few SQL Server OLAP (Analysis services) cube servers here, and users typically connect directly to them for ad hoc analysis. Let's say I have 2 servers, cube1 and cube2.I used to create a DNS cname called "cube" and have it re route to cube1 or cube2 depending on the status of the server (cube2 being more up to date than cube1, e.g.). That strategy somehow stopped working completely after we bought new routers / switches. [b]Meaning I can have CNAME CUBE point to cube1.... people connect to CUBE in Excel. Then I switched CUBE to point to cube2. And I noticed existing users are STILL connecting to cube1, even though new connection / PING would actually point to cube2.[/b]I asked around the office network people, and they actually were surprided that it ever worked before... (the network people who helped me before is no longer with the company). They said Excel typically resolve the DNS to a specific IP, then stay connected to that IP. So new DNS CNAME changes after they are connected should NOT change their connection.But I need to be able to route our users to a specific server, even if they have already connected to a cube before. For example, everyone is connected to CUBE, and is pointed to CUBE1. I need to be able to change it to point to CUBE2, and they would automatically be routed to CUBE2.I am open to all solutions from network to sql server specific.Thanks in advance!!! Also, we have added a few new Named instance in the server farm. Is there any way I can set up an alias (not sql alias as that's local computer only), which have user point to that, and it would automatically be routed to a specific named instance?thanks

SQL 2008 ODBC Connection

Posted: 26 Feb 2013 05:34 AM PST

Hello,This is my first attempt at creating a ODBC connection from a workstation to a SQL 2008 server and I'm not having very good luck (SQL 2005 was not a problem.) I've been trolling the internet for some clues to what I might be missing and the few things that I did try were no good. I get what the error is telling me I just cant seem to find anything on the sever that is not enabled. - I checked TCP/IP- I added the user to SQL 2008 (Reader Only)Do I need a new Native client just for 2008?This was a full version from a vendor (Not MS) would there be other spots I might have to check that you normally wouldn't?This is the current error when I try to create an ODBC from my PC to the SQL 2008 server:Connection failed:SQLState:'01000"SQL Server Error: 10061[Microsoft][ODBC SQL server Driver][TCP/IP Sockets] ConnectionOpen(Connect()).Connection failed:SQLState:'08001'SQL Server Error: 17[Microsoft][ODBC SQL server Driver][TCP/IP Sockets] SQL Server does not exist or access denied.

User tables in the system DBs. Are they affected by SPs and CUs

Posted: 26 Feb 2013 05:33 AM PST

Hello all, I have a client with several instances of SQL Server from 2000, 2005, & 2008, at various builds. They unfortunately have several cases where they have created objects in the system DBs. I am only certain of the Master DB being used but it wouldn't surprise me if the other system DBs have user tables as well.Will updates, SP or CU's leave user objects alone in the system DBs?Thanks.Joe

Can't Run Job Activity Monitor from Remote SSMS

Posted: 26 Feb 2013 04:13 AM PST

When I connect via RDP to the machine hosting SQL Server 2008 R2 and run SSMS, I can run the Job Activity Monitor. When I run SSMS on a remote machine, it says that it is "Unable to execute requested command."I log into SSMS using the same account in both cases.What could be causing that?Thanks,Tom

Migrating from SQL 2008 to SQL 2008 R2

Posted: 26 Feb 2013 01:24 AM PST

Hi,We need to migrate our DB from using SQL 2008 to SQL 2008 R2.What do you suggest to check ?BR,Aviad

delete from multiple tables within a database

Posted: 26 Feb 2013 12:30 AM PST

Greetings -I have a database which contains over 70 tables, there are over 30 tables that I need to delete rows from them based on two parms I am passing to the stored procedure. Below is the stored procedure, but I know it got to be a better way to delete rows from all tables in the database than wtiting single delete statment for each table..... Thank you. ***************************************ALTER PROCEDURE [dbo].[DELETEINC_SP] @incId intASBEGIN SET NOCOUNT ON; BEGIN TRANSACTION DECLARE @injId int DECLARE @trtid int SELECT @injId = Inj_id FROM Inj_T WHERE Inc_ID = @incId If @@error <> 0 goto ERR_HANDLER SELECT @trtid = IT_ID FROM IT_T WHERE Inj_id = @injId DELETE FROM ITr_T WHERE ITr_ID = @trtid If @@error <> 0 goto ERR_HANDLER DELETE FROM INo_T WHERE Inj_id = @injId If @@error <> 0 goto ERR_HANDLER DELETE FROM IA_T WHERE Inj_id = @injId If @@error <> 0 goto ERR_HANDLER DELETE FROM IN_T WHERE Inj_id = @injId IF @@ERROR <> 0 goto ERR_HANDLER DELETE FROM IW_T WHERE Inc_ID = @incId If @@error <> 0 goto ERR_HANDLER DELETE FROM SUP_T WHERE Inc_ID = @incId If @@error <> 0 goto ERR_HANDLER DELETE FROM LOC_T WHERE Inc_ID = @incId If @@error <> 0 goto ERR_HANDLER DELETE FROM EQU_T WHERE Inc_ID = @incId If @@error <> 0 goto ERR_HANDLER COMMIT TRANSACTIONRETURN 0ERR_HANDLER:Select 'Unexpected error occurred!' ROLLBACK TRANSACTION RETURN 1END

Database to Database Connection - Unsecure?

Posted: 26 Feb 2013 12:22 AM PST

Hello, I work for a government agency and I'm trying to implement some automated data pull procedures and I was getting some push back regarding "database to database connections are not secure". When I first heard this I thought they were crazy but I wanted to get some feedback from the community as to how "secure" a linked server or SSIS package is... I did read that linked servers can be encrypted and use SSL and of course they would be using integrated security with only the permissions they need.So, I wanted to ask the community for those out there that work in government agencies... how do you handle data transfer between agencies? I have already been using linked servers for some of our trading partners but I have 1 last group that we have to manually download data files on a monthly basis and then manually import them into our database. I personally think that in today's age, we should not have to do any sort of manual data movement but I wanted to get some "offical" positions on securing db to db connections and what the "industry standard" was for doing this. The data that we are moving is not PII and it is not classified at all... its basically just FOUO data. Thanks in advance!
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog