Archive

Archive for the ‘SQL’ Category

Bảng tạm trong MS SQL Server – Temporary tables in MS SQL Server

Khi làm việc với MS SQL Server, hẳn bạn cũng khá quen thuộc với khái niệm bảng tạm (Temporary table). Ít nhiều bạn đã từng làm hoặc từng gặp các đoạn:

SELECT Col1, Col2 INTO #tTmpTable FROM TableName WHERE ...

hay

CREATE TABLE #tTmpTable (col1 varchar(8), ...)

Trong bài viết này mình xin trình bày với các bạn cái nhìn tổng quan về Temporary table.

Các loại bảng tạm

MS SQL Server cung cấp cho chúng ta 2 loại bảng tạm là Local Temporary table Global Temporary table. Mỗi loại bảng tạm có phạm vi ảnh hưởng và truy cập khác nhau.

Local temporary table là bảng tạm được tạo ra và tồn tại trong 1 kết nối. Khi kết thúc kết nối thì bảng tạm này sẽ tự động được xóa. Tên của bảng tạm kiểu Local được bắt đầu bằng ký tử #.

Global temporary table là bảng tạm có phạm vi ảnh hưởng trong tất cả các kết nối và nó chỉ tự động được xóa đi khi tất cà kết nối đã được ngắt. Tên bảng tạm kiểu Global được bắt đầu  bằng ##.

Để minh chứng cho 2 loại bảng tạm này, các bạn hãy làm thử ví dụ sau:

  • Mở Microsoft SQL Server Management Studio (SSMS) và chọn [New Query].
  • Gõ đoạn lệnh sau:
CREATE TABLE #LocalTmpTable
(
    ID int,
    Name nvarchar(32)
)
    Đoạn lệnh này sẽ tạo ra trong Database

Tempdb 

    một bảng có tên là #LocalTmpTable____000002 (xem hình)

image

SQL Server sẽ tự động tăng số thứ tự vào phía sau tên bảng nhằm quản lý việc nhiều kết nối tạo bảng tạm có cùng tên.

  • Gõ đoạn sau và F5 để kiểm tra, bạn sẽ nhận được kết quả là ID của bảng tạm đã tạo.
PRINT OBJECT_ID('Tempdb.dbo.#LocalTmpTable')
  • Mở 1 cửa sổ Query khác bằng [New Query], gõ lại đoạn lệnh trên và F5 bạn sẽ không nhận được kết quả.

Để Test về phạm vi của Global Temp table, bạn thực hiện lại các thao tác trên với tên bảng là ##GlobalTmpTable.

Tạo và thao tác trên bảng tạm

Để tạo bảng tạm, bạn có thể sử dụng lệnh CREATE TABLE

CREATE TABLE #LocalTmpTable ( ID int, Name nvarchar(32) )

hoặc sử dụng lệnh SELECT INTO

SELECT Ma, Ten INTO ##GlobalTmpTable FROM TableName WHERE .........
Với Temporary Table, bạn  hoàn toàn có thể truy vấn, thao tác dữ liệu như đối với các table bình thường.

Sử dụng bảng tạm

Bảng tạm và biến bảng thường được sử dụng khi cần xử lý một tập dữ liệu con trong tạp dữ liệu lớn được lưu trữ trong Table của Database hoặc từ nhiều Tables. (Đặc biệt là khi viết các Store procedures xử lý báo cáo).

Bảng tạm được tạo ra trong Database Tempdb khác với Database của bạn vì thế có thể gây ra các vấn đề về hiệu suất . Do đó, bạn cần tính toán kỹ số lượng các Fields cần thiết phải lấy ra.

Tham khảo

  1. Quick Overview: Temporary Tables in SQL Server 2005
Categories: SQL Tags:

SQL: Linked Server – truy vấn dữ liệu từ xa thông qua linked server

Đôi khi vì một lý do gì đó mà bạn phải truy vấn dữ liệu từ một database đặt tại server khác (hoặc một instance khác của SQL Server) – ví dụ: so sánh dữ liệu giữa 2 database, hay các giải pháp phân tán …

Bạn sẽ làm thế nào? Tôi xin giới thiệu với các bạn sử dụng Linked Server để giải quyết vấn đề này.

Trong bài này, tôi giả sử tôi đang ở SQL server 2000 máy local (instance là CHUNGTV\SQL2K), tôi cần lấy dữ liệu của bảng DMKH ở database HungLong để tại SQL Server 2008 trên máy chủ (instance là ServerDB\SQL2008)

Cách 1: Cấu hình bằng giao diện

[1.] Mở trình quản lý database lên, hiện tôi đang dùng Microsoft SQL Server Management Studio 2008 (10.x); đối với Microsoft SQL Server Management Studio 2005 (9.x) thì làm tương tự, với Microsoft SQL Enterprise Manager 8 (2000) thì có khác một ít tôi sẽ nói sau.

