軟體開發(軟件開發)

網智數位主要提供套裝及客製化的軟體系統解決方案,專為客戶量身訂做客製化的軟體,達成客製化、智慧化及網路化的管理功能。

室內設計、裝潢、窗簾報價估算軟體

網智數位主要提供套裝及客製化的軟體系統解決方案,針對室內設計師、木工、裝潢業產業,量身訂做客製化的軟體,達成客製化、智慧化及網路化的商用軟體。

商用軟體-客製化設計

網智數位主要提供套裝及客製化的軟體系統解決方案,專為客戶量身訂做客製化的軟體,達成客製化、智慧化及網路化的管理功能。

IOT 物聯網-系統開發

根據客戶實際狀況,結合雲端與載具進行客製化物聯網IOT導入與軟體開發

雲端VPS虛擬主機租用

我們的雲端VPS虛擬主機是採用雲端(虛擬化)技術所開發之全新雲端伺服器服務,可以選擇多種作業系統(Windows、Linux等),客戶可載入自訂的應用環境,執行自己所要提供的網路服務,我們的雲端服務可為您的網站提供最完美的解決方案。

ERP軟體客製化導入

ERP軟體客製化導入,室內設計、營造業、裝潢、木作工程、系統櫃工程、會計系統,全面提升公司管理營運效率。

搜尋引擎最佳化SEO

搜尋引擎最佳化(SEO)不僅能提高網站在搜尋結果的排名,更能帶來大量對我們產品或服務真正有需求的訪客。SEO 最棒的特質之一就是不像廣告一樣亂槍打鳥而導致用戶的反感,反而更能提升點閱率跟成交率喔。

服務宗旨

網智數位主要提供套裝及客製化的軟體系統解決方案,專為客戶量身訂做客製化的軟體,達成客製化、智慧化及網路化的管理功能。

我們的成立宗旨就是要以最猛的IT技術讓這個世界更Smart,在我們貫徹我們裡想的同時,我們希望可以把我們所開發的系統帶給台灣的中小企業,除了要推薦好的東西之外,我們也希望做點改變,所以我們的第一目標就是要使用最好用的系統再加上您寶貴的創意,不僅僅可以節省你大量的荷包,還可以有一個像樣的網站。我們可以幫你做的有

企業管理
  • 策略管理
  • 目標管理
  • 行銷管理
  • 財會管理
  • ERP導入
  • 企業流程自訂
資訊管理
  • 網站架設
  • 虛擬化/雲端架設
  • 主機代管
  • 私有雲建制與導入
軟體開發
  • UML設計
  • 版本控管
  • 企業軟體開發
  • APP開發
  • 網頁設計
資訊安全
  • 網頁弱點掃描
  • 主機弱點掃描
  • 木馬檢測
  • 資安鑑識
  • 設計網路架構
  • 資安監控
行銷
  • 關鍵字SEO
  • 社群網路行銷
  • 部落格行銷
  • FaceBook 粉絲團
其他
  • 協助企業申請Google Email
好玩工具開發

講出你的創意吧!沒有甚麼是資訊辦不到的

顯示具有 資料庫 標籤的文章。 顯示所有文章
顯示具有 資料庫 標籤的文章。 顯示所有文章

2014年11月2日 星期日

【EntityFramework 實戰】 一對多關聯

     這篇教學文件,我想一步一步的教讀者如何利用 EntityFramework 來寫一對多 (One-To-Many) 關聯 Table的程式碼技巧,並且分別利用 Entity SQL
LINQ To SQL  的方式來讀取資料庫。

在實例個案,我們常常會遇到如下的資料表一對多關聯

(SQL 語法)
  1. CREATE TABLE Employee
  2. (
  3.   [No] NVARCHAR( 12) NOT NULL PRIMARY KEY,
  4.   HireDate Datetime not null default(getdate ()),
  5.   Name NVARCHAR( 12) NOT NULL
  6. )

  7. CREATE TABLE EmployeeSalary
  8. (
  9.  EmpNo NVARCHAR(12) NOT NULL,
  10.  PayYear INT NOT NULL,
  11.  PayMonth INT NOT NULL,
  12.  bonus DECIMAL(18,2) NOT NULL,
  13.  constraint PK_EmployeeSalary primary key (EmpNo,PayYear,PayMonth),
  14.  constraint FK_EmployeeSalary foreign key (EmpNo)
  15.       references Employee ([No])
  16. )


          在上述案例,我們可以知道一個員工(Employee)在每年的每個月有可能因為績效達到某些企業設定的條件,因此可以得到應領取的獎金(EmployeeSalary) , 但也有可能某員工為新進員工,所以雖然在 Employee 資料表存在記錄,但 EmplyeeSalary 卻不存在任何記錄,如果此時企業的系統應用要求,列出所有員工(不論是否已有任何獎金),如有獎金也顯示出在前端界面,我們該如何利用 EntityFramework 去實戰這個很普遍到不行的案例呢? 下面我就示範出比較適當的程式技巧。

