Tuesday, June 16, 2015

SQL Server v2012 - Running Totals by Date - SUM() OVER()

In a previous post, I provided an example for calculating running totals on orders traded, using a correlated subquery and the OVER() clause, which was introduced in v2005. You'll see that here: http://www.sqlfingers.com/2014/10/calculate-running-totals-in-sql-server.html

In this post I am going to show you a much sexier version of the same, using the SUM() OVER() Windows functions, introduced in v2012. Remarkably simple, take a look:

     /* to  load our data */
     DECLARE @Orders TABLE(OrderID INT,OrderDate DATETIME,OrderAmount INT)

     INSERT @Orders VALUES
          (1,'06/15/2015 09:30',20.00),
           (2,'06/15/2015 09:35',22.125),
          (3,'06/15/2015 10:01',50.00),
          (4,'06/15/2015 12:32',-10.00),
          (5,'06/15/2015 13:55',12.75),
          (6,'06/16/2015 09:30',20.00),
          (7,'06/16/2015 09:00',22.25),
          (8,'06/16/2015 10:17',-5.00),
          (9,'06/16/2015 11:44',100.23),
           (10,'06/16/2015 12:36',77.00),
          (11,'06/16/2015 13:22',20.00),
          (12,'06/16/2015 14:58',20.25);

     /* single query */
     SELECT 
          OrderDate,
          OrderAmount,
          RunningTotal = SUM(OrderAmount) OVER (
       PARTITION BY DAY(OrderDate) 
       ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) 
     FROM 
         @Orders
    ORDER BY 
         OrderID;

Here is our result-set:

















Two things to note;  First, if you look at the RunningTotal from 6/15 to 6/16, you will see the total ends at the end of one day, and begins anew the next. I did this by partitioning by DAY(OrderDate). If you want to calculate the running total from the beginning to the end of your orders, across all of your trade dates, then you would use this statement:

      SUM(OrderAmount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING)

These are the results, where you'll see the Running Total is not partitioned by date:
















Secondly, a key factor of these new Windows functions is 'framing', which lets us further define our collection using ROWS or RANGE. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which just means to calculate all previous rows up to your current row of data. Similarly, you can use ROWS in your frame, as I have in my first query. The results are the same, but the optimizer actually creates the work table in memory... and improves the performance!!

Not that big of a deal with a 12 record sample, but you can still see it. Run this query in comparison to the top one. SET STATISTICS IO on both, and you will see what I'm saying. Exactly the same results, but better performance using ROWS rather than RANGE.

     SELECT 
          OrderDate,
      OrderAmount,
          RunningTotal SUM(OrderAmount) OVER (
PARTITION BY DAY(OrderDate
ORDER BY OrderDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)  
     FROM 
          @Orders
     ORDER BY 
          OrderDate;

STATISTICS IO for ROWS:
(12 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B7361CD1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

STATISTICS IO for RANGE:
(12 row(s) affected)
Table 'Worktable'. Scan count 14, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B54DD45F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Thursday, June 11, 2015

Query Open/High/Low/Close Tick Price data from SQL Server

I got a call from one of my prop trader customers today;  They wanted to pull back the tick prices in 5 and 10 minute bars, showing the OHLC in each interval. That means returning the Open, High, Low and Close prices for each 'bar'. Time is the only consideration for this chart; volume and trading activity have no bearing. In periods of high market activity, there would be more bars than in periods of low market activity. This type of collection allows traders to analyze price movement and market volatility.  

This is an example of the logic I built for them, trimmed down only for AAPL. I have coded it for any interval they choose;  5 and 10 minute intervals, or 1, 2 or 3 minutes, etc. See here:

     -- create and load @tickdata
    DECLARE @TickData AS Table
TimeStamp DATETIME,
Price MONEY,
Symbol VARCHAR(5)
    )

    INSERT @TickData VALUES 
('2015-06-09 09:15:01',22.11,'AAPL'),
('2015-06-09 09:15:02',22.10,'AAPL'),
('2015-06-09 09:15:04',22.25,'AAPL'),
('2015-06-09 09:15:58',22.45,'AAPL'), 
('2015-06-09 09:16:03',23.12,'AAPL'), 
('2015-06-09 09:16:08',23.15,'AAPL'), 
('2015-06-09 09:17:10',22.28,'AAPL'), 
('2015-06-09 09:17:30',22.13,'AAPL'), 
('2015-06-09 09:18:59',22.15,'AAPL'),
('2015-06-09 09:18:02',20.19,'AAPL'), 
('2015-06-09 09:18:04',22.25,'AAPL'), 
('2015-06-09 09:20:58',24.14,'AAPL'), 
('2015-06-09 09:20:03',22.22,'AAPL'), 
('2015-06-09 09:21:08',22.28,'AAPL'), 
('2015-06-09 09:24:10',22.29,'AAPL'), 
('2015-06-09 09:25:30',22.13,'AAPL'), 
('2015-06-09 09:27:37',22.10,'AAPL'), 
('2015-06-09 09:27:40',23.25,'AAPL'), 
('2015-06-09 09:28:43',22.45,'AAPL'), 
('2015-06-09 09:28:50',22.22,'AAPL'), 
('2015-06-09 09:28:52',23.25,'AAPL'), 
('2015-06-09 09:29:53',22.28,'AAPL'), 
('2015-06-09 09:30:54',20.13,'AAPL'), 
('2015-06-09 09:30:56',21.11,'AAPL'), 
('2015-06-09 09:30:59',22.25,'AAPL'), 
('2015-06-09 09:31:02',22.45,'AAPL'), 
('2015-06-09 09:31:03',22.22,'AAPL'), 
('2015-06-09 09:32:07',22.25,'AAPL'), 
('2015-06-09 09:32:11',22.28,'AAPL'), 
('2015-06-09 09:34:12',22.13,'AAPL'), 
('2015-06-09 09:35:15',22.10,'AAPL'),
('2015-06-09 09:35:16',22.25,'AAPL'), 
('2015-06-09 09:36:18',22.45,'AAPL'), 
('2015-06-09 09:37:20',22.22,'AAPL'), 
('2015-06-09 09:37:25',22.25,'AAPL'), 
('2015-06-09 09:40:10',22.28,'AAPL'), 
('2015-06-09 09:40:11',22.13,'AAPL'); 

     -- pull it back out at intervals for the given @TimeInterval
     DECLARE @TimeInterval AS INT
     SET @TimeInterval= 10 -- minutes, 1 = 1 min, 5 = 5 min, etc.

     SELECT
Symbol,
DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102))) [Time],
  (SELECT PRICE FROM @TickData WHERE TimeStamp = MIN(t.TimeStamp)) [Open],
