11 votes

Are duplicated NVARCHAR values stored as copies in SQL Server?

Under "normal" conditions, no, data in VARCHAR and NVARCHAR columns is not de-duped (although duplicate attribute and/or element names in a single XML value are reduced to a unique instance)....
user avatar
8 votes
Accepted

Are duplicated NVARCHAR values stored as copies in SQL Server?

Yes duplicated data is stored as copies in SQL Server To change this behavior, you would need to implement PAGE COMPRESSION feature - create or rebuild indexes with (data_compression = page) option It ...
user avatar
7 votes

Consolidating a row of data, based on previous rows

Sample data with the Date column typed as date: CREATE TABLE dbo.Staff ( [ID] integer NOT NULL, [Surname] varchar(5) NULL, [FirstName] varchar(4) NULL, [Office] varchar(9) NULL, [Date] ...
user avatar
  • 72.5k
6 votes

Are duplicated NVARCHAR values stored as copies in SQL Server?

Compression is fine and probably useful in your case, but you should normalize your tables anyway and try to achieve a structure where you're only storing one copy of the same address. This will lead ...
user avatar
  • 25.7k
5 votes
Accepted

Consolidating a row of data, based on previous rows

I was able to do this with a recursive CTE so it's not that different from a cursor. Also these do not tend to scale well over large volumes. Have a look through the code and see what you think. ;...
user avatar
  • 10.2k
5 votes

Are duplicated NVARCHAR values stored as copies in SQL Server?

Just a note on page compression: This type of compression is limited on what's on a page. I.e., you won't see any reduction of duplicates values that ends up on different pages. Also, page compression ...
user avatar
4 votes

Are duplicated NVARCHAR values stored as copies in SQL Server?

I'm not sure of your design, but my recommendation is that you store the address in a separate table from the customer table. The association between the two tables will depend on the business logic (...
user avatar
  • 540
4 votes
Accepted

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

Checking whether the dates are contiguous You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in ...
user avatar
3 votes
Accepted

Building slowly changing dimension on a Fact/Dimension Star Schema

Usually, dimension tables contain a single valid time (start and end date) for all fields and SCD2 would apply to the complete record. It is good practice to use an non-null end value ahead in time to ...
user avatar
  • 359
3 votes
Accepted

Designing a dimensional DB off a normalized source that already implements SCD's

As you are seeing, one of the benefits of Kimball Dimensional Modeling is that the data warehouse design is essentially your SSAS design. While there are always exceptions, you can typically select a ...
user avatar
  • 2,369
3 votes
Accepted

How do I make a nullable "parent" hierarchy level?

You actually have 2 questions in one question. If you create a new question for the attributes it would be neater and I'll cut/paste half of this as an answer there :) Nullable Parent Level You ...
user avatar
  • 15.4k
2 votes

Consolidating a row of data, based on previous rows

You could use a window MAX() to find the date of the last column value so far, for each column: SELECT *, LastSurnameDate = MAX(CASE WHEN Surname IS NOT NULL THEN [Date] END) OVER (PARTITION ...
user avatar
  • 21.3k
2 votes
Accepted

History table implementation: "Tuple-versioning" vs Effective Date

Tuple versioning Will require two updates for each new piece of data - one to set the end date of the old row & one to insert the new row. The index(es) most likely will contain the interval ...
user avatar
2 votes
Accepted

SQL Server Stored Procedure Slowly Changing Dimension

f you can find a way to make this more efficient/optimal, feel free to edit Without knowing anything about indexes etc, I tried filling up the staging table with some sample data: (FoodName,...
user avatar
2 votes

How to store hierarchical dimension for timeseries data

SQL Server has a hierarchyid type defined to deal with this kind of system. Consider this example: IF OBJECT_ID(N'dbo.Categories', N'U') IS NOT NULL DROP TABLE dbo.Categories; GO CREATE TABLE dbo....
user avatar
  • 67.5k
2 votes
Accepted

Join Fact table to SCD Type 2 ... how to write query? (SQL Server)

Setup some sample data: create table fact_picked (Emp_Name varchar(30) ,[Date] date ,Apples_Picked int); insert into fact_picked values ('John','10/17/15',175), ('John','05/01/...
user avatar
  • 2,501
2 votes
Accepted

Slowly Changing Dimensions Type 4

Usually, we use SCD-type-4 when a dimension(SCD Type 2) grows rapidly due to the frequently changing of its attributes. These frequently changing attributes will be removed from the main dimension and ...
user avatar
1 vote

How to deal with fact table data that needs to be version controlled?

As is often the case; a little more googling gave me an answer. Just in case the link ever breaks: So the way the solution works is that the fact table changes are simply treated like SCD type 2 ...
user avatar
  • 131
1 vote

Fact table linking to natual key rather than row key in a slowly changing dimension table?

I am curious to see if there are any other answers here but I will explain how I have approached this issue in the past. There are two solutions I believe you can implement in this case: As you said, ...
user avatar
  • 158
1 vote
Accepted

Type 2 Dimension for a fact measure

Honestly it really just depends on your use cases, but for me I would likely just store all 10 columns in the same table and call it a day (unless they were very unrelated to each other, then I might ...
user avatar
  • 25.7k
1 vote

The right way to model multiple FACTs schema

So the first question is: Are you absolutely sure you need to use a star schema? Having worked in a number of warehouses built to varying levels of quality, I have found star schema to be: 1. ...
user avatar
  • 3,442
1 vote

SSIS: Slowly changing dimension with lists (one-to-many relationship)

I think that in order to keep the logical aspect of a dimension you should use a third table to relate both dimensions: PersonScd GroupOfCars CarScd --------- ...
user avatar
1 vote

Consolidating a row of data, based on previous rows

FYI, here's the answer I have at the moment using a while loop. Note, I've also added a column with the change id (which can easily be added using something like ROW_NUMBER() OVER (PARTITION BY ID ...
user avatar
1 vote
Accepted

How to create a view with added rows for missing dates?

I wouldn't organize your "helper table" that way. I would keep only {Region, Salesman, StartDate}. To create a view of intervals, join that table to itself to create data ranges where a.StartDate &...
user avatar
1 vote

When to choose an attribute vs. a new dimension?

To make gender a freestanding "dimension" is, more likely than not, an attempt to normalize your dimensional model. Unless you have attributes associated with gender then how is it a dimension? Is it ...
user avatar
  • 2,369
1 vote

Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

I think, in your case a derived table is necessary to isolate querying number of mutations of postcodes per customer: SELECT c.postalcode , sum(s.SaleAmount) SaleAmount , count(postcode_mutations....
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible