Một số bài báo hay

http://www.sqlviet.com/blog/tim-kiem-voi-nhieu-tham-so/

http://www.sqlviet.com/blog/cau-hinh-sql-server-de-gui-mail/

http://www.sqlviet.com/blog/tao-sql-job-backup-database-hang-ngay/

SQL động

http://www.sqlviet.com/blog/sql-dong/

http://www.sqlviet.com/blog/sql-dong/2

Cách 1:

DECLARE @SqlStr NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SqlStr = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID AND OrderQty > @MinQty'
EXEC SP_EXECUTESQL @SqlStr, N'@ProductID INT, @MinQty INT', 1, 1000

Cách 2:

DECLARE @SqlStr NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SqlStr = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID AND OrderQty > @MinQty'
EXEC SP_EXECUTESQL @SqlStr, N'@ProductID INT, @MinQty INT', @ProductID = 1, @MinQty = 1000

Phân giải tên đối tượng

Đoạn code sau sẽ báo lỗi:

DECLARE @SqlStr NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM @Tenbang2 WHERE ProductID = @ProductID2'
SET @ParamDefinition = N'@Tenbang2 NVARRCHAR(100),@ProductID2 int'
EXEC SP_EXECUTESQL
@SqlStr = @SQL,
@params = @ParamDefinition,
@Tenbang2 = @Tenbang,
@ProductID2 = @ProductID

Bạn cần viết như sau (nhớ luôn luôn dùng hàm QUOTENAME với tên đối tượng để ngăn chặn sql injection):

DECLARE @STATEMENT NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@Tenbang) + ' WHERE ProductID = @ProductID2'
SET @ParamDefinition = N'@ProductID2 int'
EXEC SP_EXECUTESQL
@STATEMENT = @SQL,
@params = @ParamDefinition,
@ProductID2 = @ProductID

Tìm kiếm với nhiều tham số

Khi ta cần viết một thủ tục để tìm kiếm dữ liệu dựa vào các tham số đầu vào, ta có thể hình dung ra logic sẽ như sau:

IF @Param1 IS NOT NULL
SELECT... FROM dbo.Tblxxx WHERE Col1= @Param1
ELSE
SELECT TOP 200 ... FROM dbo.Tblxxx -- TOP 200 để khống chế số bản ghi khi không có tham số

Tức là khi tham số vào @Param1 được truyền giá trị thì ta lọc các bản ghi dựa trên giá trị đó, còn nếu không (NULL) thì ta không lọc. Tuy nhiên cách làm trên không thể mở rộng với nhiều tham số, vì số nhánh chương trình sẽ tăng rất nhanh (2^n). Ví dụ nếu ta có hai tham số @Param1 và @Param2, đoạn code sẽ giống như thế này:

IF (@Param1 IS NOT NULL) AND (@Param2 IS NOT NULL)
...
ELSE IF (@Param1 IS NOT NULL) AND (@Param2 IS NULL)
...
ELSE IF (@Param1 IS NULL) AND (@Param2 IS NOT NULL)
...
ELSE
...

Không những code rất cồng kềnh mà nó còn rất khó bảo trì. Nếu đến một lúc ta cần thêm một tham số thứ ba @Param3, sẽ tốn rất nhiều công để sửa lại và viết thêm vào đoạn code trên. Hoặc nếu cần thêm một cột vào kết quả đầu ra, ta sẽ phải thêm vào tất cả các nhánh của chương trình. Có thể nói cách làm trên là không khả thi trong đa số trường hợp.

Bài viết này giới thiệu hai cách làm có thể áp dụng trên thực tế, nhưng trước hết tôi nói qua về ví dụ sẽ được sử dụng trong bài.Ta có một database về các bộ phim đã được sản xuất, và giả sử ta cho phép tìm kiếm theo các tiêu chí sau:

@Tenphim:Tên của bộ phim

@NamsxMin: Từ năm sản xuất

@NamsxMax: Đến năm sản xuất

@Nuocsx: Nước sản xuất

@Theloai: Thể loại phim (hành động/hài/chính kịch…)

Cách làm thứ nhất

CREATE PROCEDURE dbo.TimKiemPhim_1
    @Tenphim NVARCHAR(50),
    @NamsxMin INT,
    @NamsxMax INT,
    @Nuocsx NVARCHAR(50),
    @Theloai NVARCHAR(50)
