Creating Partitioned Views

Erik Darling describes partitioned views:

Hooray. Now you have to type less.

Partitioned views don’t need a scheme or a function, there’s no fancy syntax to swap data in or out, and there’s far less complexity in figuring out RIGHT vs LEFT boundaries, and leaving empty partitions, etc. and so forth. You’re welcome.

A lot gets made out of partition level statistics and maintenance being available to table partitioning. That stuff is pretty much automatic for partitioned views, because you have no choice. It’s separate tables all the way down.

Partitioned views, AKA SQL Server 2000 partitioning.  I think my favorite use case for them today is to serve as a combination of hot data in a memory-optimized table and cold data on disk.

Related Posts

Aim For Database-First Designs

Lukas Eder explains why database-first design patterns tend to work better than code-first design: The real “truth” of your database schema, and the “sovereignty” over it, resides with your database. The database is the only place where the schema is defined, and all clients have a copy of the database schema, not vice versa. The […]

Read More

A Non-Relational Database Taxonomy

Thomas Henson has a taxonomy of non-relational databases: Columnar Database The first type of NoSQL database is the Columnar databases which is optimized for reading and writing columns of data as opposed to rows of data. Column-oriented storage for database tables is an help drive down the input/output requirements for database. Since the I/O profile […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930