Writing SQL Server code with AI using GitHub Copilot

  • Comments posted to this topic are about the item Writing SQL Server code with AI using GitHub Copilot

  • This is a really nice article, but it's a bit scary. Suppose AI generated a script but didn't "know" that a division by 0 could occur since no training example encountered it? Would always check the divisor in any division where 0 could possibly occur? That would be impressive.

  • A while ago when I looked into GitHub Copilot, the service was based on ChatGPT-3.5 which is/was quite inferior imo to the ChatGPT-4 which was available at the time.  I just checked again and apparently GitHub Copilot is now a multi-model combination of GPT's 3 and 4.

    https://github.com/orgs/community/discussions/56397

    Yes, both models are in play. Each model is being used where it is most effective. We'll have more nitty-gritty details in an upcoming blogpost, stay tuned.

    Want to know more?  Ha, "stay tuned" maybe they'll let you know in a few weeks, if ever

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Longtime SQL Server engineer, I've been experimenting with it quite a lot.  Initial findings:

    • Asking it to generate brand new code by instruction (like in this article)- varying success, usually not good.  Best results with boilerplate code.
    • Asking it to fix code - good at finding syntax issues, missing aggregates (e.g., column in group by but not select, or select but not group by), terrible at any logical issues
    • Auto-suggesting code (think intellisense)- absolutely amazing.  Examples:

      • With a Temp table definition further up in script, it has the knowledge of it, so as you start to write code around that temp table, it starts including for you all the columns inside (e.g., on selects, inserts, etc.).  Further, if you've aggregated on any of it, it retains a knowledge of what is the operative composite key for joins
      • Automatic join criteria based on common columns (and including your aliases)
      • Automatic group by based on what you didn't aggregate
      • Automatic writing of coalesce statements for a full join
      • Automatic suggestion of the next column in sequence as you are writing what to do with each column
      • Automatic suggestion of columns you may have missed
      • Automatic writing of EXISTS checks (this one was the biggest jaw dropper - it writes the entire exists subquery!)

    • Creation of DML has some very nice cases - the following common cases work like magic (or at least better than you'd guess)

      • Turning a tab-delimited table into INSERT statements (while connected to a database, initiate chat, paste in a tab-delimited table into chat, and ask to turn it into INSERT statements for X table - will understand schema)
      • Generation of mock data for a table

  • Steve Collins wrote:

    ...We'll have more nitty-gritty details in an upcoming blogpost, stay tuned.

    Ha, "stay tuned" maybe they'll let you know in a few weeks, if ever

    Almost reads like a teenager blog where they posted something like, "Sorry I've been studying a lot, but I plan to really get back into my blog!  Stay tuned!!!1!"  That of course was the last post, 2.5 years ago.

    • This reply was modified 4 months ago by  thisisfutile. Reason: Shortened the quote
  • troy.smyrnios wrote:

    Longtime SQL Server engineer, I've been experimenting with it quite a lot.

    I don't have the time or patience to mess with it at this stage of my career, but I'm genuinely curious; does AI actually know the difference between set theory and object-oriented programming?  The biggest education I received in SQL was 15 years ago, right here in these forums with Jeff Moden's superb article about Tally tables.  Many a SQL novice guru has entered this realm thinking like an OO programmer and absolutely destroyed production code with While loops and function calls.  If AI uses either of these in its code, then it will need educated on Jeff's article.  However, if it explains the need to add a Tally table (or an on-the-fly CTE), then my hats off to it...but I doubt that's the case.  I really am genuinely curious here; does it understand data sets?

    • This reply was modified 4 months ago by  thisisfutile.
    • This reply was modified 4 months ago by  thisisfutile. Reason: Changed 'For loop' to 'While loop'...it's been 15 years since I've used one
  • thisisfutile - Don't expect it to write the best code or know the optimal solution to a broader problem.

    Think of it this way.

    You are (still) the brain who must already plan and be thinking of best approach to solve a problem, know the first words of what will be the SQL (to get it started), and know when you see it what is appropriate code or no. (in this respect, the copilot is far less powerful in the hands of a SQL novice).

    This is just your instant boilerplate code writer companion.

    The sheer amount of SQL you would ordinarily write line-by-line, but can now just lead off with a few words (and the rest of the block is obvious), is a bit shocking.  I thought it would maybe shave a little bit of time, but it ended up turning the first non-exploratory case from an 8-hour task to 2 hours.  I guess I never realized just how much of SQL writing is so repetitive.

  • troy.smyrnios wrote:

    The sheer amount of SQL you would ordinarily write line-by-line, but can now just lead off with a few words (and the rest of the block is obvious), is a bit shocking.

    Fascinating!  Thanks for the feedback.  I just see the novice taking anything they throw at it and putting the results into production code.  I'm very curious what portion of that will be OO vs set logic.  I really do need to mess with it, but you know what they say about old dogs...that's right...we lay around and fart!  No, wait, there's another more appropriate saying...I guess I should ask AI.  😛

Viewing 8 posts - 1 through 7 (of 7 total)

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