AS
SELECT P.*
FROM dbo.Phim P
WHERE (@Tenphim IS NULL OR P.Tenphim like '%'+@Tenphim+'%')
AND (@NamsxMin IS NULL OR P.Namsx >= @NamsxMin)
AND (@NamsxMax IS NULL OR P.Namsx <= @NamsxMax)
AND (@Nuocsx IS NULL OR P.Nuocsx = @Nuocsx)
AND (@Theloai IS NULL OR P.Theloai = @Theloai)

Trong cách làm này ta khai thác trị chân lý của mệnh đề OR – khi tham số @p là NULL, tức là “@p IS NULL” đúng, thì cả mệnh đề ở mỗi dòng AND đúng. Do đó chỉ khi @p được truyền giá trị thì điều kiện tìm kiếm mới được thực hiện. Như vậy code trông đã gọn hơn, mà mở rộng cũng rất dễ dàng, khi cần bổ sung thêm một tham số thì ta chỉ cần viết thêm một dòng lệnh.

Có những trường hợp khi một tham số nào đó được cung cấp ta cần truy nhập vào bảng khác . Giả sử có thêm tham số @TenDienvien để tìm các phim có một diễn viên nào đó tham gia; và giả sử bảng dbo.Dongphim (Đóng phim) chứa tên các diễn viên tham gia đóng phim và quan hệ của bảng dbo.Phim với bảng này là 1-nhiều (mỗi phim có nhiều diễn viên tham gia). Ta có thể thêm đoạn code sau:

AND (@TenDienvien IS NULL OR
     EXISTS(SELECT 1 FROM dbo.Dongphim D
            WHERE D.PhimID = P.PhimID AND D.TenDienvien like '%'+@TenDienvien+'%')
)

Trong một số tình huống, thủ tục trên có thể chạy rất nhanh ở lần thực hiện đầu nhưng lại chậm hơn nhiều ở lần tiếp theo, khi các tham số tìm kiếm khác với lần đầu. Nguyên nhân của nó là hiện tượng “parameter sniffing” (tôi sẽ nói ở dịp khác). Một cách để khắc phục là thêm lựa chọn “WITH RECOMPILE” vào đoạn khai báo thủ tục, ngay trước từ khóa AS.

Ví dụ về WITH RECOMPILE

EXEC MyStoredProc '12/31/2004' WITH RECOMPILE

Cách làm thứ hai

Dùng sql động, xây dựng chuỗi sql động dựa trên các tham số đầu vào và thực thi chuỗi sql đó.

CREATE PROCEDURE dbo.TimKiemPhim_2
    @Tenphim NVARCHAR(50) = NULL,
    @NamsxMin INT = NULL,
    @NamsxMax INT = NULL,
    @Nuocsx NVARCHAR(50) = NULL,
    @Theloai NVARCHAR(50) = NULL
AS
DECLARE @SqlStr NVARCHAR(MAX),
        @ParamList NVARCHAR(2000)
SELECT @SqlStr = '
       SELECT P.*
       FROM dbo.Phim P
       WHERE (1=1)
       '
IF @Tenphim IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Tenphim like '''%'+@Tenphim2+'%''')
              '
IF @NamsxMin IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Namsx >= @NamsxMin2)
              '
IF @NamsxMax IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
             AND (P.Namsx <= @NamsxMax2)
             '
IF @Nuocsx IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Nuocsx = @Nuocsx2)
              '
IF @Theloai IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Theloai = @Theloai2)
              '
SELECT @Paramlist = '
       @Tenphim2 NVARCHAR(50),
       @NamsxMin2 INT,
       @NamsxMax2 INT,
       @Nuocsx2 NVARCHAR(50),
       @Theloai2 NVARCHAR(50)
       '
EXEC SP_EXECUTESQL @SqlStr,
                   @Paramlist,
                   @Tenphim,
                   @NamsxMin,
                   @NamsxMax,
                   @Nuocsx,
                   @Theloai

Với cách làm này việc viết code có rườm rà và khó theo dõi hơn. Tuy nhiên trong một số trường hợp cách này lại có ưu điểm hơn cách thứ nhất:

1. Thủ tục sp_executesql sẽ lưu kế hoạch thực thi cho mỗi bộ tham số, do đó nó giải quyết vấn đề “parameter sniffing” một cách thông minh hơn so với cách thứ nhất (luôn luôn phải biên dịch lại).