首先在此我一樣是利用 Code-First Style 來操控 EntityFramework , 並前端界面我利用Windows Form來示範,前端界面圖示如下

在寫入資料 Button ,我先示範利用 EntityFramewok,來寫入所需的測試資料

     1:    private void btnInsert_Click(object sender, EventArgs e)  
     2:          {  
     3:              using (DBEntities entities = new DBEntities())  
     4:              {  
     5:                  Employee emp1 = new Employee()  
     6:                                  {  
     7:                                      No = "001", //員工編號  
     8:                                      HireDate = DateTime.Parse("2014/05/01"), //僱用日期  
     9:                                      Name = "湯姆克魯斯", //員工姓名                                       
    10:                                  };  
    11:                  EmployeeSalary salary1 = new EmployeeSalary()  
    12:                                           {  
    13:                                               bonus = 1000, //獎金  
    14:                                               PayYear = 2014, //年  
    15:                                               PayMonth = 9, //月                                                
    16:                                           };  
    17:                  emp1.EmployeeSalary.Add(salary1);  
    18:     
    19:                  Employee emp2 = new Employee()  
    20:                  {  
    21:                      No = "002", //員工編號  
    22:                      HireDate = DateTime.Parse("2014/06/01"), //僱用日期  
    23:                      Name = "李奧納多", //員工姓名                                       
    24:                  };  
    25:                  EmployeeSalary salary2_1 = new EmployeeSalary()  
    26:                  {  
    27:                      bonus = 1000, //獎金  
    28:                      PayYear = 2014, //年  
    29:                      PayMonth = 9, //月                                                
    30:                  };  
    31:                  emp2.EmployeeSalary.Add(salary2_1);  
    32:                  EmployeeSalary salary2_2 = new EmployeeSalary()  
    33:                  {  
    34:                      bonus = 6000, //獎金  
    35:                      PayYear = 2014, //年  
    36:                      PayMonth = 10, //月                                                
    37:                  };  
    38:                  emp2.EmployeeSalary.Add(salary2_2);  
    39:     
    40:     
    41:                  Employee emp3 = new Employee()  
    42:                  {  
    43:                      No = "003", //員工編號  
    44:                      HireDate = DateTime.Parse("2014/10/01"), //僱用日期  
    45:                      Name = "基諾李維", //員工姓名                                       
    46:                  };  
    47:     
    48:                  entities.Employee.Add(emp1);  
    49:                  entities.Employee.Add(emp2);  
    50:                  entities.Employee.Add(emp3);  
    51:     
    52:                  entities.SaveChanges();  
    53:              }  
    54:          }  

執行寫入資料按鈕後,後端資料庫結果為如下
 

測試資料有了以後,開始我們可以分別利用 Entity SQL 與 LINQ To Entities 兩種技巧來讀取

在 顯示資料(Entity SQL) 按鈕 ,程式如下
  
     1:   private void button1_Click(object sender, EventArgs e)  
     2:          {  
     3:              this.richTextBox1.Clear();  
     4:     
     5:              using(DBEntities entites = new DBEntities())  
     6:              {  
     7:                  var eSQL = @"SELECT A.No,A.Name,A.HireDate,B.PayYear,B.PayMonth,B.bonus  
     8:                                FROM Employee AS A OUTER APPLY A.EmployeeSalary AS B   
     9:                                ORDER BY A.No ";  
    10:                  var allEmpSalaryRecords = ((IObjectContextAdapter)entites).ObjectContext.CreateQuery<DbDataRecord>(eSQL).ToList();  
    11:     
    12:                  this.richTextBox1.AppendText("所有員工與獎金明細表:\r\n");  
    13:                  this.richTextBox1.AppendText("-------------------------------------------------------------------\r\n");  
    14:     
    15:                  //開始讀取  
    16:                  foreach(var empRecord in allEmpSalaryRecords)  
    17:                  {  
    18:                      if(empRecord["bonus"] != DBNull.Value)  
    19:                      {  
    20:                          this.richTextBox1.AppendText( "員工姓名 : " + empRecord["Name"].ToString() + " \r\n");  
    21:                          this.richTextBox1.AppendText("(年/月)  " + empRecord["PayYear"].ToString() + "/" + empRecord["PayMonth"].ToString()   
                                                                           + " 獎金為 " + empRecord["bonus"].ToString() + " \r\n");  
    22:     
    23:                      }  
    24:                      else  
    25:                      {  
    26:                          this.richTextBox1.AppendText("員工姓名 : " + empRecord["Name"].ToString() + " \r\n");  
    27:                          this.richTextBox1.AppendText(" 未有獎金 \r\n");  
    28:                      }  
    29:                  }  
    30:     
    31:              }  
    32:          }  

