試著把流水號pkey做成另一種流水號id吧

T-SQL
SELECT CONVERT(VARCHAR, GETDATE(),126)+'-'+CONVERT(NVARCHAR(50),NEWID())

為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)

在MS SQL2005以後，增加了一些幫查詢結果加上序號的函數

以下的範例使用北風(NorthWind)資料庫

介紹如下：

1.ROW_NUMBER

依照指定的欄位排序，並逐筆加上順號的方式

例如：

SELECT 
 ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
 ,*
FROM Orders

2.RANK

依照排序的欄位，相同的資料相同排名，下一個不同會【跳脫】

SELECT 
 RANK() OVER(ORDER BY CustomerID) AS ROWID
 ,*
FROM Orders

3.DENSE_RANK

依照排序的欄位，相同的資料相同排名，下一個不同會【不跳脫】

SELECT 
 --ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
 --RANK() OVER(ORDER BY CustomerID) AS ROWID
 DENSE_RANK() OVER(ORDER BY CustomerID) AS ROWID
 ,*
FROM Orders

用其它的 Table 來更新資料(Update From table)

pl-sql

UPDATE table1 a
SET table1_column = (SELECT table2_column
FROM table2
WHERE ID = a.ID)

如果雙方均有KEY值，使用下面這個方法會超快

UPDATE (
  SELECT PAYCDL.ID_NO, PAYCDL.DATE_ADD DATE_ADD_OLD,PAYCDL.DATE_LAST_MAINT DATE_LAST_MAINT_OLD,
               EXSCCS_PMS2.DATE_ADD DATE_ADD_NEW, EXSCCS_PMS2.DATE_LAST_MAINT DATE_LAST_MAINT_NEW
          FROM PAYCDL,
               EXSCCS_PMS2
         WHERE PAYCDL.ID_NO = EXSCCS_PMS2.ID_NO)
   SET DATE_ADD_OLD = DATE_ADD_NEW,
       DATE_LAST_MAINT_OLD = DATE_LAST_MAINT_NEW

-----------------------------------
ms-sql

UPDATE table1
      SET table1.col = table2.col1
FROM table2 WHERE table2.oldCol = table1.col

SQL Server 2005 如何解開 Lock

使用 select object_id('TABLE_NAME') 或是 select object_name('OBJECT_ID') 去查出該 Lock 是屬於哪個 Table

使用 sp_lock 去查詢被 Lock 的資料有哪些, 因為他不會直接列出 Table 名稱, 所以可透過上述取得OBJID

接下來針對SPID去下指令即可

kill 81

SQL去除欄位前後空白及斷行字元

SQL 中的 TRIM 函數是用來移除掉一個字串中的字頭或字尾。最常見的用途是移除字首或字尾的空白。這個函數在不同的資料庫中有不同的名稱：

MySQL: TRIM(), RTRIM(), LTRIM()
Oracle: RTRIM(), LTRIM()
SQL Server: RTRIM(), LTRIM()

SQL Server及Oracle沒有TRIM()函數，因此可用下列的語法清除:

-- SQL去除斷行字元 (1st)
UPDATE [Donor]
SET [DonorList] = REPLACE(([DonorList]), CHAR(10), '');

-- SQL去除前後空白 (2rd)
UPDATE [Donor]
SET [DonorList] = LTRIM(RTRIM([DonorList]));
而UI裏該欄位也要在異動資料時，自動去除斷行及空白字元。

// C#去除斷行及空白字元
this.ctrlDonorList.Text = this.ctrlDonorList.Text.Trim('\r', '\n', ' ');

如此內服外敷，即可藥到病除。

還原資料庫時,"無法獲得獨佔存取權,因為資料庫正在使用中"的解決方法

使用Sql Server Management Studio 還原資料庫時,
常會遇到 "無法獲得獨佔存取權,因為資料庫正在使用中" 的錯誤訊息

我的一勞永逸解決方法
用指令還原
指令如下

