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

The Minimum Upgrade Point

This editorial was originally published on April 9, 2019. It is being republished as Steve is out of town.

These days the pace of change with SQL Server can be intimidating. Many of us work in disparate environments with multiple versions of the platform in our environment. In the distant past I've had people note that they often have 2 or 3 versions to support. In the last five or six years, when I've asked it seems that many people have 5 or 6 versions to support. That can be challenging in trying to manage your estate as a single entity and understand which scripts will run on which machines.

Recently someone noted on twitter that they tried to convince their company to upgrade to SQL Server 2017, but apparently someone at the company wanted to stick with SQL Server 2014. I asked a question and the person responded that since Dev and QA was at the 2014 compat level, they wanted prod there.

Pedro Lopes, from Microsoft, asked a good question: "How can I help you avert this?" I tend to agree, in that moving to 2014 now, which will fall out of mainstream support in July 2019, is silly. You're installing a system that will be unsupported almost from the beginning. While you will get security patches for a few years, it seems short sighted to start using a version that you may want to use for 10 years.

That got me thinking. What is the minimum upgrade point for your organization? Not the pace or the need, but if you had to pick some random instance to upgrade, where would you go? SQL Server 2017? Something sooner? Perhaps you have some requirements in your organization that limit you from moving to a very new version. I would argue that it's important dev/test are similar to prod, but I'd also say that dev and test ought to upgrade to a recent version as well.

I think I'd specify SQL Server 2016 SP1 as the minimum point. This changed the feature list in Standard Edition, which is what I've often run for systems. We run that now for SQLServerCentral, though our decision last year was a move to 2017. We probably won't upgrade again for many years, but if we did, I think I'd be looking to get close to the most recent version.

What would you do?

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

What is OneLake in Microsoft Fabric?

diponkar.paul from SQLServerCentral

Get ready to be blown away! The highly anticipated Microsoft Build in May 2023 has finally unveiled its latest and greatest creation: the incredible Microsoft Fabric - an unparalleled Data Intelligence platform that is guaranteed to revolutionize the tech world! fig 1: OneLake for all Data One of the most exciting things in Fabric I […]

External Article

GENERATE_SERIES: My new go-to to build sets

Additional Articles from SimpleTalk

I have come across a lot of use cases for manufacturing rows on the fly, aside from the common goal of populating a large data set such as a numbers or calendar table. A few favorites include building sample data, pivoting an unknown number of columns, data extrapolation, and filling gaps in date or time ranges.

Technical Article

PASS Data Community Summit 2023

Additional Articles from PASS

Keynote speakers for Summit 2023 have been announced! Who will you go see on the main stage? If you’re not already on the mailing list, sign up now and be the first to know all Summit 2023 updates.

Blog Post

From the SQL Server Central Blogs - T-SQL Tuesday #164: Code that makes you feel

Steve Jones - SSC Editor from The Voice of the DBA

The invitation this month is from Erik Darling, and it’s a neat one. I like this thought, asking us to find code that impressed us or made us feel...

Blog Post

From the SQL Server Central Blogs - Code that made you feel a certain way : T-SQL Tuesday #164

Kenneth.Fisher from SQLStudies

It’s been a while, but I’m finally participating in T-SQL Tuesday again! This month Erik Darling (blog|twitter) is hosting. Ever ... Continue reading

Microsoft Power BI Quickstart Guide cover

Microsoft Power BI Quick Start Guide: The ultimate beginner's guide to data modeling, visualization, digital storytelling, and more, 3rd Edition

Steve Jones - SSC Editor from SQLServerCentral

Bring your data to life with this accessible yet fast-paced introduction to Power BI, now in color.

 

 Question of the Day

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

 

Which Data Comes First?

In a SQL Server 2022 database, I run this code: 
CREATE USER apiuser FOR LOGIN apiuser WITH DEFAULT_SCHEMA=webapi
GO
ALTER ROLE db_datareader ADD MEMBER apiuser
GO
CREATE TABLE dbo.Location (locationname VARCHAR(20))
GO
INSERT dbo.Location (locationname) VALUES ('dbo schema')
GO
CREATE TABLE webapi.Location (locationname VARCHAR(20))
GO
INSERT webapi.Location (locationname) VALUES ('webapi schema')
GO
CRE
I then log in as apiuser and run this code: 
SELECT * FROM location
What is returned?

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 Left Joins

I have a number of tables with this data:

CREATE TABLE CustomerLeft (CustomerID INT, CustomerName VARCHAR(20))
GO
CREATE TABLE CustomerContact (CustomerID INT, CustomerEmail VARCHAR(100))
GO
CREATE TABLE EmailDomain (CustomerEmail VARCHAR(100), domain VARCHAR(20))
GO
INSERT dbo.CustomerLeft (CustomerID, CustomerName) VALUES (1, 'Steve'), (2, 'Andy'), (3, 'Brian')
GO
INSERT dbo.CustomerContact
  (CustomerID, CustomerEmail)
VALUES
  (1, 'steve.jones@red-gate.com'), (2, 'awarren@sqlservercentral.com')
GO
INSERT dbo.EmailDomain (CustomerEmail, domain) VALUES ('steve.jones@red-gate.com', 'red-gate.com')
GO

If I run this query, how many rows are returned?

SELECT *
 FROM dbo.CustomerLeft AS cl
 LEFT JOIN dbo.CustomerContact AS cc ON cc.CustomerID = cl.CustomerID
 INNER JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail

Answer: 1