執行結果如下


在 顯示資料(LINQ) 按鈕 ,程式如下
  1. private void button2_Click(object sender, EventArgs e)
  2. {
  3.      this.richTextBox1.Clear();

  4.      using (DBEntities entities = new DBEntities())
  5.      {
  6.           var allEmpSalaryRecords = (from emp in entities.Employee
  7.                                            from empSalary in emp.EmployeeSalary.DefaultIfEmpty()
  8.                                            orderby emp.No
  9.                                            select new
  10.                                            {
  11.                                                 emp.Name,
  12.                                                 PayYear = (int?)empSalary.PayYear,
  13.                                                 PayMonth = (int?)empSalary.PayMonth,
  14.                                                 Bonus = (decimal?)empSalary.bonus
  15.                                            }).ToList();

  16.           //開始讀取
  17.           foreach (var empRecord in allEmpSalaryRecords)
  18.           {
  19.                if (empRecord.Bonus.HasValue)
  20.                {
  21.                     this.richTextBox1.AppendText("員工姓名 : " + empRecord.Name + " \r\n");
  22.                     this.richTextBox1.AppendText("(年/月)  " + empRecord.PayYear.ToString() + "/" + empRecord.PayMonth.ToString() 
  23.                                                                          + " 獎金為 " + empRecord.Bonus.ToString() + " \r\n");

  24.                }
  25.                else
  26.                {
  27.                     this.richTextBox1.AppendText("員工姓名 : " + empRecord.Name + " \r\n");
  28.                     this.richTextBox1.AppendText(" 未有獎金 \r\n");
  29.                }
  30.           }
  31.      }
  32. }
執行結果


透過以上2種讀取技巧,我追蹤EntityFramework 6都是自動產生以下 SQL 語法
  1. SELECT 
  2.     [Project1].[C1] AS [C1], 
  3.     [Project1].[No] AS [No], 
  4.     [Project1].[Name] AS [Name], 
  5.     [Project1].[HireDate] AS [HireDate], 
  6.     [Project1].[PayYear] AS [PayYear], 
  7.     [Project1].[PayMonth] AS [PayMonth], 
  8.     [Project1].[bonus] AS [bonus]
  9.     FROM ( SELECT 
  10.         [Extent1].[No] AS [No], 
  11.         [Extent1].[HireDate] AS [HireDate], 
  12.         [Extent1].[Name] AS [Name], 
  13.         [Extent2].[PayYear] AS [PayYear], 
  14.         [Extent2].[PayMonth] AS [PayMonth], 
  15.         [Extent2].[bonus] AS [bonus], 
  16.         1 AS [C1]
  17.         FROM  [dbo].[Employee] AS [Extent1]
  18.         LEFT OUTER JOIN [dbo].[EmployeeSalary] AS [Extent2] ON [Extent1].[No] = [Extent2].[EmpNo]
  19.     )  AS [Project1]
  20.     ORDER BY [Project1].[No] ASC

以上 One-To-Many的EntityFramework 6 讀取方式,完整介紹完畢,希望對讀取有所幫忙

2014年10月29日 星期三

Entity Framework 實戰 - Table Per Hierarchy Inheritance (TPH)實作

      今天我想抽空來寫篇關於 .Net EntityFramework 與 資料庫單一資料表(Table)的實作,這次實作機制方法為 Table Per Hierarchy Inheritance(TPH),顧名思義就是在底層資料庫的一個資料表對應了程式的資料模型多個實體,
我直接舉個例子來解釋資料庫與領域模型間的圖型。

資料庫 Table 圖例

然而在系統設計時,領域模型我們採取了一下設計