ALTER DATABASE 你的DB名稱 SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE 你的DB名稱 FROM  DISK='C:\你的路徑\你的備份檔點BAK' WITH RESTRICTED_USER,REPLACE

絕對有效

TRIGGER SAMPLE

USE [DB_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tg_NAME]
   ON  [dbo].[TABLE_TAB]
   AFTER INSERT,UPDATE,DELETE
AS
BEGIN
    SET NOCOUNT ON;
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) = 0 BEGIN --新增
        INSERT INTO TABLE_LOG
        SELECT *, 'INSERT' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --變更
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_BEFORE' FROM DELETED
        INSERT INTO TABLE_LOG
        SELECT *, 'UPDATE_AFTER' FROM INSERTED
    END
  
    IF (SELECT COUNT(*) FROM INSERTED) = 0 AND (SELECT COUNT(*) FROM DELETED) > 0 BEGIN --刪除
        INSERT INTO TABLE_LOG
        SELECT *, 'DELETE' FROM DELETED
    END
  
              
END

GO

將多筆記錄做成一個欄位資料

Select count(*) AS [A]
From CNTMGM.DBO.CNTCMSD_TAB WHERE CNT_NO = 'M10200118000' AND Isnull(CheckDate, '') = ''
And BPKEY Not In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')
UNION ALL
Select SUM(CONVERT(DECIMAL, REPLACE(INFOBCK.DBO.FN_ISEMPTY(ISNULL(OriMoney, '0'), '0'), ',', ''))) AS [A]
From CNTMGM.DBO.CNTCMSD_TAB WHERE BPKEY In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')


SELECT ';' + CONVERT(NVARCHAR(MAX),A.A) FROM (
    Select count(*) AS [A]
    From CNTMGM.DBO.CNTCMSD_TAB WHERE CNT_NO = 'M10200118000' AND Isnull(CheckDate, '') = ''
    And BPKEY Not In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')
    UNION ALL
    Select SUM(CONVERT(DECIMAL, REPLACE(INFOBCK.DBO.FN_ISEMPTY(ISNULL(OriMoney, '0'), '0'), ',', ''))) AS [A]
    From CNTMGM.DBO.CNTCMSD_TAB WHERE BPKEY In ('4A409A56-97AB-42C6-A54D-32E077CC3ADC','15C7C764-32EF-40CF-B9C9-29D4595C1964')
)A
FOR XML PATH('')


SELECT SeqNo,
(
SELECT DISTINCT S2.ProjectNo + ',' FROM CNTCMSD_TAB as s2
WHERE s1.SeqNo=s2.SeqNo
FOR XML PATH('')
) as ProductID
FROM CNTCMSD_TAB as s1
WHERE SeqNo LIKE 'NCNT-1020000142'
group by SeqNo

利用 CURSOR 跑迴圈

--宣告三個變數，@colA及@colB分別為table中的欄位,@MyCursor為指標
DECLARE @colA nvarchar(10)
DECLARE @colB nvarchar(10)
DECLARE @MyCursor CURSOR
--指定啟用了效能最佳化的 FORWARD_ONLY、READ_ONLY 資料指標
SET @MyCursor = CURSOR FAST_FORWARD
--假設TableA中有100筆，則下列的語法就會直接傳回100筆
FOR
Select colA,colB
From tableA
--開啟指標
OPEN @MyCursor
--取得第一筆的值放到@colA及@colB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
-- @@FETCH_STATUS=回針對連接目前開啟的任何資料指標而發出的最後一個資料指標 FETCH 陳述式的狀態
--傳回值
--  0  FETCH 陳述式成功。
-- -1 FETCH 陳述式失敗，或資料列已超出結果集。
-- -2 遺漏提取的資料列。
--開始繞迴圈
WHILE @@FETCH_STATUS = 0
BEGIN
--顯示@colA及@colB的值在畫面上
PRINT @ColA
PRINT @ColB
--你也可以在這之中處理其他sql部份，例如你可以寫依據@colA及@colB這兩個key在tabl2查詢後的資料寫到table3
--這是其中一種應用
insert into table3
select *
from table2
where table2.c1=@colA and table2.c2=@colB
--取得下一筆記錄的colA及colB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

