0

I have Payroll process implemented in SQL server 2012. Table having 365 columns in order to store details of each day for different PayHead section.

PayHead includes (Basic,HR,Deduction,ESIC,Tax,PF,etc..)

Is it a good idea to make all this 365 columns into single Date column and then need to process the query?.

Need different suggestions on the above scenario to align the table such that i can do some improvement operations on it(Indexing,Partitioning,etc..).

Jignesh patel
  • 23
  • 12
  • first you have to concentrate on Normalisation otherwise we will be having issue in insert ,deletion update, anomalies., then depends the records size we can add indexes to improve performance – Sreenu131 Nov 29 '18 at 12:35
  • A year actually has up to 366 days -- but a table with 366 columns is an absurd monstrosity. A table with 366 rows (or actually less, because there are fewer *business* days in a year) is a lot more reasonable. Aside from that, I wonder what sort of "details" vary per *day*. You'd expect most of this to be simple variation that could be computed. Where it's not (like a flag whether something's a business day) *that's* the kind of thing you want columns for. Typically, this stuff is stored by generating a calendar table that holds all the relevant data for, say, the next 100 years. – Jeroen Mostert Nov 29 '18 at 12:36
  • While in a general sense I see you have something with 365 columns in a specific descriptive narrative your question is not easy to understand. " Table having 365 columns in order to store details of each day for different PayHead section." - generally you would create a table with an ID (PayHeadId and other columns) and then a table of "days" of stuff. Say "PayDetailId, PayHeadId, PayDate, PayAmount" etc. – Mark Schultheiss Nov 29 '18 at 12:48
  • "Partitioning" - only if you need to do that (probably not) – Mark Schultheiss Nov 29 '18 at 12:51
  • @MarkSchultheiss : My table will be having records like if there are 100 PayHead then for 10 Employees There will be 1000 rows for the PayHeads only, extra rows will be there for Calculative columns for those 100 employees. And all this with 365 columns having values associated with each employees/per financial year. – Jignesh patel Nov 29 '18 at 12:52
  • Is "PayHead" something like pay details for an Employees pay date? Please edit your question to elaborate as frankly I am even more confused by your last comment where you mix rows/columns in "extra rows will be there for Calculative columns" – Mark Schultheiss Nov 29 '18 at 12:59
  • @MarkSchultheiss edited the question... – Jignesh patel Nov 29 '18 at 13:07
  • Payroll systems can get very complicated, you might consider starting with a sample database you can download. – Mark Schultheiss Nov 29 '18 at 19:38
  • I already have the table structure like : Id EmpID PayHeadDescription SalaryRegister_PayHead_Category .... Just to give better idea on the table structure – Jignesh patel Nov 30 '18 at 04:50

1 Answers1

1

No, it is not good idea to create 365 columns. You need to normalize your data. This article shows what you want to do and the way what to do. So create one table with columns

  • column ID to have a key
  • column Date where you will store day
  • column PayHead

And you can create the following non-clustered index to find by column Date :

CREATE NONCLUSTERED INDEX [IX_Payroll_Date_PayHead]
ON dbo.Payroll
(Date)
INCLUDE (PayHead)
GO

And you will have table with approximately 365 rows which is good to be to find by Date column with non-clustered index IX_Payroll_Date_PayHead.

This query will have a query plan with index seek:

SELECT
  Date
, PayHead
FROM Payroll
WHERE DATE = '2018-29-11'
StepUp
  • 36,391
  • 15
  • 88
  • 148
  • So by using this is there a need to use this table as INNER JOIN in the original query to map PayrollHead Dates?... Can you give idea on how to use this newly created table. – Jignesh patel Nov 29 '18 at 12:57
  • @Jigneshpatel yeah. You are right. But it is not expensive operation. – StepUp Nov 29 '18 at 12:59
  • Ok, Let me align this kind of structure and then identify usefulness through this process. – Jignesh patel Nov 29 '18 at 13:01
  • @Jigneshpatel I've updated my answer to add a link to article where you can read about your wish to implement and how it can be solved. – StepUp Nov 29 '18 at 13:04