在上圖系統領域模型中, Media 為父類別 (Parent Class) ,而 Article 與 Video、Photo皆為繼承 Media 父類別的子類別(Child Class)


PS.注意,在 Media 類別,並沒有 Media 資料表的欄位 MediaType , 而 MediaType欄位的值在資料表是用來識別被繼承的子類別是屬於那個型別,
如 MediaType = "Article" , 此時代表為 Article Class ,依次為 MediaType = "Video" 代表為 Video Class ; MediaType = "Photo" 代表為 Photo Class 。

現在我開始撰寫 Media 、Article 、Video、Photo Class Code


因為 我採用 Code-First 的方式去實作 EntityFramework ,所以底下我設計一個 DBEntities Class ,並讓它繼承了 DbContext ,
這樣後續我就可以透過 DBEntitis 去處理後續的實體增修 CRUD。

上述步驟都完成後,接下來可以來測試透過 DBEntities 來進行新增并實際寫入資料表。
在這邊我利用 Windows Form 當做前端界面來實作


而在 寫入(存檔)的  Button  ,我寫了以下程式
    1:          private void button1_Click(object sender, EventArgs e)  
    2:         {  
    3:             //進行存檔
    4:             using(var context = new DBEntities())  
    5:             {  
    6:                 Article article = new Article() { Title = "網智數位行銷手冊秘笈", MediaID = "001" };  
    7:                 context.Media.Add(article);  
    8:   
    9:                 Video video = new Video() { Title = "網智ERP教學影片", MediaID = "002" };  
   10:                 context.Media.Add(video);  
   11:   
   12:                 Photo photo = new Photo() { Title = "網智數位設計", MediaID = "003" };  
   13:                 context.Media.Add(photo);  
   14:   
   15:                 context.SaveChanges();  
   16:   
   17:             }  
   18:         }  

點擊 寫入(存檔)Button後,此時在去後端資料庫檢查,我們可以看到結果如下

此時,資料表正確無誤的寫入 3 筆資料列,並且 MediaType 欄位,也會自動寫入對應的值(Article、Video、Photo)。




網智數位-軟體開發


2014年7月12日 星期六

免費版 WAF ModSecurity 增加網站安全性

在大部分成功被駭客入侵的網站案例中,大多都屬於網頁程式設計師的疏忽,或本身缺乏安全的意識而開發的軟體,很容易造成網頁主機被入侵成功的危機,如果不確定自己有沒有被入侵,可以參考我之前寫的5個自我檢視資料庫之安全性的方法,先自行檢測看看。

一般為了預防像是 XSS、SQL Injection 此類的安全性漏洞,大多會購買WAF(Web Application Firewall,網頁應用程式防火牆)來阻絕駭客,但類似的產品,價格往往相當的昂貴,絕非一般中小企業所能負擔。 相關 WAF 資訊可以參考:


或許有人會說為什麼要砸大錢買WAF?WAF不過是至標不治本的東西,程式本身有漏洞應該是將漏洞修補起來就好了,當然這是正確的觀念,如果你的維護的是小系統可能還可以迅速做修補,但如果是大型的 ERP 系統在現實上就沒有那麼簡單了,而且在修補的過程中沒有任何防護的話,還是有一段的空窗期喔。

為了獲得最好的安全效果,我們需要雙管齊下,一方面必須提高管理者和開發者的安全意識,另一方面儘可能提高應用系統的安全性。

因此,本文將介紹這款開放原始碼的軟體 ModSecurity,免費為您的網站主機多加一層保護。

ModSecurity 簡介

ModSecurity是一個開放原始碼的WAF(Web Application Firewall,網頁應用程式防火牆),是一個入侵偵測與防護引擎,它可以作為你的服務器基礎安全設施,目前支援Apache、IIS、Nginx 等..可增強這些 Web 伺服器的安全性和保護Web應用程式避免遭受來自已知與未知的攻擊。

ModSecurity功能特點

  1. 即時監控和攻擊檢測
  2. 攻擊防禦和即時修補
  3. 靈活的規則引擎
  4. 嵌入式模式部署
  5. 基於網絡的部署
  6. 可移植性

ModSecurity新特性


  1. 增加狀態報告(Status Reporting)
  2. 增加JSON解析器
  3. 添加@detectXSS模塊
  4. 連接限制(SecConnReadStateLimit/SecConnWriteStateLimit)支持黑白名單
  5. 增加新變量FULL_REQUEST和FULL_REQUEST_LENGTH,支持對請求的所有內容進行規則限制。