2. Trong trường hợp ta cần SELECT dữ liệu từ các bảng khác nhau tùy theo tham số được truyền. Ví dụ ta có tham số @Phimkinhdien kiểu BIT, khi bằng 1 thì cần SELECT từ bảng dbo.Phimkinhdien, khi bằng 0 thì SELECT từ bảng dbo.Phim như trên. Với cách làm dùng sql động ta có thể dễ dàng làm như sau:

...
SELECT @SqlStr = '
              SELECT P.*
              FROM ' + CASE WHEN @Phimkinhdien=1 THEN 'dbo.Phimkinhdien' ELSE 'dbo.Phim' END+'
              WHERE (1=1)
              '

Với cách làm thứ nhất, ta không có cách nào khác là tạo thêm một nhánh, trong đó lặp lại câu lệnh SELECT và thay bảng dbo.Phim bằng dbo.Phimkinhdien

Bổ sung: một số bạn viết thư hỏi dùng EXEC thay cho sp_executesql có được không. Câu trả lời là bạn nên dùng sp_executesql và tránh EXEC, vì sp_executesql tăng khả năng dùng lại kế hoạch thực thi, trong khi EXEC luôn dẫn đến thủ tục phải biên dịch lại. Một lý do nữa là sp_executesql tránh được lỗi SQL injection, EXEC thì gặp lỗi này. Tôi sẽ trở lại vấn đề so sánh giữa sp_executesql và EXEC trong một dịp khác.
Một bạn nêu trường hợp các cột cần trả về thay đổi tùy theo giá trị của tham số, ví dụ nếu tham số @p=1 thì SELECT các cột col1, col2, col3, còn nếu @p=2 thì SELECT col4, col5, col6. Khi đó cách làm thứ hai ở trên có thể áp dụng dễ dàng, và đây cũng là một trường hợp nó có ưu thế hơn cách làm thứ nhất.

Một số điều thú vị trong SQL Server

-- Kiểm tra xem database của bạn có cài đặt FULLTEXT SEARCH hay không?
SELECT SERVERPROPERTY('IsFullTextInstalled')

-- Bạn chạy 2 lệnh sau rồi hãy chạy lệnh trên, nó sẽ in ra chi tiết các thông tin vào ra và thời gian xử lý của CPU:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Lấy bản ghi tuần tự

SELECT *
FROM Sales.Customer;

Lấy bản ghi ngẫu nhiên

SELECT C.*
FROM Sales.Customer C
JOIN ( SELECT CustomerID ,
ROW_NUMBER() OVER ( PARTITION BY TerritoryID ORDER BY NEWID() ) AS ID
FROM Sales.Customer
) RC -- Random Customer
ON C.CustomerID = RC.CustomerID
WHERE RC.ID <= 2
ORDER BY C.TerritoryID ,
C.CustomerID;

Hiểu thêm về Index trong SQL

Xem xét ví dụ sau

USE AdventureWorks
GO
-- câu lệnh 1 (non-sargable)
SELECT * FROM Sales.Individual
WHERE CustomerID+2 = 11002
 
-- câu lệnh 2 (sargable)
SELECT * FROM Sales.Individual
WHERE CustomerID = 11000

Hai câu lệnh trên cho cùng một kết quả, nhưng ở câu lệnh 1 điều kiện tìm kiếm của nó là non-sargable và index trên cột CustomerID trở nên vô dụng. Kế hoạch thực thi của nó cho thấy điều này:

Câu lệnh 1 dẫn đến thao tác Clustered Index Scan, tức là quét cả cây clustered index, đồng nghĩa với quét bảng (vì clustered index chính là bảng). Do vậy mà chi phí của nó tăng vọt. Sở dĩ index đã không được sử dụng vì khi bạn áp dụng một phép tính toán trên cột, hệ thống phải thực hiện tính toán đó trên từng node trên cây index trước khi có thể lấy kết quả để so sánh với giá trị cần tìm. Vì thế nó phải duyệt tuần tự qua từng node thay vì tìm theo kiểu nhị phân (index seek, như với câu lệnh 2). Và đây là các con số thống kê về IO và thời gian thực hiện:
Câu lệnh 1 (non-sargable):
Table 'Individual'. Scan count 1, logical reads 3088, physical reads 35
CPU time = 0 ms, elapsed time = 259 ms.

Câu lệnh 2 (sargable):
Table 'Individual'. Scan count 0, logical reads 3, physical reads 3
CPU time = 0 ms, elapsed time = 19 ms.

