--查看当前库的账号

SELECT  members.name, roles.name,roles.type_desc,members.type_desc
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
ON rolemem.member_principal_id = members.principal_id
ORDER BY members.name

EXEC dbo.sp_helprolemember

T-SQL Enhancements
参考：英文，中文

1，WITH RESULT SETS
更改存储过程返回结果表的字段名类型
CREATE PROCEDURE Denali_WithResultSet  AS  
BEGIN         
    SELECT 1 as No,’Tsql’ Type, ‘WithResultSet’ AS Feature
    UNION ALL  SELECT 2 as No,’Tsql’ Type, ‘Throw’ AS Feature 
    UNION ALL SELECT 3 as No,’Tsql’ Type, ‘Offset’ AS Feature 
    UNION ALL SELECT 4 as No,’Tsql’ Type, ‘Sequence’ AS Feature  
END  
GO  
EXEC Denali_WithResultSet  
WITH RESULT SETS  (( No int,FeatureType varchar(50), FeatureName varchar(50) )   )

2，OFFSET and FETCH
offset过滤几行 fetch:取下面的几行
SELECT top 15 * FROM dbo.frmuser f(NOLOCK) order BY account     
SELECT * FROM dbo.frmuser f(NOLOCK) order BY account OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY   
SELECT top 5 * FROM ( SELECT * ,ROW_NUMBER()OVER (ORDER BY account)n FROM frmuser f)m WHERE m.n>10

3，SEQUENCE
提供自增长，与identity不同的是不依赖与表，并且是全局性的 可不设置最大值,若到最大值时可自动循环。
CREATE SEQUENCE SeqRange  AS int   START WITH 1   INCREMENT BY 1     MINVALUE 1      MAXVALUE 5    CYCLE ;//循环自动    
SELECT NEXT VALUE FOR dbo.SeqRange  S
ELECT NEXT VALUE FOR dbo.Seq; 
 SELECT NEXT VALUE FOR dbo.Seq;

到达最大值后，可重置
ALTER SEQUENCE seq  RESTART WITH 1

赋值：

DECLARE @I INT

相关存储过程
DECLARE @first_value sql_variant,
@last_value sql_variant
EXEC sp_sequence_get_range @sequence_name = N’SeqRange’, @range_size = 4, @range_first_value = @first_value OUTPUT, @range_last_value = @last_value OUTPUT;
SELECT @first_value AS FirstNumber, @last_value as LastNumber

应用：订单号生成
可在表定义时使用，这样我们就能在插入之前获取ID
create table t1 ( orderID integer default (next value for dbo.TestID), OrderDesc varchar(50) )

4，FORCESEEK and FORCESCAN
forceseek:强制查询按某个索引查找
forecescan:强制查询按查个索引扫描

5，
EOMONTH：返回当月最后一天
CHOOSE:返回列表中的第几个值
Select Choose (2, 'January', 'February', 'March');
IIF:三值逻辑
SELECT IIF (@a > @b, 'TRUE', ‘FALSE’) AS Result;
CONCAT: 连接，可多种类型，如整型与字符串相加
SELECT CONCAT ('age:',1) AS Result;

OVER 增强
unbounded preceding：从头开始
CURRENT ROW：当前行
unbounded following：尾

如：
SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

