Tag Archives: SQL

從 Oracle 轉 SQL 資料發生 PK 重複

透過 Linked Server 回 Oracle 查失敗的紀錄,在 Oracle 的資料表內顯然有使用者輸入時,可以敲入 FF 導致眼睛看不到,但卻重複兩筆的 PK

select * from openquery(LinkedServer,’select utl_raw.cast_to_raw(PKCol) b, PKCol from Schema.Table where PKCol like “%DuplicateKey%"‘)

image

無題,效能調教標本

純粹留紀念

image

透過 PowerShell 讀 MSMQ,以及在資料庫內,跨資料表與欄位找尋是否包含某個字串

朋友的需求:

$q= new-object System.Messaging.MessageQueue ‘機器名稱\private$\erptowf’
$msg=$q.Peek()

$s=new-object System.IO.StreamReader $msg.BodyStream

$s.ReadToEnd()

 

declare cur cursor
for select schema_name(o.schema_id) schName, object_name(c.object_id) tbName,c.name from sys.columns c
join sys.objects o on c.object_id=o.object_id
where TYPE_NAME(user_type_id) in (‘varchar’,’nvarchar’,’char’,’nchar’) and o.type=’u’ and o.is_ms_shipped=0

declare @sch sysname,@tb sysname,@col sysname
create table t(schemaname sysname,tablename sysname,columnname sysname)
open cur
fetch cur into @sch,@tb,@col
declare @sql nvarchar(max), @search nvarchar(100)=’58.’
while @@FETCH_STATUS =0
begin
    set @sql=’if exists(select * from [‘ + @sch + ‘].[‘ + @tb + ‘] where [‘ + @col + ‘] like “%’ + @search + ‘%") insert t values(“‘ +@sch + “‘,"‘ + @tb + “‘,"‘ + @col + “‘)’
    –print @sql
    exec(@sql)
    fetch cur into @sch,@tb,@col
end
close cur
deallocate cur
select * from t
drop table t

SQL Server 2016 的交易記錄傳送可能需要手動安裝 .NET Framework 3.5

交易記錄傳送實際上是透過各SQL Server執行個體上的作業呼叫「sqllogship.exe」應用程式,需要小心的是在安裝SQL Server 2016時並未要求一定需要.NET Framework 3.5,而sqllogship.exe工具程式需要.NET Framework 3.5,導致執行失敗卻未將失敗原因寫入作業記錄。

透過命令提示列呼叫sqllogship.exe才會出現缺少.NET Framework 3.5的錯誤訊息。

先用 SSMS 2016 英文版為 SQL Server 2016 中文版設定 AG

到目前為止 SSMS 2016(2016 年 7 或 8 月號,網路 download 寫的是 7 月號,但安裝完程式 show 的是 8 月號)中文版建置 AG 的精靈有 Bug,但可以用英文版 SSMS 幫中文版 SQL Server 建置與設定。

當過濾欄位是 char/varchar 時,透過 SqlCommand 搭配 SqlParameter 時,大量存取會有效能考量

最近幫朋友壓測時,發現的問題。

依據 SQL Server 的 Data Type Precedence https://msdn.microsoft.com/en-us/library/ms190309.aspx

  1. user-defined data types (highest)

  2. sql_varian t

  3. xml

  4. datetimeoffset

  5. datetime2

  6. datetime

  7. smalldatetime

  8. date

  9. time

  10. float

  11. real

  12. decimal

  13. money

  14. smallmoney

  15. bigint

  16. int

  17. smallint

  18. tinyint

  19. bit

  20. ntext

  21. text

  22. image

  23. timestamp

  24. uniqueidentifier

  25. nvarchar (including nvarchar(max) )

  26. nchar

  27. varchar (including varchar(max) )

  28. char

  29. varbinary (including varbinary(max) )

  30. binary (lowest)

代表輸入的條件參數若是 unicode,而目標欄位是 char/varchr/text,將會是欄位內所有的紀錄值先轉型成 unicode,再跟輸入條件比較。

範例程式碼:

use tempdb

drop table if exists t1

create table t1(c1 int identity primary key,c2 char(6),c3 char(10),c4 varchar(50),c5 datetime2(7) default(sysdatetime()),
c6 uniqueidentifier default(newsequentialid()),c7 bigint default(0))

set nocount on

declare @i int=0
while @i<100000
begin
    insert t1(c2,c3,c4) values(right(‘000000’+convert(varchar(6),@i),6),
    right(‘0000000000’+convert(varchar(6),@i),10),rand())
    set @i+=1
end

select * from t1

create index idx on t1(c2,c3,c4)
go
create proc sp @c2 char(6),@c3 char(10),@c4 varchar(50)
as
    select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4
go

簡單利用 console 程式測試

using System;
using System.Data.SqlClient;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection cnn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;integrated security=sspi"))
            {
                SqlCommand cmd = new SqlCommand("select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4");
                cmd.Parameters.Add(new SqlParameter("c2", "000000"));
                cmd.Parameters.Add(new SqlParameter("c3", "0000000000"));
                cmd.Parameters.Add(new SqlParameter("c4", "0.0558658"));
                cmd.Connection = cnn;

                SqlCommand cmd2 = new SqlCommand("select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4");
                cmd2.Parameters.Add(new SqlParameter("c2", System.Data.SqlDbType.Char,6));
                cmd2.Parameters.Add(new SqlParameter("c3", System.Data.SqlDbType.Char, 10));
                cmd2.Parameters.Add(new SqlParameter("c4", System.Data.SqlDbType.VarChar, 50));
                cmd2.Parameters[0].Value = "000000";
                cmd2.Parameters[1].Value = "0000000000";
                cmd2.Parameters[2].Value = "0.0558658";
                cmd2.Connection = cnn;

                SqlCommand cmd3 = new SqlCommand("sp");
                cmd3.CommandType = System.Data.CommandType.StoredProcedure;
                cmd3.Parameters.Add(new SqlParameter("c2", "000000"));
                cmd3.Parameters.Add(new SqlParameter("c3", "0000000000"));
                cmd3.Parameters.Add(new SqlParameter("c4", "0.0558658"));
                cmd3.Connection = cnn;

                cnn.Open();
                long l = DateTime.Now.Ticks;
                int times = 100000;
                for(int i=0;i<times;i++)
                {
                    //exec sp_executesql N’select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4′,N’@c2 nvarchar(6),@c3 nvarchar(10),@c4 nvarchar(9)’,@c2=N’000000′,@c3=N’0000000000′,@c4=N’0.0558658′
                    SqlDataReader dr = cmd.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());

                System.Threading.Thread.Sleep(3000);

                l = DateTime.Now.Ticks;
                for (int i = 0; i <times; i++)
                {
                    //exec sp_executesql N’select c1 from t1 where c2=@c2 and c3=@c3 and c4=@c4′,N’@c2 char(6),@c3 char(10),@c4 varchar(50)’,@c2=’000000′,@c3=’0000000000′,@c4=’0.0558658′
                    SqlDataReader dr = cmd2.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());
                System.Threading.Thread.Sleep(3000);

                l = DateTime.Now.Ticks;
                for (int i = 0; i < times; i++)
                {
                    //exec sp @c2=N’000000′,@c3=N’0000000000′,@c4=N’0.0558658′
                    SqlDataReader dr = cmd3.ExecuteReader();
                    dr.Close();
                }
                Console.WriteLine((DateTime.Now.Ticks – l).ToString());
            }
        }
    }
}

 

