SQL SERVER – Row Offset in SQL Server For Different Version

A very common question is how to find row offset in SQL Server. Here are a few examples based on different version of SQL Server. I have included SQL Server 2000 event, though it is almost 15 years old product and I encounter is less and less every day on production server.

I have used database adventureworks for example.

USE AdventureWorks2014
-- SQL Server 2012/2014
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
(@PageNumber-1)*@RowsPerPage ROWS
-- SQL Server 2008 / R2
-- SQL Server 2005
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
FROM Sales.SalesOrderDetail ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
-- SQL Server 2000
@PageNumber INT
@RowsPerPage = 10
SET @PageNumber = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
SELECT TOP (@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
SELECT TOP ((@PageNumber)*@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
ORDER BY SalesOrderDetailID DESC
ORDER BY SalesOrderDetailID ASC

The result of the above queries is identical to each other.

This blog post is based on an earlier blog post which had a few errors corrected in this blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – ​Tuning Queries is Sometimes Waste of Time – Notes from the Field #049

[Note from Pinal]: This is a 49th episode of Notes from the Field series. Every day I get few emails where I am asked how to tune queries so the entire server run faster. I always end up writing long answer this question. The reason is simple – query tuning is indeed the need of hours, but it is not everything. There are many more things one should do along with tuning queries. I asked the same question to Brian Moran, who is every day dealing with consultants and various organizations who are facing issues with SQL Server.

In this episode of the Notes from the Field series database expert Brian Moran explains a how Query Tuning is sometimes a waste of time when we are facing issues with performance and scalability. Read the experience of Brian in his own words.

Tuning queries is sometimes a waste of time when you’re trying to fix performance and scalability problems. That sounds crazy but it’s true. Designing an efficient workload is almost always more important than maintaining a laser focus on query tuning and other technical tricks of the DBA trade.

Here’s an example that helps to explain what I mean.

Simple Example

What’s the shortest distance between two points? A straight line, of course. Everyone knows that. Imagine that you need to calculate the most efficient path from Washington DC to New York. Imagine those cities on a traditional 3D globe. The straight line is simple to figure out, right? Just head north as the crow flies.

But what if you wander about and take a curvy path as you head north? DC to New York is about 204 miles as the crow flies, but Google says the distance is 226 miles if you take I95 and the New Jersey Turnpike. Google also presents some reasonable travel options that are as far 264 miles. It’s easy to imagine you could increase the distance even more by taking scenic roads and side trips along the way. Of course, you can also draw a straight line on a globe between DC and New York that heads in a southerly direction, which is over 24,000 miles. That’s over 100 times more expensive that the straight route northwards.

Now, let me map that mapping example back to database performance tuning. Sometimes database performance tuning is an exercise in making the workload more efficient, just as you might want to make your route to New York more efficient. To make the database workload more efficient, you can tune queries, add indexes, and do all sorts of other neat tricks. Query tuning is like an exercise in flattening the northerly path from DC to New York to make as straight a line as possible. You want to tune your queries so that they’re as direct as possible and don’t veer off into unnecessary detours.

But, what if you’re talking about trying to flatten a squiggly line from DC to New York that starts out by heading south 180 degrees, the wrong way?  You can make that line as straight and as efficient as you want.  But heading south from DC to get to New York is never going to be efficient no matter how straight of a line you draw. A route that goes the wrong way can be optimized, but it’s still going the wrong way.

Inefficient Workload

The same idea applies to queries. If you start out tuning an inefficient workload, you’ll end up with an inefficient workload that is tuned, but it is still inefficient.

This seems like a silly example. Everyone intuitively knows that the southerly route is inherently a poor choice. You can tune the route as much as you want, but in best case scenarios it’s still going to be over 100X worse than going north.

Oddly enough, 25 years of database consulting tells me many DBAs will spend most of their time tuning queries that are the database equivalent of trying to make that southerly line from DC to New York as straight as possible. They’ll spend days, weeks, or months, trying to shave that last 10%.

All too often, DBAs fail to take a moment to pause, understand the nature of the problem they are trying to solve, and try to envision a more efficient way to solve the problem.  These DBAs don’t recognize that changing the workload can be vastly better than tuning the workload.

Two Classic Examples

Here are a two classic examples that most DBAs intuitively understand. Say you have a business need defined in a way that requires drop-down list box that will be populated with 100,000 rows that users will have to scroll through. You can try to tune the process to make it more efficient at loading 100,000 rows every time the screen is touched. But that tuning does not change the workload.

Rather than simply tuning the existing workload, a better investment in time would be if you helped the designers of that screen understand and accept a solution that doesn’t require loading 100,000 rows. Or perhaps you have a system that makes heavy use of database cursors in a procedural manner of some kind. Most DBA’s know that architecting with a set-based solution will almost always be better than trying to make the cursor-based approach as fast as possible.

Here’s a 15-year-old example that’s still a common design pattern mistake made today. I was working on an office supply site when e-commerce was first becoming big business. The application was built in a very object-oriented manner. Developers love object-oriented approaches, but databases aren’t always as fond of the approach. In this case this object-oriented design pattern led to 1000 and sometimes 2000 or more round trips to SQL Server when a single user searched for office chairs, and the search brought back just one screen of results.

This was a problem because the system needed to support hundreds or thousands of concurrent users. The existing workload might therefore need to handle hundreds of thousands or millions of batch requests per second. The client wanted to do this on a single SQL Server instance since that’s all their budget could afford.


I could have spent an infinite amount of time tuning the queries used in this approach, but the workload was never going to be efficient. I was never going to be able to handle hundreds of thousands of batch requests per second on a single instance of SQL Server 2000. Instead, I made some changes to the architecture. In other words, I changed the workload, and I was able to achieve the performance goals I had.

The examples I used here are pretty basic, and most DBA’s today are aware of how to deal with such situations. But I’m willing to bet that many people reading this post are struggling with a performance problem where tuning the queries is a poor use of time as compared to completely changing the essence of the workload. It’s too easy to lose sight of the forest for the trees.

Having a hard time figuring out how to tune your queries to achieve performance goals? Maybe you should take a step back and focus on changes you can make to the workload instead?

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reset the Identity SEED After ROLLBACK or ERROR

I have been blogging for almost 8 years now. I believe I have seen pretty much every single kind of email and comments I can receive from users. However, there are sometimes few which just are so interesting that I feel like blogging about it. This blog post is written based on the earlier blog post which I have written over here How to Catch Errors While Inserting Values in Table. I suggest you read that blog post before continuing this post. Now in the original blog post I have mentioned how one can catch the error with the help of TRY…CATCH. In reply to this blog post, I received an email which I have reproduced after removing few of the unimportant details.

“Hi Pinal,

We read your blog post How to Catch Errors While Inserting Values in Table and we have found one error in your blog post. The error is that when we execute your query displayed, it automatically increases the identity value of your table. In another word here is what is happening.

Current Identity: 1
 Execute your code which inserts two rows: It errors out.
 Check Identity again: 3

We believe that when errors are caught, it should be not increase the identity of the table.

We immediately removed the TRY…CATCH error and our result was the same. That means TRY…CATCH is just to display the error gracefully. After carefully reading your blog post we realized that you had mentioned the same there. However, we still believe the identity should not be incremented as there was no real insert.

After carefully thinking we decided to use ROLLBACK. However, the ROLLBACK even does not have any impact on the IDENTITY. I think this is indeed not a good thing.

We finally researched a lot on web and found ROLLBACK does not impact identity. Finally, we decided to take up this challenge and resolve the problem. We came up with following code.

This works beautifully and resets the identity upon error or ROLLBACK. Please review the code and give us your opinion about the same.

Please post this on your blog as I believe this will be useful to many who are facing similar issues.


DBCore Group of (CompanyName Removed)”

Here is the code which was included along with the email.

-- Select Identity
-- Reset Identity Code
SELECT @IdentityValue = IDENT_CURRENT('SampleTable')
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
SELECT 'FifthRow---------'
,ERROR_MESSAGE() AS ErrorMessage;
DBCC CHECKIDENT ('SampleTable', RESEED, @IdentityValue);
-- Select Identity
-- Clean up
DROP TABLE SampleTable

Very interesting email indeed. First of all, I really appreciated the email composed by the DBCore Team. They did some real research on the subject and attempted a solution. Here is my comment about the above code.

Comment1: First of all, above code may have issue with concurrency. That means, after you have retrieved identity value from the table, it is quite possible that in any other process with similar or different code the identity would have been updated and when you reset the identity at that time, you may reset it incorrect value and eventually forfeiting the original purpose of the identity. If you have Primary Key or any other kind of Unique Key, you may start facing error as well and your data integrity would have been compromised.

Here you may come up with the solution that when you enter this transaction you put the lock on the table, but that will additionally complicate the things and your performance will degrade big time. This code will work in the case, when you have single transaction at any point of time accessing the code and there is no issue with concurrency and performance.

Comment2: There should be never any dependance on the identity column of the table. If you need a serial number generated for display purpose, just create a column with int or bigint dataype and increment it at every insert. If you are using an identity column value for display, you should be very much aware of the situation that you will have gaps in that value and you should be able to write business logic around it. For example, you should not use identity columns as order number and if there is a gap in the order number, your business should just accept it. If you are using identity column value as invoice number, I think it is a bad idea as a general rule of accounting says that invoice number should be in sequence.

Well, I guess that is what I wanted to add the code which is displayed above. I would personally follow my both the comments above and will not use identity columns for any display purpose or will not depend on sequence of it. However, I truly appreciate the efforts of DBCore group.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

MySQL – Scaling Existing Applications: Key Observations and Measurements

In the earlier blog post we discussed about How to Create a Distributed Relational SQL Database, today we will continue to learn about Scaling Existing Applications: Key Observations and Measurements.

In the use case of an existing application that is on the verge of reaching the limits of its monolithic MySQL database, scaling up – getting bigger hardware – can only go so far.  In today’s public, private and hybrid cloud world that leverages distributed infrastructure, scaling up is a counterintuitive approach. And it’s expensive.

A monolithic MySQL database that is suffering from scalability issues (for example; inconsistent performance, inconsistent availability, or transaction throughput bottlenecks) can become a distributed MySQL database that retains its relational principles by applying a declarative, policy-based data distribution process.

A well thought out data distribution policy aligns with the application’s current database structure and commands. Related data within various tables has to be identified and amassed to stay localized in a single database instance.

Simply put (and to paraphrase a familiar saying), “the data that plays together, should stay together.”  Our goal is to enable “reads” and “writes” to be completed successfully using only data from within one database instance (or shard) from the total distributed database. The more that data is processed within a single database instance, without needing other data found by traveling across machines within the distributed database array, the better that application performance and database scalability are enhanced. This way, compute processing is brought to the data, as opposed to the other way around.

Arriving to Data Distribution (Analysis)

Reads: Commands play a large role in queries, or reads. Examining what bits of data are accessed in joins, sub-queries or unions will indicate which pieces of data ought to be kept together on one machine. For example, when identifying the ‘users’ in a database, the next step would involve identifying the ‘orders’ related to those ‘users’, then the ‘items’ related to the ‘orders’, and so on and so forth. So, first, the tables, and data that are related, are identified. This usually comes from related tables, which have the same foreign keys. However, even if there are no foreign keys, it is possible to identify which columns within and between tables are generally joined for query execution.

Writes: On the transactions, or writes, side, additions to the database need to be placed in the appropriate partitioned database instance (or shard) with their related data. For instance, an ‘order’ is made up of many ‘items’, which are consequently added to the same shard as the ‘order’. A transaction is more efficient when it is contained to a single shard, so that the order and all of its items are placed on the same shard. This practice eliminates the need for a distributed transaction with a 2-phase-commit. In our example, due to the fact that there is not a direct link between ‘users’ and ‘items’, the distribution process is that much more significant because it requires cascading key lookup.

Efficiency dictates that we want data is either read together, such as in queries (i.e. sub-query joins), or written together, as in transactions.

Denormalization – Not the Best Solution

While denormalization may seem like a decent solution to data placement issues, ScaleBase’s cascading key lookup solution easily removes the need for denormalization whilst efficiently resolving any data placement issues. However, returning to our example, due to the fact that the ‘items’ table does not contain a ‘user_id’ category, the routing process can become very difficult. In this situation, if cascading key lookup is not utilized, denormalization is in order. After verifying which tables contain the distribution key, the key would have to be added to the tables in which it is missing. However, while this aids in solving the complex process of data distribution, it creates many additional problems along the way. Learn more about data distribution policies and the ScaleBase solution, here.

Null Columns

A shard key is the field according to which data is directed (it can be the ‘user_id’ or some other cascaded link, such as ‘order_id’, that eventually leads to ‘user_id’). The fields that are used to determine where to route the data and commands cannot be empty (i.e. null) or updated during the life of the row.

Looking at the data life-cycle, every piece of data must be born with a distribution key that it keeps for the course of its entire life. Looking back at our example, an ‘order’ is always born with a ‘user_id’ that never changes. All ‘items’ are linked to a specific ‘parent order’, so they never migrate between ‘orders’, the ‘parent order’ never changes for existing ‘items’, leaving no possibility for them to be ‘orphans’.

It is not enough to simply have the shard key in all tables; it needs to be populated, as part of the data in the table, as well. A row can be inserted into a table, updated many times and deleted. It is vital to insert every table into the database with an updated shard key. Returning to our example, it is imperative that the shard key is included in every insert into the database (i.e. not null). Nonetheless, if a row is inserted into the database with a ‘null’ shard key, it cannot be placed into the distributed database.

New Applications: Design for Scale from the Start

If you’re building a new app that is web-facing, mobile or social, today you have to design anticipating millions of users, high-transaction rates and ever larger data volumes.

The same data distribution principles applied to existing applications should also be applied to new applications. Data is stored and accessed together on the same database, whether it is for “reads” or “writes”.

When designing a data distribution policy, the distribution key should be selected according to how the data will be distributed. You can then denormalize, adding the distribution key to every table. Or, better yet, you can distribute by understanding the link between the tables within each shard from the beginning of the design process.

For example, when looking at an ‘order’ table, it should be clear how it is linked to the ‘user’ table, as well as the ‘order item’ table. It is in this initial phase that either cascading key lookup or denormalization should be carried out, identifying which data may still be missing from the equation. Therefore, when designing the database, ask yourself about the life-cycle of the rows of your data. Were they born with a populated distribution key? Designing your application in a way that makes sure this is taken care of avoids the unpleasant situations of null distribution keys.

When developing an application from scratch, analysis tools are not appropriate, as new applications do not have anything to track.   ScaleBase created a special guide “Building a New Application with Massive Database Scalability – Getting Started with ScaleBase” The goal of this document is to demonstrate how to build a new application that plans for massive database scalability right from the start.  It outlines the high-level steps involved and provides a walkthrough of how to create a simple, straightforward database data distribution policy. As an aid, it uses a sample application, called ‘Blog System’, mimicking today’s typical modern web scale applications. It frames the steps involved in building the application, including activities such as uploading files, quick and frequent database writes, as well as performing speedy database queries.

If you want to add linear scalability to an existing MySQL application you can leverage ScaleBase’s free online service Analysis Genie. It will help define the best data distribution policy tailored to your unique application is based on a guided analysis of the nature of your data, data relationships and the functional use of your data.

If you are building a new app that is web-facing, or mobile, or social, you have to design anticipating millions of users, high-transaction rates and ever larger data volumes. This guide will help you get started and on your way to a more efficiently distributed database.

Have you been exploring distributed databases?

What are some challenges with distributed relational databases that you would like me to focus on in future blog posts?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

MySQL – How to Create a Distributed Relational SQL Database

Distributed relational databases are a perfect match for Cloud computing models and distributed Cloud infrastructure.  As such, they are the way forward for delivering new web scale applications.

But how is the data distributed in a distributed relational database?  What is the best way to distribute data for my applications?  How to I retune my distributed database for optimal performance as applications evolve and usage patterns change?  You do all of this with your data distribution policy.

In this blog I’d like to explore different aspects of a data distribution policy. I want you to come away with a practical understanding you can use as you explore your distributed relational database options.

So, let’s dive in.

Data Distribution Policy: What It Is and Why You Should You Care

A data distribution policy describes the rules under which data is distributed.  A policy that matches your application’s workflow and usage patterns will give you critical web scale benefits:

  • endless scalability
  • high-availability
  • geo-location of data nearest user populations
  • multi-tenancy
  • archiving
  • datatiering

A poorly conceived data distribution policy will degrade performance, use more system resources and cause you problems.

In The Beginning, there was Sharding, and it wasn’t so Good

In the past, to distribute data across an “array” of linked databases, developers needed to program data distribution logic into their actual applications. The effect was to “shard” a database into slices of data. Quite literally every read or write would need to run through new custom-built application code to know where bits of data should be placed, or could be found.  This is what Facebook, Twitter and many others did as, at the time, there was no better alternative.

This extra sharding code required application developers to take on tasks typically handled by a database.  A do-it-yourself approach may seem like a fun challenge (“hey, after all, how hard can this really be??”).  But with your database divided this way, you face the following issues to contend with:

  1. Operational issues become much more difficult, for example: backing up, adding indexes, changing schema.
  2. You also need to start checking your queries results to test that each query path is actually yielding accurate results.

A lot has been written about the challenges of sharding a relational database (here’s a good whitepaper you can read: Top 10 DIY MySQL Sharding Challenges), so I won’t go into them here.  But, let’s also recognize that some great work has been accomplished by dedicated developers using sharding techniques. They have proven the inherent value of a distributed database to achieve massive scale.  At the time, they had to shard as they had no alternative.

Today, there is a better way.

What is a Good Data Distribution Policy?

As I briefly mentioned, a data distribution policy describes the rules under which data is distributed across a set of smaller databases that, taken together and acting as one, comprise the entire distributed database.

The goal we are aiming for is an even and predictable distribution of workloads across the array of clusters in our distributed database.  This brings us immense scalability and availability benefits to handle more concurrent users, higher transaction throughput and bigger volumes of data. But these benefits are all lost with a poorly conceived data distribution policy that does not align to your application’s unique usage and workloads. Let’s take a look.

Imagine we have a single database that is starting to exhibit signs of reaching its capacity limits.  Throughput is becoming unpredictable.  Users are getting frustrated waiting.

We decide the best way to improve the situation is to evolve to a distributed database. Our distributed database would aim to evenly divide the total workload across an array of databases.  In this way, data distribution decreases the number of queries that any individual database cluster (or shard) receives.

Figure 1. A good data distribution policy: ensures that a specific transaction or query is complete within a specific database.

The critical point here is that we want to distribute the data in such a way that we minimize the cross-database chatter (from cluster to cluster, or shard to shard), so that each transaction can be completed within a single cluster and in a single fetch/trip.

If we distribute data without respecting how the data is actually used, we can make matters worse.

Figure 2. A bad data distribution policy: requires transactions or queries to access or collect data from multiple databases.

In the two images above, you can see that one case depicts 1,000,000 transactions equally spread across available resources.  And the other case shows a bad distribution policy where each query needs to collect information from every cluster (or shard) – thus in every practical sense we are actually increasing the overall workload.

Data Distribution Policy
Bad Data Distribution Policy Good Data Distribution Policy
The load isn’t distributed – it’s multiplied! Distributes the workload evenly across available resources
Doesn’t scale Distributes the sessions
Adding an additional DB does NOT reduce the overall workload Delivers linear scalability
The limitation of a single DB becomes the limitation of the entire array Adding another database, increases the overall scale potential of the distributed database
When queries need data from multiple DBs, transactions must commit multiple separate DBs (2PC) before completing. This adds a lot of overhead to each Commit. Queries complete using data from a single, smaller database. This reduces a lot of overhead to any Commits.

Table 1. A comparison of a good and bad data distribution policy

So, we can see that unless we distribute the data intelligently, we will not achieve any benefit. Actually, we can see things can become worse than before.

The natural question we are lead to ask is: “OK, So what is the best way to distribute data for my applications and my workloads?

Good question!

How Create the Best Data Distribution Policy for Your Application

Distributing data across a cluster of smaller database instances and maintaining full relational database integrity, two-phase commit and rollback, (as well as leveraging SQL!) is today’s state of the art  for distributed relational databases.

We can define two broad types of data distribution policy:

  1. Arbitrary Distribution: This is when data is distributed across database instances, but without any consideration or understanding for specific application requirements and how the data will be used by users or the application;
  2. Declarative, Policy-Based Distribution: This is when data is distributed across database instances, but in a way that specifically understands all application requirements, data relationships, transactions, and how the data is used in reads and writes by the application.
Data Distribution Policy
Arbitrary Data Distribution Policy Declarative Data Distribution Policy
Pros - Pros -
Unsophisticated  Ensures that a specific transaction finds all the data it needs in one specific database
 Predetermined (no forethought required)  Aligns with schema and DB structure
Cons - Highly efficient and scalable
 No intelligence about business, schema, use cases  Anticipates future requirements and growth assumptions
 Leads to excessive use of database nodes Cons -
Leads to excessive use of network  Requires forethought and analysis

Arbitrary data distribution is often used by NoSQL database technologies.  In fact, breaking the monolithic single-instance database into a distributed database has been the core of the NoSQL revolution so that NoSQL databases can tap into the scalability benefits of distributed database architecture. However, to get scalability, NoSQL databases have been willing to abandon the relational model. NoSQL and document store type databases can rely on arbitrary data distribution because their data model does not provide for joins. Meanwhile, customers have needed something to handle their massive web scale database loads, so they’ve been willing to try new technologies, like MongoDB, with new non-relational approaches. And in some application scenarios, losing the relational data model has been an OK trade-off. Having a choice is good.

However, nowadays you can get massive web scale and keep the time-tested relational database model, if you use a declarative, policy-based data distribution approach.

Academia has written about various types of distributed relational databases for decades. But today they are a reality. Declarative, policy-based data distribution is the way forward.

The good news is that today tools can identify the best declarative, policy-based data distribution approach for you!

If you use MySQL, you can take what you know now and check out ScaleBase’s free online Analysis Genie service for MySQL. It guides you through very simple steps to create the best data distribution policy matched to your unique application requirements and data.

If you’re just naturally curious about how to evolve your relational database into a modern distributed relational database, let’s dive into the details by looking at two very typical database and development scenarios:

  1. Scaling an existing application
  2. Designing scalability in a brand new application

In tomorrow’s blog post we will discuss about Scaling Existing Applications: Key Observations and Measurements.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to Catch Errors While Inserting Values in Table

Question: “I often get errors when I insert values into a table, I want to gracefully catch them, how do I do that.”

Answer: Very simple. Just use TRY… CATCH. Here is the simple example of TRY…CATCH I have blogged earlier when it was introduced.

Here is the example, I have build from the earlier blog post where user can catch the error details during inserting value in table.

First, we will create a sample table.


Now we will attempt to insert value in this table which will throw errors and the same error we will catch into the table.

INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
SELECT 'FifthRow---------'
,ERROR_MESSAGE() AS ErrorMessage;

The second row of the above table will throw an error as the length of the row is larger than the column in which we are inserting values. It will throw an error and the same error will be caught via TRY…CATCH and it will be displayed in the SELECT statement. Here is the result set.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to Find Running Total in SQL Server

Finding running total is one of the most popular request user encounters in the industry. There are two different ways to find out running totals. One of the methods is as per SQL Server 2008 R2 and earlier version. This is indeed a very expensive version of finding running total and I always hated this solution when I had to implement it in the industry. However, I am extremely delighted since SQL Server 2012 as it has a new feature of OVER ORDER BY ROW methods. It is much more efficient and cleaner to implement.

Let us first create a sample table and populate the same.

USE tempdb
INSERT INTO TestTable (ID, Value)
SELECT 1, 10
SELECT 2, 20
SELECT 3, 30
SELECT 4, 40
SELECT 5, 50
SELECT 6, 60
SELECT 7, 70
-- selecting table
FROM TestTable

Here is the screenshot of the resultset.

Here is the query which you can execute on SQL Server 2008 R2 or earlier version. The query is very expensive.

-- Running Total for SQL Server 2008 R2 and Earlier Version
FROM TestTable T2
WHERE T2.ID <= T1.ID) AS RunningTotal
FROM TestTable T1

Here is the query which you can execute on SQL Server 2012 or later version. The query is very efficient.

-- Running Total for SQL Server 2012 and Later Version
FROM TestTable

Both of the above query returns following results.

If there is any other better option, please share it here.

Reference: Pinal Dave (http://blog.sqlauthority.com)