‘sql server’ 分类下的所有文章
2016七月29

tsql 从内容中获取电话号码

sql server 评论关闭

/*
 * 从内容中获取电话号码
 */
--SELECT dbo.GeTelFromContent('已订18620023427/4')
ALTER function [dbo].[GeTelFromContent](@str VARCHAR(MAX))
RETURNS VARCHAR(50)
AS
BEGIN
	DECLARE @validchars VARCHAR(100) = '[1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
	DECLARE @idx INT,@Result VARCHAR(50)

	SET @idx = PATINDEX('%'+ @validchars +'%',@str)
	--SELECT @idx
	IF @idx > 0 AND ( @idx = LEN(@str)-10 OR PATINDEX(SUBSTRING(@str,@idx+11,1),'[0-9]')=0 )
	BEGIN
		SET @Result=SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 11)
	END
	ELSE
	BEGIN
		SET @Result = ''
	END

	RETURN @Result

END	
2015十二月3

数据库加密

sql server 评论关闭

对数据库启用 透明数据加密(TDE)【此方法适用于 sql server 2008 及以后的版本(含2008)】

注:仅 sql server enterprise(企业版)支持此功能。

它是对整个数据库进行了加密,而且既然是“透明”,也就是说不会影响到任何对数据库的操作,正常的对数据库操作(增删改查什么的),还有备份恢复什么的,都不需要特别的考虑加密问题。只有离开了当前的数据库服务器,就会发现,什么都做不了。
对bak进行还原的时候,在UI上会提示介质找不到,用sql restore时会提示
找不到指纹为 '0xBAA127AA4C8BE3F4BAD4E1369DB9F2D0910D40BA' 的服务器 证书。
需要在新的服务器中导入原来的加密证书即可正常使用。

引自:SQLServer Transparent Data Encryption


SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys;

--整个加密
--1、在 master 数据库中,添加 数据库主密钥:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'helloworld';

--2、在 master 数据库中,添加 加密数据库用的证书:
USE master;
CREATE CERTIFICATE A9Safe WITH SUBJECT = 'A9Safe';

-- 3、在 要加密的数据库 中,设置 证书以及加密算法:
USE Authorize
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

--对 要加密的数据库 启用加密:
ALTER DATABASE Authorize SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

USE CedarLog
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

ALTER DATABASE CedarLog SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

USE S60623
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

ALTER DATABASE S60623 SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

USE SMSDB
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE A9Safe;

ALTER DATABASE SMSDB SET ENCRYPTION ON WITH ROLLBACK IMMEDIATE

--5、首先要从 master 数据库中,备份加密证书:paswword 此密码用于保护私钥,恢复时使用
USE master;
BACKUP CERTIFICATE A9Safe TO FILE = 'D:A9Safe.cer'
WITH PRIVATE KEY ( FILE = 'D:A9Safe.pkey', ENCRYPTION BY PASSWORD = '$$helloworld$$' );

--6、在其他数据库服务器中,仍然首先建立 数据库主密钥,同第1步操作;
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

--7、然后,开始从文件中恢复证书:
USE master;
CREATE CERTIFICATE A9Safe FROM FILE = 'D:A9Safe.cer'
WITH PRIVATE KEY ( FILE = 'D:A9Safe.pkey', DECRYPTION BY PASSWORD = '$$helloworld$$');

--需要注意的是,数据库加密的关键是 那个证书,数据库主密钥 是用来保护数据库信息的,比如证书的存放什么的,并不直接关系到数据库的加密。
--所以,一定要备份好证书!!!不然别到时候哭着解密不了数据库。
2015十一月12

sqlserver2012新函数取月最后一天

sql server 评论关闭

SELECT DATEADD(dd,1,EOMONTH(GETDATE(),-1)),EOMONTH(GETDATE())
-- -1是取上个月
2015十月31

SQLSERVER2014内存数据表

sql server 评论关闭

内存数据表有两大索引,一是hash,一是非聚集,对于hash索引对=起作用,若是范围查询,则会执行表扫描。可以建立hash索引后再建立个非聚集索引解决单个与范围查询的问题,经测试,内存数据表平均比SSD还要快上10倍以上。故可将热表放入内存中,如业绩统计表,利用它插入更新迅速的特点,写触发器更新,利用其查询速度快的优点,提升查询速度。

USE [master]
--创建数据库
CREATE DATABASE [TestDB]
ON  PRIMARY
( NAME = N'TestDB', FILENAME = N'D:SQL2104SQLDataTestDB.mdf' ,
SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB )
LOG ON
( NAME = N'TestDB_log', FILENAME = N'D:SQL2104SQLDataTestDB_log.ldf' ,
SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 204800KB )
GO
--创建内存表使用的文件组
ALTER DATABASE [TestDB] ADD FILEGROUP [TestDB_MFG1] CONTAINS MEMORY_OPTIMIZED_DATA
GO
--创建内存表使用的文件夹
ALTER DATABASE [TestDB]
ADD FILE ( NAME = N'TestDB_MDir1', FILENAME = N'D:SQL2104SQLDataTestDB_MDir1')
TO FILEGROUP [TestDB_MFG1]
GO

