数据库优化整合

1、OR语句可能会破坏索引的作用引发表扫描，可以可以分解成Union语句。例如：
         select a.cola,b.colb from taba a inner join tabb b on a.xxx=b.sss or a.ttt=b.yyy
      可以转化为
         select a.cola,b.colb from taba a inner join tabb b on a.ttt=b.yyy
               union all
         select a.cola,b.colb from taba a inner join tabb b on a.ttt=b.yyy
      不过具体情形还是要看执行计划。
2、TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

TRUNCATE TABLE 

    [ { database_name.[ schema_name ]. | schema_name . } ]

    table_name

[ ; ]

3、SQL中cross join，left join，right join ，full join，inner join 的区别

cross join 是笛卡儿乘积就是一张表的行数乘以另一张表的行数
left join 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
full join 返回两张表中的行 left join+right join
inner join 只返回两张表连接列的匹配项

4、SQL中使用cast转化datetime为varchar时会丢失秒，最好使用convert， CONVERT(data_type,expression[,style])，最后那个style变量就是在转换datetime时使用的。

style数字在转换时间时的含义如下

-------------------------------------------------------------------------------------------------
Style(2位表示年份) | Style(4位表示年份) | 输入输出格式 
-------------------------------------------------------------------------------------------------
- | 0 or 100 | mon dd yyyy hh:miAM(或PM) 
-------------------------------------------------------------------------------------------------
1 | 101 | mm/dd/yy 
-------------------------------------------------------------------------------------------------
2 | 102 | yy-mm-dd 
-------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy 
-------------------------------------------------------------------------------------------------
4 | 104 | dd-mm-yy 
-------------------------------------------------------------------------------------------------
5 | 105 | dd-mm-yy 
-------------------------------------------------------------------------------------------------
6 | 106 | dd mon yy 
-------------------------------------------------------------------------------------------------
7 | 107 | mon dd,yy 
-------------------------------------------------------------------------------------------------
8 | 108 | hh:mm:ss 
-------------------------------------------------------------------------------------------------
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
-------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy 
-------------------------------------------------------------------------------------------------
11 | 111 | yy/mm/dd 
-------------------------------------------------------------------------------------------------
12 | 112 | yymmdd 
-------------------------------------------------------------------------------------------------
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小时制) 
-------------------------------------------------------------------------------------------------
14 | 114 | hh:mi:ss:mmm(24小时制) 
-------------------------------------------------------------------------------------------------
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小时制) 
-------------------------------------------------------------------------------------------------

- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制)

 

列出所有存储过程

SELECT * FROM SysObjects WHERE [xtype] = 'P'

查询存储过程的内容
EXEC Sp_HelpText '存储过程名'

查询空字符串
SELECT SPACE(空字符个数)

 查看某个对象的全部依赖情况

SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE charindex('Object_Name', text) > 0

 

Select 赋值

select @counts=rowsCount from #tempRowCount

SQL引擎会遍历查询返回的数据，每条记录赋一次值，@counts的结果是查询返回的最后一条记录的值。

Delete Top

Delete top (1000) from table

可能的话，将表中所有的列都显示的声明为 NOT NULL，并且为丢失的或未知的项定义默认值。

 

取整函数:

上取整ceiling() 例如:ceiling(4.1) 返回值为5

下取整floor() 例如： floor(4.9)  返回值4

 

 

Get Table Space Status: sp_spaceused @TableName

 

Grant/Revoke

 

Attach DB By Script(you can name for the new DB):

 CREATE DATABASE ASGDMbck ON
( FILENAME = N'G:\Data\ASGDM.mdf' ),
( FILENAME = N'G:\Data\ASGDM_1.ndf'),
( FILENAME = N'G:\Data\ASGDM_2.ldf')
 FOR ATTACH 

 

你不能在进程中看到SQLServer实际占得内存，需要到性能查看器中去看。

 

 

Find out which procedures are set to execute as specific user :

 

select object_name(object_id) from sys.sql_modules where execute_as_principal_id = user_id('alice')

 

Measuring the Run Time of Queries 

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

SET Statistics IO on
set statistics time on

exec spRptPonToolVerticalData_Adc2 
@VerticalKey='194',
@Sites='bing.com',
@StartDate='Mar  1 2010 12:00AM',
@EndDate='May 30 2010 12:00AM',
@DateType='D',
@Alias='admin',
@ActionEntry='Ribbon'

set statistics time off
SET Statistics IO off

 查看数据库的状况：sp_who2

 

Kill any sp: kill [spId]. For example: Kill 68

 

Get all sp which are using one DB:

SELECT spid,hostname,loginame,* FROM sysprocesses WHERE DBID=DB_ID('ASGDM_Executor_Test')

Rows --> varchar: 

declare @s varchar(1000)
select @s=isnull(@s,'')+' Kill ' +rtrim(spID) from master..sysprocesses where dbid=db_id('ASGDM_Executor_Test')

 Get Execute As:

select object_name(object_id) from sys.sql_modules where execute_as_principal_id = user_id('CSOAExecutor_bck')

DB Split:

grant impersonate on user::[CSODExecutor] to [REDMOND\Lighthouse Users]
alter database ASGDM_Executor set trustworthy on
ALTER AUTHORIZATION ON DATABASE::[ASGDM_Executor] TO sa

Grant Permission:

USE [ASGDM_EXECUTOR_Dev]
Declare AllSpCursor cursor for
SELECT 'GRANT EXECUTE ON OBJECT::dbo.' + name 
+ ' To [REDMOND\csousers],[REDMOND\Lighthouse Users]' FROM sys.objects WHERE type in (N'P', N'PC')

