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

Daily Coping Tip

Enjoy trying a new recipe or cooking your favorite food

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.

A 2020 Look at Software Developers

Who are we? I think we often don't have a good view, since most surveys struggle to get 1,000 responses. I know many surveys think that's a representative number, but I think it feels low. I wish we'd get more data at SSC, but too few people participate in our efforts. The one place that seems to do a great job of getting responses is Stack Overflow. They released the data on their 2020 survey, and an article at Freecodecamp summarized some things.

Everyone looks at this data differently. For example, in the top loved languages, I see these: Rust, TypeScript, Python, Kotlin, and Go. Of these, I've really only used one. The most dreaded language is VBA, and I'd concur, but the most wanted language is Python. More developers want to use this than any other. Interesting numbers, but these seem more like emotional items, not practical ones. If we look at what pays the best, the Perl is at the top. I don't know anyone that primarily works in Perl, though to be fair, I'm Microsoft focused. At least in the tech clusters, most have some relation to some SQL technology.

The database section is very interesting to me. This is my field, and I wonder how developers look at the world. The most loved db is Redis, and I'm not surprised. It's key-value, blazingly fast, and relatively simple. I went through a couple courses at Redis University and found it to be a neat platform. SQL Server is 7th, with 50% loving it compared to 66% for Redis.  It's also 8th in dreaded and 12th in wanted. Interestingly, while Oracle and DB2 are dreaded, Couchbase and Cassandra are up there. Redis is the least dreaded, though still 30% of developers dread this. My guess is that developers just struggle with databases in general and get annoyed by having to deal with them.

The other interesting data point in the results is about searching for a solution online. Just over 50% of the people are happy to find a link they've already searched on Stack Overflow. That makes me wonder if we aren't really learning to code better, or if perhaps we don't bother because we can just search. For a lot of programming research I do in R, Python, and other non-SQL places, SO is a great resource and often gives me a concise, useful answer.

Maybe this is the future of programming? Learn to search and what things to use, and don't bother memorizing deep syntax or pattern specifics? I don't think that works for SQL. We need to learn to be better. Perhaps this is why developers dislike SQL because it has few keywords and requires more programming skill to build efficient solutions. Those might be harder to search for on the Internet.

Most developers work overtime, but it's balanced among the time scales. Only a quarter of people do this every week, which I think is still too much, but at least this isn't the vast majority. I think 15-20 years ago that would have been different. At last 60+% are slightly or very satisfied. Still about a quarter aren't satisfied with their jobs.

There are other interesting items in there, and if you are a developer, or you're looking to grow your career, it's worth spending a few minutes with the data, and looking at how you view the world compared to others. I don't know that I'd try to follow the crowd, but I'd certainly think a bit about how I view the world compared to others.

Steve Jones - SSC Editor

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

 
 Featured Contents

Stairway to U-SQL Level 22: Creating a Custom Extractor

Mike McQuillan from SQLServerCentral

Throughout this series, we’ve consistently dealt with delimited text files; comma and tab, for instance. We’ve used the U-SQL built-in extractors to process these files. But what if we need to deal with different types of file, like JSON, XML or fixed width? That’s where custom extractors come in. U-SQL provides us with the ability […]

SQL Server Data Masking with DbDefence

Additional Articles from MSSQLTips.com

Data breaches are becoming too common place with some accounts reporting more than 1000 breaches per year with close to 50 million records exposed yearly in the financial, business, education, government and healthcare industries. Learn about how DbDefence performs SQL Server Data Masking as a portion of a three pronged approach to protect your data.

Free eBook: Performance Tuning with SQL Server Dynamic Management Views

Additional Articles from Redgate

Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions.

From the SQL Server Central Blogs - TIL: Dismount-DbaDatabase and Mount-DbaDatabase

Kevin3NF from Dallas DBAs

We have a double feature for today’s dbatools blog post, as these two commands go hand-in-hand. Todays commands: Dismount-DbaDatabase and Mount-DbaDatabase Detach-DbaDatabase and Attach-DbaDatabase can be used as aliases....

From the SQL Server Central Blogs - Restoring a SQL Server Database in Docker

John Morehouse from John Morehouse | Sqlrus.com

Last month I blogged about using Docker to run SQL Server as a quick and easy way to get SQL Server up and running.  While it continues to be...

 

 Question of the Day

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

 