批次UPDATE跨資料庫

SQL Server 只收 [server].[database].[owner].[object name] 四個階層，超出的話就會錯誤，請改用別名 (alias) 方式來處理。

EXAMPLE:


Update PROJMGM.DBO.PISMINT_TAB set PROJMGM.DBO.PISMINT_TAB.G1TEMP = WKFSRV01.WEBFLOWDATA.DBO.TB_161_1.G1TEMP
FROM WKFSRV01.WEBFLOWDATA.DBO.TB_161_1
WHERE PROJMGM.DBO.PISMINT_TAB.EFORMSN = WKFSRV01.WEBFLOWDATA.DBO.TB_161_1.EFORMSN


Update PROJMGM.DBO.PISMINT_TAB set PROJMGM.DBO.PISMINT_TAB.G1TEMP = B.G1TEMP
FROM WKFSRV01.WEBFLOWDATA.DBO.TB_161_1 B
WHERE PROJMGM.DBO.PISMINT_TAB.EFORMSN = B.EFORMSN

TSQL顯示資料表, 欄位名稱, 型別, 長度 語法

這語法很好用，可以順利撈出資料庫中的所有資料表名稱，各資料表中的欄位名稱，型別，與長度。
另外補充:
要找出資料庫中的所有資料表==>查sys.objects
要找出某資料表中的所有欄位與欄位型別id, 長度==>查sys.columns
要找出欄位型別名稱==>查sys.types

--===============================================================
自從訂閱了點部落之後，對資訊系統的自我認知，已經遠超越「Mammut」與「Marmot」 的大小差異，並且日漸渺小當中，每日只能從眾多高手牙慧中，盼望能多吸取一些知識。這幾年來由於工作的不穩定，我得常接手別人開發的專案，每一個專案跟程 式設計師一樣，有著不同的個性，有的有高度的模組化，有的ASP互相亂 include 一堆，有的把4-5個專案放到同一個目的網站中，有的遺留了許多無用的程式碼......。程式碼這麼的多元化，當然資料庫就更千奇百怪了，相同的 是......都沒有DD、都沒有有用的文件。

所以我便常常需要一個一個去看資料表的欄位名稱，也得用 Query 的結果猜測，這個欄位的「意義」還有他可能會跟「哪些資料表」是有關連的。
今天看到「尋找某欄位存在那幾些資料表」文章，發現這個功能不就是我之前很想要的功能嗎？一次把相關連的欄位名稱都找出來，真棒～這個功能我需要。
可是我還想知道其他的一些資訊，所以動手改了一下 T-SQL，在看整個程式前，先來看三個資料庫物件
SELECT * FROM sysobjects where type='U' -- 查詢所有使用者資料表

SELECT * FROMsyscolumns whereid=117575457 -- 依某資料表ID查所有欄位

SELECT * FROM systypes  -- 查欄位屬性xtype的意思

當然你要查詢上述的資料庫物件，要先切換到你想查的資料庫囉（USE DataBase_Name;）！
底下以 「NorthWind」資料庫查詢為對象
請注意，當中的「store」的值是決定要不要把 Query 結果給儲存起來，當 store=1 則不儲存，其他值會寫到同一個DB裡，建立名為「__T_all___」的資料表，每次會刪除重新建立。

