Skip to main content
12 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)....
Solomon Rutzky's 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 ...
Aleksey Vitsko's 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] ...
Paul White's user avatar
  • 84.3k
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 ...
J.D.'s user avatar
  • 37.5k
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. ;...
wBob's user avatar
  • 10.4k
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 ...
Tibor Karaszi's 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 (...
Jim D's user avatar
  • 560
3 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 ...
Yassine LD's 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 ...
Grimaldi's user avatar
  • 420
3 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/...
markp-fuso's user avatar
  • 2,569
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 ...
Andriy M's user avatar
  • 23k
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 ...
Michael Green's 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,...
Randi Vertongen's 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....
Hannah Vernon's user avatar
  • 70.1k
1 vote

Optimal join for joining facts with scd-type-2 dimension for aggregation/reporting

use range join optimization - it's intended to support this exact scenario. You an also do a bit of z-ordering or bucketing of your data to help manage shuffling and data exchanging https://docs....
feemurk's user avatar
  • 11
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 ...
Jossy's user avatar
  • 83
1 vote

Converting EAVT table into SCD type 2

I would personally get a combined set of both tables for each date and load them (and historize as SCD type 2) as if they were loaded on daily basis. So load each day separately one by one
Tomasz T's user avatar
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, ...
Antyan's user avatar
  • 158
1 vote

Implementing revisioning logic with SCD2/4

Original SO post: https://stackoverflow.com/questions/69057139/implementing-revisioning-with-scd2-4 From your comment in that post (which should be copied into your question here -- please edit your ...
dwhitemv's user avatar
  • 1,567
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 ...
J.D.'s user avatar
  • 37.5k
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 --------- ...
Yassine LD's 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 ...
user2045064's 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 &...
James K. Lowden's user avatar

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