I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about another million per year, for now. One of the columns in the source table is VARCHAR(MAX) and almost all of the values in this column are unique. My questions here are:

  1. Is the best approach here to store this table as fact table in the first place? Could it be modeled as a dim table?
  2. Should I store the column as it is in the fact table?
  3. Should I try to decompose the elements of the XML data (there are about 10 elements) and add them as attributes in a dim table? There could be a problem with this approach if more elements are added into the source table in the future.
  • 1
    I would imagine storing XML Documents as xml (instead of VARCHAR(MAX) ) would ensure that the document conforms to XML Standards. This way, garbage data (ie non XML data) would not mess up your SQL Statements and Processes because its not allowed in the database. – Michael Kutz Feb 11 '19 at 20:36
  • @MichaelKutz if you meant change data type of the column at the source, I can't do it as I have no control over that system. If you meant to change the column data type to XML in the fact table, so are you suggesting that the 2nd approach above is the best way forward here? – Sohi Feb 11 '19 at 20:46
  • I'll try to move it into an Answer. But - IMHO - (2) and (3) – Michael Kutz Feb 11 '19 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.