useNorthwind ;    -- 更換成你要查詢的資料庫 DECLARE@tablename NVARCHAR(50)DECLARE @cloumnname NVARCHAR(50)DECLARE @store TINYINT SET @tablename=''   -- 輸入要查詢的資料表，留下空的表示查全部 SET@cloumnname=''  -- 輸入要查詢的欄位名稱，留下空的表示查全部 SET@store =2      -- 設定store != 1 會將結果暫存在 [__T_all___]  資料表，                        --請注意是否跟既有資料表同名 IF@store =1       -- store=1 只會顯示、傳回結果～不會儲存 BEGIN SELECT so.name 'Table',sc.name 'Column',st.name 'Type', sc.length 'Length' FROM sysobjects so INNER JOIN syscolumns sc ON so.id =sc.id INNER JOIN systypes st ON st.xtype=sc.xtype WHERE (so.type='U'AND st.name <> 'sysname') AND so.name LIKE '%'+@tablename+'%' AND  sc.name LIKE '%'+@cloumnname+'%'ORDER BY1 END ELSE   --不成立則存在[__T_all___]  資料表 BEGIN IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[__T_all___]') AND TYPE in (N'U'))DROP TABLE[__T_all___] SELECT so.name 'Table',sc.name 'Column',st.name 'Type', sc.length 'Length' INTO__T_all___ FROM sysobjectsso INNER JOIN syscolumns sc ON so.id =sc.id INNER JOIN systypes st ON st.xtype=sc.xtype WHERE (so.type='U'AND st.name <> 'sysname') AND so.name LIKE '%'+@tablename+'%' AND  sc.name LIKE '%'+@cloumnname+'%'ORDER BY1 END

多了欄位屬性與欄位大小，我想在做比對的時候，會準確更容易找到真正對應的欄位名稱。
另一個例子：搜尋 emp 相關欄位
SET@cloumnname='emp'


～ End

利用暫存資料表逐筆完成工作

DECLARE @cts As DECIMAL
DECLARE @rcount As DECIMAL
SET @cts = 1

SELECT DISTINCT IDENTITY(INT,1,1) AS sno, *
INTO #temp1
FROM TABLE1

SELECT @rcount = Count(*) FROM #temp1

WHILE (@cts <= @rcount) Begin
    SELECT @Times = Count(*) FROM #temp1 Where sno = @cts
    SET @cts = @cts + 1
END

DROP TABLE #temp1

-----------------------------------------------------

DECLARE @TEMP TABLE
    (
      ID int IDENTITY PRIMARY KEY,
      seqno nvarchar(50),
      number nvarchar(50),
     )

Declare @cts As Decimal
    Declare @rcount As Decimal
    Set @cts = 1

    insert into @TEMP
    Select  seqno, number
    From cntmgm.dbo.CNTCMSD_TAB

    Select @rcount = Count(*) From @TEMP

    While (@cts <= @rcount) Begin
        Select @ResultVar = number + '|' + plandate + '|' + projectno
        From @TEMP
        Where ID = @cts       
        Set @cts = @cts + 1
    End

產生GUID的方法

Select CONVERT(varchar(255), NEWID()) As PKEY

[SQL]並未為 RPC 設定伺服器 'SQL-LINK-SERVER' (Msg 7411)

今天同事問說他Run Link Server的STORE PROCEDURE，出現了以下的錯誤!
Msg 7411, Level 16, State 1, Line 1
並未為 RPC 設定伺服器 'SQL-LINK-SERVER'
用中文去查，居然找不到，用Msg 7411就查到了，要把LINK-SERVER的屬性「RPT Out」設成「True」就搞定了!

另外，如果DB的定序與LINK SERVER的定序不同，LINK-SERVER的屬性「Use Remote Collation」要設成「False」哦!

讓你的資料欄位可以用時間當預設值

日期可以使用：(CONVERT([char](10),getdate(),(120)))  →  2012-10-05

時間可以使用：(CONVERT([char](8),getdate(),(114)))  →  10:33:07

T-SQL sp_password 修改sa語法

EXEC sp_password NULL, '你的密碼', 'sa' Go

insert into select 及 select into 用法差異

如果今天要寫的目的資料表不存在，可以直接使用 select into 完成，例如

Select Column1, null, 'A', @parameter1, Column2, Column3 Into Table1 From Table2 Where Column1 = 'A' and Column1 = 'B'

如果今天要寫的目的資料表已存在，請使用 insert into select 完成，例如

