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

Good Luck is Needed with Old Versions

Today we have a guest editorial from Grant Fritchey as Steve is away on his sabbatical.

I’ve been extremely lucky in my career. I’ve either been in a job that required me to keep up with the latest technology, or I’ve worked for organizations that wanted to keep up with the latest technology. So, as each, shiny, new bit of functionality was unveiled, I had the opportunity to learn it and put it to work.

That’s not the situation for everyone.

There are people out there still struggling with most of their SQL Server instances on SQL Server 2008. I even ran into a person a couple of years ago who had several thousand instances of SQL Server 7 still running. I worked for an organization that lurked on eBay looking to buy old hardware in order to keep systems built in the 1970s running rather than take the time and trouble to upgrade those systems.

So, I get it. You’re just not going to upgrade because, if it ain’t broke, why go to the time, trouble, and cost of fixing it? Why even bother upgrading at all? SQL Server 2008 is out of support, but it works. Windows 7 just recently was removed from support, but it works. Can’t you just run these things forever? Go to eBay and buy old hardware like my former employer if you have to.

I suppose, for some, that will work. You’ll have SQL Server 2008 running for another 20 years or so, never getting a fully functional Extended Events suite of tools. However, for the vast majority of people, not only is this plan doomed to failure, but it’s probably going to fail sooner rather than later. A security vulnerability is going to be discovered and exploited at your cost. A new set of hardware is going to have incompatible drivers right after a failure of the old hardware. Something is going to happen, and now, instead of a planned upgrade, you’re going into emergency mode.

I do understand not upgrading just because the new shiny has arrived. It’s not a fun position because the new shiny is new and shiny. However, it’s a perfectly sensible approach. Upgrade when you have a clear need and a viable path. On the other hand, I have a tough time understanding why you would refuse to upgrade once it becomes a dangerous proposition. Please, take a moment and tell me why rolling the dice and planning on good luck is the more sensible approach.

Grant Fritchey

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

Redgate University
 
  Featured Contents

Shell Scripting Standards

Imran Quadri Syed from SQLServerCentral

Imran Quadri Syed covers best practices for writing shell scripts.

Queue table issues with Availability Groups in SQL Server

Additional Articles from MSSQLTips.com

I recently came across an issue in a SQL Server Availability Group scenario where queries against a heavily-used queue table were taking longer and longer over time.

The Dangers of using Float or Real Datatypes

Additional Articles from Redgate

Floating point datatypes accommodate very big numbers but sacrifice precision. They are handy for some types of scientific calculations, but are dangerous when used more widely, because they can introduce big rounding errors.

From the SQL Server Central Blogs - Lessons from Iowa’s Caucus Debacle

Tim Mitchell from Tim Mitchell

Earlier this week, the state of Iowa held its caucuses to choose each political party’s nominee for November’s presidential election. Being the first state in each election cycle to...

From the SQL Server Central Blogs - #PowershellBasics: Get input using Read-Host

Kenneth.Fisher from SQLStudies

Conditions are the life blood of programming. This condition is met so now I need to do this task. That ... Continue reading

 

  Question of the Day

Today's question (by Grant Fritchey):

 

Execution Plan Types

How many different execution plan types are there?

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

 

 

  Yesterday's Question of the Day (by Kathi Kellenberger)

Calculate the Days Between Orders

Your SQL Server 2017 database has a Sales table with 100 million rows that contains CustomerID, OrderID, and OrderDate columns. You would like to return a list of orders (all columns). For each order, return the number of days until the next order is placed. For the last order, return a 0 instead of NULL.  Assuming a helpful index is in place, which query will give you that answer and also performs the best?

Query #1

