Going Back to the Basics

  • In the past couple months, I have helped people understand a handful of topics associated with MS SQL Server:

    1) Why clustered indexes are good and additional functionality it offers (compressions, sorting, etc...)

    2) Benefits of an identity column

    3) Nuances with primary keys, natural keys, and composite keys

    4) Allocated space and utilized space

    This year, I had a wonderful opportunity come about where I was able to introduce people to the following system tables and give a brief review.

    1) sys.schemas

    2) sys.tables

    3) sys.indexes

    4) sys.partitions

    Oddly enough, I found myself giving advice on how to read Microsoft documentation on SQL Server.

    Because of this, the concept of "going back to basics" has been on my mind and I have found myself reviewing this material so I can speak to it with a sense of "relearned authority."

    Any thoughts on article concepts focusing on "going back to basics" and "relearning the essentials"?

  • You've done well (although I only see 4 replies of yours on this forum and with that I'll say "Welcome Aboard") and my hat is off to you for helping others and, YES,  all of those subjects are VERY worthwhile.

    Being a bit pedantic, though, none of the subjects that you've listed above are considered to be in the realm of "basics" or "essentials", IMHO.  For example, Items 1-3 of the first group listed above all straddle the initial line between beginner and intermediate levels and have follow-ons in the advanced and master levels. Item 4 in that same group is a pretty solid intermediate subject (IMHO and even if you're only talking about what a page and extent is) with some major follow-ons in the advanced and master levels.

    The second group is (again, IMHO) decidedly all high intermediate with substantial follow-ons in the advanced and master levels.

    And, therein, we find the problem that I have when most speak of "basics" and "essentials".  For example, in every language that I know of, except SQL, people first teach the "Hello World" example.  That simply means that they've got all of the necessary tools setup to begin to learn how to program.  After learning some basic syntax, the next they they learn how to do is count from 1 to 100 in a loop.

    Solving repetitive tasks is the main reason for computers and that involves loops.  No one ever starts off with that in SQL server and then teaches how a SELECT is really a loop (I call it a "Pseudo-Cursor", a phrase first coined by R. Barry Young on these very forums).   AND, you have to remember that most people only understand loops at the very beginning (even if they weren't programmers to start with but especially if they are).  If people were to first learn that a SELECT is actually a loop first instead of a lot of techo-jargon like "set-based", "relational division", etc, etc, people would do a hell of a lot better in advanced subjects.

    No one (actually, I did... I have two articles in a small series on the subject before I got interrupted) teaches one of the most essential skills of them all in "basic level" classes and that's how to build test data.  It goes hand-in-hand with the previous lesson and is, IMHO, the quintessential skill to all else.  Like I tell some people, you don't understand data, never mind being able to use it effectively, until you know how to make data in very large volumes.  And, if you don't know how to make large amounts of data, then you'll also never learn how to really get the maximum performance out of SQL regardless of supposed "advanced techniques" and "advanced functionality" that a lot of people have studied and still end up with performance and accuracy problems.

    I know this to be true because, I too, learned it the wrong way at the start, especially since I took a "professional certification" course on the subject of implementing SQL. 😀

    With all that being stated, what do you mean when you say "going back to basics" and "relearning the essentials"? 😀  Again, the subjects you listed are great subjects for articles but they are not "basics" nor "essentials", IMHO.

    Shifting gears a bit, I think a great beginner's course would be how to download and use Books Online and another would be how to use Google (or whatever search engine) to begin to find help, especially on "basic" and "essential" topics.  Both courses/articles should also explain how to learn from forums such as this and why it's important to "practice".

     

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Hey Jeff - thanks for the responding!

    A long time ago, I taught high school math.  Before my first teaching assignment, a professor suggested that I review a standard geometry textbook from a learner's perspective.  When I did this, I thought to myself, "Was this really written for a learner?"

    Interestingly enough, if I consider a scenario where I have to explain to someone what a square is, I would not point them to Wikipedia.  However, to be clear, I think Wikipedia has a great article on squares, but it was not written for an introductory learner: https://en.wikipedia.org/wiki/Square

    So when it comes to the SQL topics above, I found myself thinking about the subjects and reviewing a variety of materials out there.  I had a feeling more questions (from younger employees) would be coming and I thought it would be good for me to sharpen the axe per se.

    When doing so, I found great material out there, but I wondered if the people I helped would have understood the respective content.  I wondered if they would have the patience to work through some of the online articles.

    One idea came to mind.  In relation to the "Squares" article, have a tiered set of articles...

    • First article should only take 3-4 minutes to work through.  The context is more on a summary level, just enough for them to be able to test the idea and get the bigger point across.
    • Second article, same topic.  However, more mature context and the article takes 5-7 minutes to review.
    • Third article, same topic.  Now the context is very mature (like the Wiki article) and could push 7-10 minutes.

    Not really sure if that answers the two questions you have, but I do hope it helps.

    And yes!  Your idea on helping people research and practice are definitely spot-on!

    Thanks again for taking the time to respond!

  • Great example of why you wouldn't, at least initially, point someone new to geometry to the Wiki article you sighted to explain what a square is.

    Understood and appreciated on the order of revelation you speak of.  Sometimes that can all be accomplished in a single article, sometimes not.  It's like climbing a set of stairs, which is a segue to a suggestion...

    There's a menu item with the simple title of "Stairways" in the menu bar at the top of this very page.  Click on that and it'll take you to a list of "Stairways".  Pick one and see that they're kind of what you're suggesting.  It's almost like writing chapters in a book.  Contact Steve Jones (the Chief Editor of this site) if you're interested in doing something similar.  He's also the same fellow to contact if you're interested in writing standalone or much shorter series of articles.

    I'm actually a bit surprised he hasn't yet picked up on the conversation between us by now because he does (usually) monitor this particular forum.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • We'd love some articles like this, small, focused articles. We could make a Stairway if you have a theme here on what we're teaching someone. If it's better explanations of the docs, I might just make this a series, but helping someone to understand a simple concept is what we aim for.

  • And there the man is!  😀  Go for it, Scott!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • So I bean running with the advisements you both offered on better explaining the docs and getting access to Book Online.

    After reviewing the "Doc Navigation Tips" page (see link below), I realized this might be a great place to start!  There is good instruction on navigation basics, getting offline access, and such.  Personally, I have used the "breadcrumb" feature many times and never realized Microsoft had a name for it.

    A thought has come to mind and I wanted to run it by you...

    Article 1: Goes over the "Doc Navigation Tips" page.  Basically, go over the navigation tips and how to access the offline notes.

    Article 2: Take what was reviewed and apply it to the "SQL Server" page (link above).

    • Perhaps use this as an opportunity to target a specific page/topic and talk about a general layout scheme associated with most SQL-Server documentation pages.

      •   Reference / Transact-SQL (T-SQL) Reference / Numeric / int, bigint, smallint, & tinyint

    Any general thoughts, concerns, or advisements?

  • It would be handy if you posted the links you found as clickable links instead of a graphic. 😉

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Yeah...that would help.  Sorry about that!

    Navigation Guide Document: https://docs.microsoft.com/en-us/sql/sql-server/sql-docs-navigation-guide?view=sql-server-ver15

    SQL Server Guide Page: https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15

     

  • Man... I followed the first link and clicked through a couple of links to get to the following...

    https://docs.microsoft.com/en-us/sql/t-sql/tutorial-writing-transact-sql-statements?view=sql-server-ver15

    I have to say (again) that I absolutely hate Microsoft documentation.  They think that will suffice as a "beginner" article?  Lordy.

    Although I still don't care for it (lack of decent graphics), one of the better FREE tutorials (in a serious "Back to Basics" model) can be found at the following link.

    https://www.w3schools.com/sql/sql_intro.asp

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Many years ago, Fabian Pascal wrote a book entitled "SQL and Relational Basics", whose title explains its intent and contents. It was published in 1990, and very much shows its age. But it was one of the first books to discuss basic relational operations without reading like a math book. This is how we really should be doing a book on basics. It deals with the operations, a little bit about datatypes a little bit about data integrity and how the RDBMS mindset works.

    I've been teaching SQL for about 30 years now, and I find that people near first introduced to it want to write loops, if-then-else logic, and sequential processing. There is a Zen proverb "before you can drink it new tea, you must first empty the old tea from your cup." It says it all. I have a lecture I do on "our enemy, the punchcard," in which I discuss using a sequential file model in your mindset.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • @SSCrazy Eights - thanks for mentioning Fabian Pascal.  I started learning SQL in 2010.  The first book given to me was printed in the mid 1990s.  I cannot remember a thing about the book, but it got me started.

    @jeff - your mention of documentation has been on my mind.  Your "love" for Microsoft documentation is noted =D

    Your reference to W3 schools is spot-on.  For me, it is my go-to when having to refresh myself on HTML or CSS.  So yeah, I have a love for that site.

    I am taking what I am getting from this forum and trying to mesh out an article idea.  My problem is that I am over thinking it.  So yeah, perhaps I need to go back to basics...

    • Your mention of how to use Google is actually interesting.  Lately, I have been playing in the SAS world with a touch of Teradata.  Because of this, I have been going to the web and researching conversion functions involving date functions, chars to numeric, and such - very simple stuff, but product-specific lingo.  Telling this story from a SQL Server point-of-view would not be difficult.  Discussing how I articulated my question into a Google search is pretty simple.

    Any thoughts?

    Going to back to the "Square" article comparison, I am wondering if an article on something like conversions could start from "Googling the question" to "Now I am comfortable with going straight to Microsoft Docs"?  But that could be me over thinking it again.

     

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

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