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

Daily Coping Tip

Make plans with a friend and catch up with them

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.

Nested Transactions

One of the very common expectations from many SQL developers involves transactions. Many developers (database or application developers) think they can open a transaction, do something, open an inner transaction (nested), and then commit or rollback the inner transaction separate from the outer one.

If you've worked with explicit transactions and experimented with this a bit, then you know that this doesn't work. Recently Brent Ozar wrote a post on this as he had a client think that committing the inner transaction would release locks. It doesn't.

Knowing whether work gets committed or not is important to data integrity. We often need to ensure that multiple things happen or nothing happens. That's key, and if we want to decide that thing A can happen without thing B, those are two transactions. In most cases, where we'd want the behavior I described at the top, these don't need to be nested. They're just two transactions.

Understanding how data modifications work is important, especially if you work across different platforms and you need to ensure there is some level of durability. Some platforms use different locking strategies, some limit transactions even more, and digging into the details is important.

As technical people, we know there are many ways to solve problems, and we often spend a lot of time ensuring that users of our systems have options. We would assume our users will learn and understand how the options work, which is no different that what we ought to do ourselves. Don't assume. Ensure you know how the database will behave if you depend on it behaving a certain way.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

ChatGPT in SQL Server - Practical examples

Daniel Calbimonte from SQLServerCentral

Introduction ChatGPT in SQL Server In this article, we will learn some basic examples of ChatGPT in SQL Server. In a previous article, we had an interview with ChatGPT an AI chatbot developed by OpenAI. If you do not know how to use it yet, you can go to that article. We will show some […]

External Article

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.

External Article

Snowflake for SQL Server Developers - NULL Functions - Part 2

Additional Articles from MSSQLTips.com

Learn about NULL functions that exist in Snowflake but not in SQL Server like NVL2, ZEROIFNULL, EQUAL_NULL, and IS_NULL_VALUE.

Blog Post

From the SQL Server Central Blogs - Understanding the TOGAF 9 Foundation Exam

K. Brian Kelley from Databases – Infrastructure – Security

When I teach the ISACA Certified Information Systems Auditor (CISA) course, one of the things I walk candidates through is how the test is structured and how much each...

Blog Post

From the SQL Server Central Blogs - What Happens on Azure SQL Database?

Grant Fritchey from The Scary DBA

Last week I posted the results from using Extended Events to snoop on what happens inside an AWS RDS database. This week, I’m taking a look at what happens...

SQL Server 2022 Query Performance Tuning

SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance

Additional Articles from SQLServerCentral

Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.

 

 Question of the Day

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

 

Dropping One Table

I have this DDL trigger on SQL Server 2019: 
CREATE TRIGGER NO_DROP_ImportantTable
ON DATABASE
FOR DROP_TABLE
AS
DECLARE @Table SYSNAME;
SELECT @Table = EVENTDATA ().value ('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
IF @Table = 'ReallyImportantTable'
  ROLLBACK;
GO
A user (with DROP TABLE rights) runs this in SSMS on the database with default settings: 
DROP TABLE dbo.MyNewTable
DROP TABLE dbo.ReallyImportantTable
Both dbo.MyNewTable and dbo.ReallyImportantTable exist. What is the result of this code?

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)

Cross Database Context

I set my context in DatabaseA with this code:

SET CONTEXT_INFO 0x1256698456;

If I change the same connection to DatabaseB, what does this code return?

SELECT CONTEXT_INFO();

Answer: 0x1256698456

