MS SQL vertical partitioning

Asked by At

I want to implement vertical partitioning in Microsoft sql server and tried following steps,

Step 1

ALTER DATABASE [mydatabase] ADD FILEGROUP DATA_1

Step 2

ALTER DATABASE [mydatabase]
ADD FILE ( NAME = [fileData-1], FILENAME = 'D:\xxx\filedata.ndf')
TO FILEGROUP DATA_1

Step 3

CREATE TABLE [dbo].[partitionedtable1](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [value1] [nvarchar](max) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[partitionedtable2](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [value1] [nvarchar](max) NOT NULL
) ON [DATA_1]

when i check above table in sys.partitions table still it shows partition_number as 1

further i can do above using if i needed partition based on textimage.

CREATE TABLE [dbo].[partitionedtable1](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [value1] [nvarchar](max) NOT NULL,
      [value2] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [DATA_1]

Is it possible to have one single table for below with partitioned columns in two data files

CREATE TABLE [dbo].[partitionedtable1](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [value1] int NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[partitionedtable2](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [value2] int (max) NOT NULL
) ON [DATA_1]

0 Answers