SQLServer常用语句

1.提高模糊查询速度的方法
前提是字段要加全文索引
select COUNT(id) from k_companyinfo where 1=1 and (freetext(fcompanyname,'长沙') or freetext(fcontact,'长沙') or freetext(fmobile,'长沙'))

2.搜索出来的结果用逗号串联：

select stuff((select ',' + convert(varchar(20),[id]) from user desc for xml path('')), 1, 1, '')

3.获取当天数据
select * from info where DateDiff(dd,datetime,getdate())=0 

4.获取表字段的说明
select a.name,CAST(g.value AS nvarchar) from sys.columns a left join sys.extended_properties g on (a.object_id = g.major_id AND a.column_id=g.minor_id) where object_id=OBJECT_ID('user') order by object_id,a.column_id

5.获取某天数据

当天：DateDiff(dd,CreateDate,getdate())=0
昨天：DateDiff(dd,CreateDate,getdate())=1
本周：select * from table where datediff(week,C_CALLTIME,getdate())=0     --C_CALLTIME 为日期字段
本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0   --C_CALLTIME 为日期字段
本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0 
前半年1-6，后半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 = datepart(mm,getdate())/7

6.快速查询表数据条数（适合大数据）

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('dbo.crm_firm_data') AND indid < 2

7.如果数据库字段为ntext，可以通过以下语句修改某字段（context）的值

update m_book_spu set context=replace(Cast(context as varchar(8000)),'http://8.141.51.86/api/profile/upload/','http://jinghongfeishugc.qiyedaishu.com/upload/img-two/');

8.获取表所占空间大小

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    UsedSpaceMB desc,
    t.Name

9.数据库连接字符串

<add name="ConnStr" connectionString="Data Source=127.0.0.1;Initial Catalog=qiyedaishu3.0;User ID=sa;Password=Server2012R2!@#$%;MultipleActiveResultSets=true;TrustServerCertificate=True;Pooling=True;Max Pool Size=1000;Min Pool Size=0;Connect Timeout=15;" />

10.数据库如果一直显示【正在还原】，执行下列语句即可：

RESTORE database [ZongHeZhiFa2.0_ErQi] with recovery