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

Daily Coping Tip

Catch yourself overreacting and take a breath

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.

A Broken Streak

SQL Saturday #1 was in Orlando, as the first actual event that has gotten us to nearly 1000 in just over a decade. I didn't go to the first one, but I did go to SQL Saturday #8 and many more since that time. I've been luck to get back to Orlando a few times, and I look forward to more in the future.

Andy Warren announced that this year's SQL Saturday in Orlando is cancelled. This breaks the longest streak so far, and I'm sad to see that. I wasn't likely to go, though this one is always on my list. However, I was hoping it would go on in some form, but I understand the desire to step back and examine this.

A virtual SQL Saturday isn't quite the same for me. I've presented at one, and submitted to a couple more to support them, but I don't love this format, and I think it becomes hard to make it a special event for your city. I know a few more are planned, and I hope someone runs one with only local speakers for that area.

Since the pandemic hit, and since SQL Saturday #950 in Victoria, I'm not sure anyone has held a live event. I'm not sure when the next live SQL Saturday will be, but I'm hoping that it happens in 2021 sometime. I don't think anyone can plan for this year, but I am hopeful we will find a way to get back to some live interaction next year.

We have until 14 Mar, 2021 to avoid losing a whole year of SQL Saturdays and breaking a streak of having them every year since 2007. I'm confident that if it's safe, someone will organize and run a SQL Saturday as soon as they can. I'm also sure it will be well attended as I'm guessing many of you are looking forward to a live event as much as I am. I just hope I get the chance to speak, because I will certainly be ready for a live audience.

Steve Jones - SSC Editor

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

 
  Featured Contents

Honorifics in the 21st century

Randolph West from SQLServerCentral

As data professionals, we should understand how language and forms of address can impact our data model. In the 21st century, we should also learn how to communicate with colleagues in a respectful and professional manner.

Getting Started with Azure Blueprints

Additional Articles from MSSQLTips.com

Learn about Azure Blueprints, available in the Azure portal, which is a new method of deploying Azure Resources through ARM templates.

What specific deployment data is valuable to you or your organization?

Additional Articles from Redgate

Redgate have assembled a small development team to research and build a capability that not only records your deployments over time, but provides a window into past trends and insights. To achieve the best results we need to understand from you what deployment value should be recorded and displayed. Please help us by completing our short two-page survey.

From the SQL Server Central Blogs - Assume a role with AWS PowerShell Tools

Sheldon Hull from Sheldon Hull

Assume A Role
I’ve had some issues in the past working with AWS.Tools PowerShell SDK and correctly assuming credentials.
By default, most of the time it was easier to use a...

From the SQL Server Central Blogs - Azure DevOps Changing Build Notifications

Steve Jones - SSC Editor from The Voice of the DBA

I don’t do a lot of team builds in Azure DevOps, but I constantly use it for demos. However, I’m often experimenting with things and I break builds regularly....

 

  Question of the Day

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

 

File Backups

I have a SQL Server 2017 instance and database. The database has two filegroups, each with 4 files. One of the files in the ARCHIVE filegroup is offline, but it was recently emptied. I want to backup the ARCHIVE filegroup with a file backup. What happens when I issue the BACKUP DATABASE command for this filegroup?

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)

Costs in Python

I have a dataframe in Python, using pandas. In this dataframe, I have a column called "Cost". I can see this with this code:

sales['Cost'].head()

This returns a few values:

>>> sales['Cost'].head()
0     360
1     360
2    1035
3     900
4     180
Name: Cost, dtype: int64

What does this code do?

sales['Cost'] *= 1.05

Answer: This increases all costs by 5%

Explanation: The *= syntax uses the current value and multiplies it by the value on the right, replacing the value on the left with the result. Ref:

Discuss this question and answer on the forums

 

Featured Script

Exploring SQL Server 2019 Feature – Table Variable Deferred Compilation

yasubmj from SQLServerCentral

Have explored the SQL Server 2019, Intelligent Query Processing Feature – “Table Variable Deferred Compilation”.

The script contains some theory at the top and links to read.
After the theory, there are required queries to run on SQL Server 2019.
This way we can see the feature in action and look at its strengths and caveats.

--Check Version of the server
SELECT @@VERSION
/*
Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)
Oct 28 2019 19:56:59 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)
on Windows 10 Pro 10.0 X64 (Build 18363: )
*/

--Check Compatability of the Microsoft Demo Database - WideWorldImportersDW
SELECT Compatibility_Level
FROM sys.databases
WHERE name = 'WideWorldImportersDW'
GO
--By Default when you restore WideWorldImportersDW, Compat level is 130 as it was created from SQL 2016 onwards.
--WideWorldImportersDW is the Microsoft Demo DB used these days instead of AdventureWorks.

