Writing SQL Server code with AI using GitHub Copilot

Question

Writing SQL Server code with AI using GitHub Copilot

Daniel Calbimonte

SSCertifiable

Points: 6200
More actions
February 12, 2024 at 12:00 am

#4362888

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

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

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

Glen Cooper SSCommitted Points: 1917 More actions · Answer 1

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.

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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

troy.smyrnios SSC Enthusiast Points: 108 More actions · Answer 3

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

thisisfutile Hall of Fame Points: 3668 More actions · Answer 4

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 9 months, 1 week ago by thisisfutile. Reason: Shortened the quote

thisisfutile Hall of Fame Points: 3668 More actions · Answer 5

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 9 months, 1 week ago by thisisfutile.
This reply was modified 9 months, 1 week ago by thisisfutile. Reason: Changed 'For loop' to 'While loop'...it's been 15 years since I've used one

troy.smyrnios SSC Enthusiast Points: 108 More actions · Answer 6

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 7

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. 😛