簡單的說 ModSecurity 可保護您的 Web 應用程式免受複雜的攻擊,阻止線上身份竊取,並防止資料經由應用程式洩露,建議系統管理者都可以安裝這個防護措施。

2014年3月14日 星期五

5個自我檢視資料庫之安全性的方法



資料竊取越來越普遍,黑市裡信用卡資料的價錢從2006年一筆10元變成2009年幾毛錢。客戶對於電子商務、網路銀行與其他電子商業活動失去信心。同時駭客竊取資料的方法也越來越聰明,造成越來越多企業成為他們的犧牲品。至2009年時法令與法規以更嚴格地保護消費者,但仍不斷發生新的事件。在Verizon Business Data Breach Investigations Report報告中,研究過去五年內的600個事件,其中SQL Injection是單一攻擊中獲取大量資料的手法。

這個結果並不令人驚訝!若設計網站應用程式的成員不瞭解SQL Injection攻擊時,是很容易發生的。

何謂SQL Injection?
SQL Injection 應稱為SQL 指令植入式攻擊,主要是屬於 Input Validation 的問題。中文翻譯為『資料隱碼』攻擊。SQL Injection 攻擊法並非植入電腦病毒,它是描述一個利用寫入特殊 SQL 程式碼攻擊應用程式的動作。換言之,只要提供給使用者輸入的介面,又沒有做到相當嚴密的輸入資料型態管制,就有可能會遭受這種行為的攻擊。

SQL Injection攻擊可能會對企業造成重大與昂貴的損失。攻擊的目標是資料庫,其中儲存了員工、客戶的資料。這類型的攻擊利用應用程式中的漏洞,操作從瀏覽器輸入的SQL查詢。 在SQL Injection攻擊中,惡意使用者可發送任意輸入至伺服器且假冒web應用程式產生與原先不同的SQL描述。

因此資料庫伺服器會執行並取得與原先不同的結果。SQL Injection攻擊是最常對資料庫獲得未經授權的存取與資料操作。

如果不確定自己的資料庫安不安全或有沒有被入侵成功建議可以按照以下的五個作法自行檢視。


1.查詢SQL Server系統內帳號資訊

如果不幸被SQL Injection 很有可能會被駭客偷偷新增一個sa權限的帳號,即便未來SQL Injection漏洞被修復了,駭客可能還是可以透過其他管道連到資料庫,等於變相開了一個洞給駭客使用,所以我們先來查詢資料庫裡的所有使用者,是否有異常帳號存在。

select * from sysusers

可在結果欄位按滑鼠右鍵選擇「儲存結果」,將檔案匯出進行分析。




2.檢視交易紀錄檔

Transaction 一觸發會建立一個暫時性 Log ,暫存性Log會記錄交易中,修改資料的過程和內容,在自我檢查時建議也可以利用 Transaction Log Viewer 工具看看交易記錄檔中是不是有些惡意的SQL 語法,去Google就可以找到類似的工具,不過都是要錢的,備份方式如下。

—UI資料庫備份
BACKUP DATABASE [Arcsight] TO  DISK = N'D:\Arcsight.bak

(請自行修改資料庫名稱及備份路徑)

—UI交易記錄檔備份
BACKUP LOG [Arcsight] TO DISK = N'D:\Arcsight.bak' WITH NO_TRUNCATE
(請自行修改資料庫名稱及備份路徑)


或可以使用SQL Server Management Studio 圖形介面中設定備份


選擇完整備份


備份完整備份後在選擇交易記錄備份




3.停用系統擴充預存程序

檢查是否有啟用xp_cmdshell
系統延伸預存程序是存儲在SQLServer中的預先寫好的SQL語句集合, 其中危險性最高的程序就是xp_cmdshell了,它可以執行操作系統的任何指令,如果被 SQL Injection 駭客就可透過 xp_cmdshell 取得整台電腦的控制權。

檢測語法:
SELECT * FROM sys.configurations WHERE [name]='xp_cmdshell' ORDER BY name 

如果value = 1代表有啟用
 -- 建議關閉 xp_cmdshell
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;

如果系統為SQL Server 2000
可將有安全性考量的SQL SP (shell、regtree、COM+)刪除

