SQLServerCentral.com Community Update for May 22, 2023

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Picking the Backup to Restore

The Voice of the DBA

Database APIs

Creating an API and using an API server of some sort has become a very common way of working with modern software, especially distributed systems. Whether developers move to microservices or something else, they often make more use of APIs than ever before. At Redgate, I've watched us work on new products, often beginning with a basic API at the command line and evolving from there.

However, does the database need an API? This article thinks so, though I don't love the piece. It seems to wander a bit amongst a few topics and doesn't always fit together. It's also trying to promote the Stargate API gateway for Cassandra. I don't know if this is a good idea, or if it really helps make developers more productive, but the idea of having an API for developers makes sense.

In fact, when I work with clients I often try to get them to think of their tables as an API of sorts. With APIs, we want to rev them, but carefully. After all, we know there are dependencies from various other pieces of software, including other items inside the database. We want to be sure we don't break anything. At least not for long, which is why DevOps and being able to release work quickly matters.

Communicating changes and coordinating work is a challenge. I was surprised by a client who used SQL Doc for this reason, to ensure everyone had up-to-date knowledge of what their schema meant. It's also a reason I've taken some of the work I do with clients and built my Architecting Zero Downtime talk. Many developers think narrowly about the requirement in front of them and not about all the other dependencies and impacts. I think a bit more unit testing in databases would help here. In fact, I demonstrate sometimes how to write a test that treats your table like an API to prevent breakage.

I do agree that databases, especially relational ones can be complex. If we can simplify things for developers, especially developers new to our system, we can get more work done and hopefully, raise code quality. I don't know that Stargate or GraphQL or anything else is better, though I have started to think LINQ is a better implementation than SQL for working with relational databases. That's not going to change anytime soon, and plus, I need SQL to remain popular until I retire

Whether you formally build an API or not, it is helpful to treat your schema like an API and make changes in the same way that you would if there were other teams depending on your interface. That's because there likely are others who depend on what you build.

Steve Jones - SSC Editor

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

Featured Contents

How to Generate Images with AI and Store them in SQL Server using Python and DALL·E

Daniel Calbimonte from SQLServerCentral

Introduction In this article, we will learn how to generate images with AI. This time we will work with DALL·E. DALL·E is an image generator using AI. You just need to provide a description and DALL·E will generate the image: In this article we will learn these topics: First, we will learn what is DALL·E […]

PostgreSQL 101: The 10 key differences between SQL Server & PostgreSQL

Additional Articles from Redgate

Join us for the second webinar in our PostgreSQL 101 series.

As a SQL Server developer or DBA, you have a broad skill set and have become accustomed to interacting with the larger #SQLFamily community. Now, by choice or necessity you've begun to explore PostgreSQL and things feel oddly familiar and foreign at the same time.

Join Ryan Booze on May 25th to find out the things that often trip up SQL Server users when they first start exploring PostgreSQL.

Cache SQL Server Data in a .NET Web Application

Additional Articles from MSSQLTips.com

One of the slowest parts of any application is data retrieval and in this article, we look at how to cache SQL Server data for a web application.

From the SQL Server Central Blogs - T-SQL Tuesday 162 : Data Science and ChatGPT

Diligentdba 46159 from Mala's Data Blog

This month’s T-SQL Tuesday is hosted by Tomaz Kastrun – his call is to write about how we’ve used ChatGPT, and what are ethical issues, if any, that we...

From the SQL Server Central Blogs - How to refresh Power BI desktop data

Joyful Craftsmen from Joyful Craftsmen Blog

Recently we faced the issue on one project to refresh power BI desktop file to update structures.
There could be various reasons why you would like to automate PB desktop...

Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance

Site Owners from SQLServerCentral

Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Picking the Backup to Restore
I have a backup file for a SQL Server 2019 database that has been used over and over. There are 5 full backups that were appended to this file across the last week. If I want to restore the third one, what do I need to do?
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)

Connecting to Localhost with the new sqlcmd

I want to use the new sqlcmd, written in Go, to connect to my local SQL Server instance using localhost. I do not want this to fall back to the old sqlcmd. How can I do this and query AdventureWorks.

Answer: Use the sqlcmd config commands "add-endpoint" and "add-context" to set a context to the local machine and then sqlcmd query to query.

Explanation: To connect to a local instance, you need to use add-endpoint to add an endpoint and then add-context to add a context. From here, you can query a database. Here is an example:

sqlcmd config add-endpoint --name lh --address localhost sqlcmd config add-context --name defaultinstall --endpoint lh sqlcmd config set-context defaultinstall sqlcmd query "select @@version"

You can see this working here: Create the endpoint: Next, config the context: Now set the context and query sqlcmd local connection Ref: go-sqlcmd - https://learn.microsoft.com/en-us/sql/tools/sqlcmd/go-sqlcmd-utility?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 - Development

