查看当前数据库中存在的临时表表名称命令
select name
from tempdb.dbo.sysobjects
where type='u' and name like '#%'
--查询实际表表名称
select name
from syscolumns
where id=
(
select max(id)
from sysobjects
where xtype='u' and name='表名'
)
跨库访问sql语句
方法-、
exec sp_addlinkedserver '服务器名称','','SQLOLEDB','ip地址'
exec sp_addlinkedsrvlogin '服务器名称','false',null,'sa','数据库密码'
方法二:
select *
from PlatformOneServer.dbo.PlatformOneDB.TableUser
Union ALL
select *
from PlatformTwoServer.dbo.PlatformTwoDB.TableUser
方法三、
select tT_Mobile
from OPENDATASOURCE('SQLOLEDB','Data Source=ip;User ID=用户名;Password=密码').数据库名.dbo.表名
where 字段A = 1081
and not exists
(
select sS_Mobile
from smg_ServerBook
where sS_Mobile = tT_Mobile
)
创建主键
alter table wyf_test201006 add constraint PK_wyf_test201006_ID primary key CLUSTERED (ID)
创建check约束
alter table wyf_test201007 add constraint CK_ID_wyf_test201006 check(ID between 5001 and 10000)
alter table wyf_test201007 add constraint DF_Storage_M_Shelf_Default default(0) for ID
alter table wyf_test201007 add constraint FK_Goods_Pr_GID_Storage_Go_GID foreign key(GID) references Storage_Goods(GID)
查询数据库的所有默认值
select *
from sys.default_constraints
/*删除游标*/
deallocate result_cur
sql服务重启命令
net stop mssqlserver
net start mssqlserver
查找现有表的约束名称
exec sp_helpconstraint 表名
计算列删除与添加
Alter table Table1 drop column ColumnC
Alter table Table1 add ColumnC as ColumnA+ColumnB
delete 中使用别名,其实delete时,真正起作用的是from后面的表
delete a
from #ta a
where exists (select 1 from #tb b where a.id=b.id)
sqlserver排名函数
全年段排名
RANK() OVER(ORDER BY sumPoint DESC)
按班级排名
RANK() OVER(PARTITION BY classId ORDER BY sumPoint DESC)
A. 重命名表
下例将表 customers 重命名为 custs。
EXEC sp_rename 'customers ', 'custs '
B. 重命名列
下例将表 customers 中的列 contact title 重命名为 title。
sp_rename 'HSL_TestPassStatistics.[aa]', 'bb'
在sql语句中使用sum, min,max,avg聚合函数,会自动过滤null的数据行,不会纳入统计
sql触发器查看与管理
1、通过可视化操作来管理和查看触发器
在Microsoft SQL Server Management Studio中,选中某一数据库的某一张表时,在“对象资源管理器详细”窗口中有“触发器”项。
通过“触发器”的右键菜单功能,我们可以新建触发器。如果原来的表中已经存在了触发器,通过双击“触发器”项可以查看到具体的触发器,在此处可以执行 修改、删除等操作。
2、通过查询分析器来管理和查看触发器
1)查看表中的触发器类型:
sp_helptrigger:返回对当前数据库的指定表定义的 DML 触发器的类型。sp_helptrigger 不能用于 DDL 触发器。
示例:
EXEC sp_helptrigger '表名'
2)查看触发器的有关信息:
sp_help:报告有关数据库对象(sys.sysobjects 兼容视图中列出的所有对象)、用户定义数据类型或某种数据类型的信息。
示例:
EXEC sp_help '触发器名'
3)显示触发器的定义:
sp_helptext:显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。
示例:
EXEC sp_helptext '触发器名'
4)查看当前库中所有的触发器:
查询脚本:
SELECT * FROM Sysobjects WHERE xtype = 'TR'
5)查看当前库中所有的触发器和与之相对应的表:
查询脚本:
SELECT tb2.name AS tableName,tb1.name AS triggerName
FROM Sysobjects tb1
JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id
WHERE tb1.type='TR'
mssql中的pivot 和unPivot行转列关键字使用方法
pivot语法
PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明,请参阅 FROM (Transact-SQL)。
以下是带批注的 PIVOT 语法。
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
pivot使用示例
WITH StudentPointStandardSubjectCountCTE AS(
SELECT StudentId, StandardName, COUNT(*) levelCount
FROM PointStandard B,HSL_Point A
WHERE A.subjectId = B.SubjectId
AND Point >= PointDown and Point <= tP_PointUp
GROUP BY StudentId, StandardName
),
studentPointStandardSubjectCountPivotCTE AS(
SELECT tP_StudentId,tP_Asterisk,
[55001] [excellentCount],
[55002] [favorableCount],
[55004] [passCount],
[55005] [noPassCount],
[55006] [qualifedCount],
[55007] [noQualifedCount]
FROM
(
SELECT StudentId,levelCount,StandardName
FROM StudentPointStandardSubjectCountCTE
)AS AA
PIVOT
(
sum([levelCount])
FOR [StandardName]
IN([55001],[55002],[55004],[55005],[55006],[55007])
)AS TableStudentPointStandardSubjectCountPivot
)
select *
FROM studentPointStandardSubjectCountPivotCTE;
unpivot使用示例,引用自msdn上面的例子
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
group by 结合grouping 用法
1、如果希望再在分类统计中,添加汇总行,可以使用以下语句:
Select CategoryID, SUM(UnitPrice), GROUPING(CategoryID) AS 'Grouping'
FROM dbo.Products
GROUP BY CategoryID
WITH ROLLUPGrouping
这一列用于标识出哪一行是汇总行。它使用 ROLLUP 操作添加汇总行。
2、如果使用 WITH CUBE 将会产生一个多维分类数据集,如下:
Select CategoryID, SupplierID, SUM(UnitPrice) AS SumPrice
FROM dbo.Products
GROUP BY CategoryID, SupplierID
WITH CUBE
它会产生一个交叉表,产生所有可能的组合汇总。
3、使用 ROLLUP CUBE 会产生一个 NULL 空值,可以使用以下语法解决,如下:
Select CASE WHEN (GROUPING(SupplierID) = 1) THEN '-1' ELSE SupplierID END AS SupplierID, SUM(UnitPrice) AS QtySum
FROM dbo.Products
GROUP BY SupplierID
WITH CUBE
它首先检查当前行是否为汇总行,如果是就可以设置一个值,这里设置为 '-1' 。
4、使用grouping 进行总计、合计、小计的sql写法示例
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
sql临时表生命周期
1、局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。
2、全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。
3、不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。
在sql server 2008上查询缓存点击率
SELECT cntr_value
FROM sys.dm_os_performance_counters
where counter_name = 'Buffer cache hit ratio'
bcp命令
触发器禁用
/* 触发器固然能禁用触发器,但不能区分用户 */
ALTER TABLE 表名 DISABLE TRIGGER triggerName
查看数据表索引
第一种方式
select * from sysindexes where id = object_id('tablename')
select * from sysindexes where indid>=1 and indid<>255 and name not like '_WA_Sys_%' AND id=OBJECT_ID(表)
第二种
sp_helpindex HSL_SumPoint
len()表示字符长度
datalength()字节长度
SQLSERVER分区函数示例
create PARTITION FUNCTION [PartionFunction](int)
AS RANGE LEFT FOR VALUES ( 1,1111,2111)
go
CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO
([db1_fg_00], [db1_fg_00], [db1_fg_00], [PRIMARY])
CREATE TABLE tb1 (id INT PRIMARY KEY, NAME NVARCHAR(1000)) ON [PartionStruct](id)
-- 删除分区方案和分区函数
DROP PARTITION SCHEME HitDateRangeScheme
DROP PARTITION FUNCTION HitDateRange
openRowSet () 竟然不支持参数传递文件路径,只能采用动态sql执行
查看当前数据库的数据文件和日志文件
sp_helpfile
mssqlServer性能查看工具
select * from Sys.dm_exec_requests
查看当前数据库正在执行和等待执行的sql语句
select * from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(er.sql_handle)
解决代理计划中作业无法删除问题
1. 在msdb中先执行下面的语句将维护计划的ID查出。
select * from sysmaintplan_plans
2. 将查出的ID填入到下面几句话中的''中,并执行。
delete from sysmaintplan_log where plan_id = ''
delete from sysmaintplan_subplans where plan_id = ''
delete from sysmaintplan_plans where id = ''
3. 在Sql Agent中删除相应的Job(维护计划会自动建立相应的Job).
DBCC CHECKDB(N'databaseName') 检查数据库完整一致性
dbcc updateusage(0) : 报告目录视图中的页数和行数错误并进行更正
4、查看sqlserver数据库日志大小和占用空间
DBCC SQLPERF(LOGSPACE)
日志应该采用线性增长方式,最好是预估未来的日志大小,直接设置空间大小
恢复模式:将恢复模式设定为True意味着让SQL自动截去tempdb的日志文件(在使用了每个表格之后),要找出tempdb所使用的恢复模式,可以使用如下命令:
SELECT DATABASEPROPERTYEX('tempdb','recovery')
恢复模式有三种选择:简单、完整或大量记录(bulk-logged),如要改变设置,可以使用以下命令:
ALTER DATABASE tempdb SET RECOVERY SIMPLE
查询数据库当前最耗资源的10个查询语句
SELECT TOP 10
total_worker_time/execution_count AS [Avg CPU Time],
(
SELECT
SUBSTRING(text,statement_start_offset/2,
(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
sqlserver2008的内存对象
SELECT SUM (pages_allocated_count * page_size_in_bytes) as 'Bytes Used', type
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 1 DESC;
GO
sqlserver2008查看当前访问数据库连接数
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT
[DBID]
FROM
[Master].[dbo].[SYSDATABASES]
WHERE
NAME='databaseName'
)
select *
from sysprocesses
where dbid in (select dbid from sysdatabases where name='databaseName')
在sqlserver中插入或update可以直接output值很神奇,做一下记录
DECLARE @table table(keyvalue int)
UPDATE TableMaxId
SET tT_MaxId = tT_MaxId + 1
OUTPUT inserted.tT_MaxId
into @table(keyvalue)
WHERE tT_Name='HSL_ClassType'