MS SQL Server R2 General operations

List all files used by the current database

Transfer data from a file in the current filegroup to another file in the same filegroup

DBCC shrinkfile (' file ', emptyfile);

Remove a file from a filegroup

Remove filegroups

Add a file group

ALTER DATABASE [DB] add filegroup Group1704

To add a file to a filegroup

ALTER DATABASE [db] Add File   (    name=db_170103,    filename= ' E:\DB\db_170113.ndf ',    SIZE=3MB,    FILEGROWTH=20MB), (    name=db_170104,    filename= ' E:\DBdb_170114.ndf ',    size=3mb,    FILEGROWTH=20MB) to Filegroup Group1701

Rename file name (logical), physical name is not renamed

ALTER DATABASE [DB] Modify file (name= ' db_170103 ', newname= ' db_01 ')

create partition function (time)

N ' 2017-08-01t00:00:00.000 ')

create partition scheme (time)

[Group1704], [Group1705], [Group1706], [Group1707], [Group1708], [PRIMARY])

create partition function (date)

(n ' 2016-08-01t00:00:00.000 ', n ' 2016-09-01t00:00:00.000 ', n ' 2016-10-01t00:00:00.000 ', n ' 2016-11-01t00:00:00.000 ',

N ' 2017-04-01t00:00:00.000 ', n ' 2017-05-01t00:00:00.000 ', n ' 2017-06-01t00:00:00.000 ', n ' 2017-07-01t00:00:00.000 ',
N ' 2017-08-01t00:00:00.000 ')

Create a partition scheme (date)

[Group1704], [Group1705], [Group1706], [Group1707], [Group1708], [PRIMARY])

create partition function (GUID | uniqueidentifier)

' 00000000-0000-0000-0000-e8b900000000 ')

Create PARTITION scheme (GUID | uniqueidentifier)

[Group1702], [Group1703], [Group1704], [Group1705], [PRIMARY])

Adding partitions to existing partition functions and partition schemes

Alter PARTITION scheme Ak_dutysource_old_fung next used group1702goalter partition function ak_dutysource_old_partition _fun () split range (N ' 2017-02-01t00:00:00.000 ')

Merge partition functions

Alter PARTITION function Ak_dutysource_partition_fun () Merge range (N ' 2016-08-01t00:00:00 ')

Delete a table partition (converting a partitioned table to a normal table), mainly by deleting the index of the partition app

CREATE CLUSTERED INDEX ix_ak_dutysource_2 on Ak_dutysource (chktime desc)  

Deleting a partition scheme

Drop partition scheme [Ak_dutysource_partition_fang]

Delete Partition function

Drop partition function [Ak_dutysource_partition_fun]

Apply Table partition (delete previous focus index, re-create focus index)

BEGIN transactionalter TABLE [dbo]. [Ak_account_pointrecords] DROP CONSTRAINT [Pk_ak_account_pointrecords]alter TABLE [dbo]. [Ak_account_pointrecords] ADD  CONSTRAINT [pk_ak_account_pointrecords] PRIMARY KEY nonclustered ([GUID] ASC) with (Pad_index = OFF, Statistics_ NoRecompute = off, sort_in_tempdb = off, Ignore_dup_key = off, ONLINE = off, Allow_row_locks = on, allow_page_locks = ON) On [Primary]create CLUSTERED INDEX [clusteredindex_on_ak_split_guid_fung_636172205823253839] on [dbo]. [Ak_account_pointrecords] ([Userguid]) With (sort_in_tempdb = off, drop_existing = off, ONLINE = off) on [Ak_split_guid_fung] ([Userguid]) DROP INDEX [Clusteredind ex_on_ak_split_guid_fung_636172205823253839] on [dbo]. [Ak_account_pointrecords]

MS SQL Server R2 General operations