MAX(Price) [High],
MIN(Price) [Low],
(SELECT PRICE FROM @TickData WHERE TimeStamp = MAX(t.TimeStamp)) [Close]
     FROM 
@TickData t
     GROUP BY 
Symbol
DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102)));

I've run it for 2, 5 and 10 minute bars, and posted the output below for each. Take a look at that 2 minute bar; there are two spots with a larger than 2 minute jump. This is because it's coded to bring data back only for the bars where price data exists. Try it out on your own data, let me know what you think.

   2 minute bars -  
  

  5 minute bars -   










10 minute bars -  

Tuesday, June 9, 2015

SQL Server Login Properties

Just a quick post that you can use to check your SQL Server login properties.  Very helpful to keep an eye on the password policies and login state.  It's also something that you can use to see how many failed logins are occurring, and when. 

/* login properties */
SELECT
name [SQLLogin],
CASE WHEN is_policy_checked = 0 THEN 'Disabled'
                WHEN is_policy_checked = 1 THEN 'Enabled' END [EnforcePasswordPolicy],
CASE WHEN is_expiration_checked = 0 THEN 'Disabled' 
              WHEN is_expiration_checked = 1 THEN 'Enabled' END [EnforcePasswordExpiration],
CASE LOGINPROPERTY(name, 'IsLocked')
              WHEN 0 THEN 'No'
              WHEN 1 THEN 'Yes' ELSE 'Unknown' END [IsLocked],
LOGINPROPERTY(name, 'PasswordLastSetTime') [DatePasswordSet],
LOGINPROPERTY(name, 'BadPasswordCount') [FailedLogins],
LOGINPROPERTY(name, 'BadPasswordTime') [LastFailedLogin],
LOGINPROPERTY(name, 'LockoutTime') [DateLockedOut],
CASE LOGINPROPERTY(name, 'IsExpired')
             WHEN 0 THEN 'Password is not expired'
             WHEN 1 THEN 'Password is not expired, change it' ELSE 'Unknown' END [PwdExpired],
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login' ELSE 'Unknown' END [PwdChangeNextLogin],
LOGINPROPERTY(name, 'DaysUntilExpiration') [DaysUntilPwdExpires]
FROM 
sys.sql_logins
WHERE 
[name] NOT LIKE '##%'
ORDER BY 
[name]


Take a look at this reference for more detailed LOGINPROPERTY information from MSFT:   
     https://msdn.microsoft.com/en-us/library/ms345412(v=sql.110).aspx
           

Sunday, June 7, 2015

Find last modified date for SQL Server database objects

