Create a Whitelist for SQL Server - UPDATED!!!

A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.

The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.

We’ll add 1 row to the WhiteList table should allow all users from the workstation, ECHOBASE1, access, regardless of its IP address.

USE master;
GO
INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
VALUES ('*','ECHOBASE1','*');
GO

Next, we’ll create a SQL login with only connect permission to the server and nothing else.

CREATE LOGIN LogonTriggerTest WITH PASSWORD = 'Password1';
GO

Finally, we’ll open a new query window using that login.

As you can see, we are denied access to the server because of the logon trigger. If we look in the Errorlog, we can see that we lack the VIEW SERVER STATE permission.

This was my first mistake. I did my initial testing using an administrative login. Most users are not admins; therefore, they will not have the permission required to view sys.dm_exec_connections. I was using this DMV to get the IP address of the client connection, and it requires the VIEW SERVER STATE permission. To get around this, I can use the CONNECTIONPROPERTY function, as it does not require any additional permissions.

SELECT CONNECTIONPROPERTY(‘client_net_address’);
GO

Now let’s try to connect again.

Again, we failed. This would be my second mistake. I failed to grant SELECT access to the WhiteList table. By default, a user is will have public permission to the master database, but no permission to the table. To solve this, we can grant permission to the public database role. This will allow any authenticated user to read from the WhiteList table.

USE master;
GO
GRANT SELECT ON dbo.WhiteList TO public;
GO

Finally, our connection to SQL Server is successful. Using the same code from the trigger, we can compare it what’s in the WhiteList table.

USE master;
GO
SELECT
   ORIGINAL_LOGIN() AS 'LoginName'
  ,HOST_NAME() AS 'HostName'
  ,CONNECTIONPROPERTY('client_net_address') AS 'HostIpAddress';
GO
SELECT * FROM dbo.WhiteList;
GO

If I had followed my own rules, I could have discovered most of these issues before posting the original article.

The fully updated code is below. Please let me know if you run into any other issues with this new version. I also added another column to the WhiteList table that can be used for hold comments. The idea is to provide some documentation about what the white-listed item is attempting to do.

USE master;
GO

IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
  DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList
(
   Id INT IDENTITY(1,1) PRIMARY KEY
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(50)
  ,Comments VARCHAR(2000)
);
GO

GRANT SELECT ON dbo.WhiteList TO PUBLIC;
GO

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress,Comments)
VALUES
   ('*','ECHOBASE1','*','Any user from the workstation "ECHOBASE1" is allowed to connect, regardless of IP address.')
  ,('WebSiteLogin','webserver1','192.168.100.55','Only the WebSiteLogin from webserver1 with an IP of 192.168.100.55 is allowed access.');
