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

Daily Coping Tip

Go outside and spend a few minutes appreciating the weather

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.

Note: Althought we are moving to 3 newsletters a week today, I'll still continue with daily coping tips on my blog, if you want to follow there.

Holding Code in Your Head

It is hard to write software. Many of us write lines of code, and often if we go back, we find flaws and problems in our code. As I look at the solutions others might give for the same problem, I'm sometimes amazed at the way they tackle the problem and what language features they use. I'm especially always intrigued when I see someone using STUFF() or APPLY in their queries. I know I have a bit of a hole here, as I haven't done enough work with them in my career. It's a tool I just don't think to reach for.

Part of that is that I don't know I completely understand how they work in my head, at least not without effort. The lack of familiarity hurts me here, but it's part of the issue that Paul Graham expresses in this essay about holding a program in your head (essay via Brent Ozar).

I certainly have held entire programs in my head, and when I do, I think I work a little more efficiently. Certainly faster, and I can change things as I refine the solution I'm aiming for. I also find that when someone else is touching the code I'm working in, even lightly, it is distracting. The differences in reading my code vs. someone else's are noticeable.

I think this is where working in a team, unless you are pair/mob programming, can be challenging. While discussing issues with others might help find solutions, I do think that groups need to subdivide code into sections and allow each person/pair/mob to work on that code alone for a period of time. Some organizations do this well, but plenty do not.

Perhaps one of the best reasons to be careful about touching the same code in a database is the last writer wins. While some tools might work with the code in memory on your workstation, many of the ways we capture and track T-SQL code come from the database itself. If multiple people change the same object in a shared database, we can easily lose code or frustrate programmers seeing strange behavior.

There are a number of items that Mr. Graham thinks will help programmers write better, or more, code. However, I also think that his problem space is focused more on startups and new applications rather than the legacy code on which many of us work on every day. However, I do take the point that having a more succinct language might allow a programmer to hold more in their head and get more done.

If they're are intimately familiar with the language itself. I think that is a skill managers should promote in teams. And something I need to work on with STUFF and APPLY.

Steve Jones - SSC Editor

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

 
  Featured Contents

Understanding CRUD Operations Against Heaps and Forwarding Pointers

Mohsin_A_Khan from SQLServerCentral

In this article, we examine how data changes are made against heaps.

How to Generate Fake Test Data for SQL Server

Additional Articles from Redgate

How to use a SQL random data generator to fill SQL Server tables with realistic test data, to shift left database unit testing, integration testing and performance testing so that it is performed during the early stages of database development.

SQL Server Memory Usage Query

Additional Articles from MSSQLTips.com

Check out this SQL Server function that can return memory usage information for a SQL Server instance such as the amount of memory SQL Server is using and available memory on the machine.

From the SQL Server Central Blogs - Finding Foreign Key Child Records In SQL Server

John Morehouse from John Morehouse | Sqlrus.com

Foreign keys help to ensure referential integrity between tables.  In other words, parent records cannot be deleted if there are child records present.   This is a great thing and...

From the SQL Server Central Blogs - Chaos Engineering and SQL Server

dbafromthecold@gmail.com from The DBA Who Came In From The Cold

Recently I’ve been delving into Chaos Engineering, reading books, watching videos, listening to podcasts etc. and I find it really intriguing….I mean, it just sounds exciting, right? CHAOS Engineering!...

 

  Question of the Day

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

 

String Together ADF Strings

I have a flow in Azure Data Factory (ADF) in which I get a few strings from files and a lookup. I want to put these together to get a filename. What function should I use in place of the xxxx below?
xxxx(@string1, @string2, '.csv')

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)

Shutting Down the Instance

Which fixed server role(s) can shut down the instance?

Answer: sysadmin and serveradmin only

Explanation: Only the sysadmin and serveradmin can shut down the server. Ref: Server-Level Roles - https://docs.microsoft.com/sql/relational-databases/security/authentication-access/server-level-roles?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 2016 - Administration
The most illustrative case for using EXTENDED EVENTS? - What would be a couple of things that I can do with Extended Events that I cannot do via Profiler Traces, DMVs, and/or QueryStore?  (Leaving aside the  lower XE's overhead/performance issues compared to svr side traces...). ANDOR a rephrased question: what kind of analysis/things to troubleshoot can I do with XE that I cannot do […]
Install SQL Server SP2 - Hello. On a Windows Server there are installed two SQL Server 2016 SP1 named instances. Can i install SP2 on one of them without to affect the other? Thank you.
SQL Server 2016 - Development and T-SQL
Category Entry and Exit Dates per ID - Hello, I have the following table, where ID is the unique identifier. An can move from category to category, both up and down. My table records each day an ID stays in a given category. I am trying to identify the start date and the end date of an ID in a given category. The […]
Encryption String and SQL Server Jobs - Hello~ I am a developer of a DB Application (Azure SQL Server Back End/MS Access Front End), where we have recently encrypted several fields. We have a scheduled SQL Server Job, which has started hanging -- this job simply executes a VBS Script from the command line using 'cscript'.  This VBS Script connects to the […]
SQL Server 2019 - Administration
stop responding to queries of specific database - Hello I found a problem on my SQL Server instant, after 5-6 days the servers stop responding to queries of specific database and it won’t get fixe until I restart the service!   I checked the event logs, it says: " SQL Server was unable to run a new system task, either because there is […]
SQL Server 2019 - Development
Script to DROP and RE-CREATE all Primary and Foreign Key Constraints on all Tabl - Hi Does anyone know of a good way to Drop and Recreate all Primary and Foreign Key Constraints for all tables in a database please? Ive looked at various solutions but they dont seem to capture and recreate all the PK and FK constraints for all tables
Using CASE Statement with $Action in Output Clause in Merge - Hi there I want to write one combined Output statement at the end of a Merge statement to insert data into an action table, based on the action So I want to do something like Select $action case when $action = 'Update' then Output $Action, Deleted.* into #DeviceDataDelta case when $action = 'Insert' then Output […]
Sum of Rows - Hi Guys, How do i group the below code so it gives e a total duration for each person for each day. So for 07/08/2018 Dave Jones should show:     -- Sample data IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab SELECT * INTO #lab FROM (VALUES ( '07/09/2018','Dave', 'Jones', '07/09/2018 09:56', 2301 ), […]
Simple SQL query - MOTEL (motelID, motelName, town) ROOM (roomID, motelNo, type, cost) BOOKING (motelNo, guestID, dateFrom, dateTo, roomID) GUEST (guestID, guestName, guestAddress)   For the above SQL database relation, I have two questions that I'm trying to answer: Select all guestIDs that have the person's first name as 'John', and have a booking with an undefined dateTo. No explicit or implicit […]
Problem with conversion from char to datetime - Hello everybody. I am trying to insert  some data in table  Production.WorkOrder. There are some data which type is datetime. I am trying to insert that type of data with CONVERT keyword. But when I run my query I get this.  What I need to do to solve this problem?    
Deadlocks during concurrent delete operation - In our environment we have written all our business logics inside a stored procedures. In general, in any bigger transactions where it has integrations with multiple modules we used to populate the required data in a table (its kind of temporary table) with guid column as clustered index and this table would be used subsequently […]
Strategies and Ideas
Looking for suggestions on improving our "data warehouse" approach - I've come to find myself inheriting a loose "data warehouse" approach of sorts.  I'm quite new to data warehousing concepts, ETL, etc, but I have a feeling some of the things we're doing are less than optimal, so I'd appreciate any feedback on what we should be doing differently.  We're a low-to-mid size company that […]
Integration Services
The table name could not be retrieved from the script provided - I am having trouble moving data with ssis fromSQL Sever to Maria DB. When i run the following script, the table gets created in Maria DB. But no access to upload the data. Here is the error: "TITLE: Microsoft Visual Studio ------------------------------ The table name could not be retrieved from the script provided. Select a […]
SQL Server is changing the date format - Hi All, I am using VS 2017 to import data from excel to a SQL table, very basic no problems with loading the rows. The issue is I need the date format to be UK dd/mm/yyyy and SQL server keeps changing the formatting to US once it hits the table. The windows clock on the […]
TSQL To Maria DB - I am trying to load dat from TSQL Into Mysql/Maria DB. I get an aoutamic warning when trying to create the destination table about manually edit data types for destination table. I looked at the TSQL n Maria DB data types. I changed it from CREATE TABLE "ADO NET Destination" ( "uid" BIGINT, "order_uid" BIGINT, […]
 

 

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

 

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