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

Daily Coping Tip

Bring joy to others. Share something which made you laugh

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.

Looking Back at 2020

In some sense I hate to do this. 2020 was a black swan event for the world, with a pandemic that caused changes all across the world in ways that many of us might never have thought possible.

For the data platform, perhaps one of the more startling changes was the demise of PASS. This organization has been a part of my life for the last 20 years, and while I've had my disagreements, I think they have brought the SQL Server and related communities closer than they might otherwise have been. I hope that any of you that have had dealings with PASS or been to an event will wish the best for the staff at HQ. Anika, Audrey, Craig, Erick, Leeze, Marcella, and others have really helped the community over the years, and I hope I get to see them in the future.

We didn't get a new version of SQL Server, though we got a number of cumulative updates. We also had quite a few updates to their tools, SSMS and ADS. The latest releases included a welcome change. You can now avoid installing ADS with SSMS if you run the installation from the command line. This was something requested and voted on by many people. Glad Microsoft is listening.

The other big thing that changed for many of us was the way we work. Our work environments dramatically changed, in a way that many managers would not have thought could be effective. Many organizations transitioned to remote work in March or April of 2020 and are still embracing the format. A few large tech companies will give people the option to work from home forever, though forever might be changed at some point. Some, like Microsoft, have picked dates next year, which I think is good. Choosing June or July makes sense, as it gives some stability for those with children.

At Redgate we went from requiring most people to be in the office to embracing a remote-first approach from this time onward. Lots of tech professionals have known this was possible, but haven't had the chance until the pandemic forced this upon us. I've seen many people and groups thrive, which shows us that the office might not be quite as important as we thought. We also changed our attitude from coping with this change to thriving under this new paradigm. I don't know what this will mean, and I certainly look forward to the chance to go back to Cambridge, but I'm not sure when that will be possible.

From the database perspective, much of 2020 felt somewhat like a blur. We had breaches, we had minor patches and changes. No big changes, no dramatic evolution of how we work with data, though I know all the cloud vendors would point to all the software releases they've during the year. I am hoping that 2021 starts to feel more normal, even if the pandemic continues to drag us along in the same manner. At least, I hope I adjust more.

Steve Jones - SSC Editor

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

 
 Featured Contents

Stairway to Server Management Objects (SMO) Level 3: The Database Class

laertesqldba from SQLServerCentral.com

Learn how to get started working with SMO and PowerShell.

Feature branches and pull requests with Git to manage conflicts

Additional Articles from SimpleTalk

Feature branching and pull requests are two important concepts when using Git. In this article, Kendra Little explains these patterns and even provides a video to demonstrate.

Join the Redgate Tool Tips Swap

Additional Articles from Redgate

Learn and share tool tips with your peers. Redgate has launched their Tool Tips Swap to help everyone make the most of their Redgate products. To get involved, simply share your top Redgate tool tip for boosting productivity. All tips will be collated into a blog post and shared with the community. Plus, you’ll get the chance to win a 3-month subscription to Pluralsight.

Free eBook: SQL Server Internals: In-Memory OLTP

Press Release from Redgate

In this free eBook, Kalen Delaney explains how Microsoft's 2016 In-memory OLTP engine works. In her book, learn how to use lock- and latch-free data structures to allow non-blocking data processing, and find out how to migrate existing tables to Hekaton.

From the SQL Server Central Blogs - Estimating the storage savings by removing columns with NULL value across the table or marking them as SPARSE

Brahmanand Shukla from SQL Server Carpenter

In the previous article Find columns with NULL values across the table we discussed that storage space can be saved by removing columns with NULL value across the table...

From the SQL Server Central Blogs - Workout Wednesdays for Power BI in 2021

Meagan Longoria from Data Savvy

I’m excited to announce that something new is coming to the Power BI community in 2021: Workout Wednesday! Workout Wednesday started in the Tableau community and is expanding to...

 

 Question of the Day

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

 

ggplot2 mapping in R

Which ggplot2 function will map columns to parts of a chart?

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)

Can I create this view?

Is this view definition valid, why or why not?

CREATE VIEW MyView
AS
WITH cteData (lowerbound, upperbound, letter, pwd)
AS (   SELECT
            SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) AS lowerbound
          , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) AS upperbound
          , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1)
          , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50)
       FROM dbo.Day2 AS d
   )
   , cteSolution (occ, valid)
AS (   SELECT
            LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ
          , CASE
                WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound
                     AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN
                    1
                ELSE
                    0
            END AS valid
       FROM cteData AS d)
SELECT COUNT(*) totalcount FROM cteSolution WHERE valid = 1;
GO

