How to create the partition dynamically using SP

Question

How to create the partition dynamically using SP

Arutprakasam 261

Hi, can any share the query partition the table based on timestamp. My scenario is I have to create the partition for everyday based on date and timestamp. Please help me.

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-11-25T08:37:32.13+00:00

Can you give more background to the requirement? Particularly, why the need to include the timestamp? Why not simply generate one partition per date, in which case you could generate partitions for one year ahead if you wanted to. Sure, it can be done dynamically by a given timestamp, but it may be overly complex. I mean, if you don't know how to do it, it will be difficult for you to maintain the solution if write something for you.

Arutprakasam 261

Hi @Erland Sommarskog ,

My Requirement is to retrieve the set of data in between the two datetime.

In the above scenario if that table has a huge amount of records then am getting a timeout error or some delay in getting a result from a select query, at the same time 100 to 200 updates and 300 select queries are performed on the same table.

So, I want to partition the table by date. It has to be done one day before the next day's partition or else for the entire week, month, or year.

If you have any other better solution then please share and also kindly provide the partition script.

CREATE TABLE [dbo].[alert_log](
    [alert_id] [int] IDENTITY(1,1) NOT NULL,
    [alert_no] [nvarchar](100) NOT NULL,
    [station] [nvarchar](30) NULL,
    [sensor] [nvarchar](100) NOT NULL,
    [sensor_type] [nvarchar](100) NOT NULL,
    [alert_type] [varchar](15) NULL,
    [fault_type] [varchar](20) NULL,
    [lsl] [nvarchar](10) NULL,
    [alert_value] [nvarchar](20) NULL,
    [hsl] [nvarchar](20) NULL,
    [start_time] [nvarchar](25) NULL,
    [end_time] [nvarchar](25) NULL,
    [duration] [nvarchar](25) NOT NULL,
    [status] [varchar](10) NULL,
    [timelapse] [nvarchar](25) NULL,
    [date] [nvarchar](20) NOT NULL,
    [remarks] [nvarchar](255) NOT NULL,
    [acknowledge] [varchar](25) NULL,
 CONSTRAINT [PK_alert_log_alert_id] PRIMARY KEY CLUSTERED 
(
    [alert_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [NCK_St_time]    Script Date: 23-11-2023 10:07:19 ******/
CREATE NONCLUSTERED INDEX [NCK_St_time] ON [dbo].[alert_log]
(
    [station] ASC,
    [sensor] ASC,
    [fault_type] ASC,
    [status] ASC
)
INCLUDE([start_time]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[alert_log] ADD  CONSTRAINT [DF__alert_log__start__4D94879B]  DEFAULT (NULL) FOR [start_time]
GO
ALTER TABLE [dbo].[alert_log] ADD  CONSTRAINT [DF__alert_log__end_t__4E88ABD4]  DEFAULT (NULL) FOR [end_time]
GO
ALTER TABLE [dbo].[alert_log] ADD  CONSTRAINT [DF__alert_log__timel__4F7CD00D]  DEFAULT (NULL) FOR [timelapse]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'alert_log' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'alert_log'
GO

Kindly help me.

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-11-25T08:37:32.13+00:00

Can you give more background to the requirement? Particularly, why the need to include the timestamp? Why not simply generate one partition per date, in which case you could generate partitions for one year ahead if you wanted to. Sure, it can be done dynamically by a given timestamp, but it may be overly complex. I mean, if you don't know how to do it, it will be difficult for you to maintain the solution if write something for you.

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

From what you say, I don't think partitioning is a good solution to the problem.

It is true that if the date column you query by today is not indexed, there would be less to scan if you partition the table, since SQL Server only has to scan the partitions that contains the interval. On the other hand, queries that do not include the partitioning column will be less efficient since they have to visit every partition. You say that rows are updated; I assume that updates are by the primary key, alert_id. These updates will be a lot less efficient, unless they are rewritten to also include the partitioning column.

I think a better option is to simply add a traditional index on the column(s) you query by.

By the way, I note that the columns start_time, end_time and date are all nvarchar. This is a very bad choice for date/time data. It will not take long before you have a record for Nov 31st. Also, in which format do you store the dates? If the format is not YYYYMMDD, there is no indexing - or partitioning - to save you. I strongly recommend that you change these columns to use proper data types. That is, datetime2 with the proper scale for start_time and end_time and date for the date column.

Arutprakasam 261 Reputation points

2023-11-26T10:02:51.4933333+00:00

Hi @Erland Sommarskog ,

Can you please share the query for partition the table.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-11-26T10:11:50.3966667+00:00

I'm sorry, but I don't understand this. You first accept my answer, of which the gist is "don't partition", and then you ask how to partition the table anyway.

In any case, my strong recommendation is that you don't partition the table. Instead I recommend that you review the data types and add proper indexes. This is a lot better start. Partitioning a table when you don't know you are doing can easily make matters worse.
Arutprakasam 261 Reputation points

2023-11-26T11:34:17.3666667+00:00

Yea thank you I will do what you suggested for me..

Share via

How to create the partition dynamically using SP

0 additional answers

Your answer