Sql server 實用技巧總結

一、 從主鍵說起
我們建立樣本表BingoT1 並插入若干條資料，主鍵是一個自增欄位。

CREATE TABLE BingoT1

    (

      id INT IDENTITY(1, 1) ,

      NAME NVARCHAR(10) ,     --姓名

      IdentityNo VARCHAR(18) ,   --社會安全號碼碼

      LogDate DATETIME DEFAULT ( GETDATE() ) ,     --錄入日期

PRIMARY KEY ( id )--主鍵約束

)

Go

INSERT  INTO BingoT1( NAME, identityNo )

VALUES  ( '李斌', '123456789012345' )

INSERT  INTO BingoT1( NAME, identityNo )

VALUES  ( '張霖青', '123456789012345678' )

INSERT  INTO BingoT1( NAME, identityNo )

VALUES  ( '李菲', '12345678901234500x' )

INSERT  INTO BingoT1( NAME, identityNo )

VALUES  ( '戈曉娟', '123456789012345' )

Go

SELECT * FROM BingoT1

Go

 

問題一：誤刪了一條記錄如何修複？
在企業管理器（sql 2000）或Sql Server Management Studio中無法修改Id欄位，

 

解決辦法：

SET  IDENTITY_INSERT  bingot1 ON

INSERT  INTO BingoT1(id, NAME, identityNo )

VALUES  ( 1, '李斌', '123456789012345' )

SET  IDENTITY_INSERT  bingot1 OFF --用完一定要關閉

 

執行後查看資料如下：

 

 

從LogDate的時間上可以看出Id=1的資料是最後插入的。

問題二：Delete掉所有的資料後，為什麼Id不從1 開始？

DELETE  FROM bingot1GoINSERT  INTO BingoT1( NAME, identityNo )VALUES  ( '李斌', '123456789012345' )INSERT  INTO BingoT1( NAME, identityNo )VALUES  ( '張霖青', '123456789012345678' )INSERT  INTO BingoT1( NAME, identityNo )VALUES  ( '李菲', '12345678901234500x' )INSERT  INTO BingoT1( NAME, identityNo )VALUES  ( '戈曉娟', '123456789012345' )GoSELECT * FROM BingoT1Go

 

執行後查看資料如下：

 

解決辦法：
      TRUNCATE TABLE BingoT1

GO

 

如果表包含識別欄位，該列的計數器重設為該列定義的種子值。如果未定義種子，則使用預設值 1。若要保留標識計數器，請使用 DELETE。

TRUNCATE TABLE刪除表中的所有行，而不記錄單個行刪除操作。TRUNCATE TABLE 在功能上與沒有 WHERE 子句的 DELETE 語句相同；但是，TRUNCATE TABLE 速度更快，使用的系統資源和交易記錄資源更少。

 

二、 SQL SERVER 的中樞神經--系統資料表

• SysObjects 

儲存資料庫中建立的每個對象（例如約束、預設值、日誌、規則以及預存程序）。

SELECT * FROM sysobjects WHERE xtype='U'

SELECT * FROM  SysObjects WHERE xtype='D'

SELECT * FROM  SysObjects WHERE xtype='PK'

執行後可以看到我們新建立的表BingoT1、表的預設約束DF__BingoT1__LogDate__07020F21（以DF開頭）、表的主鍵PK__BingoT1__060DEAE8（以PK開頭）。
Xtype標識物件類型。可以是下列物件類型中的一種：

C = CHECK 條件約束         D = 預設設定或 DEFAULT 約束

F = FOREIGN KEY 約束      L = 日誌       FN = 純量涵式     IF = 內聯表函數

P = 預存程序 PK = PRIMARY KEY 約束（類型為 K） RF = 複製篩選器預存程序

S = 系統資料表 TF = 表函數 TR = 觸發器 U = 使用者表 UQ = UNIQUE 約束（類型為 K）

V = 視圖 X = 擴充預存程序

可以通過下面的語句查看和表相關的各種資源：

--查看和表相關的各種資源

SELECT  *

FROM    sysobjects

WHERE   parent_obj IN ( SELECT  id

                           FROM    sysobjects

WHERE   name = 'BingoT1' )

• SysColumns

