SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

A NOT NULL Default

The Voice of the DBA

Daily Coping Tip

Support a local business with a positive online review or friendly message

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 Recommended Software Practices

Many of us work inside an organization that has a process for building and deploying software. We may find our org doing this well, or we may feel our process is poor with lots of room for improvement.

A lot of the discussion around how to be better at building software in the last ten years has been around the philosophy of DevOps. This concept doesn't really prescribe how to build software, but give you goals to aim for. That means you still need to take the ideas of flow, feedback, and learning and decide how you implement them with your staff. What practices do you follow to ensure you can deploy quickly anytime code is done. These can include ensuring you've tested code, getting feedback from customers, and more.

I ran across a post on recommended software engineering practices for an organization. The list includes seven things you should do:

Keep documentation in the code repo
Have a mechanism for test data creation
Use rock solid database migrations
Create templates for new projects
Automate code formatting
Automate a process for new dev environments
Automate preview environments

This is a set of things I often preach to customers as well, especially 2, 3, 6, and 7. I often focus on the database and having curated test data, migrations you can count on and easy setup is important. And, of course, with SQL Prompt, you don't need formatting ;). Just kidding, that's important, too.

These are solid practices, and none of them are that hard to set up, but they do require some discipline and willingness to work as a team and maintain your process across time. Each of these items needs some care and feeding over time to remain relevant and helpful to your staff.

Do you have good software engineering practices? Are you proud of them and would you bring them to a new position? Or perhaps you wish your team would adopt better habits and a different mentality towards building software.

Steve Jones - SSC Editor

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

Featured Contents

PostgreSQL Temporary Table

Shivayan Mukherjee from SQLServerCentral

Learn how temporary tables work in PostgreSQL.

Join us in our castle at SQL Bits, March 15-18

Additional Articles from Redgate

SQL Bits is coming to Wales, home of dragons and D&D, and we’re happy announce we’re supporting them as platinum sponsors again this year. Meet us at our Red Keep and catch sessions by noble Fighter Grant Fritchey, Sorcerer Steve Jones, and more beloved Redgate Paladins and Druids. For a magical 10% discount on your ticket, use 10REDGATE from our spell book.

Resources for PL-900 Microsoft Power Platform Fundamentals Certification Exam

Additional Articles from MSSQLTips.com

This resource includes several links to related to...

From the SQL Server Central Blogs - CONCAT_WS Function Truncates Text

Koen Verbeeck from Koen Verbeeck

I was writing some dynamic SQL that generates some SQL statements to load my facts and dimensions into a data warehouse. Some of those SQL statements can become very...

From the SQL Server Central Blogs - Using SQL Data Compare from the Command Line with a Project

Steve Jones - SSC Editor from The Voice of the DBA

SQL Data Compare (SDC) is a great way to sync data among tables. It’s a software utility analogous to SQL Compare, but working with data rather than schema. I...

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):
	A NOT NULL Default
What setting do I change to ensure when I create a table the columns have a NOT NULL default for this code: CREATE TABLE NotNullTable ( myid 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)

Storing Always Encrypted Keys

What is the storage location for the keys used in Always Encrypted?

Answer: Column Encryption Keys in the database and Column Master Keys in a trusted store outside the database

Explanation: In an Always Encrypted environment, there are two types of keys. The Column Master Keys are stored in a trusted store outside of the database that contains encrypted data. The Column Encryption Keys are stored in the database. Ref: Always Encrypted - https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16

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 - Development and T-SQL

