Missing numbers in a series

Question

Missing numbers in a series

Viewing 15 posts - 46 through 60 (of 61 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Jeff Moden (5/6/2010)
I'm not sure where you found the time to study that but, like Seth said, I learn something new every day. I didn't know why it worked well... I just knew it did. Thanks for the education, Paul.

I find the internals fascinating, so spend a lot of my free time with this sort of analysis. Most of the information is publicly available in one form or another - though I did have to confirm that SQL Server (almost always) uses a binary search algorithm to find index key values from the pointers in the page slot array, with Paul Randal (via Twitter as it happens ;-))

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Garadin One Orange Chip Points: 29613 More actions · Answer 2

To know that much about SQL Server... AND be able to write C# code. Way to make the rest of us look like schmucks ;-).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Paul White NZ (5/6/2010)
Jeff Moden (5/6/2010)
I'm not sure where you found the time to study that but, like Seth said, I learn something new every day. I didn't know why it worked well... I just knew it did. Thanks for the education, Paul.
I find the internals fascinating, so spend a lot of my free time with this sort of analysis. Most of the information is publicly available in one form or another - though I did have to confirm that SQL Server (almost always) uses a binary search algorithm to find index key values from the pointers in the page slot array, with Paul Randal (via Twitter as it happens ;-))

I'm impressed, very impressed.

Couldn't say it better than Seth did.

Thank you, Paul, this is an excellent piece of knowledge.

-- Gianluca Sartori

Paul White SSC Guru Points: 150467 More actions · Answer 4

Garadin (5/6/2010)
To know that much about SQL Server... AND be able to write C# code. Way to make the rest of us look like schmucks ;-).

Hey look so I've picked up a few things about a few things but:

a) I understand less than 1% of SQL Server; and

b) Any decent .NET developer will laugh at my C# efforts

Summary: I'm a schmuck too. 😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

j-1064772 SSCertifiable Points: 5316 More actions · Answer 5

Paul White NZ (5/5/2010)

Jeff Moden (5/5/2010)

Doesn't this remind you of the 'triangular join' I surprised you with a few weeks back? The one with the computed column CASE expression on the NULLs? I don't recall the exact details...

I missed that one. Could you please provide the URL ?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

j-1064772 (6/1/2010)

Paul White NZ (5/5/2010)

Jeff Moden (5/5/2010)
Doesn't this remind you of the 'triangular join' I surprised you with a few weeks back? The one with the computed column CASE expression on the NULLs? I don't recall the exact details...
I missed that one. Could you please provide the URL ?

URL to what? The Triangular Join article or ???

Keep in mind that whatever you surprised me with a few weeks back was a few hundred posts back for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

j-1064772 SSCertifiable Points: 5316 More actions · Answer 7

Jeff Moden (6/1/2010)

j-1064772 (6/1/2010)

Paul White NZ (5/5/2010)
Jeff Moden (5/5/2010)
Doesn't this remind you of the 'triangular join' I surprised you with a few weeks back? The one with the computed column CASE expression on the NULLs? I don't recall the exact details...
I missed that one. Could you please provide the URL ?
URL to what? The Triangular Join article or ???
Keep in mind that whatever you surprised me with a few weeks back was a few hundred posts back for me.

I meant the surprise about the trinagular join.

WayneS SSC Guru Points: 95460 More actions · Answer 8

Paul White NZ (5/7/2010)
Garadin (5/6/2010)
To know that much about SQL Server... AND be able to write C# code. Way to make the rest of us look like schmucks ;-).
Hey look so I've picked up a few things about a few things but:
a) I understand less than 1% of SQL Server; and
b) Any decent .NET developer will laugh at my C# efforts
Summary: I'm a schmuck too. 😀
Paul

Paul, I'd love to get elavated to your level of schmuck-iness!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 9

j-1064772 (6/1/2010)
I missed that one. Could you please provide the URL ?

Sure can:

http://www.sqlservercentral.com/Forums/Topic896583-338-1.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Paul White NZ (6/2/2010)
j-1064772 (6/1/2010)
I missed that one. Could you please provide the URL ?
Sure can:
http://www.sqlservercentral.com/Forums/Topic896583-338-1.aspx

Ohhh! That! I didn't realize he'd quoted you, Paul.

Yeah... that was pretty cool.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 11

Jeff Moden (6/2/2010)
Ohhh! That! I didn't realize he'd quoted you, Paul.

He made a bit of a mess of the quote tags :pinch: 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Eric M Russell SSC Guru Points: 125561 More actions · Answer 12

Paul White NZ (5/7/2010)
Garadin (5/6/2010)
To know that much about SQL Server... AND be able to write C# code. Way to make the rest of us look like schmucks ;-).
Hey look so I've picked up a few things about a few things but:
a) I understand less than 1% of SQL Server; and
b) Any decent .NET developer will laugh at my C# efforts
Summary: I'm a schmuck too. 😀
Paul

Real schmucks are the guys who think they know everything there is to know about SQL Server and blindly repeat the same bad patterns over and over again while informing everyone else that they're doing it wrong.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

wiltc-836148 Valued Member Points: 66 More actions · Answer 13

Jeff,

Your gap detection code is very sweet...

However, I'm wondering what you think of the use of a tally table vs your gap detection if the requirement is to find the first (or any) available number within a range?

Thanks!

Charles Wilt

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

wiltc-836148 (9/8/2011)
Jeff,
Your gap detection code is very sweet...
However, I'm wondering what you think of the use of a tally table vs your gap detection if the requirement is to find the first (or any) available number within a range?
Thanks!
Charles Wilt

Thanks for the feedback, Charles...

No... Gap detection isn't one of the jobs that the Tally Table is real performant at because it requires a JOIN to the data and then a comparison. It would be much quicker to just use a TOP 1 with the "Gap Start" part of the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wiltc-836148 Valued Member Points: 66 More actions · Answer 15

Jeff Moden (9/8/2011)
Thanks for the feedback, Charles...
No... Gap detection isn't one of the jobs that the Tally Table is real performant at because it requires a JOIN to the data and then a comparison. It would be much quicker to just use a TOP 1 with the "Gap Start" part of the code.

Jeff,

I'm actually still trying to wrap my head around the code...so I'm not sure I understand what you mean by using just the "Gap Start".

It seems to me, that the only thing I could take out is the line

GapEnd = hi.MyID - 1

which would have little effect on performance...

Am I missing something?

Thanks again!

Charles