Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
3,046
questions
0
votes
1
answer
30
views
How can I get the accurate and optimized grouped data by passing expression in partition by statement?
I need to get the correction and a way to group it in a better and efficient way.
What I want is that I need to group the data on the bases of RenterUserId or MainDriverUserId
in following cases /...
- 13
0
votes
1
answer
48
views
Get current current object and previous one in one row
Lets say I have a table with columns:
Name
City
Date
Value
John
NewYork
2022-01-02
10
John
NewYork
2022-01-03
20
Mark
NewYork
2022-01-01
30
Mark
NewYork
2022-01-03
40
now main query qould be:
...
- 41
1
vote
1
answer
167
views
How can I refine my query in a best way with optimum results (for large database)?
How can I refine my query in a best way with optimum results (for large database tables) ?
See the tables structure and the query below
CREATE TABLE [dbo].[Franchise](
[Id] [int] IDENTITY(1,1)...
- 13
0
votes
2
answers
575
views
How to rtrim the slash character (/)?
rtrim('txt/','/')
What if we cannot use this version of the rtrim function.. How else to remove the trailing '/' from the end of a string (if present)?
- 101
4
votes
0
answers
128
views
T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed
BACKGROUND
I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10.
I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
- 197
0
votes
1
answer
809
views
How to get definition of partition function programmatically?
I have tables partitioned by date with various frequency space (multi-years, years, quarters, months etc.)
sys.partition_functions gives name and function_id.
Is there a way to query the actual ...
- 11
4
votes
1
answer
751
views
'SUSER_SNAME' cannot be invoked with parameters in this version of SQL Server
I was reading this blog post which contains a query that can allow you to find when a DROP was fired. I changed LIKE 'DROPOBJ%' to NOT LIKE 'DROPOBJ%' so I can return more rows:
SELECT [Current LSN]
...
- 1,494
1
vote
1
answer
989
views
SQL COUNT occurrence of column value on LEFT JOIN
I have seem similar questions on here but none of the answers seem to fit what I am looking for-
Say I have one table like so:
AssignedTo
DeptID
Type
Mary
5001
Elementary
Bob
5002
Middle
Bob
5003
...
- 173
1
vote
0
answers
57
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
0
votes
2
answers
2k
views
How to continue the INSERT even after the error
I have millions of rows to insert in SQL Server, but some of them are in error, I would like to know if there is a way for the INSERT to continue even after the error? and know which lines are in ...
- 21
1
vote
2
answers
323
views
Decrypt Symmetrically/Asymmetrically Encrypted Data in MS SQL (T-SQL) Server via Apex Crypto Class
Hope you're having a good day. This is in continuation to my previously posted question in Salesforce StackExchange. I was suggested to seek help at DBA SE in order to find a way to extract Symmetric ...
0
votes
1
answer
2k
views
How can I perform a join using a concatenated field? [closed]
I would like to know how to approach performing a join using a concatenated field.
I have one table Task that has a value User. I want to join the Users table which has the value Logon the inner join ...
- 37
21
votes
1
answer
7k
views
Why is my ORDER BY in STRING_AGG not always working?
I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.
CREATE TABLE HashTable(
RecordID VARCHAR(255),
...
- 321
0
votes
1
answer
214
views
Change the schema of table while restoring the database from bacpac file?
I have a bacpac file which I generated from SQL Server. And now, I am restoring the database using this bacpac file into the Azure Managed Instance.
Here, I am trying to change the schema of the table ...
- 1
1
vote
0
answers
178
views
How do I change the database owner in Synapse Analytics Serverless SQL Pool?
I am working with Azure Synapse Serverless SQL Pool. A former employee created the databases in the pool, and is listed as their owner. Consequently, I cannot drop the login.
I would like to change ...
- 111
0
votes
1
answer
64
views
If a table column corresponding to different target columns in different tables, how to join this column in SQL?
While I was reading the documentation of sys.allocation_units, I found the following description for its container_id column.
ID of the storage container associated with the allocation unit.
If type =...
- 1,149
-1
votes
1
answer
230
views
Does SQL Server support join type "LEFT SEMI JOIN" in your query?
While I was watching this YouTube video, I found the author seems using SSMS.
I never know SQL Server supports the LEFT SEMI JOIN syntax, so I give it a try with the following code.
USE tempdb;
GO
...
- 1,149
4
votes
1
answer
328
views
Is this valid SQL Server Update syntax?
Today I was reading some of my database notes that were taken long time ago. And I saw this query:
UPDATE Sales.Orders
SET (shipcountry, shipregion, shipcity)=
(SELECT country, region, city
FROM Sales....
- 1,149
3
votes
1
answer
253
views
sys.dm_exec_describe_first_result_set returns wrong data type
I have two SQL Servers 2016 connected with Linked Server. When I query sys.dm_exec_describe_first_result_set from the remote server, it returns numeric datatype for column table instead of decimal.
...
- 1,079
2
votes
1
answer
63
views
Insert rows into a table from another table row?
I've got a table Schedule
Id
unserId
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
0
10
0
1
1
1
1
0
0
1
20
1
0
0
0
0
0
0
I need to insert lines from one row in table Schedule into seven ...
7
votes
3
answers
740
views
How does this piece of TSQL work?
Why the following batch doesn't work as expected?
DROP TABLE IF EXISTS #test1;
IF 1 = 1
SELECT *
INTO #test1
FROM sys.databases
WHERE database_id <= 5;
ELSE
SELECT *
INTO #...
- 1,149
-1
votes
1
answer
2k
views
How to combine multiple rows into one, based on event id, datetime, and an action into their own columns?
I'm not sure how to word the topic title...
I have a table with data similar to below, where each row stores an event associated with alarms. For example, events might include:
alarm goes active
...
- 123
2
votes
1
answer
871
views
Converting rows to desired JSON format in TSQL
Trying to convert the data in required JSON but wasn't successful, any help or suggestions would be appreciated.
SQL Server version: 2019
Test script:
drop table if exists t1
go
create table t1(id int,...
- 532
-2
votes
1
answer
462
views
sql server agent job with xp_cmdshell to move files [closed]
i have the following t-sql to move files from 1 directory to another.
I tried both versions. job step succeeded with no errors, but files do not move.
any thoughts/suggestions?
EXEC xp_cmdshell ' ...
0
votes
1
answer
262
views
View or build create table schema statement programmatically with T-SQL [closed]
Is it possible to view or build create statement for selected table?
For example SSMS has feature Table > Script Table as > Create To > New Query Editor Window that can generate similar ...
- 1,079
-2
votes
1
answer
93
views
CASE clause doesn't work on column sql server [duplicate]
this is my query
SELECT Name,
Salary,
ReportsTo,
CASE WHEN ReportsTo = NULL THEN 'Manager' ELSE 'Employee' END AS R
FROM Employees
but when I run it, the R column shows ...
- 33
0
votes
0
answers
35
views
How do I use this script to find why my recovery model changed from full to simple?
I have found a post that has a script that will provide details for my question. When I execute the script no results return. What do I need to change within the script to return results for the ...
- 37
0
votes
1
answer
251
views
How to enhance performance when compare two table feature value to run faster?
I work on sql server 2019 i face issue when compare feature values between two tables
Based on RecomendationId .
it take too long time to display result
time that take it 5 minute .
total rows on ...
- 103
0
votes
0
answers
195
views
T-SQL: Slow performance on Stored Procedure Sending Data to LinkedServers
I am new to T-SQL and I am facing an issue with a slow processing stored procedure. The purpose of this procedure is to transfer table data from child databases up to their corresponding parents.
The ...
- 11
0
votes
1
answer
199
views
Doesn't T-SQL support correlated CROSS JOINs?
This query works
SELECT QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM SWOPS.MIGR.QMCAddresses QMC
CROSS JOIN (SELECT STRNAME FROM SWOPS.MIGR.EB_DICT_STREET_QMC WHERE FK=8055909) t1
CROSS JOIN ...
0
votes
1
answer
34
views
Generate a script to drop a DB user
I have a script below to search ALL DBs for a specific user:
DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250))
SET @DBuser_sql='SELECT ''?'' ...
0
votes
2
answers
253
views
Select newest single field of a combined SELECT DISTINCT?
I have the following table in SQL Server 2019.
The row with the highest id is the newest row.
Column obj_id has an immutable identifier for an object.
Column obj_name has the name of the object at ...
- 307
-1
votes
1
answer
28
views
T-SQL: Add another column with some commands
i have a script below to find all the db fixed role db_datawriter. this is working fine, no issues. what i want to do is come up with a series of commands:
drop the member from the db
So the 5th ...
0
votes
1
answer
97
views
Working with varchar field in Clustered Index. (Performance Test)
I am using Microsoft SQL Server 2016.
For my products table;
I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)).
I know ...
- 91
0
votes
1
answer
217
views
Concatenate column with aggregate of another column
This is my table.
And the result I want is below. I cannot use where stage = ? because that column values can be anything.
I tried with this query but it returns the null values.
select case when [...
0
votes
1
answer
319
views
T-SQL update table A with values from table B (hard coded)
I have table A, where I have 164 distinct values for column CODE as follows:
Code
May-Update-DTO
Terminated
Breaking
One-Two-One
"None"
etc
I also have a CSV file where all of these ...
2
votes
1
answer
77
views
variable grouping? or filling available slot from pool
i am creating a execution list for some kind of engine, which executes 10 tasks at a time.
the tasks are two types: standalone and dependent.
dependent ones should be executed in ExecutionOrder, ...
- 53
1
vote
1
answer
2k
views
Query optimisation to remove nested loop
I have below query which is having high Sub-tree cost. I have created appropriate Indexes for for the query which are getting seek but, still the sub-tree cost in high.
however, I can see nested Loop (...
- 33
4
votes
1
answer
1k
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
226
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.
- 37
0
votes
1
answer
557
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
238
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
1
answer
717
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 ...
3
votes
4
answers
1k
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/...
- 37
6
votes
2
answers
3k
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,
...
- 247
2
votes
2
answers
112
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
124
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
631
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
512
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 ...
- 31
0
votes
1
answer
699
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