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

Daily Coping Tip

Spend some time on a creative task or activity

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.

Your Code Is Your Standards

The MERGE statement is controversial, though I think you should avoid it. Aaron does as well, and Paul White demonstrates potential issues. Recently I also saw Cyndi Johnson give a few reasons not to use this in your code.
 
However, there's a part at the end of Cyndi's post that says this: " Your code is your standards. Repeat it with me: YOUR CODE IS YOUR STANDARDS."
 
The context is that while you might document why MERGE is only used in certain situations, new developers might look at the code, see it, and not remember the standards.
 
I'm sure many of you think, surely we've documented standards, so everyone ought to know them. Do they? How many standards do you have? Are they consistent or maybe some of them might have changed over time or across projects?
 
I've built standards, and I've often found that while I internalize many of them, I make mistakes. I might not remember certain things. Usually there are a few that are very important, and I remember them. Or I make mistakes a bunch and someone chastises me enough to get me to remember.
 
I do agree that in some sense your code does represent your standards, but I also know that code changes. Habits, patterns, and knowledge change, so I doubt that your entire codebase actually adheres to all your standards at any point in time. It's just in too much flux.

Steve Jones - SSC Editor

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

 
 Featured Contents

Hacking the DTSX (SSIS XML file) to Change Defaults

Steve Rezhener from SQLServerCentral

Introduction According to Microsoft, DTSX is an XML-based file format that stores the instructions for the processing of a data flow from its points of origin to its points of destination, including transformations and optional processing steps between the origin and destination points. In a nutshell, when you are creating your SSIS package, the SQL […]

Checking for Missing Module References in a SQL Server Database Using Flyway

Additional Articles from Redgate

There are certain checks that need to be done after a database migration is complete. One good example of this is the check that a migration script, such as one that merges changes from a branch into main, doesn't cause 'invalid objects' (a.k.a. 'missing references') in your databases. I'll show you how to run this check, using sp_RefreshSQLModule, and incorporate it into a Flyway "after" migration script.

Exploring the Capabilities of Azure Synapse Spark External Tables

Additional Articles from MSSQLTips.com

In this article we explore additional capabilities of Azure Synapse Spark and SQL Serverless External Tables.

From the SQL Server Central Blogs - Storage Tiers What SQL Server DBAs Need to Know

SQLEspresso from SQLEspresso

“One Gerbil, Two Gerbils or Three Gerbils?” is a common DBA joke about server and storage performance. No matter how many gerbils power your storage, you need to know...

From the SQL Server Central Blogs - Two methods of deployment Azure Data Factory

KamilN78 from SQL Player Blog

Azure Data Factory is a fantastic tool which allows you to orchestrate ETL/ELT processes at scale. This post is NOT about what Azure Data Factory is, neither how to...

 

 Question of the Day

Today's question (by Peter Petrov):

 

NULLIF and 0

What is the outcome for the script: 
SELECT NULLIF(0,'0')
SELECT NULLIF(0,NULL)
SELECT NULLIF(0,'')
SELECT NULLIF(1,'')
 

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by BTylerWhite)

Querying the Pandas DataFrame

I've created a Pandas DataFrame in Python using this code snippet:

import pandas as pd

data = {"Name": ["Adam", "Beatrice", "Charles", "David", "Elizabeth", "Frank"],
        "Grade": [84, 91, 73, 45, 97, 77]}

df = pd.DataFrame(data, columns=["Name", "Grade"])

I need to do some filtering. Whose name will not be present after executing the following line?

print(df.query("Grade>=73"))

Answer: David

Explanation: David received a grade of 45, thus excluding him from our result. Charles barely made the cut. The query method returns the columns of a DataFrame and can be used to filter results using boolean expressions. This is among a few of the ways we can filter Pandas DataFrames. We can also use the query method for multiple conditions, such as in the following example:

print(df.query("Grade>=73 and Name=='Adam'"))

