73,637 questions
-4
votes
1
answer
58
views
Matching on dates when a MM/DD combination needs to be excluded [closed]
I've been tasked to write a query where a month and date combination from one column needs to not equal another date column.
i.e. MED_EFF_DATE = 06/01/XXXX and MED_CANC_DT NOT EQUAL 08/29/XXXX
I've ...
- 8,497
Advice
1
vote
1
replies
54
views
Is there an exercise repository for AdventureWorks database tables?
I am currently practicing SQL using the AdventureWorks sample database.
Before posting, I tried searching but couldn’t find a structured collection of exercises specifically designed for practicing ...
Best practices
0
votes
3
replies
64
views
Replicating an app on my company's website using T-SQL
I have a report application that i'm replicating. This report shows us 4 columns based on a date filter. Assembly Line, Product#, Enters and Exits. The Enters and Exits are time Stamps of when this ...
Best practices
0
votes
5
replies
113
views
Alter a SQL Server table column size in production
What is the best way to alter a production SQL Server 18 table's column size, varchar(n +1).
My understanding would be the following:
Shut the website down to stop the transaction from coming in.
...
9
votes
1
answer
425
views
Selecting from a temporary #table whose column is sp_renamed has stopped working for SQL Server version 2019 and later
In a stored procedure (version 2019), renaming a column of a #table to a @value stops working for subsequent executions. The #table retains/gets the @value name of the very first execution.
Is this an ...
- 99
3
votes
1
answer
127
views
Get table name of the columns, used only in select statement of the view
Suppose the following is the select statement of a view
SELECT Emp.ID, Emp.FullName, Emp.JoiningDate, Dept.DepartmentName, Reg.RegionName
FROM Employees AS Emp
INNER JOIN Departments AS Dept
...
1
vote
3
answers
148
views
How to return row where items are grouped by Id and related reference fields?
Suppose I have this table and values:
DECLARE @TEST AS TABLE
(
GroupId INT NOT NULL,
ItemId INT NOT NULL,
Ref1 NVARCHAR(10) NULL,
Ref2 NVARCHAR(10) NULL
)
INSERT INTO @TEST
VALUES
(1, ...
- 351
1
vote
0
answers
52
views
Cannot Find T-SQL in ColdFusion Report Builder File (.cfr)
I'm using ColdFusion Report Builder. I have an existing .cfr open. it has many properties (query.column_1, query.column_2... query.column_n) defined and used for display within the file. However, the ...
- 23
3
votes
2
answers
193
views
Removing duplicates in a sequence
I have recently investigated an audit table that had a wee bit of a bug with events that led to its data population, which I identified and fixed. Now there is the issue of past data that has ...
- 36.5k
0
votes
2
answers
171
views
Inner/Outer SELECT filtering
Is there any difference (maybe in performance) between INNER SELECT filter and OUTER SELECT filtering:
Situation 1 - INNER SELECT filtering:
SELECT
FROM
(
SELECT ColA, ColB
FROM myTable1
...
- 6,565
Best practices
0
votes
5
replies
64
views
Conditional lag and retention
How to generate a conditional lag in T-SQL?
If the visit is a D01 visit and VIS_DAT data is present -> retain VIS_DAT
If the visit is a D01/D08 visit, and the VIS_DAT data is missing -> lag ...
- 1
0
votes
2
answers
129
views
Incorrect ordering in CTE query
I run the below query against a view in MS Fabric environment (Data Warehouse). Here the compl_officer_id is the same across management_id partition (unfortunately there are no other unique values ...
- 65
0
votes
2
answers
182
views
SQL join using BETWEEN => GREATER THAN Val1 and LESS THAN or EQUAL TO Val2
I need to join 2 tables based on a column named TRANS_ID. The problem is, one table has all available TRANS_IDs and the other table only has SOME of the available TRANS_IDs.
For instance, take this ...
- 8,497
0
votes
1
answer
142
views
Pivoting table doesn't return the expected results
I am trying to use pivot, but it's not working the way I expect.
The temptable I want to use input is #GenericTeam:
Versionnumber
Valid_from
Team_name
Attributenumber
Attribute_displayname
...
- 35
0
votes
2
answers
83
views
Parsing an XML column in T-SQL with a colon in the XML Structure [duplicate]
I have a column of XML data in SQL Server that I'm trying to parse into columns. The start of the XML data looks something like:
<DataSet xmlns="google.com">
<diffgr:diffgram ...
0
votes
0
answers
61
views
LIKE with NVARCHAR parameter returns incorrect results [duplicate]
I am getting incorrect results to a LIKE query when the like-string is defined as a NVARCHAR type parameter.
The problem happens when including either brackets (span of characters) or underscore (any ...
- 103
0
votes
2
answers
211
views
Setting OrderNumber of rows in a table
I have a TimeZones table that provides a list of all the time zones in the world with names, offset info, etc. I also have a CountryId column in my table which tells me which country time zones are in ...
- 31.1k
Best practices
1
vote
2
replies
119
views
T-SQL ETL update evaluation recommendation: What is the most elegant & performative way to evaluate a source value to update a target value?
What recommendations might be offered for the most elegant and performative way with T-SQL to evaluate whether a source value should update a target value, as part of an ETL update process in which ...
- 505
Best practices
0
votes
4
replies
180
views
How to ensure an inserted row survives a rollback?
In SQL Server, I have a stored procedure that returns some sensitive data to the client, and logs that fact into a log table. The stored procedure is executed in a transaction controlled by the client,...
- 52.3k
2
votes
3
answers
216
views
SQL Server stored procedure passed a Biztalk XML message not returning field values when parsing the XML text
Our integration team want to send a XML parameter from their Biztalk server containing values to be written to a SQL Server table using a stored procedure, and have sent me an example for me to create ...
- 21
0
votes
0
answers
55
views
Find SQL Server stored procedures that have optional parameters [duplicate]
I am trying to run a query in T-SQL to identify stored procedures that are defined with optional ("defaulted") parameters. I had expected this to be a common thing for people to try and find ...
- 1,120
1
vote
1
answer
223
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 ...
- 161
0
votes
0
answers
121
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
1
vote
1
answer
380
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....
- 1,120
2
votes
1
answer
109
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 ...
- 1,120
0
votes
1
answer
91
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
192
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
140
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
124
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
1
vote
2
answers
145
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,497
0
votes
0
answers
93
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
81
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,497
0
votes
1
answer
207
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 ...
4
votes
1
answer
255
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?
- 87
2
votes
2
answers
124
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
70
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
202
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
168
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
571
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 ...
- 42.1k
0
votes
2
answers
205
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
107
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
62
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
194
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
88
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
119
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
105
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 ...
- 355
-4
votes
1
answer
169
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
170
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.5k
-1
votes
1
answer
77
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
203
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