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

Daily Coping Tip

Show your appreciation to those who are helping others

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

 

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Missing Data

I gather some data regularly about my life. I examine and look for trends and anomalies every week, and use the data in data analysis to help me make decisions. I get this data from my watch, a Garmin Forerunner 645, which tracks a number of data points for me. Some automatically, some I set, but I have come to appreciate that information.

Recently I had to get a replacement watch after a bit of a hardware failure. Huge props to Garmin for replacing my out-warranty, 3 year old watch at no cost. I was without a watch for almost two weeks, and I really noticed the lack of data.

My watch gathers data on sleep, which I often check to see when I've struggled at night and wake up tired. Where was my time spent? I don't need a high level of accuracy, but something that helps me to think about what I might have done differently the previous day, with diet, with stress, with exercise (or lack) or even emotional coping. I often check the steps, just to see if I've taken breaks and gotten moving, or am I sitting too much. I look at my heart rate, both for the long term trends and the specific exercise to see if I'm trying hard enough.

Losing some of this data isn't really a big deal, and it's something we need to account for, but this is a pretty big hole in my system. As I look for trends and compute averages, I need to account for this. If I were building a report for someone, I need to ensure the missing dates are still shown, albeit with no data. I can't enter zeros here, because imagine 2 weeks worth of zeros in a month or resting heart rate data. I might think I'm much more fit than I am!

There are plenty of solutions and ways to handle both aggregations and visualizations, but as data professionals, we need to ensure we know how and we do account for missing data points. This becomes especially important with sensor data, but even sales data can be affected. We can, and do lose data for a variety of reasons, and we should also be prepared for those situations.

Steve Jones - SSC Editor

#BlackLivesMatter

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

 
  Featured Contents
Stairway to SSAS Tabular

Stairway to SSAS Tabular Level 10: Multiple Dates in Fact Table

Thomas LeBlanc from SQLServerCentral

In this level of the stairway, learn about multiple dates in fact table

Right-Sizing Row Mode Query Memory Requirements

Additional Articles from SimpleTalk

When the optimizer doesn’t estimate the correct amount of memory for a query, either memory is wasted that could be used for other processes or some operations will spill to disk. Microsoft has added Memory Grant Feedback to help overcome this issue. In this article, Greg Larsen explains what you need to know about this new feature.

Getting Started with Data Masker for SQL Server: I Want to Mask A Database

Additional Articles from Redgate

Grant Fritchey explains what's involved in masking a SQL Server database. It can seem a daunting task, but it all becomes a lot more logical if you start from a plan, based on agreed data classifications, and then use a tool like Data Masker to implement the masking, and track progress.

SQL Server Transaction Log Management eBook Download

Free eBook: SQL Server Transaction Log Management

Press Release from Redgate

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works.

From the SQL Server Central Blogs - Database Fundamentals #25: Referential Integrity

Grant Fritchey from The Scary DBA

If you have been reading through all my fundamentals posts and following along, you have built a small sample database, loaded it with data, and learned how to retrieve...

From the SQL Server Central Blogs - Updated Default Settings for Azure SQL Managed Instance Databases

Tim Radney from Tim Radney - Database Professional

A common saying about Azure is that it is always changing. This is a very accurate statement, want proof, just take a look at Microsoft Build 2020. Microsoft is...

 

  Question of the Day

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

 

Column Mean in R

I have loaded a dataframe in R with this code:
> sales <- read.csv2(file="d:\\downloads\\sales_data.csv", sep=",")
There are a number of columns in here, one of which is "Profit". I want to get the mean value of this column. What code does this?

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 Order of Operations

What is the result from this code?

SELECT -100.0/-100.0*10.0

Answer: 0.1

Explanation: The answer is actually 0.1. The negative operator is lower precedence than the multiplication/division, so this actually executes like:

SELECT -(100.0/-(100.0*10.0))

