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

Play a game that you enjoyed when you were younger

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.

Have You Made a Difference?

On New Year's Eve, I was reminiscing about the past decade. I started posting some pictures on Twitter from the past decade, which were really memories for me of what had happened in life. Later, I saw this tweet from the Pittsburgh Pirates, quoting the late Roberto Clemente, a renowned humanitarian. The quote is:

"Any time you have an opportunity to make a difference in this world and you don't, then you are wasting your time on Earth."

The world is in a crisis. Whether you think we are over- or under-reacting, making good or bad choices, or anything else. The reality is that we are fighting a pandemic throughout most of the world, many of us are isolated at home with limited contact with others. Many people are struggling with health concerns, financial issues, job security, and other issues. It's not just their own personal struggles, but the struggles of others can affect us. Loneliness is certainly a big issue for many, even those living with others and missing their routine.

Have you made a difference during this time to others?

It's a simple question, but it's one that weighs on my mind. I wish I were a medical professional and could volunteer. I wish I were trained as a first responder and could do something. I'd be happy to go move boxes or otherwise help people working long hours to help others. I'm somewhat upset and guilty because inaction and reducing the spread is more important than my feelings.

I've tried to make a difference in a few ways, with the daily coping in the newsletter and my own blogging for the things I'm trying. I've hosted some video calls at T-SQL Tuesday live, just providing a space to touch base with friends and the rest of #sqlfamily. I'm proud my company is trying some things and reaching out the community, without an ulterior motive. Our CEO passionately asked that we do things for the community without regard to sales, customers, or any Redgate purpose other than giving back.

This is a time to pull together and help others. Many are scared, many are worried, many are dismissive, but even more are struggling. What can you do to help someone else this week?

Steve Jones - SSC Editor

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

 
 Featured Contents

Automate your Morning Checklist

jpomfret7 from SQLServerCentral

A morning checklist is a good thing, but an automated one is better.

Database Reliability Engineer- The new DBA?

Additional Articles from SQLServerCentral

The role of the DBA is evolving! With automated builds, cloud and DevOps being the new A,B,C,Ds in the day to day management of databases, you need to up-skill and learn about Database Reliability Engineering. Join Microsoft MVP Hamish Watson to find out more.

DevOps is Essential for Analytics

Additional Articles from SQLServerCentral

Database DevOps means accurate and consistent data which is critical for analytics, artificial intelligence, and machine learning.

From the SQL Server Central Blogs - Error handling protip: @@Error resets after the next command

Kenneth.Fisher from SQLStudies

The system variable @@ERROR is a quick and easy way to retrieve the error number from the last statement run ... Continue reading

From the SQL Server Central Blogs - Understanding Disk Performance and IOPS in cloud platform

nelsonaloor from PracticalSQLDba

In the past many years, apart from working as DBA, I was working with multiple cloud technologies and actively involved in the migration process to cloud infrastructure. In between,...

 

 Question of the Day

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

 

Linux Support

Which of these Linux platforms is not supported for SQL Server 2019?

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)

RANKX Parameters

The RANKX DAX function takes a number of parameters. Among them are:

  • Table
  • Expression
  • Value
  • Order
  • Ties

Which of these are required?

Answer: Table and expression

Explanation: Table and expression are required. The others are optional. Ref: RANKX - https://docs.microsoft.com/en-us/dax/rankx-function-dax

Discuss this question and answer on the forums

 

Featured Script

Calculating Easter in SQL

Jeffrey Williams from SQLServerCentral

Several months ago someone commented on a calendar thread that you should not even try to calculate Easter in SQL Server because the math was too hard.  That got me to thinking and I decided to see if that was actually the case. I found that there are several well known algorithms available - but […]

Set Nocount On;
Set STATISTICS time On;

Declare @startYear bigint = 0001
, @endYear bigint = 9999;

With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, inputYears (y)
As (
Select Top (@endYear - @startYear + 1)
(@startYear - 1) + row_number() over(Order By @@spid) As rn
From t t1, t t2, t t3, t t4
)
Select *
From inputYears dd

