OLTP databases work best when data that becomes no longer current is then transferred to a separate database for analysis and reporting. There are many ways to do this, but Feodor describes a rapid technique that takes advantage of partitions to automates the rotation of the data and moving it to the analysis server.
It is quite common to have an OLTP database that must store large amounts of data which pile up into hundreds of millions, even billions, of rows. What do we do in such cases? In this article I will describe a way to deal with constant flows of OLTP data into production systems, and how to offload this data, describing the process from beginning to end.
Here are some assumptions for the sake of this article:
How do we tackle the problem?
This is an illustration of the classic problem of mixing OLTP and Analytics data. We have a database which was designed to handle OLTP requirements, but it ends up gathering data which is required for analytical purposes. As a general rule, OLTP databases should be light on their feet. They ought to be small, storing only very recent data that should fit in memory so we can easily do In-Memory-OLTP. (Please note that no fancy technology is needed for In-Memory-OLTP: anyone who is smart enough can do In-Memory-OLTP, since memory nowadays is very cheap and the amount of relevant transactional data is finally getting behind the available memory capacities).
There are several ways to deal with this situation:
Neither of the first two options are much good, but the third one seems quite reasonable and we will explore it in this article.
For this article we can assume that we have a database called ProdLogDB, which has a table dbo.Log, which is designed like this:
CREATETABLE [dbo].[LOG]
(
[column1] [VARCHAR](50) NULL ,
[column2] [VARCHAR](50) NULL ,
[column3] [VARCHAR](50) NULL ,
[column4] [VARCHAR](100) NULL ,
[notes] [VARCHAR](MAX) NULL ,
[timestamp] [DATETIME] NOT NULL ,
[log_id] [INT] IDENTITY(1, 1)
NOT NULL ,
CONSTRAINT [PK_LOG] PRIMARY KEY CLUSTERED ( [log_id] ASC )
)
ON [PRIMARY]
Now we need lots of data, and good representative data too. For the sake of this article I will generate some data with the help of Redgate’s Data Generator. After the generation of data the dbo.Log table will have data for the time between 2012-01-01 and 2015-01-20. For this time frame I have chosen to have 10 million rows and the data is 1.5Gb. I will be using the same data generation project to generate 100,000 rows for the next few days, while we do the database management and data distribution in this article.
We have the data, but what should we do next? The idea is to have only a very small portion of the data in our production database, and to offload the rest of the data to a secondary server where the historical data will be stored. In our case we need only 7 days of data in the production system, so we can do analysis: The rest of the data can be someplace else. Furthermore, we want to automate the offloading process in such a way that a scheduled job will run every day and will move the oldest day’s data to the secondary location. (The code below is flexible enough to support the rotation of 1 month’s data, and we can provide a parameter to specify how many days of data to keep, up to 31 days.)
Firstly, we need to ‘empty’ the dbo.Log table and move the data to a table containing the historical data. There are many ways to do this: We can, for example, bulk-copy the data to a different table, and batch-delete most of the data from the primary table. This, of course, would work, but it will take a long time and use a lot of resources, even if we do batch deletes. I would prefer the following strategy:
We Create a new table called dbo.Log1, which has identical schema as the current production table, but it is partitioned, and ready for data writes from the application.
We run the following script to do this:
CREATEPARTITION FUNCTION PF_RingRecyclerByMonth(TINYINT)
ASRANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
CREATEPARTITION SCHEME PS_RingRecyclerByMonth
ASPARTITION PF_RingRecyclerByMonth ALL TO ([PRIMARY])
CREATETABLE [dbo].[LOG1]
(
[column1] [VARCHAR](50) NULL ,
[column2] [VARCHAR](50) NULL ,
[column3] [VARCHAR](50) NULL ,
[column4] [VARCHAR](100) NULL ,
[notes] [VARCHAR](MAX) NULL ,
[timestamp] [DATETIME] NOT NULL ,
[log_id] [INT] IDENTITY(11000000, 1)
NOT NULL ,
[Offset] AS ( CONVERT([TINYINT], DATEPART(DAY, [timestamp])) ) PERSISTED
)
ON PS_RingRecyclerByMonth(Offset)
GO
An important point to note here is that we are creating a new table, with one additional persisted computed column called [Offset]. This column is used to partition the table by the day number. We will use this functionality later on to find the oldest partitions and to send them to the historical table.
Also, it is worth noting that we have the identity column [log_id], however the identity seed starts from a certain level. This is very important to get right, since we do not want to have duplicate ids later on. Make sure to set the identity seed from a level higher than the current one, even if there is a small gap.
In my case I have 10,000,000 rows, and I am estimating that in the next few days I will have 10,300,000, so I will set the seed to be 11,000,000. There will be some gap but better to be on the safe side.
So far, we have one empty partitioned table (dbo.Log1), and one constantly growing production table (dbo.Log). Now it is time to do something buzzingly exciting: we will move data from one table to the other. In the matter of a second. Here is how:
BEGINTRANSACTION;
EXECsp_rename ‘dbo.LOG’, ‘LOG_History’;
EXECsp_rename ‘dbo.LOG1′, ‘LOG’;
COMMITTRANSACTION;
So here is what just happened: our application was writing to the dbo.Log table, which was not partitioned and now, after renaming the tables, the application is writing to the newly created partitioned table. If the application is smartly written, we won’t need to schedule for downtime or maintenance window. The rename should take less than a second and the application should retry to write again any rows that fall in the time of the rename.
At this point we have two tables – dbo.Log – this one is partitioned and growing, and dbo.Log_History – this one is our large table which is not written to anymore.
Now it is time to offload the historical data to our secondary server. One way to do it is to create a new database on the secondary server and bulk-copy the data. Another way to do it is to simply backup the current production database and restore it as a historical database on the secondary server. Either way, the result will be the same.
After the successful copy of the data to the secondary server we can now delete the large table from our production server. (some magic may have to be performed to regain the allocated space after removing the large table, but this is a trivial task for a DBA)
Also, keep in mind that your DBA will need to do some wizardry if your secondary server is not an Enterprise edition, since the database won’t restore if it contains enterprise edition objects in it.
At this point we have two databases on two different servers, one is called ProdLogDB and contains the partitioned table dbo.Log, and the other is called ProdLogDB_History and contains the dbo.Log_History table.
How do we rotate the production table, though?
The idea is to take the oldest partitions and to move them to the historical database.
First we need to create an empty table with the same structure so we can do partition switching. Create the same table in both databases:
CREATE TABLE [dbo].[LOG_SwitchTarget]
(
[column1] [VARCHAR](50) NULL ,
[column2] [VARCHAR](50) NULL ,
[column3] [VARCHAR](50) NULL ,
[column4] [VARCHAR](100) NULL ,
[notes] [VARCHAR](MAX) NULL ,
[timestamp] [DATETIME] NOT NULL ,
[log_id] [INT] NOT NULL ,
[Offset] AS ( CONVERT([TINYINT], DATEPART(DAY, [timestamp])) ) PERSISTED
)
ON PS_RingRecyclerByMonth(Offset)
GO
Note that there is no identity specification on the [log_id] conlumn.
After creating the tables, we will use them to switch the partitions to them by using the following stored procedure:
IF OBJECT_ID(‘dbo.RingbufferRotate_ByMonth’) IS NULL
EXEC (‘CREATE PROCEDURE dbo.RingbufferRotate_ByMonth AS RETURN 0;’)
GO
ALTER PROCEDURE RingbufferRotate_ByMonth
@Now DATETIME = NULL ,
@PartitionsToKeep INT = 7
AS
SET NOCOUNT ON
BEGIN
IF @Now IS NULL
SET @Now = GETDATE()
DECLARE @BufferSize INT
SELECT @BufferSize = COUNT(*)
FROM sys.partitions P
JOIN sys.tables T ON P.object_id = T.object_id
WHERE T.name = ‘LOG’
IF @PartitionsToKeep > @BufferSize
BEGIN
RAISERROR (‘Can”t keep more partitions than the current buffer size of: %i’, 16, 1, @BufferSize)
RETURN
END
DECLARE @CurrentPartition INT
SET @CurrentPartition = DATEPART(DAY, @Now)
DECLARE @OldestPartitionToKeep INT
SET @OldestPartitionToKeep = ( @CurrentPartition + @BufferSize
- @PartitionsToKeep ) % @BufferSize
/* Start from the next partition up from current and move forward */
DECLARE @P INT = ( @CurrentPartition + 1 ) % @BufferSize
DECLARE @I INT = 0
WHILE @I < @BufferSize - @PartitionsToKeep
BEGIN
DECLARE @Sql NVARCHAR(4000) = ‘ALTER TABLE LOG SWITCH PARTITION
TO LOG_SwitchTarget PARTITION
‘
SET @Sql = REPLACE(@Sql, ‘
‘
EXEC sp_executesql @Sql
SET @I = @I + 1;
SET @P = ( @P + 1 ) % @BufferSize
END
END
This procedure takes two parameters:
At this point we don’t have that much data in our Log table, since we renamed the tables fairly recently. But in a few days the data will pile up and our procedure will be useful.
Here is a picture of how the data transfer looks like:
Note that there are many different ways to implement this solution, but the important part is the automated partitions switching, the bulk-copying of the data from the [dbo].[LOG_SwitchTarget]
Table on the production server to the [dbo].[LOG_SwitchTarget] table on the secondary server and merging the data to the [dbo].[LOG_History] table.
Here is the MERGE procedure which should be created on the secondary server:
IF OBJECT_ID(‘dbo.Log_History_Merge’) IS NULL
EXEC (‘CREATE PROCEDURE dbo.Log_History_Merge AS RETURN 0;’)
GO
ALTER PROCEDURE dbo.Log_History_Merge
AS
SET IDENTITY_INSERT LOG_History ON
MERGE INTO LOG_History AS T
USING
( SELECT [column1] ,
[column2] ,
[column3] ,
[column4] ,
[notes] ,
[timestamp] ,
[log_id]
FROM [dbo].[LOG_SwitchTarget]
) AS A
ON A.[log_id] = T.[log_id]
WHEN NOT MATCHED THEN
INSERT ( [column1] ,
[column2] ,
[column3] ,
[column4] ,
[notes] ,
[timestamp] ,
[log_id]
)
VALUES ( A.[column1] ,
A.[column2] ,
A.[column3] ,
A.[column4] ,
A.[notes] ,
A.[timestamp] ,
A.[log_id]
);
SET IDENTITY_INSERT LOG_History OFF
TRUNCATE TABLE [LOG_SwitchTarget];
From this point on it is very easy to implement the solution. For example, we can create an SSIS package which will run daily and will carry out the following tasks:
In order to test the code, let’s get back to our test scenario: we have 10 million rows in the Log_History table. Let’s generate some data in the LOG table, which will have timestamp between 2015-01-20 and 2015-01-28. We can then run the RingbufferRotate_ByMonth procedure and take a look at the data distribution per partition:
SELECT $ PARTITION.PF_RingRecyclerByMonth(OFFSET) AS PARTITION ,
COUNT(*) AS [COUNT] ,
CONVERT(DATE, [timestamp]) Datestamp
FROM dbo.[LOG]
GROUP BY $ PARTITION.PF_RingRecyclerByMonth(OFFSET) ,
CONVERT(DATE, [timestamp])
ORDER BY CONVERT(DATE, [timestamp]);
This query returns data from the LOG table, which looks like this:
|
PARTITION |
COUNT |
Datestamp |
|
23 |
111111 |
2015-01-23 |
|
24 |
111111 |
2015-01-24 |
|
25 |
111111 |
2015-01-25 |
|
26 |
111111 |
2015-01-26 |
|
27 |
111111 |
2015-01-27 |
|
28 |
111111 |
2015-01-28 |
And the following query will show us what we have in the LOG_SwitchTarget table:
SELECT $ PARTITION.PF_RingRecyclerByMonth(OFFSET) AS PARTITION ,
COUNT(*) AS [COUNT] ,
CONVERT(DATE, [timestamp]) Datestamp
FROM dbo.[LOG_SwitchTarget]
GROUP BY $ PARTITION.PF_RingRecyclerByMonth(OFFSET) ,
CONVERT(DATE, [timestamp])
ORDER BY CONVERT(DATE, [timestamp]);
The data looks like this:
|
PARTITION |
COUNT |
Datestamp |
|
20 |
111112 |
2015-01-20 |
|
21 |
111111 |
2015-01-21 |
|
22 |
111111 |
2015-01-22 |
Now it is time to do a bulk-copy of the data from the LOG_SwitchTarget table on the primary server to the LOG_SwitchTarget table on the secondary server.
After the bulk-copy has run, we can execute the dbo.Log_History_Merge procedure. Now we can see that we have the new data into our LOG_History table on the secondary server.
It is important to remove historical data from a fast-working OLTP database, and retain only any necessary summary and aggregate data. The requirement of transaction processing and analysis are quite different, and it is unwise to mix the two.
In this article I have described a simple but effective way to keep our log data under control by rotating the data in the production system and taking it incrementally to a secondary server. There are plenty of other ways to do it, but the method you choose must be able to comfortably handle the heaviest volume of data you are likely to experience.
This article focuses on a solution where the primary server is using Enterprise edition, but in reality there is a possibility to do similar logic even in standard edition, it will just take a bit more effort to code the data flows and some smart management of database file groups.
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
We had a case where Reporting Services was crashing with the following error.
Failed allocate pages: FAIL_PAGE_ALLOCATION 2
The number at the end can be different. In the customer’s case it was a 1. In my local repro it was a 2. This is basically an indication that we are out of memory for the process and the process crashes at that point as we can’t allocate any more memory. You won’t see an OutOfMemory error, as the allocations come from SQL Operating System (SOS) which hosts the .NET CLR. So, it is SOS that is trying to do the allocation that fails. SOS is also used with the SQL Engine, so you will see this error from that side as well when you are out of memory.
Before we get into the meat of it, here is a link to an article that goes through the memory thresholds for Reporting Services and explains how it will handle memory when those thresholds are hit.
Configure Available Memory for Report Server Applications
https://msdn.microsoft.com/en-us/library/ms159206.aspx
When Reporting Services starts, it will calculate what the maximum amount of memory will be for the process. This will be done in one of two ways.
First, if WorkingSetMaximum is not set in the rsreportserver.config (which it isn’t there by default), then Reporting Services will derive the maximum memory setting based on your total physical memory. To see this happen, we can look at the Reporting Services Trace Log and look for Derived memory.
rshost!rshost!19c4!01/29/2015-05:03:22:: i INFO: Derived memory configuration based on physical memory as 33486264 KB
servicecontroller!DefaultDomain!b14!01/29/2015-05:03:22:: i INFO: Total Physical memory: 34289934336
You may or may not see something like the following entry if you configure WorkingSetMaximum.
library!DefaultDomain!3278!12/02/2014-16:11:18:: i INFO: Initializing WorkingSetMaximum to ‘12486264’ kilobytes as specified in Configuration file.
We also have the concept of MemorySafetyMargin and MemoryThreshold. These are used to alert Reporting Services and to start to back off as we need to allocate more memory and we are already pretty full. These are also configured within the rsreportserver.config. The default values are 80% and 90% respectively of whatever our Maximum value is set to.
80 90
We can also validate these values within the Reporting Services Trace Log.
library!DefaultDomain!19c4!01/29/2015-05:03:19:: i INFO: Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
library!DefaultDomain!19c4!01/29/2015-05:03:19:: i INFO: Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.
All of this amounts to when Reporting Services will start triggering memory pressure notifications. These notifications can either be Low, Medium or High. The link above has a great image that shows you when each one will trigger.
NOTE: You will only see the NotifyMemoryPressure items in the log if you have the log set to Verbose. Specifically the applicationdomain category.
You also have the opportunity to define WorkingSetMaximum and WorkingSetMinimum in the rsreportserver.config if you know that you have other items running on this machine and you don’t want Reporting Services to starve other items. Or at the minimum, for Reporting Services and the other services to play nice with each other. This allows us to cap Reporting Services manually instead of going on the derived value.
Let’s go back to the FAIL_PAGE_ALLOCATION error that I mentioned at the beginning. If you receive this, we ran out of memory and couldn’t recover fast enough and then fail to allocate because nothing is available.
Without verbose logging, we can see the following type of behavior in the Reporting Services Log.
processing!ReportServer_0-2!199c!01/29/2015-05:28:37:: w WARN: Processing Scalability — Memory Shrink Request Received
processing!ReportServer_0-2!199c!01/29/2015-05:28:38:: w WARN: Processing Scalability — Memory Shrink Request Received
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Here we can see that there were Memory Shrink Requests. This is an indication that we are starting to hit the ceiling and Reporting Services wants to back off to have more breathing room. The allocation error caused the process to crash. It would then restart on it’s own. There is no other information that is logged. Nothing in the Event Logs or from the Reporting Services Trace log.
The first thing I tend to look at for this is what are the settings defined in the rsreportserver.config. In the customer’s case, we see the following.
80 90 1000000 400000
This alone is a giant red flag. WorkingSetMaximum looks really low. I don’t necessarily care about WorkingSetMinimum. And the defaults for MemorySafetyMargin and MemoryThreshold are fine.
This is the definition of WorkingSetMaximum from the MSDN article referenced at the top of this blog.
WorkingSetMaximum
Specifies a memory threshold after which no new memory allocations requests are granted to report server applications.
By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service starts.
This setting does not appear in the RSReportServer.config file unless you add it manually. If you want the report server to use less memory, you can modify the RSReportServer.config file and add the element and value. Valid values range from 0 to maximum integer. This value is expressed in kilobytes.
When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum.
If existing requests continue to consume additional memory after the WorkingSetMaximum value has been reached, all report server application domains will be recycled. For more information, see Application Domains for Report Server Applications.
If this value is specified in kilobytes (KB), then the WorkingSetMaximum defined above is only about 1GB of memory. That definitely won’t be enough. We won’t get very far with only 1GB of maximum memory. From the Reporting Services Trace Log, we can see what the customer’s total physical memory was.
servicecontroller!DefaultDomain!9e80!01/26/2015-13:48:48:: i INFO: Total Physical memory: 137372422144 <--137GB!!!
I’m guessing that the thought was that WorkingSetMaximum was defined in megabytes (MB) instead of kilobytes (KB). So, if we really wanted 100GB for the WorkingSetMaximum, we would need to add two more 0’s.
Verbose Logging can also help you understand the situation a little better, especially if the out of memory condition doesn’t happen right away. From a memory perspective, I only really care about the appdomainmanager category. We can set that up for verbose by setting the following within the ReportingServicesService.exe.config file.
Here is what the output will look like. I included some INFO messages to show the flow of what happened.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing WorkingSetMaximum to ‘1000000’ kilobytes as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing WorkingSetMinimum to ‘400000’ kilobytes as specified in Configuration file.servicecontroller!DefaultDomain!3034!01/29/2015-05:44:35:: i INFO: Total Physical memory: 34289934336
library!ReportServer_0-2!17a0!01/29/2015-05:45:46:: i INFO: RenderForNewSession(‘/MemoryPressure/MemoryHogContoso’)
library!ReportServer_0-2!31f8!01/29/2015-05:45:46:: i INFO: RenderForNewSession(‘/MemoryPressure/MemoryHogContoso’) <-- This is the report request received before we started seeing the shrink requests.appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:50:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=MediumPressure, kBytesToFree=33960)
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:50:: v VERBOSE: Memory Statistics: 0 items, 0KB Audited, 0KB Freeable, 924384KB Private Bytes
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:50:: v VERBOSE: Spent 3ms enumerating MAP items and 2ms dispatching notifications.appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:51:: v VERBOSE: Appdomain (ReportServer) attempted to free 23016 KB.
appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:52:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=HighPressure, kBytesToFree=121216)
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:52:: v VERBOSE: Skipping shrink request for appdomain (WindowsService_0) because no memory consuming requests are registered.
appdomainmanager!ReportServer_0-2!18e4!01/29/2015-05:45:52:: v VERBOSE: Skipping shrink request for appdomain (ReportServer_MSSQLSERVER_0-2-130670054877986461) because not enough time has passed since last shrink request.Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Assuming the issue was not due to a low value setting on the WorkingSetMaximum value, I would look to see what report executed before we started seeing the shrink requests and look at how that may be consuming a lot of memory. Depending on your server, it may be a result of several report requests working together and we would need to see what we can do to stagger them or pull back the amount of data they are consuming.
If it is due to the number of users hitting the Report Server, you may want to look at going into a scale-out configuration to spread the load. Also, if you are running subscriptions, you could look at offloading those to a separate server from on demand report requests.
Performance Monitor (perfmon) can be used to see the consumption as well. Unfortunately, it won’t really do a lot to help you pinpoint the problem. It will just help you confirm that you do have a problem.
The counters I used were the following.
Process : Private Bytes (ReportingServicesService)
Process : Virtual Bytes (ReportingServicesService)
Process : Working Set (ReportingServicesService)
Process : Working Set – Private (ReportingServicesService)
ReportServer : Service : Memory Pressure State
ReportServer : Service : Memory Shrink Amount
ReportServer : Service : Memory Shrink Notifications/sec
Unfortunately, the ReportServer : Service counters did not appear to pick anything up.
To wrap up, there isn’t a magic bullet when it comes to memory issues. We need to investigate what the Report Server is doing and what is running. It could be because of a Report, a 3rd Part Extension, custom code segments. Try to narrow it down to a specific report and go from there. Also, make sure Reporting Services is playing nice with other services on the machine, if there are any. You can use WorkingSetMaximum to do that.
Adam W. Saxton | Microsoft Business Intelligence Server Escalation Services
Twitter YouTube
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
What better way to learn how to construct complex CHECK CONSTRAINTs, use the SQL 2012 window frame capability of the OVER clause and LEAD analytic function, as well as how to pivot rows into columns using a crosstab query? Create the SQL code to score Ten-pin Bowling, of course. Dwain Camps explains the how, what and why.
There is nothing like a real task to illustrate how to use the SQL 2012 LEAD analytical function, the new window frame facility of the OVER clause and a bit about constructing CHECK CONSTRAINTs.
When I was growing up, one of my favorite sports was bowling. It was only later in life after I moved overseas that I realized that bowling actually has another connotations, both in the game of cricket and in lawn Bowls. Bowling is, in Europe called Ten-pin bowling or skittles.
In bowling, the way that the game is scored is rather complicated, with some arcane conventions. Because of this, it makes a good example to illustrate the power of the analytic functions in SQL 2012, especially when used together with constraints.
In bowling, the objective is to roll a ball down the lane and knock down all of the ten pins sitting at the end of the lane in each frame. Ideally, you do this in one roll of the ball, but you get two tries.
While in the modern age, we have computerized bowling lanes that tally and provide an overhead projection of your score in bowling, in the old days a bowling game was scored using a score sheet that looks like this.
In bowling, balls are rolled and tallied in pairs, with the exception of the tenth frame where there are cases when you will be allowed to roll three balls. Each of the numbered boxes above represent a frame. There are three types of frame depending on the number of pins you knock down:
Strikes and spares are called “marks” (probably because they are represented by X’s and slashes), and if you get a mark in frame ten you’ll be allowed to roll three balls in that frame. We’ll explain the details of scoring in a moment, but for right now let’s focus on how many balls you can roll in a game.
The maximum number of balls you can roll in a game is 21, which results if you get a spare or open frame for the first nine frames, then roll a spare in the tenth frame allowing you to roll one additional ball in that frame. You’ll note that this is the same as the number of small boxes in the score sheet (two in each of the frames one through nine, plus three in the tenth frame).
The minimum number of balls you can roll in a game of bowling is 12, if you were to manage to roll a strike in each frame (knocking down all the pins on the first ball means you don’t need to throw another ball in that frame) and roll three strikes in the tenth frame. In this case, you would achieve a perfect game. The last two balls in the tenth frame are required to score the first strike in that frame.
Scoring for each frame depends on the type of frame you rolled:
I can still remember my dad teaching me how to score bowling!
Let’s look at a couple of example frames for Big Al and show how strikes and spares appear on the score sheet, and how this tallies out to a score.
In this game, Big Al has rolled only nine pins (an open frame) in frame one and in the second frame he knocks down three pins on his first ball, plus seven more on his second ball (a spare). Frame two cannot be scored until he rolls one more ball.
Let’s say that Big Al rolls a six in frame three on the spare in frame two (so now frame two can be scored), follows with another spare and then a strike in frame four as shown above. Sixteen (3 + 7 + 6) is added to his score for frame one to get 25 in frame 2, while his spare plus strike combination in frames three and four net him twenty additional points in frame three.
Now let’s say he gets on a roll and bowls another strike. Frame four cannot yet be scored until he rolls one more ball, because of the scoring rule that a strike scores ten pins plus the next two balls.
In bowling parlance, two strikes in a row is known as a “double.” So let’s say he really goes on a roll and bowls another strike.
Now we can score frame four, but frames five and six cannot yet be scored. Big Al’s three strikes in a row is called a “triple” or more commonly a “turkey.” Now we’ll fill out frames seven, eight and nine, and score the results.
At this point, Big Al is doing pretty good. All he needs is eight pins to bowl what is known as “scratch.” People that can average 180 pins or more per game are known as “scratch bowlers,” which in league bowling means they are assigned no handicap, which is usually calculated as 80% of 180 minus their league average.
Let’s look at a few possible outcomes for the tenth frame.
In this case, Big Al did not get a strike or a spare: He got an open frame instead, so he doesn’t get to roll a third ball. Still, his game is quite good at a total score of 181.
If Big Al rolls a spare in frame ten, he gets to roll a third ball. So his total score for that frame is nineteen. Another excellent game.
If he bowls a strike on his first ball, he gets to roll two more balls. In this case, he managed a spare on top of the strike, to add twenty to his score in frame nine.
A perfect tenth frame consists of another turkey, adding thirty points to his frame nine score for a total of 202. Bowling a 200 game in bowling is kind of a magic number. Any time you can do that you’ve had a great game!
Here is what a perfect game looks like.
Nearly as rare as a perfect game, there is this symmetrical combination of alternating strikes and spares, resulting in a total score of 200.
This is called a “Dutch” game, for reasons I was never able to fathom. Like any other sport, bowling has its own lexicon of cool slang including gutter, curve and hook balls.
In case you’re wondering, I’ve never been a scratch bowler (without a handicap) but my high score was 233 and that came along with my personal best of seven strikes in a row! What can I say, I really messed up the other frames of that game to get only a 233.
Now that you’ve learned the ins-and-outs of scoring a game of bowling, let’s see how we might do this in SQL.
First let’s create a table to store our bowling scores, and provide you a drop statement you can use later to clean up your sandbox.
CREATE TABLE dbo.BowlingScores
(
GameID INT NOT NULL
,PlayerID INT NOT NULL
,Frame SMALLINT NOT NULL
,PinsBall1 SMALLINT NOT NULL
,PinsBall2 SMALLINT NULL
,PinsBall3 SMALLINT NULL
,PRIMARY KEY (GameID, PlayerID, Frame)
);
GO
– DROP TABLE dbo.BowlingScores;
Note that the first ball in any particular frame must always be present (NOT NULL), while rolling additional balls in a frame may be optional (so they are allowed to be NULL).
A game in bowling only has ten frames, so we’ll add a CHECK CONSTRAINT for that.
ALTER TABLE dbo.BowlingScores
ADD CONSTRAINT bs_frame CHECK
(
Frame BETWEEN 1 AND 10
);
The first ball in any frame can knock down as few as zero pins (known as a “gutter ball”) or as many as ten (a strike).
ALTER TABLE dbo.BowlingScores
ADD CONSTRAINT bs_ball1 CHECK
(
PinsBall1 BETWEEN 0 AND 10
);
The CHECK CONSTRAINTs get a little more tricky at this point because they depend on whether you’re in the tenth frame or not.
ALTER TABLE dbo.BowlingScores
ADD CONSTRAINT bs_ball2 CHECK
(
(
– In Frame 10, the second ball must always be rolled
Frame = 10 AND PinsBall2 IS NOT NULL AND
– If the first ball is a strike, the second ball can be gutter through strike
(PinsBall1 = 10 AND PinsBall2 BETWEEN 0 AND 10) OR
– If the first ball is not a strike, the total pins cannot exceed ten
(PinsBall2 BETWEEN 0 AND 10-PinsBall1)
) OR
– A strike in frames 1-9 requires that the second ball is not rolled (NULL)
(
PinsBall1 = 10 AND PinsBall2 IS NULL
) OR
– Total pins for frames 1-9 must be <= 10
PinsBall2 BETWEEN 0 AND 10-PinsBall1
);
Hopefully the comments above guide you through what is being checked to ensure a valid pin count for the second ball of each frame.
Once again, for the third ball we must break the check into pieces depending on whether it occurs in frame ten.
ALTER TABLE dbo.BowlingScores
ADD CONSTRAINT bs_ball3 CHECK
(
– Before Frame 10 no score is allowed
(Frame < 10 AND PinsBall3 IS NULL) OR
– In Frame 10, if two strikes are made the third ball must be gutter through strike
(Frame = 10 AND PinsBall1 = 10 AND PinsBall2 = 10 AND PinsBall3 BETWEEN 0 AND 10) OR
– In Frame 10, if the first ball is a strike, the third ball can’t score more than a spare
(Frame = 10 AND PinsBall1 = 10 AND ISNULL(PinsBall3, 0) BETWEEN 0 AND 10-PinsBall2) OR
– In Frame 10, if the first two balls are a spare, the third ball must be gutter to strike
(Frame = 10 AND PinsBall1+PinsBall2 = 10 AND ISNULL(PinsBall3, 0) BETWEEN 0 AND 10) OR
– In Frame 10, if the first two balls are an open frame, the third ball cannot be rolled
(Frame = 10 AND PinsBall1+PinsBall2 < 10 AND PinsBall3 IS NULL)
);
Because the PRIMARY KEY consists of game number, person ID and frame number, it won’t be possible to insert the same frame for a game/player more than once. About the only thing we can’t verify using CHECK CONSTRAINTs is a missing frame, or when the next frame is inserted but the second ball of the prior frame was not recorded (and that frame was not a strike).
As with any code you write, you should test your CHECK CONSTRAINTs thoroughly to make sure they don’t let any bad data get through. Here’s a series of test INSERTs that should all fail for the reasons specified in the comments.
– Frame number should be between 1 and 10
INSERT INTO dbo.BowlingScores VALUES(1, 1, 0, 9, NULL, NULL);
GO
– First ball in a frame cannot be more than 10
INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 11, NULL, NULL);
GO
– First ball in a frame cannot be < 0
INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, -1, NULL, NULL);
GO
– First two balls in a frame cannot be > 10
INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 8, 3, NULL);
GO
– Third ball not allowed unless this is frame 10
INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 8, 1, 1);
GO
– In Frame 10, this is not allowed
INSERT INTO dbo.BowlingScores VALUES(1, 1, 10, 8, 3, 1);
GO
– In Frame 10, this is not allowed either
INSERT INTO dbo.BowlingScores VALUES(1, 1, 10, 8, 2, 11);
GO
The T-SQL LEAD analytical function consists of the call to LEAD with one to three arguments supplied, and an OVER clause.
LEAD(field_name, row_offset, default_value) OVER (…)
The second and third arguments are optional:
The OVER clause requires an ORDER BY, but PARTITION BY is optional.
Let’s start with some sample data that is captured on a per-ball basis, based on Big Al’s game above. The objective is to write a T-SQL query that will display the score for the frame even if the frame is incomplete. This is actually a bit harder to do than it would be if you had to insert the complete results for a frame.
– The first three balls for Big Al before he makes his spare
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 1, 7, NULL, NULL);
UPDATE dbo.BowlingScores
SET PinsBall2 = 2
WHERE GameID = 1 AND PlayerID = 1 AND Frame = 1;
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 2, 3, NULL, NULL);
We can begin creating our set-based scoring algorithm using the SQL 2012 window frame feature of the OVER clause to calculate a running sum across frames. As a starting point, we’ll mostly ignore strikes and spares, but we’ll create a flag (FrameType) for use later to tell us what kind of frame has been bowled.
WITH PinCounts AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType = CASE
– Strike
WHEN PinsBall1 = 10 THEN 2
– Spare
WHEN PinsBall1 + PinsBall2 = 10 THEN 1
– Open frame
ELSE 0
END
– Pick up next ball for scoring a spare or strike
,NextBall1 = LEAD(PinsBall1, 1) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike
,NextBall2 = LEAD(PinsBall2, 1) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike if
– previous frame was a strike
,NextBall3 = LEAD(PinsBall1, 2) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
FROM dbo.BowlingScores
),
FrameScore AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType, NextBall1, NextBall2, NextBall3
– Logic to calculate the pins for this frame will go here
,FramePins = PinsBall1 + PinsBall2
FROM PinCounts
)
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameScore = CASE
WHEN FrameType = 0 AND PinsBall2 IS NOT NULL
THEN SUM(FramePins) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
ROWS UNBOUNDED PRECEDING
)
END
FROM FrameScore;
Note that we have included some results (NextBall1, NextBall2 and NextBall3) that we are not yet using but will use later when we add the logic for scoring strikes and spares. The LEAD function returns a NULL value in cases where the next frame has not been recorded. We’ve also included a stub Common Table Expression (CTE) named FrameScore where we will place most of the scoring logic.
This gives us the correct result for scoring Big Al’s first three balls.
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 NULL NULL NULL
While the results are just what we need, our CASE logic in the final SELECT will require some additional work to handle the scoring when spares and strikes are the result in a frame.
On his next ball, Big Al gets seven more pins making a spare.
UPDATE dbo.BowlingScores
SET PinsBall2 = 7
WHERE GameID = 1 AND PlayerID = 1 AND Frame = 2;
If we run the same query, these are our results:
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 7 NULL NULL
We see that frame two has still not been scored, which is the correct result because Big Al must bowl another ball before it can be scored. On his next ball (on the first ball of frame three), Big Al knocks down six pins.
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 3, 6, NULL, NULL);
To score his spare in frame two properly, we must do two things to our query.
WITH PinCounts AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType = CASE
– Strike
WHEN PinsBall1 = 10 THEN 2
– Spare
WHEN PinsBall1 + PinsBall2 = 10 THEN 1
– Open frame
ELSE 0
END
– Pick up next ball for scoring a spare or strike
,NextBall1 = LEAD(PinsBall1, 1) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike
,NextBall2 = LEAD(PinsBall2, 1) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike if
– previous frame was a strike
,NextBall3 = LEAD(PinsBall1, 2) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
FROM dbo.BowlingScores
),
FrameScore AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType, NextBall1, NextBall2, NextBall3
– Logic to calculate the pins for this frame will go here
,FramePins = CASE
– Calculate the score for a spare
WHEN PinsBall1 + PinsBall2 = 10
THEN PinsBall1 + PinsBall2 + NextBall1
– Calculate the score for an open frame
ELSE PinsBall1 + PinsBall2
END
FROM PinCounts
)
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameScore = CASE
WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR
– Display the result for a completed spare
(FrameType = 1 AND NextBall1 IS NOT NULL)
THEN SUM(FramePins) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
ROWS UNBOUNDED PRECEDING
)
END
FROM FrameScore;
Our changes are:
We see that the results are now calculated correctly for his spare.
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 7 NULL 25
1 1 3 6 NULL NULL NULL
When Big Al gets four more pins in the third frame to make his spare:
UPDATE dbo.BowlingScores
SET PinsBall2 = 4
WHERE GameID = 1 AND PlayerID = 1 AND Frame = 3;
Our scoring algorithm remains correct.
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 7 NULL 25
1 1 3 6 4 NULL NULL
Big Al then gets a strike in frame four.
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 4, 10, NULL, NULL);
Our scoring algorithm still works, but it will need some changes to make it score the next balls properly.
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 7 NULL 25
1 1 3 6 4 NULL 45
1 1 4 10 NULL NULL NULL
Here is that modified query.
WITH PinCounts AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType = CASE
– Strike
WHEN PinsBall1 = 10 THEN 2
– Spare
WHEN PinsBall1 + PinsBall2 = 10 THEN 1
– Open frame
ELSE 0
END
– Pick up next ball for scoring a spare or strike
,NextBall1 = LEAD(PinsBall1, 1) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike
,NextBall2 = LEAD(PinsBall2, 1) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike if
– previous frame was a strike
,NextBall3 = LEAD(PinsBall1, 2) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
FROM dbo.BowlingScores
),
FrameScore AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType, NextBall1, NextBall2, NextBall3
– Logic to calculate the pins for this frame will go here
,FramePins = CASE
– Calculate the score for a strike
WHEN PinsBall1 = 10
THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3)
– Calculate the score for a spare
WHEN PinsBall1 + PinsBall2 = 10
THEN PinsBall1 + PinsBall2 + NextBall1
– Calculate the score for an open frame
ELSE PinsBall1 + PinsBall2
END
FROM PinCounts
)
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameScore = CASE
WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR
– Display the result for a completed spare
(FrameType = 1 AND NextBall1 IS NOT NULL) OR
– Display the result for a completed strike
(FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL)
THEN SUM(FramePins) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
ROWS UNBOUNDED PRECEDING
)
END
FROM FrameScore;
We’ve added the scoring logic for a strike (adding the next two balls) in the FrameScore CTE and modified the CASE in the last select to display the score for a strike once it is completed. Let’s complete Big Al’s turkey with two more strikes.
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 5, 10, NULL, NULL);
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 6, 10, NULL, NULL);
Our results show that the first strike has now been scored properly, and the interested reader who is following along can verify that other combinations of pin counts also work.
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 7 NULL 25
1 1 3 6 4 NULL 45
1 1 4 10 NULL NULL 75
1 1 5 10 NULL NULL NULL
1 1 6 10 NULL NULL NULL
We’ll dispense with the statements that add an individual ball at a time and simply insert the next three frames for Big Al’s game.
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 7, 9, 1, NULL);
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 8, 10, NULL, NULL);
INSERT INTO dbo.BowlingScores
VALUES(1, 1, 9, 8, 1, NULL);
Big Al’s score now looks as follows (correct when matched against the scoring sheet we showed earlier):
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
1 1 1 7 2 NULL 9
1 1 2 3 7 NULL 25
1 1 3 6 4 NULL 45
1 1 4 10 NULL NULL 75
1 1 5 10 NULL NULL 104
1 1 6 10 NULL NULL 124
1 1 7 9 1 NULL 144
1 1 8 10 NULL NULL 163
1 1 9 8 1 NULL 172
To score the tenth frame properly, just a little tweak is required to our query (in the PinCounts CTE).
WITH PinCounts AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType = CASE
– Strike
WHEN PinsBall1 = 10 THEN 2
– Spare
WHEN PinsBall1 + PinsBall2 = 10 THEN 1
– Open frame
ELSE 0
END
– Pick up next ball for scoring a spare or strike
,NextBall1 = LEAD(PinsBall1, 1
– For frame 10, next row returns NULL
,CASE
WHEN Frame = 10 AND PinsBall1 = 10
THEN ISNULL(NULLIF(PinsBall2, 10), PinsBall3)
WHEN Frame = 10 AND PinsBall1 + PinsBall2 = 10
THEN PinsBall3
END
) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike
,NextBall2 = LEAD(PinsBall2, 1
– For frame 10, next row returns NULL
,PinsBall3
) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike if
– previous frame was a strike
,NextBall3 = LEAD(PinsBall1, 2) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
FROM dbo.BowlingScores
),
FrameScore AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType, NextBall1, NextBall2, NextBall3
– Logic to calculate the pins for this frame will go here
,FramePins = CASE
– Calculate the score for a strike
WHEN PinsBall1 = 10
THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3)
– Calculate the score for a spare
WHEN PinsBall1 + PinsBall2 = 10
THEN PinsBall1 + PinsBall2 + NextBall1
– Calculate the score for an open frame
ELSE PinsBall1 + PinsBall2
END
FROM PinCounts
)
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameScore = CASE
WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR
– Display the result for a completed spare
(FrameType = 1 AND NextBall1 IS NOT NULL) OR
– Display the result for a completed strike
(FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL)
THEN SUM(FramePins) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
ROWS UNBOUNDED PRECEDING
)
END
FROM FrameScore;
The new logic that applies to frame ten can be seen as the third argument to LEAD (the result to return instead of NULL) for NextBall1 and NextBall2 in the PinCounts CTE.
Let’s see how this works for each of the outcomes of frame tens we suggested to complete Big Al’s game. I will show only the tenth frame for each.
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
– Open frame for Frame 10
1 1 10 6 3 NULL 181
– Spare in Frame 10 followed by third ball of 9 pins
1 1 10 7 3 9 191
– Strike in Frame 10 followed by spare in balls 2 and 3
1 1 10 10 7 3 192
– Turkey in Frame 10
1 1 10 10 10 10 202
The interested reader can verify that if frame ten is not complete, the FrameScore is NULL (for all possible cases).
Let’s say that Big Al is on another roll in game two. We can insert a second game quite easily, making it consecutive strikes and spares.
INSERT INTO dbo.BowlingScores
SELECT 2, 1, 1, 10, NULL, NULL
UNION ALL SELECT 2, 1, 2, 3, 7, NULL
UNION ALL SELECT 2, 1, 3, 10, NULL, NULL
UNION ALL SELECT 2, 1, 4, 6, 4, NULL
UNION ALL SELECT 2, 1, 5, 10, NULL, NULL
UNION ALL SELECT 2, 1, 6, 9, 1, NULL
UNION ALL SELECT 2, 1, 7, 10, NULL, NULL
UNION ALL SELECT 2, 1, 8, 8, 2, NULL
UNION ALL SELECT 2, 1, 9, 10, NULL, NULL
UNION ALL SELECT 2, 1, 10, 7, 3, 10;
Our bowling-scoring algorithm shows us this for the result:
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
2 1 1 10 NULL NULL 20
2 1 2 3 7 NULL 40
2 1 3 10 NULL NULL 60
2 1 4 6 4 NULL 80
2 1 5 10 NULL NULL 100
2 1 6 9 1 NULL 120
2 1 7 10 NULL NULL 140
2 1 8 8 2 NULL 160
2 1 9 10 NULL NULL 180
2 1 10 7 3 10 200
In the final game of his three game series, Big Al rolls a perfect game, basically the game of his life!
INSERT INTO dbo.BowlingScores
SELECT 3, 1, 1, 10, NULL, NULL
UNION ALL SELECT 3, 1, 2, 10, NULL, NULL
UNION ALL SELECT 3, 1, 3, 10, NULL, NULL
UNION ALL SELECT 3, 1, 4, 10, NULL, NULL
UNION ALL SELECT 3, 1, 5, 10, NULL, NULL
UNION ALL SELECT 3, 1, 6, 10, NULL, NULL
UNION ALL SELECT 3, 1, 7, 10, NULL, NULL
UNION ALL SELECT 3, 1, 8, 10, NULL, NULL
UNION ALL SELECT 3, 1, 9, 10, NULL, NULL
UNION ALL SELECT 3, 1, 10, 10, 10, 10;
GameID PlayerID Frame PinsBall1 PinsBall2 PinsBall3 FrameScore
3 1 1 10 NULL NULL 30
3 1 2 10 NULL NULL 60
3 1 3 10 NULL NULL 90
3 1 4 10 NULL NULL 120
3 1 5 10 NULL NULL 150
3 1 6 10 NULL NULL 180
3 1 7 10 NULL NULL 210
3 1 8 10 NULL NULL 240
3 1 9 10 NULL NULL 270
3 1 10 10 10 10 300
If you ever do this in league bowling, trust me you will have too much to drink that night!
Typically in a league bowling night, each player bowls three games. To average all the games for the players, the same query can be used with some simple modifications (like encapsulating the former query into a second CTE).
WITH PinCounts AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType = CASE
– Strike
WHEN PinsBall1 = 10 THEN 2
– Spare
WHEN PinsBall1 + PinsBall2 = 10 THEN 1
– Open frame
ELSE 0
END
– Pick up next ball for scoring a spare or strike
,NextBall1 = LEAD(PinsBall1, 1
– For frame 10, next row returns NULL
,CASE
WHEN Frame = 10 AND PinsBall1 = 10
THEN ISNULL(NULLIF(PinsBall2, 10), PinsBall3)
WHEN Frame = 10 AND PinsBall1 + PinsBall2 = 10
THEN PinsBall3
END
) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike
,NextBall2 = LEAD(PinsBall2, 1
– For frame 10, next row returns NULL
,PinsBall3
) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
– Pick up second following ball for scoring a strike if
– previous frame was a strike
,NextBall3 = LEAD(PinsBall1, 2) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
)
FROM dbo.BowlingScores
),
FrameScore AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameType, NextBall1, NextBall2, NextBall3
– Logic to calculate the pins for this frame will go here
,FramePins = CASE
– Calculate the score for a strike
WHEN PinsBall1 = 10
THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3)
– Calculate the score for a spare
WHEN PinsBall1 + PinsBall2 = 10
THEN PinsBall1 + PinsBall2 + NextBall1
– Calculate the score for an open frame
ELSE PinsBall1 + PinsBall2
END
FROM PinCounts
),
GameScores AS
(
SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3
,FrameScore = CASE
WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR
– Display the result for a completed spare
(FrameType = 1 AND NextBall1 IS NOT NULL) OR
– Display the result for a completed strike
(FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL)
THEN SUM(FramePins) OVER
(
PARTITION BY GameID, PlayerID
ORDER BY Frame
ROWS UNBOUNDED PRECEDING
)
END
FROM FrameScore
)
SELECT PlayerID, AverageScore=AVG(FrameScore)
FROM GameScores
WHERE Frame = 10
GROUP BY PlayerID;
The above query will show the three game averages for all players, assuming that you had that data available. In this case, we’ve used Big Al’s first game of 181 to calculate the average.
PlayerID AverageScore
1 227
Using the same three CTEs developed above (PinCounts, FrameScore, GameScores) we can construct a final SELECT that flattens the results for each player/game. The CTEs are omitted for brevity.
SELECT GameID, PlayerID
,F1B1 = MAX(CASE Frame WHEN 1 THEN Ball1 END)
,F1B2 = MAX(CASE Frame WHEN 1 THEN Ball2 END)
,F2B1 = MAX(CASE Frame WHEN 2 THEN Ball1 END)
,F2B2 = MAX(CASE Frame WHEN 2 THEN Ball2 END)
,F3B1 = MAX(CASE Frame WHEN 3 THEN Ball1 END)
,F3B2 = MAX(CASE Frame WHEN 3 THEN Ball2 END)
,F4B1 = MAX(CASE Frame WHEN 4 THEN Ball1 END)
,F4B2 = MAX(CASE Frame WHEN 4 THEN Ball2 END)
,F5B1 = MAX(CASE Frame WHEN 5 THEN Ball1 END)
,F5B2 = MAX(CASE Frame WHEN 5 THEN Ball2 END)
,F6B1 = MAX(CASE Frame WHEN 6 THEN Ball1 END)
,F6B2 = MAX(CASE Frame WHEN 6 THEN Ball2 END)
,F7B1 = MAX(CASE Frame WHEN 7 THEN Ball1 END)
,F7B2 = MAX(CASE Frame WHEN 7 THEN Ball2 END)
,F8B1 = MAX(CASE Frame WHEN 8 THEN Ball1 END)
,F8B2 = MAX(CASE Frame WHEN 8 THEN Ball2 END)
,F9B1 = MAX(CASE Frame WHEN 9 THEN Ball1 END)
,F9B2 = MAX(CASE Frame WHEN 9 THEN Ball2 END)
,F10B1 = MAX(CASE Frame WHEN 10 THEN Ball1 END)
,F10B2 = MAX(CASE Frame WHEN 10 THEN Ball2 END)
,F10B3 = MAX(CASE Frame WHEN 10 THEN Ball3 END)
FROM GameScores a
CROSS APPLY
(
SELECT Ball1 = CASE PinsBall1 WHEN 10 THEN ‘X’ ELSE CAST(PinsBall1 AS VARCHAR) END
,Ball2 = CASE
WHEN PinsBall2 IS NULL THEN ”
WHEN PinsBall1+PinsBall2 = 10 THEN ‘/’
WHEN PinsBall1+PinsBall2 < 10 THEN CAST(PinsBall2 AS VARCHAR)
ELSE ”
END
,Ball3 = CASE
WHEN PinsBall3 IS NULL THEN ”
WHEN PinsBall3 = 10 THEN ‘X’
WHEN PinsBall2+PinsBall3 = 10 THEN ‘/’
WHEN PinsBall1+PinsBall2 < 10 THEN CAST(PinsBall3 AS VARCHAR)
ELSE ”
END
) b
GROUP BY GameID, PlayerID;
This is referred to as a crosstab query because the result pivots the rows to columns. The CROSS APPLY is used to calculate intermediate results that greatly simplifies the redundancy that would have otherwise been present in each MAX function.
If you’ve never seen or written a crosstab query before, we encourage you to add in the results for the total frame score where it makes sense to do so.
A data modeling purist may argue that it would be better to have a BallID column in the table, with one column to record the pin score for that ball. Then BallID could be 1 or 2 in any frame, and 3 would be allowed in frame ten. This also allows for one row to be recorded in frames one through nine when a strike is obtained by the bowler.
However that approach would not allow for the same level of validations performed by the CHECK CONSTRAINTs that we’ve provided.
Another approach would be to include a column for FrameScore in the table. Since FrameScore can easily be calculated as we’ve shown, that is not a normalized data model. However if you chose to use that approach, you could also calculate the FrameScore in an AFTER INSERT, UPDATE TRIGGER. Because of the need to “look forward,” you would not be able to use a computed column.
More than anything, I hope that you’ve concluded that learning complex SQL can be fun!
In this article, we have demonstrated quite a number of somewhat advanced T-SQL features including:
Because we have built up our query one little bit at a time working through a scoring example, it may be possible to further simplify the query and make it a little bit shorter. We’ll leave that as an exercise for our more intrepid readers, and we are interested to see those results posted to the comments section of this article.
Our methodology demonstrated how you can take a relatively complex problem and break it into sub-problems that you can solve one little bit at a time, and this is an essential skill in formulating and writing complex queries in any dialect of the SQL language.
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
In calendar year 2014, the SQL Server escalation team had the opportunity to work on several interesting and challenging customers issues. One trend we noticed is that many customers were migrating from old versions of SQL Server running on lean hardware to newer versions of SQL Server with powerful hardware configurations. Typical examples would look like this: SQL 2005 + Win 2003 on 16 cores + 128 GB RAM migrated to SQL 2012 + Win 2012 on 64 cores + 1 TB RAM. The application workload or patterns remained pretty much the same. These servers normally handle workloads that is multiple thousand batches per sec. Under these circumstances, the normal expectation is that the throughput and performance will increase in line with the increase in the capabilities of the hardware and software. That is usually the case. But there are some scenarios where you need to take some additional precautions or perform some configuration changes. These changes were done for specific user scenarios and workload patterns that encountered a specific bottleneck or a scalability challenge.
As we worked through these issues, we started to capture the common configuration changes or updates that were required on these newer hardware machines. The difference in throughput and performance is very noticeable on these systems when these configuration changes were implemented. The changes include the following:
- SQL Server product updates [Cumulative Updates for SQL Server 2012 and SQL Server 2014]
- Trace flags to enable certain scalability updates
- Configuration options in SQL Server related to scalability and concurrency
- Configuration options in Windows related to scalability and concurrency
All these recommendations are now available in the knowledge base article 2964518:
As we continue to find new updates or tuning options that are used widely we will add them to this article. Note that these recommendations are primarily applicable for SQL Server 2012 and SQL Server 2014. Few of these options are available in previous versions and you can utilize them when applicable.
If you are bringing new servers online or migrating existing workloads to upgraded hardware and software, please consider all these updates and configuration options. They can save a lot of troubleshooting time and provide you with a smooth transition to powerful and faster systems. Our team is using this as a checklist while troubleshooting to make sure that SQL Servers running on newer hardware is using the appropriate and recommended configuration.
Several members of my team and the SQL Server product group contributed to various efforts related to these recommendations and product updates. We also worked with members of our SQL Server MVP group [thank you Aaron Bertrand and Glenn Berry] to ensure these recommendations are widely applicable and acceptable for performance tuning.
We hope that you will implement these updates and configuration changes in your SQL Server environment and realize good performance and scalability gains.
Suresh B. Kandoth
SQL Server Escalation Team
Microsoft SQL Server
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
It is sometimes common practice to enable Distributed Transaction (DTC) behavior but it can be unnecessary, and adds unwanted overhead.
DTC has the ability to determine single phase vs two phase commit requirements. A DTC transaction involves resource managers (RMs) of which SQL Server can be one of them. If a single resource manager is involved in the transaction there is no need to perform 2-phase commit. DTC shortcuts the activity and performs a single-phase commit safely. This reduces the communication between the DTC and RM managers. However, the overhead of the DTC manager is still involved making the transaction slightly slower than a native TSQL transaction.
Single Phase
The following is a single phase DTC commit example.
begin distributed tran
goupdate dbTest.dbo.tblTest set object_id = 100
gocommit tran
go
Notice the trace output does not indicate a prepared state. This is a direct indication of a single phase commit.
Two Phase
The following is a 2-phase commit example.
begin distributed tran
goupdate MYREMOTESERVER.dbTest.dbo.tblTest set object_id = 100
gocommit tran
go
The transaction involved the local instance (RM=1) and a remote instance (RM=2). With 2 RMs involved DTC commits the transaction under full, 2-phase commit protocol. Notice the prepared state in the trace indicating full, 2-phase commit protocol is being used.
You may want to review the DTC transactions executing on your system, looking for prepared state. If the DTC transactions running on your system are not using 2-phase commit protocol you should consider removing DTC from the transactions in order to improve performance.
Bob Dorr – Principal SQL Server Escalation Engineer
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
Michael Donnelly, president of the Minnesota SQL Server User Group, has worked as a database administrator (DBA) and a database architect during his career. As a DBA, time was always of the essence, but one thing he wouldn’t delegate was restoring the database from backups. He considered SQL Server database recovery too technical a task to leave to junior DBAs or developers.
Had restoration been less intense, “it would be hugely beneficial to hand off tasks to some of the junior DBAs,” said Donnelly, who today is a consultant at Digineer.
Donnelly described the process he had to go through to restore a 300 GB file. While he needed only the one table — the file — he still had to restore the entire database from backup and then move it to the development environment. Before that point, however, he had to clean the development environment so the database would fit. He believes it would have been much easier if he had to copy only the tables he needed.
After hearing about this challenge from Donnelly and other SQL Server users, Minneapolis-based data-recovery company Kroll Ontrack Inc. jumped at the business opportunity. The resulting product, a SQL Server version of the vendor’s Ontrack PowerControls software, became generally available in November. It lets users select and restore just the tables they need using drag-and-drop functionality.
The new product is welcome news for Shannon Roderick, a database administrator at Gillette Children’s Specialty Healthcare in St. Paul, Minn., who, along with Donnelly, participated in alpha testing for Ontrack PowerControls for SQL.
At his job prior to Gillette, Roderick frequently had to restore a database after a developer made an incorrect update. It would then take approximately 20 minutes to get the data back. Roderick used Dell’s LiteSpeed software to perform the restoration, which wasn’t as quick or efficient as he wanted.
“When you update production,” he said, “and everything goes haywire, you want [the data] back as soon as possible.”
Kroll Ontrack began the alpha test in July 2014. It found its testers through the Minnesota SQL Server User Group. Donnelly feels that Ontrack PowerControls allows DBAs to hand off database restoration responsibilities. “Even a developer who is a little bit SQL-Server-savvy can use this [product] to input their data,” he said. “I’ve seen third-party tools with something similar to this, but never something with drag-and-drop functionality.”
However, Donnelly has a few requests for later versions. Ontrack PowerControls is currently rated only for SQL Server 2012, and Donnelly uses SQL Server 2014. He also specifically asked for referential integrity in version two.
Roderick has been using Ontrack PowerControls for development, administration and recovery. However, he did not use it for recovery in the production server during the alpha test. “It’s quick and easy to get into and doesn’t have a lot of overhead,” he said.
He noted that with Ontrack PowerControls, there isn’t any need to go into the production server. All he has to do is go into the backup and pull the information. Compared to LiteSpeed and similar products, Roderick said, “It’s more efficient for the one task it does well.”
Roderick added that Ontrack PowerControls is also workflow-oriented. “It’s a very, very targeted recovery,” he added. Roderick wants to see more granular recovery in the next version.
“The best part about [the alpha test] is that Kroll has been so interested in getting feedback from the community,” Roderick said. Roderick and Donnelly have met regularly with Kroll Ontrack representatives and made suggestions that in turn they saw incorporated into the product.
Kroll Ontrack’s involvement with its alpha users has been far greater than in past tests, said Tom McCaffrey, product director for enterprise solutions. However, McCaffrey said, the process has been such a success that management at Kroll Ontrack is encouraging other teams to implement the same tactics.
McCaffrey intends to continue meeting with the Minnesota SQL Server User Group over the next year to get more feedback on the SQL Server database recovery technology. He said Kroll will be releasing monthly updates to Ontrack PowerControls for SQL, with the added features to be determined by user request. The top new feature right now is increasing the granularity of the restore — Roderick’s request. In the next release, according to McCaffrey, users can expect to see Donnelly’s requests — support for SQL Server 2014, 2008 and 2005, and enhanced referential integrity — as well as compressed backups.
Jessica Sirkin is associate site editor of SearchSQLServer. Email her at jsirkin@techtarget.com and follow us on Twitter: @SearchSQLServer.
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
Dear Customers,
The 5th cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site.
To learn more about the release or servicing model, please visit:
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
Traffic on the two primary websites operated by Canadian direct-sales food distributor Epicure went up substantially at the end of each month — so much that the spikes often made the sites unusable. Epicure tried working with the third-party vendor that hosts the sites to resolve the problem, but saw no improvement. And the North Saanich, British Columbia company’s business model is dependent on the two sites. It uses one website to market itself and its selection of food and cookware products; the other site is used by the independent “consultants” who sell the Epicure products at tasting parties to place orders and communicate with the company.
Russell Born, senior network infrastructure administrator at Epicure, looked for a way to keep the sites running smoothly through the end-of-month rush — and, while doing a Google search last year, he found SIOS Technology Corp. and its DataKeeper data replication software and SANLess clustering tools. The SIOS offerings enable users looking to guard against processing downtime to create Windows or Linux server clusters that utilize local storage only, avoiding the need to deploy a storage area network (SAN).
“Before that, I thought we required a SAN,” Born said. SIOS also supports SAN-based clusters, but Epicure would have faced some difficulties if [it] went that route,” he added. According to Born, a SAN leads to higher costs and more setup effort; it also creates a “single point of failure,” since the entire operation becomes dependent on it to function. To combat that, Born could have deployed multiple SANs, but he said doing so would have become prohibitively expensive.
Epicure had another good reason to want to bolster its websites, which use SQL Server to process and fulfill orders and track the work of its consultants. Shortly before Born learned about SIOS, Epicure’s data center suffered an outage that lasted several hours. “We didn’t have redundant Web servers, so there was no failover externally,” he said.
The company had two servers, one for each site, and both were reaching their resource limit, which contributed to the end-of-month performance problems. In addition to SIOS, Born turned to cloud computing vendor Amazon Web Services (AWS) to help relieve the pressure. The new setup for Epicure includes a primary Web server running on-premises and a cloud-based failover system, and the SIOS software replicating data between them. Concurrently, Epicure is upgrading from SQL Server 2008 to SQL Server 2012, and from Windows 2008 to Windows 2012.
Jerry Melnick, COO of San Mateo, Calif.-based SIOS, said one of the primary motivators for the deployment was Epicure’s concerns about disaster protection and recovery. “There is so much depending on those sites,” he said. “Users are ordering off the sites. If they go down, it could take weeks to get [them] back up.”
One method for ensuring high availability is to deploy servers in a second location so that, if the primary data center goes down, you can failover to the backup one. But Melnick said that has a high startup cost, so he recommended companies like Epicure tap into a cloud provider, such as AWS, for disaster recovery. SIOS’ job, according to Melnick, is to be the linchpin of the SANLess cluster.
Consolidating the two servers into one primary system also saved the company some money on the DataKeeper software. “SIOS’ licenses are based on the number of servers, so it was half the cost,” Born said.
Also, prior to working with SIOS, Epicure had been using SQL Server Enterprise Edition solely because that was the only way to get the high-availability features included in the database. Now that Epicure is using the SIOS software, Born said it has been able to downgrade to the much less-expensive SQL Server Standard Edition, achieving “very substantial cost savings” on that as well.
Born started out with some reservations about the SIOS technology. “I did have concerns initially that if SIOS lost its data connection, there would be some corruption,” he said. “But we didn’t see that.” In fact, he was able to work out all of the startup issues with the SQL Server version of DataKeeper while testing the product, and only needed to make a couple calls to SIOS support. “It migrated over seamlessly,” Born said, adding that so far, he’s pleased with what the SIOS software has done for Epicure’s websites and the SQL Server systems that power them.
Jessica Sirkin is associate site editor of SearchSQLServer. Email her at jsirkin@techtarget.com and follow us on Twitter: @SearchSQLServer.
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
I’ve had about 4 cases in the last two months that centered around the following error when trying to use Windows Integrated authentication with JDBC.
java.sql.SQLException: This driver is not configured for integrated authentication
The key environment point on this was that they were trying to do this on a Linux platorm and not a Windows platform. Specifically they were running WebSphere on a Linux platform. The last one I worked on was running WebSphere 8.5.
There is only one location within the JDBC Driver where this particular error is raised. It is when we are trying to use Kerberos and the Authentication Scheme is set to NativeAuthentication, which is the default setting for this property. Starting in the JDBC 4.0 driver, you can use the authenticationScheme connection property to indicate how you want to use Kerberos to connect to SQL. There are two settings here.
NativeAuthentication (default) – This uses the sqljdbc_auth.dll and is specific to the Windows platform. This was the only option prior to the JDBC 4.0 driver.
JavaKerberos – Makes use of the Java API’s to invoke kerberos and does not rely on the Windows Platform. This is java specific and not bound to the underlying operating system, so this can be used on both Windows and Linux platforms.
So, if you are receiving the error above, there are three possibilities that could be causing it to show up. First, you actually specified authenticationScheme=NativeAuthentication in your connection string and you are on a Linux Platform. Second, you specified integratedSecurity and omitted authenticationScheme, which defaulted to NativeAuthentication, and you are on a Unix/Linux Platform. Third, you are using a version of the JDBC Driver prior to the 4.0 driver and trying to use Integrated Authentication on a Unix/Linux platform. In the third case, even if you specify authenticationScheme=JavaKerberos, it won’t help as the older drivers aren’t aware of it, so it is ignored.
The following document outlines how to use Kerberos with the JDBC Driver and walks through what is needed to get JavaKerberos working properly.
Using Kerberos Integrated Authentication to Connect to SQL Server
http://msdn.microsoft.com/en-us/library/gg558122%28v=sql.110%29.aspx
Another aspect that was discovered was the that it appears that the WebSphere 8.5 release comes with the 3.0 version of the SQL JDBC Driver. This will not honor the JavaKerberos setting and you will get the error listed above.
So, you will need to make sure your driver is updated to the 4.0 driver or later. After that is done, you will need to make sure that the Kerberos Configuration file (krb5.ini or krb5.conf) is configured properly on your platform. In the above referenced documentation we have a sample of what that should look like. You will also need to generate keytab files for the platform to reference. A login configuration file also needs to be setup. If you don’t have one, the driver will automatically configure it using the Krb5LoginModule. If you need to use a different Login Module, you will need to make sure that is configured for your environment. Assuming all of that is in place, the driver should work using JavaKerberos to connect.
The following blog does a good job of walking through the steps to get this setup for Java. It indicates Weblogic, but really it just goes through the java aspects. It walks through how to create the keytab files and what to do with the krb5.ini file.
Configure Kerberos with Weblogic Server (really just a Java reference)
https://blogbypuneeth.wordpress.com/configure-kerberos-with-weblogic-server/
If you have a multiple domain environment with SQL Servers in different domains that you are trying to hit, you will run into issues. We found that in order to get it to work properly, you need to set the default domain within the Kerberos configuration file, to the domain that the SQL Server resides in. You can only have one default domain, so if you have multiple SQL Servers in different domains, you are going to have to pick one.
I’ve also heard a lot of questions and seen confusion on the file versioning, file name and system requirements. Here is a table where I tried to highlight what comes with what driver for reference.
JDBC Driver Version
JAR Files
JDBC API Support
Supported JVM
2.0
sqljdbc.jar
3.0
1.5
sqljdbc4.jar
4.0
1.6 or later
3.0
sqljdbc.jar
3.0
1.5
sqljdbc4.jar
4.0
1.6 or later
4.0
sqljdbc.jar
3.0
1.5
sqljdbc4.jar
4.0
1.6 or later
4.1
sqljdbc.jar
3.0
1.5
sqljdbc4.jar
4.0
1.6 or later
sqljdbc41.jar
4.0
1.7 or later
Also, we have documentation regarding the System Requirements that you can look at that goes a little further into this.
System Requirements for the JDBC Driver
http://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx
Hopefully this will help clear things up for you when using the SQL JDBC Driver on a Unix/Linux Platform.
Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton
Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
Interested in the new features coming for Failover Clustering? I recently stopped over at Channel 9 and did an interview where we discussed some of the big clustering and availability features in Windows Server Technical Preview.
Here’s the link:
http://channel9.msdn.com/Shows/Edge/Edge-Show-125
Thanks!
Elden Christensen
Principal Program Manager Lead
Clustering & High-Availability
Microsoft
Congratulations, all very exciting new features, especially the new storage spaces sharing nothing, just love that 
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
