[SOLVED] nvarchar or varchar or date in joins of SQL Server

nvarchar or varchar or date in joins of SQL Server

I have a management project developed in Web using SQL Server 2008 R2 and Windows Application using SQL Server CE 4.0.

Both uses almost same database structure. I have a fees table as :

TABLE [fees] 
(
    [feeid] bigint NOT NULL IDENTITY(1,1),
    [acno] int NULL DEFAULT 0,
    [billingmonth] nvarchar(10) NULL,
    [oldbal] numeric(18,2) NULL DEFAULT 0,
    [duedtamt] numeric(18,2) NULL DEFAULT 0,
    [afterdtamt] numeric(18,2) NULL DEFAULT 0, 
    [bal] numeric(18,2) NULL DEFAULT 0,
    [depamt] numeric(18,2) NULL DEFAULT 0,
    [totdpo] numeric(18,2) NULL DEFAULT 0,
    [depdt] datetime NULL
)

billingmonth will always use format MMM-yyyy eg. Jan-2018

BillingMonth uses various joins (inner and left outer join) in other tables.

How to increase performance of joins with BillingMonth ? , should i :

Convert nvarchar to varchar ( as it will always store Month in SQL2008R2 )
Convert nvarchar to datetime ( as first day of month 01-MMM-yyyy in SQL Server CE and SQL Server 2008 R2)

Solution

Storing date values as nvarchar is not recommended at all

There are many suggestions to increase the join performance:

Use Date datatype
Use two numeric field month and year instead of one varchar field (tinyint for month, smallint for year, they can be used only for joining purpose)

Note that: AS @Pரதீப் mentioned, When you store month and year separately, you need to do some integer manipulations when searching for date ranges