While using partition table SQL feature, I ’ve experienced strange behaviour. It seems that it’s better to insert rows into the table with while loop than inserting rows into the temporary table, and then at once insert from temporary table to original table.
I will try to illustrate the situation.
I ’ve enabled partitioning on table TestTabela on column CourtID.
CREATE PARTITION FUNCTION [TestPartition](int) AS RANGE LEFT FOR VALUES (1, 2, 3)
GO
/****** Object: PartitionScheme [TestPartition] Script Date: 1/18/2016 10:19:54 AM ******/
CREATE PARTITION SCHEME [TestPartition] AS PARTITION [TestPartition] TO ([Test1], [Test2], [Test3], [Test1])
GO
/****** Object: Table [dbo].[TestTabela] Script Date: 1/18/2016 10:19:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTabela](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CourtID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_TestTabela] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [TestPartition]([CourtID])
GO
ALTER TABLE [dbo].[TestTabela] SET (LOCK_ESCALATION = AUTO)
GO
/****** Object: Index [NonClusteredIndex-20160117-134607] Script Date: 1/18/2016 10:19:54 AM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160117-134607] ON [dbo].[TestTabela]
(
[CourtID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I am now trying to insert 100.000 rows in CourtID 1 (first partition), and at the same time, insert single row in CourtID 2 (second partition).
Example for the first case:
I will insert rows into the temporary table and than copy into the original table.
create table #tmp(CourtID int,Name nvarchar(50))
DECLARE @count int
SET @count = 1
WHILE @count <> 100000
BEGIN
INSERT INTO #tmp
SELECT 1,N'Test'
SET @count = @count + 1
CONTINUE
END
begin tran
insert into TestTabela(CourtID,Name)
select CourtID,Name from #tmp
commit
Example for the second case:
Using while loop to insert rows directly into the table.
begin tran
DECLARE @count int
SET @count = 1
WHILE @count <> 100000
BEGIN
INSERT INTO TestTabela
SELECT 1,N'Test'
SET @count = @count + 1
CONTINUE
END
commit
In both cases, I tried to execute following command in a separate window:
INSERT INTO TestTabela
SELECT 2,N'Test'
In first test, insert waited for commit. In second case, insert into second partition happened immediately.
I want to find a way to lock only one partition using the first case scenario. Is it possible?
I am using SQLServer 2012R2, by the way...
