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