Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Double Check Your Math

Performing calculations in our applications is important. In fact, if we do any sort of math, we really want to ensure that our results are what we expect. That seems obvious, but I've run into applications where the code deployed didn't quite calculate things as expected. In fact, there are all sorts of cases where someone was using a type of rounding that didn't work well. You can read about a number of stories in this article, some of which were very expensive.

While much of the work of calculations is done in application software, there are powerful capabilities in a database platform that are useful, especially when working with more than a row of data. It seems that even when developers want to treat an RDBMS as a storage location, they still will manipulate data with SQL functions to get results from a query that are easier to work with in their application.

Some of us might use FLOOR(), CEILING() , ROUND(), or other mathematical functions in our work. We should be sure this is what the actual specification calls for, and that we are performing the calculations correctly. After all, mathematics in computers are often not as precise as we'd like them to be, or perhaps, not as we'd expect them to be.

This is one reason I think you should have database testing. Clients have had the wrong calculations in production, sometimes for years. When we make switches based on values, or we perform a calculation designed to somehow round or trim a number, we may do so incorrectly. I've seen incorrect discount rates, tax calculations, and more in production systems.

Even if you don't want to write tests for most of your database query code, you ought to at least ensure that any math calculations have some testing around them that documents the expected behavior with easy to use numbers. This might not prevent mistakes, but it does give you a way to explain what you expect to happen in the code, verify it works, and show the input and output to a client. Perhaps their set of eyes on your test and test data will prevent silly mistakes in your math algorithms.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
  Featured Contents

Querying database schema using graph tables

Diligentdba 46159 from SQLServerCentral

SQL Server 2017 introduced the concept of graph data tables as part of the SQL Server database engine. With SQL Server 2019, there were some enhancements like ‘shortest path’ function and constraints on edge tables that make this feature more usable – although it is far from a full-fledged graph database. Graph tables are essentially […]

SQL Prompt by Keyboard

Additional Articles from SQLServerCentral

Phil Factor shares a handy keyboard shortcut diagram that reveals some of the most useful of Prompt's keyboard shortcuts, and a wallchart showing every piece of Prompt functionality, what it does, where to find it in the various menus, and of course its keyboard shortcut.

How to Restore Model Database in SQL Server

Additional Articles from MSSQLTips.com

In this tip we look at how to restore a missing or corrupt SQL Server model database.

From the SQL Server Central Blogs - T-SQL Tuesday #121: Gifts received for this year

taboggiano@gmail.com from Database Superhero’s Blog

At the end of 2019, Mala (b|t) invites us to write about the gifts we’ve gotten during the year.  I’ve been gifted with a lot of things this year personally...

From the SQL Server Central Blogs - A gift. T-SQL Tuesday #121

Kenneth.Fisher from SQLStudies

My good friend Malathi Mahadevan (blog|twitter) is hosting T-SQL Tuesday this month and wants us to talk about Gifts received ... Continue reading

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Finding SQL Logins

There is a DMV in the master database, called sys.sql_logins. This inherits data from another DMV. Which one?

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

The PowerShell String

I have this code:

$name = 'Steve'
write-host( "My name is $name")

What is output from the write-host() statement?

Answer: My name is Steve