6，分析函数
lag:返回按某种排序相比当前行，上几行的字段 LAG (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
SELECT TOP 10 Account,lag(account,1,'开始') OVER( ORDER BY account )
FROM dbo.frmuser
ORDER BY account

SELECT TOP 10 Account,lead(account+'aaa',1,'开始') OVER( ORDER BY account )
leag:返回按某种排序相比当前行，下几行的字段
LAST_VALUE 返回 SQL Server 2012 中有序值集中的最后一个值,这个集可用range指定 默认是从第一行到当前这一行:RANGE BETWEEN unbounded preceding AND CURRENT ROW FIRST_VALUE 开头一个值
–需求：取出客户的最近一次购买的订单号与订单金额

SELECT *
FROM (
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount,
last_value(orderdate) OVER( PARTITION BY bc.ID ORDER BY bo.OrderDate RANGE BETWEEN unbounded preceding AND unbounded following ) lastorderdate
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
)m
WHERE m.orderdate = m.lastorderdate
ORDER BY CustomerName,OrderDate

–原方法
SELECT bc.CustomerName, bo.OrderDate,bo.OrderNo,bo.Amount
FROM dbo.bmdCustomer bc(NOLOCK)
JOIN dbo.bdOrder bo(NOLOCK) ON bc.ID = bo.CustomerID
–WHERE bc.id IN(1819,2206,2444)
AND bo.orderdate = ( SELECT MAX(OrderDate)
FROM dbo.bdOrder bo2(NOLOCK)
WHERE bo2.CustomerID = bo.CustomerID)
ORDER BY CustomerName,OrderDate

For many reasons SQL Server DBAs need to find the last date and time of an update on a sql table. The SQL Server DMV sys.dm_db_index_usage_stats  can supply enough information to allow the DBA to make a good estimate of the time.

In this case I needed to create a list candidates for tables to be deleted. The data became bloated with a number of different temp tables .

The one drawback of using the DMV is that data is refreshed after every SQL Server restart.

This script lists all tables in the current database, with various columns assisting in deciding the last update.

 

SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()
last_user_update - Time of last user update.
user_updates - Number of updates by user queries.
last_user_seek - Time of last user seek .
last_user_scan - Time of last user scan.
last_user_lookup - Time of last user lookup.
user_seeks - Number of seeks by user queries.
user_scans - Number of scans by user queries.
user_lookups - Number of bookmark lookups by user queries

Related Posts

SQL Server – Last DML operation

SQL Server - Calculate table size with existing data

How to request SQL Server troubleshooting

--合并表
--输出调成以文本格式显示结果
DECLARE pcur CURSOR FOR
SELECT a.name,a.object_id
FROM sys.tables a
WHERE name IN('bdRecordTrace',
'bdTrace',
'bmdCustomerAsk',
'bmdCustomerShipRep',
'bmdTelTrace',
'bdfollowrecord')

DECLARE @tableName VARCHAR(50),@object_id INT,@insertsql NVARCHAR(max)
OPEN pcur
FETCH NEXT FROM pcur INTO @tableName,@object_id
WHILE @@FETCH_STATUS = 0
BEGIN 
	SET @insertsql = ''
	--SET @insertsql = 'INSERT INTO dbo.' + @tablename + '( '
	SELECT @insertsql = @insertsql + ',' + c.name
	FROM sys.columns c
	WHERE c.object_id = @object_id
	ORDER BY column_id
	SET @insertsql =  '--'+@tablename + '--'
					  + CHAR(13)  
					  +'INSERT INTO dbo.' + @tablename + '( ' + STUFF(@insertsql,1,1,'') + ')' 
					  + CHAR(13) 
					  +  'SELECT * ' 
					  + CHAR(13)
					  +'FROM [211].BRM_LVJIAN.DBO.' + @TableName + ' a'
					  + CHAR(13)
					  +'WHERE NOT EXISTS( SELECT 1 FROM dbo.'+@tableName+' b(NOLOCK) WHERE b.id = a.id )'
					  + CHAR(13)
					  + 'GO'
					  + CHAR(13)
	PRINT @insertsql
	FETCH NEXT FROM pcur INTO @tableName,@object_id
END 
CLOSE pcur
DEALLOCATE pcur

--1，修改文件逻辑位置
	
	--1)查询文件逻辑名，
	SELECT name logical_name, physical_name AS CurrentLocation, state_desc
	FROM sys.master_files
	WHERE database_id = DB_ID('test')
	
	--2）修改
	ALTER DATABASE test MODIFY FILE ( NAME = Test, FILENAME = 'M:\Program Files\SQL Server2008R2\Test.mdf' )
	ALTER DATABASE test MODIFY FILE ( NAME = Test_log, FILENAME = 'M:\Program Files\SQL Server2008R2\Test_log.ldf' )

--2，脱机或停数据库
ALTER DATABASE test SET OFFLINE

–3，物理移动数据库文件到新位置。 –4，联机或启动数据库 ALTER DATABASE test SET ONLINE –5，查看效果 SELECT name logical_name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID('test') –参考：http://msdn.microsoft.com/zh-cn/library/ms345483(v=sql.90).aspx

方案有二种：
1，OPENROWSET
SELECT *
FROM
OPENROWSET('MSDASQL','DRIVER={MySQL ODBC 3.51 Driver};
SERVER=192.168.16.210;PORT=3306;DATABASE=mydb;USER=mas;PASSWORD=123456;
STMT=set names gb2312;OPTION=2049',
'select bill_time,mobile,sms_content from tbl_sm_bill_120809;')

2,建立链接服务器。
  1）安装mysql ODBC
  2) 配置系统DSN,控制面板 – 管理工具-数据源 (ODBC)
  3）sqlserver中增加链接服务器。
参考：How To Setup SQL Server Linked Server to MySQL

  4）使用：SELECT * FROM mysqlsms…tbl_sm_bill_120809
     或者：SELECT * FROM OPENQUERY(mysqlsms, 'SELECT * FROM tbl_sm_bill_120809;')

  对于mysql中列是char型的查询会报错，如：

INSERT INTO OPENQUERY(Servername,'select * from DBName.tablename WHERE 1=2') --1=2防止查询整个表
SELECT '1'

SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')

UPDATE OPENQUERY(Servername,'select * from DBName.tablename')
SET colname='2'
WHERE colname='1'

SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')

--1，VS新建数据库项目，，添加新项-sqlserverclr #,添加自定义函数,
--函数前标记 [Microsoft.SqlServer.Server.SqlFunction]
--可直接右建部署。生成后在debug文件夹下会有sql执行的部署文件
--选择一数据库，便于调试与部署测试
--注：--vs并不运行直接添加第三方引用，可参考此种方式：http://updates.sqlservervideos.com/2010/07/adding-references-to-sql-clr-projects.html
--注意，64位的sqlserver不支持32位的dll 

--开启数据库支持
sp_configure 'clr enabled', 1;
RECONFIGURE;
 
--在部署时若程序集引用了其它的dll,应将这些dll先注册进sqlserver再创建自己的程序集
--注册引用
ALTER DATABASE [master] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [System.Net.Http]
AUTHORIZATION [dbo]
FROM 
'C:Program FilesReference AssembliesMicrosoftFrameworkv3.5System.Net.Http.dll' –没有的copy放置上
WITH PERMISSION_SET = UNSAFE

–-部署
–-1，建立程序集
–-增加
CREATE ASSEMBLY [SqlServerProject1]
from N'I:ITshareSqlServerProject1.dll'
–或者直接增加二进制文件，二进制文件见部署sql

CREATE ASSEMBLY [MySafe]
    AUTHORIZATION [dbo]
 FROM 0x4D5A900003000000040000....
WITH PERMISSION_SET = UNSAFE;

