Grant Fritchey (1/7/2008)

---

I did some tests. For good or for ill, I found that the XML & Table of Numbers worked better than anything else when dealing with large tables...

Grant Fritchey (1/7/2008)

---

You just have to keep the tests honest. I wasn't counting icons and going "Oooh, it's faster." In the simple tests, both processes had a table scan. The...

if mcfarland is using that field as legal notation, then parent-child columns don't really help if the hierarchy exceeds two levels. using the legal notation column as an alternate...

GSquared: Thanks for helping me out.

I think some people are missing the point. Assuming is indexed, cast( as anything) will mean that 's index will...

I think you're assuming that a simpler execution plan is faster. I just ran this on a load table which as 126K records. seq is a unique key.

select...

depends on your workload. have you used performance monitoring to determine which is the higher load, sql server or cold fusion?

it appears the key is implementating legal notation (1.2.1, 1.2.4, etc.). if so, two keys won't work.

mcfarland's original suggestion is best.

select ...

from Section as child join Section as parent

on parent.key...

how can this:

select * from monitor_hosts where @myList like ('%,'+cast(hostid as varchar(8)+'%')

be faster than this:

select * from monitor_hosts where hostid in (select val from dbo.fListToValues(@myList))

the first statement will always consider...

your example drops the first 5 characters of the string, not the last 5.

i don't think TSQL can easily find the last occurrence of a character. so...

if the NUMERIC_ROUNDABORT option is ON for the database/session, any operation that results in loss of precision will cause an overflow error. all of the following statements will fail...

assuming your "date timestamp column" is really a datetime column that you're using to track when something happened:

dateadd( millisecond, 4, dateTimestampCol )

note that datetimes are only accurate to 3.33...

if monitor_hosts is really small and you just want a quick and dirty solution.

declare @myList as varchar(22)

set @myList=','+'1,2,3,4,5,6,7,8,9'+',' -- enclose list in commas

select * from monitor_hosts where @myList like ('%,'+cast(hostid...

[yourtable] is

Category ct sales

ABC 1 250

ZYX ...

if you're willing to use temp tables, a union would make this very easy. assuming ClaimDate and PremiumPaidDate are smalldatetimes and ClaimAmount and PremiumAmount are both the same numeric...

thanks for the feedback kenneth. we've decided on using a calendar table and should have it implemented in our framework by week's end.

as for testing, i don't agree entirely...