Facebook Sign in | Join
Getting Started with Adobe After Effects - Part 6: Motion Blur

TSQL Challenge 50

rated by 0 users
This post has 26 Replies | 11 Followers

Top 150 Contributor
Male
Posts 14
Points 65

Are numeral constants and periods (.) allowed?

Is the != operator allowed?

 

  • Post Points: 20
Top 10 Contributor
Posts 831
Points 12,705

Ramesh,
Numerical constants including periods are allowed anywhere.
Anything that is not explicitly forbidden for the where clause can be used. Thus != is ok.

  • Post Points: 20
Top 150 Contributor
Male
Posts 14
Points 65

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.

 

  • Post Points: 20
Top 50 Contributor
Posts 83
Points 1,165

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

  • Post Points: 20
Top 50 Contributor
Male
Posts 68
Points 770

Can we use Row_number() function ?

  • Post Points: 20
Top 10 Contributor
Posts 831
Points 12,705

Leonid Koyfman:

Can we use Row_number() function ?

No.
I think that would make a little too easy.:-)

  • Post Points: 20
Top 150 Contributor
Male
Posts 14
Points 65

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.

 

 

  • Post Points: 5
Not Ranked
Posts 1
Points 20

What about other functions: substring, stuff, case, abs?? Are they prohibited?

  • Post Points: 20
Top 10 Contributor
Posts 831
Points 12,705

Szauri:

What about other functions: substring, stuff, case, abs?? Are they prohibited?

No functions are allowed except for the single aggregate function 'count' in the keywords list of rule 1.

  • Post Points: 5
Top 50 Contributor
Male
Posts 72
Points 550

Are function allowed?

  • Post Points: 20
Top 10 Contributor
Posts 831
Points 12,705

Parth, I repeat: NO functions are allowed except for the single aggregate function 'count' in the keywords list of rule 1.

  • Post Points: 20
Top 10 Contributor
Male
Posts 1,711
Points 22,489

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.

  • Post Points: 5
Page 2 of 2 (27 items) Prev12
| RSS
Contact US

Copyright © Rivera Informatic Private Ltd.