Reference: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.query.html

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
Cumulative Updates - I am about 6 versions behind on my cumulative updates, and need to apply the latest one. I have looked for detailed documentation but haven't found any yet. I have a DEV environment and a test environment that I can apply it to first, however, these environments will not have the workload of the production […]
SQL Server 2017 - Development
pagination with case - Have a requirement of case , pagination in Order by clause. It looks something like this. But getting error. how to fix ORDER BY case when (@PageCount <= 0 OR @PageIndex <= 0) then c.fullname else ( c.fullname OFFSET (@PageCount * (@PageIndex - 1)) ROWS FETCH NEXT @PageCount ROWS ONLY ) end    
SQL Server 2016 - Administration
Statistics Update Frequency - What's the best way to know which index statistics need to be updated on a daily basis?  I currently update stats on Friday, but I've noticed SQL is updating some of my stats automatically.  The issue is the auto update of stats doesn't do a full scan, so it causes slowness and bad query plans.  […]
SQL Server 2016 - Development and T-SQL
Copy tables from one db to another db - I have a user that needs to truncate a number of tables in DB2 and then make a backup of some tables from DB1 before a lengthy import process begins. What I would like to do is have the user call an SP that truncates the destination tables in DB2 and then copies the data […]
VBA/VBS Insert Into Always Encrypted Column - Hello~ I work on an application (Azure SQL Server Back End/MS Access Front End), and have been tasked with encrypting a few columns in our back end and ensuring our application continues to function properly. Using the articles I've found here, I've been able to use SSMS's GUI Tool to encrypt the columns in question, […]
Administration - SQL Server 2014
Space Error - Hi Experts, I got error on one of our production database. Could not allocate space for object 'dbo.'.'' in database '' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. […]
Development - SQL Server 2014
Is this a RBAR? - DECLARE @forms TABLE ([Id] int NOT NULL); INSERT INTO @forms ([Id]) VALUES (2481916), (2481917), (2481918), (2481919), (2481920), (2481921), (2481922), (2481923), (2482093), (2482094), (2482095), (2482096), (2482097), (2482098), (2482099), (2482100) DECLARE @versions TABLE ([FormVersion] int NOT NULL, [FormId] int NOT NULL, [CreatedDateTime] datetime); INSERT INTO @versions ([FormVersion], [FormId], [CreatedDateTime]) VALUES (1, 2481916, '2021-01-01'), (2, 2481916, '2021-01-02'), (1, […]
SQL 2012 - General
How to select code type 1900 and 1885 when exist at least one time per part ? - How to select code type 1900 and 1885 when exist at least one time per part ? I work on sql server 2012 I face issue I can't select Parts from table trades that exist at least one time per for code type 1900 and 1885 so i need to make query select and get […]
SQL Server 2019 - Administration
"execute as login" limited to the query window? - Hi! Let's assume I'm doing "execute as login='user1' " and that user 1 only has access to northwind_spp database. If I try to run something under it such as "select * from adventureworks2019" it will give an error that this user1 does not have access to that database. However, if I go to the left […]
How to Export Yahoo mail to Gmail - I want to export my emails from Yahoo Mail to Gmail account.
TDE + Encryption key in Azure Key Vault - Hi all, I have SQL 2019 server with Always On of a few servers. I am using TDE on the DBs with encryption key managed by Azure Key Vault (AKV). From time to time, as every Azure managed service, the AKV is not available for different time periods which causes us to have the DB […]
SQL Server 2019 - Development
Only lists records when StockValue changes - Good day, After running below create and insert scripts, my query should only return the rows in blue color (when StockValue changes from IN to SOLD , SOLD to IN, IN to SOLD) I've listed 2 NR's to help with understanding what I'm looking for. Can anyone help me with this? Thank you
SSIS SQL Agent Job - SQL Step referring other server - Hi there, everyone. I have a bit of a pickle. I need to move all our SSIS jobs from one server to another. The problem is all the databases that are used / queried also reside on the same server. One of the jobs has a step that runs a SQL query inside that actual […]
How to stop a user creating a new table - I'm experiencing a problem where tables in my SQL Server database are getting randomly re-created and all the old records are being lost. I would therefore like to prevent a particular user from being able to create any new tables in the database. Is there a role I could assign to the user to achieve […]
Reporting Services
Subscriptions not showing in jobs on new server? - Hi I'm in the process of moving our Report Server . I the subscription jobs aren't showing under jobs in SSMS. I can create a new one and it shows. I have the ones I moved disabled at the time being. Will they show when I enable them and they run?   Thanks  
 

 

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

 

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