Plan Handle exists but NO QUERY PLAN? - Is this a fully legit situation in the result of the below query to have Plan Handle but NO QUERY PLAN? SELECT * FROM ( SELECT DBName = DB_NAME(ps.database_id) , ObjectName = OBJECT_NAME(ps.object_id, ps.database_id) , cached_time = ps.cached_time , last_execution_time = ps.last_execution_time , last_elapsed_time_secs = ps.last_elapsed_time / 1000000.0 , execution_count = ps.execution_count , PlanHandle = […]

SQL Server 2019 - Administration

TempDB misconfiguration - Bit of a dilemma, I accidentally entered the incorrect sizing of the tempdb files during the installation of a clustered instance. the installation completed but the tempdb partially created the files then ran out of space. The issue is the SQL instal completes but failed on a few steps and now it will not start. […]

SQL Server 2019 - Development

Operating System Error on Bulk Insert - Hello everyone, it's my first post here, and I'm no expert on SQL, so I hope you're able to help, and will forgive my ignorance, I'm keen to learn! I'm trying to import around 100 txt files into a SQL table, for processing ahead of bringing into PowerBI. I've built a query, and a layout […]

Split field to top 4 - I need to split the column 'DiagnosisCodes' into 4 new columns 'Diagnosis1', 'Diagnosis2', 'Diagnosis3' and 'Diagnosis4' for the unique ChartProcedureId's. If they add more DiagnosisCodes, I only need the top 4. Any assistance is appreciated.

SQL Server 2008 - General

Is there a way to have SQL Server be aware of a new file? - Every weekday we receive a file which under normal circumstances gets processed by a SQL Job that runs on a schedule at 7 AM. It's worked this way for years. This file is produced by a partner, who uploads it to a secure FTP site. However, for the last several weeks the file has either […]

SQL Azure - Development

Reducing varchar(max) cols that are in auto generated indexes - Hi all, I inherited an app schema that is shared across 140+ different DBs [one for each client; they all have their own data but the schemas are otherwise exactly the same]. I am their tsql dev, and have been doing a lot of performance enhancements. One thing I'm running into is that one of […]

General Cloud Computing Questions

Azure SQL managed Instance tiers comparison - Hello everyone. I have an azure general purpose managed instance, 8vCore, 1Tb storage running. The average CPU usage is 50%, with 70-75% in peak hours. Now we have a new report that need almost real-time data and since the server is heavy used, the query get a lot of concurrrency with writes tasks. Noted that […]

What are the alternatives of S3 Object Lambda? - Hi all! AWS has a good example of how to manipulate the requested objects from S3 and it is S3 Object Lambda which can be placed as a middleware between Amazon S3 API Consumer and S3 bucket. What are the alternatives of that in Azure, GCP, Alibaba Cloud, Oracle Cloud, RedHat, VMWare, SalesForce, DigitalOcean, etc?

General

SQL Script plug-in - Is the SQL Script plug-in listed here no longer available? SQL Scripts in SSMS Is there any other plug-in that can be used to search SQLServerCentral scripts?

Integration Services

CozyRoc REST Connection and Source - Any CozyRoc REST connection users out there? I'm looking for an explanation of how to set up error handling for 400 and 404 errors so that the source component won't report an error for these but just move on. I'm using some in a loop, passing an item number as a parameter and if there […]

SQLServerCentral.com Website Issues

Warning prompt when writing a reply - When I wrote this reply https://www.sqlservercentral.com/forums/topic/sql-server-database-redunduncy#post-4148195 and pressed submit I got an error "Are you sure you want to do that?" I could be missing the obvious but I'm not sure why the website queried my post? Possibly it's looking at language and it thought my post had a negative tone to it? Unless I'm […]

SQL Server 2022 - Development

Best way to combine records across databases - Data which is distributed over multiple databases needs to be gathered and then queried/filtered. Currenttly a temporary table in which the records are inserted from the different databases is used however it is really slow, is there a faster way to handle it?

SQL Server Database Redunduncy - I am having two databases A and B Both are logging in Same DATA at the same interval of Time But in case of power failure system A it should copy missing data from system B and Vice Versa In case of power failure of System B it should copy the missing data from System […]

Return a set of rows between 2 values (multiple times) - Hello I have a table, a (sample only shown) This has a set of ids in it CREATE TABLE a (id int) INSERT INTO a (id) VALUES (1, 4, 8, 15) I'm trying to create a set (table) that holds the id in column 1 (call it pid) and links all the […]

Update records using previous row and calculated value of current row. - Hi All, I am trying to update a table's column avg_gain based on previous avg_gain and current gain column for RSI calculation. I was able to implement this logic through cursor but it is time consuming, so was trying to see if there are other techniques to achieve it for having performance gains; The Formula […]

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.