use master
EXEC sp_dropextendedproc 'xp_cmdshell'
EXEC sp_dropextendedproc 'Sp_OACreate'
EXEC sp_dropextendedproc 'Sp_OADestroy'
EXEC sp_dropextendedproc 'Sp_OAGetErrorInfo'
EXEC sp_dropextendedproc 'Sp_OAGetProperty'
EXEC sp_dropextendedproc 'Sp_OAMethod'
EXEC sp_dropextendedproc 'Sp_OASetProperty'
EXEC sp_dropextendedproc 'Sp_OAStop'
EXEC sp_dropextendedproc 'Xp_regaddmultistring'
EXEC sp_dropextendedproc 'Xp_regdeletekey'
EXEC sp_dropextendedproc 'Xp_regdeletevalue'
EXEC sp_dropextendedproc 'Xp_regenumvalues'
EXEC sp_dropextendedproc 'Xp_regread'
EXEC sp_dropextendedproc 'Xp_regremovemultistring'
EXEC sp_dropextendedproc 'Xp_regwrite'
drop procedure sp_makewebtask

在 SQL Server 2008 和 SQL Server 2005 中,sp_dropextendedproc 不會卸除系統擴充預存程序。應該由系統管理員拒絕將擴充預存程序的 EXECUTE 權限授與 public 角色。但在 SQL Server 2000 中,您可以使用 sp_dropextendedproc 來卸除任何擴充預存程序。


如果不確定自己是使用什麼SQL Server 版本,可以使用以下語法可以查詢資料庫版本

select @@VERSION


--查詢系統資訊
select * from sys.dm_os_sys_info



4.使用SQL Server Profiler檢視和分析追蹤

當我們想知道誰對SQL Server 做了甚麼事時,就可以使用 SQL Server Profiler 來建立追蹤紀錄,記錄的內容包含所下的SQL語法、Lock狀態、登入的狀況或可用來監視 Database Engine 或 SQL Server Analysis Services 的執行個體....等,可以看到的資訊非常的多,所以您可以擷取每一個事件的相關資料,並將資料儲存至檔案或資料表,以供稍後分析。但也因為這樣SQL Server Profiler使用上多少會影響效能,慎用。

SQL Server Management Studio的「工具」中找到「SQL Server Profiler」



勾選「儲存至檔案」,設定此檔案存放到磁碟C或D
在「設定檔案大小上限(MB)」,輸入 20,表示每個「SQL 追蹤」的檔案大小為 20 MB,每20MB會切割一個檔案。
勾選「啟用檔案換用」
勾選「伺服器處理追蹤資料」
「啟用追蹤停止時間」的意思是,當你開始追蹤後,到所設定的時間系統會自動停止追蹤,這個選項請依實際狀況調整。
點選「執行」


程式執行時,就可以在SQL Profiler中抓到對SQL Server所下達的指令
其中包含「事件類別」、「SQL語法」、「使用者」、「CPU」、「讀取」、「寫入」、「開始時間」與「結束時間」等重要資訊


追蹤結束後到【檔案】->【匯出】 ->【擷取 SQL Server 事件(S) 】->【擷取 Transact-SQL事件(T)】


儲存D:\DBAudit.trc ,就可以先針對敏感的資料表或已知異常語法進行分析與篩選。


5.SQL Server 安全性更新

到Windows 控制台\所有控制台項目\Windows Update\檢視更新記錄
查看是否有更新 SQL Server 2008 Service Pack 3


如果沒有請到下面網址下載
http://support.microsoft.com/kb/2546951/zh-tw

2014年3月12日 星期三

SQL Server 暫時提高權限的做法


我們都知道管理者賦予使用者的權限不要開太高,一般我們會以權限較小的使用者來當啟動帳號但有的時候需要提高權限的時候怎麼辦呢? 方法很簡單我們先來檢查目前的使用者身份

select SUSER_SNAME() 'login name',USER_NAME() 'user name'


確認現在的使用者身份後,就可以來更換使用者帳號了

execute as user='chris'  (chris改為您要提高權限的帳號)



再檢查一次帳號是否變更了

select SUSER_SNAME() 'login name',USER_NAME() 'user name'



如果您要還原原來的身份只要再輸入

revert

就可以了,或是如果你要建立一個預存程序(Store Procedure)但權限不夠也可以加入在語法裡當執行完畢後權限就降回來

create procedure uspuserchange1
with exec as 'sa'
as
begin
select SUSER_SNAME(),USER_NAME()
end
go

2012年1月14日 星期六

SQL SERVER 2008索引維護實作篇

