Can you use a dummy date such as 1900-1-1 for the entries which don't have a date?

Why does your column need to be NOT NULLable?

just a thought...

If you are calling your stored procedure directly from SSMS then you could add some debug information into the procedure. Such as "select count(*) from inputtable".

If you are calling...

Jeff Moden (7/4/2012)

I suspect that the C# code runs very fast for this and that the reason why they came out so even is because they are so fast compared...

just tried another clr version which multiple functions which return different data types (as below). and used Jeff's test harness (attached).

there was no clear winner!

CREATE ASSEMBLY [IsGUID]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030046DDF34F0000000000000000E00002210B010800000C000000060000000000009E2A0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000482A000053000000004000004003000000000000000000000000000000000000006000000C000000D42900001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A40A000000200000000C000000020000000000000000000000000000200000602E72737263000000400300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000802A000000000000480000000200050014220000C0070000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200180100000100001100170A0F00FE16030000016F1100000A0B0772010000706F1200000A130511052D0C720100007007281300000A0B0772050000706F1400000A130511052D0C077205000070281300000A0B076F1500000A1F26FE01130511052D0900160A0038A700000000160C38900000000007086F1600000A0D08130611061F0E30131106162E2211061F092E2B11061F0E2E252B4111061F132E1D11061F182E1711061F252E202B2D091F7BFE01130511052D02160A2B43091F2DFE01130511052D02160A2B34091F7DFE01130511052D02160A2B2509281700000A2D12091F41320A091F46FE0216FE012B01162B0117130511052D02160A2B00000817580C081F26FE04130511053A62FFFFFF0006281800000A13042B0011042A13300200270000000200001100022801000006281900000A16FE010B072D0917281A00000A0A2B0916281A00000A0A2B00062A0013300200270000000300001100022801000006281900000A16FE010B072D0917281B00000A0A2B0916281B00000A0A2B00062A0013300200290000000400001100022801000006281900000A16FE010B072D0A176A281C00000A0A2B0A166A281C00000A0A2B00062A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000090020000237E0000FC0200005403000023537472696E677300000000500600000C000000235553005C0600001000000023475549440000006C0600005401000023426C6F620000000000000002000001471502000900000000FA01330016000001000000190000000200000004000000040000001C0000000E00000004000000010000000200000000000A00010000000000060033002C000A005B0046000A00660046000A00770046000A008C0046000A00A10046000600D500C3000600F200C30006000F01C30006002E01C30006004701C30006006001C30006007B01C30006009601C3000600CE01AF010600E201AF010600F001C30006000902C3000600390226024F004D02000006007C025C0206009C025C020A00DC02C1020600FA022C00060031032C0000000000010000000000010001008101100015000000050001000100502000000000960070000A0001007421000000009600800011000200A821000000009600950018000300DC21000000009600AA001F00040000000100B60000000100B60000000100B60000000100B6003900EC0026004100EC0026004900EC0026005100EC0026005900EC0026006100EC0026006900EC0026007100EC0026007900EC002B008100EC0026008900EC0026009100EC0026009900EC003000A900EC003600B100EC003B00B900EC003B000900F1024400C10001034800C1000C034D00C10013034800C1001C035300C10027035700C90036035C0011003F03610011004B03720021003F03780029003F03840031003F039000200083003F002E003300D3002E001300AD002E001B00AD002E002300B3002E002B00C9002E000B009C002E003B00AD002E004B00AD002E005300EB002E00630015012E006B0022012E0073002B012E007B00340167007E008A009600048000000100000000000000000000000000BA020000020000000000000000000000010023000000000002000000000000000000000001003A00000000000000003C4D6F64756C653E004973475549442E646C6C004775696446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E67004973477569640053716C496E74313600497347756964496E7431360053716C496E74333200497347756964496E7433320053716C496E74363400497347756964496E74363400706F737369626C65475549440053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500497347554944004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E67005374617274735769746800436F6E63617400456E647357697468006765745F4C656E677468006765745F436861727300436861720049734E756D626572006F705F496D706C69636974006F705F54727565000000037B0000037D000000000071D3EE146FA03F409E0E1359ECBB98600008B77A5C561934E0890600011109110D0600011111110D0600011115110D0600011119110D042001010E042001010205200101115104200101080320000104010000000320000E042001020E0500020E0E0E03200008042001030804000102030500011109020A0707020E08031109020805000102110905000111110605070211110205000111150805070211150205000111190A0507021119021001000B49734755494420546573740000050100000000150100104461766964204265747465726964676500000901000454657374000017010012436F7079726967687420C2A920203230313200002901002462633835333634662D373034322D343534302D396330372D61616632376330323264333600000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000046DDF34F000000000200000058000000F0290000F00B00005253445315F7FB80F10E0D4CA9E5A61F15E1F11203000000433A5C506572736F6E616C5C446176696420426574746572696467655C4973475549445C4973475549445C6F626A5C44656275675C4973475549442E70646200702A000000000000000000008E2A0000002000000000000000000000000000000000000000000000802A000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000044001100010043006F006D00700061006E0079004E0061006D0065000000000044006100760069006400200042006500740074006500720069006400670065000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E0000000000490073004700550049004400200054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D00650000004900730047005500490044002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003200000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004900730047005500490044002E0064006C006C00000000002C0005000100500072006F0064007500630074004E0061006D00650000000000540065007300740000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A03A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH...

How many rows are you testing with? I.e are in myguid? (I couldn't see it mentioned in the previous posts but apologises if I've missed it)

regards

david

CLR version below and attached ( i hope!)

