SQL Server Error Msg: Divide by zero error encountered.

I was reviewing my posts today and recognized that I don't have near enough SQL Server error messages w/resolutions up here.  My bad.  I am going to begin building a reference for some of the more common (and more frustrating) errors that I'm sure you've all seen.  

First up is the Divide by Zero error.  This error is caused by performing a division where the denominator is a zero.  Here's a very quick example:

    -- sample data in @numbers 

    DECLARE @numbers TABLE (Numerator INT, Denominator INT)
    INSERT @numbers (Numerator,Denominator)
    VALUES (100,10),
               (20,4),
             (36,9),
             (22,0);  -- note the 0 denominator

    -- perform your division
    SELECT 
         Numerator / Denominator [Result]
    FROM
        @numbers

You'll receive this error:

    (4 row(s) affected)
    Msg 8134, Level 16, State 1, Line 8
    Divide by zero error encountered.

We can workaround the problem using this CASE statement.

    -- workaround CASE
    SELECT
        CASE WHEN Denominator = 0 THEN 0 ELSE Numerator / Denominator END [Result]
    FROM
        @numbers

No more error.  This is your result, where that 0 is now returned without the error:

    Result
      10
      5
      4
      0

We can also use NULLIF for the workaround, rather than the CASE statement, like this:

    -- workaround NULLIF
    SELECT 
         Numerator / NULLIF(Denominator,0) [Result]
    FROM 
          @numbers

Slightly different return:

    Result
      10
      5
      4
      NULL

NULLIF returns a NULL value if the two specified expressions are equal. More details here:     https://msdn.microsoft.com/en-us/library/ms177562.aspx

I'll be back with more SQL Server Error messages soon.  Until then, feel free to post any errors you're struggling with, and I'll help you out.

What port is SQL Server listening on?

I was going through some of my older posts today, and I found a response to one that I had never seen before.  You'll see it at the bottom of this page:

    http://www.sqlfingers.com/2011/05/tcp-port-ip-address.html

My post is just a quick tip for finding the port that SQL Server is listening on, using xp_regread. As my reader says, xp_regread is undocumented and unsupported... I have used the xp's many times over the years, but the point is valid. They're unsupported and could even be gone in the next build.  If you can work around them, you probably should.

And I have! One of my favorite DMVs is sys.dm_exec_connections. You can use it to monitor your active connections and all kinds of fun stuff, as I've sampled here. In this particular case, we can use sys.dm_exec_connections to return the port number that SQL Server is listening on. 

       SELECT local_tcp_port
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

Your output will resemble this:

Super easy, documented... and no longer unsupported.  :-) 

Take a look at both of these for more details regarding the DMVs:
    https://msdn.microsoft.com/en-us/library/ms181509(v=sql.110).aspx
    https://msdn.microsoft.com/en-us/library/ms188754.aspx

Use SERVERPROPERTY to list the SQL Server Instance Name

I received an email from one of my customers this morning -- " How do I get the name of the SQL instance and what version it is, with tSQL? "  I love the easy ones! :-)
This is a quick post of one of the examples I gave to them, using SERVERPROPERTY.  

         SELECT 

                 SERVERPROPERTY('MachineName') [HostName], 
                SERVERPROPERTY('ServerName') [SQLServerName], 
                 CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'DEFAULT' 
                     ELSE SERVERPROPERTY('InstanceName') END [InstanceName], 
                 LEFT(@@Version, CHARINDEX('-', @@version) - 2) [ReleaseName],
                 SERVERPROPERTY('Edition') [Edition],
                 SERVERPROPERTY('ProductVersion') [ProductVersion]

See the CASE on 'InstanceName' -- I'm just doing that to return either the name of the named instance, or 'DEFAULT', if it is not a named instance.

These are the results from one of my v2012 instances:

Please also take a look at MSDN for more details on SERVERPROPERTY:
    https://msdn.microsoft.com/en-us/library/ms174396(v=sql.110).aspx

SQL Server - Query all non-default configurations

If you're a SQL Server DBA, then you know sp_configure. If not, well... it's time to learn. sp_configure is a system stored procedure which allows you to view or change SQL Server configuration settings. Min/max memory, backup compression, fill factor (%), etc. There are many different opinions on which configurations should be changed to what, but that's not what this post is about. I just want to show you how to quickly list all non-default SQL Server configuration settings. Why? Well, maybe you just inherited a server, or maybe you're troubleshooting a problem and want to see if any configurations could be contributing -- or any that could help! This returns ONLY the non-defaults. Much easier than running 'EXEC sp_configure' and then going through all configurations...

      /* Query SQL Server for any non-default configuration settings.    */
      SET NOCOUNT ON;

-- declarations
DECLARE
@version VARCHAR(128),
@charindex BIGINT,
@majversion VARCHAR(MAX),
@temp SQL_VARIANT;

-- set local variables
SELECT @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128));
SELECT @charindex = CHARINDEX('.', @version);
SET @majversion = SUBSTRING(@version, 1, @charindex-1);
IF @majversion IN (9,10)
       SET @temp = 20  ELSE SET @temp = 10

-- load all defaults into table variable
DECLARE @defaults TABLE (
id int IDENTITY(1,1),
config VARCHAR(128),
value SQL_VARIANT
);

