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

Daily Coping Tip

Talk kindly to yourself like you would to someone you love

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.

Compatibility Level Confidence

SQL Server databases have had a compatibility level in them for a long time. This is a number that essentially corresponds to a version. We had 70 for version 7.0, and then we went to 80 for SQL Server 2000, 90 for 2005, and we currently are at 150, which you just have to know maps to 2019. I really miss real version numbers.

In any case, there is this statement on the Compatibility Certification page that says this: " As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level, with no need to recertify an application. "

That's a good statement, but it feels soft to me. This doesn't really seem to me that Microsoft wants to stand behind the compatibility level as a guarantee that the way all code works on SQL Server 2017 with compat level 140, is the way it will work on SQL Server 2021 with compat level 140. That pages does say that the queries display the same behavior, and query shapes are protected, but what I, and most people, want to know is that our application will run the same.

What about statistics behavior? What about encryption algorithms (that's already been an issue)?  What about the Cardinality Estimator? In my mind, there are still lots of unknowns, and while I appreciate MS moving in this direction, I'm not sure they are as confident as they'd like you to be in their statement.

I'm sure some of you have used the database compatibility level to upgrade an instance, but keep a database behaving as it would on the prior version. If that's worked well, let us know. If that caused issues, what did you do or was there a workaround? Trace flags, perhaps some database or instance settings might help, and if they did, I'd like to know. In fact, I think Microsoft would like to know, so leave a comment on how you feel, with our without details.

Moving forward, would you consider upgrading the engine, without much testing, and assume that your application will still work well? Do you think vendors would provide support with a different version, but the right compat level? I'm not sure they will.

Steve Jones - SSC Editor

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

 
 Featured Contents

Availability Groups with Docker Containers

rafaelrodrigues from SQLServerCentral

Build a SQL Server AlwaysOn environment with docker

Why you should embed monitoring earlier in your Database DevOps cycle

Additional Articles from Redgate

With insights from a recent Gartner report, Redgate’s Jamie Wallis looks at the benefits of tracking, and acting on, key metrics early in the DevOps process and how they can apply equally to database monitoring.

Understanding SQL Server Recovery Models

Additional Articles from SimpleTalk

The database recovery model controls how a SQL Server database can be backed up and restored. In this article, Greg Larsen explains the three recovery models and what to think about when choosing a recovery model for a database.

From the SQL Server Central Blogs - Prepping the local SSD D: in an Azure VM for SQL Server TempDB

Will Assaf from SQL Tact

One of the few good, safe uses for the local SSD D: volume of an Azure VM is for SQL Server TempDB data and log files. There's a gotcha....

From the SQL Server Central Blogs - Passing array of arrays as a parameter in Azure Data Factory and the role of an @item element in this

Rayis Imayev from Data Adventures

(2020-Sep-13) Array of arrays, in a JSON world, it’s a very common concept to have a set of sets of other elements in a dataset. Despite how strange it may...

 

 Question of the Day

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

 

How Many Rows Remain?

I have written this code. 
DECLARE @founders TABLE
(foundername VARCHAR(50));

BEGIN TRAN;
INSERT @founders VALUES ('Andy');
INSERT @founders VALUES ('Brian');
INSERT @founders VALUES ('Steve');
ROLLBACK;

SELECT * FROM @founders AS f;
How many rows are returned from the SELECT?

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)

Getting the Remote URL in Git

I have been working with a repository in git, and am having an issue. I want to delete the repo and then re-clone it, but I can't remember where the repo is located. I deal with so many, that I'd like to quickly just get the URL. How can I do that in git?

Answer: git remote -v

Explanation: The git remote -v will show you the remote URLs for fetch and push, and you can use these to re-clone the repo. Ref: Git remote - https://git-scm.com/docs/git-remote

Discuss this question and answer on the forums

 

Featured Script

Fiscal/Retail 4-5-4 Calendar Function

Jeffrey Williams from SQLServerCentral

This function returns a 3 year calendar based on a 4-5-4/5-4-4/4-4-5 calendar, also known as a 52/53 week calendar.  The basis of this calendar function was derived from the NRF retail calendar published at https://nrf.com/resources/4-5-4-calendar and the Wikipedia article published at https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar. The calendar function requires 2 helper functions.  The helper functions were derived from […]

CREATE Function [dbo].[fnFiscalCalendar] (
@startYear int
, @restated int = 0
)
Returns Table With schemabinding
As
Return