GO

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
  DECLARE 
     @LoginName VARCHAR(255) = ORIGINAL_LOGIN()
    ,@HostName VARCHAR(255) = HOST_NAME()
    ,@HostIpAddress VARCHAR(50) = CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address'));

  IF 
  (
    SELECT COUNT(*) FROM dbo.WhiteList
    WHERE 
    (
      (LoginName = @LoginName) OR (LoginName = '*')
    )
    AND
    (
      (HostName = @HostName) OR (HostName = '*')
    )
    AND
    (
      (HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*')
    )
  ) = 0
  ROLLBACK;
END;
GO

Create a Whitelist for SQL Server

UPDATED - May 16, 2017 - Please review the updated code here.

In my previous post, Configuring the Azure SQL Database Firewall, I discussed how you can configure the firewall rules to manage access to the SQL Server. Today, I wanted to demonstrate how you can accomplish the same thing with your on-premise SQL Servers by creating a whitelist.

So let’s start off by defining “whitelist”. According to the American Heritage Dictionary, it is “a list of people or organizations that have been approved to receive special considerations”. In our example, that “special consideration” is access to the SQL Server only if your workstation IP address is on the whitelist.

So why would we care about this? Why not just manage access to SQL Server the normal way with logins and passwords. Here is one example. Let’s say you have a company policy that prohibits anyone from using a common login to connect to a SQL Server. But your application uses a single SQL login to make its connection to SQL Server, and EVERY developer in the company knows the password. Even though there is a written policy in place, what would prevent one of those developers form connecting to SQL Server to fix a bug, or worse, change data to circumvent the application logic.

A whitelist will define which logins are allowed to connect to SQL Server from a specific IP address. Using our scenario from above, we can walk through an example. First we need to create the whitelist table to enforce this policy: the application login (WebSiteLogin) should only be allowed to connect to SQL Server if it originates from the web server’s hostname (webserver1) and IP address: 192.168.100.55.

USE master;
GO

IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
    DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList
(
     Id INT IDENTITY(1,1) PRIMARY KEY
    ,LoginName VARCHAR(255)
    ,HostName VARCHAR(255)
    ,HostIpAddress VARCHAR(15)
);
GO

Next, we need to add an entry to allow access.

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
SELECT 'WebSiteLogin','webserver1','192.168.100.55';
GO

Just creating the whitelist does nothing. That’s why we need to use a logon trigger to enforce the whiltelist rules.

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE 
         @LoginName varchar(255) = ORIGINAL_LOGIN()
        ,@HostName varchar(255) = HOST_NAME()
        ,@HostIpAddress varchar(15);

    SELECT @HostIpAddress = client_net_address 
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID;

    IF 
    (
        SELECT COUNT(*) FROM dbo.WhiteList
        WHERE 
        (
            (LoginName = @LoginName) OR (LoginName = '*')
        )
        AND
        (
            (HostName = @HostName) OR (HostName = '*')
        )
        AND
        (
            (HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*')
        )
    ) = 0
    ROLLBACK;
END;
GO

The logon trigger simply compares the user’s login name, hostname, and IP address to what’s in the WhiteList table. If there is a match, then access is allowed. If not, then the connection is terminated via a rollback. To test this, we can attempt to login using WebSiteLogin from an IP address other than 192.168.100.55.

Immediately, we are greeted with a logon error message.

In addition to comparing the logins, the code also adds a wildcard option using the asterisk (*).

SELECT * FROM dbo.WhiteList;
GO

Line 1 has an asterisk for LoginName and HostIpAddress. This means that any login from any IP address is allowed to connect as long as the HostName is dbserver. Line 2 has an asterisk for HostName and HostIpAddress. This means the sa login is allowed to connect from any hostname or IP address. Line 3 is the example we used above. An asktrisk in all three columns would allow any login from any hostname or IP address to connect.

In the event you lock yourself (or everyone) out of the SQL Server, there is a quick way to restore access. You’ll need to connect to SQL Server using the Dedicated Admin Connection, either through Management Studio or the SQLCMD command line using a login with SysAdmin permission. Once connected, you can disable the logon trigger.

From the example above, you can see how quickly you can implement your own firewall for your on-premise SQL Servers using a whitelist and a logon trigger.

Additional resources about Logon Triggers:
https://msdn.microsoft.com/en-us/library/bb326598.aspx

How Long is that SQL Command Going to Take?

Have you ever needed to restore a large database while someone is standing over your shoulder asking “How long is that going to take"? If that hasn't happened to you yet, then it’s only a matter of time.

Let’s throw out all the reasons why you need to do the restore and just discuss the technical part. Obviously the easiest way to know how long the restore will take is to use the “WITH STATS” option in the restore database command. But let’s say in the heat of the moment you forgot that little piece of the statement. Now what?

In older versions of SQL Server, there was really no way to tell how long a database restore operation would take to complete. You could make a rough guesstimate that if it took SQL Server one hour to backup the database, then it’s likely the restore would take the same amount of time. But in reality, that’s just a guess, and the person standing over your shoulder probably wants a more accurate estimation.

First introduced in SQL Server 2005, DMVs give us a wealth of new information on the internals of SQL Server, and for our dilemma above, we can use sys.dm_exec_requests. This DMV returns one row for each session that actively executing a command. One of the columns returned by the DMV is percent_complete which returns the percent complete for the currently executing command.

USE master;
GO
SELECT
   session_id
  ,start_time
  ,status
  ,command
  ,percent_complete
FROM sys.dm_exec_requests
WHERE session_id = 56;
GO

It looks like the database restore is about 33% complete. Now you have a more accurate idea of how long it will take to complete.

This may seem like a useless tidbit of information, since you can use the “WITH STATS” option in the restore database command to get the same information, but what happens when the command your running doesn't have that option; for example, DBCC SHRINKFILE.

On the rare occasion when you need to shrink a database to free up disk space, SQL Server needs to physically move data pages from one part of the file to another. Depending on how much data needs to be moved, this could take a long time. Using the same TSQL statement from above, we can query the sys.dm_exec_requests DMV.

This is awesome! A percent complete value for every command executing on SQL Server? Not so fast. The percent_complete column in sys.dm_exec_requests only works a few commands.

From Books Online:

• ALTER INDEX REORGANIZE
• AUTO_SHRINK option with ALTER DATABASE
• BACKUP DATABASE
• CREATE INDEX
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• KILL (Transact-SQL)
• RESTORE DATABASE
• UPDATE STATISTICS

What a bummer that it doesn't work for every command. But from a DBA’s point of view, this list comprises quite a few of the administrative commands you’d use on a regular basis. These are commands that you would run instead of an end user, and knowing that you can relay a “percent complete” value back to them will assure them you are taking good care of their database.

For more details on this DMV, check out Books Online.

Are You the Primary Replica?

UPDATED -- Jul 3, 2015 -- To verify database exists, per comments by Konstantinos Katsoridis. Thanks for finding the bug!

In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica.  The gap being Microsoft did not provide a DMO to return this information.  The good news is the documentation for the upcoming release of SQL Server 2014 looks to include a DMO, but that doesn't help those of us who are running SQL Server 2012.

I've developed a function, dbo.fn_hadr_is_primary_replica, to provide you with this functionality.  This is a simple scalar function that takes a database name as the input parameter and outputs one of the following values.

 0 = Resolving
 1 = Primary Replica
 2 = Secondary Replica
-1 = Database Does Not Exist

The return values correspond to the role status listed in sys.dm_hadr_availability_replica_states.

In this example, I have setup 2 SQL Servers (SQLCLU1\SPIRIT1 and SQLCLU2\SPIRIT2) to participate in some Availability Groups.  I have setup 2 Availability Groups; one for AdventureWorks2012 and a second for the Northwind database.  SQLCLU1\SPIRIT1 is the primary for AdventureWorks2012 and secondary for Northwind.  SQLCLU2\SPIRIT2 is the primary for Northwind and secondary for AdventureWorks2012.

First let's run the function for both databases on SQLCLU1\SPIRIT1.

On this server, the function returns 1 because it's the primary for AdventureWorks2012, and returns 2 because it's the secondary for Northwind.

Now let's run it again on SQLCLU2\SPIRIT2.

As expected we get the opposite result.

This function does not take into account the preferred backup replica; it only returns information based on whether it is the primary or secondary replica.  It was created to use within other scripts to help determine a database's role if it's part of an Availability Group.  I hope this script can help you as well.

USE master;
GO

IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_hadr_is_primary_replica;
GO

CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************

  File Name:    fn_hadr_is_primary_replica.sql

  Applies to:   SQL Server 2012

  Purpose:      To return either 0, 1, 2, or -1 based on whether this 
                @DatabaseName is a primary or secondary replica.

  Parameters:   @DatabaseName - The name of the database to check.

  Returns:      0 = Resolving
                1 = Primary
                2 = Secondary
               -1 = Database does not exist

  Author:       Patrick Keisler

  Version:      1.0.1 - 07/03/2015

  Help:         http://www.patrickkeisler.com/

  License:      Freeware

********************************************************************/

BEGIN
    DECLARE @HadrRole TINYINT;

    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
    BEGIN
        -- Return role status from sys.dm_hadr_availability_replica_states
        SELECT @HadrRole = ars.role
        FROM sys.dm_hadr_availability_replica_states ars
        INNER JOIN sys.databases dbs 
            ON ars.replica_id = dbs.replica_id
        WHERE dbs.name = @DatabaseName;
    
        -- @DatabaseName exists but does not belong to an AG so return 1
        IF @HadrRole IS NULL RETURN 1;

        RETURN @HadrRole;
    END
    ELSE
    BEGIN
        -- @DatabaseName does not exist so return -1
        RETURN -1;
    END    
END;
GO

An Alternative to SELECT COUNT(*) for Better Performance

Sometimes rapid code development doesn't always produce the most efficient code.  Take the age old line of code SELECT COUNT(*) FROM MyTable.  Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a table or clustered index scan.

USE AdventureWorksDW2012;

SELECT COUNT(*) FROM dbo.FactProductInventory;

GO

Turning on STATISTICS IO on reveals 5753 logical reads just to return the row count of 776286.

Table 'FactProductInventory'. Scan count 1, logical reads 5753, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Starting with SQL Server 2005, Microsoft introduced a DMV, sys.dm_db_partition_stats, that provides you with the same information at a fraction of the cost.  It requires a little more coding, but once you turn on STATISTICS IO, you will see the performance benefit.

USE AdventureWorksDW2012;

SELECT

        s.name AS 'SchemaName'

       ,o.name AS 'TableName'

       ,SUM(p.row_count) AS 'RowCount'

FROM sys.dm_db_partition_stats p

       JOIN sys.objects o ON o.object_id = p.object_id

       JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE p.index_id < 2 AND o.type = 'U'

       AND s.name = 'dbo'

       AND o.name = 'FactProductInventory'

GROUP BY s.name,o.name

ORDER BY s.name,o.name;

GO

Since we're querying a DMV, we never touch the base table.  We can see here we only need 16 logical reads to return the same row count of 776286, and the FactProductInventory table is nowhere in our execution plan.

Table 'sysidxstats'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

By using the DMV, we have improved the query performance and reduced the total I/O count by nearly 100%. Another added benefit of using the DMV, is we won't need locks on the base table and therefore will avoid the possibility of blocking other queries hitting that table.

This is just one simple example of how you can easily improve the performance of an application.

Merging SQL Server and Softball Just for Fun

With opening day of Major League Baseball season finally here, I thought I’d take the time to cover two of my favorite topics…SQL Server and softball.  Have you ever thought about how you can use SQL Server in conjunction with softball? Ok, so maybe you haven’t, but I have.  I have been managing a slow-pitch softball team, the Sons of Pitches, for the past 5 years.  Yes, I did say “slow-pitch”.  My friends and I have already passed the peak of our physical ability, so we use it as an excuse to get together and have a little fun.

As a DBA, I’m big on keeping track of metrics, so naturally this spills over into my extracurricular activities.  For each softball game, the league requires us to keep score, but in addition to that I like to keep individual player stats.  Why would I want to do this?  For benchmarking, of course!  How will I ever tell if my players are getting better or worse without it?  So to save myself a lot of time each week, I created a SQL Server database to keep track of all the stats.  Each week, all I have to do is enter the stats and then generate a report by running a few TSQL scripts.  Below is a sample of the score book that I keep for each game.  This is the only manual piece.  Everything else is automated by SQL Server.

Let’s start by creating our database and then a Games table to hold all of the data.

CREATE DATABASE Softball;

GO

USE Softball;

GO

CREATE TABLE dbo.Games(

    [Id] INT IDENTITY(1,1) NOT NULL,

    [GameDate] DATE DEFAULT(GETDATE()) NOT NULL,

    [SeasonId] INT NOT NULL,

    [SeasonName] VARCHAR(50) NOT NULL,

    [GameNumber] SMALLINT NOT NULL,

    [BattingOrder] SMALLINT NOT NULL,

    [Roster] SMALLINT NOT NULL,

    [Name] VARCHAR(50) NOT NULL,

    [PA] FLOAT NOT NULL,

    [AB]  AS ([PA]-([SACf]+[BB])),

    [H]  AS ((([1B]+[2B])+[3B])+[HR]),

    [1B] FLOAT NOT NULL,

    [2B] FLOAT NOT NULL,

    [3B] FLOAT NOT NULL,

    [HR] FLOAT NOT NULL,

    [ROE] FLOAT NOT NULL,

    [SACf] FLOAT NOT NULL,

    [BB] FLOAT NOT NULL,

    [K] FLOAT NOT NULL,

    [RBI] FLOAT NOT NULL,

    [RUNS] FLOAT NOT NULL

);

GO

ALTER TABLE dbo.Games

    ADD CONSTRAINT PK_Games PRIMARY KEY CLUSTERED (Id,GameDate);

GO

Let's backup for one minute.  For those of you who are not familiar with baseball (or softball) scoring; here’s a quick break down of what each abbreviation means.  These are the definitions for my softball scoring.  Some baseball statistics are omitted, because they are not relevant in our league.

PA = Plate Appearance: number of times that player appeared in the batter’s box.
AB = At Bat: plate appearances, not including walks or sacrifices.
H = Hit: number of times a batter safely reached a base w/o an error by the defense.
1B = Single: number of times a batter safely reached first base w/o an error by the defense.
2B = Double: number of times a batter safely reached second base w/o an error by the defense.
3B = Triple: number of times a batter safely reached third base w/o an error by the defense.
HR = Home Run: number of times a batter safely reached all four bases w/o an error by the defense.
ROE = Reached on Error: number of times a batter safely reached a base with an error by the defense.
SACf = Sacrifice Fly: Fly ball hit the outfield that was caught for an out, but allowed a base runner to advance.
BB = Base on Balls (aka Walk): number of times a batter did not swing at four pitches outside the strike zone, and was awarded first base by the umpire.
K = Strike Out: number of times a third strike is called or swung at and missed, or hit foul when the batter already had two strikes.
RBI = Run Batted In: number of runners who scored as a result of the batters’ action.
RUNS = Runs Scored: number of times a runner crossed home plate.
BA = Batting Average: Hits (H) divided by At Bats (AB).
OB = On Base Percentage: number of times a batter reached base (H + AB) divided by (AB + BB + SACf).
SLUG = Slugging Average: number of bases achieved (1B+2B*2+3B*3+HR*4) divided by At Bats (AB). One base is for each 1B, two bases for each 2B, three bases for each 3B, and four bases for each HR.
OPS = On Base Percentage Plus Slugging: sum of batter’s OB + SLUG.

The Games table will hold one row for each player and his stats for that game.  All columns will need to be manually entered, except for H and AB.  The hits and at bats are two of the various computed columns.  What we have now is the raw data stored in the database.

SELECT * FROM Games;

GO

Next, need to build some views to add the other calculated fields: BA, OB, SLUG, and OPS, as well as present the data in a more readable format.  Since Hits (H) and At Bats (AB) are already computed columns, we need to use a view in order to use them in another computed column.

CREATE VIEW dbo.PlayerStats

AS

SELECT

     [SeasonId]

    ,[SeasonName]

    ,[GameNumber]

    ,[BattingOrder]

    ,[Roster]

    ,[Name]

    ,[PA]

    ,[AB]

    ,[H]

    ,[1B]

    ,[2B]

    ,[3B]

    ,[HR]

    ,[ROE]

    ,[SACf]

    ,[BB]

    ,[K]

    ,[RBI]

    ,[RUNS]

    ,CONVERT(DECIMAL(5,3),(ISNULL(([H]/NULLIF([AB],0)),0))) AS [BA]

    ,CONVERT(DECIMAL(5,3),(ISNULL((([H]+[BB])/(NULLIF([AB]+[BB]+[SACf],0))),0))) AS [OB]

    ,CONVERT(DECIMAL(5,3),(ISNULL((([1B]+([2B]*2)

        +([3B]*3)+([HR]*4))/NULLIF([AB],0)),0))) AS [SLUG]

    ,CONVERT(DECIMAL(5,3),(ISNULL((([H]+[BB])/NULLIF([AB]+[BB]+[SACf],0)),0)

        +ISNULL((([1B]+([2B]*2)+([3B]*3)+([HR]*4))/NULLIF([AB],0)),0))) AS [OPS]

FROM dbo.Games;

GO

SELECT * FROM PlayerStats;

GO

Next, we can create a view for the season stats.

CREATE VIEW dbo.SeasonStats

AS

SELECT

     [SeasonId]

    ,[SeasonName]

    ,[Roster]

    ,[Name]

    ,COUNT(GameNumber) as [Games]

    ,SUM([PA]) AS [PA]

    ,SUM([AB]) AS [AB]

    ,SUM([H]) AS [H]

    ,SUM([1B]) AS [1B]

    ,SUM([2B]) AS [2B]

    ,SUM([3B]) AS [3B]

    ,SUM([HR]) AS [HR]

    ,SUM([ROE]) AS [ROE]

    ,SUM([SACf]) AS [SACf]

    ,SUM([BB]) AS [BB]

    ,SUM([K]) AS [K]

    ,SUM([RBI]) AS [RBI]

    ,SUM([RUNS]) AS [RUNS]

    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)

        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]

    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])

        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]

