SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

T-SQL Language Changes in SQL Server 2022 Part 3

Frank Dolan from SQLServerCentral

Learn about more of the T-SQL language changes in SQL Server 2022. This article is the third in a series and will cover the changes to the JSON functions.

Secure File Transfer Protocol (SFTP) support for Azure Blob Storage

Additional Articles from MSSQLTips.com

In this article, we walk through the steps and examples of how to use Secure File Transfer Protocol (SFTP) when working in Azure.

Enabling digital transformation – and data modernization – with DevOp

Additional Articles from Redgate

Where does data fit into the digital transformation story? Discover how and why it deserves a place in this fascinating piece from Pramod Sadalage, principal consultant at Thought Works

From the SQL Server Central Blogs - T-SQL Tuesday #157- “End of Year Activity” – RECAP

gbargsley from GarryBargsley

I am humbled that many posts were submitted for T-SQL Tuesday #157. There is such a wide range of plans to wind down 2022. So enjoy the posts below...

From the SQL Server Central Blogs - A SQL Compare Picklist

Steve Jones - SSC Editor from The Voice of the DBA

Recently a customer was trying to sync up production and development. They’d somewhat lost control of both environments and wanted to build a plan of how to sync them....

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The New Year 2023
This weekend is the end of 2022 and the start of 2023. I hope you celebrate responsibly, but to get you started, I wonder how long it's been since I started working with SQL Server. I set up my first production database on Dec 31, 1991, 1800, in Virginia. Given I am in Colorado, how many days will it be for me when Jan 1, 2023 starts at midnight on Saturday? Note: Virginia is Eastern Standard Time on Dec 31 and Colorado is Mountain Standard Time.
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 Invalid Synonym

Can I create a synonym for an object that doesn't exist?

I have this code. Does it work?

CREATE TABLE dbo.NewTable (myid INT)
GO
DROP TABLE dbo.NewTable
GO
CREATE SYNONYM MyNewTable FOR dbo.NewTable
GO

Answer: All three statements work

Explanation: All three statements work. Synonyms do support deferred name resolution. The documentation notes "the base object does not need to exist at synonym creation time." Ref: CREATE SYNONYM - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-synonym-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Featured Script

Remove Leading Zeros in any situation T-SQL

cusvenus from SQLServerCentral

A quick script that removes leading zeros in a numeric stored as a string.

--Example 1: - Starts with Zero
--=========
declare @String varchar(30)
select @String = '00001234'
select case when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) end
GO
--Example 2: - Starts with Text but still has leading zeros
--=========
declare @String varchar(30)
select @String = 'TEST00001234'
select case when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''))) end

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 - Development

A question about stored procedures and Transactions - I have been trying go get an answer to this question for sometime now. It has to do with transaction handling and stored procedures. I can best explain with an example. Suppose I have two stored procedures: Insert_TableA and Insert_TableB. One inserts to TableA and the other to TableB. There are times when I need […]

SQL Server 2016 - Administration

Any way to store successful logins in a different event log? - Hello experts, I am running into an issue where log information in the Windows Application event log gets rotated out too quickly. Our Systems team tells me this is because the SQL Server in question is set to log both successful and failed logins. And it is true that I see tons and tons of […]

SQL Server 2016 - Development and T-SQL

Group by producing different results - So, I have two sets of code which should ideally produce the same results. The first set of code works with just Group by on 'ts_originationopportunity' SELECT ts_originationopportunity, STRING_AGG(distinctreferrals.referralcompanycontact,'; ') AS referralcompanycontacts FROM (SELECT DISTINCT ts_originationopportunity, ISNULL(ts_referralcontactname + ' at ','') + ts_referralcompanyname AS referralcompanycontact FROM ts_referralsource) distinctreferrals GROUP BY ts_originationopportunity However, the second set […]

Code Modification - with Sub Query - Hi Community, Can someone show me how to modify this code without the use of Sub-Queries. SELECT activityid, concat_ws(ts_inititals, ', ') AS attendees FROM (SELECT activityparty.activityid, systemuser.systemuserid, systemuser.ts_inititals FROM baseorigination.activityparty LEFT JOIN baseorigination.systemuser ON activityparty.partyid = systemuser.systemuserid GROUP BY activityparty.activityid, systemuser.systemuserid, systemuser.ts_inititals ) attendees GROUP BY activityid Pleaes let me know if you need sample […]