--MICROSOFT DEMO

-- ******************************************************** --
-- Table variable deferred compilation
-- ******************************************************** --

--Bring DB Compatability to SQL 2017 and clear the db plan cache.
USE [WideWorldImportersDW]
GO
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

--Enable Exec Plan
DECLARE @Order TABLE
([Order Key] BIGINT NOT NULL,
[Quantity] INT NOT NULL
);

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > 99;

-- Look at estimated rows, speed, join algorithm
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC;
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO

--Now See the 2019 feature in action.
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

DECLARE @Order TABLE
([Order Key] BIGINT NOT NULL,
[Quantity] INT NOT NULL
);

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > 99;

-- Look at estimated rows, speed, join algorithm
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC;
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO

--We can keep the comptability 150 for the Server and disable this feature on specific db's if needed.

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

SELECT * FROM sys.database_scoped_configurations
GO

--OR Disable for specific Queries.
DECLARE @Order TABLE
([Order Key] BIGINT NOT NULL,
[Quantity] INT NOT NULL
);

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > 99;

-- Look at estimated rows, speed, join algorithm
SET STATISTICS TIME ON
SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));
SET STATISTICS TIME OFF
GO

--PROBLEM!!! :)
--Parameter Sniffing, when parameters have large variations in output, resulting in poor plan choices.

CREATE OR ALTER PROC dbo.usp_TableVariableTest @Quantity INT AS
BEGIN

DECLARE @Order TABLE
([Order Key] BIGINT NOT NULL,
[Quantity] INT NOT NULL
)

INSERT @Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > @Quantity

SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN @Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC
END
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON
EXEC dbo.usp_TableVariableTest @Quantity = 99
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

--Uses cached plan and estimates poorly.
SET STATISTICS TIME ON
SET STATISTICS IO ON
EXEC dbo.usp_TableVariableTest @Quantity = 360
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

--Bring DB Compatability to SQL 2017 and clear the db plan cache.
USE [WideWorldImportersDW]
GO
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

--Create Proc with same logic as earlier, but with temp table.
CREATE OR ALTER PROC dbo.usp_TempTableTest @Quantity INT AS
BEGIN

CREATE TABLE #Order
([Order Key] BIGINT NOT NULL,
[Quantity] INT NOT NULL
)

INSERT #Order
SELECT [Order Key], [Quantity]
FROM [Fact].[Order]
WHERE [Quantity] > @Quantity

SELECT oh.[Order Key], oh.[Order Date Key],
oh.[Unit Price], o.Quantity
FROM Fact.[Order] AS oh
INNER JOIN #Order AS o
ON o.[Order Key] = oh.[Order Key]
WHERE oh.[Unit Price] > 0.10
ORDER BY oh.[Unit Price] DESC
END
GO

SET STATISTICS TIME ON
SET STATISTICS IO ON
EXEC dbo.usp_TempTableTest @Quantity = 99
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

--With Temp Table, with varying data output and with 2019 feature off,
--this SP, makes the right estimations.
SET STATISTICS TIME ON
SET STATISTICS IO ON
EXEC dbo.usp_TempTableTest @Quantity = 360
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

--Final Verdict, Feature is good for legacy code, where lots of table variables were used,
--but if you are designing new code, better to use Temp Tables for varying data outputs/loads,
--and not rely on this feature, as it resolves one issue but brings up another with paramter sniffing
--and poor estimations.

--Yasub

