sqlserver语句总结

动态查询
declare @sql = select * from table;
exec(@sql)

统计信息会在每个新创建的索引中自动创建统计信息。
如果数据库中AUTO_CREATE_STATISTICS被设置为ON，SQLServer将会自动对查询中用到的，且没有索引的列自动创建统计信息。
--Create statistics on all rows

CREATE STATISTICSstatistics_name   ONYourDBName.YourSchema.YourTable(YourColumn1,YourColumn2)  
WITH FULLSCAN

 --Create statistics using a random 10 percent sampling rate

CREATE STATISTICSstatistics_name   ONYourDBName.YourSchema.YourTable(YourColumn1,YourColumn2)   
WITH SAMPLE 10PERCENT
sp_updatestats存储过程来实现
1.聚集索引 ：在聚集索引中，表中各行的物理顺序与键值的逻辑顺序相同。一个表中只可以有一个聚集索引。  如果表中有聚集索引，则该表为聚集表，如果没有则为堆的无序结构表。

2.非聚集索引：具有独立数据行的结构。包含非聚集索引键值，并且每个索引键值都有指向包含键值的数据行的指针。

select OBJECT_NAME(ix.object_id),ix.index_id,ix.name 
from sys.indexes as ix
where ix.object_id = object_id('test');
sys.indexes
index_id =0:堆--表没有聚集索引，会自动添加类型为0的索引，一开始应该是堆表。加了聚集索引了，就成聚集索引表。
index_id =1:聚集索引
index_id =2.....:非聚集索引
      
sp_helpindexs 'table_name'
exec sp_helpindex test
select * from sys.indexes where name='Clus_uniq_index_id'
--表对应的所有索引
select o.name,i.name 
from sys.objects o 
join sys.indexes i
on o.object_id=i.object_id 
where o.name='tb'
查看统计信息
select * from sys.stats s   
join  sys.objects  o                                                                                   
on s.object_id=o.object_id
where o.name='tb'
————————————————
结论：重组索引（Reorganize Index）不会触发对应索引的统计信息更新. 也不会触发其它统计信息更新。也就说，重组索引（Reorganize Index）不会触发任何统计信息更新。
结论：重建索引（Rebuild Index）会触发对应索引的统计信息更新。但是，重建索引（Rebuild Index）不会触发其它统计信息更新。
堆表并不是B树结构。
dbcc show_statistics('db.dba.table',index_phone)
--列出表中的所有统计信息
select * from sys.stats where object_id=OBJECT_ID(N'[Sales].[SalesOrderDetail]')
--查看统计信息及其列
SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c  ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]');
 
--查看所有统计信息更新时间
exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL'
————————————————
还可以使用命令DBCC SHOW_STATISTICS查看，以下为列。
DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','IX_SalesOrderDetail_ProductID')

sp_helptext sp_name
select * from sys.sql_modules
SELECT OBJECT_DEFINITION( OBJECT_ID(‘CountProc’));

exec sp_help 'view'
exec sp_helptext 'view'

--数据库修复：
--查看错误信息
dbcc checkdb;
DBCC CHECKDB WITH NO_INFOMSGS;
--数据库修复
EXEC sp_dboption 'pg', 'single user', 'TRUE'  
dbcc checkdb ('pg',REPAIR_REBUILD)           -------执行不会丢失数据的修复
dbcc checkdb（'pg',repair_allow_data_loss）  -------修复数据库
EXEC sp_dboption 'pg', 'single user','FALSE'

--表修复
EXEC sp_dboption 'pg', 'single user', 'TRUE'  
DBCC CHECKTABLE(tablename)--检查
DBCC CHECKTABLE(IDCODE,REPAIR_REBUILD);--修复
DBCC CHECKTABLE(IDCODE,REPAIR_ALLOW_DATA_LOSS);--尝试修复报告的所有错误。 这些修复可能会导致一些数据丢失
EXEC sp_dboption 'pg', 'single user','FALSE'

set showplan_all on|off --on ,执行信息，但不执行  -off 执行语句
sys.dm_db_index_physical_stats
sp_helpstats PRIMARYCODE
DBCC SHOW_STATISTICS(PRIMARYCODE,FWMAPPLYCODE)--查看统计信息
DBCC SHOWCONTIG (PRIMARYCODE,IDX_PRIMARYCODE_PRIMARYCODE)
SQLServer提供了一个数据库命令——DBCC SHOWCONTIG——来确定一个指定的表或索引是否有碎片。 
示例：
显示数据库里所有索引的碎片信息
DBCC SHOWCONTIG WITH ALL_INDEXES