Answer: Yes

Explanation: This is a valid view definition. Ref: CTE - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-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 2016 - Administration
Updating SQL Server 2016 - We are currently running SQL Server version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) Can we upgrade straight to CU15 for CU12? Or do we have to do each version in between? […]
Change name of a particular step in an existing SQL Agent Job - What is the easiest way to change name of a particular step in an Agent Job? SSMS does not seem to allow to do that.  
Could not find a login matching... when access granted via AD group - Hello experts, A client just reported the following puzzling error. There is a user ABC\user1 that was added to the AD group ABC\foo_db_readers. The group ABC\foo_db_readers was added to the user-defined database role db_foo_datareader, which has read access to foo_db. The client's goal with this setup is to avoid having to create the login ABC\user1 […]
always on sql server - Hi Guys, I have a question to configure always on sql server same subnet vs multi subnet. It is three node cluster, two in same DC  HA (node 1, node 2) and other in DR (Node 3). It needs a multi subnet, I am thinking to configure node1 & 2 as well into different subnet, […]
SQL Server 2016 - Development and T-SQL
Converting MONEY - Hi, running this declare @code VARCHAR( 50 ) = '748.019876'; select [code1] = @code, [code2] = CONVERT( MONEY, @code ), [code3] = CONVERT( VARCHAR( 10 ), CONVERT( MONEY, @code ) ) returns --code1                   code2              code3 --748.019876        748.0199    […]
Extracting SELECT statement from queries. - Hi All, I have a need to replace, in various views, the SELECT statement that produces the results (SELECT --> SELECT TOP 10). These queries could be in any of the following formats: --1) single select SELECT * FROM test --2) CTE with single select WITH test AS (SELECT 1 a), test2 AS (SELECT 2 […]
Administration - SQL Server 2014
Full Text Search and Enablement at DB Level - Day One. - I have 2 DBs - they are 'vendor' DBs - part of an application. They both originated from a single source DB - so were identical to start with. One DB has been upgraded and has new functionality from the vendor that utilises full text search - the other hasn't. The SQL instance has Full […]
Development - SQL Server 2014
While loop - CREATE Table TABLEA(N varchar(50),Q int,R int,Dt1 datetime,Dt2 datetime) DECLARE @I int = 1 DECLARE @R int = 2 DECLARE @Q int = 3 DECLARE @Q1 int SET @Q1 =@Q DECLARE @N varchar(100) SET @N = 'NameA' WHILE (@I <= @R+1) BEGIN WHILE (@I <= @Q) BEGIN SELECT @N as Name,@I,@R,@Q SET @Q = @Q - […]
SQL 2012 - General
How to get parts that not have same features count on table settings by code typ - How to get parts that not have same features count on table settings by codetypeid and PLID ? I work on sql server 2012 . if face issue I can't get parts that not have all features on table settings so I will count distinct features from table settings and compare it with count features […]
How to copy Excel file from Office 365 online to local drive in SSIS? - Can anybody help me with problem how to copy Excel file from Office 365 SharePoint online to local drive in SSIS? I never worked with SharePoint before, what is the best practices? Thanks, Aleks
Using cast inside sum or outside ? - I work on sql server 2012 I face issue using cast first or sum ? cast (sum(a) as decimal(18,2) or sum(cast (a as decimal(18,2))) which is correct and more speed a is integer need to convert to decimal so using cast inside sum or outside
SQL Server 2019 - Development
Error: Msg 105070, The ODBC data source name was no specified or the driver is m - Hello everybody, I am trying to use external table with PolyBase for import data from Oracle to Sql Server 2019. I have an issue about creating an external table. I created external data source OracleExternalData. CREATE EXTERNAL DATA SOURCE OracleExternalData WITH ( LOCATION = 'oracle://145.145.145.145:1521', CONNECTION_OPTION = 'DSN=CData Oracle Sys' CREDENTIAL = OracleProxyAccount ) ; […]
Azure Data Factory
How to export from sql server table to multiple csv files in Azure Data Factory - I have a clients table in sql server that containes client name and city . In Azure Data Factory , how can I export this table to multiple csv files that each file will contain only a list of clients from the same city , which will be the name of the file
Strategies and Ideas
Filtering Customer Specific Data - Hi all! Your help would be appreciated. We have a data warehouse that is a single tenant design with multiple reports and widgets that query the data The underlying structure of the DW is modelling invoice data - so essentially header records and line items The widgets and reports show pretty much what you would […]
Article Discussions by Author
Download SQL Patch Info - Comments posted to this topic are about the item Download SQL Patch Info
 

 

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

 

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