FROM dbo.PlayerStats

GROUP BY [SeasonId],[SeasonName],[Roster],[Name];

GO

This will allow us to view each player’s stats for a given season.

SELECT * FROM SeasonStats

WHERE SeasonId = 12

ORDER BY [OB] DESC, [BA] DESC;

GO

In addition to season stats, we also need a view for career stats.

CREATE VIEW dbo.CareerStats

AS

SELECT

     [Roster]

    ,[Name]

    ,COUNT(GameNumber) as [Games]

    ,SUM([PA]) AS [PA]

    ,SUM([AB]) AS [AB]

    ,SUM([H]) AS [H]

    ,SUM([1B]) AS [1B]

    ,SUM([2B]) AS [2B]

    ,SUM([3B]) AS [3B]

    ,SUM([HR]) AS [HR]

    ,SUM([ROE]) AS [ROE]

    ,SUM([SACf]) AS [SACf]

    ,SUM([BB]) AS [BB]

    ,SUM([K]) AS [K]

    ,SUM([RBI]) AS [RBI]

    ,SUM([RUNS]) AS [RUNS]

    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)

        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]

    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])

        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]

FROM dbo.PlayerStats

GROUP BY [Roster],[Name];

GO

SELECT * FROM CareerStats

ORDER BY [OB] DESC, [BA] DESC;

