SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Moving Dynamic Data Masked Data to New Locations - Step 5 of the Stairway to Dynamic Data Masking
SQL Server Clustered Index Seek Operation Generates Poor Query Performance
“As DBAs, should we be worried about our jobs because of AI?” and other burning questions
From the SQL Server Central Blogs - T Sql Tuesday 170: Learning From Abandoned Projects
From the SQL Server Central Blogs - Elevate Your Data Leadership: A Unique Intensive Learning Experience
SQL Server 2022 Administration Inside Out

Question of the Day

Query Execution Modes

The Voice of the DBA

The Art of the Code Review

The inspiration for this was a piece about the art of the good code review. Throughout my career, I've seen code reviews grow and change. From formal meetings to automated notifications and asynchronous discussions to complete lip service to the process. I'd like to think that most organizations are beyond the latter and there is some sort of review beyond the developer, but I still see a lack of other eyes looking at code before it's deployed, especially database code.

The article above opens with the idea of why we review code. The main reason is to create ownership, or more specifically, shared ownership. I had never thought of it in these terms, even though I think the ideas of standards and patterns are certainly shared items. Having everyone take ownership not only keeps quality high but could help you share knowledge and also ensure everyone feels a responsibility to safeguard all the code. This also helps everyone keep an eye on the larger picture of the entire codebase.

I know lots of modern application developers are very familiar with pull requests, though I think these are still somewhat rare in the database world. This is a notification that someone would like their code to be put together with everyone else's code. A good code review does start with a good pull request, as the idea is to have enough information to let the reviewer decide if they should approve things. PRs should also be focused, so if you are making a major change in one object and refactoring another, make those two different PRs. That way I can reject one without the other.

Overall I like the suggestions in the post, but I worry about one of them. The author notes that if there are minor changes requested in a comment, you should pre-approve the PR and trust the author to address the issues. In today's very busy world, and with the challenge of changing code once it's deployed to a database, I don't know if I'd follow this for SQL code. Maybe for C#, but if I have to live with your code for a decade, I don't want mistakes deployed that could be prevented.

Steve Jones - SSC Editor

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

Featured Contents

Moving Dynamic Data Masked Data to New Locations - Step 5 of the Stairway to Dynamic Data Masking

Steve Jones - SSC Editor from SQLServerCentral.com

In this level of the Stairway to Dynamic Data Masking we examine how masking affects data movement.

SQL Server Clustered Index Seek Operation Generates Poor Query Performance

Additional Articles from MSSQLTips.com

In this article, we look at why a SQL Server clustered index seek operation might not always be the most optimized approach for executing a query.

“As DBAs, should we be worried about our jobs because of AI?” and other burning questions

Additional Articles from Redgate

We recently launched the State of the Database Landscape 2024 survey results, with information from almost 4,000 database professionals from around the globe. A clear picture emerged from the results, suggesting that 2024 is the year that skill diversification among database professionals is imperative.

From the SQL Server Central Blogs - T Sql Tuesday 170: Learning From Abandoned Projects

alevyinroc from FLX SQL

T-SQL Tuesday is a monthly blog party hosted by a different community member each month. I missed out on January 2024’s edition because I didn’t think I had anything...

From the SQL Server Central Blogs - Elevate Your Data Leadership: A Unique Intensive Learning Experience

James Serra from James Serra's Blog

I’ve collaborated with two industry experts to design a transformative course for data leaders: “The Technical and Strategic Data Leader.” This six-week intensive learning journey is crafted to elevate...

SQL Server 2022 Administration Inside Out

Site Owners from SQLServerCentral

Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Query Execution Modes
What are the two distinct processing modes that the SQL Server Database Engine can use to process T-SQL statements?
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)

Updatable Views II

I have this view:

CREATE OR ALTER VIEW dbo.City 
AS
SELECT TOP 10 
 cn.CityNameID, cn.CityName
 FROM dbo.CityName AS cn
 WITH CHECK OPTION
GO

How can I modify the view to make it is updatable with this code?

INSERT City (CityName) VALUES ('Dillon')

Answer: Remove the WITH CHECK option

Explanation: A TOP clause can be used in a view, but not if the WITH CHECK OPTION is included. Ref: Updatable Views - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16#updatable-views

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

Estimated rollback completion: 0%. Estimated time remaining: 0 seconds - Greetings! I have a process that has been running for over a day (normally it runs about 30 minutes), tried "Kill SPID", it shows "Commands completed successfully." but it is still running. tried "Kill Spid with StatusOnly", it shows "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." The sql […]

