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

Daily Coping Tip

Try a different radio station or TV show

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.

Building a Production Database

Many of us have worked on a database in some way. We might write the code to build it, or we may support it in production, but often we see it evolve over time to meet new needs and goals. Sometimes it's a smooth journey, but other times it can be bumpy. In any case, many of us don't end up with our designs being documented in academic papers.

I ran across an interesting list from someone that Facebook that built a control plane system (Delos) and write a post about things they learned from building a database. Really, this is a storage system that helps infrastructure teams manage their environment. Since most of us don't work at Facebook scale (billions of users), we might not care, but the list was something to read, as it's broken into various categories. Maybe this is more a list of things you should think about when building software in general, but a few items stuck out to me.

First, the list opens with "keep customers happy; else the rest of this document doesn't matter." That's been one of my key mantras throughout my career. My thoughts are less important than the customers, and I need to keep that priority straight. This helps me to ensure I'm being effective in their eyes, not mine, and helps me decide when I compromise the way I might design and implement something.

The second thing from the Customers sections say "read their code," with the reminder that the customer might not really understand what they want or need, and often just express their use case poorly. I think this might be extremely important for anyone building software. Read between the lines.

The one database item that I think really can matter is linked to storage systems, but I think it applies to databases. "...bias heavily towards consistency and durability rather than availability.." The reasoning being these are harder to fix. While database platforms often handle this well, we often don't take advantage of PKs, FKs, and other items that help ensure consistency in our system. Too often I see people trying to retrofit these items in later when the data is a mess. I really worry about this in systems built on NoSQL platforms.

I like the list, and also the deployment item that notes critical checks should be in the deployment. We want to know if we've made a mistake early on, before our customers notice. I might stress for databases that we often want this done in QA or staging. After all, if something breaks in the data, it can be very difficult to fix.

Steve Jones - SSC Editor

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

 
 Featured Contents
Technical Article

Stairway to Snowflake Level 3 – Configure Snowflake with Your Cloud Account

Steve Jones - SSC Editor from SQLServerCentral

Learn how you can configure a Snowflake account in your cloud database and load data.

External Article

SQL Server RETURN and OUTPUT Clause in Stored Procedures

Additional Articles from MSSQLTips.com

In this article we look how to use RETURN and OUTPUT in a SQL Server stored procedure to get a return value after execution.

External Article

On-Demand: 2021 Accelerate State of DevOps webinar

Additional Articles from Redgate

Redgate is a proud sponsor of this year’s Accelerate State of DevOps Report, by the DevOps Research and Assessment (DORA) team at Google Cloud. Watch our lastest webinar to find out how the future of DevOps will effect you.

Blog Post

From the SQL Server Central Blogs - Azure Synapse Analytics database templates

James Serra from James Serra's Blog

One of the biggest announcements at Microsoft Ignite that seemed to be overlooked by a lot of people was Azure Synapse Analytics database templates, now in public preview. I...

Blog Post

From the SQL Server Central Blogs - 3 Important Queries for Testing Your Availability Group Endpoints

SQLRNNR from SQL RNNR

In this article, I have shown the importance of performing three different validation tests against your Availability Group Endpoints. Each test also demonstrates what can be run in the...

 

 Question of the Day

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

 

Finding the ADF Trigger

What variable gives me the name of the trigger that invoked an ADF pipeline?

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)

A Funny Substring NULL

I have this code:

CREATE TABLE MyTable
(
    txt NVARCHAR(10)
);
GO
INSERT MyTable (txt)
VALUES ('a'),(NULL);
GO
SELECT SUBSTRING(txt, 1, 2)
FROM MyTable;
GO

This returns two values, an "a" and a NULL.

What happens with this code:

SELECT SUBSTRING(NULL, 1, 2), 'NULL'
GO

Answer: An error is returned

Explanation: We actually get this error: Msg 8116, Level 16, State 1, Line 1 Argument data type NULL is invalid for argument 1 of substring function. The problem is that only certain data types are allowed in SUBSTRING. So while you can pass a NULL value in for the first parameter, it needs to be one of the allowed data types. This code works:

SELECT SUBSTRING(CAST(NULL AS VARCHAR(100)), 1, 2), 'NULL'

