Currently, I’m working with a client to develop a custom database application that will be used for tracking and reporting on account receivables. We have estimated between 2-3 billion transactions per year and need to maintain 3 years of history…so it will be a fairly large database. But more interesting that the sheer size of this database (and some of the architectural decisions to support future growth via horizontal scaling), is the fact that we will be relying heavily on the use of Microsoft’s new ColumnStore indexing technology to support the query speeds needed for a fluid UX via the web interface.
If you’re not already familiar with ColumnStore indexes, here’s a brief introduction. And for a more in depth look, the presentation Eric Hansen delivered at TechEd North America 2012, SQL Server Columnstore Performance Tuning, is a must watch.
Initially, I was a bit skeptical of the performance improvements, but after a bit of reading and some proof-of-concept testing, I’m starting to feel a bit more confident. The final solution will require a very well thought out physical design (especially with the table partitioning strategy) and I’m looking forward to the challenge! So expect to see more ColumnStore-related posts in the future.