查看索引資訊
我們可以透過指令與親和力的圖形使用者介面進行查看。
(1) 透過 sp_helpindex 儲存程序
clip_image001
(2) 透過 SQL SERVER Management Studio 進行檢索索引資訊
透過物件總管工具,展開目標資料庫->資料表,將會看到索引鍵資料夾
clip_image003clip_image004
建立索引
透過 Create Index 語法來建立索引
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
A. 建立簡單的非叢集索引

   1: USE AdventureWorks;



   2:  



   3: GO



   4:  



   5: IF EXISTS (SELECT name FROM sys.indexes



   6:  



   7: WHERE name = N'IX_ProductVendor_VendorID')



   8:  



   9: DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;



  10:  



  11: GO



  12:  



  13: CREATE INDEX IX_ProductVendor_VendorID 



  14:  



  15: ON Purchasing.ProductVendor (VendorID); 



  16:  



  17: GO




B. 建立簡單的非叢集複合索引




   1: USE AdventureWorks



   2:  



   3: GO



   4:  



   5: IF EXISTS (SELECT name FROM sys.indexes



   6:  



   7: WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')



   8:  



   9: DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;



  10:  



  11: GO



  12:  



  13: CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD



  14:  



  15: ON Sales.SalesPerson (SalesQuota, SalesYTD);



  16:  



  17: GO




C. 建立唯一的非叢集索引




   1: USE AdventureWorks;



   2:  



   3: GO



   4:  



   5: IF EXISTS (SELECT name from sys.indexes



   6:  



   7: WHERE name = N'AK_UnitMeasure_Name')



   8:  



   9: DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;



  10:  



  11: GO



  12:  



  13: CREATE UNIQUE INDEX AK_UnitMeasure_Name 



  14:  



  15: ON Production.UnitMeasure(Name);



  16:  



  17: GO




D.建立唯一的叢集所引




   1: USE AdventureWorks;



   2:  



   3: GO



   4:  



   5: IF EXISTS (SELECT name from sys.indexes



   6:  



   7: WHERE name = N'PK_UnitMeasure_ID')



   8:  



   9: DROP INDEX PK_UnitMeasure_ID ON Production.UnitMeasure;



  10:  



  11: GO



  12:  



  13: CREATE UNIQUE CLUSTERED INDEX PK_UnitMeasure_ID 



  14:  



  15: ON Production.UnitMeasure(ID);



  16:  



  17: GO




修改索引


語法:


ALTER INDEX { index_name | ALL } ON <object>


{ REBUILD | DISABLE | REORGANIZE}


A. 重建索引


在 Employee 資料表上,重建單一索引。




   1: USE AdventureWorks;



   2:  



   3: GO



   4:  



   5: ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee



   6:  



   7: REBUILD;




GO


B. 在資料表上重新組織索引




   1: USE AdventureWorks;



   2:  



   3: GO



   4:  



   5: ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto



   6:  



   7: REORGANIZE ;




GO


C. 停用索引




   1: USE AdventureWorks;



   2:  



   3: GO



   4:  



   5: ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee



   6:  



   7: DISABLE ;



   8:  



   9: GO




刪除索引


語法:


DROP INDEX ON 資料表名.索引名 | 檢視表名.索引名


CODE:




   1: DROP INDEX [CustomerID] ON [dbo].[Orders] WITH ( ONLINE = OFF )




使用系統內建的系統資料表查看索引資訊


clip_image006


詳細欄位意義請查詢MSDN官方定義

http://technet.microsoft.com/zh-tw/library/ms190283(SQL.90).aspx


維護索引的統計資訊


索引的統計資訊,可以協助SQL SERVER 的查詢引擎利用這些資訊來確定最佳的查詢計畫,進而提高查詢的效率。





(1) 透過SQL SERVER Management Studio工具進行查看索引統計資訊


依序選擇目標資料庫->資料表->統計資料,如圖示


clip_image008


在要進行查看的索引統計資訊,雙擊滑鼠兩下,將可以看到更詳細的資訊


clip_image010


(2) 透過命令語法


使用系統檢視表sys.stats進行查看


clip_image012


欄位定義請查詢MSDN完整的定義

http://technet.microsoft.com/zh-tw/library/ms177623.aspx


使用DBCC SHOW_STATISTICS 命令


DBCC SHOW_STATISTICS 會針對資料表或索引檢視表顯示目前的查詢最佳化統計資料。


語法




   1: DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) 



   2:  



   3: [ WITH [ NO_INFOMSGS ] < option > [ , n ] ]



   4:  



   5: < option > :: =



   6:  



   7: STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM




