One thing applications tend to do when retrieving a lot of data from SQL Server is to page the results.

Since SQL Server 2005, I tended to do this using the ROW_NUMBER() function. Like this

SELECT *

FROM   (SELECT p.firstname,

               p.lastname,

               Row_number() OVER (ORDER BY p.firstname DESC) AS rownum

        FROM   person.person p) AS person

WHERE  person.rownum BETWEEN 11 AND 20

or like this

WITH person(firstname, lastname, rownum)

     AS (SELECT p.firstname,

                p.lastname,

                Row_number() OVER (ORDER BY p.firstname DESC) AS rownum

         FROM   person.person p)

SELECT *

FROM   person p

WHERE  p.rownum BETWEEN 11 AND 20

I would of course parameterize 11 and 20 and call that in a stored procedure or parameterized SQL statement. Linq to Sql or the Entity Framework currently generates a similar SQL statement.

OFFSET ... FETCH NEXT 

Now in SQL Server 2012 RC0 we can achieve the same thing using the offset  ... fetch next clause.

The Order By statement now has an additional clause we can use as follows:

SELECT p.firstname,

       p.lastname

FROM   [Person].[Person] p

ORDER  BY p.firstname DESC


OFFSET 10 rows

FETCH NEXT 10 rows only

The offset clause specifies the number to rows to skip before returning the rows from the query expression.

The fetch specifies the number of rows to return after the OFFSET clause has been processed.

In terms of performance, I have not done any extensive performance tests but I do not believe there is a significant performance difference between the two.

The query execution plan for the two statements are similar.

James

At long last SQL Server gets Sequences. The community had been asking for this ANSI feature for some time now. ORACLE has had this since its inception.

A sequence is simply a user-defined object that is used to generate a number sequence according to a set of rules that are specified when the sequence in created.

The rules that govern sequence creation are as follows:

• Create sequences in ascending or descending order
• Control the starting point and intervals
• Specify the maximum and minimum values
• Be configured to restart (or cycle) when exhausted
• Cache sequence values to improve performance.

Applications can obtain the next sequence number by calling the NEXT VALUE FOR function.

