Are numeral constants and periods (.) allowed?
Is the != operator allowed?
Ramesh,Numerical constants including periods are allowed anywhere.Anything that is not explicitly forbidden for the where clause can be used. Thus != is ok.
dishdy: Ramesh,Numerical constants including periods are allowed anywhere.Anything that is not explicitly forbidden for the where clause can be used. Thus != is ok.
Thanks dishdy.
Stats for Challenge Data:
(8 row(s) affected)Table 'TC50'. Scan count 33, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tsqlc_Tally'. Scan count 6, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 9 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
----------------------------------------------------------------------------------------------------------------------------------
Stats for 65536 rows (Leszek Gniadkowski)
(32768 row(s) affected)Table 'Worktable'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TC50'. Scan count 12, logical reads 1120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tsqlc_Tally'. Scan count 6, logical reads 135, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 281 ms, elapsed time = 622 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.----------------------------------------------------------------------------------------------------------------------------------
Stats for 100,000 unique rows (MisterMagoo)
(50000 row(s) affected)Table 'TC50'. Scan count 7, logical reads 702, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 100000, logical reads 23188756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tsqlc_Tally'. Scan count 100000, logical reads 312694, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 789515 ms, elapsed time = 416806 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Ramesh Saive: Stats for 100,000 unique rows (MisterMagoo) (50000 row(s) affected)Table 'TC50'. Scan count 7, logical reads 702, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 100000, logical reads 23188756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tsqlc_Tally'. Scan count 100000, logical reads 312694, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 789515 ms, elapsed time = 416806 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Thanks for posting those stats - one thing though... the 100,000 unique values data is Dishdy's, not mine (minor point) .
edit: removed incorrect statement
Can we use Row_number() function ?
Leonid Koyfman:Can we use Row_number() function ?
MisterMagoo: Thanks for posting those stats - one thing though... the 100,000 unique values data is Dishdy's, not mine (minor point) . edit: removed incorrect statement
Thanks for the note.
What about other functions: substring, stuff, case, abs?? Are they prohibited?
Szauri:What about other functions: substring, stuff, case, abs?? Are they prohibited?
Are function allowed?
Parth, I repeat: NO functions are allowed except for the single aggregate function 'count' in the keywords list of rule 1.
Has anyone created some tricky data to run the logic testing?
Jacob Sebastian, SQL Server MVP
See this post that explains how to format TSQL code listing when posting in this forum.