Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,983
questions
3
votes
2
answers
155
views
sp_executesql Not Working with Parameters
I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I've been tearing my hair out the last few hours over this. I would really appreciate any ...
- 158
0
votes
0
answers
48
views
Why do Sql Server In operator behaves differently and return nothing? [closed]
I am using sql server 2017.
I am trying to use the in operator, but it behaves me so differently.
What I am trying to do is
I created a cte and split the comma separated string into array and use this ...
- 11
2
votes
1
answer
56
views
Assigning values to multiple @variables using SELECT
I've encountered some T-SQL code which seems to rely on behaviour I found unexpected.
When assigning values to multiple @variables in one SELECT statement, when one @variable is dependent on another, ...
- 581
0
votes
1
answer
31
views
I want to find the max number of characters of all varchar and nvarchar columns in DB
I want to find the max number of characters of all varchar columns in DB.
I have a query like this:
SELECT TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
...
- 3
2
votes
1
answer
95
views
MIN function returning multiple rows after adding column to SELECT
I'm trying to get the minimum value of Price together with product Description in the table ProductVariation.
When creating my query this way:
SELECT productId, MIN([price]) AS price
FROM ...
- 123
0
votes
1
answer
38
views
JOIN Temp Table and table on other linked server poor perfromance
I have a query on my FirstServer like this one:
DROP TABLE IF EXISTS #TEMP;
CREATE TABLE #TEMP
(
ItemId NVARCHAR(100),
[Date] DATE
);
INSERT INTO #TEMP(ItemId,[Date])
(
SELECT 'test', '11/...
-1
votes
1
answer
27
views
SQL Search Does not Work on a remote database [closed]
I am trying to us the Red gate SQL search as the image is attached, but it does not work on my system, and it is always showing me that it is indexing, images attached, I also see a blue icon on my ...
0
votes
1
answer
50
views
Sql server Intellisense is not working
My sql server management studio intellisense is not working, below is my SSMS version and screen shot that I have tried, my SQL server is not installed locally but I am accessing the sql server ...
-2
votes
1
answer
37
views
Binary data type in SQL Server and joins [closed]
Will joining on a binary data type be slower than e.g. char? E.g binary(10) vs char(10). Storage is 10 bytes for both.
- 677
1
vote
2
answers
27
views
Change Tracking SYS_CHANGE_COLUMNS on Update operation
I would like to get an understanding of why on an update operation in the change tracking table, the SYS_CHANGE_COLUMNS can be NULL. I would expect the SYS_CHANGE_COLUMNS will be populated.
In the ...
- 21
8
votes
2
answers
649
views
SQL Modulo Function gives the wrong value?
In Javascript, Excel, Python, and a scientific calculator, the modulo operation
-34086.1576962834 % 360.0 <-- python
=MOD(-34086.1576962834, 360.0) <-- Excel function
gives a value of 113....
- 2,168
0
votes
0
answers
53
views
TSQL (SQL Server) Table-valued function whose return-form depends on parameters
I have a table who has an arbitrary number of bit columns which can change over time.
Key
Bit1
Bit2
Bit3
'something1'
1
0
0
'something2'
1
1
0
'something3'
0
0
1
What I am trying to do is set up ...
- 101
0
votes
0
answers
64
views
Create logins for all active directories groups named ("Domain Users"), related to current Windows Authenticated user in SQL Server
I need to get all active directories locations, related to current machine, by SQL script to create logins for them. Is there a way to perform that? For now I wrote such procedure:
USE [master]
GO
...
4
votes
2
answers
262
views
Case-Sensitive Collation on a select query in SQL Server
Our SQL Server's collation is set to SQL_Latin1_General_CP1_CI_AS
I have a requirement where we have the same values with different cases but SQL Server treats them the same since our collation is set ...
- 67
0
votes
0
answers
100
views
How can i combine two select columns value and values should be unique and optimized?
We have Microsoft SQL Server 2017 (RTM) - Microsoft Corporation Web Edition (64-bit) and Compatibility Level is: 140.
I want to combine (Not through concatenation) the values of two columns (ar....
- 11
0
votes
0
answers
18
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 /...
- 11
0
votes
1
answer
36
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:
...
0
votes
1
answer
69
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)...
- 11
0
votes
2
answers
46
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
-1
votes
0
answers
26
views
Trying to bring in previous years data
I am working on a performance tuning on a view. The view is using joins on the same table two times to pull previous years data. If I remove the second filter the performance of the view changes but ...
- 1
4
votes
0
answers
88
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
31
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
247
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,235
1
vote
1
answer
60
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
49
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
81
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 ...
- 11
1
vote
2
answers
49
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
53
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
19
votes
1
answer
1k
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),
...
- 301
0
votes
1
answer
51
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
43
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
28
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 =...
- 624
-1
votes
1
answer
43
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
...
- 624
3
votes
1
answer
236
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....
- 624
3
votes
1
answer
86
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.
...
- 951
1
vote
1
answer
42
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 ...
- 252
6
votes
3
answers
730
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 #...
- 624
-1
votes
1
answer
93
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
...
- 101
1
vote
1
answer
68
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,...
- 502
-2
votes
1
answer
78
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
60
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 ...
- 951
-2
votes
1
answer
51
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
26
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
64
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
54
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 ...
- 101
0
votes
1
answer
95
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
23
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
64
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 ...
- 297
-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
32
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 ...
- 77