/* Gauss' Easter algorithm

a y % 19 year's position in the 19-year lunar phase cycle
b,c,k y % 4, y % 7, y / 100 corrections for century years
p (13 + 8*k) / 25 correct for the lunar orbit not being fully describable in integer terms
q k / 4 leap-year exceptions in century years
M (15 - p + k - q) % 30 correct starting point at the start of each century
N (4 + k - q) % 7 starting point for each century
d (19*a + M) % 30 number of days between 21 March and the coincident or next following full moon
e (2*b + 4*c + 6*d + N) % 7) offset days that must be added to make d arrive on a Sunday
o iif(e = 6 And (d = 29 Or (d = 28 And a > 10)), 7, 0) subtract days for 26 April OR 25 April AND a > 10
Add d + e - o to March 22 Easter Sunday (Gregorian calendar)
*/
Cross Apply (
Select * --e7.EasterDay
From (Values (y % 19, y % 4, y % 7, y / 100)) As e1(a, b, c, k)
Cross Apply (Values ((13 + 8*k) / 25, k / 4)) As e2(p, q)
Cross Apply (Values ((15 - p + k - q) % 30, (4 + k - q) % 7)) As e3(M, N)
Cross Apply (Values ((19*a + M) % 30)) As e4(d)
Cross Apply (Values ((2*b + 4*c + 6*d + N) % 7)) As e5(e)
Cross Apply (Values (iif(e = 6 And (d = 29 Or (d = 28 And a > 10)), 7, 0))) As e6(o)
Cross Apply (Values (dateadd(day, d + e - o, datefromparts(y, 3, 22)))) As e7(EasterDay)
) As ge