Populate fields where CSV Sting has data matching given Column name - Given multiple ROWs comprised of varying combinations of Comma delineated values and an ID. Given a TABLE whose COLUMNS names are a comprehensive compilation of the (SPERATED/PARCSED) delineated values existing between all ROWs. Populate the given TABLE by looping through the COLUMN names and PARCING a given ROW for a delineated value that MATCHES a […]

sql time zone conversions - hi folks i need to convert a date/time field between two time zones the date/time stamp is in GMT Standard Time +01:00 which is correct but i want to convert it to UTC +00:00 - as the app we are using wants to manage the time series data and zones so 2023-04-05 23:59:43.410 GMT should […]

SQL Server 2016 - Administration

How to view actual execution plan, for long running jobs? - I have a couple of very long running jobs which are scheduled by SQL Agent. These jobs are doing archiving task & runs to several hours. I would want to view the execution plan for the jobs to see what can be improve. May I know how can I view the actual execution plans for […]

SQL Server 2016 - Development and T-SQL

Index Spool Eager Pool issue - I have an archiving job running on our production database which will move old datas from an existing production DB to another archiving DB running on the same box. The job utilize on MERGE INTO operations to do the archiving. This job isn't a new job and has been running for quite some time. Users […]

Development - SQL Server 2014

Dirty Reads with Read Committed? - Hey everyone! I have a SQL agent job that deletes data then inserts data into the same table hourly. Both the delete and insert command are wrapped in the same Begin/Commit/Rollback Transaction statement and the process takes about 30 seconds to complete. I have another job that on occasion reads data during this update and […]

SQL 2012 - General

SSL Certificate - Hi.. SSL certificate has been installed in our standalone and cluster servers . and from Registry Editor , I can see the Certificate number and force encryption is zero ( screenshot is attached) Yesterday after the restart our standalone server , I can see Message in error Log as below : The certificate [Cert Hash(sha1) […]

SQL Server 2019 - Administration

Configure replication with Always-On (Basic Availability group) - Hi Folks We have configured Basic Availabilty group and now we need to configure replication . Scenario is this sev 1 and sev 2 are in basic availability group. Database is sev1 would initially act as publisher .sev3 would acts as subscriber and our distributor database would be on remoter server sev4. So i would […]

How to run SSMS as non Admin and restart Services - I have a user that needs full access to a specific instance on our SQL Server (2019). I do not want this user to have local or domain admin prividleges. I have gotten SSMS on his pc and have given him a domain account (localAdmin1) to use that does have local admin prividleges on his […]

Move SQL Server users from one RDS for SQL Server instance to another - Hi, How can we move SQL Server logins from one RDS for SQL Server instance to another ?

Restore further T-Log Backups - Hi, is it possible, to restore more t-log backups, after I restored the database? We got a problem, and we had to restore the database, then check the table in the database. Maybe we did the point in time restore to early, and I'd like to restore some more t-log backups, without the restore of […]

Blocking alert - Hi All, I am trying to capture and set up alert for blocking when it goes more than 30 seconds. Is the following correct OR maybe i need to do with wait time. select @@servername [SERVER],sp.spid,sp.blocked, (CONVERT(DATETIME,CAST(sp.last_batch AS CHAR(8)),101)) as last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, sp.dbid, sp.loginame,sp.hostname,sp.cpu from master.dbo.sysprocesses sp where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(Second,last_batch,GETDATE())>30 0 […]

SQL Server 2019 - Development

Index help - Performance issue - This table contains contains 127 M rows and growing. We are starting to see some performance issues on any query that touch this table. There is a columnsotore index on it including clustered and several non-clustered indexes. Do you notice duplicate indexes and/or t wide indexes? Any suggestions on what can be removed or […]

Employee count based on target date - I have table for employee activity with the following fields: [Emp ID] , [Hire Date] , [Term Date] , [Mod Date] , [Old Div] , [New Div] When an employee is hired, [Emp ID] is filled, [Hire Date] is filled with the current date and [Old Div] and [New Div] are filled with the division […]

how to get previous year and current year data in coma separated values in sql - Hi Team, I have data like below and expected output is like this. --Expected o/p --Jan-2022,Jan-2023,January-2022,01-01-2023,Jan-2023,Jan-2023 Can you please help me on this. USE [Tempdb] GO /****** Object: Table [dbo].[tempIssueRange] Script Date: 09-05-2023 18:53:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tempIssueRange]( [IssueID] [int] NULL, [IssueName] [varchar](110) NULL, [IssueDate] [date] NULL […]

Azure Data Factory

Monitoring ADF best practices - All, I know that within Azure Data Factory, there is a great monitoring section to see what was successful, what failed, and you can get super granular with this and dig into individual runs. My question here is, can we integrate these logs with Azure Application Insights? If so, has anyone done this and […]

This email has been sent to [email protected]. To be removed from this list, please click here. If you have any problems leaving the list, please contact the [email protected]. This newsletter was sent to you because you signed up at SQLServerCentral.com.