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 TABLE3、SQL中cross join,left join,right join ,full join,inner join 的区别
[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]
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 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:
Rows --> varchar:
select @s = isnull ( @s , '' ) + ' Kill ' + rtrim (spID) from master..sysprocesses where dbid = db_id ( ' ASGDM_Executor_Test ' )
Get Execute As:
DB Split:
alter database ASGDM_Executor set trustworthy on
ALTER AUTHORIZATION ON DATABASE :: [ ASGDM_Executor ] TO sa
Grant Permission:
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:
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:
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):
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的主要用途:
-- >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条记录)
结果一目了然,不用多解释!