73,654 questions
1
vote
1
answer
154
views
Pivot JSON data for multiple rows
Is it possible to pivot OPENJSON data for multiple rows in T-SQL (on SQL Server 2022).
I have a case like below, it works fine for single ID (eg. CustID=111). Surely I can go this route processing ...
- 141
0
votes
0
answers
93
views
Long-running procedure being terminated before finishing
We have an End-to-End process which loads data into a Landing database. It used to run individual repopulate scripts for each data source in sequence, but that was taking more and more time to the ...
- 19
0
votes
1
answer
89
views
sp_delete_database_firewall_rule fails for greyed out firewall rules
I'm looking at the Networking settings for one or our Azure SQL servers, and I see some of them are greyed out.
Azure Portal shows a message "You can use TSQL to remove or modify greyed out rules....
- 976
2
votes
1
answer
87
views
Way to simply list the Forms of a word by the full-text parser?
We're using fulltext indexes in current builds of SQL Server and/or Azure SQL - compatibility levels well over SQL Server 2016 or higher, preferably SQL Server 2022 on Azure.
While the searching in a ...
- 976
0
votes
1
answer
85
views
Varying SQL query results [duplicate]
I have a table valued function called "elara_customer_byname". When I execute SELECT * FROM elara_customer_byname('%Kar%') I get 2 rows, which is incorrect. If I copy the SQL from the ...
-4
votes
1
answer
178
views
Recursive CTE looking for all combinations of values that equal one target amount
I found examples online using a recursive CTE to find all combinations of values that equal one target amount. The database column ledger_amount is DECIMAL(26,6).
DECLARE @TARGET_AMOUNT DECIMAL(26, 6) ...
- 499
-8
votes
1
answer
127
views
Find total minutes [duplicate]
Note that it's saved in time datatype.
So if I have someone that started work at let's say 5:30 am and ended their shift at 10:00 am
Is there faster way than:
declare @Shiftstart time;
declare @...
0
votes
1
answer
112
views
Aggregate function for bit-wise OR [duplicate]
In SQL Server 2022, is there an aggregate function for doing a bit-wise OR against a particular column?
-- Example table, data and output to show the different flags
DECLARE @TEST TABLE (FLAGS TINYINT)...
- 20.1k
0
votes
0
answers
38
views
Is there a faster/cleaner/more elegant way to write this SQL Server OPENJSON code block? [duplicate]
I need to extract most keys/values separately, but also to collect the value of the entire 'wms' key into a single field.
I am not the most experienced with OPENJSON(), so this is what I came up with (...
1
vote
2
answers
126
views
Change columns to not nullable if they're nullable
I have a group of tables being updated/replaced by an outside process. I need to add a primary key to make my queries run faster. However, if the column I'm using is nullable, it obviously won't be ...
- 8,502
0
votes
0
answers
61
views
Getting this error running sp_blitzCache on one database: Error message is: The ''' character, hexadecimal value 0x27, cannot be included in a name
On one of our databases, when I attempt to run sp_blitzCache, I receive the error: Error message is:
The ''' character, hexadecimal value 0x27, cannot be included in a name.
Procedure fails to ...
- 1
-1
votes
1
answer
67
views
Best way to Index and or Key massive datasets [closed]
I have two tables related to each other, each with roughly 200M records.
CREATE TABLE [dbo].[AS_tblTBCDEF](
[CDEF_SOC_NUM] [numeric](5, 0) NULL,
[CDEF_EFF_DATE] [date] NULL,
[CDEF_TYP_BUS] ...
- 8,502
0
votes
1
answer
194
views
Does source length of SQL functions matter
First, to define what I'm talking about, the "length" of a source is how many characters it has. Having more characters allows improving readability by using more descriptive function and ...
2
votes
1
answer
203
views
How to insert SM (service mark) symbol in SQL
How can I select a service mark (SM) symbol in SQL?
select CHAR(0153) --> ™
select CHAR(0174) --> ®
select CHAR(0169) --> ©
What would the equivalent be for a SM symbol?
- 67
2
votes
2
answers
118
views
Keep ancestors/siblings structure in XQuery query() method
Take this for example:
DECLARE @xml XML = N'
<members>
<member>
<name>John</name>
<age>32</age>
</member>
<member>
&...
- 61
1
vote
1
answer
65
views
Trying to convert .NET Ticks to Central Time but without the offset
In a SELECT statement I want to convert .NET ticks to a datetime and display in Central Time. I don't want the timestamp+offset that "AT TIME ZONE 'Central Standard Time'" gives, I just want ...
- 13
2
votes
2
answers
186
views
SQL syntax for using STRING_SPLIT with OPENQUERY
I have a linked server named MyLinkedServer. The table I need to pull from has an XML column, so I can't just do SELECT * FROM MyLinkedServer.Database.dbo.Table due to known limitations with linked ...
- 1,546
2
votes
2
answers
134
views
Include control characters in my XML without affecting other data
My nvarchar value contains a control character - Start of Text (0x0002 / STX). I would like to include this in my XML value. Now I know XML is not tolerant of control characters so the only way I can ...
- 23
0
votes
1
answer
384
views
Using the new REGEXP_LIKE in SQL Server 2025 [closed]
As mentioned in Unlocking the Power of Regex in SQL Server, SQL Server 2025 preview would contain new REGEXP_LIKE function.
So I went to docker hub and grabbed the docker image with 2025 preview ...
- 41.2k
0
votes
2
answers
183
views
For ERROR_NUMBER 515, how to get the column name from ERROR_MESSAGE
In many SQL error messages, SQL Server does not mention column name. But in messages such as the one shown below, the error message does include the column name.
Question: How can we extract the ...
- 24.3k
0
votes
0
answers
101
views
SQL Server 2014 full text search can't handle short strings
I have an instance of SQL Server 2014.
I have FTS index on my Customerstable. Everything works fine but I found that it can't handle short strings. For example:
SELECT *
FROM [dbo].[Customers]
WHERE ...
- 5,327
3
votes
1
answer
59
views
Execute xpath having column name into a variable
I need to extract data from a XML column in SQL Server 2016, for this example I'll create a XML variable, but in the real scenario the XML in inside a table so it is not editable.
This is a sample of ...
- 1,112
-1
votes
1
answer
190
views
Get random rows in interval in T-SQL
I need to select 10 to 20 random rows from a table. (I only can find how to select just N random rows). I need to get something like this
SELECT *
FROM table
WHERE ABS(CHECKSUM(NewId())) % 19 BETWEEN ...
- 15
1
vote
1
answer
82
views
SQL script to import a XML file [duplicate]
I'm trying to import an XML file into a SQL Server table, there are no errors, but all values are 0 instead of the actual values.
Could you please help me to understand what is wrong with my script?
...
- 29
3
votes
1
answer
96
views
Import and parse XSLT file using XML
I am trying to import a legacy configuration file into SQL Server table that is in XML that also has an XSLT transform file. I have imported the XML config using .nodes and XPath syntax with a lot of ...
- 33
1
vote
1
answer
90
views
Use the INTERSECT operator in a case-sensitive manner [duplicate]
I have a number of system-versioned tables in my database. Microsoft notes here that...
When you run any data modification queries on a temporal table, the Database Engine adds a row to the history ...
- 345
-4
votes
1
answer
135
views
Using the DIFFERENCE function in SQL - results are confusing
I've been doing practice with the difference function in SQL, but I'm confused with some of the results.
The statement is:
SELECT id, fname, surname, DIFFERENCE(fname, 'matt') AS difference
FROM table ...
- 382
-1
votes
3
answers
163
views
Get the first Item from a "group by" without CTE or Temp Table [duplicate]
Say I have a list of orders that looks like this:
OrderId
CustomerId
ItemOrdered
OrderedWhen
1
1
Orange
2024-08-01 16:33:00
2
1
Apple
2022-01-28 01:00:00
3
1
Peach
2025-06-10 03:39:00
4
1
Banana
2022-...
- 83.3k
-1
votes
1
answer
70
views
Parsing date & time from column in SQL
I've got a column that stores text and I'm trying to find a way to pull the datetime from the field. I've tried to use a combination of SUBSTRING,CHARINDEX, and LEN to pull the datetime from the ...
- 1,070
0
votes
3
answers
178
views
How to combine two queries keeping all results from one
I have a table of Accounts:
AccountID
Name
1
Account A
2
Account B
3
Account C
4
Account D
5
Account E
and a table of CurrentAccounts:
AccountID
2
4
The desired result of combining these two tables is:...
- 537
0
votes
4
answers
130
views
SQL JSON continue the INSERT even after the error
As expected, following code example 1 throws a primary key violation error in SQL Server table since I am inserting 2 twice in the id column. And the code does not enter any other values (1,3,4,5) ...
- 24.3k
0
votes
0
answers
95
views
Writing the result of a T-SQL block to a file in activebatch
Using Activebatch's JobStep library, I want to run a SELECT query in the T-SQLBlock job step, and have the output of that written to a file using the WriteTextToFile job step.
Is there actually a way ...
- 141
0
votes
1
answer
199
views
Why do I get an error with OpenQuery and not with Execute At?
I am using a System ODBC Connection (64 bit) in a Link Server to Amazon Redshift on SQL Server.
This works and returns data
SELECT *
FROM OPENQUERY (LINKSERVER, 'SELECT C1_FULL, C2_FULL FROM ...
- 1,003
1
vote
1
answer
148
views
Chunk insert using Dapper of 5000 rows did not have any performance improvement over inserting row by row [duplicate]
I have an Azure Function built using .NET 8.0, which reads data from an Excel sheet and deletes all rows in Azure SQL Server, then adds around 67,000 new rows.
I was doing this insert one by one with ...
0
votes
4
answers
130
views
Delete duplicate values in a string, preserving sequence
I need to remove duplicate values in a string but the string must have the same sequence.
e.g.
@string = "I need to remove duplicate duplicate values in a a string"
must be converted to
@...
- 63
-2
votes
2
answers
153
views
Truncate a Datetime at different lengths without DATETRUNC [duplicate]
I want to truncate a datetime(7) at different lengths - determined by parts - in SQL Server 2019 (DATETRUNC starts on 2022 *). Kind of "year-month", "year-month-day", ... up to ...
- 375
-1
votes
1
answer
126
views
Enable Ledger on Azure SQL Server, will hang the Azure function execution which add around 65K records
I have an Azure function which read data from Excel sheets and update 3 SQL tables, and it clear 2 SQL tables before re-add the rows. On some scenarios, I will add the data as-is, while on another ...
1
vote
2
answers
203
views
Why ROUND() function adding too many trailing zeros
create table #t(col varchar(10))
insert into #t values(1042800)
select col, ROUND(col,2) as RoundVal from #t
update #t set col = ROUND(col,2)
The select col, ROUND(col,2) from #t returns - as ...
- 24.3k
-1
votes
1
answer
103
views
DELETE large amounts of data in TRANSACT SQL
I have an Excel file with approximately 300k records and I want to delete these rows from a SQL Server table.
I tried using INSERT for each record but that takes way too long. I also tried to create a ...
0
votes
1
answer
120
views
Sleeping Sessions with NULL sql_text and sql_command
I'm monitoring my SQL Server environments (local setup, various versions like SQL Server 2017, 2019, 2022) and frequently encounter sessions that appear as sleeping but have sql_text and sql_command ...
- 1
1
vote
2
answers
119
views
Change date format in SQL Table
My SQL table shows dates in US format. I would like to change them to show UK format in the table.
My column datatype is smalldatetime.
Existing SQL table format
yyyy-MM-dd mm:ss
I would like the ...
- 41
0
votes
1
answer
130
views
Remove overlapping timestamps across events with hierarchical logic in TSQL
I have source data that looks like this:
USER_ID
STATUS_GROUP
STATUS
START_TIME
END_TIME
DURATION
52a3246f6
Available
Available
2025-05-18 19:00:30
2025-05-18 19:01:13
43
52a3246f6
On_Queue
Idle
2025-...
- 21
2
votes
1
answer
149
views
How to calculate Subtotals in SQL Queries
I can’t calculate subtotal and total using GROUP BY ROLLUP syntax in a SQL Server 2019 database:
CREATE TABLE [dbo].[G_Dashboard]
(
[orderId] int NULL,
[C_CO] varchar(4) NULL,
[D_CO] ...
- 131
-3
votes
1
answer
95
views
Make a batch of “Alter procedure” that are stored in a table [duplicate]
Currently I have hundreds of stored procedures that are in multiple servers, and that coexist with other stored procedures that are in other servers with linked server.
The origin of this is that when ...
0
votes
1
answer
134
views
SQL Server cursor inside cursor
I'm trying to use a cursor inside another cursor.
I'm facing a problem that's: I'm only be able of update one row of all the millions of rows, and the others are considered as 'not found' which is not ...
0
votes
0
answers
57
views
Execute Boolean expression with sp_executesql [duplicate]
I am trying the following:
declare @V_SQL nvarchar(4000);
declare @v_formula nvarchar(4000);
declare @P_RESULT Varchar(5) ;
set @v_formula = '1 AND 0' ;
set @V_SQL =
'BEGIN
if ' + @v_formula + '
...
- 375
0
votes
1
answer
105
views
Consolidating data so no duplicates in the same column per ID
How to reduce clutter of data i.e. remove "duplicates per column" not "duplicates per row" i.e. type1 and type2 have no association.
ID
Type1
Type2
Type3 ...
1
A
YY
1
A
ZZ
1
B
YY
1
...
- 810
3
votes
1
answer
90
views
When are curly brackets required when used in a XQuery
I'm using the modify() and query() methods to interact with XML data.
Here is an example of how I'm using modify():
DECLARE @i int = 1
DECLARE @x xml = N'<?xml version="1.0" encoding=&...
- 345
0
votes
1
answer
109
views
Syntax for coalescing to an error in SQL Server
In some languages (C# for example), you can coalesce to a throw expression,
E.G.
string someValue = MyFunctionThatMightReturnNull() ?? throw new Exception("null value");
Wondering if there ...
- 2,253
3
votes
1
answer
123
views
Set rows to read-only
I have an application with the following requirements:
Before a predefined event (in my application) occurs, I need to update and insert rows in different tables.
After the event, certain rows in ...
- 85