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
1
vote
1answer
83 views

Filter rows if value exists in different columns

We're on SQL Server 2016 and 2019 I have a data-set where I'm rolling up a parent/child relationship. I'm doing this because some of the parent rows will return a SalesValue of 0 when their children ...
0
votes
3answers
33 views

Query to fine "Order_num" that has no two equal "product_name"

I have a table with below structure: create table A_TEST ( order_num int, product_name varchar(50), product_amount int, product_price int ); Sample data : insert into a_test (...
0
votes
2answers
78 views

How to tell if SQL is autogenerated by some tool [closed]

I am looking into some stored procedures and the code is more or less non-readable e.g. there are 15 main joins in one statement and the level of nesting of the subqueries is e.g. 7 in order to simply ...
3
votes
1answer
131 views

Recursive CTE Multiple Levels [duplicate]

I have a table showing which roles recursively grant access to which resources within a database. For example: The Default_Role grants access to the App_Role, the App_Role grants access to the ...
1
vote
1answer
50 views

Will SQL Server check FK restrictions between row deletes in a single DELETE statement?

I have a table that is self-referencing: CREATE TABLE [dbo].[TestTable] ( [id] [bigint] IDENTITY(100000,1) NOT NULL, [referenced_id] [bigint] NULL, CONSTRAINT [PK_TestTable] PRIMARY KEY ...
0
votes
1answer
46 views

Can I just restore the .diff file over full backup to speed up restore?

I'm doing a server migration, and i'm looking at ways to speed up restore. I have a 1TB full backup file which takes about 2 hours to restore RESTORE DATABASE [MyDb] FROM DISK = N'C:\db\202010152000....
1
vote
1answer
116 views

Job waiting for resources - is it better to kill it or let it wait

Our server is very busy sometimes and I can see my jobs are waiting for resources. Is it better to kill it and start it after e.g. one hour or let it wait until it finishes? Right now there is a rule ...
-2
votes
1answer
82 views

Convert Varchar to Datetime

I have a database field Column_A which is in Varchar(16) not null and the values are for example 20191210215033CS but I want to break it like 2019-12-10 21:50:33 Since it is a production database ...
3
votes
1answer
91 views

T-SQL equivalent to Python Pandas Concatenate

I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does. I do not want to replicate the information for it is rather large. I'd like to have a VIEW like ...
1
vote
3answers
679 views

BULK INSERT continue on PRIMARY KEY error

I get a CSV file with a several million records every few days. I need to insert them into our database. What I do is quite straightforward: BULK INSERT dbo.activity FROM 'C:\tmp\activity.csv' WITH ( ...
1
vote
1answer
207 views

Equivalent of varchar(max) in ASE

What is the equivalent of SQL Server's varchar(max) data type in ASE? By ASE I mean what used to be known as Sybase ASE, which is now owned by SAP. I'm attempting to execute a piece of dynamically ...
2
votes
1answer
154 views

Basic alternative to fn_LocalTimeToUTC

My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime. How would I go about getting the same functionality that these two functions offer manually and directly in SQL ...
0
votes
2answers
25 views

Cannot Find Job Associated Active Email Notifications

We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via ...
0
votes
1answer
24 views

Debug stored procedure that times out from year to year

We have a stored procedure that joins a couple of tables and returns json. We've added indexes and it returned the expected results without any issues for months. Btw, here's a snapshop of the live ...
0
votes
1answer
33 views

SQL Server 2017: OR and LIKE Interaction

I am using SQL Server 2017 (i.e. v14). I could use one of the following equivalent criteria in a query: x IN ('104','504','554') (x='104' OR x='504' OR x='554') (x='104' OR x Like '5[05]4') If I use ...
0
votes
1answer
63 views

Are multiple column index used in single column where clause

I have a table with a defined non clustered index on 3 columns. CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] ( [Col1] ASC, [Col2] ASC, [Col3] ASC ) Will it be used if i do a ...
0
votes
1answer
35 views

Another question about finding the last time a value changed

I know there is a simple answer to this question but just can't get my head round it. I have a table with datetime and int columns. | TimeStartTz | RowNumber | RateID | |--------------...
0
votes
1answer
42 views

Need listing of all relationships of tables SQL Server 2017

I have a ticket that I need to get a listing of all the relationships in one of my tables, I found that fairly easy here - I need to take it one more step - what my manager wants to see is all the ...
-1
votes
2answers
32 views

Concatenated string gets truncated

I have a query that i am writing to convert some heap tables with primary keys into clustered tables, but the string that i'm generating truncates on objects past a certain length it seems. SELECT ...
3
votes
4answers
562 views

SQL query for FIFO allocation of items

I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem. We have Buyers and Items. DECLARE @Buyer TABLE ( BuyerId INT IDENTITY(1,1), Name ...
0
votes
3answers
188 views

What is the cost of having many columns as included columns in a nonclustred index?

What is the cost of having many columns as included columns in a nonclustred index in SQL Server?
0
votes
1answer
67 views

Insert in to table without duplicate records TSQL

Team, Need some assistance with TSQL table1 ID |Name| pin 1 a 101 2 b 201 3 c 301 4 d 401 table 2 ID|Name|pin 3 c 301 4 d 401 5 e 501 6 f 601 I want to ...
1
vote
1answer
149 views

Eliminate filter operator before columnstore index scan operator

I have a large fact table with millions of rows called MyLargeFactTable, and its a clustered column store table. There is a composite primary key constraint on it there as well (customer_id,...
2
votes
2answers
1k views

Linked Server throws metadata error

I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, ...
1
vote
2answers
48 views

How to update group no incremental by 1 based on every group from 1 to 3?

I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3 meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc .. create table #...
-1
votes
2answers
53 views

Can an expression be used as an expression as well as a predicate? [closed]

In MySQL, Oracle and PostgreSQL an expresion like 1 = 1 can be used in where like: where 1 = 1 as well as where (1 = 1) is not null In MSSQL, it seem that: a) when an expression is a predicate, it ...
1
vote
1answer
75 views

Clustered and columnstore indexes

I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So ...
-1
votes
2answers
80 views

TSQL - Sql Injection

DECLARE @TSQL NVARCHAR(MAX) = N'SELECT 1, ''One'''; CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10)); INSERT INTO #ThisIsATable EXEC [dbo].[sp_executesql] @TSQL ; Is it safe to have input ...
0
votes
1answer
540 views

Getting the "Column name or number of supplied values does not match table definition" when I use CTEs in an IF statement inside a stored procedure

I have a Stored Procedure that has 3 parameters. The stored procedure looks like this: ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '') AS SET NOCOUNT ON DECLARE @...
0
votes
2answers
197 views

