Slowly Changing Dimension Implementations - Asynchronous Methods
Produced: 30/12/2018 21:42:00
Finally I think I’m done with the topic of Slowly Changing Dimensions for the moment. This doesn’t mean this is all you need to know about them, or that I’ve covered every angle and consideration (I haven’t), but this should now suffice for most people to make an informed and considered decision as to when to use them, how to use them, and how best to populate them.

Slowly Changing Dimension Implementations - Synchronous Triggers
Produced: 22/12/2018 15:23:00
In the previous post I went through what a Slowly Changing Dimension is and why they can be such a problem for people when designing database solutions. Here I’ll start to work through a few implementations. Note there is no real “this is the way to go” solution because each has its advantages and pitfalls and needs to be considered carefully by each architect for the design at hand but, hopefully, knowing a few solutions will mean that you can try and few and find one that you’re most comfortable with.

Slowly Changing Dimension Designs - Temporal Tables
Produced: 12/12/2018 06:59:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Slowly Changing Dimension Designs - History tables
Produced: 01/12/2018 16:34:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Slowly Changing Dimension Designs - Date Range with Parent
Produced: 21/11/2018 00:47:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

Slowly Changing Dimension Designs - Basic Date Range
Produced: 10/11/2018 10:51:00
In a previous post I went through what a Slowly Changing Dimension is and how it can be a pain to all and sundry when designing a database. Before I move on to implementation in regard to code, I just wanted to touch on a few design thoughts in regard to the tables themselves as there is more than a couple of ways to store a slowly changing dimension. There’s no right or wrong, therefore these are just a few ideas to consider depending on what you feel suits you best or you’re most comfortable with.

What is a Slowly Changing Dimension?
Produced: 31/10/2018 02:05:00
In all databases, data mart architects and developers have inevitably been faced with the one peril we all wish was easier… Slowly Changing Dimensions.

Paging using Native T-SQL Commands
Produced: 22/10/2018 13:07:00
One of the most common requirements within websites and other application is the ability to page results. This used to be an onerous task and, in many cases I’ve seen, people have tended to simply pull a full dataset from SQL Server, cache the results in the application, and then let the application itself page out the results accordingly. However, there is an easier way to do this directly from within SQL Server without using complex code.

Create a Login with no User
Produced: 12/10/2018 20:22:00
Continuing the theme of Users and Logins, I was asked if there was a circumstance in which you would ever require a Login without an affiliated User? The answer is yes, and it’s more common than you think.

Create User without Login
Produced: 05/10/2018 09:23:00
Following on from my last post in which I explained the difference between a Login and a User, I’m going to expand a little and explain the concept I use quite a lot within some of my posts… the “login-less user”.

SQL Server Login versus SQL Server User
Produced: 26/09/2018 23:44:00
In a good many of my demo scripts I create Users with the caveat “without Login” which led to someone asking me the other day what the difference is in SQL Server between Logins and Users. This was actually asked via text which resulted in a somewhat stunted answer but, in essence, an accurate one and therefore I’m going to keep this relatively short as well…

SELECT INTO with IDENTITY
Produced: 17/09/2018 08:47:00
This is a fun one because I’ve seen numerous people struggling with identity columns in temp or freshly generated tables. For example, let’s take a look at the Person.Person table in AdventureWorks:

Row Level Security on In-Memory Tables
Produced: 08/09/2018 15:19:00
Quick note to combine the last few sets of posts… can you use Row Level Security on in-memory tables?

Greyed out Column Permissions in SSMS
Produced: 31/08/2018 21:30:00
I’ve had this question a few times now so I thought I would follow up my last post on column permissions with this little note about the SSMS implementation. Basically, I’ve had people ask “Why can’t I add Column Permissions in SSMS? It’s always greyed out.”. This is what they’re talking about:

Column Level Security Examples
Produced: 24/08/2018 07:22:00
I’ve done a few posts about Row Level Security so I thought I would make one about Column Level as well as, many times, people want to restrict access to columns as well and believe the only way is through re-architecting their tables or hiding everything behind a view layer.

Easily Testing User Permissions
Produced: 14/08/2018 20:57:00
This is a quick post just to highlight a simple concept that many people don’t know exists. Every DBA should be implementing tough security rules and permissions across their estate, but this can be very hard to do without having the ability to test any security amendments you may be making.

Row Level Security Example
Produced: 04/08/2018 02:57:00
This follows on from the last post in which we discussed Row Level Security. This post provides a nice and simple example based on the example proffered in the previous blog post. We have the following query:

Basics of Row Level Security
Produced: 24/07/2018 08:31:00
As with most other people I know, I have been working with several companies lately around security in the wake of the GDPR law changes. As an offshoot of this I figured I would write a few posts surrounding some of the data security features within SQL Server that I’ve been playing with. The first of these is Row Level Security.

Column Compression? Compress and Decompress
Produced: 13/07/2018 16:43:00
This was something which came up recently when discussing storage of XML columns within a SQL Server database and the large amounts of space it can take up. Most of us know about the compression which has been around for years now, those being Row level and Page level compression…

Quick Note About Backup File Extensions
Produced: 03/07/2018 00:53:00
This was something I was asked about the other day which I’d not really given any thought to but which was causing others a great deal of confusion… We’re all aware that the default file type for a SQL Server backup file is “.bak”, but does it have to be?