Adding a new column in Python

I have a dataframe in Python that looks like this:
>>> import pandas as pd
>>> salessdata = {'SaleDate':['1 Jun 2020', '2 Jun 2020', '3 Jun 2020'],'SaleAmount':[100,200,300],'NumItems':[1,4,7]}
>>> sales = pd.DataFrame(salessdata, columns = ['SalesDate', 'SaleAmount', 'NumItems'])
I want to add a new column that will calculate the average cost per item, based on dividing the SaleAmount by the NumItems. Which of these will do that for me?

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 A in ACID

What does the A in ACID stand for when related to relational databases?

Answer: Atomicity

Explanation: The A in ACID is for Atomicity. This is the concept that each transaction is a single unit Ref: ACID - https://en.wikipedia.org/wiki/ACID#:~:text=In%20computer%20science%2C%20ACID%20(atomicity,errors%2C%20power%20failures%2C%20etc.

Discuss this question and answer on the forums

 

Featured Script

move db files to different location dynamically

1974lg from SQLServerCentral

Lately, I'm dealing with lots of DB migrations and came across situation that I have no proper dynamic script to move DB files to different location. There are an option to proceed manually scripting one by one DB and execute it. Another option is detach attach DB - I'm not a big fan of this […]

-- Get database file information for each database
IF OBJECT_ID('TempDB..#holdforeachdb') IS NOT NULL
DROP TABLE #holdforeachdb;

create table #holdforeachdb
( [databasename] [nvarchar](128) collate sql_latin1_general_cp1_ci_as not null,
[size] [int] not null,
[name] [nvarchar](128) collate sql_latin1_general_cp1_ci_as not null,
[filename] [nvarchar](260) collate sql_latin1_general_cp1_ci_as not null
)
INSERT
INTO #holdforeachdb exec sp_MSforeachdb
'select ''?'' as databasename,
[?]..sysfiles.size,
[?]..sysfiles.name,
[?]..sysfiles.filename
from [?]..sysfiles '
--NEW location of DB files
DECLARE @NewDataPath NVARCHAR(4000)='N:SQLDATA', /*!!!!!!MODIFY ACCORDINGLY!!!!!!*/
@NewTlogPath NVARCHAR(4000)='L:SQLTLOG' /*!!!!!!MODIFY ACCORDINGLY!!!!!!*/

;WITH DataBasefiles (dbname, size_Gb, logical_name, Path, PhysFileName, FileType)
AS
(select databasename ,
(size*8.00/1024/1024) size_Gb ,
sf.name logical_name,
LEFT(FileName,LEN(FileName)-CHARINDEX('',REVERSE(FileName))+1) Path,
RIGHT(FileName,CHARINDEX('',REVERSE(FileName))-1) PhysFileName,
SUBSTRING([filename], (LEN(filename)-2), 4) AS FileType
from #holdforeachdb sf
JOIN sys.databases db on db.name=sf.databasename)

