Partition

MSSQL 2012 New T-SQL Features

/ MSSQL Server /

The example query below uses a new feature in SQL Server 2012 to divide the result of a query in pages. This particular example will start from the second record and retrieves the following 2 records.

SELECT [name], [value] FROM Table1
ORDER BY id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS

The next query adds a derived column to the result with a cumulative sum based on a specific column. When using PARTITION BY the sum is only applied to the specific group.

SELECT [name], [value], 
SUM(value) OVER (
    --PARTITION BY [name] --SUM by column name only...
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cummulativeValue
FROM Table1

MSSQL Table Switch IN/OUT examples

/ MSSQL Server /

MSSQL table switch IN, switch OUT examples…

-- Switch in/out procedure examples...
-- Tim van Kooten Niekerk


-- Partition function en schema maken...
--DROP PARTITION FUNCTION pfTable_1
--DROP PARTITION SCHEME psTable_1
CREATE PARTITION FUNCTION pfTable_1 (int) AS RANGE LEFT FOR VALUES (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
CREATE PARTITION SCHEME psTable_1 AS PARTITION pfTable_1 ALL TO ([PRIMARY])


-- Make  table with a partition funciton on column jaar (year)...
CREATE TABLE [dbo].[Table_3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL
) ON [psTable_1]([jaar])


-- Check partitioned table...
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Table_3') ORDER BY partition_number

-- Make Switch OUT and truncate table so you can reload 2015...
-- Make Switch OUT table without constraints...
CREATE TABLE [dbo].[Table_3_OUT](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL 
)
ALTER TABLE Table_3 SWITCH PARTITION 5 TO Table_3_OUT
TRUNCATE TABLE [dbo].[Table_3_OUT] 


-- Now you can reload (INSERT) new values for 2015 in the original table... 
-- Or... Make Switch IN with using a switch IN table for 2015...

 
-- Switch IN table with constraint on jaar = 2015...
CREATE TABLE [dbo].[Table_3_IN](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] CHECK (jaar = 2015) NOT NULL 
)
-- INSERT new values in switch IN table and make switch...
ALTER TABLE Table_3_IN SWITCH TO Table_3 PARTITION 5


-- Extend partition function with a new year...
ALTER PARTITION SCHEME psTable_1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION pfTable_1 () SPLIT RANGE (2021)

MSSQL Create Table Partitions

/ MSSQL Server /
Instruction written in the Dutch language…
  1. Open SQL Server Management Studio en klik met de rechter muisknop op de gewenste database. Selecteer properties.
  2. Maak nieuwe bestandsgroepen op de de Filegroups pagina. Markeer zonodig de filegroup als readonly.
  3. Selecteer de gewenste database in het drop-down menu.
  4. Voer onderstaande query uit om de partition function aan te maken op basis van een kolom met datums waarbij de eerste partitie stopt bij datums kleiner of gelijk aan de huidige datum – 365 dagen (LEFT). Bij RANGE RIGHT wordt de tabel gesplitst bij waarden kleiner dande opgegeven waarde. 
    CREATE PARTITION FUNCTION pfTablename (datatype) AS RANGE LEFT FOR VALUES (Getdate() - 365)
  5. Voer onderstaande query uit om een partion schemeaan te maken met de eerder aangemaakte filegroups. 
    CREATE PARTITION SCHEME pfTablename AS PARTITION pfTablename TO (Filegroup1, Filegroup2, Filegroup3)
  6. Koppel het partition scheme aan de kolom in de tabel m.b.v. de table designer (Regular Data Space Specification).