MS-SQL 分頁 (SQL Paging)

MS-SQL 的分頁語法一直是很多程式人員的痛，但是微軟也不是沒有改進

在 SQL2005 的時候，推出了  ROW_NUMBER() 函數，讓程式人員寫分頁語法的時候負擔小了一點

現在 SQL 的分頁語法終於有比較快速的方法了.............
如果我要抓 第20筆到 30 筆的資料可以用下面的語法

SELECT * FROM Orders
ORDER BY OrderID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

還沒說完，請繼續往下看~~

很可惜的是上面的語法僅支援 SQL2011、SQL Server CE4.0，真是殘念>"<

所以還是乖乖的用 CTE 加上 ROW_NUMBER() 的方式來做

;

WITH CTEResults AS 
(     SELECT
         IDColumn,
         SomeField,
         DateField ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
    FROM MyTable ) 
SELECT *,
(SELECT MAX(RowNum) FROM CTEResults)  as TotalRows  ---### 這裡會回傳總筆數 FROM CTEResults WHERE RowNum BETWEEN 20 AND 30;

from : https://blog.xuite.net/dlnk/work/51786477-MS-SQL+%E5%88%86%E9%A0%81+%28SQL+Paging%29

MSSQL Performance tuning statement

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DBCC FreeProcCache
DBCC DropCleanBuffers

Select *
FROM users
WHERE DisplayName='xxx'

MSSQL TRY CATCH

BEGIN TRANSACTION
BEGIN TRY
    INSERT news_group (newsid, groupid)
    VALUES (99, 99)
   
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS ErrMsg
    ROLLBACK TRANSACTION
END CATCH

MSSQL COALESCE

DECLARE @items AS VARCHAR(MAX)

SELECT @items = COALESCE(@items + ',', '') + item_text
FROM vote_items WHERE voteid = 10

SELECT @items

Using dynamic SQL to handle multiple optional parameters

Previously I mentioned a way to handle stored procedures with multiple optional parameters, that of the recompile hint. That's fine in many cases but since it prevents plan caching for that query, it can result in higher CPU usage, This may can be unacceptable if the procedure is called very frequently or if the server is already under CPU load.

For those cases, converting the procedure to use dynamic SQL may be a better solution.

Let's start with the same example as last time

1

SELECT ProductID, 

2

    ReferenceOrderID, 

3

    TransactionType, 

4

    Quantity,

5

    TransactionDate, 

6

    ActualCost 

7

FROM Production.TransactionHistory

8

WHERE (@ProductID IS NULL OR ProductID = @ProductID)

9

    AND (@OrderStatus IS NULL OR OrderStatus = @OrderStatus)

10

    AND (@OrderTotal IS NULL OR OrderTotal >= @OrderTotal)

11

    AND (@RegionID IS NULL OR Region = @RegionID);

When using Dynamic SQL, there is one very important requirement. User input must NOT be concatenated into a string that will be executed. Doing so causes SQL injection vulnerabilities. SQL Injection has been the cause of a number of high-profile data breaches recently.

Fortunately, concatenating input into the string isn't necessary (it's the easy way, hence why so much of it is seen). Dynamic SQL can be parameterised.

Firstly, the variables and the initial portion of the select statement

1

DECLARE @SQL NVARCHAR(4000), @Where NVARCHAR(4000) = ''

2

SET @SQL = 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost

3

from Production.TransactionHistory '

The variables do need to be nvarchar.

For each of the parameters which may or may not be passed, an IF block checks whether or not to add that predicate to the where clause

1

IF (@Product IS NOT NULL)

2

    SET @Where = @Where + 'AND ProductID = @_Product '

3

IF (@OrderID IS NOT NULL)

4

    SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '

5

IF (@TransactionType IS NOT NULL)

6

    SET @Where = @Where + 'AND TransactionType = @_TransactionType '

7

IF (@Qty IS NOT NULL)

8

    SET @Where = @Where + 'AND Quantity = @_Qty '

Note the use of new parameters inside the dynamic SQL. The values which are being checked are not being added to the string.

You can start the where clause off with WHERE 1=1 AND ..., but I personally dislike that pattern, I rather do this

1

IF (LEN(@Where) > 0)

2

    SET @SQL = @SQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)

Now we have a dynamic SQL string which just contains the predicates that are actually going to filter the data. All that's left is to execute it. This is done with the sp_executeSQL system procedure, allowing for parameters to be passed to the dynamic string.

The first parameter to sp_executeSQL is the string to be executed. The second is the parameter list, the third onwards assigns values to the parameters

1

EXEC sp_executesql @SQL,

2

    N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int',

3

    @_Product = @Product, @_OrderID = @OrderID, @_TransactionType = @TransactionType, @_Qty = @Qty

from : https://yoda.entelect.co.za/view/3279/using-dynamic-sql-to-handle-multiple-optional-parameters