若 .NET 未指定 type,會自動賦予 nvarchar,導致欄位為 char 的要轉成 nvarchar 再行比較,以此例而言,也就是每次比較就需要轉 10 萬筆,3 欄位的紀錄值:

image

就單次查詢時,SQL Server 的執行計畫判讀不覺得有效能差,所以彼此占比相同。

但重複執行 10 萬次後,時間上,可以看得出來,因為指定正確的欄位型態,或是 Stored Procedure 只需要輸入參數時轉對參數型態,實際語法比較時,不需逐筆轉換,所以效率較佳。

image

就總 CPU 耗用而言,因為無法平行運算,轉資料型態導致 CPU 要跑得比較久,但只會操死一顆 CPU

image

移轉 Table 到不同 FileGroup

回答朋友的問題:利用 drop_existing 選項,在新 FileGroup 重建叢集索引

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/025825ee-ecbc-4d7c-94ff-0595afc48347/how-to-move-a-table-from-a-file-groupprimary-to-another?forum=transactsql

CREATE unique clustered INDEX pk_testmember ON DBO.資料表

(

欄位 ASC

)

WITH (DROP_EXISTING=ON, ONLINE=ON)

ON 新的 FileGroup

Unicode、Big5或其他的文字編碼?Windows 輸入的是 Unicode,要如何解釋是你告訴 SQL Server