select dbname,
--size_Gb,
logical_name,
Path AS 'existing_path',
PhysFileName,
FileType,
CASE
WHEN FileType = 'ldf' THEN 'USE [master]; ALTER DATABASE '+QUOTENAME(dbname)+' SET OFFLINE WITH ROLLBACK IMMEDIATE;'
ELSE '' END AS 'SET_DB_OFFLINE',
'USE [master]; ALTER DATABASE '+QUOTENAME(dbname)+' MODIFY FILE (Name = '+logical_name+' , FileName = N'''+CASE
WHEN FileType = 'mdf' THEN @NewDataPath
WHEN FileType = 'ndf' THEN @NewDataPath
WHEN FileType = 'ldf' THEN @NewTlogPath
END +''+PhysFileName+''');' AS 'MOVE_DB_FILES_CMD',
CASE
WHEN FileType = 'ldf' THEN 'USE [master]; ALTER DATABASE '+QUOTENAME(dbname)+' SET ONLINE;'
ELSE '' END AS 'SET_DB_ONLINE'
FROM DataBasefiles

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 - Development
NPI Data - Has anyone ever imported the NPI data from the CMS website? I can't seem to get it to work.   https://download.cms.gov/nppes/NPI_Files.html
SQL Server 2016 - Administration
Linked server -   Hi I have 2 servers A and B for which I need to setup a linked server. these are SQL servers. When I right click on server A and select the option create new linked server and I enter the new linked server name as B and in server B when I select the […]
Health check for SQL Server - We had ESXi host issue cause SQL Server VM's to reboot. So I looked at the logs and connectivity test and now running check db. Do you think I am missing anything here? Thanks in Advance!
SQL Server 2016 - Development and T-SQL
Create a customer calendar from its history - Hello, I would like to create a calendar from a history table for a customer, so that I can read the status for each day. Date        SupportlevelID 2020-12-29  4 2020-12-30  NULL 2020-12-31  NULL 2020-12-29  NULL But I only have a history table where I can find dates of changes. SupportlevelID | date […]
RunRate , Forecast using Recursive CTE or UNION ALL - I would be grateful if you can help me to calculate the RunRate(forecast) on the following table . The run rate will be calculate only on the future months on the field 'im_actual' , and the completed months (Past_Months) the value of 'Im_actual' will be same. The field 'int_period' keep Fiscal months , so the […]
Administration - SQL Server 2014
error while setting up linked error - Hi I get the error Client and Server cannot communicate...they do not posses a common algorithm how to resolve this Regards  
Development - SQL Server 2014
unique index syntax - The following syntax works on 2017 SQL Server, but fails on 2014: create table TESTUNQ (   COL1 int not null , COL2 int not null , index UIQ_COL1_COL2 unique nonclustered (COL1,COL2) ); I am getting: Incorrect syntax near the keyword 'unique' I understand that I could use this syntax: create unique index UQ_TESTUNQ on TESTUNQ […]
SQL 2012 - General
value separate to text and unit not working when value have comma separated mult - I work on SQL server 2012 I face issue : I can't split Value column to Text Unit and Value Unit in case I have on Value column multiple value separated by comma . as example Value ValueUnit TextUnit 1.71V, 2.375V, 3.135V 1.71 V, 2.375V, 3.135V have issue when separate it to text and value […]
SQL Server 2019 - Administration
upgrade SSIS catalog got an error - I move a SSIS database and other user databases  from SQL server 2017 to a new server 2019. Other databases work fine. SSISDB does not work as expected. I restore the SSIS database and master key, successfully. But When I try to run database upgrade from SSMS by right click SSISDB Database upgrade I got […]
SQL Server 2019 - Development
Read from Excel for O365 in SQL Server 2019 - I'm trying to read the contents of an Excel file, so I'm attempting to follow the instructions from this article: https://www.mssqltips.com/sqlservertip/6178/read-excel-file-in-sql-server-with-openrowset-or-opendatasource/ and when I try to run this: use Testdb; GO EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.12.0', N' 'DisallowAdHocAccess'', 1 EXEC sp_configure 'show advanced option', '1'; RECONFIGURE; EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.16.0', […]
SQL Server 2008 - General
OPENROWSET vs OPENDATASOURCE Issues - Hi All, I've recently been working on importing Excel sheets through Ad hoc queries and there is something I found very odd. Below is an OpenRowSet query to extract Sheet1 from TEST.xlsx: SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 XML; HDR = Yes; Database = "F:\SQLTEST\TEST.xlsx";', [Sheet1$]) Now we have an almost identical query using OpenDataSource: […]
General
Using LEFT JOIN as MINUS or EXCEPT (RESOLVED) - The solution is i need to remove the not and add where c.ItemID is null   Ok i'm revisiting this concept again. It is not working. Again, i kinda see why this is not working. But i don't fully understand why it isn't cut and dry.   Why does the following code return all of […]
Analysis Services
Changing Analysis Service Directory - Hi Guys, I need to move the Analysis Services directory found at this location ...\MSAS14.POWERPIVOT Is this possible to do? I've had a read and am confused by some folk saying I have to re-install and others saying its a config option?   Cheers Alex
Integration Services
Deploy error after migrating database from 2017 to 2019 new server - I am working on to migrate databases from a SQL server 2017 to 2019 new SQL server. Other databases work fine. But I have errors when running packages in the SSIS catalog. So I though I  need to upgrade the packages in MS Data tool first. I open VS 2019 data tool, and changed the […]
COVID-19 Pandemic
Daily Coping 29 Jun 2020 - Today’s tip is to appreciate the joy of nature and the beauty in the world around you. http://voiceofthedba.com/2020/06/29/daily-coping-29-jun-2020/
 

 

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

 

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