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