Category Archives: SQL Server

從 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

串接授權與拒絕

簡單範例:

use tempdb
create table t(c1 int)
create user u without login
create user u2 without login
grant select on t to u with grant option
exec(‘grant select on t to u2 as u’) as user=’u’
exec(‘select * from t’) as user=’u2′
deny select on t to u cascade
/*
Msg 229, Level 14, State 5, Line 18
結構描述 ‘dbo’,資料庫 ‘tempdb’,物件 ‘t’ 沒有 SELECT 權限。
*/
exec(‘select * from t’) as user=’u2′

drop table t
drop user u
drop user u2

透過 SSMS 可以看到授予者

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 只能轉換兩個字集間能轉的部分,其餘都是問號了

透過 RS 呈現 R 的圖形

參考:https://www.mssqltips.com/sqlservertip/4127/sql-server-2016-r-services-display-r-plots-in-reporting-services/

我寫的範例報表 ShowRImage.rdl

透過 T-SQL 呼叫 R Script 可以直接傳回圖檔的 binary 結果,就可以透過影像物件呈現

以如下的 R-Script 為例

EXEC   sp_execute_external_script
      @language = N’R’
     ,@script = N’    df <- inputDataSet; #read input data
                image_file = tempfile(); #create a temporary file
                jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
                hist(df$Ages); #plot the histogram
                dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
                #file() opens a file, in this case the image. rb = read binary
                #readBin() reads binary data. what = described the mode of the data. In this case, it"s raw data. n = maximum number of records to read.
                #data.frame converts the data to a data frame, which is required as output by SQL Server. The result is written to the OutputDataset variable.
                OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
                    ‘
    ,@input_data_1 = N’SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                        FROM [AdventureWorksDW].[dbo].[DimCustomer];’
    ,@input_data_1_name = N’inputDataSet’
    ,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));

從 SSMS 的執行結果可以取到 varbinary(max) 的結果:

image

在 Report Builder 就以這段 T-SQL 當作資料集

image

在報表中加入影像物件,設定來源就是資料庫傳回:

image

透過 Report Builder 和 Report Server 都可以正常執行:

image

image