/* ===========================================================================================
Author: Jeff Williams
Created: 02/06/2020
Description: Returns the Fiscal Calendar for the specified 3 year time period beginning
at @startYear. We only return 3 years as that is consistent with how the
fiscal calendar is published by NRF.

The following columns are returned:

DimDateID calculated YYYYMMDD integer date
FiscalYear
Restated flag indicating whether or not the calendar has been restated
JulianDayNumber
FiscalDate date using the 'datetime' data type
OrdinalDay day number of the year
USDayInWeek 0 = Sunday, ..., 6 = Saturday
ISODayInWeek 1 = Monday, ..., 7 = Sunday
FiscalWeek the fiscal week number for the year (1 - 52/53)
FiscalDayInWeek 1 through 7 based on the fiscal start/end days
FiscalQuarter the 13 (14 for 53 week years) week quarter
FiscalWeekInQuarter the week number for the period (Quarter)
FiscalMonth454 fiscal month based on 4-5-4 calendar
1 = first 4 weeks, 2 = next 5 weeks, 3 = final 4 weeks
FiscalWeekInMonth454 the week number in each month on the 4-5-4 calendar
FiscalMonth544 fiscal month based on 5-4-4 calendar
1 = first 5 weeks, 2 = next 4 weeks, 3 = final 4 weeks
FiscalWeekInMonth544 the week number in each month on the 5-4-4 calendar
FiscalMonth445 fiscal month based on 4-4-5 calendar
1 = first 4 weeks, 2 = next 4 weeks, 3 = final 5 weeks
FiscalWeekInMonth445 the week number in each month on the 4-4-5 calendar

When the calendar is not-restated, any years with 53 weeks will include the 53rd week
at the end of the last month of the 4th quarter as an additional week in that month.
For the 4-4-5 calendar, the 4-5-4 calendar is used with the 53rd week added as the 5th
week in the last month making it a 4-5-5 quarter.

Note: month here is not a calendar month, it is the number of weeks defined for each
calendar type such that each month will have either 4 or 5 equal weeks all starting
and ending on the same day of the week (Sunday through Saturday).

For the restated calendar - the weeks are shifted by one so that week 2 becomes week 1
and week 53 becomes week 52. This allows like for like comparisons - when comparing
2016->2017 the non-restated calendar would be used and for 2017->2018 the restated
calendar would be used.

This function is based on the NRF 4-5-4 Calendar (https://nrf.com/resources/4-5-4-calendar)
and determines the start/end of the fiscal year as the closest Saturday/Sunday to the
end of January.

Other methods of generating the start/end of the fiscal year can be used. To do so, modify
the fsYears CTE to return the appropriate start/end JDN days. This includes changing
the start/end day in week - for example, you can set the start/end day to Monday through
Sunday and the week, month, quarter values will be calculated correctly for that period.

Example - to calculate the start/end as the last Saturday/Sunday of August:

, fsYears (FiscalYear, FiscalStart, FiscalEnd)
As (
Select @startYear + n
, cy.jdn - ((cy.jdn + 1) % 7)
, ny.jdn - ((ny.jdn + 1) % 7) - 1
From (Values (0), (1), (2)) As y(n)
Cross Apply dbo.fnGetJDNfromYMD(@startYear + n, 8, 31) As cy
Cross Apply dbo.fnGetJDNfromYMD(@startYear + n + 1, 8, 31) As ny
)

Example - to calculate the start/end as closest Monday/Sunday to the end of June:

, fsYears (FiscalYear, FiscalStart, FiscalEnd)
As (
Select @startYear + n
, cy.jdn + (6 - ((cy.jdn + 1) % 7)) + 1
, ny.jdn + (6 - ((ny.jdn + 1) % 7))
From (Values (0), (1), (2)) As y(n)
Cross Apply dbo.fnGetJDNfromYMD(@startYear + n, 6, 27) As cy
Cross Apply dbo.fnGetJDNfromYMD(@startYear + n + 1, 6, 27) As ny
)

Note: To calculate the 'closest' to the end of a month, we start at 3 days prior to the end of that
month. This assures that the 'day' we are looking for will be no more than 3 days prior or 3
days after the end of the month (7 total days with the 4th day being the end of the month).

Called From:
Procedures, Views, Functions and Queries

Example Calls:
Select * From dbo.fnFiscalCalendar(2016, 1) Where OrdinalDay = 1 Order By FiscalYear;
Select * From dbo.fnFiscalCalendar(2017, 0) Order By OrdinalDay, FiscalYear;
Select * From dbo.fnFiscalCalendar(2020, 0);

Revision History
Date Edited By Change
---------- --------------- --------------------------------------------------------------
02/20/2020 Jeff Williams Created
=========================================================================================== */