More »

 

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 - Administration
ExecuteScalar: CommandText property has not been initialized - One ASP.NET project keeps on receiving a message below from "Try...Catch...End try" block. System.InvalidOperationException: ExecuteScalar: CommandText property has not been initialized I searched Google but can't get help. Is this SQL script mistake? Please help. --------------------------------------------------------- It points at a function from app below dim iCount as Int32 = 0 dim SQL as string = […]
How to stop database restore that survived reboot showing progress in error log - SQL Server 2016 SP CU7 on Windows Server 2012 R2 Standard.  Backstory: Agent job runs daily that performs restore of database in about 30 minutes (360G MDF).  All of a sudden one day restore still running hours later.  Waited it out.  Ran in 18 hours then several hours later early morning restore ran as normal.  […]
using Powershell scripts to do SQL backups - Hello, hopefully this can be a straight forward question, but at the same time, looking to get some advice and if possible experience from someone that tried this... but as the title says, My company has large databases, about 1.5 tb, and dont want to invest in software, but of course we do have good […]
Monitoring CDC - Hi All I've noticed our CDC capture job causing blocking for hours on end at a time. Digging around, I've found the latency using this script to reach 1775 SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0   I'm hoping that someone can assist me with really figuring out how far behind it actually is. […]
SQL Server 2016 - Development and T-SQL
Nested FOR XML Problem - I have the following data and I want to generate XML where the transactions are nested under the position for the relevant asset. CREATE TABLE #temptable ( [bp_sym] varchar(50), [person_key] varchar(100), [bargain_date] date, [disp_acqu_text] varchar(9), [quantity] decimal(12,2), [cumulative_qty] decimal(12,2), [book_cost_change] decimal(12,2), [cumulative_book_cost] decimal(12,2), [swim_refs] varchar(6), [security_code] char(7), [asset_name] nvarchar(4000) ) INSERT INTO #temptable ([bp_sym], [person_key], […]
SQL 2012 - General
Run same query on multiple servers with sqlcmd and save the output - Hello, I have this 3 lines: sqlcmd -S "Server1" -d -U -P -i query.sql -o result.txt sqlcmd -S "Server2" -d -U -P -i query.sql >> result.txt sqlcmd -S "Server3" -d -U -P -i query.sql >> result.txt If I copy and paste this code to a cmd […]
When run query for only part it take too much time so How to solve this issue ? - I work on sql server 2012 query I face issue : when run query return 10 rows for only one part it take 50 second I try to run it in another time may be pc have more load but it take same time 50 second to return 10 rows for only one part . […]
SQL Server 2012 - T-SQL
Split dates based on Start and End Dates - Hi Team, I'm seeking help on date split, the dates can start between month also. below is the sample code and output. Thank you!   Create Table #DATE_SPLIT ( ID INT, StartDate DATE, EndDate DATE ) INSERT INTO #DATE_SPLIT SELECT 10,'01/10/2020','03/21/2020' UNION ALL SELECT 10,'03/22/2020','12/31/9999' --OUTPUT ID StartDate EndDate 10 '01/10/2020' '01/31/2020' 10 '02/01/2020' '02/29/2020' […]
SQL Server 2019 - Administration
File Paths in sysfiles on Replica Database - Morning Guys, How can I get the correct file path from sysfiles (or anywhere in the database with t-sql) when the db is the read only replica. It appears that sysfiles shows the location of the files on the principle database not on the replica. In this case the are in different locations.   Cheers […]
SQL Server 2019 - Development
create view out of dynamic query from INFORMATION_SCHEMA.COLUMNS - Hi, Is there a way to create a view out if this query below? DECLARE @query nvarchar(max) SELECT @query = STUFF( ( SELECT ' UNION ALL SELECT ''' + TABLE_SCHEMA + ''' AS SCHEMANAME, ' + '''' + TABLE_NAME + ''' AS TABLENAME, ' + 'LEFT(''' + TABLE_NAME + ''', 3) AS SYSTEM, ' + […]
Get multiple values out of xml - Hi I have the following Syntax:   DECLARE @MyXMLString XML SET @MyXMLString = ' Woonkamer + Eetkamer Woonkamer 1 1 11.3 3.86 43.62 2.5 109.05 Zuluft 151 0 1 1 Keuken Open keuken 6 1 3.82 2.42 9.24 2.5 23.1 […]
Reporting Services 2005 Development
Average in a Matrix - Hello I'm using the Matrix Wizard and everything is working right except one total that I modified.  See image: The detail columns total at the bottom and along the row without any problem. But in my last column 'Avg FTE' I'm trying to get an average. The average works across the row, but not at […]
General
Looking for custom T-SQL - I've inventoried all our SQL Servers, Service Accts, etc in a home-grown Inventory DB. Simplified here, my Tables\Cols\Data include: TABLE=ServerName w/ Cols: ServerName, Active TABLE=ServiceAccts w/ Cols: ServerName, SvcAcct, Service ServerName Active SvcAcct Service ========== ====== ======= ======= Server123 N SvcAcct123 SQL Service ServerABC N SvcAcctABC <--- SQL Service ServerABC N SvcAcctABC <--- SQL Agent […]
SSDT
SSIS Expression help - Hi Guys,/ Is anyone can help me convert this SQL to SSIS Expression? SELECT DATEADD(DAY,DATEDIFF(DAY,0,CONVERT(DATE,DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) - 8)), '00:00:00') Thanks in Advance.  
Design Ideas and Questions
Columnstore Index and Nonclustered Index on Table - We have a table definition below with a columnstore index and nonclustered row indices. The data in this table increases 10K per day and in a year can have 400K ColNumber with each having 1 to many ColProperty. This table will be used mostly for reads/reference and hardly have updates. Would it be beneficial to […]
 

 

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

 

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