数据库的常用命令

1）在数据库中建立新列

ALTER TABLE dbo.DataDictionary ADD IsDefaultSelect  BIT     

1：IsDefaultSelect 加的字段名称

2：BIT 该字段的类型

数据类型有：

2）更改字段名字

EXEC sp_rename 'dbo.DataDictionary.[GroupNO]', 'GroupNo', 'column' 

3）数据库建表

CREATE TABLE LimitSetting

(

 [Id] [int] IDENTITY(1,1) NOT NULL,

 [UpdateDate] [datetime] NULL,

 [Remark] [varchar](100) NULL,

 [IsValid] [bit] NULL,

 [AddDate] [datetime] NULL,

 )

4）开启时间和IO

set statistics io on

set statistics time on;

5）查看索引碎片

Declare @db SysName;

Set @db = 'dbo.DataDictionary';

 

SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'tableName',

 CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type',

 I.Name As 'Index Name',

 avg_fragmentation_in_percent As 'Avg % Fragmentation',

 record_count As 'RecordCount',

 page_count As 'Pages Allocated',

 avg_page_space_used_in_percent As 'Avg % Page Space Used'

FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,'DETAILED' ) S

LEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID)

AND S.INDEX_ID > 0

ORDER BY avg_fragmentation_in_percent DESC

 

6）关于索引

 下面语句可以重建指定表的所有索引:

ALTER INDEX ALL ON <Table Name> REBUILD;

    下面语句可以重建指定索引:

ALTER INDEX <Index Name> ON <Table Name> REBUILD;

    当然，我们也可以整理索引，下面语句整理指定表上的所有索引:

ALTER INDEX ALL ON <Table Name> REORGANIZE;

    下面语句指定特定的索引进行整理:

ALTER INDEX <Index Name> ON <Table Name> REORGANIZE;

在重建或整理完索引之后，重新运行上面的语句来查看索引碎片的情况。