主题：[转帖]经典SQL自定义函数

1、确定某年某月有多少天

实现原理：先利用DATEDIFF取得当前月的第一天，再将月份加一取得下月第一天，然后减去1分钟，再取日期的

                   天数部分，即为当月最大日期，也即当月天数

CREATE FUNCTION DaysInMonth ( @date datetime ) Returns int
AS
BEGIN
RETURN Day(dateadd(mi,-3,DATEADD(m, DATEDIFF(m,0,@date)+1,0)))
END

调用示例：

select dbo.DaysInMonth ('2006-02-03')

(2)计算哪一天是本周的星期一

SELECT DATEADD(week, DATEDIFF(week,'1900-01-01',getdate()), '1900-01-01') --返回2006-11-06 00:00:00.000
或
SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)     

(3)当前季度的第一天

SELECT DATEADD(quarter, DATEDIFF(quarter,0,getdate()), 0)—返回2006-10-01 00:00:00.000

(4)一个季度多少天

declare @m tinyint,@time smalldatetime

select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
                       when @m between 4 and 6 then 4
                       when @m between 7 and 9 then 7
                       else 10 end

select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time)) —返回92

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
8.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
10：获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13：查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
Select * From TableName Order By CustomerName
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
Sql优化是一项复杂的工作，以下的一些基本原则是本人看书时所记录下来的，很明确且没什么废话：
1． 索引的使用：
（1）.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。
（2）.避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：
低效：select * from dept where sal*12 >2500;
高效：select * from dept where sal>2500/12;
(3).避免在索引列上使用not和 “!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=”时，就会停止使用索引而去执行全表扫描。
(4).索引列上>=代替>
低效：select * from emp where deptno > 3
高效：select * from emp where deptno >=4
两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
(5).非要对一个使用函数的列启用索引，基于函数的索引是一个较好的方案。
2. 游标的使用：
   当在海量的数据表中进行数据的删除、更新、插入操作时，用游标处理的效率是最慢的，但是游标又是必不可少的，所以正确使用游标十分重要：
   (1). 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。
   (2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录，例如：
insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)
ods_customer为数据源表。dim_customer为维表。
   (3). 使用显式的游标，因为隐式的游标将会执行两次操作，第一次检索记录，第二次检查too many rows这个exception,而显式游标不执行第二次操作。
3． 据抽取和上载时的sql优化：
(1). Where 子句中的连接顺序：
oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。如：
低效：select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);
高效：select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;
   (2). 删除全表时，用truncate 替代 delete,同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml。
   (3). 尽量多使用commit
只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。
   (4). 用exists替代in ，可以提高查询的效率。
   (5). 用not exists 替代 not in
   (6). 优化group by
提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。如：
低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;
高效： select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;
   (7). 有条件的使用union-all 替代 union：这样做排序就不必要了，效率会提高3到5倍。
   (8). 分离表和索引
       总是将你的表和索引建立在不同的表空间内，决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上



========================分割线=================================


将IP地址转换成BigInt的函数：

CREATE FUNCTION GetIPNumber ( @ip varchar(20) ) Returns bigint
AS

BEGIN
declare @lastpointplace int
declare @curpointplace int
declare @ip1 bigint
declare @ip2 int
declare @ip3 int
declare @ip4 int
set @lastpointplace=0
set @curpointplace=0
set @curpointplace=charindex('.',@ip,@lastpointplace+1)
set @ip1=convert(int,substring(@ip,@lastpointplace+1,@curpointplace-@lastpointplace-1))
set @lastpointplace=@curpointplace

set @curpointplace=charindex('.',@ip,@lastpointplace+1)
set @ip2=convert(int,substring(@ip,@lastpointplace+1,@curpointplace-@lastpointplace-1))
set @lastpointplace=@curpointplace

set @curpointplace=charindex('.',@ip,@lastpointplace+1)
set @ip3=convert(int,substring(@ip,@lastpointplace+1,@curpointplace-@lastpointplace-1))
set @lastpointplace=@curpointplace

set @ip4=convert(int,substring(@ip,@lastpointplace+1,len(@ip)-@lastpointplace))

return @ip1*256*256*256+@ip2*256*256+@ip3*256+@ip4

END

在SQL SERVER中调用自定义函数时，有一点需要注意，必须在自定义函数前加上创建此函数的用户，如：

select dbo.GetIPNumber('222.111.111.255')



由汉字生成拼音首字母

/****** Object: UserDefinedFunction [dbo].[fun_getPY_jianpin]    Script Date: 10/21/2009 13:50:30 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fun_getPY_jianpin]') AND xtype in (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[fun_getPY_jianpin] GO /****** Object: UserDefinedFunction [dbo].[fun_getPY_jianpin]    Script Date: 10/21/2009 13:50:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create function [dbo].[fun_getPY_jianpin](@str nvarchar(4000)) returns nvarchar(4000) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0 begin set @word=left(@str,1) --如果非汉字字符，返回原字符 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC) else @word end) set @str=right(@str,len(@str)-1) end return @PY end GO