Improving Speed of Cross Apply

I posted a question on how to create a relation between 2 tables based on a spatial field https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering The performance is ...
0
votes
1answer
30 views

Get count of near points based on 2 unrelated tables

I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on. They both have a geography field. I would like to get list of all ...
-1
votes
1answer
264 views

Log backup full [duplicate]

Please I got this error message when trying to delete some rows a table on my database : message 9002, level 17, state 2, line 48 The transaction log for database 'NewHopeHospial' is full due to '...
0
votes
2answers
114 views

T-SQL query must include and exclude child rows

I have simple parent -> child (one to many) relationship. Need to return children that have all given attributes, in this case red hair and brown eyes. Table: CREATE TABLE [dbo].[children]( [...
5
votes
2answers
457 views

SQL Server not using nonclustered index

I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (...
2
votes
1answer
531 views

SQL: pivot non numeric data [duplicate]

I have a set of data which basically looks like the following: +---------+--------+ | EventId | Field | +---------+--------+ | 1 | apple | | 1 | paper | | 1 | boy | | 2 | ...
3
votes
1answer
255 views

What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

My company logs DDL on one database using a Database Trigger, looking for DDL including: ALTER_TABLE CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER When I disable or enable triggers using ALTER TABLE x ...
4
votes
1answer
405 views

Add query hints to a CROSS APPLY [duplicate]

I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM sys.objects obj WITH (NOLOCK) ...
1
vote
1answer
199 views

TSQL success or failure result of EXEC dynamic SQL

Based on the following code: /* This SP takes a query and a unique temporary table name as input... The outputs is a string with the command to create the tmp table based on the query that is given ...
0
votes
2answers
68 views

JOIN between 3 tables to show missing data from 2 tables

I have 3 DB tables, a Computer table, a Patch table, and a ComputerPatched table. Below is a table mock up to assist my explanation.... DECLARE @Computer AS TABLE ( [ComputerID] int , [...
0
votes
1answer
82 views

T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location

I am using the following query answered by Marcello Miorelli. This query serves the purpose for normal backups but fails to determine the actual backup size of all the split backups and copy only ...
5
votes
2answers
183 views

CTE query that fails to find ancestors

I have a table foo with the content So the records of the table foo can graphically be represented with this graph. When I run a procedure that should return ancestors using @starting_id=9 (...
1
vote
1answer
49 views

TSQL trigger on actual changes - update field

Background - Table that looks like this: country_id product_id flow_date update_date amount country_id, product_id, flow_date, and update_date are a composite PK, because I need to track the history ...
1
vote
1answer
56 views

Select statement take more time in sql

Statement 1 : select top 1000 pmch.Columnname1 Statement 2 : select top 1000 pmch.* Statement 2 executes quicker the Statement 1. I know that using "SELECT * " will affect performance so ...
-3
votes
1answer
46 views

Best way to update a Table

I have a SP that updates rows in a table periodically (several times a day) the SP currently gets a feed of the PK and the update value. The input feed can contain 1 - 1000 rows. I want to know if it ...
1
vote
0answers
264 views

Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

DBAs, Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over? When I hit debug (Alt-F5), gives me this error, after making some ...
1
vote
2answers
463 views

SQL Server always running

I have a personal computer where I have different instances of SQL server running (developer edition) with both integration services and analysis services (one in tabular and one in multidimensional). ...
2
votes
2answers
118 views

Recursively get ids of tree

I have a table foo. So we have a hiearchical object structure where on foo has one parent (or none) and a list of children. What I want is a sql statement that lists all id:s that can be found either ...
-1
votes
1answer
104 views

SQL AVG() Returning the wrong result [closed]

I have a function and a stored procedure that used this function, when i run the function seratelty with the stored procedure i get the correct result but when i use this function with the stored ...
-1
votes
1answer
36 views

Complex SQL Agent Job

I would like to tun the Stored Procedures on the following dates. 1st sept 2020 1st Nov 2020 1st Dec 2020 1st Feb 2021 1st March 2021 1st May 2021 1st june 2021 1st august ...
2
votes
1answer
103 views

Space Calculation for SQL Server Table: What's wrong with my query?

I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database: SELECT t.name AS TableName, p.rows, (sum(a....

1
3 4
5
6 7
58