Antonio:

I must be missing something. Per your own tests, the Numbers-based string parser was 2-3 times faster than the While loop parser every time. Yet you say its...

Antonio:

On maintaining a Numbers table wherever the code is going to be used, yes, it's work. But such tables have a lot of uses, so I do it. ...

Kenneth: Since I stole the function from http://www.simple-talk.com, with a couple of minor modifications, please, feel free to re-steal it from me. 🙂

Antonio: Yes, a straight up "in ()" list...

I got this one right, but I have to say, the QotD seems to be aimed at some sort of cute "tricky" question, instead of clear-cut tests of actual SQL...

Check out the TOP clause in Books Online. That might help.

Books Online has an article on creating roles on a server:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b0cd54ad-e81d-4d71-acec-8a6d7261ca08.htm

That should give you the data you need on how to do this. It's pretty simple.

(If you use domain...

Does "select distinct" solve what you're running into?

I just tested:

set statistics time on

select *

from dbo.names

inner join common.dbo.stringparser('140564,142245,139778,139779,149508,141315,141618,141175,140808,

141127,141128,141131,140822,141135,141140,140817,141163,141148,141609,140833,141152,140804,141115,

141113,141709,141706,141707,141277,141725,141728,141724,141286,141281,141720,141722,141673,141682,

141681,141679,141230,141677,141224,141675,141265,141261,141247,141693,141249,141630,141184,141187,

141636,141189,141200,141201,141191,141650,141649,141193,141802,143006,143754,141762,141340,148796,

148797,151588,139750,140565,148860,151568,139095,148856,139019,138996,139009,139313,138894,138893,

138980,138979,138886,148838,151600,148842,151643,148847,148848,148844,140150,140143,140145,140158,

139703,139704,139601,139724,139449,140167,140165,139718,140164,139720,140117,140334,139667,140341,

139895,139892,140135,139684,140124,139620,140097,140095,140096,139642,150912,140093,140100,140071,

140542,140850,140541,140548,140559,140557,140552,140553,140858,140554,140844,140874,139603,140881,

139608,139607,140880,139286,140501,140502,140049,140513,140246,140066,140255,139791,139796,139795,

140236,139792,139797,140232,140391,139941,140408,140405,139953,139952,140395,139957,139956,139955,

139950,140397,139948,140423,139971,140382,140375,140387,139972,139934,140384,139936,139920,139921,

140364,140425,140426,139303,140421,139633,140418,140010,140438,139982,140437,140467,139987,140448,

140439,140440,140442,140495,140032,140445,140446,139984,139990,140034,139988,139994,140452,140453,

139991,140000,140003,140487,140024,140005,140494,140475,140014,140305,151151,140357,138957,140329,

140350,140183,139739,140176,140182,140259,139727,139769,139768,140214,139765,139764,139733,140194,

140187,140185,139732,139816,140276,139836,140286,151632,151616,151617,151625,148835,140585,140584,

151633,141087,140761,140763,140752,140757,141084,141369,141368,141050,140718,141064,140736,140731,

141062,141061,141057,140654,140670,140681,140888,140580,140581,141007,141030,140699,140694,140695,

151613,143369,140638,140976,140645,140647,140983,140597,140907,140910,140905,140982,140649,140941,

140936,140621,140937,140618,140960,140634,140958,140959,140950,140948,140631,140952,140955,140626,

140964,140966,141356,140602,140916,140604,140603,140918,140926,140927,140606,140605,140925,140921,

140609,140933,140931,140615,151608,140588,151609,140593,151610,139096,139439,139438,139029,139064,

139073,140073,139413,139058,139387,139373,139378,139056,139371,139369,139370,139368,139341,139339,

139484,139116,139458,139104,141806,139196,139155,139156,139574,139578,139512,139518,139493,139494,

139497,149917,139157,139552,139249,139219,139217,139535,143368,152326,149999,149998,149994,149991,

151500,151516,148735,151509,151501,150546,152383,145780,142106,143362,150059,142907,150952,142101,

143134,143751,145674,150020,152350,141125,143338,145072,142884,150026,142892,142891,147786,147784,

151345,142271,143345,140181,140179,139737,140178,139736,139738,140180,151515,150083,142953,142952,

142949,151403,151409,151398,148639,148281,142941,142930,148653,148687,148675,148673,148671,151448,

148669,151449,148668,151439,148664,148656,148657,148655,148659,148660,151438,148661,148662,151435,

148693,151469,142937,143408,142916,152397,150069,150953,148595,142920,151370,148587,148588,151391,

151388,142980,152415,148745,141160,151685,150052,139974,146267,143473,142432,144485,144486,144487,

149005,149006,142108,142061,150186,152421,147705,147703,147701,151222,147700,151443,151433,151446,

151445,143456,151539,149430,148756,141608,152440,143463,143462,142908,142995,142994,143458,148725,

140826,146389,146385,148777,141499,146373,141915,146372,149559,141917,146357,149544,149542,149545,

149538,149539,146361,149547,149522,142820,143502,149645,149512,146318,151196,151199,151200,149578,

146467,149637,146468,149636,149639,146412,146410,149573,146411,152573,146329,149617,146452,149622,

146450,146453,140080,140079,151507,151508,146457,146434,150786,146415,149586,149585,146428,149570,

149596,146418,146417,151040,147474,151046,149660,151051,147478,147451,151330,151335,151334,151333,

147766,147434,147439,147442,147447,147763,147446,151015,151014,147445,151013,147494,141122,151027,

151028,151033,151023,146605,150188,147083,147084,147075,147078,147077,147081,147079,147088,146592,

150277,150273,146588,150159,150172,146601,150174,146595,141919,146586,150156,146581,146579,150149,

146568,146577,150342,147159,150142,147167,150329,146632,147019,147021,150196,146622,150201,146623,

147124,147112,147109,150286,147123,147129,150297,150299,147135,147132,150307,150309,150312,147041,

150237,147060,150257,147061,150258,147057,147055,150261,147026,150218,150216,150212,150211,150209,

150210,147024,150190,150224,147029,147036,147035,147427,151002,147241,150422,150409,147235,147238,

150376,150368,147195,147197,150390,150391,147201,147210,150383,150394,150393,150392,147229,150352,

150351,150350,147176,147182,147185,147475,147281,150447,150452,150456,147219,150439,150399,147225,

147410,147413,150433,150997,147419,147421,147256,147408,149693,149683,146496,149680,146497,146492,

146559,149710,146524,149713,146525,149711,149702,149690,149689,149715,150348,150130,146550,149738,

146546,147322,147595,147368,150945,150950,150948,150946,147371,147376,147308,150957,150954,150484,

150492,150486,147315,150488,150490,147302,150473,147528,147350,150921,147349,149406,147773,150938,

150507,147330,147329,147518,147520,147511,151084,151086,147337,147335,150513,150518,139782,147583,

151138,151136,147391,150976,150978,151105,147555,147553,147549,151108,151111,147552,147563,151114,

151125,147569,147568,151123,151098,151100,151099,147542,147546,145326,151261,151262,151271,151244,

147643,151185,147642,151186,147644,147660,151215,151213,147668,151237,147693,147678,147679,147687,

147690,151238,147675,151226,139388,149433,151202,147656,151193,140370,151277,151075,147722,151076,

151776,151778,151777,147734,151287,147740,151078,151079,151303,151301,147500,151077,139360,151279,

140535,139036,147490,151058,149912,151133,151064,147491,141494,147603,141812,141813,141362,143370,

143543,141492,143358,142905,143353,143340,143341,142883,142882,142885,143350,143348,142950,143418,

143416,142958,142957,143417,142954,142928,142929,142942,143381,143384,142917,143374,142919,143440,

143014,143451,148666,151434,148667,143471,143474,140317,143477,143461,142993,143466,141916,141923,

141466,141476,141925,141927,141938,141490,148932,141370,141374,141371,141887,141879,141891,141897,

141440,141888,141442,141869,141409,141414,141418,141419,141417,141858,141857,141421,141423,141865,

141875,141407,141825,141837,141390,141391,141834,141831,141835,141842,141843,141394,141845,141829,

141387,141821,141378,141377,141822,141906,141444,141447,141904,141450,141909,141908,141539,141994,

142028,141957,141956,142024,141978,141999,141980,141555,141531,141982,143485,141512,141510,141941,

141947,141486,141485,142582,141818,142573,142595,142510,142509,141816,142550,142549,142651,142227,

142242,142567,142220,142536,150202,142524,142724,142379,143161,142726,142513,142362,142701,142709,

142708,142294,142671,142681,142328,142331,142679,142674,142346,142689,142343', ',')

on names.nameid = stringparser.parsed

(Yes, that's 1,000 numbers in a parsed string).

Results:

SQL Server parse and compile time:

CPU...

Kenneth Fisher (1/7/2008)

---

Actually there is a flaw in your logic. I agree if I am compairing '1' to '2,3,12' I'm going to get a false hit. However I'm...

Two points:

1. Splitting a list in a While loop is more expensive than splitting one using a Numbers table. (See http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/ for a lot of good data on this...

The transaction rolled back by a proc is only the transaction from that proc, and any procs called by that proc that haven't already committed. Multiple procs run in...

When you set up the parameters in SSIS, you can provide their names. In that case, it works off of names, not sequence. You do that on the...

The order of your Where clause doesn't matter. It just needs to make sense to you. The database doesn't care.

On links for index use, try searching "ms sql...

If this isn't something that's going to be done repeatedly (it'll only be done once as part of a migration from SQL 2000 to SQL 2005), I'd do the whole...

"stauts" should be "status" (of course), but otherwise SQLBill's rewrite looks correct to me.