Sql-server – Storing date as integer (numeric), what are the advantages

date formatsql serversql-server-2008

Question 1

I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example

20120101  -- 01 Jan 2012

Is there any advantage of keeping numeric date system and not using SQL Datetime?

Question 2

Now I am trying to loop through numeric date to find customers between two dates. If the start and enddate encompass two months, I get thousands of records instead of just 60. Example:

create table #temp1(day int,capacity int) /* just a temp table */

declare @start int 
declare @end int

set @start=20111201
set @end = 20120131

while (@start <= @end) 
Begin
    insert into #temp1  /* I am storing things in #temp table so data looks pretty */
    exec usp_GetDailyCap @date1= @start

    set @start = @start + 1;    
end

select * from #temp1

This pulls 8931 records instead of 60. Is there a better way to improve the logic above so I pull only valid dates? I tried IsDate and sub-queries but that did not quite work in an efficient way.

Best Answer

To answer your first question, I would recommend using the DATETIME data type within SQL Server. Not necessarily for performance reasons, but to leverage the RDBMS-specific functionality. For instance, you would have to re-invent a lot of logic just to do basic date math (think DATEDIFF(), DATEADD(), DATEPART() and many other functions. They are obviously tailored to the DATETIME data type and are easy to work with).

As for your second question, you are running into the exact problem that the first question (and my answer) is geared towards. You are looking at 20111201 and 20120131 as dates, and your brain is telling you that should be a difference of 60 days. Well, you're looping through based off of the delta...which is:

20120131 - 20111201 = 8930 (with the inclusive loop it'll be 8931)

In other words, your WHILE loop is executing 8931 times. This is happening because those are integer values and your loop will not jump from 20111231 straight to 20120101.

You integers aren't going to take into account the cap of years and months (i.e. your Question 2 problem).

Related Solutions

Sql-server – the best way to get all data for a date range, plus the last event just before the range

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT 
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END ) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY 
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )

Sql-server – Should I add a clustered Index on a foreign key that is not unique

It would be a great design approach to put the clustered index on your FK if you will be fetching all the lines (I mean all the data of the line) for a given FK, or making joins based on that key : as they're stored physically in this order, they will come up much more quickly. Otherwise you could use a non-clustered index (slower fetching of the actual rows' data).

You may encounter a slower insert performance though.

Values can be multiple in a CI, they are also very efficient to quickly fetch a single, uniquely identified, row's data (Is it the reason why MS puts the clustered index on the PK by default ?).

I would recommend to make some tests using both approaches with a subset of your data, and check the estimated execution plans using CTRL+L, then include the EP via CTRL+M before you execute the test statement. This way you will see wich indexes are being used and how the engine modifies its strategy depending on what you are querying.

I generally avoid includig dates for unicity beacause on multiple insert statements the date for all inserted records is the same... I prefer using the default value of the uniqueidentifier type : myCol uniqueidentifier NOT NULL DEFAULT(NEWID()) which ensures uniqueness among all.

If you need all the columns' data of several rows with the same foreign key I would use the clustered index on non unique values. You may however check the execution plans.