Just Five Mins!

Memory-Optimized Tables, also known as In-Memory OLTP, were introduced in SQL Server 2014. This feature was further enhanced in subsequent versions of SQL Server, including SQL Server 2016, 2017, and in Azure. However, in Azure, they're only available in the Premium tier.

Memory-Optimized Tables in SQL Server are a feature that provides significant performance improvements by storing table data in memory and optimizing data access. Here's an overview:

* What are Memory-Optimized Tables? Memory-Optimized Tables are tables that have their data completely held in memory, eliminating disk access except for durability purposes. This allows SQL Server to redesign how data access is handled, including locking and latching, resulting in substantially improved performance.

* How do they work? Memory-Optimized Tables store their data in memory using multiple versions of each row’s data. This technique, known as "Non-blocking multi-version optimistic concurrency control", eliminates both Locks and Latches, thus achieving breakthrough performance. All row versions are maintained in the same table data structure. Row versioning is used to allow concurrent reads and writes on the same row.

* Durability of Data: Memory-Optimized Tables are fully durable by default, meaning that like transactions on (traditional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). A second copy of the table data is maintained on disk, but only for durability purposes. Data in memory-optimized tables is only read from disk during database recovery (e.g., after a server restart).

* Non-Durable Tables: Besides the default durable memory-optimized tables, SQL Server also supports non-durable memory-optimized tables, which are not logged and their data is not persisted on disk. This means that transactions on these tables do not require any disk IO, but the data will not be recovered if there is a server crash or failover.

* Limitations: Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features. Starting with SQL Server 2016, and in Azure SQL Database, there are no limitations for collations or code pages that are specific to In-Memory OLTP.

I tried to do a “simple” overview of vector databases back in September 2023 (Episode 57 if you really want to know). I tried to get a basic overview back then and now, I figured, I would try to delve into this a little deeper.

Oh, that was such a wrong decision! (or was it?)

Vector databases, or at least vectors, are nothing that new but vector databases do seem to be quite a new thing although I suspect vector storage has been around for some time too of course.

Anyway, my brain is most definitely pickled and if you want your brain to be pickled too, then take a listen (at your own risk ;) ).

A few links from my ramblings:

What is a vector database (Amazon AWS)

About the k-NN algorithm - in this example, used within AWS OpenSearch

PostgreSQL vector database features (pgvector)

Microsoft: vector database explanation

Microsoft vector search

Back to school to learn some basic mathematics!

So, you don’t want to go down the whole ETL (Extract Transform Load) route in order to access external data from your Microsoft SQL server?

Okay, what about MS SQL Polybase?

Take a listen to learn more and to see how you could access external data using T-SQL without having to ETL the data into your environment - leaving the external data where it is.

For me personally, the CSV option via say Amazon S3 buckets, Azure Storage is an interesting option as well as being able to query SAP HANA data and possibly even joining to SQL database data ;)

Here are some links to delve deeper if you so wish:

What is Polybase / Data Virtualization

Virtualize a CSV file into SQL

Accessing generic ODBC sources

Accessing SAP HANA data via Polybase

We all love JSON data, don’t we?

Well, unless you are working with JSON directly from say C# or whatever flavour of language you use, working with JSON data can be a challenge sometimes. Sure, if you are in native JavaScript land or one of the many plethora of JS frameworks, then “JavaScript Object Notation” (aka JSON) isn’t much use!

jq is a really neat command line JSON processor. Written in portable C, it seems to be available anywhere… and I mean anywhere! even the old Solaris (Sun Microsystems).

There is a great tutorial too!

Thanks to my good friend Tim Abell for suggesting this one!

Maybe check out Tim’s podcast too!

Okay, so just to be clear, DAB (Data API Builder) doesn’t simply expose your database ;) better make that clear!

However, when you need to build the key components to work with a database i.e. CRUD (Create, Read, Update and Delete), then you can of course use whatever method you like. But what if an API can be delivered for you? better still you can have REST or GraphQL or both!

Oh, and yes, it works with MS-SQL databases, Azure SQL but also noSQL databases and MySQL/PostgresQL too! :)

Anyway, take a listen to learn more.

Links from the podcast:

Data API Builder on Github

More in-depth MS Learn docs

What is the most popular database engine around?

Well, a good contender is SQLite - it has been around for years and is in pretty much every device! yes, inside your Android and iPhone as well as many others including the web browser (I have only looked at using via WASM BTW!).

SQLite is a flat file based SQL database with some rather nifty features (including functions, CTE and JSON support via plugins). It has an amazing ecosystem and has been around for a long, long time.

SQLite cloud is taking the next step - SQLite, well, in the cloud! multi-region support, local/cloud sync and much more!

Take a listen to find out more.

Links below from the show:

SQLite official docs

SQLite CTE support

SQLite JSON support

SQLite Cloud!

Well, perhaps this should have been one for my other podcast (just experimenting with “What’s going on with…”) although, here it is trying to fit within just five minutes - not a chance!! :)

Okay, so a ramble it is! Covering some AI investment observations as well as AI hardware - the brutal world of trying something new and attempting to break ground into new markets, even if you don’t need them ;)

I reference a couple of links which are below:

The investment video I mentioned is here

The limitless AI site which includes the AI hardware pendant (where I would bet my AI hardware money) is here

Okay, if you managed to get through the whole podcast, I should honestly buy you a coffee or beer or whatever you care to enjoy!

I promise I will not make too many of these weekend rambles! back to normal JFM’s now :)

Oh, and if you want to understand/take a look at Nvidia GeForce Cloud Gaming, the link is here (I cannot believe I didn’t understand the whole proposition of this!)

I think I covered some basics about Docker dev containers a while ago when it was new’ish in beta.

Recently, I have been playing more and more with dev containers to save cluttering up my local machine and being able to be truly portable. I can take my dev container with me :) Windows, Mac, Linux - all works the same.

What is really fun is being able to say develop dotnet with SQL Server within a dev container, and have that same experience across any platform - including GitHub Codespaces - essentially developing fully in that infamous cloud! Might have to revisit a Codespace episode in the future!

But for now, enjoy, and here are some links to get you started if you wish to do so:

Docker Desktop - Windows, Mac, Linux, Palm Pilot

Dev Containers

As a bonus link, test containers! a clean test environment whenever you need it :)

Test Containers

GitHub Copilot is great, you do have to pay and it integrates nicely with VSCode and Visual Studio but what if there was a generic AI code assistant that is free to use and you can use it anywhere (not on the moon I am guessing)?

Codeium is the tool we are talking about.

It has a lovely free account that is pretty feature rich and you can use it all over the place! in your favourite IDE/text editor and even as a browser extension :)

Codeium also have trained their own models - no reliance on OpenAI or other API’s.

Well, it was Microsoft build conference recently and although there were some interesting announcements most were, of course, AI and Copilot related.

I have to say though, the new ARM based Windows “Copilot + PC’s” (my goodness, they need a better name!) do look rather interesting!

New Windows 11 File Explorer - 7-zip/TAR and built in GIT source control

Windows Copilot + PCs

Copilot runtime and library (local “SLM” small language models)

Until next time, take care!