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

Daily Coping Tip

Send a positive message to a friend who needs encouragement

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.

Better Communication of Information

Many of us work with data in some way that helps a customer better understand data, use it to make a decision, or support a some conclusion. The way we present data (or help others present data) can impact how well a consumer understands the information represented by the data. There are people whose job it is to design visualizations and ensure the final product communicates well, but often I find a developer or DBA is the person that gets asked for help.

What do we recommend when looking at a set of data and deciding how to format it? A few posts I saw recently help with decisions (when should I use a map) or basic knowledge (what is a square chart). I've seen presentations that help me understand the impact of my decisions and how to better design my informative reports for more people. In fact, I hope more people produce content on how and why they have built reports. It's helpful for many of us that would like to do a better job.

The other side of this is actually producing some report and then getting feedback on it. I've been working on a report for the kids I coach and need to produce some content on the way I've built it and use it. I certainly could use some feedback and I know someone will have suggestions on how to better structure a report, color it, choose a different style, or something else.

I might even get some better feedback on how I chose to store the data. That certainly isn't a set or known thing, and I would guess that there are some things I haven't thought about in building a structure.

Ultimately there is information to be gleaned from the data we capture, store, and manage. While it might not be our job to help convey that information, we are a part of the process and the more we know about it, the more we might do our jobs better, or help others do theirs better. I do hope that I see more posts on not only how to query information and solve problems but how to better present the data from those queries.

I'm looking forward to some blog posts, articles, or maybe even someone hosting a T-SQL Tuesday on this topic.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Power BI Report Builder Expressions - Build In Fields and Functions

Daniel Calbimonte from SQLServerCentral

This article continues looking at various expressions that are available in the Report Builder. We cover some build in functions as well as various logical operators.

External Article

10 Benefits of Database Monitoring for Business Leaders [Video]

Additional Articles from Redgate

A third-party database monitoring tool is an investment that drives enormous value for the bottom line of your business in ten key ways. Here's how a tool like Redgate SQL Monitor can save you money, time and assist with security and compliance concerns.

External Article

Insights from the SSRS database

Additional Articles from SimpleTalk

The SSRS database contains a wealth of undocumented information that can help administrators. In this article, Edward Pollack explains how to get to the data and ways to use it.

Blog Post

From the SQL Server Central Blogs - Why You Should Run Your SQL Server Virtual Machines in Azure

Tracy Boggiano from Database Superhero’s Blog