CREATE TABLE [dbo].[TB1_IM]
(
    [c1] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY
    NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
    [c2] [nchar](200)  COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL
    INDEX ix_c2 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
    [c3] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    [c4] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    INDEX ix_c3_c4 NONCLUSTERED HASH(c3,c4) WITH(BUCKET_COUNT=1000000),
    INDEX ix_c2_c3  NONCLUSTERED (c2,c3)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

说明:
对于原有表,可以右键内存优化表来进行建立

1,内存索引表不支持ALTER TABLE 和 ALTER INDEX操作,如果需要修改表或者修改索引
2,对于内存优化表,有两种持续性可以选择:SCHEMA_ONLY(非持久表)和SCHEMA_AND_DATA(持久表),SCHEMA_ONLY 选项会导致数据在实例重启后丢失;而对于SCHEMA_AND_DATA(持久表),又可以设置完全持久行还是延迟持续性,延迟持续性选项允许在事务提交时可以不立即将日志写入磁盘,从而提升性能,当然代价就是发生故障时可能丢失数据。
2015十月29

mysql时间戳timestamp在sqlserver中的实现

SQL,sql server 评论关闭

CREATE FUNCTION [dbo].[UNIX_TIMESTAMP]
(
    @DATE DATETIME=''
)

RETURNS DECIMAL
AS
BEGIN
  --注意时区,北京时间统一减8变成格林尼治时间
   IF @DATE='' SET @DATE=GETDATE()
  SET @DATE=DATEADD(HOUR,-8,@DATE)
   RETURN DATEDIFF(SECOND,'1970-01-01',@DATE)

END

CREATE FUNCTION [dbo].[FROM_UNIXTIME]
(
    @i DECIMAL
)

RETURNS VARCHAR(20)
AS
BEGIN
 RETURN CONVERT(VARCHAR(20),DATEADD(SECOND,@i+28800,'1970-01-01'),120) --北京时间要加回8*3600=28800秒
END

引自:MSSQL(SQL SERVER)中获取UNIX时间戳

2015九月28

常用的sql性能语句

sql server 评论关闭
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[p_lockinfo]
    @kill_lock_spid BIT = 1 , --是否杀掉死锁的进程,1 杀掉, 0 仅显示
    @show_spid_if_nolock BIT = 1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS
    DECLARE @count INT ,
        @s VARCHAR(MAX) ,
        @i INT
    SELECT  id = IDENTITY( INT,1,1 ),
            标志 ,
            进程ID = spid ,
            线程ID = kpid ,
            块进程ID = blocked ,
            数据库ID = dbid ,
            数据库名 = DB_NAME(dbid) ,
            用户ID = uid ,
            用户名 = loginame ,
            累计CPU时间 = cpu ,
            登陆时间 = login_time ,
            打开事务数 = open_tran ,
            进程状态 = status ,
            工作站名 = hostname ,
            应用程序名 = program_name ,
            工作站进程ID = hostprocess ,
            域名 = nt_domain ,
            网卡地址 = net_address,
			sql_handle,
			stmt_start,
			stmt_end

    INTO    #t
    FROM    ( SELECT    标志 = '死锁的进程' ,
                        spid ,
                        kpid ,
                        a.blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = a.spid ,
                        s2 = 0,
						sql_handle,
						stmt_start,
						stmt_end
              FROM      master..sysprocesses a
                        JOIN ( SELECT   blocked
                               FROM     master..sysprocesses
                               GROUP BY blocked
                             ) b ON a.spid = b.blocked
              WHERE     a.blocked = 0
              UNION ALL
              SELECT    '|_牺牲品_>' ,
                        spid ,
                        kpid ,
                        blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = blocked ,
                        s2 = 1,
						sql_handle,
						stmt_start,
						stmt_end
              FROM      master..sysprocesses a
              WHERE     blocked <> 0
            ) a
    ORDER BY s1 ,
            s2

    SELECT  @count = @@rowcount ,
            @i = 1

    IF @count = 0
        AND @show_spid_if_nolock = 1
        BEGIN
            INSERT  #t
                    SELECT  标志 = '正常的进程' ,
                            spid ,
                            kpid ,
                            blocked ,
                            dbid ,
                            DB_NAME(dbid) ,
                            uid ,
                            loginame ,
                            cpu ,
                            login_time ,
                            open_tran ,
                            status ,
                            hostname ,
                            program_name ,
                            hostprocess ,
                            nt_domain ,
                            net_address,
							sql_handle,
							stmt_start,
							stmt_end
                    FROM    master..sysprocesses
            SET @count = @@rowcount
        END

    IF @count > 0
        BEGIN
            CREATE TABLE #t1
                (
                  id INT IDENTITY(1, 1) ,
                  a NVARCHAR(3000) ,
                  b INT ,
                  EventInfo NVARCHAR(2000)
                )
            IF @kill_lock_spid = 1
                BEGIN
                    DECLARE @spid VARCHAR(50) ,
                        @标志 VARCHAR(50)
                    WHILE @i <= @count
                        BEGIN
                            SELECT  @spid = 进程ID ,
                                    @标志 = 标志
                            FROM    #t
                            WHERE   id = @i
                            INSERT  #t1
                                    EXEC ( 'dbcc inputbuffer(' + @spid + ')'
                                        )
                            IF @标志 = '死锁的进程'
                                EXEC('kill '+@spid)
                            SET @i = @i + 1
                        END
                END
            ELSE
                WHILE @i <= @count
                    BEGIN
                        SELECT  @s = 'dbcc inputbuffer('
                                + CAST(进程ID AS VARCHAR) + ')'
                        FROM    #t
                        WHERE   id = @i
                        INSERT  #t1
                                EXEC ( @s
                                    )
                        SET @i = @i + 1
                    END
            SELECT  标志 ,
			 数据库名 ,
			  进程的SQL语句 = b.EventInfo,
					sqlText=substring(qt.text,a.stmt_start/2,
 					(case when a.stmt_end = -1
						  then len(convert(nvarchar(max), qt.text)) * 2
						  else a.stmt_end end -a.stmt_start)/2
					) ,
					 进程状态,
            用户名,
            累计CPU时间,
            登陆时间  ,
            工作站名 ,
            应用程序名,
			 进程ID ,
            线程ID
            FROM    #t a
                    JOIN #t1 b ON a.id = b.id
					OUTER apply sys.dm_exec_sql_text(a.sql_handle) as qt
        END
GO

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--==========================
--创建人: zxs 20110728

--==========================
CREATE       PROCEDURE [dbo].[p_cpu]
@TopNum INT =NULL,
@Last_Execution_Time VARCHAR(50) = NULL
AS
SET @TopNum = ISNULL(@TopNum,50) --默认前50条
SET @Last_Execution_Time = ISNULL(@Last_Execution_Time,'2012-01-01') --默认前50条
/*
SELECT TOP (@TopNum)
    qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
        (case when qs.statement_end_offset = -1
        then DATALENGTH(qt.text)
        else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
    as [查询语句], qt.text [所在存储过程],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE qs.last_execution_time >=@Last_Execution_Time --限定时间
ORDER BY
        [平均消耗CPU 时间(ms)] DESC
*/

	SELECT SUM([平均消耗CPU 时间(ms)])[平均消耗CPU 时间(ms)],
	SUM([总消耗CPU 时间(ms)]) [总消耗CPU 时间(ms)],
	SUM([运行次数])[运行次数],[查询语句],[所在存储过程],
	dbid, dbname,objectid,ObjectName
	FROM (
	SELECT TOP (50)
		qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
		total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

		dbo.SQLSig(SUBSTRING(qt.text,qs.statement_start_offset/2+1, --利用sqlsig函数进行参数化,以屏蔽传来的参数,使之标准化
			(case when qs.statement_end_offset = -1
			then DATALENGTH(qt.text)
			else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1),4000)
		as [查询语句], qt.text [所在存储过程],
		qt.dbid, dbname=db_name(qt.dbid),
		qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
	FROM sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
	WHERE qs.last_execution_time >=CONVERT(VARCHAR(10),GETDATE(),120)
	ORDER BY [平均消耗CPU 时间(ms)] DESC
	)M
	GROUP BY [查询语句],[所在存储过程],dbid, dbname,objectid,ObjectName
	ORDER BY [平均消耗CPU 时间(ms)] DESC

GO

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

--==========================
--创建人: zxs 20120420
--查询当前的锁,及相关sql语句
--==========================
CREATE       PROCEDURE [dbo].[p_curwaitdropindex]
	AS
Select d.database_id,d.name,t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) AS use_Count,t.name AS tb_name,ix.name AS ix_name,sc.name AS col_name
    from sys.dm_db_index_usage_stats ius
	JOIN sys.databases d ON d.database_id=ius.database_id
    JOIN sys.tables t ON ius.object_id = t.object_id
    JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
    JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
    JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
    where user_updates > 10 * (user_seeks+user_scans)
    and ius.index_id > 1
    AND CHARINDEX('merge',t.name) <1
    order by user_updates / (user_seeks+user_scans+1) DESC

GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--==========================
--创建人: zxs 20110728

--==========================
CREATE       PROCEDURE [dbo].[p_curRun]
AS 

	SELECT DB_NAME(r.database_id) DB,s.session_id,
	substring(qt.text,r.statement_start_offset/2,
	(case when r.statement_end_offset = -1
	then len(convert(nvarchar(max), qt.text)) * 2
	else r.statement_end_offset end -r.statement_start_offset)/2
	) as 'SQL statement',
	qt.text batch,s.status,c.client_net_address,s.login_name, s.program_name,s.host_name,
	s.login_time,c.connect_time,s.last_request_start_time, s.last_request_end_time,s.session_id,
	s.host_process_id, s.client_version, s.client_interface_name,c.net_transport, c.net_packet_size,
	r.request_id, r.start_time, r.status, r.command, r.user_id, r.blocking_session_id, r.wait_type,r.wait_time,
	r.last_wait_type, r.wait_resource, r.open_transaction_count,r.transaction_id, r.percent_complete, r.cpu_time, r.reads, r.writes,r.granted_query_memory
	FROM Sys.dm_exec_connections c
	JOIN Sys.dm_exec_sessions s ON c.session_id = s.session_id
	JOIN Sys.dm_exec_requests r ON s.session_id = r.session_id
	outer apply sys.dm_exec_sql_text(r.sql_handle) as qt
	WHERE r.status = 'running' AND s.session_id != @@SPID
	ORDER BY c.client_net_address,s.login_name

GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--==========================
--创建人: zxs 20120420
--查询当前的锁,及相关sql语句
--==========================
CREATE       PROCEDURE [dbo].[p_curlock]
	AS
SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2
) AS SqlStatement
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_exec_sessions AS ES
ON TL.request_session_id = ES.session_id
LEFT JOIN sys.dm_tran_active_transactions AS TAT
ON TL.request_owner_id = TAT.transaction_id
AND TL.request_owner_type = 'TRANSACTION'
LEFT JOIN sys.objects AS OBJ
ON TL.resource_associated_entity_id = OBJ.object_id
AND TL.resource_type = 'OBJECT'
LEFT JOIN sys.partitions AS PAR
ON TL.resource_associated_entity_id = PAR.hobt_id
AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')

