Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

Filter by
Sorted by
Tagged with
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, ...
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' ...
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 ...
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 ...
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 ...
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 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 ...
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 ...
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 ...
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 =...
-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 ...
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....
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 ...
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 #...
-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 ...
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,...
-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 ...
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 ...
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

1
2 3 4 5
60