3,866 questions
1
vote
2
answers
130
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
-1
votes
1
answer
72
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
-2
votes
1
answer
98
views
Rows Affected to appear under custom message [duplicate]
I've got some code where I'd like to show a custom message, and then the number of records affected. So I run this:
DECLARE @RptDate VarChar(10)
DECLARE @PrtMsg as VARCHAR(8000)
DECLARE @DayTime as ...
- 8,502
-1
votes
1
answer
109
views
Grouping data using the LEAD() function
I've got this query:
SELECT TOP (1000) [TRANS_ID]
,[VSN_VENDR_EFF_DT]
,LEAD([VSN_VENDR_EFF_DT], 1, '9999-12-31') OVER (ORDER BY [ACCT_NUM],[VSN_VENDR_EFF_DT]) AS NEXT_EFF_DT
,[...
- 8,502
4
votes
1
answer
101
views
Strip numbers from string after certain characters into a recursive table
I have unstructured, free-form data, which requires cleansing to produce a clean list of all PO numbers in a column. Unfortunately, the column can accommodate multiple PO numbers in various formats (...
- 575
2
votes
1
answer
84
views
UDTT variable showing sometimes as "unknown" only in rpc:completed event
I'm trying to troubleshoot a performance issue with our C# application. After investigation, the culprit is a SQL Server query that takes a long time to execute.
The slow query uses an udtt (user-...
- 21
0
votes
0
answers
46
views
Trapping multiple errors in SQL [duplicate]
Using SQL Server 2019 (Some instances back to 2016, but mainly 2019)
Background: We have a process that allows developers to run dynamic SQL, and errors are logged to a table if things go wrong. If ...
- 2,729
0
votes
2
answers
77
views
Split string to join to table and rejoin
I have data in a database that I cannot change which is stored like this:
DocId
Staff/Relationship
127866
1395/3003,1399/1388
where:
1395/3003 = Cat Stevens/Therapist
and
1399/1388 = Dog Stevens/...
- 75
0
votes
4
answers
240
views
Efficiently comparing DATE and DATETIME columns in a SQL query [closed]
I've got tables with millions (possibly billions) of rows, so I really need to be efficient with the query.
In this query, I'm joining multiple tables. The segment in question is:
LEFT JOIN
dbo....
- 8,502
-2
votes
1
answer
84
views
DateFromParts and CASE expression causing an error
I am getting the error shown below, when using the provided CASE expression. Every value of Offername in the sample I am currently running fails both of the WHEN conditions and should return a null. ...
- 1,232
1
vote
0
answers
93
views
JOIN ignores null characters in NVARCHAR - is this expected?
When I run inner join, it returns a match when one of the values is X and another is CHAR(0) + X. Is this expected?
create table t1(c nvarchar(30))
create table t2(c nvarchar(30))
insert into t1 ...
- 85
0
votes
1
answer
132
views
Standalone query returns 87 rows in 2 minutes, but batch script gets stuck in infinite loop
I'm encountering an issue where a standalone query runs in about 2 minutes and returns 87 records, yet when I embed it in a batch processing script, the script seems to hang in an infinite loop. I've ...
0
votes
0
answers
62
views
Combining SQL stuff and pivot [duplicate]
My data looks similar to this:
GroupID
TaxID
Division
EmployeeID
1
132
ABC
12345
1
132
ABC
67890
1
132
DEF
67890
2
999
DEF
99999
I want to use a pivot command to convert the Divisions to columns and ...
- 55
0
votes
3
answers
346
views
Calculating Fiscal Month from Date
My company is on a 4-4-5 calendar, and our week goes from Sunday to Saturday and I'm looking for advice on how to return the proper Fiscal Month from a date. Right now, I'm handling this easily in ...
- 71
0
votes
0
answers
60
views
SQL Server deadlocks after index rebuild
We rebuilt a bunch of indexes that had avg_fragmentation_in_percent>30. Now we have 1 job that is consistently getting deadlocks and I'm trying to find the root cause. Attached is the event data ...
- 91
0
votes
4
answers
251
views
Splitting a string into n segments of equal length m
I wonder if there is a way, just using a select statement, without involving loop/for/while control statements or creating any function in SQL Server, to split a string into n segments of equal length ...
- 3,404
1
vote
1
answer
62
views
How can I perform an 'AND' search across multiple full text index tables in my SQL Server command?
I'm currently using SQL Server 2016 and I need to search for n-number of user input terms across four different full-text index tables. We originally tried this:
DECLARE @searchString varchar(1000)
...
- 717
0
votes
3
answers
264
views
How can I efficiently convert a bigint into a base 2 string and count the 1s?
I have a bigint column or variable. I want to convert it into its bitmap (base 2) representation as a varchar and additionally to that count the 1s.
I need both components - not just the bit_count ...
- 85
0
votes
2
answers
107
views
String Matching Struggle
I need to find State abbreviations in column name my_column. This column can contain values like these
John Smith of AZ --> Match
John Smith of AZ(Tucson)' --> Match
AZ John Smith --> Match
...
- 39
0
votes
1
answer
57
views
Update query by order of sequence
I am having an issue trying to use an update query:
select
lc.name as locations,
lc.sequence
from
dbo.LocationContainers lc
Currently this query shows this result:
locations ...
1
vote
0
answers
175
views
Filter Extended Events to get rid of the NULL value
How do I configure the filter in Extended Events in SQL Server 2016 so that it does not log when certain columns have a NULL value, e.g. sqlserver.nt_user. I can't use IS NOT NULL or length(sqlserver....
- 21
0
votes
4
answers
111
views
Truncate path into descending subpaths
I have a path that looks like this
Subdomain1>Subdomain2>Subdomain3>Subdomain3>Subdomain5
The length is variable, and it is possible that it could be NULL as well.
How could I create 5 ...
3
votes
3
answers
96
views
Update a table with a rank number with a subquery
I am using the following SQL query to update a temp table to be able to determine priority.
This is my SQL statement
UPDATE #CARD
SET rank_no = x.no_rank
FROM
(SELECT
rank_no, ...
0
votes
1
answer
560
views
Copy Activity in ADF errors out when I use Dynamic Range Partitioning for concurrents reads and writes
I've been trying to copy some data using the Copy Activity from SQL Server 16 to Snowflake through a self hosted IR that has direct Line of Sight to the Server. I'm trying to enable dynamic range ...
0
votes
1
answer
113
views
Generate random characters in stored procedure
I'm trying to create a stored procedure to randomly generate two unique alphanumeric characters from the following character set on SQL Server 2016: ABCDEFGHIJKLMNPQRSTUVWXYZ0123456789 (35-character ...
- 36k
0
votes
0
answers
201
views
How to split comma value string into multiple columns, ignoring commas inside double quotes [duplicate]
I have comma delimited data like Steve,Mark,"1,2",Final. I want to split these string into columns, but ignore the comma inside the double quote. Here "1,2", I don't want to split ...
- 119
2
votes
2
answers
70
views
Query to get Value in row split with virgula character
I have the following table:
CREATE TABLE [dbo].[PRC_Tutors]
(
[ID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[LivelloZoom] [int] NULL,
[PosizioneGIS] [nvarchar](max) NULL,
...
- 2,177
0
votes
1
answer
155
views
Connect Macbook (M2) to SQL Server 2016 via ODBC
I am trying to query a SQL Server 2016 database via a jupyter notebook in VSCode. To do this, I've followed these instructions to install the ODBC drivers.
This is the output of the odbcinst -j ...
- 341
-2
votes
1
answer
129
views
Does SQL Server ALWAYS enforce referential integrity with foreign key constraints?
In other words, is it possible to create a situation where there are orphaned child records, for which no parent exists anymore?
Imagine a situation where ON DELETE CASCADE cannot be used and an ...
- 69
1
vote
0
answers
124
views
How to force Entity Framework to treat variable as constant? [duplicate]
I have an EF6 IQueryable<T> which contains a variable in a where clause. I have performance issues with parameter sniffing because the execution plans for some variable values are very ...
- 69
0
votes
1
answer
169
views
Change Existing JSON value "JSON_MODIFY" must be a string literal
First insert is not a problem. But update JSON value getting error. It is getting error because I am using dynamic value. However I cannot figure out how I can.
DECLARE @Index INT = (
SELECT rn - 1
...
- 200
0
votes
1
answer
72
views
Replacing multiple characters in single column in result set
So I am trying to create a fixed length flat file from the result set of this query, however there are three name fields where I have to strip out hyphens, apostrophes, spaces, periods, suffixes (Jr) ...
1
vote
1
answer
175
views
Update all column names to be lowercase
I need to rename all columns in SQL Server tables to lowercase. I came up with the code below; however, there's something wrong with the select statement. If I run that by itself, it doesn't return ...
- 113
-1
votes
1
answer
73
views
Convert datetime works fine, unless I Order by the converted column [duplicate]
select x, convert(datetime,datecolumn) + convert(datetime,timecolumn) as datetime
from abc
where d = e
This works fine, I can see all of the columns and the dates and times have added to combine from:...
- 1
0
votes
0
answers
53
views
SQL Server Agent Job cannot run a query on audit log
I would like to send the result of the following query by mail every 15 minutes.
select
event_time, server_principal_name, application_name,
database_name, statement
from
sys....
0
votes
1
answer
106
views
Query to get dates that have a 12 month gap between them
I have a table that contains PK and a created date:
PK
Created_Date
1
01/01/2007
1
01/02/2007
1
12/12/2008
2
15/02/2021
2
01/06/2022
2
02/06/2022
I am trying to bring back any records that have at ...
- 11
1
vote
0
answers
427
views
Query Regressed Queries in SQL Server 2016 Query Store
SQL Server 2017 introduced the system view known as sys.dm_db_tuning_recommendations, which displays all detected plan regressions. Using this view, you can retrieve data, compare the last known good ...
- 277
-5
votes
2
answers
138
views
SQL to tell if string is numeric characters followed by alphabetic characters
Is it possible to determine if a string is numeric characters followed by alphabetic characters?
E.g. "123AB" is valid, "4.5 CD" is also valid, "CDE3" is not. "4d9&...
- 1,241
-3
votes
1
answer
93
views
Tracking status changes over time
I am trying to track status changes over a given date range. 1/1/2023 - 1/31/2023
Table 1: Customer
filekey
activestatus
activestatuseffdate
1000
0
1/15/2023
Table 2: StatusHistory
filekey
status
...
- 83
0
votes
1
answer
42
views
Count Distinct Window Function Workaround
We have a data table that is transactional detail and a table called TimeSortOrder30 which list each of the 30 min intervals in a day with columns for Hour, Min, CustomDateSort. The CutsomDateSort is ...
- 1,232
0
votes
2
answers
72
views
Delete rows without budget and act
I've a table data
For the same ER and month, if there's no data for scenario budget_N or act_N, so no insertion in the final table:
This is what I think a good point to start
select *
FROM ...
- 259
0
votes
2
answers
299
views
Insert INTO #TempTable is fast but SELECT * FROM #TempTable is very slow, why and how to improve performance?
I need to insert into temp table "#Temp" 300 000 rows and 30 columns from different tables, I created indexes and inserting is very fast, it needs about 3 seconds. But then, when I want to ...
0
votes
0
answers
497
views
Long HTMEMO waits when deleting from columnstore index
I've a table that has a clustered columnstore index having 8 billion records. Once in a day, there is a process that inserts, updates and deletes data from this table with around 1-30 million record, ...
- 86
0
votes
0
answers
24
views
How to upload an SQL query to SSRS that returns 5 tables, ensuring that when the report is exported to Excel, these tables appears on separate sheets [duplicate]
Each table has different columns that's why I can not use UNION queries with an additional column to indicate which query produced each row.
Also I tried to use additional parameter and IF - ELSE ...
2
votes
2
answers
665
views
Date as a string when exported to CSV
Can anyone help convert a Date in SQL Server 2016 to a string format, 'January 2024'? When I use the various methods below, the data in the CSV file still shows 01/01/2024.
FORMAT(COMPLETED_DATE, '...
- 575
-1
votes
1
answer
136
views
What is maximum row count possible for In-memory tables in Microsoft SQL Server? [closed]
We are looking to use in-memory tables for permanent and temporary tables used by T-SQL stored procedure based ETL pipeline. Goal is to fasten processing since it takes about 20 minutes for 10,000 ...
- 157
0
votes
0
answers
163
views
Old tempdb files visible in sys.master_files block new ndf from creating at instance start
Some time ago I noticed that one of our servers has old tempdb ndf files still in sys.master_files.
Of course sys.databases or sys.database_files are actual.
Problem is that after an instance restart ...
- 1
0
votes
0
answers
51
views
SSRS 2016 Database Renaming issue
A predecessor made a mess of a previous SSRS upgrade to SQL Server 2016 and we're stuck with the database named as ReportServer_New (and ReportServer_NewTempDB). My manager has asked if it can be ...
0
votes
1
answer
92
views
Daily schedule - 'concatenating' multi-time periods into one block
I am working (in SQL Server 2016) on dental schedules, and by using LEAD (over PARTITION ...
I can produce data of this format:
Chair
Date
StartTime
EndTime
Blocked
Booked
Session
Cent 1
2025-03-03
...
-2
votes
1
answer
106
views
Calculate the end date of partition rows using the start date of the following partition
How can I write a CTE in SQL Server to get the End_Date for a given partition of rows, where the partition is by Person & Type, with a new partition starting if the Type changes within a person. ...
- 110