SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Even More OUTER APPLY

The Voice of the DBA

Creative Development

I was working with a customer recently that has a development process that both made me cringe and struck me as very creative. In this case, the customer has software they have written when spawning a few databases for each new project that is created. There are three types of databases created for each project, with unique names for the project. The DDL and DML to create these initial databases are stored in a central database as a set of rows in a table.

To make changes to their software, developers create a project (with new databases) and then alter the application and the database to meet the requirements. These changes then need to be captured and applied to a development template database. There is a template for each of the three different databases created for projects. Changes made to these are processed and then added to the central database for new projects, and to upgrade existing ones.

This creates a complex development process with lots of potential for mistakes and simple human error. However, that's the state of the software, and so I've been trying to help them find ways to simplify this as well as make it more robust across time (and staff changes).

The situation got me thinking, however. While they or I might not like the process, I do admire the creativity it took to set this up and build a system that allows custom software to meet their needs for project tracking work. It's a solution that works, albeit one that now looks overly complicated. However, I wasn't part of the initial design or the various evolutions since then. Perhaps I'd have ended up in a similar place, given the knowledge and requirements known at each point in time.

I'm sure many of you have an architecture or a process that is unusual in some way. Perhaps you designed it, or perhaps someone else did, but there was some creativity in building a solution to a problem. Today I'm looking to hear the stories of where you've seen creative solutions in development, either to a programming problem or maybe to a process that manages or deploys your software. Maybe you deal with remote systems that aren't connected. Maybe you work with large numbers of sharded databases. Perhaps you have cultural challenges that require creativity to ensure you can update your software.

Let us know today what sorts of creative development solutions you've seen implemented.

Steve Jones - SSC Editor

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

Featured Contents

Exploring Parameter Sensitive Plan Optimization in SQL Server 2022

Almighty from SQLServerCentral

PSPO (Parameter Sensitive Plan Optimization) is a SQL Server feature that improves query performance by accepting varied data sizes based on the runtime parameter value(s) specified by the customer. It deals with the situation in which a single cached plan for a parameterized query isn't the best option for all potential incoming parameter values. Non-uniform data distributions exhibit this phenomenon. When using PSPO, SQL Server keeps several execution plans for a single query, each one customized for a particular parameter value. With the help of this feature, numerous execution plans for a parameterized query are generated, each of which is tailored for a certain range of parameter values.

SQL Server vs Oracle Query Statistics to Improve Performance

Additional Articles from MSSQLTips.com

In this article, learn how to query cached query statistics and execution plans for Oracle or SQL Server to identify code that can be optimized to improve performance.

PASS Data Community Summit - Early Bird price ends July 26

Additional Articles from PASS

Ready to secure your spot at PASS Data Community Summit? Register by July 26 at the Early Bird rate.

From the SQL Server Central Blogs - Volunteering at Work: Teaching Others

Steve Jones - SSC Editor from The Voice of the DBA

I give a few talks on career topics, and one of these is Branding Yourself for a Dream Job. In the talk, I sometimes tell a story in the...

From the SQL Server Central Blogs - Notes from SQLSaturday South Florida 2023

Andy Warren from SQLAndy

Quick notes on the event this year: Overall a good event and now I have a few months until SQLSaturday in Orlando in October.

Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis

Steve Jones - SSC Editor from SQLServerCentral

Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.

Question of the Day

Today's question (by Carlo Romagnano):
	Even More OUTER APPLY
What does the last query? declare @t table(i int) insert into @t SELECT * FROM (VALUES ('1') ,('2') ,('3') ,(NULL) ) AS V([i]) SELECT t.i --,MSG.msg FROM @t t OUTER APPLY ( SELECT msg = CASE t.i WHEN 1 THEN 'ONE' WHEN 2 THEN 'TWO' WHEN 3 THEN 'THREE' ELSE CAST(1/0 AS VARCHAR(10)) END ) MSG
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)

Finding Identity Columns

I need to do some maintenance on identity columns in my SQL Server 2019 database. What is the easiest way to find out which tables have identity columns?

Answer: Check the sys.identity_columns DMV and join this to sys.objects to get the table name

Explanation: There is a sys.identity_columns DMV that inherits from sys.columns, but filters to just identity columns. You can join this with sys.objects to get the table name as well. Ref:

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 […]

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.