数据开发-经典:
1.按姓氏笔画排序:
Select
*
From
TableName
Order
By
CustomerName
Collate
Chinese_PRC_Stroke_ci_as //从少到多
2.数据库加密:
select
encrypt(
'原始密码'
)
select
pwdencrypt(
'原始密码'
)
select
pwdcompare(
'原始密码'
,
'加密后密码'
) = 1
--相同;否则不相同 encrypt('原始密码')
select
pwdencrypt(
'原始密码'
)
select
pwdcompare(
'原始密码'
,
'加密后密码'
) = 1
--相同;否则不相同
3.取回表中字段:
declare
@list
varchar
(1000),
@sql nvarchar(1000)
select
@list=@list+
','
+b.
name
from
sysobjects a,syscolumns b
where
a.id=b.id
and
a.
name
=
'表A'
set
@sql=
'select '
+
right
(@list,len(@list)-1)+
' from 表A'
exec
(@sql)
4.查看硬盘分区:
EXEC
master..xp_fixeddrives
5.比较A,B表是否相等:
if (
select
checksum_agg(binary_checksum(*))
from
A)
=
(
select
checksum_agg(binary_checksum(*))
from
B)
print
'相等'
else
print
'不相等'
6.杀掉所有的事件探察器进程:
DECLARE
hcforeach
CURSOR
GLOBAL
FOR
SELECT
'kill '
+RTRIM(spid)
FROM
master.dbo.sysprocesses
WHERE
program_name
IN
(
'SQL profiler'
,N
'SQL 事件探查器'
)
EXEC
sp_msforeach_worker
'?'
7.记录搜索:
开头到N条记录
Select
Top
N *
From
表
-------------------------------
N到M条记录(要有主索引ID)
Select
Top
M-N *
From
表
Where
ID
in
(
Select
Top
M ID
From
表)
Order
by
ID
Desc
----------------------------------
N到结尾记录
Select
Top
N *
From
表
Order
by
ID
Desc