insert into Table1 Select  Column1, null, 'A', @parameter1, Column2, Column3 From Table2 Where Column1 = 'A' and Column1 = 'B'

如何動態組出 T-SQL 指令

sp_executesql 系統預存程序具備以參數來指定所要執行的 T-SQL，藉由這個特性，可以讓動態組出的 T-SQL 更加安全。


假設我們要動態組出 T-SQL，從 AdventureWorks 資料庫中的 Production.Product 資料表內，找出「顏色」是黑色的產品，可以使用下面的程式碼：


USE AdventureWorks
GO
 
-- 宣告變數
DECLARE @SQLCommand nvarchar(200)
DECLARE @columnList nvarchar(50)
DECLARE @parmColor varchar(10)
 
-- 定義變數
SET @columnList = N'ProductID 產品編號, Color 顏色'
SET @parmColor = 'Black'
 
-- 動態組出 T-SQL
SET @SQLCommand = 'SELECT ' + @columnList + ' FROM Production.Product WHERE Color = @color'
 
-- 執行動態組出的 T-SQL
EXECUTE sp_executesql @SQLCommand, N'@color varchar(10)', @color = @parmColor

執行的部分結果畫面：

從程式碼中，可以看到系統預存程序 sp_executesql 一共需要傳入 3 個參數，分別說明如下：


●第 1 個參數：
所要執行的 T-SQL 陳述式，其內容必須是 Unicode 字串，所以在宣告 @SQLCommand 時，必須指定為 n 開頭的資料型別，在這個範例我是使用 nvarchar 型別。
因為要把英文的欄位名稱改用中文來顯示，所以在定義變數 @columnList 時，要使用前置詞 N 來指定該變數的內容。


●第 2 個參數：
它會包含第 1 個參數中，所有內嵌的參數定義。以本程式碼為例，@color 就是內嵌的參數定義。同樣的，這個參數也必須使用前置詞 N 來指定該變數的內容，且每個參數的定義都是由參數名稱（在該程式碼內，就是 @color）和資料型別（在該程式碼中，就是 varchar(10)）所組成。


●第 3 個參數：
指定第 2 個參數內的參數定義的值。以本程式碼為例，第二個參數指定為 @color varchar(10)，所以要把先前設定的 @parmColor 的值指派給 @color，這樣才能讓 @parmColor 所代表的 Black 傳遞給 @color。


大家可以跟上一篇分享：「如何動態組出 T-SQL 指令（上）」，比較一下，於設定 @color 顏色變數的值時，上一篇文章在變數值的左右各用了 3 個單引號（'）來把 Black 包起來，而在這裡只需要用 1 對單引號就可以了。


如果現在要再加上一個查詢的限制條件，就是「產品編號」要小於 325，就要多宣告並設定一個新變數 @parmPID，同時在系統預存程序 sp_executesql 也要隨之修改：



USE AdventureWorks  
GO  
  
-- 宣告變數  
DECLARE @SQLCommand nvarchar(200)  
DECLARE @columnList nvarchar(50)  
DECLARE @parmColor varchar(10)  
DECLARE @parmPID int  
  
-- 定義變數  
SET @columnList = N'ProductID 產品編號, Color 顏色'  
SET @parmColor = 'Black'  
SET @parmPID = 325  
  
-- 動態組出 T-SQL  
SET @SQLCommand = 'SELECT ' + @columnList + ' FROM Production.Product WHERE Color = @color AND ProductID < @pID'  
  
-- 執行動態組出的 T-SQL  
EXECUTE sp_executesql @SQLCommand, N'@color varchar(10), @pID int',  
                        @color = @parmColor, @pID = @parmPID  

執行結果：

如果不想使用 EXECUTE 或 sp_executesql 來動態組出 T-SQL，可以改用下面的程式碼



USE AdventureWorks  
GO  
  
-- 宣告變數  
DECLARE @parmColor varchar(10)  
DECLARE @parmPID int  
  
-- 定義變數  
SET @parmColor = 'Black'  
SET @parmPID = 325  
  
