This week’s installment in the series on efficient data is all about data types. Be smart about choosing them; The right type for the right job will not only store your data in less physical space and enforce data integrity better, but smaller indexes and tables will also improve overall query performance.
Category: Series
Efficient data, part 4: Fill factor
A quick way to improve I/O performance is to set a good fill factor. This installment of the series on efficient data discusses fill factors and page splits, and how they affect physical I/O performance.
Efficient data, part 3: Compression
This is the third part in a series of articles on optimizing large volumes of data. This part is all about using compression, and since I wrote an article on that a few months ago, I’m not going to re-invent the wheel. So this article is a repost of my july article on compression.
Efficient data, part 2: Partitioning
This is the second part in a series on storing and modelling data efficiently. A great way to add performance to your data is to partition it. Like the name implies, partitioning splits a table or index into multiple partitions, so the data can be stored across multiple physical files and drives. Partitioning is a feature of SQL Server Enterprise Edition, but if you have one, you’re in luck!
Efficient data, part 1: Normalization
We’ve talked a lot about optimizing queries and query performance, but we haven’t really touched that much on the storage and data modelling aspects. In this series of post, I’ll run through some basic tips on how you can more efficiently model and store your data, which may come in particularly handy when you’re working with large databases and large transaction volumes, but a lot of it also makes good design sense in smaller databases.
In this first article, we’ll cover the normalized data model.
Moving objects between schemas
Basic model changes when you’ve built your solution can be tricky, because they can require redesigning or rebuilding an entire solution. Sometimes, though, the solution can be pretty easy. Like changing an object’s schema, a task that can be done using the ALTER SCHEMA statement.
Slowly changing dimensions (part 3)
In the third installment of the series on slowly changing dimensions, we’re going to tackle the question of how to manage accumulated fact aggregates in a solution that uses SCD 2 dimensions. While SCD 2 dimensions solve a lot of problems with slowly changing dimensions, accumulated values can still make a mess of the aggregate data.
Slowly changing dimensions (part 2)
In this second installment of the Slowly Changing Dimensions series (see part one here), we’ll take a look at how to practically create a slowly changing dimension table using T-SQL.
Slowly changing dimensions (part 1)
This is the first article in a series that will describe what slowly changing dimensions (SCD for short) are, how they work, and why you might need to take them into account in your database or datawarehouse solution.
A short introduction to application roles
Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.