[2.] Expand thư mục Server Objects, sẽ thấy thư mục Linked Servers (xem hình)

image

P/s: Đối với Microsoft SQL Enterprise Manager 8 (2000) thì phần Linked Servers lại nằm trong phần Security

image

3. Righ-click lên mục Linked Servers, và chọn New Linked Servers …, màn hình “New Linked Server” sẽ hiện lên.

image

Tại phần General, bạn nhập các thông tin:

    – Linked server: Tên bạn đặt cho linked server sẽ được truy xuất khi bạn thực hiện truy vấn dữ liệu (ở đây tôi đặt là SERVERDB2K8)
    – Server type: Bạn chọn là Other data source
    – Provider: Chọn Microsoft OLE DB Provider for SQL Server (ở đây bạn có thể chọn loại khác tùy vào nguồn dữ liệu bạn sẽ link tới – có thể là Access, Oracle, Excel …)
    – Product name: đặt tên product (ở đây tôi đặt là Sql2K8)
    – Data source: nhập instance của server bạn muốn link tới (ở đây tôi gõ ServerDB\SQL2008)
    – Các thông tin khác để mặc định.

Tại phần Security:

image

    – Bạn chọn tùy chọn “Be made using this security context”
    – Nhập user đăng nhập vào SQL tại “Remote login:”
    – Nhập Password đăng nhập tại “With password:”

   

Sau đó bạn nhấn OK.

[3.] Thực hiện truy vấn dữ liệu

Bán chọn New Query và gõ câu truy vấn dữ liệu thử. Ví dụ:

SELECT TOP 100 * FROM SERVERDB2K8.HungLong.dbo.DMKH

Xem thử kết quả.

Cách 2: Thực hiện bằng lệnh

Tại cửa sổ query, bạn hoàn toàn có thể thực hiện các công việc như bước 1 bằng cách sau:

[1. ] Tạo mới Linked server bằng lệnh sau:

EXEC master.dbo.sp_addlinkedserver @server = N’SERVERDB2K8′, @provider=N’SQLOLEDB’, @datasrc=N’ServerDB\SQL2008′, @srvproduct=N’Sql2K8′

[2. ] Kiểm tra đã tạo thành công chưa bằng:

EXEC sp_linkedservers

kết quả:

image

[3. ] Đăng nhập vào Linked server

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’SERVERDB2K8′, @useself=N’False’, @locallogin=NULL, @rmtuser=N’tentruycap’, @rmtpassword=’matkhautruycap’

[4. ] Kiểm tra kết quả

SELECT TOP 100 * FROM SERVERDB2K8.HungLong.dbo.DMKH

 

Chúc các bạn thành công.

Categories: SQL Tags: , ,

Hàm đếm số ngày thứ trong 1 khoảng thời gian

Đôi khi mình cần phải đếm số thứ trong một khoảng thời gian nào đó. Ví dụ: Cần đếm xem trong tháng 11 có bao nhiêu ngày thứ 2, hay bao nhiêu ngày thứ 3.

Áp dụng thực tế: Cần tính dự kiến thu tiền trong tháng cho 1 hợp đồng mua bán trả góp với hình thức góp theo tuần.

User Defined Function sau đây cung nhận vào 3 tham số ngày bắt đầu, ngày kết thúc và ngày thứ cần đếm. Trả về số ngày thứ trong khoảng thời gian truyền vào (từ ngày bắt đầu đến ngày kết thúc)

-- =============================================
-- Author:        Chungtv
-- Create date: 11/11/2010
-- Description:    Dem so ngay thu trong mot khoang thoi gian bat ky
-- @pBeginDate: Ngay bat dau khoang thoi gian can dem
-- @pEndDate: Ngay ket thuc khoang thoi gian can dem
-- @pWeekday: Ngay thu can dem (CN~1; Thu2~2, Thu3~3 ...)
-- Example:
--        SET DATEFORMAT DMY
--        SET DATEFIRST 1
--        PRINT [dbo].[afCountOfWeekday]('01/11/2010','30/11/2010',2)
-- =============================================
CREATE FUNCTION [dbo].[afCountOfWeekday]
(
    @pBeginDate smalldatetime,
    @pEndDate smalldatetime,
    @pWeekday int
)
RETURNS int
AS
BEGIN
    declare @iBeginWd int, @d1 int, @d2 int
    set @iBeginWd = datepart(dw,@pBeginDate)
    set @d1 = datediff(day,@pBeginDate,@pEndDate) 
    if @pWeekday >= @iBeginWd
        set @d2 = @pWeekday - @iBeginWd
    else
        set @d2 = @pWeekday + 7 - @iBeginWd
    return (@d1 - @d2) / 7 + 1
