Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,931
questions
4
votes
1
answer
82
views
SQL: finding unique multi-column value triplet matching multiple rows?
TL;DR: using Transact-SQL/SQL Server, how can I find the rows of a table which match a single "solution" triplet of column values (ColA, ColB, ColC) for some set of values in another column?
...
0
votes
1
answer
34
views
SQL Server 2019 how can I create an alert for when service broker messages are aggregating in the queue and not being processed?
How do I create an alert in SQL Agent, to notify me once service broker message queue exceeds 300 messages? I am trying to get a notification to an operator in SQL Agent when this occurs.
- 25
0
votes
1
answer
50
views
How to map the newly created records in parent table with the child table?
have a table
Employees
Id Name EmployeeType_ID
1 John 1
2. Mathw 2
EmployeeTypes
Id TypeName
1 Perm
2. Temp
3. Regular
Now I am copying data from one server to ...
0
votes
0
answers
21
views
How to convert correctly from datetime2 data-type to small date and to elliminate HH:mm:SS.000000 padding
I have created a table with a column with a date data type which correspond to a date data in yyy-MM-dd format.
In the database it presents it as if the data type is datetime2(7).
More generally all ...
-3
votes
0
answers
32
views
Why case..when get a table scan ? how to workarround?
When I use CASE .. WHEN .. END I get an index scan less efficient than the index seek.
I have complex business rules I need to use the CASE, is there any workaround ?
Query A:
select * from [dbo]....
- 1
-3
votes
1
answer
46
views
Retrieving range of values from a lookup table using SQL
I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
2
votes
3
answers
64
views
How do I restore multiple transaction log backups after restoring a full backup SQLSERVER 2019
I am creating my DR strategy, and my full back-up recovers fine.
The full backups are taken with a modified version of the Ola Hallengren scripts.
The file timestamp of the full backup shows 07/26/...
- 25
5
votes
2
answers
123
views
T-SQL Update with Max and Min value from another table
I don't have much experience with SQL queries and I'm trying to learn and understand from my mistakes.
I have two tables:
CREATE TABLE #TempTest
(Id INT,
Number INT)
CREATE TABLE #TempTest2
(Id INT,
...
- 237
2
votes
2
answers
70
views
"NOT Column LIKE pattern" vs "Column NOT LIKE pattern"
Is there a logical or performance difference between these two seemingly equivalent expressions?
WHERE NOT [Column] LIKE '%pattern%'
WHERE [Column] NOT LIKE '%pattern%'
...or do both result in the ...
- 149
-2
votes
2
answers
41
views
How to give same number if it exist on table or new number if it not exist based on part value?
I work on sql server 2019 i have table #parts have group of numbers on Part Value Field .
my issue how to give unique number to every group of numbers on Part Value Field
if group numbers on part ...
- 103
-3
votes
3
answers
49
views
INSERT INTO.. SELECT with default keyword [closed]
Could not find anything related to this issue I am having, google only seems to return how to use the default keyword with INSERT INTO.. VALUES.
I have a table with the following columns:
...
- 7
1
vote
1
answer
60
views
HASHBYTES() result of another HASHBYTES(), OR CHECKSUM_AGG() aggregate for HASHBYTES()?
I want to aggregate a single HASHBYTES() from the results of another HASHBYTES()... something like CHECKSUM_AGG() does for CHECKSUM()...
I have been able find how to make a single HASH per row of ...
- 11
0
votes
0
answers
38
views
Grant permissions only if an object exists, but without mentioning the object twice
We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
- 101
3
votes
1
answer
325
views
Calculate median of values in T-SQL
I've got a piece of code (stored procedure) written in T-SQL that inserts into a table several data from a temporal table as shown here:
INSERT INTO [myschema].[CalculatedData](
Year,
Month,
...
- 139
2
votes
3
answers
93
views
SQL Server Select orders which have all of a set of items
I have the following tables, Order and OrderLine:
Order:
id | total
----------
1 | 55.09
2 | 62.42
OrderLine:
order_id | line_number | item | qty
---------------------------------------...
- 23
3
votes
2
answers
240
views
Nest a SQL file into another in SQL Server
I have a simple SQL file I'm using to perform some test:
DECLARE @VAR1 AS VARCHAR(100) = '12345'
DECLARE @VAR2 AS INTEGER = 54321
WAITFOR DELAY '00:00:02'
INSERT Orders SELECT @VAR1 AS COL1, @...
- 463
2
votes
1
answer
108
views
SQL Server PATINDEX issue/bug when using different case-sensitive collations
I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. ...
- 31
2
votes
1
answer
106
views
tSQLt test problem for some DDL queries
I am a SQL Server DBA. To automate some of my daily tasks, I need to write stored procedures.
Creating functionality without testing does not make sense to me.
For creating and running my tests, I ...
- 143
0
votes
1
answer
75
views
JSON - How do I get JSON Child Array into one column - OPENJSON in Synapse
I've broken this down with executable code. I need to get the lang and value columns into a single record. Any ideas on how to go about this:
DECLARE
@JSONMain NVARCHAR(1000)
SET @JSONMain = N'{ &...
- 173
0
votes
1
answer
61
views
When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?
I work on SQL server 2017 I have table #Pressfeature as below
create table #Pressfeature
(
PartId int,
PressName varchar(300),
TechId int
)
insert into #Pressfeature(PartId,PressName,...
0
votes
0
answers
74
views
Linked Servers - MS SQL Server Migration from 2012 to 2017
I'm preparing migration for whole server SQL Server instance 2012 to 2017 (new project, don't know the old environment in details), but have question here only about the Linked Server.
This is the ...
Spar
-3
votes
2
answers
44
views
How to get numbers that have IE in case of data type float?
I work on sql server 2017 i face issue when save data on float data type it display as
IE
so how to get data only that display as IE on float data type
CREATE TABLE #TestTable
(
...
- 103
-1
votes
3
answers
121
views
How to select Max date that is valid?
I have a date as varchar (dont ask why). I need to select Max(date). However, date can be faulty, like 2034-34-34. I would like to select max date that is valid and not greater than today. It's t-sql.
...
- 133
3
votes
1
answer
77
views
How to get call stack info in SQL Server's catch block?
When I have multiple errors in my query, and I use try catch and error_message() function in my catch block, I only get the last error message.
For example, if my errors are:
Msg 2714, Level 16, State ...
- 179
-3
votes
1
answer
95
views
How to convert data separated pipe to rows?
I work on sql server 2017 I have table name #countriesData data as below
my issue here How to convert countries column data separated pipe to rows .
meaning every rev and company and year will have ...
- 103
1
vote
1
answer
72
views
Resource consuming query
I have found below CPU consuming query with a query cost of 1540.
I have tried creating appropriate indexes which reduced the query cost to 1234 but still, the query is expensive.
Index Created :
...
- 11
0
votes
3
answers
84
views
Using Boolean Fields in a View to Identify Linked Tables
I am using SQL Server 2019. I am using SQL Server Management Studio on the client side.
Suppose I have the following data model:
-- A record of the overall test of a widget
CREATE TABLE [TestRecords]
...
- 157
-2
votes
1
answer
227
views
Row_number to not increment if values on three columns are the same from the row before
Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic:
...
- 617
2
votes
3
answers
188
views
Generate JSON with property names that include dot using SELECT FOR JSON
I need to generate the folowing JSON payload (shortened) from a table in SQL Server. Please note the dot in the property name. This is a special syntax called OData.
{
"Id" : "A1"...
- 23
0
votes
0
answers
36
views
Exporting multiple TSQL result sets into one spreadsheet using Powershell
I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table ...
- 75
0
votes
1
answer
32
views
Setting up reporting and alerting in SQL Server through TSQL and Powershell
I am trying to automate monitoring on SQL Server through PowerShell and TSQL, to get alerts through mail whenever some issue is there.
I have gone through many articles over the internet and tried the ...
0
votes
1
answer
70
views
SQL query to group results on matching record ID
I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
1
vote
0
answers
101
views
Any benefit creating temp tables with random prefixes in SQL Server to control latch waits on tempdb database
Recently I came across reading in one of the blogs that if a temp table name is prefixed with different/random names (in different procedures) that could benefit in reducing the concurrency issues / ...
- 490
-1
votes
2
answers
248
views
Must declare the scalar variable "@Name"
I need your help please since I am stuck here and not sure how to fix this.
Trying to run this code below and getting this message (Must declare the scalar variable "@Name").
How can I pass ...
- 57
3
votes
1
answer
564
views
Is there a way to Delete inserted data, or vice versa?
I'm trying to get Data inserted and then Deleted in the same query.
I'm trying to delete it in chunks to not cause LOG issues:
DECLARE @BatchSize INT
SET @BatchSize = 100000
WHILE @BatchSize <> ...
- 7,156
2
votes
1
answer
57
views
ownership chaining not working with sp_cursorfetch
ownership chaining is not working for me in some cases. I use Microsoft SQL Server 14.0.3370.1.
This is the setup for my tests:
create function fnTest() returns int
begin
return 3
end
go
create ...
- 121
0
votes
0
answers
23
views
Move two or more columns as starting columns in TSQL depending on values
AnimalId
Date
Remark
Number
PA
PCountA
PB
PCountB
PC
PCountC
PD
PCountD
AN001
2022-01-25
LR
22025002
coli
100
Strep ag
100
Strep s
100
Staph a
100
AN001
2022-01-25
12:10am
22025002
coli
300
Staph a
...
0
votes
1
answer
47
views
How can I set-up SQL Server alert for when table got corrupted?
Recently one of the database tables got corrupted in production's SQL server. I am trying to create an alert for this kind of issue. I don't see exact Error number or severity to choose from while ...
0
votes
0
answers
33
views
The configuration Option for a column is not unique error - sql server
I have a table with 5 columns. regionId is the primary key. One of the columns, regionKey, is a sql_variant type.
Create Table #Region
(
regionId int identity(1,1),
regionName varchar(100),
regionKey ...
0
votes
2
answers
119
views
How to manipulate the output of a GROUP BY CUBE query changing the NULL values generated for the groups?
I wrote a query to sum salaries grouped by Country and Gender. I don't want the rows to show NULL or empty values for the totals, instead I want it to show Total and Grand Total to better describe the ...
- 1
0
votes
1
answer
29
views
Inhibiting results to pane in SSMS (TSQL language) via script
I found that one could use a menu to "discard" results in SSMS (TSQL language). Please see: Original Question about inhibiting results and its answer.
However, I was wondering if there is a ...
- 125
1
vote
1
answer
62
views
Log Replay on top of SQL database files captured during VSS snapshot failed
I am having a SQL Server and want to perform hourly VSS based backups (will store the VHD separately in a disk) and perform log backups in every 10 min interval. And with the help of backed-up VHD and ...
2
votes
0
answers
131
views
Adding a new column to a table that has more than 1 billion rows
I have a table that has more than 1 billion rows. I have a requirement to add a new column to this table.
Alter Table mylargetable
Add newColumn varchar(50) NULL
I'm concerned that this might take a ...
-3
votes
1
answer
41
views
How to write select statment that get data based on partid from last date to current date with filling gapes?
I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) .
based on last date exist per partid
and on same time if there are any ...
- 155
0
votes
1
answer
29
views
Convert 7 columns in two rows to 14 columns in one row
I need a T-SQL script to change two rows to one row. The rows are grouped by Student ID and Unique Course Identifier. The second row columns in the groups for the Days, Start Time, End Time, Start ...
- 5
3
votes
3
answers
170
views
How to rename logical file names in recovery pending database that cannot be recovered?
Here's the situation:
Someone renamed database A to A_old in SSMS
They did not rename the physical nor logical file; physical file was renamed somehow to A_old.mdf
They then created a new database ...
- 141
2
votes
2
answers
157
views
Create unique index based on two columns (bi-directional)
I want to make a check constraint or a unique index to validate that I'm not creating duplicates within my table based on 2 columns, I do not want the same two IDs in these columns.
Thinking about ...
- 123
2
votes
2
answers
137
views
Temporarily disable SELECT for one table (during an update)
We have a dedicated data mart SQL Server with several really big tables that are updated one partition at a time via parametrized SSIS loop. Quite often when clients try to read such a table during an ...
- 31
0
votes
1
answer
82
views
Return SQL Agent Job status in SSRS when executing job from SSRS
I'm calling a SQL Agent job from SSRS and using a parameter to determine which job to run.
I can run the job(s) just fine. What I'm looking to do is return a message back to the user based on the job ...
- 309
3
votes
1
answer
893
views
Very slow update statement taking up to 11min to update 20 or 0 rows even with indexes
When I update 20 rows or no rows, it takes 11 minutes.
I mean 20 rows or no rows by different on m.MaximumReflowTemperatureID <> r.z2valueid between two tables.
Why is my update so slow, even ...
- 155