SQL server t-sql 3 min.

// T SQL Tuesday 160

T SQL Tuesday #160

This months T-SQL Tuesday is number 160 and is hosted by Damien Jones (T, L, B) - you can read the original invitation here or by clicking the image below.

T-SQL Tuesday logo

The topic of this month is really interesting as we are to discus our personal wishlist for the partnership between Microsoft and OpenAI. As I’m a SQL and Kusto guuy to the bone, this will also be my stepstone in this blogpost.

SQL and OpenAI

When writing SQL code and building data models, selecting data, updating tables and all the other stuff we do in the T-SQL language. We sometimes miss the low hanging fruits for writing code and do optimization on the executed statements.

With the introduction of CoPilot in GitHub - the service that helps you to write code very fast - it would be a very nice feature to have the same thing for T-SQL code in both Azure Data Studio and SQL Server Management Studio.

It could be very interesting to see the OpenAI engine helping us to write effecient T-SQL code for our data models. There are some standard things that could be implemented easy - like:

  1. Data model analysis: Is my datamodel effecient enough, do I have some missing contraints, missing primay keys etc.
  2. Table analysis: Key columns, data types (ex. are string types to long)
  3. Database analysis: Am I using the correct collation accoring to the content of the database? Are the recommended trace flags for ex. ad-hoc queires enabled etc.
  4. Metadata on objects: OpenAI could scan the table content and write metadata content for each object in the database - more or less on the same level as we are seing in Power BI metrics dectiptions

…and much more

Next steps could be to help us optimize the database with missing indexes - yes I know that Microsoft is doing a lot here in Azure with the missing index option. Sometimes we would like to do this handheld with suggestions.

It could also be help to write stored procedures and to highlight ineffecient code in those - based on the CoPilot option from GitHub I guess it should be implementable.

Kusto / Data Explorer and OpenAI

This might be a bit more difficult to implement. The Kusto engine is still somewhat new (compared to the SQL engine). But with the power of AI it should be possible to find a business case.

From my standpoint - it could be fun to see OpenAI help on the KQL language. For now, we have the learn capabilities from Microsoft online and the explain function directly in Kusto. But with my experience with the explain function, it does not take into account what is a dimension and what is a fact - given that the most effecient way to build your KQL query, is to start with your dimenions and then join the facts.

OpenAI could help here with popups or directly nodging the developer to choose more wisely based on given best practises for KQL and the Data Explorer world found in both Azure Data Explorer and Synapse Data Explorer.


In short - I truly believe that OpenAI and the similar engines in the future, we are in for some great help and less bad coding with leveraging the power of AI in our daily work.