Đệ qui trong MS SQL 2005

 

/*Tạo 1 bảng tạm Sample1 có các field như bên dưới. */

declare @Sample1 table 
  ( 
      RecordID int  Primary key NOT NULL ,
      ParentRecordID int,
      SortOrder int,
      Description nvarchar(100),
      Salary money
   )

 /* Start loading of test data */
1.  insert into @Sample1 values(1,null,null,'CEO',10)
2.  insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
3.  insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
4.  insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
5.  insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
6.  insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
7.  insert into @Sample1 values(7,4,1,'Human Resources Director',4)
8.  insert into @Sample1 values(8,4,2,'Some other item',3)
9.  insert into @Sample1 values(9,6,1,'Research Analyst',2)

/*
Hàng 1 là record gốc(cha) nên ParentRecordID = null, và SortOrder(sắp thứ tự) cũng bằng null
Hàng 2 có 1 record liên quan tức record lớn hơn(hay còn gọi là có record cha) nên ParentRecordID=1, và SortOrder khởi đầu bằng 1
Tương tự, hàng 3 cũng vậy.
Hàng 4 thì ParentRecordID=2 thì tức là record hàng 2 là cha của nó.
Cứ như vậy chèn cho đến hết các record.
Câu đệ qui trình bày giúp duyệt hết record này.
*/

  set nocount off;

/*Đây là hàm đệ qui*/
 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)
 as
 (
   select RecordID,ParentRecordID,SortOrder,Salary,
          convert(varchar(100),'') TOC
      from @Sample1
      where ParentRecordID is null
   union all
   select R1.RecordID,
          R1.ParentRecordID,
          R1.SortOrder,
          R1.Salary,
          case when DataLength(R2.TOC) > 0
                    then convert(varchar(100),R2.TOC + '.'
                                 + cast(R1.SortOrder as varchar(10))) 
                    else convert(varchar(100),
                                cast(R1.SortOrder as varchar(10))) 
                    end as TOC
      from @Sample1 as R1
     join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID     /*Kết giữa RecordID và ParentRecordID     */
  )

select * from RecursionCTE order by ParentRecordID,SortOrder asc

Results

RecordID    ParentRecordID SortOrder   Salary     TOC
----------- -------------- ----------- -----------------
1           NULL           NULL        10.00                
2           1              1            9.00       1
3           1              2            8.00       2
6           1              3            5.00       3
4           2              1            7.00       1.1
5           2              2            6.00       1.2
7           4              1            4.00       1.1.1
8           4              2            3.00       1.1.2
9           6              1            2.00       3.1

 

Sum Up Subordinate Salaries of All Employees

set nocount on

  declare @Sample1 table 
  ( 
      RecordID int  Primary key NOT NULL ,
      ParentRecordID int,
      SortOrder int,
      Description nvarchar(100),
      Salary money
   )

 /* Start loading of test data */
  insert into @Sample1 values(1,null,null,'CEO',10)
  insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
  insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
  insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
  insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
  insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
  insert into @Sample1 values(7,4,1,'Human Resources Director',4)
  insert into @Sample1 values(8,4,2,'Some other item',3)
  insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
 as
 (
   select RecordID,ParentRecordID,SortOrder,Salary
      from @Sample1
      where ParentRecordID is null
   union all
   select R1.RecordID,
          R1.ParentRecordID,
          R1.SortOrder,
          R1.Salary
      from @Sample1 as R1
     join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
  )
 select sum(R1.salary) as Salary
   from @Sample1 as R1
   JOIN RecursionCTE as R2
   on R1.RecordID = R2.RecordID

Results


Salary
---------------------
54.00

(1 row(s) affected)

 

Sum Up Subordinate Salaries of a Specific Employee

set nocount on

  declare @Sample1 table 
  ( 
      RecordID int  Primary key NOT NULL ,
      ParentRecordID int,
      SortOrder int,
      Description nvarchar(100),
      Salary money
   )

 /* Start loading of test data */
  insert into @Sample1 values(1,null,null,'CEO',10)
  insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
  insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
  insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
  insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
  insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
  insert into @Sample1 values(7,4,1,'Human Resources Director',4)
  insert into @Sample1 values(8,4,2,'Some other item',3)
  insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

 with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary)
 as
 (
   select RecordID,ParentRecordID,SortOrder,Salary
      from @Sample1
      where ParentRecordID =2 -- specific employee id
   union all
   select R1.RecordID,
          R1.ParentRecordID,
          R1.SortOrder,
          R1.Salary
      from @Sample1 as R1
     join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
  )
 select sum(R1.salary) as Salary
   from @Sample1 as R1
   JOIN RecursionCTE as R2
   on R1.RecordID = R2.RecordID

Results:



Salary
---------------------
20.00

 

Manager to Subordinate Salary Differential

 

set nocount on

  declare @Sample1 table 
  ( 
      RecordID int  Primary key NOT NULL ,
      ParentRecordID int,
      SortOrder int,
      Description nvarchar(100),
      Salary money
   )

 /* Start loading of test data */
  insert into @Sample1 values(1,null,null,'CEO',10)
  insert into @Sample1 values(2,1,1,'Vice Pres. Marketing',9)
  insert into @Sample1 values(3,1,2,'Vice Pres. Ops-',8)
  insert into @Sample1 values(4,2,1,'Marketing Director - Direct Mail',7)
  insert into @Sample1 values(5,2,2,'Marketing Director - TV',6)
  insert into @Sample1 values(6,1,3,'Vice Pres. - Research',5)
  insert into @Sample1 values(7,4,1,'Human Resources Director',4)
  insert into @Sample1 values(8,4,2,'Some other item',3)
  insert into @Sample1 values(9,6,1,'Research Analyst',2)

set nocount off;

 with RecursionCTE (RecordID,ParentRecordID,SortOrder,ParentSalary,Salary,Differential)
 as
 (
   select RecordID,ParentRecordID,SortOrder,
            convert(money,null) as ParentSalary,
            Salary,
            convert(money,null) as Differential
      from @Sample1
      where ParentRecordID is null
   union all
   select R1.RecordID,
            R1.ParentRecordID,
            R1.SortOrder,
            convert(money,R2.Salary) as ParentSalary,
            R1.Salary,
            convert(money,R2.Salary - R1.Salary) as Differential
      from @Sample1 as R1
     join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID


  )

select * from RecursionCTE order by ParentRecordID,SortOrder asc

 

RecordID    ParentRecordID SortOrder   ParentSalary          Salary                Differential
----------- -------------- ----------- --------------------- --------------------- ---------------------
1           NULL           NULL        NULL                  10.00                 NULL
2           1              1           10.00                 9.00                  1.00
3           1              2           10.00                 8.00                  2.00
6           1              3           10.00                 5.00                  5.00
4           2              1           9.00                  7.00                  2.00
5           2              2           9.00                  6.00                  3.00
7           4              1           7.00                  4.00                  3.00
8           4              2           7.00                  3.00                  4.00
9           6              1           5.00                  2.00                  3.00

Tạo cây bằng phương pháp đệ quy trong Sql server

Trong nhiều trường hợp xử lý số liệu trên sql server, chúng ta cần tạo một danh sách theo dạng hình cây để hiển thị thông tin một cách trực quan. Ví dụ như hệ thống tài khoản trong kế toán, cây thư mục của window.. đó là các mô hình biểu diển thông tin theo dạng hình cây.

Phương án:

Có rất nhiều cách khác nhau để có thể đem lại một kết quả như trên. Nhưng nếu bạn làm việc với Sql server thì có một phương án rất hiệu quả và tối ưu đó chính là sử dụng RCTE (recursive common table expression)

Thực hiện:

Ta lấy ví dụ về mô hình hoạt động của một công ty, gồm các thông tin nhân viên, chức vụ

Ta có bảng nhân viên gồm các trường Id nhân viên, Tên nhân viên, Id người quản lý

 

-- Tạo bảng tạm
IF OBJECT_ID('Tempdb..#Employee', 'U') IS NOT NULL
    DROP TABLE #Employee

CREATE TABLE #Employee (EmployeeID INT,
                    EmployeeName NVARCHAR(128),
                    ManagerID INT)

-- Insert các dử liệu
INSERT INTO #Employee VALUES(1, N'Nguyễn Văn Z', NULL)
INSERT INTO #Employee VALUES(21, N'Nguyễn Văn B', 1)
INSERT INTO #Employee VALUES(2, N'Nguyễn Văn C', 21)
INSERT INTO #Employee VALUES(5, N'Nguyễn Văn D', 21)
INSERT INTO #Employee VALUES(4, N'Nguyễn Văn V', 1)

-- Kiểm tra
SELECT * FROM #Employee ORDER BY EmployeeName ;