Crazy, but a good explanation from Ken Fisher. Ref: Operator Precedence - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15

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
SQL 2017 AOAG setup on Standalone servers - I've been trying to setup AlwaysON AG on two standalone default sql instances (SQL 2017 Enterprise Edition - 14.0.1000.169). I've checked, port numbers (1433 and 5022) on both the servers are open and there's no issue with DNS. (Hostname is not more than 15 characters.) Can anyone help me with this error please. Msg 47106, […]
SQL replication many publishers to 1 subscriber - Hello, I am wondering, have 2 questions in regards to transaction replication... probably best to ask my question with using an example, so I have 4 servers, Server A, B, C, D, and the SQL servers A, B, and C, will be publishers, and Server D I would like to be the distributor and Subscriber, […]
SQL Server 2016 - Administration
SP performance tuning, any hints. - How to make the below code faster, does it help by changing updlock to nolock help, or please share if you see anything which can be improved syntax wise, etc? ------------------------------------------------------------------------------------------------- CREATE PROCEDURE [dbo].spTest(@DateToProcess DateTime, @ForceTotalRecalc bit = 0) AS SET XACT_ABORT ON SET NOCOUNT ON DECLARE EffectiveDate DATE DECLARE @LastDateProcessed DATE DECLARE @ThisDate DATE […]
Who changed the SQL memory - is there a way to know who changed the SQL memory and when
SQL Server 2016 - Development and T-SQL
UTC time to EST time - Hi, The 'TIME_UTC' column is UTC timing. I would like to generate the report for EST timings . But below query gives UTC timings for the last week.I would like to convert it to EST timing. Any suggestions pls. select TIME_UTC from table_name where TIME_UTC BETWEEN cast(GETDATE()-8 as date) AND cast(GETDATE()-1 as date)   Thanks.
performance tuning for a subquery - Hey Guys I have a quick questions regarding a better way to write a query: e2 is a bigger table . I have two columns here CNTRY_ISSUE_NAME and CNTRY_ISSUE_ISO  here. I would like to know if there is a better way to write that can give a performance boost to this query. These are just […]
SQL 2012 - General
How to generating Excel File with multi sheet by using SQL server 2012? - I need to create stored procedure or query or any thing on SQL server 2012 take data table from c# code and create Excel file with multi sheet based on data Exist on data table . From data below as Example I have Function GetDate return data table . Create File ABC.xlsx with two sheet […]
SQL Server 2012 - T-SQL
Sql server index - Hi Experts , I have a job which calls 100 of stored procedure. I have identified the tables which were used in the process and rebuild the fragmented index having average_fragmentation_percentage>25. But the performance of the query degraded after index rebuild operation . Can you guys tell me the possible reasons why this happened. Thanks […]
Regex question - A developer asked me to run a query based on the following regex: ^[A-Z]{4}[0-9]{4} Which means that he wanted any row where the column in question did not have 4 alpha characters, followed by 4 numeric characters. I last played with regex about 6 years ago, so hunted around and found nothing suitable for T-SQL. […]
how to check which tsql make increasing the log file size - Hello, We set database log file size to 10GB, however database log file size is increased to 30 GB within one day (weekend day). So we would like to see which user or tsql is making increase log file size to very fast. Is it possible to see which transaction or sql statement is increasing […]
SQL Server 2019 - Administration
SET DEADLOCK PRIORITY LOW question - Hey all, Session 1 = entity framework, as of not can't change anything about it Session 2 = stored proc, can control it Session 1 and 2 are deadlocking. I want 1 to be the deadlock victim. If I set 2 to have a deadlock priority LOW, will this cause 1 to be the deadlock […]
SQL 2017 AOAG setup on Standalone servers - I've been trying to setup AlwaysON AG on two standalone default sql instances (SQL 2017 Enterprise Edition - 14.0.1000.169). I've checked, port numbers (1433 and 5022) on both the servers are open and there's no issue with DNS. (Hostname is not more than 15 characters.) Can anyone help me with this error please. Msg 47106, […]
Attach and Detach Method vs SQL Manage object method - Hi there, if you want to detach a database you would right click on the DB->Tasks->Detach What are the motivations behind doing this? I suspect if you want to make edits to an existing database and don't want to alter the production environment. What's the difference between attaching (right click on databases -> attach) and […]
Analysis Services
Translate Dimension Values (multidimensional) - Hello, is there a method to translate the values of a dimension? Example: dimension called diabetic with an IS_DIABETIC column and two values YES and NO I would like to do a translation in French of the values 'OUI' and 'NON' instead of YES / NO THANKS
COVID-19 Pandemic
Daily Coping 8 Jun 2020 - Today’s tip is to re-frame a worry and try to find a positive way to respond. http://voiceofthedba.com/2020/06/08/daily-coping-8-june-2020/
 

 

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

 

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