Explanation: This is a variable expansion in PoSh, and will return the value of the variable, so you get "My name is Steve". Powershell results Ref: Variable Expansion in Strings - https://devblogs.microsoft.com/powershell/variable-expansion-in-strings-and-here-strings/

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
Composite non-clustered index: will it still work if I use only one index col) ? - If I have an index  called idxT1_C1C2C3  on table T1 that is non-clustered non-unique non covering,  has 3 colums C1, C2, C3 in same order, and I run a query SELECT    FROM T1 WHERE C1 = 'value' can such query be expected at all to still use the idxT1_C1C2C3  index? Or […]
SQL Server 2016 - Administration
Why columnstore indexes are not very popular? - I noticed that many SQL Server shops do not even consider or discuss introducing columnstore indexes or in-memory columnstore indexes. While trying to solve performance issues with many queries in many other painful ways, this option is not even on the plate of DBAs and Developers and product owners agenda in many today's IT shops […]
SQL Server Agent replacement - Friends, I have a server, inherited, SQL Server with more than 150 jobs using ETLs, direct invokations for SPs, etc. This server, only job  is executing those jobs, because there are no user databases associated. I am just thinking, if could be a better way to program those tasks or my only option is to […]
SQL Server Pivot Table on two columns renaming one column - I've the following source table: --------------------------------------------------------------- | Id | GroupName | RuleName | RuleText | RuleValue | +-------------------------------------------------------------+ | 1 | Group1 | Exclude1 | Excluded (Reason1) | 1 | +-------------------------------------------------------------+ | 1 | Group1 | Exclude2 | Excluded (Reason2) | 1 | +-------------------------------------------------------------+ | 1 | Group1 | Exclude3 | Excluded (Reason3) | 1 […]
SSRS Email Log review - Hi gang, I need some guidance: I have a SQL server 2016 standard instance set up with SSRS on a remote server environment (running NetForum) I have been tasked with helping the report team allow scheduled reports to get sent via email. Seems simple, right? We are using Office 365. I set up an email […]
can I use Stretch database to migrate the database to azure. - HI Guys,   can I use Stretch database to migrate the database to azure.? how ?
Performance difference - I have a user sql from 3rd party started running terrible on prod server.  has 128 gig ram, 4 procs plenty of disk space.  It basically runs forever and buries tempdb (yes it is a cte and it is ugly).  take a backup of prod drop on uat server, 2 procs 16 gig of ram, […]
List of INDEX with create and DROP script should not include any constrains P/F - Hello Sir.. Please help me the script.. From entire database i want List of INDEX with CREATE and DROP INDEX script, But in this should not include any constrains like primary keys and Foreign keys. Please help any one have the script like this? Thank you..
SQL Server 2016 - Development and T-SQL
Pivot Query - /* I'm trying to write a query that uses a list of dates as a header and lists, lets say, employees and if they were in training on that day. I've made a start, but I can't get a pivot working Note: This, by necessity, is a made up scenario similar to what I'm trying […]
SQL 2012 - General
Uninstall SQL without uninstalling SSRS - We have a non-prod server that (long ago) we installed SQL Server on and we were only supposed to install the SSRS components. Long story short, corporate wants us to remove components we are not using due to security concerns. Nevermind that we've disabled the services, there are concerns. I'm not sure there's a good […]
SQL Server 2008 - General
weird sql problem. - Hi all, I´m not sure what I´m doing bad with this query: Situation: I need to select all the purchase order lines(poitem_all) that not exists in the table of requisition lines (preqlines). select po_num from poitem_all results with all the  purchase orders (po_num) for the table poitem_all. Ok. Then for select only that not exists […]
T-SQL (SS2K8)
plantation shutter exterior or interior grade - Here is a recent inquiry we received about an oval window, and our response with the customer: Customer Request– Hi there. I have an oval bathroom window for which I’m looking for Plantation shutters the hieght is 30? and the width is 40-1/2? I’m not sure if I prefer the fan -like, or horizontal louvre […]
is there any better way to export SQL server data into csv file? - I have  questions of exporting SQL server data, the details as below, thanks! how to use BCP export data from SQL server (table or view or stored procedures) into a csv file (supposing the file path is d:\temp) ? is there any better way to fast export SQL server data into csv file ?
Reporting Services
SSRS log error - We have a SQL server reporting server installed both database engine and SSRS service 2017 on it. The database only hosts the reporting database. I found a lot of repeating errors in the file: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles: 2019-12-10 15:00:04.0060|WARN|5|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file 'D:\Program Files\Microsoft SQL […]
ssrs 2012 column headers repeat on each page while the user is running the rpt - My issue I want to have column headers repeat on each page while the user is running (accessing) the ssrs report. I just started to work with ssrs 2012 reports. Recently I have workd with ssrs 2010 and ssrs 2008 reports. In the past when working with ssrs 2010 reports, I would select the 'advanced […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -