SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Counting Occurrences in Python

The Voice of the DBA

Daily Coping Tip

Contribute positively to a good cause for your community

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.

Reteaming

At many companies in which I've worked in the past, I did the same job for years. I rarely changed positions, which was fine for me as a database developer or administrator. I might move up slightly in terms of level (junior-mid-senior), and I did get raises, but I did the same job. Even through reorganizations, I mostly did the same job.

In many larger companies, it seems that reorganizing departments and people is something people do regularly. I've seen friends at Microsoft change departments, products, etc. every year or two. I have friends in other organizations where they might change slightly more or less often, but they still move around.

At Redgate, we've always done something interesting with the development staff. We allow them to reteam at the start of every year. They can list the teams with which they want to work, ranking a few choices, and then the development leadership works to match people into the teams. Overall, across the last few years, the vast majority of developers think the process works well. I'm sometimes not thrilled when someone leaves a team where I think they are making a large impact, but I am glad that people get to have some input into what work they do.

Our head of product delivery wrote about the process this year, talking about how it works, the corporate goals, and the challenges of doing this remotely. It's an interesting read, and I think this is something that can help other managers of software teams think about how they might build better teams, with developers that grow and learn, producing higher quality software over time as they grow their skills and take an active role in their own careers.

Steve Jones - SSC Editor

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

Featured Contents

Stairway to SQL Server Extended Events Level 3: Working with the Extended Events UI

Erin Stellato from SQLServerCentral.com

In the next installment of our stairway to Extended Events, we delve into the Live Data and Target Data Viewers.

Comparing and Syncing Data using SQL Data Compare Command Line

Additional Articles from Redgate

Robert Sheldon demonstrates how to start automating data comparisons between two databases, from the Windows command line or PowerShell. With a single command, you can easily compare and sync data such as test data sets, or static data used for reference or lookup purposes.

Learning SQL Server

Additional Articles from MSSQLTips.com

Learn about the core SQL Server concepts to build your skill set including the architecture, database design, development, administration and more.

From the SQL Server Central Blogs - Announcing the Azure Data Community from Microsoft

Steve Jones - SSC Editor from The Voice of the DBA

You can see an announcement from Buck Woody (b | t) of Microsoft on Data Exposed. Essentially, Microsoft is providing a number of free resources to help local user...

From the SQL Server Central Blogs - SQL Index Creation Using DROP EXISTING= ON

SQLEspresso from SQLEspresso

When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently used methods is DROP EXISTING; in...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Counting Occurrences in Python
I have some data in a list: friends = ['andy', 'brian', 'sally', 'brian', 'dean', 'tim', 'bertha', 'sarah', 'ann', 'ann', 'brian'] I want to get a count of the number of friends named "brian". How can I do 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 Default LAG

I've got a query that gives me the previous month's sales for a set of data:

SELECT 
       ms.saleyear
     , ms.salemonth
     , ms.currMonthSales
     , LAG (ms.currMonthSales) OVER (ORDER BY
                                         ms.saleyear 
                                       , ms.salemonth) AS prevsales
FROM   dbo.MonthSales AS ms
ORDER BY ms.saleyear DESC, ms.salemonth desc;

However, the value for the last (earliest) month is NULL. What is the easiest and cleanest way to change the query to make this a 0 (zero) for the last row?

Answer: Add a third parameter to the LAG function as a o (zero)

Explanation: The LAG function takes an optional third parameter, which is a default if there is no LAG value in the partition. In this case, the default is set to 0. 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 - Administration

Extended events query plan - All, Firstly apologises as I'm sure I'm missing the obvious but I've searched and I can't work it out. I have setup extended events and included both the plan_handle and query_hash actions. I'm retrieving the results with the following: set dateformat dmy SELECT n.value('(action[@name="query_hash"]/value)[1]', 'nvarchar(128)') as query_hash, n.value('(action[@name="plan_handle"]/value)[1]', 'nvarchar(max)') as plan_handle from ( select cast(event_data […]

SQL Server 2017 - Development