儲存表和視圖中的列，並儲存資料庫中的預存程序的每個參數。可以使用如下語句查看錶BINGOT1的所有列資訊。
      --查看錶的所有列資訊

SELECT  *

FROM    SysColumns

WHERE   id IN ( SELECT  id

                FROM    sysobjects

                WHERE   name = 'BingoT1' )

 

• Sys.types

存放實體儲存體類型。內容如下所示：
SELECT * FROM sys.types

 

問題：系統資料表我很少用，有什麼實際應用嗎？
下面我們就用這3個系統資料表來寫一個簡單的代碼產生器！
應用一，產生實體類屬性：

SELECT  'public virtual '

+ CASE t.name WHEN 'int' THEN 'int' ELSE 'string' END + ' ' + c.name + ' { get; set; }'

FROM    dbo.sysobjects AS o

        INNER JOIN dbo.syscolumns AS c ON c.id = o.id

        INNER JOIN dbo.systypes AS t ON c.xusertype = t.xusertype

WHERE   o.type = 'U'

        AND o.name = 'BingoT1'

輸出結果如下：

public virtual int id { get; set; }

public virtual string NAME { get; set; }

public virtual string IdentityNo { get; set; }

public virtual string LogDate { get; set; }

 

應用二，產生列表頁面和添加頁面：

首先需要給每個欄位添加一個擴充屬性，其中參數@value賦值備忘、@level1name賦值表名、@level2name賦值欄位名。

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'主鍵' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'id'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'NAME'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'社會安全號碼碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'IdentityNo'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'錄入日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BingoT1', @level2type=N'COLUMN',@level2name=N'LogDate'

然後就可以使用系統函數產生列表頁面代碼.

SELECT  '<asp:BoundColumn DataField="' + objname + '" HeaderText="'

        + CAST(value AS NVARCHAR) + '"'

+ CASE CAST(value AS NVARCHAR)                                       

WHEN '主鍵' THEN ' Visible="False"'

                 ELSE '' END + '></asp:BoundColumn> '

FROM  FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'BingoT1',

                                'column', DEFAULT)

結果如下（在查詢分析器中以文字格式設定顯示結果，快速鍵Ctrl+T）：
<asp:BoundColumn DataField="id" HeaderText="主鍵" Visible="False">

</asp:BoundColumn>

<asp:BoundColumn DataField="NAME" HeaderText="姓名">

</asp:BoundColumn>

<asp:BoundColumn DataField="IdentityNo" HeaderText="社會安全號碼">

</asp:BoundColumn>

<asp:BoundColumn DataField="LogDate" HeaderText="錄入日期">

</asp:BoundColumn>

再產生添加頁面代碼，其中Char（13）代表斷行符號符.

SELECT  '<tr>'+ CHAR(13)

       +'  <td class="add_tdr" style="width: 30%;">' + CAST(VALUE AS NVARCHAR) + '：</td>' + CHAR(13)

        +'   <td class="add_tdl" style="width: 70%;">'

        +'<asp:TextBox ID="txt' + objname + '" runat="server" style="width:220px"></asp:TextBox> <td>' + CHAR(13)

        +'<tr> '

FROM  FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'BingoT1',

                                'column', DEFAULT)

結果如下（在查詢分析器中以文字格式設定顯示結果，快速鍵Ctrl+T）：

<tr>

    <td class="add_tdr" style="width: 30%;">主鍵：</td>

    <td class="add_tdl" style="width: 70%;"><asp:TextBox ID="txtid" runat="server" style="width:220px"></asp:TextBox> <td>

<tr>

<tr>

    <td class="add_tdr" style="width: 30%;">姓名：</td>

    <td class="add_tdl" style="width: 70%;"><asp:TextBox ID="txtNAME" runat="server" style="width:220px"></asp:TextBox> <td>

<tr>

<tr>

    <td class="add_tdr" style="width: 30%;">社會安全號碼碼：</td>

    <td class="add_tdl" style="width: 70%;"><asp:TextBox ID="txtIdentityNo" runat="server" style="width:220px"></asp:TextBox> <td>

<tr>

<tr>

    <td class="add_tdr" style="width: 30%;">錄入日期：</td>

    <td class="add_tdl" style="width: 70%;"><asp:TextBox ID="txtLogDate" runat="server" style="width:220px"></asp:TextBox> <td>

<tr>

頁面如下所示：