clip_image014


更完整的欄位資訊,請參閱線上MSDN


http://technet.microsoft.com/zh-tw/library/ms174384.aspx


使用系統預存程序 sp_autostats 來查看資料庫中指定的資料表或索引檢視之所有索引和統計資料的自動 UPDATE STATISTICS 設定


clip_image015


(TIP) 在SQL SERVER 2008,資料庫選項的AUTO_CREATE_STATISTICS是最作用於控制是否自動建立索引統計資訊,預設值為ON,因此如果執行了SELECT 、INSERT、UPDATE和DELETE語法中使用到索引指定的欄位,則資料庫會自動為該欄位建立對應的統計資訊,以加速執行查詢計畫。


手動建立索引統計資訊


透過Create Statistics 語法建立





   1: CREATE STATISTICS statistics_name 



   2: ON { table | view } ( column [ ,...n ] ) 



   3: [ WHERE <filter_predicate> ]



   4: [ WITH 



   5: [ [ FULLSCAN 



   6: | SAMPLE number { PERCENT | ROWS } 



   7: | STATS_STREAM = stats_stream ] [ , ] ] 



   8: [ NORECOMPUTE ] 



   9: ] ;




例如,以下為Contact資料表的ContactID, EmailAddress兩個欄位建立統計資訊




   1: CREATE STATISTICS ContactMail1 



   2:  



   3: ON Person.Contact (ContactID, EmailAddress)




使用sp_createstats 預存程序


改程序主要是針對目前資料庫中的所有使用者資料表和內部資料表,建立所有適用資料列的單一資料行統計資料。


語法


sp_createstats [ [ @indexonly = ] 'indexonly' ]


[ , [ @fullscan = ] 'fullscan' ]


[ , [ @norecompute = ] 'norecompute' ]


引數


[ @indexonly = ] 'indexonly'


指定只應考慮建立參與索引之資料行的統計資料。indexonly 是 char(9)。預設值是 NO。


[ @fullscan = ] 'fullscan'


指定搭配 CREATE STATISTICS 來使用 FULLSCAN 選項。如果省略 fullscan,SQL Server Database Engine 會執行預設範例掃描。fullscan 是 char(9)。預設值是 NO。


[ @norecompute = ] 'norecompute'


指定針對新建的統計資料來停用統計資料的自動重新計算。norecompute 是 char(12)。預設值是 NO。


範例是針對目前資料庫中的所有使用者資料表,建立所有適用資料列的統計資料。




   1: EXEC sp_createstats;










修改統計資訊


語法




   1: UPDATE STATISTICS table | view 



   2: [ 



   3: { 



   4: { index | statistics_name }



   5: | ( { index |statistics_name } [ ,...n ] ) 



   6: }



   7: ] 



   8: [ WITH 



   9: [ 



  10: [ FULLSCAN ] 



  11: | SAMPLE number { PERCENT | ROWS } ] 



  12: | RESAMPLE 



  13: ]




此語法結構跟前面講的Create Statistics語法相似,在此直接與取之MSDN範例說明。


A. 更新單一資料表的所有統計資料




   1: UPDATE STATISTICS Sales.SalesOrderDetail;




B. 只更新單一索引的統計資料




   1: UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;




C. 利用 50% 取樣來更新特定統計資料群組 (集合) 的統計資料




   1: CREATE STATISTICS Products



   2:  



   3: ON Production.Product ([Name], ProductNumber)



   4:  



   5: WITH SAMPLE 50 PERCENT



   6:  



   7: -- Time passes. The UPDATE STATISTICS statement is then executed.



   8:  



   9: UPDATE STATISTICS Production.Product(Products) 



  10:  



  11: WITH SAMPLE 50 PERCENT;




D. 利用 FULLSCAN 和 NORECOMPUTE 來更新特定統計資料群組 (集合) 的統計資料




   1: UPDATE STATISTICS Production.Product(Products)



   2:  



   3: WITH FULLSCAN, NORECOMPUTE;




刪除統計資訊


有了前面的基礎,新增、修改統計資訊的觀念後,最後當然也有刪除的語法,刪除很簡單,只要透過簡單的語法 DROP STATISTICS 資料表.索引名,就可以進行刪除。





後續有時間會繼續寫索引如何優化資料庫的觀念………By Allen