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

Daily Coping Tip

Notice what you are feeling today without judgement

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.

The Habits of Better Teams

As a developer, I've often worked with a team of others, where we discussed the software we were building, what architectures and patterns to adopt, etc. While we all learned to work together, there were plenty of times that it felt as if we were working separately on code, but arguing often about how to make a decision about a technical approach or a priority.

As a DBA, I've often worked alone, though usually interfacing with different teams as needed. In these cases, I've often had to learn to make my own decisions and then justify those to others later. The few times I worked with a team of DBAs, it seemed as though this was still the model, with everyone deferring to the senior DBA.

Building a team takes work, and it can be difficult to do so without the support of management. I'm always amazed at how many managers undermine the building of a high performing team, often in contrast to their words or the company's statements. Many don't even realize how poorly they promote teamwork, or how they actively prevent it.

Assuming you can get support, what do you want in your team? I saw a neat post on the habits of high performing teams. These are some of the things you want to install in all your members. I've rarely had high psychological safety, though I think we do now at Redgate. I especially like the idea of leveling experience points. I hadn't thought about it in these terms, but that makes perfect sense to me.

That last section, where we assume our peers are competent and intelligent resonates with me. I haven't always done this in the past, but I have tried hard later in my career to listen more, and to assume others are working with the best information they have. Perhaps that's different than my information, so I should try to have a discussion, not a lecture or a dismissal of their effort.

That's something I wish I would have learned a lot earlier in my career.

Steve Jones - SSC Editor

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

Redgate University
 
 Featured Contents
Stairway to DAX and Power BI

Stairway to DAX and Power BI - Level 13: Simple Context Manipulation: Introducing the DAX All() Function

Bill Pearson from SQLServerCentral.com

Business Intelligence Architect, Analysis Services Maestro, eight-year Microsoft Data Platform MVP and author Bill Pearson introduces the DAX All() function, discussing its syntax, basic uses and operation. He then provides hands-on exposure to All(), focusing largely upon its most basic uses in manipulating filter context.

Managing Azure Blueprints with PowerShell

Additional Articles from MSSQLTips.com

Learn about the Azure PowerShell cmdlet for Azure Blueprints in Azure Resource Manager framework and how to export, import, publish, and assign Azure Blueprints with PowerShell cmdlets.

Free eBook: SQL Server Backup and Restore

Press Release from Redgate

In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool.

From the SQL Server Central Blogs - Query options in Azure Synapse Analytics

James Serra from James Serra's Blog

The public preview version of Azure Synapse Analytics has three compute options and four types of storage that it can access (mentioned in my blog at SQL on-demand in...

From the SQL Server Central Blogs - Merry-Go-Round or Advanced Scan

david.fowler 42596 from SQL Undercover

The merry-go-round scan or advanced scan is something that I’ve seen mentioned a few times recently and it’s a lovely little feature of Enterprise Edition that not all that...

 

 Question of the Day

Today's question (by BTylerWhite):

 

Returning Python Dictionary Values

I have the following nested dictionary in Python 3: 
people = {
    1: {
      'last_name': 'Dactyl',
      'first_name': 'Teri',
      'age': '27'
    },
    2: {
      'last_name': 'Erd',
      'first_name': 'Liz',
      'age': '22'
    }
  }
I need to retrieve Teri Dactyl's last_name. Which method can I use to return the value of this item?

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

 

 

 Yesterday's Question of the Day (by Sergiy)

How Many Distinct Records

You have a table containing non-unique records:

CREATE TABLE #QOD (
Number int NOT NULL,
String varchar(50) NOT NULL
)
INSERT INTO #QOD (Number, String)
SELECT 10, 'abcdefg'
union all
select 20, 'abcdefg'
union all
select 20, 'abcdefg'

How many records are returned by each of the following queries?

--Query1 
select distinct NEWID(), Number, String
FROM #QOD;

--Query2
select NEWID(), Number, String
FROM #QOD
GROUP BY Number, String;

Answer: Query1 - 3, Query2 - 2

Explanation: GROUP BY is executed after FROM but before SELECT. Any manipulations, functions within SELECT don't affect the number of rows returned by GROUP BY. Therefore the function NEWID() adds uniqeidentifiers to 2 distinct rows returned by GROUP BY. DISTINCT is applied against the resultset returned by SELECT. NEWID() in the SELECT adds uniqueidentifiers to the output making every row of it unique. Therefore DISTINCT keeps all 3 rows in the resultset. You may see it for yourself by looking into execution plans:

set statistics profile on

select distinct NEWID(), Number, String
FROM #QOD
select NEWID(), Number, String
FROM #QOD
GROUP BY Number, String

set statistics profile off