显示指定表的所有索引的碎片信息
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES

显示指定索引的碎片信息
DBCC SHOWCONTIG (authors,aunmind)

解决碎片问题 ：
1. 删除并重建索引 
2. 使用DROP_EXISTING子句重建索引 
3. 执行DBCC DBREINDEX 
4. 执行DBCC INDEXDEFRAG 

set statistics io on|off
DBCC SHOW STATISTICS(学生表备份， CLID X学生表备份身份)
 UPDATE STATISTICS 学生表     --更新学生表的所有索引的统计GO
 DBCC SHOWCONTING(学生表_备份)
 重新和整理索引
（1）DROP INDEX 和CREATE INDEX
（2）DBCC DBREINDEX在一次操作里重建一个表上的所有索引，但重建索引时表不可用
（3）DBCC INDEXDEFRAG删除索引碎片，提高索引扫描性能
格式;
DBCC INDEXDEFRAG (数据库名，表名|视图名，索引名)
我们来看看非聚集索引的直方图信息。

1 DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', 'ix_productid') WITH HISTOGRAM

Sql Server 查找记录的方法

【Table Scan】：遍历整个表
【Index Scan】：根据索引
【Index Seek】：根据索引，定位（获取）记录的存放位置
【Clustered Index Scan】
【Clustered Index Seek】：直接根据聚集索引获取记录，最快！

Sql Server Join 方式
Nested Loops join】，如果一个联接输入很小，而另一个联接输入很大而且已在其联接列上创建了索引
【Merge Join】，如果两个联接输入并不小但已在二者联接列上排序（
【Hash Join】，哈希联接可以有效处理未排序的大型非索引输入

查看 SQL Server 的指定数据库索引碎片百分比 (SQL)
 ---外部碎片导致磁盘上的索引页面不连续,新的叶子页面和原始叶子页面离的很远,物理顺序和逻辑顺序不同.  
  
select b.name,c.name as indexname, a.index_type_desc,a.index_depth,a.avg_fragmentation_in_percent,  
  a.avg_page_space_used_in_percent,a.fragment_count ,a.avg_fragment_size_in_pages,a.page_count   
  from sys.dm_db_index_physical_stats(DB_ID(N'MYTEST'),NULL,NULL,NULL,'DETAILED') as a  
  inner join sys.objects as b  
  on a.object_id =b.object_id   
  inner join sys.indexes as c on a.object_id=c.object_id and a.index_id=c.index_id  
order by a.avg_fragmentation_in_percent desc,a.object_id asc  
  
  
--如果 avg_fragmentation_in_percent 的%比大于30（可以自定义%比值） 则  
ALTER INDEX INDEX_NAME ON TABLE_NAME REBUILD WITH(ONLINE=ON) ---联机不锁定表来重新创建索引，以减小索引碎片  
--如果小于30 则  
ALTER INDEX INDEX_NAME ON TABLE_NAME REORGANIZE --脱机重新创建索引，以减小索引碎片  
--避免重新创建聚簇索引时表上的非聚簇索引重建两次  
CREATE CLUSTERED INDEX INDEX_NAME ON TABLE_NAME(COLUMN1) WITH(DROP_EXISTING=ON) --该方法有可能引起阻塞和索引的消失。 
--显示指定的表或视图的数据和索引的碎片信息  
DBCC SHOWCONTIG  
DBCC SHOWCONTIG   
[ (   
    { table_name | table_id | view_name | view_id }   
    [ , index_name | index_id ]   
) ]   
    [ WITH   
        {   
         [ , [ ALL_INDEXES ] ]   
         [ , [ TABLERESULTS ] ]   
         [ , [ FAST ] ]  
         [ , [ ALL_LEVELS ] ]   
         [ NO_INFOMSGS ]  
         }  
    ]  
  
--table_name | table_id | view_name | view_id  
要检查碎片信息的表或视图。如果未指定，则检查当前数据库中的所有表和索引视图。若要获得表或视图 ID，请使用 OBJECT_ID 函数。  
  
index_name | index_id  
要检查碎片信息的索引。如果未指定，则该语句将处理指定表或视图的基本索引。若要获取索引 ID，请使用 sys.indexes 目录视图。  
--SQLServer批量重建索引
dbcc dbreindex ([customer],'',90)
第一个参数是要重建索引的表名，第二个参数指定索引名称，空着就表示所有，
第三个参数叫填充因子，是指索引页的数据填充程度，0表示使用先前的值，100表示每个索引页都填满，这时查询效率最高，但插入索引时会移动其它索引，可根据实际情况来设置。
这个命令只能操作单个表，如果想对某数据库中的所有表都重建索引，则需要写个程序：

