Member Avatar for zachattack05

I am working on a table that will hold batch data.

The table will have a BatchDate column as a DateTime type. I'm considering adding a column for BatchYear as an integer and BatchPeriod (which could be a week number, a month number or a quarter number) as an integer, even though both of these values can be derived from the BatchDate and using another column in the table.

I'm considering doing this because I'm thinking it would be easier to work with queries where someone doesn't have to pull the date from the table first, compute the period they want and then query the table again. Also, if someone is looking for a batch from the 3rd quarter of 2012, the server would have to convert each BatchDate to it's quarter equivalent and do the same for the year and then test for a match, but if the values are there in separate columns, one could simply query the BatchPeriod and the BatchYear column for a match.

Is this a bad idea to break normal form this way?

  • 3 Contributors
  • 3 Replies
  • 676 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

Short answer: not inherently bad, but don’t casually duplicate columns unless you accept the maintenance cost. is right that redundancy risks inconsistency, and is right that denormalization can be the practical choice for heavy-read workloads. The usual compromise in SQL Server is to keep a single datetime and expose precomputed year/period values that the engine maintains for you (so you get consistency and query speed).

A clean, low-maintenance approach is persisted computed columns. They are defined from BatchDate, stored by SQL Server, and can be indexed for fast lookups without manual updates. Example (minimal):

CREATE TABLE dbo.Batch
(
  BatchID    INT IDENTITY PRIMARY KEY,
  BatchDate  DATETIME2 NOT NULL,
  BatchYear  AS YEAR(BatchDate) PERSISTED,
  BatchQuarter AS DATEPART(QUARTER, BatchDate) PERSISTED
);

Persisted computed columns let the engine own consistency while giving you a physical column to index. (learn.microsoft.com)

If you filter/aggregate frequently by year/period, add a nonclustered index (or a composite index) on those computed columns so queries like “WHERE BatchYear = 2012 AND BatchQuarter = 3” can use an index seek instead of scanning and computing per row:

CREATE INDEX IX_Batch_YearQuarter
  ON dbo.Batch (BatchYear, BatchQuarter);

Indexes on computed columns have requirements (deterministic/precise expressions, certain SET options); check the docs and test plans. (learn.microsoft.com)

A few practical cautions: week numbers and weekday values can be nondeterministic (session settings such as DATEFIRST affect results), so pick ISO functions or a deterministic formula, or better still use a date-dimension table when week semantics matter. If you consider an indexed (materialized) view instead, be aware of the stricter rules (SCHEMABINDING, determinism, ANSI/SET settings) and the write/maintenance costs. Choose the approach based on workload: high-volume writes -> keep single date and index it; read-heavy analytics -> persisted computed columns or a date-dimension/indexed view. (learn.microsoft.com)

Recommended Answers

All 3 Replies

Member Avatar for Reverend Jim

It is bad practice to have duplicate data. That could lead to inconsistencies. I advise you to store the date as one column and create a view with the derived columns. That gives you non-redundant columns plus the convenience of pretending that the derived coulmns actually exist (simpler queries).

Edited by Reverend Jim
commented: My thoughts too +1 +15
Member Avatar for zachattack05

Good idea!

Thanks!

Member Avatar for Dani

While I'm certainly not disagreeing about this particular instance, personally I don't think that it's always best practice to never duplicate data in a database. There are many instances in which it's beneficial to denormalize your data, being that CPU resources are much more expensive than hdd space. Sometimes it's the more efficient solution to save yourself from large and resource intensive table joins in favor of duplicating columns across tables. I speak from MySQL experience. Not sure if MS SQL is entirely a different beast entirely.

commented: Fer sher. +14
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.