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

Learning from Exercise

A long time ago I decided to run every day. I did for a bit, in fact, I had a neat milestone along the way and continued on for 1564 days. That was quite a time period in my life, and one that I look back fondly upon. I've aged a bit, and while I could do it again, I also have decided there are other things that matter more to me, so I have moved on in life.  I learned a lot from that time, lessons about myself and life that continue to help me today. Some of them even apply to work and my career, little nuggets of wisdom that help me find ways to find success in my life.

Apart from some better fitness, one of the main benefits for me was the sense of accomplishment. I had no bad days where I would think I wished I had run. None. That seems obvious, but it also provided me some comfort that I had moved things forward in my life. I took that lesson back to work, where I sometimes have bad days. I have days where nothing goes right, or I have people that take over my day and schedule.

I also know there are a number of things that I need to do at work, which aren't that important, or even aren't that critical, but they provide a measure of satisfaction that I've moved things forward. These days that is often getting things scheduled for the newsletter. In the past it might be doing a little space extrapolation (though I'd just look at this in SQL Monitor today). Low value items, but I get some accomplishment in the midst of otherwise stressful times.

The other thing that came from the running streak was the feeling of getting away from other parts of my life. Whether with family or work, I might be overwhelmed or stressed or otherwise out of sorts in my day. Taking 20-30 minutes for myself was a way of resetting my day, and it was a welcome break. While exercise is my thing, and even today it's a priority around work, I've had other crutches that help me cope. I love to read, so taking 5 minutes to escape in a book every day is something that feels similar to exercise.

I know other friends that might play a short game, play with a pet. Having some other consistent, distracting activity is a good release when things are tough. No matter how busy we are, or what's broken, taking 5, 10, 15 minutes away can help refresh you and get you ready to go back into the trenches and deal with others.

I'm sure jobs exist that can't spare the time, but I've never had one. I haven't always realized it until later, but I wish someone had told me to take 10 minutes away in a crisis to reset myself and then come back. I know I'll remember that the next time I end up being stressed during an IT problem at work.

Steve Jones - SSC Editor

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

Redgate SQL Monitor
 
  Featured Contents

ASCII Text Dump

saga from SQLServerCentral

Introduction Recently, I had the task of analyzing a varchar field for special characters. The field contained text entered by the user with a standard US keyboard; however, the proprietary software was breaking up the text and delimiting it with characters whose ASCII values were above 127. Eyeballing the text that I dumped using a […]

Take the 2020 State of Database DevOps Survey – win a new iPad

Additional Articles from Redgate

Redgate’s 2020 State of Database DevOps Survey is open now. Share your views on Database DevOps today for a chance to win a new 65GB iPad Air. Plus, for every entry, Redgate will donate $1 to UNICEF. Take the survey now.

ASP.NET Core with GitOps: Dockerizing an API on AWS EC2

Additional Articles from SimpleTalk

As more shops begin automating build and deployment pipelines, it’s critical to learn about containerization. In this article, Mircea Oprea walks you through deploying an ASP.NET Core API in a Docker container.

From the SQL Server Central Blogs - TDE in Standard Edition on SQL 2019

matthew.mcgiffen 73574 from Matthew McGiffen DBA

Recently Microsoft quietly let us know that TDE (Transparent Data Encryption) will be available in the Standard Edition of SQL Server 2019. If you don’t follow SQL topics on...

From the SQL Server Central Blogs - PowerShell Notebooks are Available in Azure Data Studio

Aaron Nelson from SQLvariant

PowerShell Notebooks are now available in the November release of Azure Data Studio. Back in March I requested this feature be added to Azure Data Studio and it has...

 

  Question of the Day

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

 

Computed Column Indexes

I have this definition for a computed column in my table:
ALTER TABLE dbo.Activities
ADD ProdOneCount AS CASE
                        WHEN ProductID = 1 THEN
                            CallCount
                        ELSE
                            0
                    END;
I now want to run this code. What happens when I run this?
CREATE INDEX dbo.Activities_PRodOneCountThreshold
ON dbo.Activities (ProdOneCount)
WHERE CallCount > 50;
GO

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)

Finding Failed Logins

I have installed SQL Server 2017 and left the default security action for "Audit Failed Logins" in the instance security properties. Where can I view the failed logins?

Answer: In the SQL Server error log and the Windows application log

Explanation: The failed logins are shown in the SQL Server error log and in the Windows application log. You can view an entry in either log.

Discuss this question and answer on the forums

 

Featured Script

Function and Queries to Convert Hierarchical Adjacency to Nested Json Rows

scdecade from SQLServerCentral

This script converts hierarchical adjacency into nested json rows which contain the recursive "downlines" of each node.  The table-valued function treats each row in the original adjacency as the root node in a recursive common table expression. 

