1.连接远程服务器 OPENROWSET
USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
2.往自动编号的列中插入数据
SET IDENTITY_INSERT t ON
INSERT t(id,dd) VALUES(10,1)
SET IDENTITY_INSERT t OFF
INSERT t(id,dd) VALUES(10,1)
SET IDENTITY_INSERT t OFF
3.影响的行数
set rowcount 2
select * from t
select @@rowcount
select * from t
select @@rowcount
4.自己创建的存储过程
select * from sysobjects where xtype = 'p' and status > 0
系统过程
select * from sysobjects where xtype = 'p' and status < 0
5.SQL Server自带的MD5加密和比较函数
declare @pwd varchar(50)
declare @en_pwd varbinary(256) --'加密后的数据
select @pwd='aaa'
select @en_pwd=pwdencrypt('aaa') --'加密
print @en_pwd
select pwdcompare('aaa',@en_pwd) --'比较密码是否符合，符合返回1，不符合返回0
declare @en_pwd varbinary(256) --'加密后的数据
select @pwd='aaa'
select @en_pwd=pwdencrypt('aaa') --'加密
print @en_pwd
select pwdcompare('aaa',@en_pwd) --'比较密码是否符合，符合返回1，不符合返回0
6.SQL保留两位小数
select cast(cast(10 as float)/3 as decimal(10,2))