SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

What will be inserted?

The Voice of the DBA

We're Not Faster with AI

At Redgate Software, we've been trialing CoPilot from GitHub with our developers. I got in on this and have tried a few things, though I'm not sure I've found it very useful. I'll continue to experiment, but for now, I just don't find the AI helping me with the types of tasks I do.

However, our chief engineer, Jeff Foster, was interviewed about how he sees AI and Copilot in Redgate. It's a good read, and I found a fe fascinating things in the piece. First, I was worried about this a bit, as we have explicit guidelines in developers that say we can't take the output from an AI and put it in our codebase. We worry about legalities since we sell software. How does that work with Copilot?

Well, I learned that there are a couple things in here. First, with a business subscription, we can have the AI not show us copies of public code. If more than 150 characters of public domain code is in a suggestion, it's hidden. This is in the Copilot docs as well. I don't know if 150 is a good length or too long, but at least there are limits. We also found that Copilot doesn't retain any snippets, which is important for us. We don't want to share Redgate code with others, especially competitors unless we are explicitly putting a public repo out there.

The second thing I found really interesting is that we have a lot of guardrails around the way we produce code. We have tests, lots of automation to evaluate things, and plenty of peer review. I see requests for PRs in Slack constantly amongst our teams. We know that the code that gets generated likely needs some work, but also that we want to be sure that whether it's sent as is or edited a developer, the code is still reviewed.

Perhaps the most interesting part for me was near the end. Jeff doesn't think that Copilot makes us faster. He says "we probably invest ten times more time finding the right problem to solve than on the actual coding." That makes sense, given what I've observed over the last 15 years. We are deliberate and careful about picking problems to solve and producing high-quality code. Sometimes it makes me a little crazy as I want things to move faster, but I've learned to appreciate that overall, this is an approach that doesn't get us into places where we struggle to onboard developers to new projects or adjust our code to meet new requirements.

I've got a few quiet weeks coming up, so I'm going to give in more and use Copilot a bunch to see what I can get out of it. If you've tried it, let me know what works or doesn't. If you want me to try and solve some problem for me, drop a note in the discussion with the challenge you have.

Steve Jones - SSC Editor

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

Featured Contents

How to Change the SQL Server Database Owner

Nisarg Upadhyay from SQLServerCentral

Discover the importance of database ownership in SQL Server and learn the process of changing database owner using SQL Server Management Studio or ALTER AUTHORIZATION statement

Installing SQL Server Integration Services

Additional Articles from MSSQLTips.com

You're tasked with installing SSIS as part of a SQL Server installation or adding it to an existing one and would like a step by step guide showing how to install it.

From the SQL Server Central Blogs - A high level view of the most costly queries by CPU

SQLPals from Mission: SQL Homeostasis

A high level view of the most costly queries by CPU

I want to see at a high level i.e at batch or stored procedure level, not at the individual...

From the SQL Server Central Blogs - Assigning a Default Schema to a New User–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. Since this...

The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform

Steve Jones - SSC Editor from SQLServerCentral

Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	What will be inserted?
I query my sys.identity_columns and sys.objects views and get this data back (only a few columns shown): TableNameColumnNamelast_valueincrement_value DataWithTimemyid 1002 1 I run this code: INSERT dbo.DataWithTime (Mydata, mytime) VALUES ('F', NULL) If I query the table for "F", only one row comes back. What is the value for myid?
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 Self Referencing CTE

What is returned by this code:

DECLARE @n int = 5;
WITH cte(F,n) AS 
(
    SELECT 1      AS F, 
           @n     AS n
     UNION ALL
    SELECT F*n    AS F, 
           n - 1  AS n 
      FROM cte 
     WHERE n > 1
)
SELECT F
  FROM cte
 WHERE n = 1
 OPTION (MAXRECURSION 4)
;

Answer: 120

Explanation: This is a recursive CTE that calculates a factorial. The code looks to call this for 5!, which is 120. There is a maxrecursion limit of 4, but there are only 4 recursive calls. If the option were 3 or less, this would return an error. Ref: CTE - https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?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 2016 - Development and T-SQL

Syntax issue while trying to create index on filegroup - Ok so this is driving me nuts. My syntax is incorrect on this statement: create index IX_Service_Start_Date ON ivc_cds.CDS_Claim_Header (Service_Start_Date ) WITH (FILLFACTOR = 98, SORT_IN_TEMPDB = ON ) on ServiceStartDatePartitionScheme ( Service_Start_Date ) ON [DefFG] Error: Incorrect syntax near the keyword 'ON'. I'm trying to create the above index on the filegroup DefFG. I've […]

SQL Server 2019 - Development

Cannot resolve the collation conflict on Azure between master database and db - We have an Azure datanbase which up to today has been working fine However after our latest deployment, we are unable to run our application connected neither can I connect Azure Data Studio - Profiler to the Azure database. I get the following error message: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in […]

Select from view on FileTable throws error - I have a view that computes some fields, using SubString and CharIndex. When I do a Select *, it works great. When I add some conditions, specifying conditions for the COMPUTED FIELDS, the query bombs claiming that invalid arguments were passed to the SubString function. Those computed fields all get computed correctly - there […]

SQL Azure - Development

Database Snapshot Alternative in SQL MI - Hi, Database Snapshot feature is not supported in SQL MI. Is there an alternative for having point in time readonly copy in same SQL MI.

Reporting Services

SSRS 2019 not able to setup URL Reservations with port 443 - HI . here is the setup i have - Windows Server 2022 and SSRS 2019 i setup IIS and setup a test site using the SSL cert and that is working fine and since have disabled it i am trying to setup SSRS 2019 to work with HTTPS and it doesn't want to work for […]

'see' and edit text box items when they are obscured by other items - Really new to SSRS Report builder and no training. I have different items and backgrounds that have visibility dependent upon query fields. This means that the items are layered and have differing parent items. I can tab through all the items and adjust their properties but, when it comes to text boxes, I need to […]

Dynamically generate a new instance of a subreport for every selected parameter - Hello. I have 2 parameter fields I'm passing through to a sub-report to get it to populate. They are 'Year' (only 1 selected) and 'ID' (multiple selected). I'd like to dynamically generate a new instance of the sub-report (aka, template) for each selected parameter. For example... Let's say the ID's selected are "1,5,13' and the […]

General

Unintended consequences (Oh sh!t moments) - I firmly believe that one characteristic of good developers is understanding and being able to anticipate unintended consequences. Today I experienced this in a fairly inconsequential thing, but my analytic mind took over. I was entering an order for K-cups on the Keurig website, and got an error when finalizing the order saying my Mastercard […]

Strategies and Ideas

Hash value in a primary key? - Hello, Problem: in a dwh table, we are getting duplicates based on some fields that represent the primary key of a table, but this is not a true duplicate cause one of the fields come with lower/upper case, and should be considered as sensitive, the issue is that our database is insensitive case so we […]

Contests!

SQL Server 2024 or 2025? - Take a guess when the next version of SQL Server will release. Give me a date, and I'll send a prize to whoever is closest (or maybe 2 people that are equidistant), and posted earliest here. To make it more interesting, if you post in 2023, you get leeway of a week. If you post […]

PostgreSQL

TSQL To Postgres - Unpivot/Union All - I am trying to convert this TSQL:INSERT INTO temp_FieldFlowsFact SELECT [Account],[Calendar day],[Financial year],[Period] ,CASE WHEN [Metric] like '%R12M%' THEN 'R12M' WHEN [Metric] like '%R6M%' THEN 'R6M' WHEN [Metric] like '%R3M%' THEN 'R3M' ELSE 'Periodic' END AS [Period type] ,[Metric],[Metric Value] FROM ( ----Calculating Rolling FTRs SELECT [Account], [Calendar day] ,[Financial year],[Period] ,[Issue] ,SUM([Issue]) OVER […]

Job Postings

SSRS Contractor Needed - Hello, I have someone who has reached out to me in need of a contractor to build out their SSRS environment and make changes to a dashboard I gave them that we use at my current employer. They are also looking for someone with Power BI knowledge that can help them link their on-premises SQL […]

SQL Server 2022 - Administration

Kerberos Configuration Manager connection error with SQL 2022 - Hello experts, I'm trying to use Kerberos Configuration Manager on an instance of SQL Server 2022 and get this error when I try to connect: "Error Unable to connect to server, please ensure that the server name is correct, SQL Server is installed properly, and the user has administrator permissions. If the problem persists, please […]

SQL Server 2022 - Development

How to import csv file into a sql table and include filename - Hi everyone, I have a directory of many csv files such as C:\Temp\NP-001.csv C:\Temp\NP-002.csv C:\Temp\NP-003.csv In each csv file, there are 5 columns: Name, DOB, MemberID, Address, Email We need to somehow loop through each csv file and bulk insert or openrowset information to a sql table. The final result would be like a sql […]

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.