SQL Server can run in any Virtual Machine that has the appropriate operating system (yes, I said that because I have some hope, that we will one day learn...

Blog Post

From the SQL Server Central Blogs - Microsoft Build event announcements

James Serra from James Serra's Blog

There were a number of data platform announcements at Microsoft Build yesterday that I wanted to blog about. Everything announced at Build can be found in the Microsoft Build 2022...

 

 Question of the Day

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

 

Creating a Columnstore Index

What is wrong with this CREATE INDEX statement? 
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"  
    ON Production.BillOfMaterials (ComponentID ASC, StartDate)  
    WHERE EndDate IS NOT NULL;

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 First LEAD

I have a query that uses LAG to calculate a sales difference. There is a part of the code that is like this:

LAG(saletotal) OVER (PARTITION BY saledate ORDER BY saledate) - SUM(saletotal) OVER (PARTITION BY saledate ORDER BY saledate) AS hrchange

However, the first row shows a null for this query. What should I do to make this a zero when the LAG value doesn't exist?

Answer: add a third parameter to LAG that is a 0

Explanation: LAG accounts for this and has a third parameter, which is a default when here is no row. Use this third parameter to handle the case. Ref: LAG() - https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-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 - Development
How to avoid using operator when there are different values on two tables ? - I work on sql server 2017 I need to get different of feature value based on partid and feature name I search for alternative way to compare different values based on partid and feature name without using <> not equal operator because i face slow when using <> not equal so what i do so […]
Error: excel automation Open File does not work in SQL 2017 - This code works on other 2005 SQL Sever but my 2017 SQL Server gives error. /* Excel Automation*/ declare @xlApp integer, @rs integer Declare @FileName varchar(500) declare @xlWorkbooks integer declare @xlWorkBook integer declare @xlWorkSHEET integer declare @xlCell integer declare @xlLastRow INTEGER DECLARE @sql VARCHAR(4000) execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT select @rs, @xlapp execute […]
SQL Server 2016 - Administration
How to confirm if the replication is using Push/Pull subscription? - Hello, On an existing configured replication setup, how to confirm if it's using a Push or Pull subscription? Basically working on setting up a similar replication in a new server, but am unable to find the details of the existing legacy server's configuration!
SQL Server 2016 - Development and T-SQL
Index FillFactor - Is there any measurement(query) that can be used to check for correct value used. I have a table that has about 9 million records with 2 additional indexes other table the PK. One of the additional indexes has a fill factor of 0 actualfillfactor of 98 currentfragmentation of 2.0 fragment_count 2600 and avgfrag of 46. […]
Administration - SQL Server 2014
SQL database practices - Hello friends, I am a casual learner and right now exploring the DBA field (I am a database developer). Now I recently had a discussion with my group of friends and we discussed about a problem where, in a dB server, there are some 8-10 databases for multiple projects. One such database is having multiple […]
SQL 2012 - General
SQL server question - Hi, I am just starting learning SQL.. I need help: I want to create a check constraint for a column in Table1 so that it is smaller than another column value in another table? I tried join but it is not working!
Approach to recreate view when new column is added to a table - In my application, we want to give users the option to create new fields in a table in addition to application's out-of-box functionality. Data for the Out-of-box functionality is accessed through SQL views from a C# application. If new fields are to be a part of this accessed data then I will have to recreate […]
Show total for every unique Id - Hello Everyone, In my select query with aggregate function I want to show total amount for every location. I wrote the query like below SELECT TLD.location_id, TP.Location, ISNULL(SUM(CAST(TLD.land_rate AS DECIMAL)),0) AS TotalRate --SUM(CAST(TLD.land_area AS DECIMAL)) As TotalArea FROM tblLandDetails TLD LEFT JOIN tbl_Projects TP ON TP.Sno = TLD.location_id WHERE TP.STATUS = 1 AND TLD.lease_from >= […]
SQL Server 2019 - Administration
How can I generate a key for this column which is part of a database model? - I am normalizing a larger table. Here is a screenshot showing part of the relationship of the normalized table: I'm using MS SQL Server and SSMS. The plan was to create the empty tables then insert values from the denormalized table. I started doing so until I realised that I hadn't generated 'Type codes' in […]
Are the data types I've chosen the best choices? - A newbie here. I have these columns, here are the samples and the data types I've chosen: I'd just like to ask if the choices I've made for the data types are the best choices?. Thank you in advance
Finding when a SQL Failover Cluster Instance failed over.... - I have a couple SQL 2019 FCI. Is there a way to find when a SQL Failover Cluster Instance failed over or if someone moved it from one node to another? I'd like to get the date, time and who did the move. Or is this kind of info only in the WSFC data?
SQL DB Restore showing 0 % - Hi, Initiated 2.4 TB restoration freshly in the sql instance using GUI.. From 2 hours it is showing as 0 %. In SQL_Data files drive occupied with 2.4 tb. But when i open the drive inside everything empty. I feel the data allocation is taking time. But I am confused whether restoration is in progress […]
SQL Server 2019 - Development
Parallel Execution of SQL in ADO.Net on C# - SQL Parameter is already contained - Hi there Im not sure if this should be asked on a C# forum, but im taking a shot here. We have a C# Application which using ADO.Net , executes SQL Stored procedures. In  fact the application calles SQL stored procedures in Parallel. The issue we have is trying to achieve Parallel execution in c# […]
SSMS Connect to Server name with hyphens? - This is a question about the Connect to Server dialog in SSMS, the one that you use to connect to a server. I never noticed this before nor have I seen anyone talk about this, and it is kind of freaking me out. We have a server with hyphens in the name. When I enter […]
Azure Data Factory
Good books for Azure (Data Factory) / Best practices - I was a BI/DWH developer for over 15 years in typical data warehouse environments. I used ETL tools like Infomatica, Pentaho, SAS and some PHP/MySQL in my early days. Recently I switched Jobs and now I m heading more in the Data Engineering direction. Less business logic and more focus on acquiring data from lots […]
 

 

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

 

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