The syntax for creating sequences is as follows:

 CREATE SEQUENCE [schema_name . ] sequence_name  
   [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
   [ START WITH <constant> ]  
   [ INCREMENT BY <constant> ]  
   [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
   [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
   [ CYCLE | { NO CYCLE } ]  
   [ { CACHE [ <constant> ] } | { NO CACHE } ]  
   [ ; ]  

Consider the following sequences:

 Create Schema SeqTest;  
 CREATE SEQUENCE SeqTest.Counter1  
   START WITH 1  
   INCREMENT BY 1  
 ;  
 Create SEQUENCE SeqTest.Counter100  
      Start with 100  
      INCREMENT BY 50  
      MINVALUE 100  
      MAXVALUE 300  
      CYCLE  
      CACHE 3  
 ;  

SeqTest.Counter1 creates a sequence that starts with 1 and increments by 1 whenever the NEXT VALUE FOR function is called.
The second sequence starts with 100 and increments by 50. Once 300 has been reached, the sequence restarts from 100.

Let us try this out:

 CREATE TABLE SeqTest.Person  
   (  
           ID int PRIMARY KEY,  
           Name varchar(20) NOT NULL,  
           Ranking int  
   );  
 GO  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'John', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jane', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Bill', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Ann', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jack', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jill', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Frank', Next Value for SeqTest.Counter100) ;

select * from SeqTest.Person 

ID	Name	Ranking
1	John	100
2	Jane	150
3	Bill	200
4	Ann	250
5	Jack	300
6	Jill	100
7	Frank	150

Data Types
Sequences can be used with the following data types :
tinyint, smallint, int, bigint, decimal and numeric( scale of 0). If the data type is not specified, it defaults to bigint.

Resetting Sequences

Sequence can be reset as follows:

 ALTER SEQUENCE SeqTest.Counter1  
 RESTART WITH 1 ;  

Cache and No Cache Option
The cache option is used to increase performance by caching the current value and number of values left in the cache. This minimizes the number of disk IOs required to generate sequences.

Transaction consistency
Sequences are not transactionally consistent and run outside the scope of the current transaction. So it is possible for gaps to appear in the sequence. If a transaction is rolled back, the next value is still taken.

 CREATE SEQUENCE SeqTest.Counter2 AS smallint    
      START WITH 1    
      MAXVALUE 100   
      ;  
 BEGIN TRANSACTION;    
 SELECT NextBar = NEXT VALUE FOR SeqTest.Counter2; --displays 1 
 ROLLBACK TRANSACTION;  
 SELECT NextBar = NEXT VALUE FOR SeqTest.Counter2;  --displays 2

Stay tuned. In my next post, I will look at when to choose Sequences over Identity columns.
James

Most of the Eurozone leaders used their New Year’s message to highlight the risks to Europe’s economy this year.  So 2012 is going to be a tough year. Really? why?

I do not understand economics. I missed my chance when I chose to study Civil Engineering rather than Economics.

These days I wish I had. It is hard for me to understand financial news much less its impact on me (or my business).

For instance, today the FTSE 100 is up by 0.1%. What the heck does that mean? Is it some kind of a score? A way to track winners and losers in some financial game?

I do not know. Neither does my wife and we are both engineers with Masters Degree.

I understand terms like callbacks, classes, objects, inheritance but the other terms like Futures, Options, Swaps, Derivatives and Hedging are completely foreign to me.

So this year, I have resolved as part of the many things I am going to learn, (e.g. Objective-C, PRINCE2), I will understand  financial stuff.

I say “stuff” because I do not even know what it entails yet. I am just going to learn about this “stuff”. Any help will be gratefully appreciated. I am going to read some books about this stuff and summarize it for folks who are in the same situation as I am.

So in this series of posts (in addition to the JavaScript posts), I will be giving developers a rundown on financial stuff. Read on at your own peril.

Derivatives

This is an evil word. It has the potential to cause another “Great Depression” whiles making a select group of individuals (in the know) a whole lot of money.

So what exactly is it?

It is an agreement between two parties that has a value which is determined by the price of something else.

Makes sense. Hell no. Let's try again.

It is a financial agreement whose value depends on the values of other more basic underlying variables.

Still foggy. Ok let us have an example.

Let us say that current price of Gold is US$ 50 per gram today.

If I enter into an agreement with some Joe Blocks that says:

If the price of a gram of gold is greater than US$ 60 next year, I will pay him US$ 10. If not, he will pay me US$10.

In simplistic sense, this is a derivative because I have an agreement with a value depending on the price of something else. (that is the price of gold).

It is a bet. Plain and Simple.

Not on the outcome of a football match but on the price of something.

You might be thinking "hey wait. Is this not some kind of gambling?".

How different is this from going on BET365.com and picking on England to win the European cup? (Yeah right).

Well it is and it isn’t.

It turns out that derivatives provides some kind in insurance.

Supposing I owned a gold mine and Joe Blocks is a goldsmith who specializes in making custom Indian gold jewellery. If the price of the gold that he buys is expensive (say more than US$ 60) he earns an additional US$10; offsetting the high cost of gold. On the other hand, if the price is cheaper, I earn an additional income of US$10.

The derivate has become a contract which reduces risk for both parties.

Simple.  Yes but why? It turns out there are a lot of reasons for using derivatives.

Amongst these include speculation and risk management.

Speculation:

Derivatives can be used to make bets that are custom made to fit a specific view. So for instance, if I want to bet that the FTSE 250 will be between 10,000 and 11,000 next year, a derivative can be created to let me do that.

Risk Management:

As in the previous example, it reduces the risk of loss for my fictitious gold mining business. In this case it can be said that I am hedging. (Another term we will come across later).

That is enough financial “stuff” for now. I must admit it is becoming as interesting as programming. I can begin to see how it can quickly become as complex as software engineering.

I will continue with this when I know a bit more.

Regards

James