mssql 2012 feature is Sequence objects

create sequence MySeq as int
start with 1  -- Start with value 1
increment by 1-- Increment with value 1
minvalue 0 -- Minimum value to start is zero
maxvalue 100 -- Maximum it can go to 100
no cycle -- Do not go above 100
cache 50 -- Increment 50 values in memory rather than incrementing from IO

SELECT NEXT VALUE FOR dbo.MySeq AS seq_no;

SELECT current_value FROM sys.sequences WHERE name = 'MySeq';

SQL SERVER – NULLIF() vs ISNULL()

SQL SERVER – NULLIF() vs ISNULL()

NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression 

Syntax  : NULLIF(expression1,expression2)

Example 1 :

Select NULLIF(100,50*2)  'NULLIF Value'

OutPut :

Example 2 :

Select NULLIF(2*2,2*7)  'NULLIF Value'

OutPut :

Example 3 :

Select NULLIF(20-2,19)  'NULLIF Value'

OutPut :

ISNULL() Replaces the NULL value with the specified expression value.

Syntax : ISNULL(check expression,replacement value)

Example 1 :

Select ISNULL(null,12) 'ISNULL VALUE'

OutPut :

Example 2:

Select ISNULL(marks,0)  'Marks'  from Student

OutPut :

pagenite by rownumber, different of subquery vs with clause in mssql

This is one way (SQL2000)

SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROM tableName 
        ORDER BY columnName ASC
    ) AS t1 
    ORDER BY columnName DESC
) AS t2 
ORDER BY columnName ASC

and this is another way (SQL 2005)

;WITH results AS (
    SELECT 
        rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
        , *
    FROM tableName 
) 
SELECT * 
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

and this is another way SQL2012

SELECT * FROM Table_1
ORDER BY id
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY

example

SELECT * from (
SELECT *, ROW_NUMBER() OVER(ORDER BY id asc) as rownum FROM [test].[dbo].[Table_1]
) as a
where a.rownum > 1 and a.rownum < 4
GO
with b as (
SELECT *, ROW_NUMBER() OVER(ORDER BY (select 1)) as rownum FROM [test].[dbo].[Table_1]
)
select top 10 * from b where b.rownum > 1
GO

horinzontal scrolling content using jScrollPane and content mousemove event

var scrollbarWidth = 16;
    var scrollHeight = $('.site-container').get(0).scrollHeight;
    var sw = $(window).width();
    var sh = $(window).height();
    var padleftStr = $('.site-container').css('padding-left');
    var padleftInt = padleftStr.substring(0, padleftStr.length - 2);

    var tnavWidth = sw - (scrollHeight > 0 ? scrollbarWidth : 0) - padleftInt;
    var lnavHeight = sh - $('.logo-wrap').height();

    $(window).on('resize', function () {
        checkScrolling($('.cd-tabs nav'));
        tabContentWrapper.css('height', 'auto');

        sw = $(window).width();
        sh = $(window).height();

        padleftStr = $('.site-container').css('padding-left');
        padleftInt = padleftStr.substring(0, padleftStr.length - 2);

        tnavWidth = sw - scrollbarWidth - padleftInt;
        lnavHeight = sh - $('.logo-wrap').height();
        $('.menu-3 span').html('sw: ' + sw + ' , padleft: ' + padleftInt);

        var toppadleftStr = $('.top-container').css('padding-left');
        var toppadleftInt = toppadleftStr.substring(0, toppadleftStr.length - 2);
        $('.top-container').width(tnavWidth - toppadleftInt - 1);
        $('.top-container #top-navbar-container').width(tnavWidth - toppadleftInt - 1);

        $('.scroll-bar-left').height(lnavHeight - 1);

        $('.scroll-bar-left, #top-navbar-container, .zar-wrapper').each(function () {
            var api = $(this).data('jsp');
            if (typeof api === 'object') {
                api.reinitialise();
            } else {
                $(this).jScrollPane();
            }
        });

        var jspPane = $('#top-navbar-container').find('.jspPane');
        var jspContainer = $('#top-navbar-container').find('.jspContainer');
        if (jspContainer.find('.jspHorizontalBar').size() === 0) {
            jspPane.css('left', '0px');
        }

    }).trigger('resize');

    var toppadleftStr = $('.top-container').css('padding-left');
    var toppadleftInt = toppadleftStr.substring(0, toppadleftStr.length - 2);
    $('.top-container').width(tnavWidth - toppadleftInt - 1);
    $('.top-container #top-navbar-container').width(tnavWidth - toppadleftInt - 1);
    $('.scroll-bar-left').height(lnavHeight - 1);

    $('#top-navbar-container').mousedown(function (event) {
        event.preventDefault();
       
        improvejScrollPane.downx = event.pageX;
        improvejScrollPane.moving = false;
    }).mousemove(function (event) {
        event.preventDefault();

        var jspPane = $(this).find('.jspPane');
        var navfullw = jspPane.find('#navbar').width();
        var jspw = $(this).find('.jspContainer').width();
        var panel = jspPane.position().left;
        var trackw = $(this).find('.jspTrack').width();
        var jspDrag = $(this).find('.jspDrag');
        var dragw = jspDrag.width();
        var dragl = jspDrag.position().left;
        var currx = event.pageX;
        var diffx = improvejScrollPane.downx - currx;
        var clicked = parseInt(event.which + '' + event.button);

        if (improvejScrollPane.downx > 0 && Math.abs(diffx) > 0 && clicked === 10) {
            improvejScrollPane.moving = true;
            improvejScrollPane.downx = currx;

            var ddiffx = diffx * -1;
            var diffw = jspw - navfullw;
            var ddiffw = dragw - trackw;
            var perw = Math.abs(ddiffw / diffw) * diffx;
            var cleft = panel + ddiffx;
            var dleft = dragl + perw;
            if (ddiffx > 0) {
                if (cleft > 0)
                    cleft = 0;
                if (dleft < 0)
                    dleft = 0;
            } else {
                if (cleft < diffw)
                    cleft = diffw;
                if (trackw < dleft + dragw)
                    dleft = trackw - dragw;
            }
            //$('.test').html(diffw + '=>' + ddiffw);
            jspPane.css('left', cleft + 'px');
            jspDrag.css('left', dleft + 'px');
        }
    }).mouseup(function (event) {
        event.preventDefault();
        improvejScrollPane.downx = 0;
    }).click(function (event) {
        if (improvejScrollPane.moving)
            event.preventDefault();
    });