/* 1) TABLE DDL */
/* 2) SAMPLE ROWS */
/* 3) TEMPORARY TABLE DDL and INSERT */
/* 4) TABLE-VALUED FUNCTION */
/* 5A) SAMPLE QUERY 1: SUMMARY COUNTS BY HIERARCHY LEVEL */
/* 5B) SAMPLE QUERY 2: SUB-SELECT BASED ON HIERARCHY LEVEL */

/* 1) TABLE DDL */
drop table if exists test_mycatalog;
go
create table test_mycatalog(
parent_id varchar(500),
document_name varchar(500),
data_id varchar(500));
go

/* 2) SAMPLE ROWS */
insert test_mycatalog(parent_id, document_name, data_id) values
(NULL,'CORP','1'),
('1','IT','11'),
('1','FIN','22'),
('1','HR','33'),
('1','Sales','44'),
('1','Legal','55'),
('11','IT Policy','11-1'),
('11','IT Procedure','11-2'),
('11','IT Deployment','11-3'),
('22','Financial report','22-1'),
('22','Financial stmnts','22-2'),
('22','Financial Release','22-3'),
('22','Financial policy','22-4'),
('33','HR Process','33-2'),
('33','HR Process','33-3'),
('33','HR Process','33-4'),
('11-1','IT Network Policy','11-1A'),
('11-2','IT Database Policy','11-2A'),
('11-3','IT Deployment 11-3A','11-3A'),
('11-1A','IT N/W Policy configuration','11-1A_1'),
('11-2A','IT DB Maint Policy','11-2A_1');
go

/* 3) TEMPORARY TABLE DDL and INSERT */
drop table if exists #tm_hierarchies;
go
create table #tm_hierarchies(
data_id varchar(500) unique not null,
parent_id varchar(500),
document_name varchar(500),
h_level int not null);
go

with
recur_cte(data_id, parent_id, document_name, h_level) as (
select
data_id,
parent_id,
document_name,
0
from
test_mycatalog
where
parent_id is null
union all
select
cat.data_id,
cat.parent_id,
cat.document_name,
rc.h_level+1
from
test_mycatalog cat
join
recur_cte rc on cat.parent_id=rc.data_id)
insert #tm_hierarchies(data_id, parent_id, document_name, h_level)
select * from recur_cte;
go

/* 4) TABLE-VALUED FUNCTION */
drop function if exists dbo.downlines;
go
create function dbo.downlines(
@data_id varchar(500))
returns table as
return
with inner_recur_cte(data_id, parent_id, h_level) as(
select data_id, parent_id, cast(0 as int) from test_mycatalog where data_id=@data_id
union all
select
cat.data_id, cat.parent_id, rc.h_level+1
from
test_mycatalog cat
join
inner_recur_cte rc on cat.parent_id=rc.data_id)
select
sum(iif(h_level=1,1,0)) lvl_1_count,
sum(iif(h_level=2,1,0)) lvl_2_count,
sum(iif(h_level=3,1,0)) lvl_3_count,
sum(iif(h_level=4,1,0)) lvl_4_count,
sum(iif(h_level=5,1,0)) lvl_5_count,
count(*)-1 lvl_all_count,
(select * from inner_recur_cte for json path, root('downlines')) json_downlines
from
inner_recur_cte;
go

/* 5A) SAMPLE QUERY 1: SUMMARY COUNTS BY HIERARCHY LEVEL */
select
*
from
#tm_hierarchies tm
cross apply
dbo.downlines(tm.data_id);

/* 5B) SAMPLE QUERY 2: SUB-SELECT BASED ON HIERARCHY LEVEL */
with tm_downlines_cte as (
select
*
from
#tm_hierarchies th
cross apply
dbo.downlines(th.data_id))
select
dl.data_id, dl.parent_id, dl.h_level, tm.document_name
from
tm_downlines_cte tdc
cross apply
openjson(tdc.json_downlines, N'strict $.downlines') with (data_id varchar(500), parent_id varchar(500), h_level int) dl
join
test_mycatalog tm on dl.data_id=tm.data_id
where
tdc.data_id='11'
and dl.h_level='2';

