Xrhrft

I have posted this question to stackoverflow (https://stackoverflow.com/questions/54613295/sql-server-design-question-stored-records-as-rows-vs-as-blob-nvarcharmax) and I was advised to post it here too.

--

I am creating a schedule for our engineer to analyze. The schedules are downloaded each day (automatically by the application on the local machines) and the analysis is done on that local computers.

So, now, I am in this dilemma of storing the schedule in database as table rows or as nvarchar(max).

Here is the requirement

1. The schedules are generated each day. Each schedule is accurate to 1 seconds. So, at most, it will contain 86,400 records per schedule.


2. In a day, depending on the setting, the system can generate up to 100 schedules per engineer (we have a about 10 engineers)


3. The schedule contains the following fields: INT | INT | INT | INT | NVARCHAR(1024) | NVARCHAR(64) | BIT | BIT | DATETIME | DATETIME (In summary: 4x INTs, 2x NVARCHARs, 2x BITs, and 2x DATETIMEs)


4. The schedule is rarely going to be updated, but it can be updated. The updatable fields are: 2x BITs and 1x DATETIME.

Now looking at the common case scenario:

In a day, it will generates about 1,296,000 records per day.

This is the calculation of common case scenario:

- 10 seconds accuracy per schedule = 8,640 rows

- 5 engineers run the scheduler each day

- Each engineer generates about 30 schedules



So total is: 8,640 * 5 * 30 = 1,296,000 records

If I store each schedule as NVARCHAR(MAX) with comma delimited, then the number of records are reduced to only 150 records per day.

Here is the calculation:

- 10 seconds accuracy per schedule = 8,640 rows --> stored as NVARCHAR (becomes 1 record)

- 5 engineers run the scheduler each day

- Each engineer generates about 30 schedules



So total is: 5 * 30 = 150 records

Now, this is the requirement for those schedules:

1. The generated schedules can be viewed on the website.


2. The schedules is downloaded by the application each day for analysis (analysis is done on local computers).


3. The fields (2x BITs) can be updated once the analysis is completed. These fields can be updated by application (after finish analyzing the schedule) or can be updated (manually) by the engineer on the website.


4. All generated schedule must be stored for at least 3 months for auditing purposes.

What is your recommendation? Store schedules as table rows OR NVARCHAR(MAX)