–-2，映射
CREATE FUNCTION [dbo].[Csharp]
(@code NVARCHAR (4000))
RETURNS NVARCHAR (4000)
AS
 EXTERNAL NAME [CLR].[UserDefinedFunctions].[Csharp]
–删除
DROP FUNCTION Decrypto
DROP ASSEMBLY MySafe

–-查询
/*
TA = 程序集（CLR 集成）触发器 CLR_TRIGGER
FS = 程序集 (CLR) 标量函数 CLR_SCALAR_FUNCTION
FT = 程序集 (CLR) 表值函数 CLR_TABLE_VALUED_FUNCTION
PC = 程序集 (CLR) 存储过程 CLR_STORED_PROCEDURE
*/

–-取调用的函数
SELECT s.type_desc 类型,s.name 名称,f.name 程序集,m.assembly_class 类名,m.assembly_method 方法名
FROM sys.objects s
JOIN sys.assembly_modules m ON s.object_id = m.object_id
JOIN sys.assemblies f ON m.assembly_id = f.assembly_id
WHERE type_desc LIKE 'clr%'
ORDER BY s.type_desc

–-程序集
SELECT TOP 10 *
FROM sys.assemblies

–-程序集对应文件
SELECT TOP 10 *
FROM sys.assembly_files
WHERE assembly_id = 65537
SELECT TOP 10 *
FROM sys.assembly_references

-–程序集对应函数
SELECT TOP 10 *
FROM sys.assembly_modules
SELECT dbo.Decrypto('UW7EcyjrWA2Qanp9nXmjJw==')
SELECT dbo.CLR_Decrypto('F0+zdAqmYv51nUL4fY5Z/w==')
select dbo.CLR_Encrypto('aaa')

mssql中有一money类型，可以用来转换
declare @num INT
set @num = 4564654
select replace(convert(varchar,cast(@num as money),1), '.00','') --4,564,654

Conversion from INT to HEX

SELECT CONVERT(VARBINARY(8), 256)

Converting from HEX to INT

SELECT CONVERT(INT, 0x00000100)
 

SELECT 0x00000100*1 HextoInt

from:http://blog.sqlauthority.com/2012/07/26/sql-server-answer-how-to-convert-hex-to-decimal-or-int/
 

在job 运行代理步骤中加入：
-OutputVerboseLevel 4 -Output C:\TEMP\mergeagent.log
运行复制job即可

引自:
http://webcache.googleusercontent.com/search?q=cache:9mOGrpV0zukJ:www.mssqltips.com/sqlservertip/1679/getting-additional-error-messaging-information-for-sql-server-replication/+&cd=3&hl=en&ct=clnk&client=firefox-a

参数：
http://msdn.microsoft.com/en-us/library/ms147839.aspx

create procedure sp_generate_insert_script
                 @tablename_mask varchar(30) = NULL