Remove Issuance and Expiry dates - Hi everyone, I have a sql table in which data looks like this: I have to modify two columns (natid and passport) data so that it would only show the actualy natid and passport numbers. For e.g natid and passport of ABC1 would show ID-123 and PASS-456 when using select query. issuance and expiry dates […]

Does a Partition Switch to an empty table appear as tranlog delete? - i have a table that has been partition by month and year. the table has CDC tracking enabled on it, and a program called HVR is being used to read the transaction logs for tables using CDC for changes. it sends the changes to parquet files, where we can consume that data elsewhere. if we […]

Development - SQL Server 2014

Trouble with date values - SQL just goes and goes and returns no records if I un-comment this date line: AND cp.from_service_date BETWEEN d.eff_from AND ISNULL(d.eff_thru,'12/31/9999') FROM --#baseFilter jBaseClaim --INNER JOIN dbo.claim_procedure cp ON cp.claim_procedure_id = jBaseClaim.claim_procedure_id dbo.claim_procedure cp INNER JOIN dbo.claim c ON c.claim_id = cp.claim_id INNER JOIN dbo.claim_procedure_2 cp2 ON cp2.claim_procedure_id = cp.claim_procedure_id INNER JOIN dbo.type_of_service t ON […]

SQL Server 2019 - Administration

DB Comp. Level 110 and Cardinality Version 150 in [sys].[query_store_plan] - I am using Query Store in SQL Server 2019 to evaluate the performance impact of upgrading the compatibility level of a database from 110 (SQL 2012) to 150 (SQL 2019). As part of this exercise I frequently alternate between the two comp. levels, from 110 to 150 and vice versa, and looking at query performance […]

Can we configure read scale between 2 fcis? - Suppose I have 2 WSFC clusters, each having 1 FCI (2-node + 1 FileShare). Fci1 (node1 & node 2) Fci2 (node 3 &node 4) There is a database on fci1 which I want to place on fci2 in near real time sync. It looks like read scale ag is the solution for me (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups?view=sql-server-ver16) Does […]

SQL Server 2019 - Development

Update query generation - Hi All, I am trying to generate MYSQL query by using this https://www.sqlservercentral.com/forums/topic/dynamically-select-load-column-names-into-select-statement , But not getting correct result. Can you help. CREATE TABLE tbl1 (col1 int,col2 int,col3 int,col4 int,col5 int) go CREATE TABLE t_column_details ( [TABLE_SCHEMA] [varchar](500) NULL, [TABLE_NAME] [varchar](500) NULL, [COLUMN_NAME] [varchar](500) NULL, [length] [bigint] NULL ) go insert into t_column_details values ('dbo','tbl1','col1',2) […]

string_agg truncation issue (EDIT: no issue, just a brain not working) - I'm using string_agg to develop a long string to be used in some dynamic sql. I'm using nvarchar(max), but it continues to truncate. This the code I'm using. It gets me a result with a length of 1920 characters. (Note: I have noticed I'm adding an extra "or" on the end of my code here, […]

Amazon AWS and other cloud vendors

I accumulated $60 on MSK serverless over a few days - Just a rant really. I'm used to lambda, DynamoDB etc, but got access to sandbox account at work and wanted to learn Apache Kafka. Turns out serverless version is not scaling from $0 like most, but it's actually more expensive than a single small instance. I left it running over the weekend and lo and […]

Analysis Services

Visual Studio and SSAS - I have a question regarding Visual Studio and SSAS. I created a "Analysis Services Tabular Project" in VSS. Connected to the SQL database and got the required tables and was able to publish to SSAS server. It created the required Database and tables on SSAS. My question is, I am going to have multiple people […]

SQL Server 2022 - Development

Is there another way to solve this "group in 5 day spans" problem? - I ran into an interesting problem over on StackOverflow at the following link... https://stackoverflow.com/questions/47388557/group-by-a-set-of-data-and-order-items-in-each-part-separately-from-other-parts To summarize, here's the data the op posted with 1 row removed because removing that row broke the "Accepted" solution. DROP TABLE IF EXISTS #MyHead; GO SELECT * INTO #MyHead FROM (VALUES (1 ,'2017-11-24 09:45:00.000') --These should be in Grp #1. […]

Best table structure for a tall table that needs to be pivoted - Hi Everyone, I have a table with about 2 million distinct IDs and another table with about 7000 distinct codes plus a varchar(02) flag. I did a simple cross join to create a table with just over 14 billion records. To be clearer each distinct ID will have the same codes. I will update 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.