Ref: Substring - https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-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
SQL Server Vulnerability Assessment for Ransomware review - Hello experts, Does anyone have advice on which, if any, results from the SQL Server Vulnerability Assessment can be used to identify and remediate ransomware risk? https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment?view=sql-server-ver15 Thanks for any help. -- webrunner
SQL Server 2017 - Development
Truncate vs Delete for Bulk Removal of (some not all ) records - EDIT : the table is not 1gb its 1tb   Hi I have a table with 351,495,000 records (about 1TB in size) . I need to remove a bunch of historical data , approx  219,000,000 records. Which be quicker , a delete based on criteria (int column , biggest value 4 digits) , or […]
SQL Server 2016 - Administration
DR test - failover with potential data loss on live server. - Hi All, So I want to test failover to my DR site.  My current set up is 2 x replica in site A (synchronous), 1 in site B (async).   It's a busy live server.  How would you do this? Options I see: Tell client they risk data loss & put them off. Set site A […]
Cannot create SQL AG Listener - HI i am getting this error when i am creating the listener in my AG Cluster resource of type 'Network Name' in clustered role failed. The error code was '0x52e' ('The user name or password is incorrect.').
Restore Database with RECOVER generates new values for identity columns - Hi, We have restored the production backup to a different machine for testing purposes. We found that one of our tables which has auto-increment column marked as "identity" is having different values when compared to Production for the same set of records in the same table. For example, In Production we have a table T1 […]
SQL Server 2016 - Development and T-SQL
Query removing NULLs when not chosen to - I have a result set below from this query: SELECT [CALL_ID], [DATE], [TIME], [CAMPAIGN], [CALL_TYPE], [DISPOSITION], [HANDLE_TIME] FROM A2WH..CallLogCommon WHERE [DATE] = '2021-11-05' AND [CAMPAIGN] = 'Chareleston IB' AND [CALL_TYPE] NOT LIKE '3rd%' ORDER BY [DATE] If I change my query to this to get rid of the word 'Test' in my Disposition column my […]
How to capture particular text from standard column to populate computed column - In SQL Server 2016, While Creating a table I want to populate a computed column that will get a value of E00... or NULL based on the value of the standard title column. So, If the title column contains text E00.. then only the computed column will have the E00.. value else NULL. As shown […]
Rounding issue after decimal in TSQL - HI All, My query is like below: declare @id numeric(17,2) = '123.456' select @id Output : 123.46 Expected output is : 123.45   Please help me on this query.  
SQL Server 2019 - Administration
vmware logical drive set up - Installing SQL Server using different drives for data, tran logs, tempdb and backups has obvious advantages on physical servers. Is there any PERFORMANCE advantages on vmware. I am pretty sure whole server is on 1 data store.
Just got a new DEV server set up How do I load a subset of my production data? - Hi all, The company I'm working for has finally, after a year of me asking, set up a 2019 Standard Edition DEV SQL server for us  so we don't do development in prod anymore. We have seven databases (Only two are important) with associated procs, tables, views, etc...  that I want to move data from. […]
Linked server failed - HI, Here is my issue:   I have 2 SQL 2019 instances and set up 1 linkded server. It works fine in local (remote to the server), but got the error from my workstation: Msg 18456, Level 14, State 1, Line 28 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Domain administrator already setup delegation and […]
Maintenance Plan excludes the AlwaysON database - Dear Friends, Want to ask if its normal for the Maintenance Plans to exclude the database(s) in Always On availability Group ? I setup one and saw it works this way...Thanks in advance for ur valuable comments.   Thanks and Best Regards Arshad
SQL Server 2019 - Development
Why is the MS calculation for SQL Server table size not reflect Storage in SSMS - Hi there We have been referring to the following article, in order to work out the size of a table in SQL Server https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-heap?view=sql-server-ver15 Now summarising the calculation we have the following: Null_Bitmap:                               2 + ((Num_Cols + 7) / 8) Fixed_Data_Size:                       Real (4 Bytes) + DateTime( 6 Bytes) Row […]
SQL Server Newbies
Unable to filter data from the query - Hi, As you know I am a newbie and learning myself how to query the database. I am unable to filter the data from the query below. SELECT DISTINCT a.InvoiceDate AS TransactionDate ,SUM(CASE WHEN a.TrnYear = '2022' AND (a.ProductClass NOT IN ( '_FRT%','CXXX')) THEN a.NetSalesValue ELSE 0 END) AS Dailycheck ,a.ProductClass ,a.SalesOrder,ar.Name FROM TrnDetail AS […]
Reporting Services
SSRS 2019 Prompts for authentication - I have a new SSRS 2019 installation and when users access the site they are prompted to authenticate. Once they do this, everything works fine. However this didn't happen on our SSRS 2016 estate. How can I get this to just open without prompting? I've tried WindowsNTLM and WindowsNegotiate (SPN has been registered) in the […]
 

 

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

 

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