GO

Next, we can create a view for the individual player stats.

CREATE VIEW dbo.IndividualStats

AS

SELECT

     CONVERT(VARCHAR,[SeasonId]) AS [SeasonId]

    ,[SeasonName]

    ,[Roster]

    ,[Name]

    ,COUNT(GameNumber) AS [Games]

    ,SUM([PA]) AS [PA]

    ,SUM([AB]) AS [AB]

    ,SUM([H]) AS [H]

    ,SUM([1B]) AS [1B]

    ,SUM([2B]) AS [2B]

    ,SUM([3B]) AS [3B]

    ,SUM([HR]) AS [HR]

    ,SUM([ROE]) AS [ROE]

    ,SUM([SACf]) AS [SACf]

    ,SUM([BB]) AS [BB]

    ,SUM([K]) AS [K]

    ,SUM([RBI]) AS [RBI]

    ,SUM([RUNS]) AS [RUNS]

    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)

        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]

    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])

        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]

FROM dbo.PlayerStats

GROUP BY [SeasonId],[SeasonName],[Roster],[Name];

GO

This will allow us to view the stats from an individual player as well as union all of the careers stats.

SELECT * FROM IndividualStats

WHERE Roster = 4

UNION ALL

SELECT 'CAREER STATS','',* FROM CareerStats

