How to add more partitions to existing table

 --STEP 1 : Find File group

SELECT name AS AvailableFilegroups
FROM sys.filegroups WHERE type = 'FG'

--AvailableFilegroups
-------------------
--PRIMARY


--- STEP 2 : Create Partition Range Function & Schema

SELECT * FROM sys.partition_functions WHERE [name] = N'PF_CREATED_DATE'  
SELECT * FROM sys.partition_schemes   WHERE [name] = N'PS_PURCHASE_ORDER' 
  

CREATE PARTITION FUNCTION [PF_CREATED_DATE](Date) 
AS RANGE RIGHT FOR VALUES (
N'2019-08-01',
N'2019-08-02',
N'2019-08-03',
N'2019-08-04',
N'2019-08-05',
N'2019-08-06',
N'2019-08-07',
N'2019-08-08',
N'2019-08-09',
N'2019-08-10',
N'2019-08-11',
N'2019-08-12',
N'2019-08-13',
N'2019-08-14',
N'2019-08-15')

CREATE PARTITION SCHEME [PS_PURCHASE_ORDER] AS PARTITION [PF_CREATED_DATE]  TO  ( 
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ) 


--- STEP 3 : Create TABLE & Index

 SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PURCHASE_ORDER](
[PURCHASE_ORDER_ID] [bigint] NOT NULL,
[CREATED_DATE] [date]  NOT NULL,
[LOAD_ID] [int] NOT NULL
) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


CREATE CLUSTERED INDEX [CI_CREATED_DATE] ON [dbo].[PURCHASE_ORDER]
(
[CREATED_DATE] ASC,
[PURCHASE_ORDER_ID] 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 [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


--- STEP 4 : Add new partitions

 -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-16')

  -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-17')

How do i change table schema to dbo

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName

Example : 1
ALTER SCHEMA dbo TRANSFER Archive.TableName


Example : 2
change schema from dbo to rpt

Source table : dbo.rpt.TableName ( Invalid table name with additional dot)

Step 1 : Rename table with _ under score.
                   dbo.rpt_TableName

Step 2 : Change schema to rpt
ALTER SCHEMA rpt TRANSFER dbo.rpt_TableName

    Now table name with fully qualified name    rpt.rpt_TableName

Step 3 : Rename table with correct name ( to remove rpt_ )
                      rpt.TableName

My Parents

This blog dedicated to my parents. Thanks for your love, blessings and support in my life. You are inspired me to share my knowledge to the world especially for SQL Server DBA fellow members.

C. SANGARAPPAN  (Father)

Born: 05-June-1929  Died : 24-November-2017 at 9:40 pm in India

&

S. MEENAKSHI (Mother)

Died :  02-June-2020 at 3:40 pm in India

SQL Server 2017 on Linux Roadshow

Get exclusive insights on data from Microsoft leadership, Red Hat, Attunity, Intel, and HPE at the SQL Server 2017 on Linux Roadshow. Sign up now: http://msft.social/AHiLgv

Register with Microsoft to find  data and AI for your company
https://www.facebook.com/sqlserver/ 

Upgrades from Earlier Versions to SQL Server 2016

You can upgrade to SQL Server 2016 supports upgrade from the following versions of SQL Server:
•SQL Server 2008 SP3 or later
•SQL Server 2008 R2 SP2 or later
•SQL Server 2012 SP2 or later
•SQL Server 2014 or later

How to Import SSIS Package into SQL Server

1. Connect Integration services using SSMS (SQL Server Management Studio)

2. Select Package Location from “File System”

3 DerivedColumn Package imported into MSDB( where the package is stored)

4. Execute Package Utiltiy to DerivedColumn Package to test to export data into Excel file D:\Temp\DerivedColumn.xls

5. Create a Job to run the Package at scheduled time

6. SSIS DerivedColumn job has been successfully executed and output on excel file location D:\Temp\DerivedColumn.xls
 

SQL Server 2016 coming on 1st June

This new version will be released on June 1st 2016.
SQL Server 2016 is the foundation of Microsoft’s data strategy, encompassing innovations that transform data into intelligent action. With this new release, Microsoft is delivering an end-to-end data management and business analytics solution with mission critical intelligence for your most demanding applications as well as insights on your data on any device.