Explanation: The context setting is by the session, not the database. Ref: SET CONTEXT_INFO - https://learn.microsoft.com/en-us/sql/t-sql/statements/set-context-info-transact-sql?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 2017 - Administration
waittypes in sql server - Hi All,   We have a UAT sql instance running on SQL 2017 EE CU23. 16cpu's - 256GBRAM max server memory set to 230GB max degree of parallelism set to default 0. At database level MAXDOP is set to 4. Tempdb configuration : 8 files and 1 logfile.   When load testing is done, we […]
SQL Server 2016 - Administration
SQL 2016 updates - Hello, so Microsoft has two Feb 2023 SQL updates listed under the same CVE.  5021128 Security update for SQL Server 2016 SP3 Azure Connect Feature Pack+GDR, and 5021129 Security update for SQL Server 2016 SP3+GDR.  I support three 2016 SQL VMs within VMWare.  Question; why two updates for 2016 SQL, as they both address the […]
SQL Server 2016 - Development and T-SQL
Additional records needed with Using MAX function on Most Recent Record - Hello - I would like to show a record for a patients last visit regardless of what doctor they saw. If patient saw a doctor who is not their primary doctor on their last visit by date, show the record and then show the record of the last time they saw their primary doctor. If […]
Development - SQL Server 2014
Update row using different steptype within same order - Hi All, been a while since I posted. Facing weird calculation, I cant wrap my head around on how to google it. I have a data that looks like this (simplified): Need to calculate Lead Time, rules as follow: For StepType 1: If Actual is empty: Today minus Steptype 10 "schedule" date Actual is not […]
SQL Server 2019 - Administration
Semaphore Errors in Variety of Applications - Hello - about 2 months ago our users started to receive semaphore errors in a variety of applications we run with SQL Server backends, including SSMS.  All of our servers are hosted in Azure and our SQL servers are traditional server setups, we are not using Azure SQL managed instances or databases. Resources on the […]
Download Sql Server 2019 - Hi, I am trying to download Sql Server 2019 enterprise edition, but I am not able to find a link to download the .exe file.   Can someone help me with it?   Thanks.
Right way and SSIS sql server integration services projects instalation - Hi there, i ´m new in this roads, and i don´t see the right topic to wrote this . I'm connected to an instance on a standard SQL 2019 server. I installed SQL Dev 2019 on my pc and visual studio 2022 community, but I can't install ssis because during the installation it doesn't recognize […]
SQL Server 2019 - Development
Join 4 Columns of Date/Time together - Hi Forumers, I have a file with 4 Columns pertaining to Date & Timestamp Values. I want to join them to show the 4 Columns as one Column with forward slashes between the Year/Month/Day and then a Colon between the Year/Month/Day & the Timestamp field. An example is attached, thanks for your help.  
SQL - Grouping SQL output in 5 minute intervals - I have a dataset that has a date column every 1 minute. I wanted to group the information to have the data "organized" every 5 minutes and average the SellCount column. For example, I have 5 records, 1 for minute 1, another for minute 2, another for minute 3, another for minute 4 and finally […]
Reporting Services
how to test https for SSRS reports in SSRS web portal - We have configured to use https on the SSRS server with port 443. so for the web portal URL is   How can I test the https is working or not from SSRS web portal? Thanks
PL/SQL to SSRS: how to migrate report events----- From ORB to SSRS - Hi everyone, I was wondering if there is an equivalent of the "after report" and "before report" events that existe in Oracle Report Builder. In ORB, we can use these events to handle errors or  update parameters and to much thing before or after report execution in PL/SQL. Can anyone tell me if SSRS has […]
Design Ideas and Questions
Help with database design - Hi all, I'vebeen using an excel spreadsheet to capure info on oranistions and vehcile related data and would like to tranfer this into the Microsoft dataverse environment.  As you can probably tell, my database skills aren't as good as my excel skills. I'd be grateful if someone could cast there eyes over the design below […]
SQL Server 2022 - Administration
DBCC error with sys.Syscolpars table - Consistency error in DB. 1 page from sys.syscolpars table is missing. There is no any impact but the thing is can't able to upgrade the DB from 2008 to higher version. Backup restore fails. Old good backups are not available. As this is a sys schema table so needed DAC connection to query it. But […]
SQL Server 2022 - Development
Random Number Generating - Hi All, I need to generate random unique numbers for 10 million records. Here is my requirement The numbers should begin with 11. The minimum length of the number should be 14 The maximum length of the number should be 25 Need to generate the random unique numbers for 10 million records. Thanks Sathish Kumar […]
Please help on SQL Code - You are given a table of people’s openions about places in a city: Create table opinions ( Id int not null primary key, Place varchar(255) not null, Opinion varchar (255) not null ); Each row contains place’s name and an opinion about it, which is eaither recommended or not recommended. Your task is to generate […]
 

 

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

 

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