SELECT ProductID 產品編號, Color 顏色  
FROM Production.Product WHERE Color = @parmColor AND ProductID < @parmPID  

SQL 日期時間轉換 (Convert Date Time to string)

YYYY-MM-DD (2008-06-26)：CONVERT(CHAR(10), GETDATE(), 120)
HH:MM:SS:sss (17:24:30)：CONVERT(CHAR(8), GETDATE(), 114)
MM DD YYYY (06 26 2008)：CONVERT(CHAR(10), GETDATE(), 100)
MM/DD/YYYY (06/26/2008)：CONVERT(CHAR(10), GETDATE(), 101)
MM.DD.YYYY (06.26.2008)：CONVERT(CHAR(10), GETDATE(), 102)
DD/MM/YYYY (14/03/2011)：CONVERT(CHAR(10), GETDATE(), 103)
DD.MM.YYYY (14.03.2011)：CONVERT(CHAR(10), GETDATE(), 104)
DD-MM-YYYY (14-03-2011)：CONVERT(CHAR(10), GETDATE(), 105)
DD MM YYYY (14 03 2011)：CONVERT(CHAR(10), GETDATE(), 106)
YYYYMMDD (20110314)：CONVERT(CHAR(8), GETDATE(), 112)
HH:MM:SS:sss (17:24:30:923)：CONVERT(CHAR(12), GETDATE(), 114)

ASP呼叫sql server 預存程序參數詳解

語法 Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

返回值 返回 Parameter 對象。

參數

Name　選用　字串　代表Parameter對象名稱。

Type　選用　數值　指定Parameter對象資料類型。
　adDBTimeStamp 135 日期時間數據類型
　adDecimal 14 十進製整數值
　adDouble 5 雙精度小數值
　adError 10 係統錯誤信息
　adGUID 72 全域性唯一識別字(Globally unique identifier)
　adDispath 9 COM/OLE自動對象(Automation Object)
　adInteger 3 4字節有符號整數
　adIUnknown 13 COM/OLE對象
　adLongVarBinary 205 大型２字節值
　adLongVarChar 201 大型字符串值
　adLongVarWChar 203 大型未編碼字符串
　adNumeric 131 十進製整數值
　adSingle 4 單精度浮點小數
　adSmallInt 2 2字節有符號整數
　adTinyInt 16 1字節有符號整數
　adUnsignedBigInt 21 8字節無符號整數
　adUnsignedInt 19 4字節無符號整數
　adUnsignedSmallInt 18 2字節無符號整數
　adUnsignedTinyInt 17 1字節無符號整數
　adUserDefined 132 用戶自定義數據類型
　adVariant 12 OLE對象
　adVarBinary 204 雙字節字符變量值
　adVarChar 200 字符變量值
　advarchar 202 未編碼字符串變量值
　adWchar 130 未編碼字符串

Direction 選用 數值 指定Parameter為輸入或輸出類型。
　adParamInput 1 允許數據輸入至該參數當中
　adParamOutput 2 允許數據輸出至該參數當中
　adParamInputOutput 3 允許數據輸入、輸出至該參數當中
　adparamReturnValue 4 允許從一子程序中返回數據至該參數當中

Size 選用 數值 指定參數值最大長度（以字符或字節數為單位）。

Value 選用 字串或數值 指定Parameter預設值。

說明
使用 CreateParameter 方法可用指定的名稱、類型、方向、大小和值創建新的 Parameter 對象。在參數中傳送的所有值都將寫入相應的 Parameter 屬性。
該方法無法自動將 Parameter 對象追加到 Command 對象的 Parameter 集合，這樣就可以設置附加屬性。如果將 Parameter 對象追加到集合，則 ADO 將使該附加屬性的值生效。
如果在 Type 參數中指定可變長度的數據類型，那麼在將它追加到 Parameters 集合之前必須傳送 Size 參數或者設置 Parameter 對象的 Size 屬性環裨蚪砦蟆?br>