With t (n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (Number)
As (
Select Top (371)
checksum(row_number() over(Order By @@spid))
From t t1, t t2
)
, fsYears (FiscalYear, FiscalStart, FiscalEnd)
As (
Select @startYear + y.n
, cy.jdn + (5 - ((cy.jdn + 1) % 7)) + 1
, ny.jdn + (5 - ((ny.jdn + 1) % 7))
From (Values (0), (1), (2)) As y(n)
Cross Apply dbo.fnGetJDNfromYMD(@startYear + y.n, 1, 28) As cy
Cross Apply dbo.fnGetJDNfromYMD(@startYear + y.n + 1, 1, 28) As ny
)
, jdnDates
As (
Select yr.FiscalYear
, wk.WeeksInYear
, FiscalStart = yr.FiscalStart + (rc.Restated * 7)
, yr.FiscalEnd
, jd.JulianDayNumber
, OrdinalDay = jd.JulianDayNumber - yr.FiscalStart - (rc.Restated * 7)
, USDayInWeek = (jd.JulianDayNumber + 1) % 7
, ISODayInWeek = (jd.JulianDayNumber % 7) + 1
, fw.FiscalWeek
, FiscalDayInWeek = ((jd.JulianDayNumber - yr.FiscalStart - 1) % 7) + 1
, FiscalQuarter = ((fw.FiscalWeek - 1) / 13) + 1 - (fw.FiscalWeek / 53)
, pd.FiscalWeekInQuarter
, FiscalMonth454 = (pd.FiscalWeekInQuarter / 5) + 1
, FiscalWeekInMonth454 = (pd.FiscalWeekInQuarter % 5) + cast(pd.FiscalWeekInQuarter / 5 As bit)
, FiscalMonth544 = (FiscalWeekInQuarter - 2) / 4 + 1 - (FiscalWeekInQuarter / 14)
, FiscalWeekInMonth544 = (FiscalWeekInQuarter - 2) % 4 + 2 - cast(FiscalWeekInQuarter / 6 As bit) + (FiscalWeekInQuarter / 14 * 4)
, FiscalMonth445 = ((pd.FiscalWeekInQuarter - 1) / 4) - (pd.FiscalWeekInQuarter / 13) + 1
, FiscalWeekInMonth445 = ((pd.FiscalWeekInQuarter - 1) % 4) + (pd.FiscalWeekInQuarter / 13 * 4 + 1)
From iTally As t
Cross Apply fsYears As yr
Cross Apply (Values ((yr.FiscalEnd - yr.FiscalStart) / 7 + 1)) As wk(WeeksInYear)
Cross Apply (Values (yr.FiscalStart + t.Number)) As jd(JulianDayNumber)
Cross Apply (Values (@restated & wk.WeeksInYear / 53)) As rc(Restated)
Cross Apply (Values (((jd.JulianDayNumber - yr.FiscalStart - 1) / 7) + 1 - rc.Restated)) As fw(FiscalWeek)
Cross Apply (Values (((fw.FiscalWeek - 1) % 13 + 1) + (fw.FiscalWeek / 53 * 13))) As pd(FiscalWeekInQuarter)
)
Select jd.FiscalYear
, Restated = @restated
, DimDateID = jdn.y * 10000 + jdn.m * 100 + jdn.d
, jd.JulianDayNumber
, FiscalDate = jdn.OutputDateTime
, jd.OrdinalDay
, jd.USDayInWeek
, jd.ISODayInWeek
, jd.FiscalWeek
, jd.FiscalDayInWeek
, jd.FiscalQuarter
, jd.FiscalWeekInQuarter
, jd.FiscalMonth454
, jd.FiscalWeekInMonth454
, jd.FiscalMonth544
, jd.FiscalWeekInMonth544
, FiscalMonth445 = iif(e1.Fiscal445NotRestated = 1, jd.FiscalMonth454, jd.FiscalMonth445)
, FiscalWeekInMonth445 = iif(e1.Fiscal445NotRestated = 1, jd.FiscalWeekInMonth454, jd.FiscalWeekInMonth445)
From jdnDates As jd
Cross Apply dbo.fnGetDateFromJDN(jd.JulianDayNumber) As jdn
Cross Apply (Values (jd.FiscalQuarter / 4 & jd.WeeksInYear / 53 & ~@restated)) As e1(Fiscal445NotRestated)
Where jd.FiscalWeek Between 1 And jd.WeeksInYear;

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 2017 - Administration
restore from azure blob - Good morning all , I must restore my backup from AZURE URL except that I have this error RESTORE DATABASE dba FROM URL = 'https://xxxxxapsbosl01igacrgt88t6tp.blob.core.windows.net/cosnprsbo1/DBA.BAK' WITH CREDENTIAL = 'Backupcredential' ,MOVE N'dba_log' TO N'D:\Logs\dba.ldf' ,MOVE N'B1if' TO N'D:\Data\dba.mdf' ,REPLACE ; i have this error sg 3268, Level 16, State 1, Line 9 Cannot use the backup […]
Backup space - I am trying to get the backup space estimate based on the dev server since I need to know how much is needed in production. However, but we won’t have production levels of change in the diffs or logs since it's DEV. Is there a way to estimate that or just go with some buffer […]
SQL Server 2017 - Development
Creating a merge that contains an IF condition - Hi, want to merge two tables on two columns, partid and serialid. but then I want to check if the purchasedate also match, if it matches I leave it alone, otherwise I update the date and source. i having having issues with the if part. MERGE [dbo].[tableone] T USING [dbo].tabletwo S ON (S.PARTID = T.PART_ID […]
SQL Server 2016 - Administration
Wanting to learn - Hi all   I'm the archetypal "accidental DBA" and it's got me hooked. I've been digging around for courses (preferably on-line) to learn more about it and (hopefully) get some certifications.   There seem to be a lot of courses at various price ranges.   Can anyone recommend some good courses (preferably not too expensive […]
JDBC timeout from one server but not the other - Hello experts, We have a production application server PROD and a development application server DEV. For testing purposes, developers are trying connections to the production SQL Server from PROD and DEV. Let's call the SQL Server SQLPROD. We have run into a strange issue. Specifically: JDBC connection DEV to SQLPROD works JDBC connection PROD to […]
SQL 2012 - General
How to update group no incremental by 1 based on every group from 1 to 3 ? - I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3 meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc .. create table #Replacement ( PartIDC INT, PartIDX INT, FlagStatus nvarchar(50), HasReplacement nvarchar(50), groupId int, step […]
SQL Server 2019 - Administration
Join secondary database to Always On AG errors: Database is in single user mode - I try to join a secondary database to an Always On availability group, and get error "Database "my_database" database is in single user mode which is incompatible with participating in database mirroring or in an availability group. Set database to multi-user mode, and retry the operation." I can't find solution to this from web. I'm […]
SQL Server 2019 - Development
Sql Queries - Hi Team, Can anyone help on this ? I'm new to this sql .  
do a replace in update statement - I have a varchar(130) field called exportpath. I want to replace the word devserver with prodserver where ever it finds that string in the field.  Can it be ran with a select first to see and verify the output before doing the update? data: \\devserver\exporta\filename.txt \\netloc\exporta\devserver\filename.txt After update: \\prodserver\exporta\filename.txt \\netloc\exporta\prodserver\filename.txt Thanks.  
Need help with following weird syntax - I'm working on a tsql parser and hit following allowed syntax: select top 30 * FROM ( table1 s (NOLOCK) LEFT OUTER JOIN table2 r (NOLOCK) ON r.id = s.id cross apply someothertable cp (NOLOCK) ) LEFT OUTER JOIN table3 t3 (NOLOCK) ON t3.id = r.id It seems like it's possible to insert brackets in […]
Reporting Services
After upgrade SSRS - Hi, anyone recently upgraded the SSRS 2019  EXE from -https://www.microsoft.com/en-us/download/details.aspx?id=100122     ? The reason I ask is after I upgraded the SSRS product version , I am unable to connect to the reporting service from SSMS! Though the web URL and reports looks good, and no errors seen in SSRS configuration manager. The error I […]
General
Tool to catalog the scripts. - Hello! I have many procedures and functions in the database that I would like to find easily through any external tool or any utility. I've been using Evernote... but I would like to know if there are other ones specific . Thank you
Analysis Services
Dax help: Shadow context with allselected() - I really need your help.  All of my numbers are wrong in production. I need to create a ratio whose denominator is dynamic and is determined according to the report filter. I created this measure using the allselected function. But I have incomprehensible results when I create other measures from this first measure. I read […]
Integration Services
Ssis memory Advice - I have a 32bit server with 32Gb of ram the sql server 2008r2 server doesn’t have any dbs and the only thing that it runs is SSIS packages what would the ideal memory allocation for the sql server ? It seems that when we restart the server the packages start miraculously and succeeding.  At the […]
General
Writing a software development Proposal. - I need to write a Proposal to a client to develop a small software for them (up to 10K priced). However, I cannot find an example or template for such purposes. Can someone please help? thank you
 

 

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

 

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