[sql] view plain copy
USE MyDB;  
DECLARE @name varchar(100)  
DECLARE authors_cursor CURSOR FOR    
Select [name]   from sysobjects where xtype='u' order by id  
OPEN authors_cursor  
FETCH NEXT FROM authors_cursor  INTO @name  
WHILE @@FETCH_STATUS = 0   
BEGIN
   DBCC DBREINDEX (@name, '', 90)  
   FETCH NEXT FROM authors_cursor    
   INTO @name   
END 
close authors_cursor
deallocate authors_cursor

--获取表名
SELECT OBJECT_NAME(ID)
--获取所有表 
select s.*
from sysobjects
where s.xtype='u'

--系统其他函数
suser_name() 用户登录名
user_name() 用户在数据库中的名字
user 用户在数据库中的名字
show_role() 对当前用户起作用的规则
db_name() 数据库名
object_name(obj_id) 数据库对象名
col_name(obj_id,col_id) 列名
col_length(objname,colname) 列长度
valid_name(char_expr) 是否是有效标识符

一、字符串函数
datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
substring(expression,start,length) 不多说了,取子串
right(char_expr,int_expr) 返回字符串右边int_expr个字符
left(<character_expression>， <integer_expression>)
    返回character_expression 左起 integer_expression 个字符。
ltrim() 把字符串头部的空格去掉。
rtrim() 把字符串尾部的空格去掉。

str (<float_expression>[，length[， <decimal>]]) 把数值型数据转换为字符型数据
    length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。
    当length 或者decimal 为负值时，返回NULL;
    当length 小于小数点左边（包括符号位）的位数时，返回length 个*;
    先服从length ，再取decimal ;
    当返回的字符串位数小于length ，左边补足空格。
upper(char_expr) 转为大写
lower(char_expr) 转为小写
space(int_expr) 生成int_expr个空格
replicate(char_expr,int_expr)复制字符串int_expr次
reverse(char_expr) 反转字符串
stuff(char_expr1,start,length,char_expr2) 将字符串char_expr1中的从
        start开始的length个字符用char_expr2代替
ltrim(char_expr) rtrim(char_expr) 取掉空格
ascii(char) char(ascii) 两函数对应,取ascii码,根据ascii码取字符
char() 将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。
charindex(char_expr,expression) 返回char_expr的起始位置
patindex("%pattern%",expression) 返回指定模式的起始位置,否则为0

--系统函数
newid() isnumeric (任意表达式)  isnull (任意表达式1，任意表达式2)  isdate (任意表达式)

二、数学函数
trunc(45.923，1) 按指定精度截断十进制数 结果：45.9 此为oracle函数
mod(1600,300) 求除法余数 结果：100
abs(numeric_expr) 求绝对值
ceiling(numeric_expr) 取大于等于指定值的最小整数
avg（numeric_expr）取平均数
exp(float_expr) 取指数
floor(numeric_expr) 小于等于指定值得最大整数
pi() 3.1415926.........
power(numeric_expr,power) 返回power次方
rand([int_expr]) 随机数产生器
round(numeric_expr,int_expr) 安int_expr规定的精度四舍五入
sign(int_expr) 根据正数,0,负数,,返回+1,0,-1
sqrt(float_expr) 平方根

三、日期时间函数
getdate() 返回日期
getutcdate()  --获取utc时间
day(getdate())  --取出天
month(getdate())  --取出月
year(getdate())  --取出年
datename(datepart,date_expr) 返回名称如 June
datepart(datepart,date_expr) 取日期一部份
datediff(datepart,date_expr1.dateexpr2) 日期差
dateadd(datepart,number,date_expr) 返回日期加上 number

上述函数中datepart的写法取值和意义：

yy 1753-9999 年份
qq 1-4 刻
mm 1-12 月
dy 1-366 日
dd 1-31 日
wk 1-54 周
dw 1-7 周几
hh 0-23 小时
mi 0-59 分钟
ss 0-59 秒
ms 0-999 毫秒

cast (表达式 AS 数据类型[(长度)])
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177

