73,654 questions
1
vote
1
answer
165
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
195
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