Ref:

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
DEA - What is the use of DEA tool in sql ?  how can we  install on sql ?
SQL 2017 MLS - cant add new python packages - Hi I'm new-ish to MLS and have been trying to add new python packages like  attrs  to the python but it hasn't been working. Our set up is SQL 2017 with CU14 on Win 2016. Python version is 3.5.2 The usual command I run is : "pip install attrs" ( without quotes ) and I […]
Index Fragmentation - I am looking into fragmentation in one of my databases and ran across these stats - should I be rebuilding these indexes? That's what it looks like, just want some ideas/confirmation to see if I'm on the right track.  
SQL Server 2016 - Administration
SQL Server 2016 seeding automatic stuck - Hi all, Problem help please, I have 2 servers in sql server 2016 and i have started seeding databases to the DR server within the GUI of AOG which is FCI  problem is that  one of my dbs which is 2 TB with take 1 month to finish. I need to stop this and continue […]
will this get replicated to secondary replica - Dear Experts, In Availability Group, if we move ndf file to another drive on primary, will this get replicated to secondary replica
SQL Server 2016 - Development and T-SQL
SQL Query - FULL JOIN multiple tables but return NULL results - Good day! Need help with my sql query code; first FULL JOIN "ILEtransfer" no NULL result but im getting NULL result once i add a FULL JOIN "ILEmsales" Thank you in advance! here's my sql query code; ;WITH Barcodes AS ( SELECT [BBI$Barcodes].[Item No_] ,[BBI$Barcodes].[Description] ,[BBI$Barcodes].[Variant Code] FROM [BBI$Barcodes] ), ILEtransfer AS ( SELECT [BBI$Item […]
Administration - SQL Server 2014
Server local connection provider has stopped listening - Hello, Once / twice a week I'm getting the following error. Server local connection provider has stopped listening on [ \\.\pipe\SQLLocal\MSSQLSERVER ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening. where should I search for the source of the error? Is this error is due to the […]
Development - SQL Server 2014
Insert Trigger - Hi All, I have table A that has 2 columns(col 1,col2). I would like a create trigger that collects all duplicate inserts.For example ,when i insert new records that both col 1and col2 exists on Table A ,then insert that record into Table Audit as well. Here what came up with ,but logic is not […]
Number weeks from April to September for every year - I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following Saturday and so on.... till the end of September. How do I do that using TSQL. Thanks in […]
SQL Server 2012 - T-SQL
slow query performance - Hi All, I have a SELECT query which is taking more 8 mins. Can you please help me in making the query run faster (if possible) . There is no blocking . Seeing PAGEIOLATCH_SH waittype for most time. The table is a clustered index table. There is no non-clustered idx on mule_batch_id column. Query: use […]
SQL Server 2019 - Administration
\'REMOTE ACCESS\' setting flipped back to 0 overnight ?? - Any reason why our SQL Server 2016 Standard edition instance would have flipped the 'REMOTE ACCESS' setting back off? Here's what happened: - I ran the following code AS IS on both SQL Servers: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'remote access'; -- Display current setting GO --EXEC SP_CONFIGURE 'remote access', […]
SQL Server 2019 - Development
SSIS 2019 - Conditional Expression for the value of a variable- fails evaluation - Hi All, Ran into an error trying to evaluate the following expression for a variable value: @[User::varBadRows] > 0 ? (DT_STR, 50, 1252) ("There are " + (DT_STR, 3, 1252) @[User::varBadRows] + " rows with a non-matching SPID value.") : NULL(DT_STR, 50, 1252) I'm using Visual Studio 2019 Pro and my target environment is SQL […]
The backup set holds a backup of a database other than the existing 'MyDB' - I'm trying to restore a database from our production server to my local machine using the following script but still get the error, 'The backup set holds a backup of a database other than the existing 'MyDB' database. USE [master] RESTORE DATABASE [MyDB] FROM DISK = 'C:\MyDB.bak' WITH REPLACE, MOVE 'MyDB' TO 'C:\MyDB.mdf', MOVE 'MyDB_log.ldf' […]
Find row closest to date - Just having trouble getting this to work as I hoped. Trying to extract entire row from one table with a date closest and before another date in the 1st table.  Sample table and 1st try below. Any help is appreciated Trying to get: 1,    2020-08-05,  2020-08-03,   25 2,    2020-08-04,  2020-08-03,   34 3,   2020-07-28,  […]
SQL Select with condition - Hi, In my sql table,  there is a column which have string of text : Column 2ABF   CD000000000012345000000000001156 3000000000003452000000000000356 I would like if the 1st character is 2 then  out put as ID               Code           Num1          Num2          Num3       Num4 2ABF        CD              123.45         11.56 if the 1st character is 3 then ID               Code           Num1          Num2          Num3       Num4 […]
 

 

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

 

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