convert (数据类型[(长度)]，表达式[，样式]) 
Style ID    Style 格式
100 或者 0    mon dd yyyy hh:miAM （或者 PM）
101    mm/dd/yy
102    yy.mm.dd
103    dd/mm/yy
104    dd.mm.yy
105    dd-mm-yy
106    dd mon yy
107    Mon dd, yy
108    hh:mm:ss
109 或者 9    mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110    mm-dd-yy
111    yy/mm/dd
112    yymmdd
113 或者 13    dd mon yyyy hh:mm:ss:mmm(24h)
114    hh:mi:ss:mmm(24h)
120 或者 20    yyyy-mm-dd hh:mi:ss(24h)
121 或者 21    yyyy-mm-dd hh:mi:ss.mmm(24h)
126    yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130    dd mon yyyy hh:mi:ss:mmmAM
131    dd/mm/yy hh:mi:ss:mmmAM

--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'
--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5．指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)

a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)

i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

--IDENTITY修改初始值，是IDENTITY无效
建表
DROP TABLE [dbo].[red_envelope_original]
GO
CREATE TABLE [dbo].[red_envelope_original] (
[id] int NOT NULL IDENTITY(1,1) ,
[qrcode] varchar(64) NULL ,
[creation_time] datetime NULL DEFAULT (getdate()) 
)
修改初始值
DBCC CHECKIDENT(N'[dbo].[red_envelope_original]', RESEED, 24000)
使IDENTITY无效
SET IDENTITY_INSERT [dbo].[red_envelope_original] ON
GO
INSERT INTO [dbo].[red_envelope_original] ([id], [qrcode], [creation_time]) VALUES (N'1', N'8FA215443AA64A9DB1E4213070F72EC8', N'2018-08-20 16:30:21.237')
GO
SET IDENTITY_INSERT [dbo].[red_envelope_original] OFF
GO

--建索引
CREATE CLUSTERED/NONCLUSTERED UNIQUE INDEX [idx_qrcode_d9l2m4n7_copy1] ON [dbo].[red_envelope_original]([qrcode] ASC) 
create unique clustered index id_index
on table_1(id asc)
with 
fillfactor=10 /*填充因子10%*/

create nonclustered  index id_datetimes_index
on table_1(id asc,datetimes asc)
with 
fillfactor=10 /*填充因子10%*/
　因此，一般设置的原则是数据变化较大，填充因子设较小值，而数据变化较小，填充因子设较大值。

--建主键
ALTER TABLE [dbo].[red_envelope_original] ADD PRIMARY KEY ([id])

--在MS Sql Server中可通过以下的方法查询出磁盘空间的使用情况及各数据库数据文件及日志文件的大小及使用利用率:

1、查询各个磁盘分区的剩余空间： 
Exec master.dbo.xp_fixeddrives;

2、查询数据库中的所有数据库名： 
SELECT Name FROM Master..SysDatabases ORDER BY Name;

3、查询某个数据库中所有的表名： 
SELECT name FROM SysObjects Where XType=’U’ ORDER BY Name;

4、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等） 
select * from [数据库名].[dbo].[sysfiles]; 
转换文件大小单位为MB： 
select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles;

5、查询当前数据库的磁盘使用情况： 
Exec sp_spaceused; 
exec sp_spaceused ‘表名’; –取得表占用空間

6、查询数据库服务器各数据库日志文件的大小及利用率 
DBCC SQLPERF(LOGSPACE);

总结：
完整的收缩日志文件的 T-SQL 语句：
BACKUP LOG xxdb to disk='x:\work\1.bak'
DBCC SHRINKFILE (xxdb_log,10)
GO

8、收缩数据库日志文件（sql server2008） 
USE[master] 
GO 
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE WITH NO_WAIT 
GO 
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE –简单模式 
GO 
USE [数据库名] 
GO 
DBCC SHRINKFILE (N’[数据库名]_Log’ , 11, TRUNCATEONLY) 
DBCCSHRINKFILE(N'YHDB_log', 11,TRUNCATEONLY)
GO 
USE[master] 
GO 
ALTER DATABASE [数据库名] SET RECOVERY FULL WITH NO_WAIT 
GO 
ALTER DATABASE [数据库名] SET RECOVERY FULL –还原为完全模式 

什么是系统版本的Temporal Table
系统版本的Temporal Table是可以保存历史

数据库脱机/联机 使数据库不可用，并且可以复制数据库文件
数据库分离/附加 和脱机类似，但是分离后SSMSS上看不到，并且可以复制数据库文件

数据库备份：是联机状态下进行
数据库备份；是一个数据库的全备份
数据库文件备份：是一个数据库的某个文件备份

收缩数据库：
收缩数据库
收缩文件