The last decade has seen a remarkable rise in specialized database systems. Systems for transaction processing, data warehousing, time series analysis, full-text search, data lakes, in-memory caching, document storage, queuing, graph processing, and geo-replicated operational workloads are now available to developers. A belief has taken hold that a single general-purpose database is not capable of running varied workloads at a reasonable cost with strong performance, at the level of scale and concurrency people demand today. There is value in specialization, but the complexity and cost of using multiple specialized systems in a single application environment is becoming apparent. This realization is driving developers and IT decision makers to seek databases capable of powering a broader set of use cases when looking to adopt a new database. Hybrid transaction and analytical (HTAP) databases have been developed to try to tame some of this chaos.

This paper describes novel aspects of the column store implemented in the MemSQL database engine and describes the design choices made to support real-time streaming workloads. Column stores have traditionally been restricted to data warehouse scenarios where low latency queries are a secondary goal, and where restricting data ingestion to be offline, batched, append-only, or some combination thereof is acceptable. In contrast, the MemSQL column store implementation treats low latency queries and ongoing writes as first class citizens, with a focus on avoiding interference between read, ingest, update, and storage optimization workloads through the use of fragmented snapshot transactions and optimistic storage reordering. This implementation broadens the range of serviceable column store workloads to include those with more stringent demands on query and data latency, such as those backing operational systems used by adtech, financial services, fraud detection and other real-time or data streaming applications.

The integration of the .NET Common Language Runtime (CLR) into the SQL Server DBMS enables rich business logic written in modern .NET languages to run close to the data. Database application developers can write business logic as functions, stored procedures, and triggers. They can also extend the native capabilities of the DBMS by adding new scalar data types, and aggregates. A previous paper [2] described the architecture and design principles of the integration of the CLR inside SQL Server. Here we present new aspects of this work. First, we describe the extensibility contracts for user-defined types and aggregates in detail. Second, we present the advances to the CLR integration in SQL Server 2008 which significantly enhances the breath of applications supported by SQL Server. In particular, we describe the support for large (greater than 8000 byte) user-defined types and aggregates, multiple-input user-defined aggregates, and order-aware table valued functions. Third, we show how we leveraged scalar type extensibility to provide a hierarchical identifier data type that enables encoding of keys describing hierarchies as well as built-in support for spatial applications. This support includes both flat- and round-earth spatial types, as well as a spatial index. Fourth, we present how we use Language Integrated Query (LINQ) enhancements in .NET languages to improve developer productivity when creating routines that require data access. Finally, we present preliminary performance results showing the efficiency of streaming TVFs and aggregates relative to equivalent native features.