-- Tiến hành đệ quy bằng RCTE
WITH DirectReports (EmployeeID, ManagerID, EmployeeName, EmployeeLevel, Sort)
AS
(
    -- Mệnh đề đệ quy
    SELECT /* Thông tin chung có thể joine với các bảng khác */
            e.EmployeeID,
            e.ManagerID,
            e.EmployeeName,
            /* Bậc bắt đầu tính */
            1 AS EmployeeLevel,
            /* Chỉ tiêu sắp xếp dử liệu */
            CAST(e.EmployeeName AS NVARCHAR(255)) AS Sort
    FROM #Employee e
    WHERE e.ManagerID IS NULL

    -- Ràng buộc đệ quy
    UNION ALL
    SELECT /* Thông tin chung có thể joine với các bảng khác */
            e.EmployeeID,
            e.ManagerID,
            e.EmployeeName,
            /* Bậc sẻ được tăng lên dần */
            d.EmployeeLevel + 1 AS EmployeeLevel,
            /* Chỉ tiêu sắp xếp dử liệu */
            CAST(RTRIM(d.Sort) + e.EmployeeName AS NVARCHAR(255)) AS Sort
    FROM #Employee e
        -- Bắt buộc của đệ quy là kết quả phải đi đến giới hạn
        INNER JOIN DirectReports d ON d.EmployeeID = e.ManagerID
)
SELECT EmployeeID,
        REPLIcate(' + ', (EmployeeLevel - 1)) + EmployeeName AS EmployeeName, EmployeeLevel
    FROM DirectReports ORDER BY Sort

-- Xóa bảng tạm
DROP TABLE #Employee

 

 

Kết quả:

EmployeeID  EmployeeName         ManagerID
----------- -------------------- -----------
21          Nguyễn Văn B         1
2           Nguyễn Văn C         21
5           Nguyễn Văn D         21
4           Nguyễn Văn V         1
1           Nguyễn Văn Z         NULL

(5 row(s) affected)

EmployeeID  EmployeeName                   EmployeeLevel
----------- ------------------------------ -------------
1           Nguyễn Văn Z                   1
21           + Nguyễn Văn B                2
2            +  + Nguyễn Văn C             3
5            +  + Nguyễn Văn D             3
4            + Nguyễn Văn V                2

(5 row(s) affected)

Sử dụng Transaction trong SQL Server

Có 2 cách để kiểm soát Transaction trong SQL Server
Cách 1: bằng cách kiểm tra biến toàn cục @@ERROR để xác định nếu có lỗi xảy ra thì thực hiện rollback. Đây là cách cổ điển của SQL Server 2000. Điểm bất tiện của nó là sau mỗi câu lệnh SQL, ta phải thực hiện việc kiểm tra giá trị của biến này, để kịp thời rollback.

CREATE PROC For_TransTest
AS
BEGIN
    BEGIN TRANSACTION updatestaff;

    UPDATE aspnet.staff SET fk_department = 30
    WHERE pk_staff = 971;

    IF @@ERROR != 0  -- neu co loi xay ra, rollback 
    BEGIN
        PRINT 'rollback ne';
        ROLLBACK TRAN updatestaff;
    END

    UPDATE aspnet.staff SET fk_department = null
    WHERE pk_staff = 971;

    IF @@ERROR != 0  -- neu co loi xay ra, rollback 
    BEGIN
        PRINT 'rollback ne';
        ROLLBACK TRAN updatestaff;
    END
    ELSE
        COMMIT TRANSACTION updatestaff;

END

Cách 2: Sử dụng lệnh TRY...CATCH, hỗ trợ trên SQL Sever 2005, rất đơn giản và dễ dùng

Ví dụ:

CREATE PROC For_TransTest
AS
BEGIN
    BEGIN TRY

        BEGIN TRANSACTION updatestaff;

        UPDATE aspnet.staff SET fk_department = 30
        WHERE pk_staff = 971;

        UPDATE aspnet.staff SET fk_department = null
        WHERE pk_staff = 971;


        COMMIT TRANSACTION updatestaff;
        PRINT 'commited';
    END TRY
    BEGIN CATCH
        PRINT 'rollback ne';
        ROLLBACK TRAN updatestaff;  
    END CATCH
END

Cấu hình MSSQL chạy port 1433 trong Windows 2008 R2

Sau khi cài đặt thành công ta tiến hành mở port 1433 bằng cách tạo một file .bal có nội dung như bên dưới và chạy nó 

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Bước tiếp theo là add file sqlservr.exe vào tường lửa bằng cách vào Run gõ firewall.cpl 

Nhấn vào Allow programs to communicate through Windows Firewall

Nhấn Allow another program chọn đường dẫn đến thư mục vài Mssql chọn sqlservr.exe tui cài để mặc định nên như vầy 

SQL 2008 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

SQL 2005 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

Vậy là bạn đã có thể chạy sql trên port 1433 rồi đó

 

Làm sao để shrink database?

/*set database sang chế độ simple*/
Declare @dbname varchar(200),@sql varchar(200)/*khai báo biến*/
SET @dbname = 'traugia_conon'/*gán tên db*/
set @sql ='alter database '+@dbname+' SET RECOVERY SIMPLE'/*chuyển sang simple*/
exec (@sql)/*thực thi lệnh*/
/*tém nó*/
DBCC SHRINKDATABASE(@dbname,10)/*shrink-->gọn nhẹ không ngờ*/