INSERT @defaults VALUES
('access check cache bucket count', 0),
('access check cache quota', 0),
('Ad Hoc Distributed Queries', 0),
('affinity I/O mask', 0),
('affinity64 I/O mask', 0),
('affinity mask', 0),
('affinity64 mask', 0),
('Agent XPs',0),
('allow updates', 0),
('backup compression default', 0),
('blocked process threshold', 0),
('c2 audit mode', 0),
('clr enabled', 0),
('common criteria compliance enabled', 0),
('contained database authentication', 0),
('cost threshold for parallelism', 5),
('cross db ownership chaining', 0),
('cursor threshold', -1),
('Database Mail XPs', 0),
('default full-text language', 1033),
('default language', 0),
('default trace enabled', 1),
('disallow results from triggers', 0),
('EKM provider enabled', 0),
('filestream_access_level', 0),
('fill factor(%)', 0),
('ft crawl bandwidth (max)', 100),
('ft crawl bandwidth (min)', 0),
('ft notify bandwidth (max)', 100),
('ft notify bandwidth (min)', 0),
('index create memory(KB)', 0),
('in-doubt xact resolution', 0),
('lightweight pooling', 0),
('locks', 0),
('max degree of parallelism', 0),
('max full-text crawl range', 4),
('max server memory(MB)', 2147483647),
('max text repl size(B)', 65536),
('max worker threads', 0),
('media retention', 0),
('min memory per query(KB)', 1024),
('min server memory(MB)', 0),
('nested triggers', 1),
('network packet size(B)', 4096),
('Ole Automation Procedures', 0),
('open objects', 0),
('optimize for ad hoc workloads', 0),
('PH timeout(s)', 60),
('precompute rank', 0),
('priority boost', 0),
('query governor cost limit', 0),
('query wait(s)', -1),
('recovery interval(min)', 0),
('remote access', 1),
('remote admin connections', 0),
('remote login timeout(s)', @temp),
('remote proc trans', 0),
('remote query timeout(s)', 600),
('Replication XPs', 0),
('scan for startup procs', 0),
('server trigger recursion', 1),
('set working set size', 0),
('show advanced options', 0),
('SMO and DMO XPs', 1),
('transform noise words', 0),
('two digit year cutoff', 2049),
('user connections', 0),
('user options', 0),
('xp_cmdshell', 0);

-- now pull back which ones you're using that aren't defaults
SELECT
s.name [Configuration],
s.value_in_use [Yours],
d.Value [Default]
FROM
@defaults d JOIN sys.configurations s
 ON s.name LIKE '%' + d.config + '%'
 AND d.Value <> s.value_in_use
WHERE
s.name <> 'show advanced options'
ORDER BY
s.name

These are the results from my own instance:

That's about it.  Now there are many different opinions about which configs should be changed, and to what. I've got my changes that I make on every server I manage, and then there are other changes that I make, for different needs. Then, of course, there are countless other recommendations from the other SQL experts out there. These are two of my favorites:  

www.brentozar.com - Five SQL Server Settings to Change
http://sqlblog.com  - Did You Know? What settings to always change

I would also be sure to take a look at this one for more detail on sp_configure:

     https://msdn.microsoft.com/en-us/library/ms188787.aspx

Query SQL Server Mirror details -- sys.database_mirroring

Just one more quick post today;  As I mentioned in my last post, I am preparing to perform a rolling upgrade for one of my customer's high performance mirror sessions. Well, as I'm covering all of the bases, I wanted to check sys.database_mirroring, just to be sure the mirror details are as I expect them to be.

Here is a quick query that you can use for exactly that. It will tell you which of your databases are mirrored; and it will provide the mirror state and safety, along with the mirroring role and mirroring partner.  Take a look, let me know what you think.

   SELECT

      DB_NAME(database_id) [Database],

      CASE WHEN mirroring_guid IS NOT NULL THEN 'Mirroring is On' 

        ELSE 'Mirroring is not configured' END [IsMirrored],

      mirroring_state_desc [MirrorState],

      CASE WHEN mirroring_safety_level = 1 THEN 'High Performance' 

           WHEN mirroring_safety_level=2 THEN 'High Safety'  

              ELSE NULL END [MirrorSafety],

      mirroring_role_desc [MirrorRole],

      mirroring_partner_instance [MirrorPartner]

   FROM 

      sys.database_mirroring

These are the results from my customer's server, where that black smudge is the name of their Mirror SQL Server:

Pretty handy, I'd say. Take a look at this for more details on sys.database_mirroring:
     https://msdn.microsoft.com/en-us/library/ms178655(v=sql.110).aspx

Query to list all SQL Server instances installed on a server

I am preparing an upgrade for one of my customers. It's a rolling upgrade of a v2008 mirror session, to v2012. This particular customer has a tendency of installing new instances without telling me, so I wanted to quickly check all instances on the server that I would be upgrading.

I know it's in the registry, at this location:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

But I'm always trying to query details like this from the engine... I am sure there are things that I cannot query from the system catalogs, but this isn't one of them. :-)  Check it out, this is MUCH quicker than regedit!

        DECLARE @instances TABLE ( 

             value CHAR(100),

              instances CHAR(100),

             data CHAR(100)

)

INSERT @instances (value,instances,data)

EXECUTE xp_regread

            @rootkey = 'HKEY_LOCAL_MACHINE',

            @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',

            @value_name = 'InstalledInstances'

/* look at your data */

SELECT

instances [InstanceNames]

FROM

@instances 

These are the instance names from my own server:

Pretty cool.  

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.

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 -