I got inspired to write this article becuase of such nice feature of SQL Server improved 80% speed to application response.
Problem: I was trying to retrive 1,000+ of customers from 4,00,000 which nearly taking 2 minutes with Indexed created and Optimisez SQL statement. After implementation of partitioning it was just 15 seconds response time.
Partition feature depends on your database scheme and use. I was retriving customers based on DealerCode so I have created the Parition by Dealers. In the example below I am taking Date as example.
Procedure:
1: Right click on the table and in the context menu select Storage >> Create Partition.
2: Select table column to parition (E.g. DealerID, Year, CountryID)
3: Give the name of the partition function
4:Provide the name of the partition scheme and press next.
5:
Left boundary
The Boundary column label in the grid will dynamically display <= Boundary when you select Left boundary.
Right boundary Select to include range values up to the specified value in the Boundary column for each filegroup selected. The specified value will be the starting value for the range values of the filegroup on the next row. The Boundary column label in the grid will dynamically display < Boundary when you select Right boundary.
Select the starting and ending date and select the Date Range from Monthly, Yearly, Quarterly, Half-Yearly , Daily and press OK.
Thats It.
