SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values

Greg Larsen from SQLServerCentral.com

When you build applications that store data in SQL Server you will most likely have to store dates and times, and you’ll call functions to do date manipulations. It is important to understand the different date and time data types, and when to use one data type over another. In this level I will be exploring the different date and time data types and discussing when each type is appropriate.

How to Batch Updates A Few Thousand Rows at a Time

Additional Articles from Brent Ozar Unlimited Blog

In this post, Brent explains how to use a combination of two separate topics that he has blogged about recently.

Multiple Regression Model Enhanced with Bagging

Steve Jones - SSC Editor from SQLServerCentral

This article explains what bagging is relative to a basic decision tree model and demonstrates how to apply bagging for better multiple regression model fits to data from SQL Server.

From the SQL Server Central Blogs - Security Updates for SQL Server Jan 2021–CVE-2021-1636

Steve Jones - SSC Editor from The Voice of the DBA

There was a security bulletin (CVE-2021-1636) for SQL Server, an elevation of Privilege vulnerability that could be exploited when an Extended Event session is running. SQL Server has released...

From the SQL Server Central Blogs - These little things about Azure Data Factory

Rayis Imayev from Data Adventures

(2021-Jan-12) Small data that is hard to be noticed but in a long run makes the greatest impact, little things that are not well apricated but eventually become those final...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Reversing a String in Python
I have a string in Python v3.8 that I want to reverse. What code will do this? MyString = "SQLFamily"
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)

The Time Zone Range

I've got a variable that I declare like this on SQL Server 2019:

declare @date datetime2

I need to assign this value from a string that looks like this: '2020-01-10 16:00:00 -07:00'

When I do this, I am concerned that I might lose the time zone value. What value is @date assigned?

Answer: 2020-01-10 16:00:00.0000000

Explanation: The datetime2 type does not store time zone information, so this is lost and the value becomes a "normal" datetime2 value. If this is cast back to a datetimeoffset, the +00:00 value would be added to the end. Ref: datetime2 - https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15

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

Help on turning query - hi there: we are using sql2017 std version. Query plan attached here. I found that sort operator was having the biggest operator cost. Any suggestion to tune this query? I am starting to work on tuning.. please forgive some naïve questions I may post from time to time. Thanks Hui

Exchange Event in SQL Profiler - Hi all. I received a Deadlock Graph and saw that I have Exchange Event in it. I read that it means the problem with parallelism. I have such a query: SELECT * FROM TABLE Account WHERE ID NOT INT (SELECT * FROM TABLE Account WHERE ...) Could this be the reason in the subqueries and […]

Performance - I am assisting with a migration. doing a V to V migration from a legacy environment to the hosted infrastructure. I want to do some baseline testing and wondered if there are some scripts i could run in SQL that i could run on the legacy and on the new environment to compare so I […]

SQL Server 2017 - Development

xp_cmdshell - System Cannot Find the Path Specified - Dear Group: This is my first attempt at using the xp_cmdshell command, but right now, I am trying to run it inside SSMS. We need to create a procedure to unzip files so that we can import the data into our tables. I am trying to use PKWare SecureZip to do this, as I have […]

SQL Server 2016 - Administration

High CPU and plan cache instability - In the past couple of months my SQL Server instance has been experiencing higher than usual CPU usage than before, at times hitting 100% and affecting an application. There have been no recent changes to any SQL Server configurations or options in that time, the OS may have in terms of Patching but that is […]

SQL Server 2016 - Development and T-SQL

Automated Deployment Scripts - I'm wondering what other people are using to create deployment scripts. We have to create a script and deploy to 3rd party tool for deployment to each environment. We use TFS/Jira, so any scripts that get created are associated to a Jira item as well as put in TFS. The issue is, that when our […]

Table Elimination In a View - I'm experiencing a situation similar to the overly simplified example provided here CREATE TABLE dbo.Sales ( DateKey INT ,Quantity INT ) INSERT INTO dbo.Sales VALUES ( 20210101 ,5 ) ,( 20210102 ,15 ) ,( 20210103 ,4 ) ,( 20210104 ,8 ) ,( 20210105 ,23 ) ,( 20210106 ,47 ) ,( 20210107 ,31 ) ,( 20210108 […]

Import JSON in SSIS with Column Row Transformed - I’m attempting to modify the code on this SQL Server Central article "Approaches to Import JSON in SSIS (SQL Server 2016+) Part 2" using the methods described under “Using SQL Functionality”. I'm successfully using this with other JSON files formatted in a more standard way. However, the JSON flat file that I’m tasked with bringing […]

Update large table rows within a group - I have a large employee table with lots of columns and versions and one org lookup table also with some versions. I need to update a column org_id in employee table which is all nulls now with no history. An employee can have multiple versions records each with same empid but same or different rc_code. […]

CAST(NULL AS varchar(1)) - Hello All, I have been tasked to improve a very old SQL code we have and first thing I see (may not necessarily be the candidate for performance improvement though) going through the code is many CASE records with CASE WHEN myColumn IS NULL THEN CAST(NULL AS varchar(1)) .... Can one advise why someone would […]

Development - SQL Server 2014

Tuning Complex Procedures - Hello, I was wondering if anyone has written something like this. Basically I am trying to tune some stored procedures and I am looking for a stored procedure or tool that would allow me to run a problematic stored procedure a specified number of iterations and then aggregate results like CPU, Reads, Writes, Etc to […]

SQL Server 2019 - Administration

SSIS jobs - we are actually not using a file logger for our SSIS jobs, the logging is done to the database. File logging is in addition, I was thinking to log them on the same server instead of shared location. Do you see any issues?

SQL Permissions - My understanding is you can't grant access to users with select, View database and view definitions etc. at instance level without any user databases created on it. Please advise?

authgrowth log during rebuil index - Good morning all , Have any idea how I can reduce disk consumption during the reindexing phase I use the IndexOptimize procedure of the olla script

Analysis Services

SSAS Tabular SSDT DAX Drillthrough Issue - Hello, I've come across a scenario where the drillthrough functionality is not working when multiple dates from a fact table is joined to a single date dimension. If I have a fact table with multiple status dates e.g. Resolved Date, Logged Date and join it to one Date dimension table, one of the relationship would […]

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.