WHERE Roster = 4;

GO

Finally, we can create one last view for the individual player stats by season.

CREATE VIEW dbo.IndividualTeamStats

AS

SELECT

     CONVERT(varchar,[SeasonId]) AS [SeasonId]

    ,[SeasonName]

    ,[Roster]

    ,[Name]

    ,CONVERT(varchar,[GameNumber]) AS [Games]

    ,SUM([PA]) AS [PA]

    ,SUM([AB]) AS [AB]

    ,SUM([H]) AS [H]

    ,SUM([1B]) AS [1B]

    ,SUM([2B]) AS [2B]

    ,SUM([3B]) AS [3B]

    ,SUM([HR]) AS [HR]

    ,SUM([ROE]) AS [ROE]

    ,SUM([SACf]) AS [SACf]

    ,SUM([BB]) AS [BB]

    ,SUM([K]) AS [K]

    ,SUM([RBI]) AS [RBI]

    ,SUM([RUNS]) AS [RUNS]

    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]

    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)

        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]

    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))

        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])

        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]

FROM dbo.Games

GROUP BY [SeasonId],[SeasonName],[Name],[Roster],[GameNumber]

UNION

SELECT

     'SEASON STATS'

    ,[SeasonName]

    ,[Roster]

    ,[Name]

    ,''

    ,[PA]

    ,[AB]

    ,[H]

    ,[1B]

    ,[2B]

    ,[3B]

    ,[HR]

    ,[ROE]

    ,[SACf]

    ,[BB]

    ,[K]

    ,[RBI]

    ,[RUNS]

    ,[BA]

    ,[OB]

    ,[SLUG]

    ,[OPS]

FROM dbo.SeasonStats;

GO

This allows us to get a line by line view of what each player did in each game throughout the season as well as have his aggregated season stats.

SELECT * FROM IndividualStatsTeam

WHERE SeasonName = 'Spring 2012'

ORDER BY Roster;

GO

As you can see, two of the things I'm passionate about, SQL Server and softball, work very well together.  Although this was done as a hobby, I use it every single week.  What kind of hobbies can you use SQL Server for?

And yes, these are the actual stats of my team.  We play just about every Tuesday night during the spring, summer, and fall for the SportsLink league in Charlotte, NC.  We’re not the best team, but we’re also nowhere near the worst, and we’ve managed to win the league championship twice.  If you happen to be in Charlotte for the SQLPass Summit in October, maybe you can find some time of come watch my team play that Tuesday night.  I can't guarantee that it will be good or bad, but it’s definitely entertaining.