LEFT JOIN sys.objects AS PAROBJ
ON PAR.object_id = PAROBJ.object_id
LEFT JOIN sys.indexes AS PARIDX
ON PAR.object_id = PARIDX.object_id
AND PAR.index_id = PARIDX.index_id
LEFT JOIN sys.dm_exec_requests AS REQ
ON TAT.transaction_id = REQ.transaction_id
outer APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST

WHERE TL.resource_database_id = DB_ID()
AND ES.session_id <> @@Spid
AND TL.request_mode like  '%x%'
ORDER BY TL.resource_type
,TL.request_mode
,TL.request_type
,TL.request_status
,ObjectName
,ES.login_name;

GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--==========================
--创建人: zxs 20120420
--查询当前事务
--==========================
CREATE       PROCEDURE [dbo].[p_curtran]
AS

	SELECT CASE WHEN TDT.database_id = 32767
	THEN 'MSSQLSystemResource'
	ELSE DB.name END AS DatabaseName
	,REQ.start_time AS ReqStart
	,TAT.transaction_begin_time AS TransBegin
	,TAT.name AS TransName
	,CASE TDT.database_transaction_type
	WHEN 1 THEN N'Read/Write'
	WHEN 2 THEN N'Read-only'
	WHEN 3 THEN N'System'
	ELSE N'Unkown' END AS TransType
	,CASE TAT.transaction_state
	WHEN 0 THEN N'Not initialized'
	WHEN 1 THEN N'Not started'
	WHEN 2 THEN N'Active'
	WHEN 3 THEN N'Ended'
	WHEN 4 THEN N'DTC active'
	WHEN 5 THEN N'Preparing'
	WHEN 6 THEN N'Committing'
	WHEN 7 THEN N'Being rolled back'
	WHEN 8 THEN N'Rolled back'
	ELSE N'Unkown'
	END AS TransState
	,REQ.[status] AS ReqStatus
	,TDT.database_transaction_log_record_count AS LogRec
	,TDT.database_transaction_log_bytes_used AS LogBytes
	,SES.login_name AS LoginName
	,REQ.wait_type AS ReqWaitType
	,REQ.percent_complete AS [ReqCompl%]
	,REQ.command AS ReqCommand
	,SUBSTRING(EST.text
	,1 + REQ.statement_start_offset / 2
	,(CASE WHEN REQ.statement_end_offset = -1
	THEN LEN(convert(nvarchar(max), EST.text)) * 2
	ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2
	) AS SqlStatement
	FROM sys.dm_tran_active_transactions AS TAT
	INNER JOIN sys.dm_tran_database_transactions AS TDT
	ON TAT.transaction_id = TDT.transaction_id
	INNER JOIN sys.databases AS DB
	ON TDT.database_id = DB.database_id
	LEFT JOIN sys.dm_tran_session_transactions AS TST
	ON TAT.transaction_id = TST.transaction_id
	LEFT JOIN sys.dm_exec_requests AS REQ
	ON TAT.transaction_id = REQ.transaction_id
	LEFT JOIN sys.dm_exec_sessions AS SES
	ON REQ.session_id = SES.session_id
	CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST
	WHERE TAT.transaction_id > 255
	AND ISNULL(REQ.session_id, -1) <> @@SPID
	AND TDT.database_id <> DB_ID(N'tempdb')
	ORDER BY DatabaseName
	,TransBegin
	,TransName;
GO
2015七月27

SSAS数据挖掘

sql server 评论关闭
决策树算法:N个属性决定一个结果,现在给出这N个属性的值,预测下结果。先训练样本,再预测。比如预测有哪些属性的客户最可能购买产品

关联算法:找出属性与属性之间的关系,典型的例子是啤酒与尿布、产品搭配、产品推荐
 
聚类分析:有N个样本,每个样本有不同的属性值,现对进行多类
 
线性回归算法:给出n个点(两个值确定坐标一点),求函数,然后预测下一个,比如给出业绩与员工数,预测下一个业绩
 

时序算法:考虑时间因素,比如给出业绩与月份历史数据,预测下一月的业绩

 
顺序分析与聚类分析:比如客户最先想买什么,再买什么
 
神经网络:找出N个属性与m个属性之间的关系
 
挖掘步奏:
1,建立一视图,2,建立挖掘,指定算法
注:挖掘准确性图表:可以查看与真实数据的差距
挖掘模型预测:据已训练好的数据,选新的数据源测试结果
 
2,在挖掘模型,右键设置算法参数中,可设置数据周期的提示(PERIODICITY_HINT),如一周7天{7}。
MAXIMUM_SERIES_VALUE:预测最大值
MINIMUM_SERIES_VALUE:预测最小值
AUTO_DETECT_PERIODICITY:通过更改 AUTODETECT_SEASONALITY 的值,可以影响生成的时间段的可能数目。可以反复设置,看偏差,选择一最优。
 
 
–sqlserver 查询ssas
EXEC sp_addlinkedserver
@server='LINKED_AS' , – local SQL name given to the linked server
@srvproduct='' , – not used
@provider='MSOLAP' , – OLE DB provider
@datasrc='localhost' , – analysis server name (machine name)
@catalog='MultidimensionalProject2' – default catalog/database
 
 
 FLATTENED转成平面表
SELECT * FROM
OPENQUERY(LINKED_AS ,
  'SELECT FLATTENED  PredictTimeSeries (amount,100) FROM [DW Daily Ship] ')
  

  
—ssas dmx预测
–传入新数据进行预测
SELECT PredictTimeSeries (amount,5,15,EXTEND_MODEL_CASES as a
FROM [DW Daily Hour Ship]
NATURAL  PREDICTION JOIN (
  select '2014-10-21 21:00' as   shipdate,2  as amount
  union select
   '2014-10-21 22:00'as   shipdate,2  as amount
  union
  select '2014-10-21 23:00'as  shipdate,2  as amount
  union
  select '2014-10-21 00:00'as  shipdate,2  as amount
  union
  select '2014-10-21 01:00'as  shipdate,2  as amount
) as t

-访问远程ssas,只有windows验证,所以要在远程服务器上添加当前机的账号密码,并且在远程ssas 实例属性-安全上添加账号
 
–用job运行ssas处理过程

2015七月21

多条件约束

sql server 评论关闭

check可对整个表约束,而WITH CHECK OPTION可以多条件约束,如建立一视图用with check option可以限制约束。
见:
每个开发人员都需要了解的一个SQL技巧

2015七月14

历史数据备份是TSQL还是SSIS

sql server 评论关闭

问题场景:定时的将多张表3个月前的数据迁移到历史库去
多年来一直用JOB+SQL方式,觉得最大的问题有两点
1,版本控制:TSQL难以做到版本控制,技术上很好实现,问题是在正式环境直接打开JOB更改TSQL比较简单,在某些情况下易造成正式环境已完成修改,而开发环境没有修改的情况,特别在管理并没有严格规定流程的前提下。
2,链接服务器:在不同DB服务器迁移数据,需用到链接服务器,链接服务器在JOB迁移的时候可能会有遗漏,需要一个个重建。

现考虑采用的方案是JOB+SSIS
SSIS部署到服务器上,直接修改比较困难,需要在本地以以项目方式进行修改再部署,这样就能将本地的项目纳入到TFS,保证了版本一致性。
缺点是没有直接修改TSQL来得快,但个人觉得也快不多,特别是部署的服务器故障,需要迁移的时候,用SSIS重新部署可能还会快些。

2015六月4

SSAS部署到服务器

sql server 评论关闭

1,在本地发布后,备份还原库到服务器  BI笔记之— SSAS部署的几种方式
2,SSAS预测结果存到表 Creating a Linked Server for Analysis Services
  2.1 在数据库建立SSAS库的链接服务器
  2.2 运行openquery查询  
  select * from openquery([ANALYSIS], 'SELECT FLATTENED predicttimeseries([V Daily Ship].Amount,4) FROM [V Daily Ship]')
  

2015四月8

导出数据库的架构与数据

sql server 评论关闭

要从sqlserver2012降级到sqlserver2008,不能直接降级,可以导出数据再重建。
对于数据量大的情况,先生成脚本再dts即可
对于数据量小的情况,在数据库右键生成脚本,两个下一步后,在高级选项中将要编写脚本的数据的类型设置为架构与数据即可

2015二月6

导出json与xml

sql server 评论关闭

宋大师的文章:在SQL Server中将数据导出为XML和Json http://www.cnblogs.com/CareySon/p/4276828.html
导出json是这个sp:http://jaminquimby.com/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json

2015一月27

profile抓取从本机发出的数据库请求

sql server 评论关闭

用profile抓取本地请求,之前是用spid,用sqlserver客户端可直接看到,但spid每次连接上会改变,找到了hostname,指定为本机名,就可抓到从本机发出的数据库请求。现在保存为模板,双击即可导入到sqlserver2012的profile,记得修改模板中的hostname.

下载:http://enjoyasp.net/enjoytools/callFromMyPC.rar

2014十一月7

ssms表格展示大数据的限制

sql server 评论关闭

工具-选项-查询结果-SQL Server 以网络显示结果中可设置最大非XML数据为65535
但实际最大只能到43679 。
测试:
select replicate(convert(varchar(max),’-'),65535)
解决方法:鉴于xml数据大小可以不限制,可以转成xml来展示
select replicate(convert(varchar(max),’-'),65535)
FOR XML PATH(”)

对于表中nvarchar(max)数据,用select也会有上述问题,解决方法是打开表,copy数据

2014十月14

sqlserver部分包含数据库

SQL Server 2012引入了包含数据库,将登录账号,工作代理,环境打包到一个db里,这样在做迁移的时候就不会丢失。

限制是:
部分包含数据库不能使用复制、更改数据捕获或更改跟踪。
编号过程
绑定到架构的对象,且依赖于可更改排序规则的内置功能
绑定因排序规则更改而导致的变化,包括对对象、列、符号或类型的引用。
复制、变更数据捕获和更改跟踪。

配置方法见: SQL Server 2012中包含的数据库(Contained Database)探索

http://blog.csdn.net/burgess_liu/article/details/7477470

2014九月25

wget使用

sql server 评论关闭

想用sqlserver发短信,做法是做一CLR打到sqlserver中,感觉有些硬,可以用xp_cmdshell+wget来实现。
wget下载:http://www.interlog.com/~tcharron/wgetwin.html

应用:
1,下载网站或文件,支持断点续传
2,get url,比如上面的短信调用或者测试网站是否可以访问,或不能发送短信。
3,post数据。

2014九月22

数据仓库SSAS+SSIS+SSRS

sql server 评论关闭

数据仓库SSAS+SSIS+SSRS

—————————–SSAS————————————-

1,用ssas生成多维度,然后利用excel的data功能可以自组各种维度的报表

2,可设置是否可见,在维度结构 子项-属性-AttributeHierarchyVisible=false

在维度结构 -属性-AttributeMemberName=myname

3,先增加维度,再在此基础上增加多维数据集.多维数据集用来对多个维度指定聚合方式,求和或者计数或者其它.

多维数据集提前聚合生成一次聚合数据,与sqlserver db分开,不用每次再进行数据查询,而关系数据库是每次都要聚合,性能很低

并且建立维度后,大家可共用,防止自己写sql不同的结果

4,多维数据集启用excel,也查以使用excel连接多维数据表

5,加载数据是在sqlserver 维度右键处理进行数据的加载或studiio中点击处理,

或者在多维数据集中的分区页设置更新时间,或者用ssis建立一个analysis services处理任务,用job来定时刷新

BI笔记之—Cube增量处理的一个场景的处理方案

6,Tableau不错,不过服务端收费

7,在多维数据集中的透视中可对度量值进行分组,然后给不同的用户看

8,kimball university:the 10 essential rules of dimensional modeling

9,权限:关系型数据库用视图,分角色

cube:建立多个角色 SQL Server分析服务的权限配置 http://www.cnblogs.com/aspnetx/p/3703335.html

—————————–SSIS——————————-

分区.用switch可以转到其他服务器上,这不费时间,很快

复制-并行数据仓库系统-大数据 规模

并行数据仓库pDw,可让数据库分到多个服务器上 Teradata做的最好

1,lession2:循环文件,连接管理器中的连接属性expression的constring用变量

2,日志事件一般选择onerror on warning,onpostexcute,ontaskfailed

若使用sqlserver记录日志,日志表为sysssislog

3,包部署模型:配置,增加变量,转换为包部署模型,ssis-包配置

好处:统一的日志管理,集中处理,有缓存,速度快

4,资源管理器打开文件夹,bin下面有个ispac文件,可以用来做部署

大表做分区,放到不同的filegroup中,表建立时启用页yasuo,能压缩一半以上,读快,不过写慢一些,用在很少更新的大表上

,然后到一定量时做switchr

alter table sss switch to eee

自己做:动态生成job,动态生成包,完全.net写取数据

解决并行问题,写sql是硬编码,并且不能并发,动态生成job就能解决这个问题

sqlserver到来60t时单台db会到pingjing

//*部署到服务器上*//
1,将SSIS的项目属性-通用属性-项目中的ProtectionLevel修改为用户加密
2,包的属性也做以上修改
3,重新生成后将bin下的ispac文件copy到服务器上
4,服务器连接到数据库引擎在Integration services目录下添加项目及文件夹,然后部署找到ispac文件,录入密码即可。

 

—————————–SSRS————————————–

列存储,对表进行分区,更新时只要drop对应分区即可,不用drop掉整个表

PowerView制作动态图表,PowerView已经可以成为一个强大的BI工具,可以做出功能非常丰富的动态交互图表甚至DashBoard

BISM是一种更加轻量级的模型,它不像CUBE那样复杂,建立的周期和其相比也要短得多,也不需要使用复杂的MDX语句(但也支持MDX),BISM支持使用DAX进行查询,总而言之,BISM是一个更加简单的能够快速提供给业务人员使用

PowerPivot:大数据级的excel,解决大数据量的存储问题

数据量在1000万以内用ssis,以上ETL层自定义框架

前端利用第三方组件自行开发

优点,ETL和UI自己开发

2014九月22

SSDT:数据库部署方案

sql server 评论关闭

1,sqlserver解决方案 ssdt:sqlserver data tools
好处:每次发布的脚本是动态生成的,可以多次发布,即使中间出错,下次也会从新的开始

2,发布-高级可以设置选项
表-属性: 生成操作. 无指不参与生成,也不会部署

3,添加-脚本
后期部署脚本,指在生成脚本后执行
预先部署脚本,指在生成脚本前执行

4,已有数据库,生成源码操作
右键sqlserver项目,导入-数据库

5,真正发布时,会用脚本来执行,命令行执行sqlpackage
可以点生成,资源管理器打开,在bin下有一个dac文件,可用sqlpackage.exe来调用这个文件来部署
这种部署是最佳实践,因为可以部署到多台服务器上,并且可以版本控制,可对checkin的脚本进行发布
redgate是用事务的,会锁住对象,并且不能插入脚本
在项目中生成文件后,将dac文件copy出来,用sqlpackage.exe发布,在进行发布时会自动比较生成脚本
如:
@echo off
set sqlpackage=”C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe”
%sqlpackage% /Action:Publish /SourceFile:E:\mydb\bin\Debug\BRM.sqlproj.dacpac /Profile:E:\Db.publish.xml

6,调用脚本
:r .\Script.sql

script.sql是用添加-脚本-不在内部版本中类型的脚本

7,对于有复制分发的数据库,可以在发布-高级里面指定不更改复制对象
做法是:在其中一台db中指定更改复制对象,其他DB不更改

2014七月30

RESTORE参数查看备份文件信息

sql server 评论关闭
RESTORE除了还原数据库外 ,还能带如下参数查看备份文件的信息
--备份集中包含的一组数据库和日志文件,mdf,ldf文件大小
RESTORE FILELISTONLY FROM DISK='E:\DB\DBBACK\mydb.bak'

--特定备份设备上所有备份集的所有备份标头信息,如备份开始时间与结束时间
RESTORE HEADERONLY FROM DISK='E:\DB\DBBACK\mydb.bak'

--备份设备标识的备份介质的信息
RESTORE LABELONLY FROM DISK='E:\DB\DBBACK\mydb.bak'

--验证备份是否有效
RESTORVERIFYONLYLY FROM DISK='E:\DB\DBBACK\mydb.bak'

参考 :RESTORE 参数
http://msdn .microsoft. com/zh -cn/ library/ms178615 .aspx
2014四月26

自动安装sqlserver2012及补丁

1,配置ini文件,即是手工安装中上一步下一步中做的设置
ini文件关键点说明:
SQLSYSADMINACCOUNTS=".\Administrator"指明了使用当前机器的计算机名,这样就不会因为第一台机器的计算机名复制到其他机器里

FEATURES=SQLENGINE,REPL…:指定要安装、卸载或升级的功能。

安装全部功能
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS,DQC,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK
安装除了Analysis Services,Reporting Services – Native,Reporting Services – SharePoint,Reporting Services Add-in for SharePoint Productsa,Integration Services功能
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,DQC,CONN,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK,MDS
 
对应列表如下:Feature list
 
 

2,执行cmd命令安装

mkdir "d:\Program Files\Microsoft SQL Server"
mkdir "d:\Program Files (x86)\Microsoft SQL Server"
mkdir "d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir"
mkdir "d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir"
.\SQLFULL_x64_CHS\Setup.exe /qs /ACTION=Install /SAPWD="passwordxxxxxxxxxxxxxxx123!$^"  /PID="FH666-Y346V-7XFQ3-V69JM-RHW28"  /IACCEPTSQLSERVERLICENSETERMS   /ConfigurationFile="sql2012.ini"

 
/q:完全没有界面
 
/qs:有界面,界面只是作为显示进度的用途
 
/IACCEPTSQLSERVERLICENSETERMS:接受许可条款
 
/PID:产品密钥  指定 SQL Server 版本的产品密钥。如果未指定此参数,则将使用 Evaluation。

/SAPWD:指定sa的密码

3,安装补丁
补丁.exe /allinstances  /qs /IACCEPTSQLSERVERLICENSETERMS 

参考:您还在用下一步下一步的方式安装SQLSERVER和SQLSERVER补丁吗?

4,ini文件如下

;SQL Server 2012  Configuration File
;安装到D盘,不安装report services,ssis等不用的功能

[OPTIONS]

; 指定安装程序的工作流,如 INSTALL、UNINSTALL 或 UPGRADE。这是必需的参数。 

ACTION="Install"

; 尚未定义命令行参数 ENU 的详细帮助。 

ENU="False"

; 用于控制用户界面行为的参数。有效值对于完整 UI 为 Normal,对于简化的 UI 为 AutoAdvance,为 EnableUIOnServerCore 则跳过 Server Core 安装程序 GUI 块。 

;UIMODE="Normal" 

; 安装程序将不会显示任何用户界面。 

QUIET="False"

; 安装程序将只显示进度,而不需要任何用户交互。 

QUIETSIMPLE="False"

; 指定 SQL Server 安装程序是否应发现和包括产品更新。有效值是 True 和 False 或者 1 和 0。默认情况下,SQL Server 安装程序将包括找到的更新。 

UpdateEnabled="False"

; 指定要安装、卸载或升级的功能。顶级功能列表包括 SQL、AS、RS、IS、MDS 和工具。SQL 功能将安装数据库引擎、复制、全文和 Data Quality Services (DQS)服务器。工具功能将安装管理工具、联机丛书组件、SQL Server Data Tools 和其他共享组件。 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,DQC,CONN,BC,SDK,BOL,SSMS,ADV_SSMS,DREPLAY_CTLR,DREPLAY_CLT,SNAC_SDK,MDS

; 指定 SQL Server 安装程序将获取产品更新的位置。有效值为 "MU" (以便搜索产品更新)、有效文件夹路径以及 .\MyUpdates 或 UNC 共享目录之类的相对路径。默认情况下,SQL Server 安装程序将通过 Window Server Update Services 搜索 Microsoft Update 或 Windows Update 服务。 

UpdateSource="MU"

; 显示命令行参数用法 

HELP="False"

; 指定应将详细的安装程序日志传送到控制台。 

INDICATEPROGRESS="False"

; 指定安装程序应该安装到 WOW64 中。IA64 或 32 位系统不支持此命令行参数。 

X86="False"

; 指定共享组件的安装根目录。在已安装共享组件后,此目录保持不变。 

INSTALLSHAREDDIR="d:\Program Files\Microsoft SQL Server"

; 指定 WOW64 共享组件的安装根目录。在已安装 WOW64 共享组件后,此目录保持不变。 

INSTALLSHAREDWOWDIR="d:\Program Files (x86)\Microsoft SQL Server"

; 指定默认实例或命名实例。MSSQLSERVER 是非 Express 版本的默认实例,SQLExpress 则是 Express 版本的默认实例。在安装 SQL Server 数据库引擎(SQL)、Analysis Services (AS)或 Reporting Services (RS)时,此参数是必需的。 

INSTANCENAME="MSSQLSERVER"

; 为您已指定的 SQL Server 功能指定实例 ID。SQL Server 目录结构、注册表结构和服务名称将包含 SQL Server 实例的实例 ID。 

INSTANCEID="MSSQLSERVER"

; 指定可以收集 SQL Server 功能使用情况数据,并将数据发送到 Microsoft。指定 1 或 True 将启用此功能,指定 0 或 False 将禁用此功能。 

SQMREPORTING="False"

; 用于授予分布式重播控制器服务权限的 Windows 帐户。 

CTLRUSERS=".\Administrator"

; 分布式重播控制器服务使用的帐户。 

CTLRSVCACCOUNT="NT Service\SQL Server Distributed Replay Controller"

; 分布式重播控制器服务的启动类型。 

CTLRSTARTUPTYPE="Manual"

; 分布式重播客户端服务使用的帐户。 

CLTSVCACCOUNT="NT Service\SQL Server Distributed Replay Client"

; 分布式重播客户端服务的启动类型。 

CLTSTARTUPTYPE="Manual"

; 分布式重播客户端服务的结果目录。 

CLTRESULTDIR="d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\ResultDir"

; 分布式重播客户端服务的工作目录。 

CLTWORKINGDIR="d:\Program Files (x86)\Microsoft SQL Server\DReplayClient\WorkingDir"

; 指定是否可将错误报告给 Microsoft 以便改进以后的 SQL Server 版本。指定 1 或 True 将启用此功能,指定 0 或 False 将禁用此功能。 

ERRORREPORTING="False"

; 指定安装目录。 

INSTANCEDIR="d:\Program Files\Microsoft SQL Server"

; 代理帐户名 

AGTSVCACCOUNT="NT Service\SQLSERVERAGENT"

; 安装后自动启动服务。  

AGTSVCSTARTUPTYPE="Automatic"

; CM 程序块 TCP 通信端口 

COMMFABRICPORT="0"

; 矩阵如何使用专用网络 

COMMFABRICNETWORKLEVEL="0"

; 如何保护程序块间的通信 

COMMFABRICENCRYPTION="0"

; CM 程序块使用的 TCP 端口 

MATRIXCMBRICKCOMMPORT="0"

; SQL Server 服务的启动类型。 

SQLSVCSTARTUPTYPE="Automatic"

; 启用 FILESTREAM 功能的级别(0、1、2 或 3)。 

FILESTREAMLEVEL="0"

; 设置为 "1" 可为 SQL Server Express 启用 RANU。 

ENABLERANU="False"

; 指定要用于数据库引擎的 Windows 排序规则或 SQL 排序规则。 

SQLCOLLATION="Chinese_PRC_CI_AS"

; SQL Server 服务的帐户: 域\用户或系统帐户。 

SQLSVCACCOUNT="NT Service\MSSQLSERVER"

; 要设置为 SQL Server 系统管理员的 Windows 帐户。 

SQLSYSADMINACCOUNTS=".\Administrator"

; 默认值为 Windows 身份验证。使用 "SQL" 表示采用混合模式身份验证。 

SECURITYMODE="SQL"

; 将当前用户设置为 SQL Server 2012 Express 的数据库引擎系统管理员。 

ADDCURRENTUSERASSQLADMIN="False"

; 指定 0 禁用 TCP/IP 协议,指定 1 则启用该协议。 

TCPENABLED="1"

; 指定 0 禁用 Named Pipes 协议,指定 1 则启用该协议。 

NPENABLED="0"

; Browser 服务的启动类型。 

BROWSERSVCSTARTUPTYPE="Disabled"

; 添加输入参数 FTSVCACCOUNT 的描述 

FTSVCACCOUNT="NT Service\MSSQLFDLauncher"