Open AllSpCursor

declare @grandSentense varchar(max)

fetch next from AllSpCursor into @grandSentense
while(@@fetch_status=0)
begin
    exec (@grandSentense)

    fetch next from AllSpCursor into @grandSentense
end


close AllSpCursor
deallocate AllSpCursor

Declare AllViewCursor cursor for
SELECT 'GRANT SELECT ON OBJECT::dbo.' + name 
+ ' To [REDMOND\csousers],[REDMOND\Lighthouse Users]' FROM sys.objects WHERE type = N'V'

Open AllViewCursor

declare @grandViewSentense varchar(max)

fetch next from AllViewCursor into @grandViewSentense
while(@@fetch_status=0)
begin
    exec (@grandViewSentense)

    fetch next from AllViewCursor into @grandViewSentense
end


close AllViewCursor
deallocate AllViewCursor

User Management:

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO dbo

REVOKE impersonate ON user::[CSODExecutor] TO [REDMOND\Lighthouse Users]

REVOKE [execute] ON user::[csodcccccExecutor] TO [REDMOND\csousers]

select * from sys.database_permissions where grantor_principal_id = USER_ID('csodcccccExecutor')

select * from sys.database_principals

select * from sys.database_permissions where grantor_principal_id = USER_ID('csodcccccExecutor')

select * from sys.schemas

Update:

update dbo.AllDailyAccountUsage  
 set KeywordCount = t2.KeywordCount  
 from dbo.AllDailyAccountUsage t1   
 inner join 
    (select DayId,AccountId,sum(KeywordCount) as KeywordCount from 
    dbo.AllDailyAccountCampaignKeywordsCount (nolock)
    group by DayId,AccountId) t2 
 on t1.DayId = t2.DayId and t1.AccountId = t2.AccountId  
 where t1.KeywordCount is null

Tree(with):

WITH VerticalTree(VerticalId, RootVerticalId)
AS
(
SELECT Vertical_Id, Vertical_Id
FROM dim_vertical(nolock)
UNION ALL
SELECT dim_vertical.Vertical_Id, VerticalTree.RootVerticalId
FROM dim_vertical(nolock)
INNER JOIN VerticalTree
ON dim_vertical.Parent_Vertical_Id = VerticalTree.VerticalId
)

 远程调用存储过程：

--创建链接服务器  
  exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','远程服务器名或ip地址'  
  exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'用户名','密码'  
  exec   sp_serveroption   'srv_lnk','rpc   out','true' --这个允许调用链接服务器上的存储过程  
  go  
   
  --查询示例  
  select   *   from   srv_lnk.数据库名.dbo.表名  
   
  --导入示例  
  select   *   into   表   from   srv_lnk.数据库名.dbo.表名  
   
  go  
  --以后不再使用时删除链接服务器  
  exec   sp_dropserver   'srv_lnk','droplogins'  
   
  --如果只是临时访问,可以直接用openrowset  
  --查询示例  
  select   *   from   openrowset('SQLOLEDB'  
  ,'sql服务器名';'用户名';'密码'  
  ,数据库名.dbo.表名)  
   
  --导入示例  
  select   *   into   表   from   openrowset('SQLOLEDB'  
  ,'sql服务器名';'用户名';'密码'  
  ,数据库名.dbo.表名)   

 

MSSQL escape的主要用途：

-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-22 09:27:36
declare @t table([test] nvarchar(5))
Insert @t
select N'11%22' union all
select N'11%33' union all
select N'12%33' union all
select N'1_%33'
--1
--使用ESCAPE关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。
--如搜索包含'1%'的紀錄
select * from @t where [test] like '%1/%%' escape '/'
/*
test
-----
11%22
11%33
(2 個資料列受到影響)
*/
--2
--允许在字符串中搜索通配符而不是将其作为通配符使用
select * from @t where [test] like '%1/_%' escape '/'
/*
test
-----
1_%33
*/

在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如如果想查找包含”%“的数据需要写成： like %/%% escape '/'

 

WITH TIES

指定从基本结果集中返回额外的行，对于 ORDER BY 列中指定的排序方式参数，这些额外的返回行的该参数值与 TOP n (PERCENT) 行中的最后一行的该参数值相同。只能在 SELECT 语句中且只有在指定了 ORDER BY 子句之后，才能指定 TOP...WITH TIES。

注意：返回的记录关联顺序是任意的。ORDER BY 不影响此规则

来源：MSDN，http://msdn.microsoft.com/zh-cn/library/ms189463.aspx

MSDN中指出这些额外的返回行的参数值与TOP n（PERCENT）行中的最后一行的该参数值相同。这个地方该怎么理解呢？其实是如果按照order by 参数排序TOP n（PERCENT）返回了前面n（pencent）个记录，但是n+1…n+k条记录和排序后的第n条记录的参数值（order by 后面的参数）相同，则n+1、…、n+k也返回。n+1、…、n+k就是额外的返回值。

举个例子，假设有如下记录：

studentID	courseName	score
09212744	数据库	90
09212745	数据库	90
09212746	数据库	90
09212750	数据库	85
09212719	数据库	84
09212720	数据库	80
09212742	数据库	80
09212751	数据库	75
09212755	数据库	74
09212740	数据库	70

select top 6 * from student order by score desc

将返回如下结果：

图1 不带with ties（注意最后一条记录）

select top 6 with ties * from student order by score desc

将返回如下结果：

图2  带with ties（多了第7条记录） 

结果一目了然，不用多解释！