END

Categories: SQL Tags:

Sử dụng COLUMNS_UPDATED() để kiểm tra trường được update trong trigger

1. ĐẶT VẤN ĐỀ

Có khách hàng làm về ô tô, có cái bảng dữ liệu SECTROPT lưu thông tin chi tiết phụ tùng được xuất ra để sửa chữa xe. Sau khi sửa xong thì kế toán xuất hóa đơn. 1 Hóa đơn (thông tin hóa đơn được lưu trong bảng PH51) có thể xuất được cho nhiều dòng trong SECTROPT vì thế thiết kế dữ liệu trong bảng SECTROPT phải lưu lại khóa của hóa đơn (đặt tên trường là Stt_rechd, khóa của  bảng PH51 là Stt_rec).

Để đáp ứng nhu cầu truy vấn nhanh hơn thì lưu thêm cái So_hd vào bảng SECTROPT nữa (nếu không cứ mỗi khi cần truy vấn số hóa đơn lại phải JOIN với PH51 thì mất công lắm).

Vấn đề đặt ra là:

  • sau khi lưu thông tin hóa đơn xong thì Stt_rechd và so_hd đã được lưu vào SECTROPT.
  • Khi User sửa số hóa đơn thì chương trình phải update lại trường so_hd trong bảng SECTROPT. => viết trigger UPDATE cho PH51 là OK.
  • Tuy nhiên, So_hd thì ít khi sửa (chủ yếu sửa mấy cái diễn giải) nên không lẽ cứ mỗi lần sửa – lưu thì trigger lại phải chạy?

Nói chung vào đề hơi dài dòng. Nói tóm lại là sau một hồi Research (cái này từ năm 2007 rồi, hôm nay nhớ ra mới viết) thì sử dụng hàm COLUMNS_UPDATED() để kiểm tra xem trường nào được update, nếu trường đó là So_hd thì mới thực thi update vào SECTROPT.

2. Về COLUMNS_UPDATED()

Chi tiết thì có thể hỏi “anh Hai google”, ở đây xin giới thiệu cái cơ bản của hàm COLUMNS_UPDATED() thôi.

Đại khái, hàm này sẽ trả về giá trị dạng nhị phân có ý nghĩa, chỉ ra vị trí cột nào được insert hoặc update

Để kiểm tra cột nào được thay đổi mình sử dụng bit mặt nạ tương ứng cho vị trí của mỗi cột.

 

Để kiểm tra các cột có vị trí <=8

Cột tại vị trí n (n<=8) có phải là cột thay đổi hay không thì sử dụng biểu thức sau:

COLUMNS_UPDATED() & Power(2,(n-1))

Nếu biểu thức đó >0 thì cột n đã thay đổi, ngược lại cột n không thay đổi.

Trong trường hợp kiểm tra 2 hoặc nhiều cột có thay đổi hay không thì làm tương tự. Ví dụ kiểm tra cột 3 cột i,j,k có thay đổi hay không (i,j,k<=8) thì sử dụng biểu thức sau:

COLUMNS_UPDATED() & (Power(2,(i-1)) + Power(2,(j-1)) + Power(2,(k-1)))

Chú ý: Để đỡ phải tính toán khi thực thi thì mình tính ra giá trị nguyên của các hàm Power() luôn rồi cho COLUMNS_UPDATED() & kq (là giá trị của biểu thức sau &)

 

Để kiểm tra các cột có vị trí >8

Sử dụng hàm SUBSTRING() cho giá trị trả về của COLUMNS_UPDATED().

Ví dụ cần kiểm tra có update cột 10 hay không, sử dụng biểu thức sau:

SUBSTRING(COLUMNS_UPDATED(),2,1) & Power(2,(10-8-1))

Còn nếu cột cần kiểm tra > 16 thì làm tương tự. Ví dụ cần kiểm tra cột 19 có Update không thì sử dụng biểu thức:

SUBSTRING(COLUMNS_UPDATED(),3,1) & Power(2,(19-16-1))

3. TRIGGER

Còn đây là trigger cho đề bài ở trên.

CREATE TRIGGER [dbo].[TG_UpdatePh51]
ON [dbo].[ph51]
AFTER UPDATE
AS

IF SUBSTRING(COLUMNS_UPDATED(),2,1) & 4> 0 –- Cột So_hd là cột thứ 11; power(2,(11-8-1)) =4
BEGIN
    declare @So_hd varchar(8), @stt_rechd varchar(20)
    SELECT @So_hd = So_hd, @stt_recOld = Stt_rec from inserted

    UPDATE SECTROPT
    SET So_hd = @So_hd
    WHERE Stt_rechd  = @stt_rechd
END

Categories: SQL