SSMS操作資料表欄位效能問題

使用Microsoft SQL Server Management Studio(本文之後都簡稱為SSMS)圖形介面操作帶給我們許多方便，但有些時候應該直接以T-SQL語法下達，以下是以資料表新增欄位來說明效能問題

原始資料表schema

CREATE TABLE [dbo].[tbl]
(
    [col1] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
    [col2] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]

我們針對上述的資料表tbl新增欄位col3，分別以SSMS和T-SQL操作並以SQL Server Profiler錄製以了解背後運作過程

SSMS操作畫面


SSMS錄製結果

CREATE TABLE dbo.Tmp_tbl
(
    col1 nvarchar(50) NOT NULL,
    col2 nvarchar(50) NOT NULL,
    col3 nvarchar(50) NOT NULL
)  ON [PRIMARY]

IF EXISTS(SELECT * FROM dbo.tbl)
    EXEC('INSERT INTO dbo.Tmp_tbl (col1, col2)
        SELECT col1, col2 FROM dbo.tbl WITH (HOLDLOCK TABLOCKX)')

DROP TABLE dbo.tbl

EXECUTE sp_rename N'dbo.Tmp_tbl', N'tbl', 'OBJECT'

T-SQL操作畫面


T-SQL錄製結果

alter table tbl
    add col3 nvarchar(50) not null

由上面的結果我們可以發現利用SSMS新增欄位時會執行四個步驟
1.建立以Tmp_開頭的資料表
2.將原本的資料表資料寫入Tmp_資料表
3.drop原本資料表
4.將Tmp_資料表改名

如果要新增欄位的資料表本身含有大量資料，此時就不適合利用SSMS應該改以直接下達T-SQL以減輕系統負載，另外變更資料表欄位類型也跟新增欄位執行相同的步驟。

利用程式讀取Access資料

這篇主要是整理如何利用程式連接Access檔案，以下為微軟針對OleDb、Odbc範例

OleDb

using System;
using System.Data;
using System.Data.OleDb;

class Program
{
  static void Main()
  {
      string connectionString = GetConnectionString();
      string queryString =
          "SELECT CategoryID, CategoryName FROM Categories;";
      using (OleDbConnection connection =
                 new OleDbConnection(connectionString))
      {
          OleDbCommand command = connection.CreateCommand();
          command.CommandText = queryString;

          try
          {
              connection.Open();

              OleDbDataReader reader = command.ExecuteReader();

              while (reader.Read())
              {
                  Console.WriteLine("\t{0}\t{1}",
                      reader[0], reader[1]);
              }
              reader.Close();
          }
          catch (Exception ex)
          {
              Console.WriteLine(ex.Message);
          }
      }
  }

  static private string GetConnectionString()
  {
      // To avoid storing the connection string in your code,
      // you can retrieve it from a configuration file.
      // Assumes Northwind.mdb is located in the c:\Data folder.
      return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
          + "c:\\Data\\Northwind.mdb;User Id=admin;Password=;";
  }
}

Odbc

using System;
using System.Data;
using System.Data.Odbc;

class Program
{
   static void Main()
   {
       string connectionString = GetConnectionString();
       string queryString =
           "SELECT CategoryID, CategoryName FROM Categories;";
       using (OdbcConnection connection =
                  new OdbcConnection(connectionString))
       {
           OdbcCommand command = connection.CreateCommand();
           command.CommandText = queryString;

           try
           {
               connection.Open();

               OdbcDataReader reader = command.ExecuteReader();

               while (reader.Read())
               {
                   Console.WriteLine("\t{0}\t{1}",
                       reader[0], reader[1]);
               }
               reader.Close();
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }
       }
   }

   static private string GetConnectionString()
   {
       // To avoid storing the connection string in your code,
       // you can retrieve it from a configuration file.
       // Assumes Northwind.mdb is located in the c:\Data folder.
       return "Driver={Microsoft Access Driver (*.mdb)};"
           + "Dbq=c:\\Data\\Northwind.mdb;Uid=Admin;Pwd=;";
   }
}

另外微軟針對Office 2007 Access檔案格式有進行修改，如使用的資料來源為2007格式，可參考wiki連線範例

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\myFolder\myAccess2007file.accdb;
Persist Security Info=False;

相關連結

http://msdn.microsoft.com/zh-tw/library/dw70f090(v=vs.80).aspx

http://zh.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

SQL 2005 PIVOT多欄彙總

微軟的SQL Server產品在SQL 2005開始就支援PIVOT，但從線上叢書通常看到PIVOT範例只有一個彙總欄位，以下的語法是利用SQL 2005的範例資料庫AdventureWorks，呈現2001-07-01到2001-07-07銷售明細產品每日的訂購數加總及產品每日的銷售加總

SELECT

ProductName,

SUM(ISNULL([1_Q], 0)) [1_Q], SUM(ISNULL([2_Q], 0)) [2_Q], SUM(ISNULL([3_Q], 0)) [3_Q], SUM(ISNULL([4_Q], 0)) [4_Q], SUM(ISNULL([5_Q], 0)) [5_Q], SUM(ISNULL([6_Q], 0)) [6_Q], SUM(ISNULL([7_Q], 0)) [7_Q],

SUM(ISNULL([1_A], 0)) [1_A], SUM(ISNULL([2_A], 0)) [2_A], SUM(ISNULL([3_A], 0)) [3_A], SUM(ISNULL([4_A], 0)) [4_A], SUM(ISNULL([5_A], 0)) [5_A], SUM(ISNULL([6_A], 0)) [6_A], SUM(ISNULL([7_A], 0)) [7_A]

FROM

(

SELECT

p.Name AS ProductName,

SUM(d.OrderQty) AS OrderQty,

SUM(d.LineTotal) AS LineTotal,

CAST(DAY(d.ModifiedDate) AS VARCHAR) + '_Q' AS D1,

CAST(DAY(d.ModifiedDate) AS VARCHAR) + '_A'AS D2

FROM Sales.SalesOrderDetail AS d

INNER JOIN Production.Product AS p ON d.ProductID = p.ProductID

WHERE d.ModifiedDate BETWEEN '2001-07-01' AND '2001-07-07'

GROUP BY p.Name, d.ModifiedDate

) p

PIVOT

(

SUM (OrderQty)

FOR D1 IN

( [1_Q], [2_Q], [3_Q], [4_Q], [5_Q], [6_Q], [7_Q])

) AS pvt1

PIVOT

(

SUM(LineTotal)

FOR D2 IN

( [1_A], [2_A], [3_A], [4_A], [5_A], [6_A], [7_A])

) AS pvt2

GROUP BY ProductName

上面的語法有用到一些小技巧，因為要求出每日的訂購數及銷售加總，所以日期欄位需要二個，一個給訂購數一個給銷售加總，這裡我們用D1代表訂購數的日期D2代表銷售加總的日期，另外SELECT清單需要14個欄位，其中7個欄位代表1~7日的訂購數，其餘7個欄位代表1~7日的銷售加總數，為了有所區別在訂購數的日期後加上_Q如1_Q，銷售加總日期後加上_A如1_A，最後注意在SUM函數裡要用ISNULL避免加總時因為有值為NULL造成結果為NULL。

table-layout

在網頁中我們常用table來呈現資料，但有時儲存格的內容有較長的單字或數字超過欄位寬度的設定，這時就會發生原先版面的設計跑掉，那我們應如何解決這個問題呢？答案就是利用table-layout:fixed; word-break:break-all，下面我們將以圖例說明

table總寬度為200px，有二個欄位各100px

網頁呈現畫面，雖然欄位設定為各100px，但由於數字的長度已大於欄位的寛度，所以版面跑掉。

修改原始碼，在table的style增加table-layout:fixed

網頁呈現畫面，這裡注意雖然版面正常了，但數字部份被截掉一些

再次修改原始碼，增加word-break:break-all

網頁呈現畫面

在上方的圖例是以IE 6做為測試用的瀏覽器，如果有興趣的人也可以測試其他瀏覽器，另外word-break:break-all的語法是以每個字完做結尾，如果網頁內容主要是以英文為主並且是給西方人觀看，這時候單字因為會被強迫折行，造成語意不清，所以使用上要注意。

將USB隨身碟由FAT32格式轉成NTFS

會寫這篇的原因是經常有朋友問我，為什麼他們的檔案從PC Copy到隨身碟會發生錯誤，我第一個直覺通常會問"你的檔案有沒有超過4G"，在我們日常生活中使用的隨身碟通常格式都為FAT32，而FAT32有個限制就是單一檔案無法超過4G，那有沒有辦法突破這個限制，答案是有的，以下提供二種方法

方法1：轉換FAT32=>NTFS

以XP系統為例，在[開始]=>[執行]輸入cmd，此時會出現命令提示字元視窗，再輸入

convert 隨身碟磁碟機代號: /fs:ntfs

方法2：格式化

以XP系統為例，在系統預設的情況下是無法格式化隨身碟為NTFS，必須要經由一些設定，設定如下

1.插入隨身碟

2.在[我的電腦]按右鍵點選[電腦管理]再點[選裝置管理員]，選擇[磁碟機]中的隨身碟裝置

3.按右鍵選擇[內容]，切換至[原則]頁籤設定[效能最佳化]

4.在格式化隨身碟時[檔案系統]選擇NTFS

那方法1與方法2有什麼差異呢?差異在方法2無法保存隨身碟的資料，如果隨身碟有資料時可選擇方法1

三門問題

有許久沒有寫blog了，會寫這篇是因為上課時聽到老師精闢的解說，所以借花獻佛分享給大家，以下是三門問題的解說

為遊戲節目，三個門中其中一個門後方有車子，其餘二個門為山羊，參賽者先選擇一個門後，主持人會開啟另一個門會出現山羊(主持人一定會開門且出現的一定是山羊)，當主持人問參賽者時，參賽者是否要換

門後的東西：羊1、羊2、車

換可獲得機率為66%
if 參賽者原選羊1 then 車 (主持人開羊2)
if 參賽者原選羊2 then 車 (主持人開羊1)
if 參賽者原選車 then 羊 (主持人開羊)

不換可獲得機率為33%
if 參賽者原選羊1 then 羊1 (主持人開羊2)

if 參賽者原選羊2 then 羊2 (主持人開羊1)
if 參賽者原選車 then 車 (主持人開羊)

原本三個門選一個得到車子的機率為1/3，但在主持人會開另一個門且一定為羊，並給予參賽者更換門的機會，參賽者如果選擇換，得到車子的機率會從33%->66%，雖然網路上有其他人的論點為50%，但我認為是66%，有興趣的看倌不訪上網查詢三門問題。

如何檢查圖檔是否存在遠端主機

最近負責的案子，有些圖檔是來自遠端的Web主機，使用者希望如果遠端圖檔不存在時能以自訂的預設圖檔取代，以下的程式正是因應此需求

步驟1.

using System.Net;

步驟2

public static bool CheckWebReference(string url)

{

bool exists = false;

WebRequest request = WebRequest.Create(url);

request.Proxy = null;

try

{

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

exists = true;

response.Close();

}

catch

{

exists = false;

}

request.Abort();

return exists;

}

上方的url是要檢查的網路資源網址，ex: http://xxx.xxx.xxx/xxx/xxx.png

相關參考