学步园

这个存储过程的作用是自动生成编号，比如我们在数据库中有个**票编号字段，该存储过程可以生成格式为头（自己定义的一个头，比如AA）＋日期（形如20070401）＋6位递增整数（形如002254），并且每到了新的一年后面的6位整数便会归0重新开始。其中用到了动态执行sql的方法。参数有四个，需要给出表名称，字段名称，头和一个返回值。

=============================================
-- Author:        <vagerent>
-- Create date: <2006-12-14>
-- Description:    <获取编码，形如HEAD20060512000001；(头+日期+6位整数)后面为六位，每新的一年编号重新

归0>
-- =============================================
Create PROCEDURE [dbo].[mp_GetPKNo]
    @sTableName varchar(50),
    @sFieldName varchar(50),--目标字段
    @sHead varchar(50),--前缀，如Head等
    @sPK varchar(50) out --输出

AS
BEGIN
    declare @dToday datetime,@sToday varchar(10),
        @sYear varchar(10),@sSubID varchar(20),@sSql nvarchar(1000);--动态sql一定要用

nvarchar！！！
    
    set @dToday = getdate();
    set @sToday = Convert(varchar,Year(@dToday))
        +SubString(Convert(varchar,Month(@dToday)+100),2,2)
        +SubString(Convert(varchar,Day(@dToday)+100),2,2);
    --select @sToday--形如20060512
    set @sYear=Convert(varchar(10),Year(getdate()))
    --select @sYear--形如2006
    
    declare @sHead1 varchar(30),@iHeadLength int;
    set @sHead1=@sHead+@sYear;--形如HEAD2006
    --select @sHead1;--形如HEAD2006
    set @iHeadLength=Len(@sHead1);
    --select @iHeadLength;--形如6
    set @sSql = N'select @sSubID=Max(SubString('
        +@sFieldName+','+Convert(varchar(10),@iHeadLength+5)+',6)) from '
        +@sTableName+' where SubString('+@sFieldName+',1,'
        +Convert(varchar(10),@iHeadLength)+')='''+@sHead1+'''';
    --select @sSql;--形如select..
    exec sp_executesql @sSql,N'@sSubID varchar(20) output',@sSubID output;

    if ((@sSubID='') or (@sSubID is null))
        set @sSubID='000001'
    else
    begin
        declare @iSubID int;
        set @iSubID=Convert(int,@sSubID)+1000001;
        set @sSubID=SubString(Convert(varchar,@iSubID),2,6)
    end
    --select @sSubID;
    set @sPK=@sHead+@sToday+@sSubID;

return
END

--Demo 1: 
use northwind 
go 
set statistics IO on 
go 
select count(*) from northwind.dbo.employees 
go 
set statistics IO off 
go 

use northwind 
go 
exec sp_spaceused employees 
go 

--Demo 2: 
set statistics time on 
go 
select count(*) from northwind.dbo.employees 
go 
set statistics time off 
go 

--Demo 3: 
set showplan_text on 
go 
select count(*) from northwind.dbo.employees 
go 
set showplan_text off 
go 

--Demo 4: 
set nocount on 
go 
select count(*) from northwind.dbo.employees 
go 
set nocount off 
go 


--Demo 5查询单条sql语句的执行时间: 
declare @start_time datetime 
select @start_time=getdate() 
select * from northwind.dbo.employees 
select '查询语句的执行时间(毫秒)'=datediff(ms,@start_time,getdate()) 

--Demo 6查询成批的sql语句的执行时间: 
create table #save_time(start_time datetime not null) 
insert #save_time values(getdate()) 
go 
select * from employees 
go 
select * from orders 
go 
select '查询语句的执行时间(毫秒)'=datediff(ms,start_time,getdate()) 
from #save_time 
drop table #save_time 
go 

--Demo 7返回语句的执行计划内容: 
set showplan_all on 
go 
select * from pubs.dbo.authors 
go 
set showplan_all off 
go 

--Demo 8从执行计划判断是否需要优化SQL: 
/**//*SEEK操作*/ 
set showplan_all on 
go 
select * from pubs.dbo.sales where stor_id>='7131' 
go 
set showplan_all off 
go 
/**//*SCAN操作*/ 
set showplan_all on 
go 
select * from pubs.dbo.sales where ord_date is not null 
go 
set showplan_all off 
go 

--Demo 9连接查询VS子查询: 
/**//*子查询*/ 
set statistics io on 
go 
select au_fname,au_lname from pubs.dbo.authors where au_id in 
(select au_id from pubs.dbo.titleauthor) 
set statistics io off 
go 


/**//*连接查询*/ 
set statistics io on 
go 
select distinct au_fname,au_lname from pubs.dbo.authors as a inner join 
pubs.dbo.titleauthor as t on a.au_id=t.au_id 
go 
set statistics io off 
go 

--Demo 10智能优化: 
select p1.productname from northwind.dbo.products as p1 inner join 
northwind.dbo.products as p2 on (p1.unitprice=p2.unitprice) 
where p2.productname like 'Alice%' 

1. 查看数据库的版本     
   select @@version 
    
   常见的几种SQL SERVER打补丁后的版本号: 
    
    8.00.194   Microsoft SQL Server 2000  
    8.00.384   Microsoft SQL Server 2000 SP1  
    8.00.532   Microsoft SQL Server 2000 SP2  
    8.00.760   Microsoft SQL Server 2000 SP3  
    8.00.818   Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031  
    8.00.2039  Microsoft SQL Server 2000 SP4   
    
2. 查看数据库所在机器操作系统参数     
   exec master..xp_msver 
    
3. 查看数据库启动的参数         
   sp_configure 
         
4. 查看数据库启动时间         
   select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 
    
   查看数据库服务器名和实例名 
   print 'Server Name: ' + convert(varchar(30),@@SERVERNAME)         
   print 'Instance: ' + convert(varchar(30),@@SERVICENAME)       

5. 查看所有数据库名称及大小 
   sp_helpdb 
    
   重命名数据库用的SQL 
   sp_renamedb 'old_dbname', 'new_dbname' 
    
6. 查看所有数据库用户登录信息 
   sp_helplogins 
    
   查看所有数据库用户所属的角色信息     
   sp_helpsrvrolemember 
    
   修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 
    
   更改某个数据对象的用户属主 
   sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner' 
    
   注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 
    
   把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 
    
   查看某数据库下,对象级用户权限 
   sp_helprotect 
    
7. 查看链接服务器         
   sp_helplinkedsrvlogin 
    
   查看远端数据库用户登录信息     
   sp_helpremotelogin 
    
8.查看某数据库下某个数据对象的大小 
   sp_spaceused @objname 
   
   还可以用sp_toptables过程看最大的N(默认为50)个表 
   
   查看某数据库下某个数据对象的索引信息 
   sp_helpindex @objname 
    
   还可以用SP_NChelpindex过程查看更详细的索引情况 
   SP_NChelpindex @objname 
    
   clustered索引是把记录按物理顺序排列的，索引占的空间比较少。  
   对键值DML操作十分频繁的表我建议用非clust