Change control is for the database objects just as much as it is for the application layer. You'd like to think that everybody has a change control process in place, but that is not always the case. There are several third party tools, such as Apex, Red Gate and Quest Software, or there is even the SQL Server Audit tool , which happens to be one of my favorites. The Audit, however, is a much larger deployment, and requires quite a bit more due diligence to put together. When I have a chance, I will put a nice overview on the Audit for your review. Until then, this is just a quick script that you can use to see when an object was changed.   
        /* find last modified date per object */
        SELECT
        name [ObjectName],
        CASE WHEN type = 'U' THEN 'Table'
           WHEN type = 'P' THEN 'Procedure'
           WHEN type IN('AF','FN','FS','FT','IF','TF') THEN 'Function'
           WHEN type = 'V' THEN 'View'
           WHEN type = 'C' THEN 'CheckConstraint'
           WHEN type = 'D' THEN 'DefaultConstraint'
           WHEN type = 'PK' THEN 'PrimaryKeyConstraint'
           WHEN type = 'F' THEN 'ForeignKeyConstraint'
           WHEN type = 'UQ' THEN 'UniqueConstraint'
           WHEN type = 'SN' THEN 'Synonym'
           WHEN type = 'TR' THEN 'Trigger' END [ObjectType],
        modify_date [Modified]
        FROM
        sys.objects
        WHERE 
        is_ms_shipped = 0  -- exclude system objects
        ORDER BY
       [Modified] DESC


I ran it against AdventureWorks2012, this is just a snapshot of the results:



Friday, June 5, 2015

Rename SQL Server Database, Table, Column, Index or Constraint

I can't count the number of times over the years that I have referenced BOL, or done a quick google, checking the syntax for my renames.  Not just constraints or indexes, but tables and columns, too.  Even databases!  In this post I am going to to give you several useful examples, compatible with all versions from v2000 to v2014. 

Side note;  In most cases you can perform these renames in the GUI, but I am a huge advocate of NOT doing renames through the GUI. The performance goes to the tubes very quickly, and you won't have near enough control over the system when these manipulations are occurring.  Don't use the GUI.  Learn and master the tSQL. You won't regret it.


    #1  Rename Database
    v2000 
    EXEC sp_renamedb @dbname = 'OldDatabaseName', @newname = 'NewDatabaseName' 

    v2005 - v2014     
    ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName

    #2  Rename Table
    EXEC sp_rename 
          @objname = 'OldTableName'
          @newname = 'NewName'
          @objtype = 'OBJECT'

    #3  Rename Index
    EXEC sp_rename 
          @objname = 'TableName.IndexName',
          @newname = 'NewName',
          @objtype = 'INDEX'

     #4  Rename Column
     EXEC sp_rename 
           @objname = 'TableName.ColumnName',
           @newname = 'NewName',
           @objtype = 'COLUMN'

     #5  Rename Constraint
     EXEC sp_rename 
           @objname = 'ConstraintName',
           @newname 'NewConstraintName',
           @objtype = 'OBJECT'
     

Remember, #4 only renames the column! It does not account for any referencing objects. For example, if you rename a table that is being called in a procedure, you will need to revise the procedure to reference the new table name. Dropping and re-creating the referencing objects is the recommended way to do this.


Monday, June 1, 2015

How to verify if SQL Server Agent is running?

I read a blog post last week with the title of my subject line. The first thing I thought was wow. You have to check manually? There are no service state alerts in place? But then thinking back, I can remember a few places without formal monitoring solutions in place. In one case, during my 1st week at a new gig, I learned it only after someone else let me know that a service was down. Not good.

There are numerous apps available for monitoring SQL Server, or other Windows services. I've worked with Nagios, Zabbix and several others, but I've also always tried to have something set up on the SQL side of things. That's all this post is. Just a quick script you can use to check the state of the SQL Server Agent, and alert if it is not running.

         SET NOCOUNT ON;

         /*  
           First we load the service name and state into a local variable. 
           Then we check the status, and email and alert if service state <> 'Running'.    */

         -- local table variable to hold our results
         DECLARE @Services TABLE (
        ServerName VARCHAR(50),
       ServiceName VARCHAR(50),
           ServiceState VARCHAR(10)
)

        -- check the SQL Server Agent service
        INSERT @Services (ServiceState)  
        EXEC master..xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'
        UPDATE @Services 
        SET ServerName = @@SERVERNAME,
        ServiceName = 'SQLServerAgent'
  
        -- Check for status <> Running, send email if exists
        IF EXISTS(
        SELECT 1 FROM @Services
        WHERE ServiceState <> 'Running.')
        BEGIN
                EXEC msdb..sp_send_dbmail 
                      @profile_name = 'YourDatabaseMailProfile',
                      @recipients = 'YourDBATeam@domain.com',
                      @subject = 'SQLServerAgent_Service_Alert',
                      @body = 'The SQL Server Agent service is not running. Please review.',
                      @importance = 'High'
        END
        ELSE
                RETURN;

        SET NOCOUNT OFF;

That is just one example targeting the SQL Server Agent. You can very easily change it to check all of SQL Server's services, alerting if any of them are found not to be running. Here's a quick read for you on the other service names, such as the Browser, SSIS or the SQL Server itself.  

Oh yes, you also need to remember, xp_servicecontrol is an undocumented extended stored procedure. Therefore, it is unsupported by MSFT. It could be pulled from the next release, or changed at anytime... without any type of notification. You need to be very wary about using anything like this in production.