as
begin
  declare @tablename       varchar (128)
  declare @tablename_max   varchar (128)
  declare @tableid         int
  declare @columncount     numeric (7,0)
  declare @columncount_max numeric (7,0)
  declare @columnname      varchar (30)
  declare @columntype      int
  declare @string          varchar (30)
  declare @leftpart        varchar (max)   
  declare @rightpart       varchar (max)   
  declare @hasident        int

  set nocount on

  -- take ALL tables when no mask is given (!)
  if (@tablename_mask is NULL)
  begin
    select @tablename_mask = '%'
  end

  -- create table columninfo now, because it will be used several times

  create table #columninfo
  (num      numeric (7,0) identity,
   name     varchar(30),
   usertype smallint)


  select name,
         id
    into #tablenames
    from sysobjects
   where type in ('U' ,'S')
     and name like @tablename_mask

  -- loop through the table #tablenames

  select @tablename_max  = MAX (name),
         @tablename      = MIN (name)
    from #tablenames

  while @tablename <= @tablename_max
  begin
    select @tableid   = id
      from #tablenames
     where name = @tablename

    if (@@rowcount <> 0)
    begin
      -- Find out whether the table contains an identity column
      select @hasident = max( status & 0x80 )
        from syscolumns
       where id = @tableid

      truncate table #columninfo

      insert into #columninfo (name,usertype)
      select name, type
        from syscolumns C
       where id = @tableid
         and type <> 37            -- do not include timestamps

      -- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames

      select @leftpart = 'select ''insert into '+@tablename
      select @leftpart = @leftpart + '('

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo
      while @columncount <= @columncount_max
      begin
        select @columnname = name,
               @columntype = usertype
          from #columninfo
         where num = @columncount
        if (@@rowcount <> 0)
        begin
          if (@columncount < @columncount_max)
          begin
            select @leftpart = @leftpart + @columnname + ','
          end
          else
          begin
            select @leftpart = @leftpart + @columnname + ')'
          end
        end

        select @columncount = @columncount + 1
      end

      select @leftpart = @leftpart + ' values('''

      -- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo

      select @rightpart = ''

      while @columncount <= @columncount_max
      begin
        select @columnname = name,
               @columntype = usertype
          from #columninfo
         where num = @columncount

        if (@@rowcount <> 0)
        begin

          if @columntype in (39,47)
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(' + @columnname + ',' +

replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
          end

          else if @columntype = 35
                                  
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(convert(varchar(1000),' +

@columnname + ')' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) +

',''NULL'')'
          end

          else if @columntype in (58,61,111)
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),' + @columnname +

')+'+ replicate( char(39), 4 ) + ',''NULL'')'
          end

          else  
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'
          end


          if ( @columncount < @columncount_max)
          begin
            select @rightpart = @rightpart + '+'','''
          end

        end
        select @columncount = @columncount + 1
      end

    end

    select @rightpart = @rightpart + '+'')''' + ' from ' + @tablename

    -- Order the select-statements by the first column so you have the same order for
    -- different database (easy for comparisons between databases with different creation orders)
    select @rightpart = @rightpart + ' order by 1'

    -- For tables which contain an identity column we turn identity_insert on
    -- so we get exactly the same content

    if @hasident > 0
       select 'SET IDENTITY_INSERT ' + @tablename + ' ON'

    exec ( @leftpart + @rightpart )

    if @hasident > 0
       select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'

    select @tablename      = MIN (name)
      from #tablenames
     where name            > @tablename
  end

end

--newid()保证每次都随机
--checksum 转成数字，可能有负数
--rand保证数字都是正数,且数字足够大
--转成bigint保证不会出现e+,科学计数法
--随机整数 SELECT CHECKSUM(NEWID()) 返回随机数1到n： abs(CHECKSUM(NEWID()))%n + 1
--随机浮点数 rand(CHECKSUM(NEWID()))
--随机固定数位随机数：SELECT LEFT(CONVERT(VARCHAR(20),CONVERT(BIGINT,RAND(CHECKSUM(NEWID())) * 1e+16)) + '0000',16)

SELECT TOP 10 LEFT(r + '0000',16)
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
CROSS APPLY( SELECT CONVERT(VARCHAR(20),CONVERT(BIGINT,RAND(CHECKSUM(NEWID())) * 1e+16)) r )  m

SELECT TOP 2000 rCode --排重
 FROM
(
    SELECT DISTINCT rCode
    FROM (
        SELECT  LEFT(r,6) rCode
        FROM sys.all_columns a
        CROSS APPLY( SELECT ABS(CHECKSUM(NEWID())) r )  m
    )M
    --WHERE NOT EXISTS( SELECT 1 FROM CouponBasicInfo cbi WHERE cbi.CouponNo = m.CouponNo )
) N

注意：abs(checksum(newid()))会出现重复，见下例：
          --正负
          SELECT CHECKSUM('22699D09-DBF8-4147-996A-16FF92A5EBFB')
          SELECT CHECKSUM('97293ABF-B3A8-4427-BE65-21F3A92EAE2A')
          
          --双负
          SELECT CHECKSUM('9F5BE5EA-D4C7-4207-AAB0-046B35795557')
          SELECT CHECKSUM('16A51019-E786-4FDD-B661-8C9353B20053')
          
          --双正
          SELECT CHECKSUM('E60F036B-86FB-48D0-AD09-DB0B7E6091EC')
          SELECT CHECKSUM('9106BB7D-7228-4649-8511-0C96780F1917')
参考：http://www.sqlservercentral.com/articles/Data+Generation/87901/

正如”随机数的生成过程非常重要因此我们不能对其视而不见”(引自Robert R -橡树岭国家实验室),我们希望占用您一点点宝贵的时间在基于SQL Server MVP Jeff Moden的 成果的基础上完成这项工作。对于使用SQL来产生随机数来说，我们会重点讲解从均匀分布随机数(non-uniformly distributed random numbers)的基础上生成非均匀分布随机数(uniformly distributed random numbers);包括一些统计分布的基础来帮你起步。

    正如我们所知，随机数在仿真中非常重要(尤其是蒙特卡洛仿真法）,还有随机数在密码学以及其它高科技领域中也扮演了同样重要的角色。除此之外在我们的SQL Server中有时也需要产生大量的随机数据来测试SQL的性能。

    因为我并不是统计学家，因此我们这里仅仅来看用SQL生成并且能显而易见的看出其随机性的的随机数字,而并不会深入到数学原理来看这个随机性是真正的“随机”还是“貌似随机”我们的意图是文章中算法的正确性以及这个算法在非关键领域是否足够使用。

   通常来说，由均匀随机数转换成非均匀随机数的技术是将均匀随机数乘以累计分布函数(CDF)对于目标数据的反转。但在实践中，累计分布函数是否针对特定分布存在有效哪怕是接近的函数并不好估计。但幸运的是，比我们聪明许多的那帮家伙已经分析过了多个领域的多种分布函数我们可以直接拿来使用,这些函数可以满足我们的大多数需求。

测试工具

    在我们的测试中，我们采用标准的SQL技术来使用伪URN(均匀分布随机数)函数生成Float类型的参数传给转换函数.,我们将使用标量(Scalar)函数包括SCHEMABINDING关键字解决性能问题然而，或许你还想使用同等的表值函数来测试性能是否还可以进一步提升。首先，来生成测试数据。

-- Data definition and setup
DECLARE @NumberOfRNs   INT
       ,@Lambda        FLOAT     -- For the Poisson NURNs
       ,@GaussianMean  FLOAT     -- For the Normal NURNs
       ,@GaussianSTDEV FLOAT
       ,@LambdaEXP     FLOAT     -- For the Exponential NURNs
       ,@WeibullAlpha  FLOAT     -- For the Weibull NURNs
       ,@WeibullBeta   FLOAT
       ,@Laplaceu      FLOAT     -- For the Laplace NURNs
       ,@Laplaceb      FLOAT

SELECT @NumberOfRNs    = 10000
      ,@Lambda         = 4.0     -- Lambda for the Poisson Distribution
      ,@GaussianMean   = 5       -- Mean for the Normal Distribution
      ,@GaussianSTDEV  = 1.5     -- Standard Deviation for the Normal Distribution
      ,@LambdaEXP      = 1.5     -- Lambda for the Exponential Distribution
      ,@WeibullAlpha   = 1.0     -- Alpha (scale) for the Weibull Distribution
      ,@WeibullBeta    = 1.5     -- Beta (shape) for the Weibull Distribution
      ,@Laplaceu       = 4.0     -- Mu (location) for the Laplace Distribution
      ,@Laplaceb       = 1.0     -- Beta (scale) for the Laplace Distribution

--CREATE TYPE Distribution AS TABLE (EventID INT, EventProb FLOAT, CumProb FLOAT)

DECLARE @Binomial      AS Distribution
    ,@DUniform         AS Distribution
    ,@Multinomial      AS Distribution

-- Simulate a coin toss with a Binomial Distribution
INSERT INTO @Binomial
SELECT 0, 0.5, 0.5 UNION ALL SELECT 1, 0.5, 1.0

-- Events returned by this Discrete Uniform distribution are the 6
-- Fibonacci numbers starting with the second occurrence of 1
INSERT INTO @DUniform
SELECT 1, 1./6., 1./6. UNION ALL SELECT 2, 1./6., 2./6.
UNION ALL SELECT 3, 1./6., 3./6. UNION ALL SELECT 5, 1./6., 4./6.
UNION ALL SELECT 8, 1./6., 5./6. UNION ALL SELECT 13, 1./6., 1.

-- Events returned by this Multinomial distribution are the 5
-- Mersenne primes discovered in 1952 by Raphael M. Robinson
INSERT INTO @Multinomial
SELECT 521, .10, .10 UNION ALL SELECT 607, .25, .35 UNION ALL SELECT 1279, .30, .65
UNION ALL SELECT 2203, .15, .80 UNION ALL SELECT 2281, .2, 1.

            （译者注：我将@NumberOfRNs 参数由100W改成1W，因为100W在测试中占用的时间过长，如果你喜欢，可以随意改动。）

   下面是测试工具，为了我们期望的目标分布生成NURNs(非均匀分布随机数):

-- Create random numbers for the selected distributions
SELECT TOP (@NumberOfRNs)
     RandomUniform     = URN
    --,RandomPoisson     = dbo.RN_POISSON(@Lambda, URN)
    ,RandomBinomial    = dbo.RN_MULTINOMIAL(@Binomial, URN)
    ,RandomDUniform    = dbo.RN_MULTINOMIAL(@DUniform, URN)
    ,RandomMultinomial = dbo.RN_MULTINOMIAL(@Multinomial, URN)
    ,RandomNormal      = dbo.RN_NORMAL(@GaussianMean, @GaussianSTDEV, URN, RAND(CHECKSUM(NEWID())))
    ,RandomExponential = dbo.RN_EXPONENTIAL(@LambdaEXP, URN)
    ,RandomWeibull     = dbo.RN_WEIBULL(@WeibullAlpha, @WeibullBeta, URN)
    ,RandomLaplace     = dbo.RN_LAPLACE(@Laplaceu, @Laplaceb, URN)
 INTO #MyRandomNumbers
 FROM sys.all_columns a1 CROSS APPLY sys.all_columns a2
 CROSS APPLY (SELECT RAND(CHECKSUM(NEWID()))) URN(URN)

       接下来，我们将会逐个介绍每种分布类型，但是在此之前，首先阐述一下关于我们的测试工具你可能会问到的问题:

•     生成高斯分布NURN的算法需要两个URN
•     我们使用RN_MULTINOMIAL 函数来生成三种不同的分布类型，因为其比另外两种更加通用（会在文章后面详细解释）
•     在创建函数RN_MULTINOMIAL之前，首先需要创建自定义表类型。自定义表类型是在SQL Server 2008引入的，因此在SQL Server 2005中你不能使用这种方式，另一种替代方式是使用XML
•     我已经在文章附件中附加上了脚本，你首先运行Function.sql,然后运行NURNs.sql。生成100W的数据大概需要4.5分钟（译者注：在我的笔记本上跑了2.5分钟）

均匀分布随机数(Uniform Random Numbers) —–怎么个均匀法？

    因为接下来我们所有的数据分布都是基于均匀随机数之上，所以我们来看一下这些由标准SQL生成的随机数是怎么个均匀法。如果这些所谓的“均匀数”如果并不是那么”均匀”的话，那或多或少会对我们后面的结果产生影响。

    在｛0，1｝之间的URN概率很简单，就是0.5。对于这一区间的方差则为1/12。对于使用SQL SERVER内置的AVG（）和VAR()函数来汇总我们生成的100W条数据。结果和我们期望的差不多。

    下面的柱状图可以清晰的看到对于我们预定义区间的数据的分布：

    正如我们所看到的结果，虽然结果并不是非常的“标准”但是已经足够对于我们这种并不需要那么精确的测试了。这里需要注意的是，我经过多次测试选择的上述结果，并没有特别的选取某个结果，如果你自己跑一遍SQL,你会发现你得到的结果也差不多。

多项式随机数

    在我们开始讨论多项分布之前，我们首先看一下其它两种类型的离散分布。

    人们所熟知的抛硬币概率其实就是柏松分布。这种用来表示二选一（正面或者反面）发生的概率,返回0或者1（或是其它某个数）来表示事件是否发生(也可以理 解成“成功”或”失败”)。当然了，就抛硬币而言出现正面和反面概率是对等的，都是50%。但是柏松分布也允许其它非50%的概率，毕竟人生不总是那么公 平嘛。

    离散均匀分布则描述了多于2个事件，比如说仍骰子，每一面出现的概率都是相同的。当然，这也是和我们生成给定范围内随机整数的方法是类似的。Jeff Moden曾经在这里给出过描述。

    多项式随机数比上述两种分布类型还要宽泛，它模拟了在一系列事件中每一个单独事件发生的概率并不相同的情况。

    记住，我们的事件并不必要是一系列简单数字的集合，比如0，1，2，3。还可以是任何数字（包括负数）的集合.比如，在我们的多项式分布中我们选择了由Raphael M. Robinson在1952年发现的梅森尼质数,对于我们的离散均匀分布来说，我们采用了以1为开始的斐波纳契数列的前6个数。我们还可以通过修改用户自定义表类型Distribution的EventID列由INT改为FLOAT来将事件改为FLOAT类型。

    现在我们来看上面我们所设立用于测试的三个表变量，我们可以发现:

•     @Binomial定义了两个事件(0,1),事件发生的概率P为50%，事件不发生的概率为1-p
•     @DUniform定义了6个事件(1, 2, 3, 5, 8, 13)，就像扔骰子一样，每一个事件的概率都是1/6
•     @Multinomial定义了5个事件(521, 607, 1279, 2203, 2281),每一个事件发生的概率各不同

CREATE FUNCTION dbo.RN_MULTINOMIAL
    (@Multinomial Distribution READONLY, @URN FLOAT)
 RETURNS INT --Cannot use WITH SCHEMABINDING
AS
BEGIN
    RETURN
        ISNULL(
            (   SELECT TOP 1 EventID
                FROM @Multinomial
                WHERE @URN < CumProb
                ORDER BY CumProb)
            -- Handle unlikely case where URN = exactly 1.0
            ,(  SELECT MAX(EventID)
                FROM @Multinomial))
END

    对于我们测试的每一种分布，我们都进行了大量的测试结果后概率百分比如下表:

RandomBinomial     BinomialFreq     EventProb   ActPercentOfEvents

0                  499778           0.5         0.499778000000

1                  500222           0.5         0.500222000000

RandomDUniform     DUniformFreq     EventProb   ActPercentOfEvents

1                  166288           0.166666    0.166288000000

2                  166620           0.166666    0.166620000000

3                  166870           0.166666    0.166870000000

5                  166539           0.166666    0.166539000000

8                  166693           0.166666    0.166693000000

13                 166990           0.166666    0.166990000000

RandomMultinomial  MultinomialFreq  EventProb   ActPercentOfEvents

521                99719            0.1         0.099719000000

607                249706           0.25        0.249706000000

1279               300376           0.3         0.300376000000

2203               149633           0.15        0.149633000000

2281               200566           0.2         0.200566000000

通过上面的表格不难发现，这个概率和我们所期望的概率基本吻合。

高斯(或正态)分布随机数

    在最近的讨论中,SSC论坛的会员GPO发帖询问基于高斯（正态）分布生成随机数的问题。所以我开始研究这个问题（这其实也是本篇文章的灵感来源）并使用了博克斯·马勒变换方法,基于我的RN_GAUSSIAN函数,你可以忽视我在这个帖子中对于如何利用URN生成NURN的算法.

    高斯分布是一个连续分布,对于熟悉这种分布需要我们一点点解释，它经常采用平均数附近的“标准”样本进行分析。

     出了平均数之外，还必须指定标准偏差。下面的函数帮助我们认识能够帮我们了解示例总体数据正态分布的形状。

CREATE FUNCTION dbo.RN_NORMAL
    (@Mean FLOAT, @StDev FLOAT, @URN1 FLOAT, @URN2 FLOAT)
 RETURNS FLOAT WITH SCHEMABINDING
AS
BEGIN
    -- Based on the Box-Muller Transform
    RETURN (@StDev * SQRT(-2 * LOG(@URN1))*COS(2*ACOS(-1.)*@URN2)) + @Mean
END

    首先我们先来比较总体（期望）平均数和方差与示例平均数和方差的比较来看这两者之间是否接近。

PopulationMean   PopulationSTDEV   SampleMean         SampleSTDEV

5                1.5               5.00049965700704   1.50020497041145

  然后，我们再来看图表。图中的间隔是加上或者减去3个平均数的标准差.

     读者如果熟悉”正态”分布就会体会到这张图是多么的”标准(正态)”,我们还认识到1,000,000中有998,596(99.86%)在我们的3个平均数的标准差之内。这也是我们所期望的结果。

指数随机数

    指数随机分布是可以用CDF(累计分布函数)进行分布，并且可以很容易的用接近的表达式表达出来的分布。指数随机被应用于物理学,水理学，可靠性，等待时间等候论领域。

CREATE FUNCTION dbo.RN_EXPONENTIAL (@Lambda FLOAT, @URN FLOAT)
 RETURNS FLOAT WITH SCHEMABINDING
AS
BEGIN
    RETURN -LOG(@URN)/@Lambda
END

    首先要知道总体平均数是1/Lambda，标准方差是1/Lambda的平方,我们可以看到我们的总体平均数和方差和示例数据十分接近。

   PopulationMean   PopulationVAR   SampleMean   SampleVAR

   0.6667           0.4444          0.666        0.4444

  我们可以看由维基百科上提供的概率密度曲线，当Lambda取值为1.5时(蓝色的线)和我们的数据比较非常相似。

韦伯分布随机数

    在大学中对韦伯分布已经小有研究,按照我的理解韦伯分布也是十分规律的，所以非常适合我们在这里生成非均匀随机数(NURN).韦伯分布在很多统计和工程领域都有应用，包括天气预报，保险，水理学,存活分析,可靠性工程（我教这门课的大学教授一定会为我骄傲的）以及其它领域。

    生成韦伯分布的公式我们可以在维基百科找到。而在我们这里实现这个方式的RN_WEIBULL函数实现起来也非常简单。两个参数分别为形状和尺度参数(@WeibullAlpha,@WeibullBeta)

CREATE FUNCTION dbo.RN_WEIBULL (@Alpha FLOAT, @Beta FLOAT, @URN FLOAT)
 RETURNS FLOAT WITH SCHEMABINDING
AS
BEGIN
    RETURN POWER((-1. / @Alpha) * LOG(1. - @URN), 1./@Beta)
END

   韦伯分布的并不是那么容易计算，因为表达式使用了伽马分布。下面是我们使用了形状参数为1.0尺度参数为1.5的图形，并与维基百科提供的图形进行了对比。

拉普拉斯随机数

    或许是因为我是一个geek，还是由于我们大学教这门课的老师非常牛逼。我非常喜欢拉普拉斯变换这门课。当我知道拉普拉斯还发明了拉普拉斯统分布时，我将这种分布加入到本文中来表达对拉普拉斯的敬意。

    拉普拉斯分布是一种连续分布,所幸的是，它的累计分布函数(CDF)非常简单,在我们的函数中一个是位置参数，一个是尺度参数。

CREATE FUNCTION dbo.RN_LAPLACE (@u FLOAT, @b FLOAT, @URN FLOAT)
 RETURNS FLOAT WITH SCHEMABINDING
AS
BEGIN
    RETURN @u - @b * LOG(1 - 2 * ABS(@URN - 0.5)) *
        CASE WHEN 0 < @URN - 0.5 THEN 1 
             WHEN 0 > @URN - 0.5 THEN -1 
             ELSE 0 
        END
END

    拉普拉斯分布有着很容易计算的平均数(@u)和标准方差(2*@b^2)所以我们再一次将我们的总体样本数据和示例数据进行比较。

   PopulationMean   PopulationVAR   SampleMean   SampleVAR

   4                2               4.0009       1.9975

  我们再一次使用我们的数据和维基百科提供的数据分布图进行比较。在这里我们取@b=4(图中红线)

总结

    从本文的研究中我们得出结论，生成的非均匀随机数基本是正确的，起码和维基百科提供的数据比来说是正确的。此外我们还发现总体平均数和方差的对应关系。我们所有示例数据都在附件的EXCEL中。

    在自然界，人类和其它领域，随机是无所不在的。而工具模拟了这些随机性帮助我们在这个混乱的世界中找到规律。
    “创新其实就是在充满不确定的自然界中引入规律”—- Eric Hoffer

    科学和工程的研究往往要使用随机数来模拟自然界的现象。我希望我们的研究可以对这些领域的人有所帮助。

    对于那些好奇为什么我们不用柏松分布来产生非均匀随机数的人。因为在SQL的内置函数中不允许我们生成多个随机数（不使用NEWID() 和 RAND()是因为它们有“副作用”）,我们将继续寻找在SQL Server中生成随机数更好的办法。
    本文阐述了在统计学中普遍存在的Alpha, Beta, Gamma和F分布。但是在生成非均匀随机数背后的数学原理更加复杂，所以有兴趣的高端读者可以自行查找资料。
    我希望感谢那些勇猛无畏把本文读完的读者，尤其是那些一路跟着思考的读者。谢谢它们对于非均匀随机数的兴趣和我们对于这个领域能提供更有用的例子。

参考：http://www.cnblogs.com/CareySon/archive/2012/07/11/GenerateNURNsUsingSQLServer.html

/*导入多个excel入数据库
 1，用xp_cmdshell获取所有excel的名称
 2, 因sheet需要指定，要获取其每个文件要导入的第一个sheet名称
 3，利用OpenDataSource上传excel数据
*/

--excel所在路径
DECLARE @filepath NVARCHAR(1000)
SET @filepath = 'D:\nocode\liaoxiaoman\DDS\'

--获取所有excel名称
DROP TABLE duifang_file_dds
CREATE TABLE duifang_file_dds([filename] NVARCHAR(1000))

DECLARE @cmd VARCHAR(1000)
SET @cmd = N'dir "' + @filepath + '\*' + '*.xls" /B'
INSERT INTO duifang_file_dds
EXEC master..xp_cmdshell @cmd

--清除无效文件名称
DELETE a
FROM duifang_file_dds a
WHERE filename IS NULL

--加字段sheetname,ID,以便做调试
ALTER TABLE duifang_file_dds ADD sheetname NVARCHAR(50),ID INT IDENTITY(1,1)

--取出sheet名称 .此处最好用工具将所有excel的sheetname设置为统一
UPDATE a
--SET a.sheetname = replace(replace(replace(replace(dbo.SUBSTRING2(FILENAME,'绿瘦商贸','第',3),'第',''),'明细',''),'(',''),'（','')
SET a.sheetname = replace(replace(dbo.SUBSTRING2(FILENAME,'','第',3),'(',''),'（','')
FROM duifang_file_dds a

--格式化
--导入时不认点,故要将点替代为#
UPDATE a
SET a.sheetname = replace(replace(sheetname,'.','#'),' ','') + '$' --sheet以$结尾
FROM duifang_file_dds a

--首字母为数字，要前后加引号
UPDATE a
SET a.sheetname = CASE(ISNUMERIC(SUBSTRING(sheetname,1,1))) WHEN 1 THEN QUOTENAME(a.sheetname,'''') ELSE a.sheetname END 
FROM duifang_file_dds a


SELECT *
FROM duifang_file_dds a


--建表
DROP TABLE duifang_bill_dds
SELECT   代收款,F2,F3,F4,F5, REPLICATE('',100) FileName INTO   duifang_bill_dds 
FROM   OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
'Data Source= "D:\nocode\liaoxiaoman\DDS\5.21第三批.xls";
Extended Properties= "Excel 5.0;HDR=no;IMEX=1;Excel 8.0";Persist Security Info=False ')...['5#21$']
WHERE 1=2



TRUNCATE TABLE duifang_bill_dds

DECLARE pcur CURSOR FOR
SELECT  filename,sheetname,ID
FROM duifang_file_dds
WHERE ID >=1
ORDER BY ID

DECLARE @filename NVARCHAR(1000), @sheetname NVARCHAR(50),@s NVARCHAR(1000),@ID INT

OPEN pcur
FETCH NEXT FROM pcur INTO @filename,@sheetname,@ID
WHILE @@FETCH_STATUS = 0
BEGIN 
	--代收款转成VARCHAR(50),防止包裹号出现e+
	SET @S = 'INSERT duifang_bill_dds  
	SELECT CONVERT(VARCHAR(50),convert(bigint,代收款)),F2,F3,F4,F5, ''' + @filename + ''' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'', 
	''Data Source= "' + @filepath + @filename + '";
	Extended Properties= "Excel 5.0;HDR=no;IMEX=1;Excel 8.0";Persist Security Info=False '')...[' + @sheetname + ']
	WHERE ISNUMERIC(代收款) = 1'
	PRINT 'ID=' + CONVERT(VARCHAR(50),@ID) + CHAR(13) +@S 
	EXEC (@s)
	FETCH NEXT FROM pcur INTO @filename,@sheetname,@ID	
END 
CLOSE pcur
DEALLOCATE pcur

--哪个文件没有导入
SELECT *
FROM duifang_file_dds a
WHERE  NOT EXISTS( SELECT 1 FROM duifang_bill_dds b WHERE a.FILENAME = b.filename )

/*
复制分发执行的是job,控制复制分发就是控制这些job的开关。

--启动合并复制所有同步
EXEC ReplControl @start = 1
--关闭所有同步
EXEC 合并复制ReplControl @start = 0
*/


CREATE PROCEDURE ReplControl
@start INT --是否启动：0停止，1开启
AS 
BEGIN 
	DECLARE @execution_status INT,@sql VARCHAR(1000)
	
	/*停止时，查询正在执行的job,开启时，查询停止的job
	@execution_status:
	1 正在执行。
	4 空闲。
	*/
	SET @execution_status = CASE(@start) WHEN 0 THEN 1 ELSE 4 END 
	SET @sql = CASE(@start) WHEN 0 THEN 'msdb.dbo.sp_stop_job @job_id = ' 
							ELSE 'msdb.dbo.sp_start_job @job_id = ' END 

	CREATE TABLE #enum_job ( 
	Job_ID uniqueidentifier, 
	Last_Run_Date int, 
	Last_Run_Time int, 
	Next_Run_Date int, 
	Next_Run_Time int, 
	Next_Run_Schedule_ID int, 
	Requested_To_Run int, 
	Request_Source int, 
	Request_Source_ID varchar(100), 
	Running int, 
	Current_Step int, 
	Current_Retry_Attempt int, 
	State int 
	)       
	insert into #enum_job 
	exec master.dbo.xp_sqlagent_enum_jobs 1,hello  
		 /*p_sqlagent_enum_jobs <is sysadmin (0 or 1)>, 
						  <job owner name> 
						  [, <job id>]

	The first parameter identifies whether you want to return information about all jobs on the server, or just jobs owned by a particular job owner. If you specify "0" for this first parameter, it means you want to return job information for a particular job owner. If you specify a "1," it means you want information for all jobs. The second parameter identifies the job owner. This parameter is required on all calls to this XP but is only used when you specify "0" for the first parameter. The third and last parameter only needs to be provided if you want to return information about a particular job_id. */

	DECLARE @Job_ID uniqueidentifier
	DECLARE @item_table TABLE (Job_ID uniqueidentifier)
	INSERT INTO @item_table(Job_ID)
	SELECT A.Job_ID
	FROM #enum_job a 
	JOIN msdb.dbo.sysjobs j ON a.Job_ID = j.job_id
	JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
	WHERE c.name = 'REPL-Merge' AND a.State = @execution_status

	DECLARE @tmpSql VARCHAR(1000)
	WHILE (SELECT COUNT(1) FROM @item_table) > 0
	BEGIN
		SELECT TOP 1 @Job_ID = Job_ID
		FROM @item_table
		SET @tmpSql = @Sql + QUOTENAME(@Job_ID,'''')
		EXEC ( @tmpSql )
		--PRINT @Job_ID

		DELETE FROM @item_table 
		WHERE @Job_ID = Job_ID
	END
	DROP TABLE #enum_job
END 

使用游标的最好方式是不用游标。
why cursor is slow,It acquires locks which table variable does not do, and that is why Cursor is slow.

游标替代方案：

--Declare variables
DECLARE @item_category_id INT
Declare @X BIGINT
Set @X = 0
--Declare a memory table
DECLARE @item_table TABLE (item_category_id INT)

INSERT INTO @item_table(item_category_id)
SELECT  object_id
From master.sys.objects c1(NOLOCK)

--WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
WHILE (SELECT COUNT(1) FROM @item_table) > 0
BEGIN
    SELECT TOP 1 @item_category_id = item_category_id
    FROM @item_table 
    
    --处理
    Set @X = @X + @item_category_id
    
   delete from @item_table where item_category_id = @item_category_id
END
PRINT @X