CREATE ASSEMBLY [IsGUID]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002321F24F0000000000000000E00002210B010800000A000000060000000000000E290000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B428000057000000004000004003000000000000000000000000000000000000006000000C000000402800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000001409000000200000000A000000020000000000000000000000000000200000602E72737263000000400300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000F028000000000000480000000200050074210000CC060000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200180100000100001100170A0F00FE16030000016F1100000A0B0772010000706F1200000A130511052D0C720100007007281300000A0B0772050000706F1400000A130511052D0C077205000070281300000A0B076F1500000A1F26FE01130511052D0900160A0038A700000000160C38900000000007086F1600000A0D08130611061F0E30131106162E2211061F092E2B11061F0E2E252B4111061F132E1D11061F182E1711061F252E202B2D091F7BFE01130511052D02160A2B43091F2DFE01130511052D02160A2B34091F7DFE01130511052D02160A2B2509281700000A2D12091F41320A091F46FE0216FE012B01162B0117130511052D02160A2B00000817580C081F26FE04130511053A62FFFFFF0006281800000A13042B0011042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000024020000237E0000900200000C03000023537472696E6773000000009C0500000C00000023555300A8050000100000002347554944000000B80500001401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000016000000020000000100000001000000180000000E00000001000000010000000200000000000A00010000000000060033002C000A005B0046000A00660046000600960084000600B30084000600D00084000600EF00840006000801840006002101840006003C01840006005701840006008F0170010600A30170010600B10184000600CA0184000600FA01E70143000E02000006003D021D0206005D021D020A009D0282020600BB022C000600F2022C0000000000010000000000010001008101100015000000050001000100502000000000960070000A0001000000010077002100AD0011002900AD0011003100AD0011003900AD0011004100AD0011004900AD0011005100AD0011005900AD0011006100AD0016006900AD0011007100AD0011007900AD0011008100AD001B009100AD0021009900AD002600A100AD0026000900B2022F00A900C2023300A900CD023800A900D4023300A900DD023E00A900E8024200B100F7024700110000034C00200083002A002E00330094002E0013006E002E001B006E002E00230074002E002B008A002E000B005D002E003B006E002E004B006E002E005300AC002E006300D6002E006B00E3002E007300EC002E007B00F50052000480000001000000000000000000000000007B020000020000000000000000000000010023000000000002000000000000000000000001003A00000000000000003C4D6F64756C653E004973475549442E646C6C004775696446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670049734775696400706F737369626C65475549440053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500497347554944004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E67005374617274735769746800436F6E63617400456E647357697468006765745F4C656E677468006765745F436861727300436861720049734E756D626572006F705F496D706C696369740000037B0000037D0000000000752A02430862FA4783ABD7085DD16F3F0008B77A5C561934E0890600011109110D042001010E042001010205200101114504200101080320000104010000000320000E042001020E0500020E0E0E03200008042001030804000102030500011109020A0707020E0803110902081001000B49734755494420546573740000050100000000150100104461766964204265747465726964676500000901000454657374000017010012436F7079726967687420C2A920203230313200002901002462633835333634662D373034322D343534302D396330372D61616632376330323264333600000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000002321F24F0000000002000000580000005C2800005C0A00005253445315F7FB80F10E0D4CA9E5A61F15E1F11202000000433A5C506572736F6E616C5C446176696420426574746572696467655C4973475549445C4973475549445C6F626A5C44656275675C4973475549442E70646200DC2800000000000000000000FE280000002000000000000000000000000000000000000000000000F02800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E80200000000000000000000E80234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00448020000010053007400720069006E006700460069006C00650049006E0066006F00000024020000010030003000300030003000340062003000000044001100010043006F006D00700061006E0079004E0061006D0065000000000044006100760069006400200042006500740074006500720069006400670065000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E0000000000490073004700550049004400200054006500730074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D00650000004900730047005500490044002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003200000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004900730047005500490044002E0064006C006C00000000002C0005000100500072006F0064007500630074004E0061006D00650000000000540065007300740000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION IsGUID(@possibleGUID nvarchar(4000)) RETURNS bit

AS EXTERNAL NAME IsGUID.GuidFunctions.IsGuid;

GO

SELECT dbo.IsGUID('not a guid')

GO

SELECT dbo.IsGUID('{66D2701F-874C-4055-A28C-76D7E1FAAF69}');

GO

SELECT dbo.IsGUID('66D2701F-874C-4055-A28C-76D7E1FAAF69');

GO

good point! 🙂

Not sure it's any better but you could try a like statement.

if '72E28A38-8A55-4F54-AE61-F870BA58E9FD' like '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]-[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]' print 'match'

regards

David

i think you need to use a sum and group by

Simplified version below:-

select SUM(d.Amount),

H.InvoiceNumber

from InvoiceDetail d

join InvoiceHeader H on d.ID = h.ID

group by H.InvoiceNumber

regards

david

not sure if you are looking some generic or just something along the lines of

select *

from table

where ID = 100

and ( SysCC != 9...

I've made some progress - uninstalling and then installing service pack 1 against the instance appears to have fixed it.

(We've also removed the original update from our WSUS server.)

If anyone...

I park at the far side of the car park away from the river as it floods twice a year!

Are there any errors reported in the logs?

If the protocols are misconfigured then you might get some errors reported if you stop and restart the sql instance.

It's quite strange...

My only concern with using 3rd party monitoring tools is that they could suddenly present you with a load of measurements/alerts which you don't fully understand!

We are currently building up...

I doubt it's anywhere near as fast but...

;with Result(NKey, P1, IsP1Set, P1Idx, P2, IsP2Set, P2Idx, P3, IsP3Set, P3Idx, LastModified) as (

select NKey,

P1, IsP1Set, ROW_NUMBER() OVER (PARTITION...