SELECT CustomerID, OrderID, OrderDate, 
   DATEDIFF(DAY,OrderDate, LEAD(OrderDate,1,OrderDate) 
   OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales;

Query #2

SELECT CustomerID, OrderID, OrderDate, 
   DATEDIFF(DAY,OrderDate, LAG(OrderDate,-1,0) 
   OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales;

Query #3

SELECT O.CustomerID, O.OrderID, O.OrderDate, 
   ISNULL(DATEDIFF(DAY,O.OrderDate,NextOrder.OrderDate),0) AS DaysUntilNextOrder
FROM Sales AS O
OUTER APPLY (
   SELECT TOP(1) OrderDate 
   FROM Sales AS I
   WHERE I.CustomerID = O.CustomerID 
      AND I.OrderID > O.OrderID
   ORDER BY I.OrderID) AS NextOrder;

Answer: Query #1

Explanation: In 2012, Microsoft introduced the window functions, LAG and LEAD. LAG returns an expression from a prior row in the partition while LEAD returns a later row in the partition. You can use these functions instead of joining a table to itself when needing expressions from a different row, and the performance is great. The default for LAG and LEAD is one row away from the current row, but by using the optional parameter OFFSET, it will return rows further away from the current row. OFFSET must be 0 or greater, so Query #2 will not run. You can't use LAG to return expressions from a later row in the partition. A second optional parameter DEFAULT, replaces any NULLs returned by LAG or LEAD. Query #1 is the correct answer using LEAD. By using the OrderDate from the current row, NULL will be replaced by zero. Instead, you can use the ISNULL or COALSCE function. Query #3 will return the correct results, but it will not perform well. Ref: Introduction to T-SQL Window Functions

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 2016 - Administration
DIff Backup on secondary replica in AG - Why differential backup is not supported on secondary replica in AG?
How to Insert EventViewer records to SQL Server Table - Hello, I developed the below code to scan through cluster error issues happening in the last 48 hrs. But if I need to insert the reocrds to a table, how to achieve the need? Get-EventLog -LogName "system" -after (((get-date).AddDays(-2)).date) | where {$_.InstanceId -eq 1069 -or $_.InstanceId -eq 1045 -or $_.InstanceId -eq 1205 -or $_.InstanceId -eq […]
Administration - SQL Server 2014
ould not open file I:\DataFiles\Alerts.mdf for file number 1. OS error: 3 - Date,Source,Severity,Message 02/07/2020 08:57:48,spid20s,Unknown,File activation failure. The physical file name "L:\LogFiles\Alerts_log.ldf" may be incorrect. 02/07/2020 08:57:48,spid20s,Unknown,FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'L:\LogFiles\Alerts_log.ldf'. Diagnose and correct the operating system error and retry the operation. 02/07/2020 08:57:48,spid20s,Unknown,Unable to open the physical file "I:\DataFiles\Alerts.mdf". Operating system error 3: […]
Development - SQL Server 2014
Group by the messages based on the Column and retrieve the records - Hello All, First of all, I would like to thank you for going through my topic. So the question is I need to compose the message and send it to the recipients based on the Notification Type (Phone, and Email). For that, I have written the below query which basically composes the part of the […]
SQL 2012 - General
Figure out Source of SPID retrospectively - Hi All I am looking for a way to find the source of a SPID that ran a query last night. I have inherited an SQL environment and there are DBCC queries being ran at 10pm each night. They are not being ran as SQL Agent Jobs or scheduled SP's so I assume they are […]
i get error All queries combined using a UNION, INTERSECT or EXCEPT operator mus - problem I get error All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists I need to display data plus one record display as first row as structure of data the following data is desired result bold row i need to added ItemId IPN […]
SQL Server 2012 - T-SQL
What EXACTLY are brain dumps, are "historical questions" illegal and so the VCE - Hello. This is probalby sensitive question, so i will try to explain it as clear and precise as possible. I've read the exam policies and FAQ of MS Server 70-461 exam, spefically the "exam security and integrity process". I am new at this subject, so please help me out. For what i understand, brian dumps […]
SQL Server 2019 - Administration
MS SQL access from Mac - I am trying to access a local MS SQL server (running in a Docker container) on my Mac. I have no trouble if I use sqlcmd but I cannot get Python to work. Here is the beginning of a script: from sqlalchemy import * engine = create_engine('mssql+pyodbc://userid:password@localhost') con = engine.connect() This results in: pyodbc.InterfaceError: ('IM002', […]
SQL Server 2019 - Development
Selecting the results of a Common Table Expression after using INSERT INTO - Here I have a common table expression followed by INSERT INTO.  After that, I want to SELECT the results so I can see the newly inserted data, however I get an error.  I presume this is because after the first occurrence of SELECT, INSERT INTO, UPDATE or DELETE the CTE can no longer be referenced. […]
T-SQL (SS2K8)
Identifying Empty columns - Hi is there a way to identify empty columns. I'm going through a new database right now and dropping columns that have never been used. I've been generating SQL statements to get the max length value of each column and identifying columns that return NULL If there is an easier way to do this I'd […]
Reporting Services
SSRS 2016 Report Server DBs in a Basic Availability - I have a 2 node cluster on which I need to setup AlwaysOn as standalone SQL instance...since it is a standard edition we would be going with Basic AGs. I can easily get the user databases added into the AGs but I am not sure about how it would be if we add the ReportingServer […]
Cached Report Readiness - Hi, I have a report that is pretty complex and performance is not as good as I'd like it to be. Luckily, report is running of data snapshot, so when snapshot is ready, I'm caching report and set it to Always run this report against pregenerated snapshots. creating a snapshot takes 10 to 15 minutes. […]
SSRS 2012
SSRS Table Display - Wanting to do some complex (at least to me) manipulation of data that looks like this: And massage it into an SSRS table that looks like this: I've made the following attempt: And it is doing something close to what I want, but not quite: In cases where there are more than one fees per […]
Powershell
Calling a SQL Server Agent job - Hi, I was looking for a way or possibility to call a SQL Server agent job using a powershell script. And wanted this powershell script to be in a windows task scheduler job. I am pretty new to PowerShell, and so far I was able to get a script as below: (but does not call […]
Integration Services
Zappysys SSIS Send HTML Email Task (FREE) - Does anyone know if the Zappysys SSIS Send HTML Email Task is free to use in VS and SQL Agent forever or just for 30 days without a purchase? The website suggests it is free but they they talk about a 30 trial for some components. SSIS Send HTML Email Task
 

 

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

 

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