Troubleshooting sql issue where multiple sessions are involved - Hi All, We have an application that spawns multiple spid's, runs multiple multiple batches , multiple txns. While troubleshooting a BLOCKING, initially thought to trace one spid and collect the sql statements for that session. But eventually, dev team and we saw multiple spid's are spawned as part of that process and even multiple databases […]

SQL Server 2019 - Administration

SSMS - Consistency in Scripting Options Across Team - Hi, We use SSMS' "generate scripts" feature to script out all DB objects for source control. We have our scripting options dialed in the way that we like them under Tools -> Options -> SQL Server Object Explorer -> Scripting. Historically it had been a small handful of people that go through the process of […]

Filestream Filegroups on state RESTORING - Hello All, I have a question. My SQL Servers are running SQL Server 2019 CU23. I have a setup where i have a logship copy of a production database available on a secondary server. This copy is Standby/Read-Only. This database uses FileStream to store BLOB data. There are several filegroups for the FileStream Data. So […]

SQL Server 2019 - Development

TSQL Pivot Crosstab - Hi, I am doing a crosstab but I am unsure if its a pivot or is there a simpler way of doing it. Create Table TblPerson (ID Int, Name Varchar(50), Child Varchar(50)); SELECT * FROM TblPerson ID, Name, Child The output should look like this, not everyone has equal number of items so some […]

Find dependencies on a table - I am in the process of doing a re-write for my project so I need to find all SP that use a particular table. SS has a tool that lets me do this. How reliable is it? I tried sp_depends and compared the output from it and the above approach...they don't give me the same […]

what does this error mean? - I was running a SP and I got this. This is the first time I have ever received this. What does it mean? How do I fix it so it doesn't happen again? Msg 9002, Level 17, State 4, Procedure dbo.Query, Line 223 [Batch Start Line 2] The transaction log for database 'myDB' is full […]

how to properly index? - I am not sure how to decide on how I should index my tables so the query is able to run as fast as possible. I am fairly new to SS. My queries are all SELECT. Some use GROUP BY but others do not. There are no DELETE/UPDATE. How do I decide which type of […]

Need help read JSON file - Hello, I've JSON file format as following, { "acc_div": [ { "level1_code" : 1104, "level2_code" : "01", "gl_code" : "0900", "description" : "Finance Department - Estate Account ", "short_name" : "FINANCE " }, { "level1_code" : 1106, "level2_code" : "01", "gl_code" : "0933", "description" : "Marketing Department - SGRK ", "short_name" : "MKTG-KKSR " }, […]

when peers maintain a job rdp'd in they corrupt the job - Hi, one of my peers prefers to maintain one specific sql agent job after rdp'ing into that server. I think its probably because he usually wants to then restart the job knowing it will continue running even if he has to shut down his own pc. I'm not sure but i think if you restart […]

import json file - Any idea on how to import a json file into sql server? I've never done one before. I saw some example like below but the file I have had columns report for different sections. SELECT import_data.* FROM OPENROWSET (BULK 'C:\Files\import_data\small_json.json', SINGLE_CLOB) as j CROSS APPLY OPENJSON(BulkColumn) WITH( name varchar (255), object_id varchar (255) , […]

Reporting Services

Upgrading the SQL version - Hi we have an old sql server 2012 box that (among other things) hosts SSRS (front and back end). I need to upgrade the version of SQL. When I do this will SSRS take care of itself or are there other things I need to be mindful of? I have vague memories of doing this […]

General

Career Advice - hello folks, I am posting this question with heavy heart. I have been a SQL server developer through out my career. I love it from all my heart. I love designing efficient database designs based on the business requirement of an existing/new application. I love playing with the data. Problem solving, bug fixing, performance tuning […]

SQL Server 2022 - Administration

Sql 2022 Alway ON - Hi i need to setup a sql server 2022 onprem replicated with sql always on availability group on azure. On prem do I need to setup a cluster or can it be a single vm? then replicate it to azure? canI use ASR to orchestrate recovery? I found some articlesthat says SQL AwaysOn (Recovery Plan […]

Best Practice for full backup and transaction log truncation - I am new to SQL Server DB. Could someone please let me know what would be the best practice to do a full backup and truncate the transaction log. If you could please let me know the strategy you are using in your company, that would give me some idea and help me learn from […]

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.