剛朋友問的問題,簡單的邏輯如下:

select ‘a中文煊’ collate SQL_Latin1_General_CP1_CI_AS,Convert(varbinary(10),’a中文煊’  collate SQL_Latin1_General_CP1_CI_AS),
‘a中文煊’ collate Chinese_Taiwan_Stroke_CI_AS,Convert(varbinary(10),’a中文煊’  collate Chinese_Taiwan_Stroke_CI_AS),
N’a中文煊’ collate Chinese_Taiwan_Stroke_CI_AS,Convert(varbinary(10),N’a中文煊’  collate Chinese_Taiwan_Stroke_CI_AS)

image

輸入時,Windows 收到的是 Unicode,且包含延伸字集,如同我現在的選字:

image

當檔案用 Unicode 存時,你都可以看得到,因為是文字編輯器在呈現這些文字圖形與 Unicode 的對應。

但要跟 SQL Server 溝通或存入時,你會告知目標的字集為何,因此上述三個範例,分別要用 Latin、Big5 和 Unicode 呈現,而 Unicode 是 superset,其他的字集較小,SQL Server 只能轉換兩個字集間能轉的部分,其餘都是問號了

調整 Visual Studio 語言

為了安裝 SSDT 2015 August CTP(14.0.50901.0),https://msdn.microsoft.com/zh-tw/mt429383,這對應的是 SQL Server 2016 CTP 2.3

好在 TechDay Demo SSIS 透過 .NET Framework 4.6 可以存取 Always Encrypted DB,但當下只有英文版,而我的 VS 2015 裝的是中文版,因此預設無法安裝這個 SSDT

更改 VS 2015 的語言,透過 控制台 –> 程式和功能 –>  變更

image

從網站下載 Visual Studio 2015 English Language Pack 安裝

http://www.microsoft.com/en-US/download/details.aspx?id=48157

image

裝了 Language Pack 後就可以安裝了 SSDT 2015

image

可以在 Visual Studio 的選項內更改語言

image

在 VS 2015 開 SSIS 2016 CTP 2.3 的專案,可惜尚看不到什麼大變動…

image

透過 .NET Framework 4.6 提供的 SQLClient 存取 SQL Server 2016 CTP 2.3 提供的 Always Encrypted 資料

image

簡單透過 Column Encryption Setting 連接設定在 SSIS 檢視 Always Encrypted 解密後的資料

image

將 SQL Server Audit 事件寫入 Windows 安全性記錄檔

照 MSDN:將 SQL Server Audit 事件寫入安全性記錄檔https://msdn.microsoft.com/zh-tw/library/cc645889.aspx#auditpolAccess 設定,但以下指令會出錯:

auditpol /set /subcategory:"application generated" /success:enable /failure:enable
因為 :"application generated" 要改成繁中,因此需要找到中文的列表:

image

image

此外,若 SSMS 不能讀,有可能是需要 Run as administrator…

image