Trên đây là một trường hợp đơn giản mà có lẽ không mấy ai mắc phải, tuy nhiên có những tình huống khác không hiển nhiên như thế. Ví dụ, khi cần tìm tất cả các đơn hàng được thực hiện trong ngày 21/08/2009, một cách trực giác có thể bạn nghĩ ngay đến một trong các cách làm sau:

SELECT *
FROM dbo.DonHang
WHERE CONVERT(VARCHAR,OrderDate,103) = '21/08/2009' --cắt bỏ phần thời gian, chỉ giữ lại phần ngày
 
-- hoặc
SELECT *
FROM dbo.DonHang
WHERE DATEPART(d,OrderDate) =21
AND DATEPART(m,OrderDate)=8
AND DATEPART(YEAR,OrderDate)=2009

Cả hai cách viết trên đều làm mất tác dụng index trên trường OrderDate. Cách viết đúng phải là:

SELECT *
FROM dbo.DonHang
WHERE OrderDate >= '20090821' AND OrderDate < '20090822'
-- lưu ý chuỗi ngày tháng mặc định của ANSI có dạng yyyymmdd

Một ví dụ khác, bạn cần tìm tất cả các khách hàng có tên bắt đầu bằng chữ C, như Can, Công, Cường… Các cách viết sau là không sargable:

SELECT *
FROM dbo.Customer
WHERE SUBSTRING(Ten,1,1) = 'C'
 
--hoặc
SELECT *
FROM dbo.Customer
WHERE LEFT(Ten,1) = 'C'

Cách viết để thành sargable là:

SELECT *
FROM dbo.Customer
WHERE Ten LIKE 'C%'

Ở đây có một chi tiết thú vị, khi quan sát kế hoạch thực thi của câu lệnh ở ngay trên, bạn sẽ thấy điều kiện “Ten LIKE ‘C%’” được chuyển thành “Ten >=’C’ AND Ten< ‘D’” (vâng, chuỗi cũng so sánh lớn bé được như số), và điều kiện này hoàn toàn thích hợp với việc tìm kiếm bằng index.

Tuy nhiên, nếu bạn thay đổi yêu cầu một chút, tìm các khách hàng có tên chứa chữ ‘C’ ở bất kỳ vị trí nào, không chỉ ở đầu. Câu lệnh của bạn sẽ trở thành:

SELECT *
FROM dbo.Customer
WHERE Ten LIKE '%C%'

Khi đó index trên trường Ten lại bị mất tác dụng, vì hệ thống không có cách nào khác là dừng lại trên từng node, kiểm tra xem giá trị của trường Ten có chứa ký tự ‘C’ hay không. Trên thực tế đây là một thao tác rất tốn kém về tài nguyên, với các bài toàn như thế này cách làm hiệu quả hơn là dùng fulltext index và fulltext search.

Row Count for all Tables in a Database

CREATE TABLE #counts
(
table_name VARCHAR(255) ,
row_count INT
);

EXEC sp_MSforeachtable @command1 = 'INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?';
SELECT table_name ,
row_count
FROM #counts
ORDER BY table_name ,
row_count DESC;
DROP TABLE #counts;

Create login

USE [master]

GO

CREATE LOGIN [manhnguyenv] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [manhnguyenv]

GO

USE [GRPNashTec]

GO

CREATE USER [manhnguyenv] FOR LOGIN [manhnguyenv]

GO

USE [GRPNashTec]

GO

ALTER ROLE [db_owner] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

CREATE USER [manhnguyenv] FOR LOGIN [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [db_owner] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

CREATE USER [manhnguyenv] FOR LOGIN [manhnguyenv] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[Bullet] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[Comms] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[SmartFX_Role] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[SQLBackOffice] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[SQLFrontOffice] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [Bullet] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [Comms_Role] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [db_owner] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [SmartFX_Role] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [SQLBackOffice] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [SQLFrontOffice] ADD MEMBER [manhnguyenv]

GO

Restore Database

Setting a Database to Restricted User Mode

ALTER DATABASE [TestDB] SET RESTRICTED_USER

ALTER DATABASE [TestDB] SET RESTRICTED_USER WITH NO_WAIT

ALTER DATABASE [TestDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [TestDB] SET RESTRICTED_USER WITH ROLLBACK AFTER 300 SECONDS

Restore database

After restore database

ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [TestDB] SET MULTI_USER

Show you who is connected at the time

EXEC sp_who2