Nullable vs Non-Nullable Columns and Adding Not Null Without Downtime in PostgreSQL (2023-02-03)

SQLServerCentral Newsletter for February 3, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Nullable vs Non-Nullable Columns and Adding Not Null Without Downtime in PostgreSQL]( - [Testing Databases: What’s Required?]( - [Extend your Power BI Reports with Analysis Services 2022 Data]( - [From the SQL Server Central Blogs - Q&A on certification exams, exam prep, and test-taking skills]( - [From the SQL Server Central Blogs - Starting the Flyway PoC–Environment]( - [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Question of the Day - [The NULL Connection Setting]( The Voice of the DBA  Daily Coping Tip Ask other people about things they’ve enjoyed recently 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. The Senior Advantage The senior advantage means more to me all the time, though often I feel there are more disadvantages to being around longer than advantages. Getting older is hard, especially physically, and I struggle with that. Having more wisdom, more tolerance, and more patience, are good things, but I'm not sure I would consciously make that trade. In a business, senior often equates to more experience and time working on systems. Sometimes it means more skill as well, but usually, the internal knowledge of how our systems work inside of our environment is more valuable, even though it can be hard to get management to realize that fact. The last two years have had many technology workers at home, doing the same work they did in an office. Over the last year, there has been contention between some managers that want people to return to the office and many workers that want to remain remote. While lots of people don't have a choice, there are some who do, and they are often the senior, skilled people. [Lots of them quit, which creates a challenge for managers]( to fill their slots. One would think that management would work to retain and keep more employees, but in some cases, the response is to find ways to make it easier to hire new people. One positive thing that happened is the relaxation of the rule for tech workers to have a college degree. That's good for many people that are talented and don't want to spend tens (or hundreds) of thousands of dollars on a degree. A little disheartening to me and others who think that this further devalues the experience of current employees. There are some great employers out there, as well as some great managers that care about their staff. My company is one of them, and I appreciate how we run the business effectively while treating employees fairly. However, there is no shortage of poor employers who do not care much about their people, their training (or re-skilling), or whether staff leaves. They will continue to lean on whoever is still employed while replacing staff with cheaper, and less knowledgeable, new hires. In thirty years, the one thing I've learned is that I need to be responsible for my own career. That means learning often, working on my tech skills, polishing soft skills, and burning no bridges. I need to be prepared and ensure I have opportunities and choices in the future. That's my senior advantage. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Nullable vs Non-Nullable Columns and Adding Not Null Without Downtime in PostgreSQL]( rohind from SQLServerCentral In this article, we are going to talk about nullable and non-nullable columns specifically related to Postgres databases. While the reasons to use nullable or non-nullable columns may seem obvious at first, there are pitfalls associated with each decision that either affect development velocity, affect application performance, or cause downtime. Let's start with definitions. Nullable […] [External Article]( [Testing Databases: What’s Required?]( Additional Articles from Redgate An overview of the challenges of database testing and test data management, reviewing the different types of database test that need to run during development work, what sort of test data they require, and how to manage all the required data sets, during development, in a way that allows rapid cycles of parallel testing. [External Article]( [Extend your Power BI Reports with Analysis Services 2022 Data]( Additional Articles from MSSQLTips.com This tip walks through the steps of how to combine data from SQL Server Analysis Services SSAS 2022 with other data using Power BI Desktop. [Blog Post]( From the SQL Server Central Blogs - [Q&A on certification exams, exam prep, and test-taking skills]( Will Assaf from SQL Tact Part 2 of 2. This blog post is co-authored by Ajayi Anwansedo, PhD and William Assaf, who met and worked together at The Futures Fund, a STEM non-profit which offers introductory coding and... [Blog Post]( From the SQL Server Central Blogs - [Starting the Flyway PoC–Environment]( Steve Jones - SSC Editor from The Voice of the DBA I’ve had a goal to redo my demo environments and get them set up to work for a variety of customers in different places. I decided to do this... [Pro Encryption in SQL Server 2022]( [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Additional Articles from SQLServerCentral This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The NULL Connection Setting I connect in SSMS to SQL Server and run this code. What happens? SET ANSI_NULL_DFLT_ON ON SET ANSI_NULL_DFLT_OFF ON GO CREATE TABLE ANSINULLTest (someid INT) GO 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) Backup Permissions Answer: Explanation: [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 - Development [View Or Stored Procedure]( - Please see my DDL. I would like to create: a) Dimensional table that tracks Primary Accounts’ group movements. An account moves to different groups that are associated with levels of commission paid. Analysts need to follow how they moved over time. Accounts can move back and forth between groups but can only be assigned to one […] SQL Server 2016 - Administration [Roles in cluster]( - Hello! When we failover one node from another, does the roles from the node failover to another node too?  Thanks  SQL Server 2016 - Development and T-SQL [Sampling : Select the sample records]( - hi, I have a data like below where i need to randomize data by review type SInce i have sample size as 5. I need to pull 1 record for each review type until sample size is met so, i will be pulling please let me know. how can i achieve this ? [SQL Table Issue]( - Hi everyone, In below code I am creating a table and inserting some english and non-english text in it. DROP TABLE IF EXISTS Translations; IF NOT EXISTS (select * from sysobjects where name='Translations' and xtype='U') begin CREATE TABLE Translations(id int identity(1,1), not_english nvarchar(500) COLLATE Arabic_CI_AI_KS_WS, english nvarchar(500)) end; INSERT INTO Translations VALUES (N'شخص إرهابي','terrorist person'),(N'جواز […] SQL Server 2019 - Administration [Database Migration]( - can you please provide your feedback on following scenario. i am migrating a database server (Active/passive) cluster to Alwayson (2019). issue is the server name is used in connection strings over 200 machines. i need to retain the server name otherwise based on vendor each workstation software has to reinstalled/reconfigured with servername. old name is […] [Weird Log Backup Failure message]( - I found the same error message in the SQL Error log on 3 dozen different SQL 2019 Standard instances. The message was: BACKUP failed to complete the command BACKUP LOG xxxx. Check the backup application log for detailed messages. They were all Error: 3041, Severity: 16, State: 1. The weird thing is that the database […] [AG failover databases go into Initializing / in recovery]( - We did a failover of an AG and two databases went into Initializing / in recovery. This is on Win2019/SQL Server 2019. I issued the below two commands for each db on the secondary node that has the issue: We have 2 other nodes for the AG so removing the db from the AG will […] [Can't Add Delegation for MSSQLSvc to IIS for AG Listener]( - I have a two-replica SQL Server Availability Group running on Windows VMs in the Government Cloud. The AG is running on a named instance on port 1436. Active Directory is running in the public cloud.  We have been working to get SPNs and delegation set up to allow connectivity to the SQL Server service. We […] SQL Server 2019 - Development [ROUND or CONVERT the output of the POWER function]( - Hello smart people, I am trying to use the POWER function to return data to pass downstream in an ETL process. It's sufficient to return 2 decimal places but I can't seem to ROUND or CONVERT to DECIMAL. Given my data I want to return 3.97 This seems to hint at some type of definitive […] [Context_Info across databases]( - I have a stored procedure that works across databases. So the "integration" database will take data from a temp table and insert that data into the "production" database. There are insert triggers on the table in the "production" database I want to bypass. I'm setting context_info = 0x55555 right before the insert statement (in the […] [Interview Question]( - Hi Team , Today i had an interview call. there was a question asked to me . I could answer the question but the interviewer was asking me to give more optimized solution Question There is a table which has 10 lakh rows . The column grade in the table needs to be update . […] [SQL to Exchange]( - I have seen the articles on this website that talk about connecting Exchange to SQL server for the purpose of sending SQL data out via email and controlling emails, calendars, and tasks...etc.   What I want to do is connect to Exchange to read the response code (550.5.1.10 and such) for outbound emails.  We are […] Connecting [Visual Studio Code - SQL Server (mssql) Version 1.17.0 - Encryption By Default]( - Microsoft is warning of a "BREAKING CHANGE" and indeed this little update took my dev work down for a bit. Hopefully these notes can save someone a lot of time tinkering around. Starting with this update encryption is enabled by default, unluckily the wizard to set up a new connection does not feature an option […] SQL Server 2022 - Administration [what does the backup log init do?]( - what does the backup log init do? [Backup Log Init run again by mistake manually.]( - backup log to int run every hour. If a log init backup is run at midnight, and then at 3:00 am it is run again by mistake manually. Can an error occur later (for example when restoring the database)?   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -