Mathematics/Algorithms in SQL

Question

Post reply

Mathematics/Algorithms in SQL

karthik M

One Orange Chip

Points: 29139
More actions
November 29, 2016 at 3:36 am

#332275

All,
I am looking for some article which explain what are all the mathematics concepts and algorithms used in SQL.
karthik

Viewing 15 posts - 1 through 15 (of 15 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Such as?

Asking because I'm not sure I understand what you're after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Ar you asking about the underlying mathematics behind the relational model? If so, you could look for papers writen by Ted Codd in the early to mid 1970s. You may find that you have to pay to get copies of some of them.

Tom

Eric M Russell SSC Guru Points: 125519 More actions · Answer 3

For the concepts of relational algebra that underly SQL, here is an overview:

https://en.wikipedia.org/wiki/Relational_algebra

The mathematical operators and functions included in the T-SQL language are quite basic.

https://msdn.microsoft.com/en-us/library/ms187716.aspx

https://msdn.microsoft.com/en-us/library/ms177516.aspx

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

OR... are you looking for things like why you might want to use Integer math, how to do things with dates and times, how to compare two differently partitioned ROW_NUMBER() results to identify overlapping events, the math behind quickly building high performance nested sets instead of using loops or recursion, why automatic conversions to decimal datatypes can suck, etc, etc?

In other words, what the hell does "all" mean in your original post? 😉

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

karthik M - Tuesday, November 29, 2016 3:36 AM
All,I am looking for some article which explain what are all the mathematics concepts and algorithms used in SQL.

Dude! It's been over a year since you posted this request... can you at least take the time to read the responses and provide some feedback?

--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)

HappyGeek SSCoach Points: 18726 More actions · Answer 6

Jeff Moden - Monday, December 4, 2017 10:03 AM
karthik M - Tuesday, November 29, 2016 3:36 AM
All,I am looking for some article which explain what are all the mathematics concepts and algorithms used in SQL.
Dude! It's been over a year since you posted this request... can you at least take the time to read the responses and provide some feedback?

Looks like an interesting question, would like to see feedback on what the OP wanted.

...

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

HappyGeek - Monday, December 4, 2017 11:19 AM
Jeff Moden - Monday, December 4, 2017 10:03 AM
karthik M - Tuesday, November 29, 2016 3:36 AM
All,I am looking for some article which explain what are all the mathematics concepts and algorithms used in SQL.
Dude! It's been over a year since you posted this request... can you at least take the time to read the responses and provide some feedback?
Looks like an interesting question, would like to see feedback on what the OP wanted.

Precisely.

--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)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 8

I know that this is an old question bumped back up to the front of the line but I agree that it's an important one.

My two cents. With respect to the "mathematics concepts" I'd say to start with
Set Theory and The Relational Model.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

drew.allen SSC Guru Points: 76944 More actions · Answer 9

Jeff Moden - Monday, December 4, 2017 2:49 PM
HappyGeek - Monday, December 4, 2017 11:19 AM
Jeff Moden - Monday, December 4, 2017 10:03 AM
karthik M - Tuesday, November 29, 2016 3:36 AM
All,I am looking for some article which explain what are all the mathematics concepts and algorithms used in SQL.
Dude! It's been over a year since you posted this request... can you at least take the time to read the responses and provide some feedback?
Looks like an interesting question, would like to see feedback on what the OP wanted.
Precisely.

He hasn't been active in four months, so I wouldn't hold my breath waiting for a response.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

drew.allen - Monday, December 4, 2017 3:23 PM
Jeff Moden - Monday, December 4, 2017 2:49 PM
HappyGeek - Monday, December 4, 2017 11:19 AM
Jeff Moden - Monday, December 4, 2017 10:03 AM
karthik M - Tuesday, November 29, 2016 3:36 AM
All,I am looking for some article which explain what are all the mathematics concepts and algorithms used in SQL.
Dude! It's been over a year since you posted this request... can you at least take the time to read the responses and provide some feedback?
Looks like an interesting question, would like to see feedback on what the OP wanted.
Precisely.
He hasn't been active in four months, so I wouldn't hold my breath waiting for a response.
Drew

Heh... too bad... I was hoping to hold HIS breath. 😉 Pork chops, anyone? 😉

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

Alan.B - Monday, December 4, 2017 3:07 PM
I know that this is an old question bumped back up to the front of the line but I agree that it's an important one.
My two cents. With respect to the "mathematics concepts" I'd say to start with
Set Theory and The Relational Model.

Heh... we should definitely do one on "Tuples" as they relate to "Set Theory"... maybe Microsoft will take the hint and finally add an "element ordinal" to the String_Split function.

As for "Relational Model", I'm just happy there are some people out there that know how to get the current date and time using -T-SQL. 😉

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Jeff Moden - Monday, December 4, 2017 5:17 PM
Alan.B - Monday, December 4, 2017 3:07 PM
I know that this is an old question bumped back up to the front of the line but I agree that it's an important one.
My two cents. With respect to the "mathematics concepts" I'd say to start with
Set Theory and The Relational Model.
Heh... we should definitely do one on "Tuples" as they relate to "Set Theory"... maybe Microsoft will take the hint and finally add an "element ordinal" to the String_Split function.
As for "Relational Model", I'm just happy there are some people out there that know how to get the current date and time using -T-SQL. 😉

The "Relational Model" - which Airfix kit to give to which nephew at Christmas??

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

ChrisM@Work - Tuesday, December 5, 2017 12:49 AM
Jeff Moden - Monday, December 4, 2017 5:17 PM
Alan.B - Monday, December 4, 2017 3:07 PM
I know that this is an old question bumped back up to the front of the line but I agree that it's an important one.
My two cents. With respect to the "mathematics concepts" I'd say to start with
Set Theory and The Relational Model.
Heh... we should definitely do one on "Tuples" as they relate to "Set Theory"... maybe Microsoft will take the hint and finally add an "element ordinal" to the String_Split function.
As for "Relational Model", I'm just happy there are some people out there that know how to get the current date and time using -T-SQL. 😉
The "Relational Model" - which Airfix kit to give to which nephew at Christmas??

The answer is "none". Keep them for yourself... they're fun to build. 😉 If they know the GETDATE() question, buy them a laptop instead. 😉

--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)

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 14

Since we're already derailing...

How many people have seen the James May Toy Stories where they build Airfix?

If not, you should. It's fantastic. I think it's available on Hulu right now (or Netflix, but I'm pretty sure it's Hulu).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.