/* Gauss' Julian algorithm (Orthodox "Eastern" Easter Day)

a y % 19 year's position in the 19-year lunar phase cycle
b, c y % 4, y % 7 leap-year dates
d (19*a + 15) % 30 Paschal Full Moon for this year
e (2*a + 4*b + 6*d + 6) % 7 the Sunday following the Paschal Full Moon
j (y / 100 - y / 400) - 2 the number of days to add to Julian date for Gregorian Calendar
Add d + e to March 22 Julian Easter Date
Add o to Julian Date Orthodox Easter Sunday (Gregorian Calendar)
*/
Cross Apply (
Select *, EasterDay = iif(y < 1783, JulianDate, GregorianDate) From (Values (y % 19, y % 4, y % 7)) As e1(a, b, c) Cross Apply (Values ((19*a + 15) % 30)) As e2(d) Cross Apply (Values ((2*b + 4*c + 6*d + 6) % 7, (y / 100 - y / 400) - 2)) As e3(e, o) Cross Apply (Values (dateadd(day, d + e, datefromparts(y, 3, 22)))) As e4(JulianDate) Cross Apply (Values (dateadd(day, o, JulianDate))) As e5(GregorianDate) ) As oge /* Anonymous Gregorian 'Meeus/Jones/Butcher' algorithm (Western Easter Day) a y % 19 year's position in the 19-year lunar phase cycle b, c y / 100, y % 100 corrections for century years d, e b / 4, b % 4 leap-year exceptions in century years f, g (b + 8) / 25, (b - f + 1) / 3 century-year auxiliary corrections to the new-moon and full-moon dates h (19*a + b - d - g + 15) % 30 number of days between 21 March and the coincident or next following full moon i, k c / 4, c % 4 position of the year in the ordinary leap-year cycle l (32 + 2*e + 2*i - h - k) % 7 number between 0 and 6 which is one less than the number of days before the next Sunday which is definitely after the full moon m (a + 11*h + 22*l) / 451 correction for transfer of full moon from a Sunday to a Saturday m = 1 for the required correction else 0 x h + l - 7*m + 114 h + l - 7*m = number of days between 21 March and Easter x / 31, (x % 31) + 1 the month and day on the Gregorian calendar */ Cross Apply ( Select * --e8.EasterDay From (Values (y % 19, y / 100, y % 100)) As e1(a, b, c) Cross Apply (Values (b / 4, b % 4, (b + 8) / 25)) As e2(d, e, f) Cross Apply (Values ((b - f + 1) / 3)) As e3(g) Cross Apply (Values ((19*a + b - d - g + 15) % 30, c / 4, c % 4)) As e4(h, i, k) Cross Apply (Values ((32 + 2*e + 2*i - h - k) % 7)) As e5(l) Cross Apply (Values ((a + 11*h + 22*l) / 451)) As e6(m) Cross Apply (Values (h + l - 7*m + 114)) As e7(x) Cross Apply (Values (datefromparts(y, x / 31, (x % 31) + 1))) As e8(EasterDay) ) As me /* Meeus's Julian algorithm (Orthodox "Eastern" Easter Day) a, b y % 4, y % 7 leap-year dates c y % 19 year's position in the 19-year lunar phase cycle d (19*c + 15) % 30 Paschal Full Moon for this year e (2*a + 4*b - d + 34) % 7 the Sunday following the Paschal Full Moon o (y / 100 - y / 400) - 2 the number of days to add to Julian date for Gregorian Calendar n (d + e + 114) / 31 the month on the Julian Calendar p (d + e + 114) % 31) + 1 the day of the month on the Julian Calendar Add o to Julian Date Orthodox Easter Sunday (Gregorian Calendar) */ Cross Apply ( Select *, EasterDay = iif(y < 1783, JulianDate, GregorianDate) From (Values (y % 4, y % 7, y % 19)) As e1(a, b, c) Cross Apply (Values ((19*c + 15) % 30)) As e2(d) Cross Apply (Values ((2*a + 4*b - d + 34) % 7, (y / 100 - y / 400) - 2)) As e3(e, o) Cross Apply (Values ((d + e + 114) / 31, ((d + e + 114) % 31) + 1)) As e4(n, p) Cross Apply (Values (datefromparts(y, n, p))) As e5(JulianDate) Cross Apply (Values (dateadd(day, o, JulianDate))) As e6(GregorianDate) ) As ome /* Oudin's Easter algorithm g y % 19 year's position in the 19-year lunar phase cycle c y / 100 leap-year corrections for centuries h (c - (c / 4) - ((13 + 8*c) / 25) + 19*g + 15) % 30 the number of days short to the full moon i h - (h / 28) * (1 - (29 / (h + 1)) * ((21 - g) / 11)) number of days from 21 March to the Paschal full moon j (y + (y / 4 + i + 2 - (c - (c / 4)))) % 7 the weekday for the Paschal full moon (0 = Sunday, 1 = Monday, etc.) m 3 + (((i - j) + 40) / 44) the month on the Gregorian calendar n (i - j) + 28 - 31 * (m / 4) the day of the month on the Gregorian calendar */ Cross Apply ( Select * --e7.EasterDay From (Values (y % 19, y / 100)) As e1(g, c) Cross Apply (Values ((c - (c / 4) - ((13 + 8*c) / 25) + 19*g + 15) % 30)) As e2(h) Cross Apply (Values (h - (h / 28) * (1 - (29 / (h + 1)) * ((21 - g) / 11)))) As e3(i) Cross Apply (Values ((y + (y / 4 + i + 2 - (c - (c / 4)))) % 7)) As e4(j) Cross Apply (Values (3 + (((i - j) + 40) / 44))) As e5(m) Cross Apply (Values ((i - j) + 28 - 31 * (m / 4))) As e6(n) Cross Apply (Values (datefromparts(y, m, n))) As e7(EasterDay) ) As oe /* Oudin's Julian algorithm (Orthodox 'Eastern' Easter) g y % 19 year's position in the 19-year lunar phase cycle c y / 100 leap-year corrections for centuries i (19*g + 15) % 30) number of days from 21 March to the Paschal full moon j (y + (y / 4) + i) % 7 the weekday for the Paschal full moon (0 = Sunday, 1 = Monday, etc.) m 3 + (((i - j) + 40) / 44) the month on the Julian Calendar n (i - j) + 28 - 31 * (m / 4) the day of the month on the Julian Calendar o (y / 100 - y / 400) - 2 the number of days to add to Julian date for Gregorian Calendar Add o to Julian Date Orthodox Easter Sunday (Gregorian Calendar) */ Cross Apply ( Select *, EasterDay = iif(y < 1783, JulianDate, GregorianDate) From (Values (y % 19, y / 100)) As e1(g, c) Cross Apply (Values ((19*g + 15) % 30)) As e2(i) Cross Apply (Values ((y + (y / 4) + i) % 7)) As e3(j) Cross Apply (Values (3 + (((i - j) + 40) / 44))) As e4(m) Cross Apply (Values ((i - j) + 28 - 31 * (m / 4), (y / 100 - y / 400) - 2)) As e5(n, o) Cross Apply (Values (datefromparts(y, m, n))) As e6(JulianDate) Cross Apply (Values (dateadd(day, o, JulianDate))) As e7(GregorianDate) ) As ooe /* New Scientist (30 March 1961) - Modification of the Anonymous Gregorian algorithm a y % 19 year's position in the 19-year lunar phase cycle b, c y / 100, y % 100 corrections for century years d, e b / 4, b % 4 leap-year exceptions in century years g 8 * b + 13 / 25 century-year auxiliary corrections to the new-moon and full-moon dates h (19*a + b - d - g + 15) % 30 number of days between 21 March and the coincident or next following full moon i, k c / 4, c % 4 position of the year in the ordinary leap-year cycle l (32 + 2*e + 2*i - h - k) % 7 number between 0 and 6 which is one less than the number of days before the next Sunday which is definitely after the full moon m (a + 11*h + 19*l) / 433 correction for transfer of full moon from a Sunday to a Saturday m = 1 for the required correction else 0 n (h + l - 7*m + 90) / 25 the month on the Gregorian Calendar h + l - 7*m = number of days between 21 March and Easter p (h + l - 7*m + 33*n + 19) % 32) the day of the month on the Gregorian calendar */ Cross Apply ( Select * --e9.EasterDay From (Values (y % 19, y / 100, y % 100)) As e1(a, b, c) Cross Apply (Values (b / 4, b % 4)) As e2(d, e) Cross Apply (Values ((8*b + 13) / 25)) As e3(g) Cross Apply (Values ((19*a + b - d - g + 15) % 30, c / 4, c % 4)) As e4(h, i, k) Cross Apply (Values ((32 + 2*e + 2*i - h - k) % 7)) As e5(l) Cross Apply (Values ((a + 11*h + 19*l) / 433)) As e6(m) Cross Apply (Values ((h + l - 7*m + 90) / 25)) As e7(n) Cross Apply (Values ((h + l - 7*m + 33*n + 19) % 32)) As e8(p) Cross Apply (Values (datefromparts(y, n, p))) As e9(EasterDay) ) As ne --==== Related Easter Holidays (using Gauss' Western algorithm) --Cross Apply ( -- Select h.IsHoliday -- , h.HolidayDate -- , h.HolidayName -- From (Values (ge.EasterDay)) As e(EasterDate) -- Cross Apply (Values (0, dateadd(day,-46, e.EasterDate), 'Ash Wednesday') -- , (0, dateadd(day, -7, e.EasterDate), 'Palm Sunday') -- , (0, dateadd(day, -3, e.EasterDate), 'Maundy Thursday') -- , (1, dateadd(day, -2, e.EasterDate), 'Good Friday') -- , (0, dateadd(day, -1, e.EasterDate), 'Holy Saturday') -- , (1, dateadd(day, 0, e.EasterDate), 'Easter Sunday') -- , (0, dateadd(day, 39, e.EasterDate), 'Ascension Day') -- , (0, dateadd(day, 49, e.EasterDate), 'Pentecost') -- ) As h(IsHoliday, HolidayDate, HolidayName) -- ) As hd; Set STATISTICS time Off;

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
SSIS Deployment Issue - Hello: Problem:  Cannot deploy SSIS packages to AZURE VM/SQL Server Instance (or execute them via SSDT/VS) from a local laptop using a SQL Login. FYI:  Project Deployment Model is used. Background:  We moved our development SQL Server instance to an AZURE VM, and would like to avoid giving every developer RDP permissions access to the server. […]
SQL Server 2017 - Development
count by week by start/end date - I'm trying to show a count by week but I am unsure of how to find dates between years? How do I show the Year, Week, and count for the example below? This ID_NUM should show 1 for every week for the entire year. I've shown what I've tried so far below. Thanks.   I'd […]
update by quantity - I'm writing an update statement that uses a quantity column to determine how many rows get updated.  I can accomplish this using RBAR, but performance is unusable and I can't figure out how to do this as a set operation. I have a sales table that contains a row for each individual item.  In the […]
SQL Server 2016 - Administration
Convert SQL HA (2 node) to Standalone - Hi, We have some of the SQL Server AlwaysOn environment (NonPROD) where we do not require alwayson any longer. Instead a standalone system will be sufficient. Without provisioning a new environment (standalone), how we can use the existing HA to a standalone and remove one of the other nodes? Does Microsoft has any clearly defined […]
Shrinking _log file for live database - I have a live database in SQL2016 (in Production, in use) that I need to shrink its l_log file which has grown to over 250GB. Can I use this type of command block safely?: ALTER DATABASE ExampleDB SET RECOVERY SIMPLE DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY) ALTER DATABASE ExampleDB SET RECOVERY FULL
SQL Server 2016 - Development and T-SQL
Temporary turn off Transactional Replication? - Hi We get a DB that is replicated every 10 seconds or so We are having an speed issue running SP's against this database. Does anyone know of a command that I could temporary turn off Replication while the SP runs then turn it back on after the SP is completed?   Thanks     […]
Stored Proc slow after switching to Replication? - Hi First, let me apologize in advance for my ignorance on this... We are in the process of switching our reporting DB from backup and restore to transactional replication. I am testing stored procedures and noticing some SP's run much slower in transactional replication. I've done some googling and have tried the three below, but […]
Development - SQL Server 2014
Trouble with Finding Dependencies between Tables using Keys - In the past I could rely on the View Dependencies feature in SQL Server Mgt Studio to show me all objects that an Object depends on or that the object is dependent on. I don't know when this changed but it no longer works reliably.  The View Dependencies works for some parent/Child tables but not […]
SQL Server 2012 - T-SQL
Dynamic Column update. - I would like to update TASK_NXT_RUN_DT  column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated   create table #CFG_PROCESS_EVALUATE_TASK_MSTR ( taskid int, TASK_SCHED_SQL_SCRIPT varchar(500), TASK_LST_RUN_DT datetime, TASK_NXT_RUN_DT datetime ) insert into #CFG_PROCESS_EVALUATE_TASK_MSTR select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043', '2020-04-02 03:17:24.353' […]
Finding a range of date within a range of date - I have a project that requires to display a list of patients within a specific period of dates but having problems building my statement the following is the sample data set. CaseNo     DateAdmitted   DateDischarge 1                 12/01/2019        12/02/2019 2                12/01/2019         12/03/2019 3                12/02/2019        NULL                   - not yet discharge Scenario 1:  getting patients from 12/02/2019 to 12/03/2019 […]
SQL Server 2019 - Administration
Unexcpted erorrs restoring large tables - Hi Please excuse me if this is a basic question, I have been given a .sql file which has a script to create and insert data into a table. The script around 100,000 records in it. Most of the content is in the form 'Insert into table ....' 'insert into table ...' GO Insert into […]
SQL Server 2019 - Development
mass null replace in a query. - I have some data that i have no control over (third party supplying the data). I am extracting it to a CSV but the end user does not want "Null"s they want blank spaces is there a way to do a mass replace on all 36 fields. Case when isnull.... = ' ' end
before update trigger on sql server - Hi everyone, I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and password. I would like to create a trigger on users table so if I insert pairs of values in […]
Integration Services
continue my package even in Failure - Good morning all , I start in ssis and I have a request that I can't find a solution I have a 3 step package (Step1 ----> Step2 ---> Step3) I'm looking to go to Step 3 regardless of the status of step 2 (succed or Failed) Who can help me please on a solution?
SQL Server Newbies
Force job to fail based on condition of IF - I am using SQL Server 2005 and I need to force a job to fail based on a condition of an IF statement. This is an overview what my code is doing: IF (SELECT '1') = 1 BEGIN PRINT 'Yes' END ELSE BEGIN PRINT 'No' END The above isn't my exact code but this gives […]
 

 

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

 

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