/* 일반적인 방법 */
ALTER FUNCTION [dbo].[FN_Split] (
    @str        NVARCHAR(MAX)     -- 원본 문자열
  , @Delimeter  CHAR(1)   = '|'  -- 구분기호(Default '|')
)
RETURNS @SplitedResult TABLE (
    Idx INT IDENTITY(1,1) PRIMARY KEY -- 결과 배열 인덱스
  , Val NVARCHAR(MAX)                 -- 인덱스에 해달하는 결과 문자열
) AS
BEGIN
    -- Declare index
    DECLARE @Idx            INT
    -- Sets the index initialize
    SET @Idx = -1;

    SET @str = ISNULL(@str, '')
    SET @Delimeter = ISNULL(@Delimeter, '|')

    WHILE (LEN(@str) > 0)
    BEGIN
        SET @Idx = CHARINDEX(@Delimeter , @str) ;

        IF (@Idx = 0) AND (LEN(@str) > 0)
        BEGIN
            INSERT INTO @SplitedResult VALUES (RTRIM(LTRIM(@str))) ;
            BREAK ;
        END

        IF (@Idx > 1)
        BEGIN
            INSERT INTO @SplitedResult VALUES (RTRIM(LTRIM(LEFT(@str, @Idx - 1)))) ;
            SET @str = RIGHT(@str, (LEN(@str) - @Idx)) ;
        END
        ELSE
            SET @str = RIGHT(@str, (LEN(@str) - @Idx)) ;
    END

    RETURN
END


/* XML을 이용한 방법 */
IF OBJECT_ID('dbo.Split') IS NOT NULL
    DROP FUNCTION dbo.Split GO
CREATE FUNCTION dbo.Split(@data VARCHAR(MAX), @delimiter VARCHAR(1))
RETURNS TABLE
AS
BEGIN
    DECLARE @textXML XML;
    SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

    RETURN (SELECT  T.split.value('.', 'nvarchar(max)') AS data
            FROM    @textXML.nodes('/d') T(split))
END

Some test results:
--------------------------------
Number of Items: 100,000
SplitString Time: 46 ms
Xml2IntList Time: 983 ms
--------------------------------
Number of Items: 10,000
SplitString Time: 40 ms
Xml2IntList Time: 93 ms
--------------------------------
Number of Items: 1,000
SplitString Time: 38 ms
Xml2IntList Time: 15 ms
--------------------------------
Number of Items: 100
SplitString Time: 25 ms
Xml2IntList Time: 1.5 ms
--------------------------------

DECLARE 
 @value1 varchar(10),
 @value2 varchar(20)

SET @value1 = ''
SET @value2 = 'value'

SELECT * FROM [TABLE] WITH(NOLOCK)
WHERE
     ([COL1] = '' OR [COL1] = @value1)
     AND ([COL2] = '' OR [COL2] = @value2)

위와 같이 사용하면 조건값이 '' 이면 참이 되어 OR 뒤의 조건이 실행되지 않는다.
때문에 [COL1] = @value1의 조건은 무시되고 [COL2] = @value2 조건만 검색하게 된다.

Syntax

Purpose

LEAD is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LEAD provides access to a row at a given physical offset beyond that position.

If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the table. If you do not specify default, then its default value is null.

You cannot nest analytic functions by using LEAD or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.

Examples

The following example provides, for each employee in the employees table, the hire date of the employee hired just after:

SELECT last_name, hire_date, 


   LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" 


   FROM employees WHERE department_id = 30


   ORDER BY last_name, hire_date, "NextHired";




LAST_NAME                 HIRE_DATE NextHired


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


Baida                     24-DEC-97 15-NOV-98


Colmenares                10-AUG-99


Himuro                    15-NOV-98 10-AUG-99


Khoo                      18-MAY-95 24-JUL-97


Raphaely                  07-DEC-94 18-MAY-95


Tobias                    24-JUL-97 24-DEC-97

SQL 2005에서는 페이징 방법을 새롭게 적용 할 수 있습니다.

또한 쿼리 구문을 복잡하게 사용 하시던 분들은 SQL 2005에서 개발 한다면

아래와 같이 쉽게 사용 하시기 바랍니다.

DECLARE @CURRPAGE  INT

DECLARE @MAXPAGE   INT

SET @CURRPAGE = [현 페이지 주소]

SET @MAXPAGE  = [페이지당 출력 수]

-- 1. WITH 구문을이용해서하는방법

WITH TEMP_TABLE AS

(

        -- ====================================================================

        SELECT

                       ROW_NUMBER() OVER(ORDER BY [소트할 내용]) AS RowNum,

                       *

               FROM

                       [테이블 명]

        -- ====================================================================

)

SELECT * FROM TEMP_TABLE WHERE ROWNUM BETWEEN ( ( @CURRPAGE - 1 ) * ( @MAXPAGE + 1 ) ) AND ( @CURRPAGE * @MAXPAGE )

-- 2. FROM를이용해서하는방법

SELECT

        *

        FROM

               (

                       -- ====================================================================

                       SELECT

                                      ROW_NUMBER() OVER(ORDER BY [소트할 내용]) as RowNum,

                                      *

                              FROM

                                      [테이블 명]

                       -- ====================================================================

               ) AS A

WHERE ROWNUM BETWEEN ( ( @CURRPAGE - 1 ) * @MAXPAGE ) + 1  AND ( @CURRPAGE * @MAXPAGE )

출처 : http://msmvp.egloos.com/3453597