Phone Test phase 1 - CLR version....

the SQL

use testing

go

dbcc freeproccache

dbcc dropcleanbuffers

--set things up

declare @StartTime datetime

IF OBJECT_ID('TempDB..#mattPhoneExtTest','U') IS NOT NULL

drop table #mattPhoneExtTest

PRINT REPLICATE('=',78)

PRINT SPACE(12)+'Matt''s CLR Solution for...

All right - here's the CLR solution for the Comedy-Separated Value field challenge.

First - the CLR function:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Runtime.InteropServices

Partial Public Class UserDefinedFunctions

...

Giants? Nah - I aint that tall....

More like - boys with their toys:)

I will tackle the split challenge tomorrow - I will not get a chance to set up both of the test scenarios etc.

Phone numbers challenge... This is 500,000 records,...

This part here isn't correct SQL - you have 2 FROM statements, and expressions in the FROM statements.

Nuts (11/13/2007)

---

Hi

Select

...

From c.dates,f.clinicalService,

(f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1))...

I just noticed - some part of your aggregate query is missing, because that syntax should NOT be getting past the syntax checker. There's no way that's running. ...

Jeff Moden (11/13/2007)

---

Like me! 😀

Heh... I don't need this thread to gut CTE's... most code examples that use...

what are the data types of cost, startdate and enddate in the patients table?

When doing count, either count the specific field (in this case, you want to count patients, so count the primary key of the patient), or count(*) (which will count each...

You're supposed to set that before the stored proc. The procedure will emulate whatever the ANSI_NULLS setting was at the time when it was created.

So, -

SET ANSI_NULLS ON

GO

create...

..only if your server has 1 processor, and 1GB...

Got it Adam. Must be it:).

By the way - if you were to rely on the execution plan - the CTE is "cheaper". So - processor time is...

I always used the

Delete tableA from

tableA inner join TableB on tablea.ida=tableb.idb

kind of syntax. Two FROM's? that's confusing.... Much more readable if you skip the first FROM,...

Jeff Moden (11/13/2007)

---

Sting manipulation? Complex mathematical calcs? Heh... Let's have a race... let's start with string manipulation... How about splitting a comma seperated column and returning a table...

Anything that DOES just gets added to your tombstone:D