Group By with SELECT MAX - Is there a tutorial for this? And can someone help me with what is wrong? This woks fine if I remove the MAX and GROUP BY. I don't understand what I need to group by. ERROR: Msg 8120, Level 16, State 1, Line 27 Column 'Documents.DocumentID' is invalid in the select list because it is […]

SQL Server 2016 - Administration

Migrate from AWS SQL Server RDS to EC2 - Hello, I could not find any documentation on how to migrate SQL server RDS to an EC2 instance. I have a requirement to migrate a large DB to ec2. Can anyone help me with the steps to migrate the DB with low downtime, please? Thanks in advance

Dynamic Data Masking and Adhoc queries. - This is the first time we are implementing this, Data Masking that became available in SQL Server 2016. I am not sure that this is a best Data Masking solution though. Because documentation says that a non-privileged user can run an ad-hoc query, using some functions like CAST, and data becomes visible. Is my understanding […]

SQL Server 2016 - Development and T-SQL

Returning single row multiple values - I am wanting to return only one row for continuous employment, what is the indcator is there WORKTYPE, RELATIONSHIPSTATUS and BEGINDATE. The bolded line is the BEGINDATE I want for Employee 100043, Employee 105640 has ENDDATE of 9999-12-31 which will be GETDATE, The attach image is of the results as they should be returned. --===== […]

DelimitedSplit8K License Question - This may be a question specifically for Jeff Moden, but does anyone know what license governs the DelimitedSplit8K function? Tally OH! An Improved SQL 8K “CSV Splitter” Function I ask because I may want to adapt it to fit some code I'm writing, and I just want to make sure I stay within the lines. […]

Question related to architecture - Hello Fellow DBA friends, Currently I am working on a project where a system has to be migrated to our system. I just want to know whether I should go with creating a new DB schema for its objects in current database or should I create a new database altogether ? What are the specific […]

Administration - SQL Server 2014

Transaction Log Shipping Location Change - It has been a while since I have done anything with log shipping and wondered if someone can provide me with some assistance. So basically the old file server is being decommissioned where the Log shipping writes to. We have a new file server and as such, I would like a simple way to modify […]

Who modified my Database? - Hi, we have an issue where entire cubes are sometimes deleted from our SSAS database. I am assuming a human has done this, but I cant find any way of determining who did it or when it has happened. Does anyone know where (if at all) Database schema modifications would be logged? Thanks!

SQL Server 2019 - Administration

Subscription owner and multiple subscriptions - We are using reporting service 2019 Enterprise. There is an issue with running the subscription the other people created. For example two user are both content manager role. And the first user created the subscription, and she becomes the owner of the subscription automatically. She runs the report with no problem. But when the second […]

SQL Server 2019 - Development

Custom Header Row in Query Result - Don't laugh too hard. For 36 months I've been sending a monthly file to a vendor that requires a custom header on Row 1 of the flat text file. I've been opening the text file in Notepad++ and manually setting the header. I want to level up and stop doing this. There are only three […]

Add column to existent table with default costraint value based on condition - Add column to existent table with default value based on condition Hello, i would to create a new column with default value, but i would know if its possible to speficy dfault value upon a value on another column, example i would write something like: ALTER TABLE [MyTable1] ADD NewRange bit NOT NULL default (IF […]

Vague job scheduler support for SQL Server on Linux - I’m having some trouble figuring out the kind of support for Linux that the SQL Server 2019 job scheduler has. It’s clear that the job scheduling procedure API, and SQL Server Agent have both been ported to Linux and appear to work. But there are a limited number of kinds of job steps supported. These “kinds” of […]

Powershell

double check if file exists in S3 bucket directory - Hello, I did google this, and not sure if I am doing it right or maybe just missing one minor thing, I am just trying to verify if the file exists in S3, if so, great, if not, then... I will figure something out for now I have the following code: $DBList = Invoke-SQLQuery […]

The Future of Community

We need to move beyond SQL Server... - I think we should have a discussion about topic. First, the future is more data. SQL Server is just one place it is put. We need reflect the needs of our customers and prospective clients. How do we get there? We do SQL Server now and add others annually. Postgres, MySQL, Oracle and Vertica have […]

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.