Explanation: This query only returns one row. There is only one row from the inner join between CustomerContact and EmailDomain. This one row limits the left join rows to only one that matches. Ref: Joins - https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver16

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
Troubleshoot a application job - Hi All, All of a sudden from yesterday, one of the application job which connects to SQL Server is failing intermittently. The job is scheduled every 15 mins. It sometimes gets executed successfully and sometimes fails. The user shares some generic SQLException and he feels that it is an issue with SQL Server. The front […]
SSPI handshake - Why do we see this SSPI handshake failed with error code? Is this something to concern about?
SQL Server 2016 - Development and T-SQL
Best Practice vs Best Performance of Splitting a Table - Due to the nature of a specific table and the needs of different development groups we need to split a table into two separate but closely related tables. Without getting too indepth on the details of this the basic question came up. Is it best to use the identity key of the primary (or base) […]
Clustered colunstore index vs Clustered date index on partitioned table - Hello everyone.  Was just looking for some input on where I may get the best performance between implementing a clustered columnstore index or a clustered index on a date (ServiceStartDate) on a table also being partitioned by the ServiceStartDate   Since I can only have one clustered index on the table I'm trying to figure […]
Development - SQL Server 2014
Need to find max items within each set - In batches a certain action is done on each order# following specified sequence. Action is marked as +1 (in) and -1(out), within life of each batch there are situations when all orders are out. I need to know max number of items processed within each set, where the set is interval while some orders are […]
QUERY HELP - Hi all I have the following table DECLARE @T table (A int ) insert into @T values (1) ,(0) ,(1) ,(0) ,(1) ,(2) ,(1) ,(0) ,(1) ,(2) ,(3) ,(2) ,(0) ,(1) The results for B column should look like   Which means every time after  A=0  it's a start for new group ranking . Thank […]
SQL Server 2019 - Administration
Downlevel CE for a stored proc - Hello, I am running a sp on sql server 2019. sp_blitzcache reports it using a downlevel cardinality estimator. My database is on compatibility lever 150 and there are no trace flags in the query. What else I could check to find out why it is using a downlevel cardinality estimator and how can I resolve […]
below script with STUFF function executes 2014 but not in 2019 version - Select distinct LabOrderUnique, STUFF( (SELECT DISTINCT '^' + lotr1.TestName FROM [Ncs_conv_IntergyMM_Labordertestresult] AS lotr1 WHERE lotr1.LabOrderUnique = Lotr.LabOrderUnique FOR XML PATH('')), 1, 1, '') as TestName from [dbo].[Ncs_conv_IntergyMM_Labordertestresult] Lotr   Above script i am able to execute in Sql server 2014 in 1 min but not able to execute in 2019 version 6hrs also. but same […]
SQL Server 2019 - Development
Using a comma seperated list in a where clause - I have a table and some incoming JSON as follows: DECLARE @DeviceSerialNumber NVARCHAR(100), @ChannelIDs NVARCHAR(50) DECLARE @Json NVARCHAR(MAX) SET @Json = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}' SET @DeviceSerialNumber = JSON_VALUE(@JSON, '$.SerialNumber') SET @ChannelIDs = REPLACE ( (Replace (JSON_QUERY(@JSON, '$.ChannelIDs'), '[','')) ,']' ,'') select @DeviceSerialNumber, @ChannelIDs this give me the values Serial Number = 940860 and ChannelIDs = '11,12,17,14,13' I […]
Alias for a table that can be used for the duration of a procedure? - I'm not sure if this can be done, but is there a way to use a table alias for the duration of a stored proc, including sub procedures? For example if you wanted to have an alias Customers that could refer to either the DailyCustomers table or the MonthlyCustomers table, perhaps set by a flag? […]
Reporting Services
Power BI Report Server with Oracle data source - We have an Oracle data source configured in Power BI Report Server. We have the Oracle 19c Client installed and configured. We used information from the Microsoft website to configure the server. Works great for a while but then we start receiving the following error when running a report using the Oracle data source - […]
Cannot save the Page Setup to Landscape, or Export to PDF in Landscape - When I change the Page Setup to Legal/Landscape and narrow margins (.25 each side), it runs ok. But when I save the Report and Open it later, the page settings are back to the default Portrait/Letter and margins 1 everywhere. And even when the right Page Settings are in effect, the exported PDF still looks […]
Powershell
Import Xcel file - $File = "C:\CostLoads\Book1.xlsx" $Instance = "xxxxx" $Database = "DBA_USAGE" $fileName = [System.IO.Path]::GetFileNameWithoutExtension($File) foreach($sheet in Get-ExcelSheetInfo $File) { $data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable $tablename = $fileName + '-' + $sheet.Name Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable -Table $tablename } I'm trying to import my xcel file into SQL but receive […]
SQL Server 2022 - Administration
Add DB to a AG - complains about space on secondary - Dear all, I removed a DB from an AG a few days ago to shrink the logs. When I try to add again, I get this error: The share in the secondary has plenty of space, several times the space of the DB and the logs. Any idea or suggestion to fix this problem and […]
SQL Server 2022 - Development
Failed to retrieve server version - Hi Forum, I'm trying to set up Visual Studio (VS) 2022 & SQL Server (SS) 2019 to create SSIS, SSRS & SSAS Projects. I've found that SSIS won't Install in VS if SS is installed, so I've installed VS first, gone to the extensions & installed the SSIS/SSRS & SSAS software. Then I've installed the […]
 

 

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

 

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