davidc-702140 (2/4/2011)

---

Don't know if this helps - I use this to search for references in stored procs:

SELECT Distinct SO.Name

FROM sysobjects SO (NOLOCK)

INNER JOIN syscomments...

Leo, are you sure the problem is solved?

150,016,000 kb = 146500 mb = 143.0664 gb

(Dividing by 1024 each time.)

Thanks, Paul, Jonathan.

I think I will go down the Unknown / Not applicable route. i have on a couple of occasions in the past had a nullable field involved...

Thanks, captain. Exactly the link I was looking for.

I'm not thrilled by this behaviour but at least it's documented and 'by design'.

Jeff Moden (1/23/2011)

---

You bet. Thanks for the feedback, the tips, and a couple of other things.

As a side bar, recursive CTE's tend to be a bit slow and resource...

Quite...

Jeff, the question marks in your results come from the fact that you're using a varchar in your convert. Make it nvarchar and you should be alright.

CONVERT(nVARCHAR(30),GETDATE(....

Well I didn't use your speciality, Jeff, the tally table...instead I used my speciality the CTE 😎

I was mucking about with recursive CTEs (actually I rather like them at the...

Jeff,

I'm waiting for you to blow us away with your tally table solution to pull the correct month from the comma separated field in sys.syslanguages.

Or will I have to...

Thanks Phil.

Putting the language in quotes will solve the Portuguese Brazil problem

Set language ['+name+']....

I did a little more hunting and I came across sys.syslanguages which looks quite promising.

To continue with the French exception, it has fields months and shortmonths with the comma separated...

Très bien, Jeff! Except of course that you can't keep everybody happy!

In France, June and July are Juin and Juillet respectively, and hence JUI and JUI when truncated to...

Was I alone in getting excited about 'filtered indexes'? (Is that what they're called? An index with a where clause)

The reason I got into a state is that it...

ok...so here are my results in csv format (sorry!) Conclusions follow.

Full Resultset,Method,Execution Time,Client Processing Time,Total execution Time,Wait Time,Notes

,CTE View,13 secs,3261,5759,2498,93% in Hash Match

,Temp Table with rownumber,33 secs,21524,25422,3898,Index Scans

,Table Variable with...

Excellent...thanks, Jeff, for getting the ball rolling.

I adapted your code to make it as per the example i.e. so that the article code would run without modif.

Specifically, I created the...