More »

 

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
SSMA tool shortcomings ? - I prefer to use ssma tool to perform the database migrations from older versions of SQL server to newer versions but could see the tool has a few shortcomings like it deletes the backup files after restoring , it wont change the database owner , compatibility level of the restored databases , it wont fix […]
SQL Server 2017 - Development
Round at specific decimal point - Hi, I am trying to round my numbers up to the nearest whole number if the decimal is equal to or greater than .7, for example 28.7 will be 29, or round down if it is below .7, for example, 28.6 will be 28.  I'm not sure If I need to truncate first and make […]
STIntersect - A friend of mine asked me a really interesting question, and I'm a little stumped. She's trying to determine from a given flight plan, which countries a plane will fly over. I was thinking the flight plan could be described as a linestring of (long, lat) points and then I could use STIntersect to see […]
SQL Server 2016 - Administration
SQL Copy Database Wizard Broken - I have been using  the SQL Copy Database Wizard (right click on the DB and Copy Database option) through the GUI (SMS)  to copy a DB up until SQL 2016. Every installation I have where 2016 or greater has been involved the feature is broken and always gives the same error. It doesn't matter if […]
SQL Server 2016 - Development and T-SQL
Transaction handling in sql server - Hi All, Have some questions related to transaction management in sql server. 1. What is the difference between having SET XACT_ABORT is ON and having BEGIN TRY ... CATCH ROLLBACK; 2. What if I use only BEGIN TRY ... CATCH ROLLBACK; and do not use XACT_ABORT ON ? Any issues we need to keep in […]
how to merge 3 different temp tables data into one - Is there any better way to do this?   create table #student (StudentName varchar(20) null, StdId int not null, Stdgrade varchar(10) null) insert into #student (StudentName , StdId , Stdgrade ) select std.Name ,std.StdID ,dp.stdgrade from dbo.student std join dbo.department dp on std.stdid = dp.id where dp.isactive = 1 insert into #student (StudentName , StdId […]
SQL Server 2012 - T-SQL
Need help to calculate in Single Query - My table and data as follow, USE [WeightManagement] GO /****** Object: Table [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1] Script Date: 7/11/2019 10:41:39 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[crpt_NotaPenyataKewangan_BL_Vertical_1]( [idx] [int] IDENTITY(-2147483648,1) NOT NULL, [batch_Id] [uniqueidentifier] NULL, [group_1Digit] [char](5) NULL, [group_2Digit] [char](5) NULL, [group_3Digit] [char](5) NULL, [chart_code] [char](5) NULL, [pusat_kos] […]
Find records from the last 24 hours - How do I get the records for the last 24 hours for each VisitID, based on the ActivityDateTime? So, beginning at the most recent ActivityDateTime for each VisitID, go back 24 hours and pull those records.     create table #T1 ( VisitID varchar(30), IdentifierID varchar(30), ActivityDateTime datetime ) insert into #T1(VisitID,IdentifierID,ActivityDateTime) values('F0-B20191023162046527','20907277','2019-10-24 10:18:00.000') insert […]
Get some row depending of Date of nexts rows - Hi, Sorry for my bad english. I need to select some rows of a table like that : Id Object   |              Start            |             End A           | 06/11/2019 09:00 | 06/11/2019 09:15 A        […]
SQL Server 2008 - General
sp_executesql not working - Hi, We have 2 dbs DB A and DB B on two servers AA and BB respectively linked to each other. Stored Procedure(stp)  Stp_A in DB A calls stp_B in DB B fetches data from DB A and stores it in temperory table. we have used dynamic sql and used exec sp_executesql command. the sample […]
SQL Azure - Administration
Migrate Database with column level encryption to Azure - Hi, We've got an on-premise database that uses column level encryption and are currently investigating whether we could migrate it to the SQL Database service in Azure. In the past if we have moved the database between on-premise servers we've been able to change the master key to encrypt by password before taking a backup […]
Reporting Services
Hosting PowerBI in SSRS - Overview I have been tasked with the question is there a tool that can host PowerBI reports other than PowerBi Reports Engine?  The problem I believe is licensing.  We have license for SQL Server Enterprise 2017 that includes SSRS & SSAS. Question Can we host PowerBI Reports in SSRS?  I believe I got the answer […]
Reporting Services 2008 Development
How to make Time Range chart in SRRS? - Hi Everyone, I am new to in SSRS report development, I have a requirement like to show the report in SSRS as below formant, Could you please help me to achieve this result? How should keep data in SQL script every five minutes what is machine status?    
TFS/Data Dude/DBPro
TFS change tracking and details. - Hi, I had a quick question and I am using TFS 2015. How do I see what is the changeset number of an SP which is deployed just now? How do I find out at what time that Stored procedure or object was checked in? Thanks!  
Integration Services
Dataflow each batch takes longer - I'm looking for a technical reference on how fast-load work. Each buffer transfer seems to take longer Case: Transfer data from Oracle to SQL Server Dataflow Source: Oracle Oledb, fetchsize 50000, default buffer max rows 50000 Dataflow Destination: SQL Server (fast-load), rows per batch 50000, maximum commit size 50000 The transfers start quick, but as […]
 

 

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

 

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