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
11
votes
1answer
846 views

Can we put an equal sign (=) after aggregate functions in Transact-SQL?

I have encountered a script like this: set @sum = sum = (case when somecol1 is null then DATEDIFF(d,[somecol2],somecol3) else 0 end) I can't understand the meaning of the equal sign (=) after the ...
0
votes
1answer
33 views

How to structure T-SQL query with many left joined “attributes”

I am writing a T-SQL query for a complex report, with a unique key, and have run into a bit of a performance problem. I am not sure where it's coming from, but I thought I'd ask how it should be done....
0
votes
1answer
52 views

Cannot find slow (frozen) running query in Query Store [closed]

I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes ...
1
vote
1answer
58 views

T SQL Cte Previous record calcuation

I'm using SQL Server 2016. I have the below table: SKU Shop Week ShopPrioirty Replen Open_Stk Open_Stk Calc 111 100 1 1 0 17 ...
0
votes
0answers
86 views

How to calculate count of rows of Memory-Optimized Table?

Performance is important for me so I don't use select count(*) from MyTable. I use the following query to calculate rows count: SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [...
2
votes
2answers
75 views

What is the best way to automate a migration of multiple SQL Databases to a different hard disk?

We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names ...
1
vote
0answers
35 views

Determine if query comes after cascade update

We have a situation that in a trigger we are calling one procedure to update/insert values in another table. Now, the new requirement asked that we have to cascade update foreign key on that table (...
1
vote
1answer
38 views

Display all parent records but only one record of child records of a parent record

I have encountered an issue while writing a stored procedure for displaying some records of a table. The following code is to illustrate the issue. declare @t table( Id int primary key identity(1,...
2
votes
1answer
727 views

UPDATE table using CTE

I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works: WITH cte AS ( SELECT AllTimeRank, r = RANK () OVER (PARTITION BY ...
2
votes
1answer
212 views

Does T-SQL have a Schema search path?

In T-Sql, a user has a single default schema. Is there a concept of schema search path similar to a postgres search_path? The question behind the question is, if I use schemas as namespaces for ...
2
votes
1answer
77 views

Run Querys on List of Databases (For Each loop?)

I have a selection of about 15 databases on an SQL server that I want to run some query's on. I do not want to affect any of the roughly 10 other databases. Specifically, I want to run the following ...
6
votes
1answer
100 views

Sql Server delete syntax

I recently faced a delete syntax I was unaware of. delete #fooTbl from #fooTbl where attr ='some' The Official Microsoft documentation states: DELETE FROM [database_name . [ schema ] . | schema. ] ...
0
votes
1answer
35 views

How do you combine the input of three parameters into one?

I'm working on a query that will run via C# to SQL server. I'm trying to make the query flexible and provide parameters for all the inputs. Here is the query: SELECT Test_Matrix.tblTestMatrix....
9
votes
3answers
1k views

Why does this query, missing a FROM clause, not error out?

So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!? SELECT 1 ,r.id ,'0D4133BE-C1B5-4141-AFAD-...
1
vote
2answers
2k views

Inserting result of stored procedure into a table with additional column

I've found hundreds of articles about how to insert output of stored procedure into a table but nobody goes a step further and explains how to insert additional data as column. I'm looping through a ...
5
votes
1answer
82 views

MSSQL: Generate a 8 digit serial/Barcode number from a prefix number

I have a complicated task ahead of me and I really could use some of your brains to help me out. At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix ...
1
vote
2answers
802 views

SQL Server default collation vs database with different collation - potential problems?

I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems. ...
2
votes
1answer
108 views

Backlog Calculation

I need to create a report that calculates the accumulated ticket backlog, but I can not get the correct result. I already tested several solutions that I found on the internet, but I'm stagnant. -...
2
votes
1answer
757 views

Invalid column name even after checking if it exists so it should skip the query part

I've got the following query: IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NotificationTableLogRows' AND COLUMN_NAME = 'DestinationAddress') BEGIN UPDATE ...
0
votes
1answer
56 views

Fuzzy searching? Any Alternatives? [closed]

I have a drugs catalogue table which is regularly updated. I need to see whether these drug names (e.g. ‘Paracetamol 500mg’) are referred to anywhere within a lengthy text field (up to nvarchar (2000) ...
0
votes
1answer
28 views

Merge two row records

I have 2 tables Person Table PersonID Firstname LastName 1 a b 2 c d 3 e f 4 g h 5 i j ...
1
vote
3answers
112 views

Count Items not In Time Range

I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out. So, I have this table: WITH ADates AS ( ...
2
votes
3answers
97 views

Can a MSSQL job change its own active schedule?

I am planning a new job for MSSQL Sever 2016. I'd like the job to run on Sunday, every 10 minutes, until it sees a confirmation record is added to a table it checks as the final step in the job. If ...
4
votes
6answers
2k views

Only first column in ORDER BY clause is sorted [closed]

It seems like I've misunderstood the concept of 'Order by'. I have a table with this structure and data: CREATE TABLE TestTest (Value1 Int, Value2 Int); INSERT INTO TestTest VALUES (1, 10), (2, 9), ...
1
vote
2answers
66 views

TSQL issues with view and data operations

I can't get my head around this sooo - help! ;) Working with SQL Server here. I have a few tables that hold customer sales, products, customer and other data imported from SAP. I have create a view ...
0
votes
1answer
46 views

Deleting duplicate based on latest datestamp entry

I have a table and I'm trying to remove all the duplicate and keep the the rows that has the latest datestamp. Here is the table: email address orgin_date new_opt_in_date datestamp 123@...
-1
votes
1answer
110 views

T SQL Cursor is only updating row with same value [closed]

I'm using MS SQL Server and I have the below table 'tmp_AVG_Weekly_Sales': RowID SKU Shop Week Avg_Value LAMBDA PMF Value 1 ABC 200 2 1 2 0.13 ...
5
votes
2answers
202 views

Find Ancestry From JSON

I have a hierarchy that looks like this: As JSON in TSQL it is this: declare @Employees nvarchar(max) = '{ "person": "Amy", "staff": [ { "person": "Bill" }, { "person": "Chris", ...
2
votes
1answer
1k views

Properly escape characters in a string for JSON

With the below example, how do I escape the double-quote characters that are in the @data variable? declare @data nvarchar(max) = N'"TEST"'; declare @jsonFragment nvarchar(max); declare @id int = 999;...
2
votes
4answers
691 views

Query to find customers that have deposit in all branches located in a specific city

I have 3 tables as follow: Customer (CustomerName , CustomerCity , CustomerStreet) Branch (BranchName , BranchCity) Deposit (CustomerName , BranchName , AccountNumber , Balance) Here I show you ...
3
votes
1answer
114 views

Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?

I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
0
votes
3answers
417 views

Sum of decimal values that come in a string

Is there an easy way to get the sum of all decimal values in a string column. Example: "ABC $3.25, DEF $2.50, HIJ2 $0.25" "ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50" I want to add only the values ...
2
votes
1answer
69 views

Object id error in sql server

I want to know the object referenced as an associatedObjectId in a deadlock graph, so I ran the below query: SELECT OBJECT_NAME(1234560112364076) I get the following error: Arithmetic overflow ...
0
votes
1answer
27 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
2
votes
1answer
196 views

How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?

How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly. ...
0
votes
1answer
52 views

Alter view after table drop

I want to alter the view for the drop tables. For an example: View: create view View1 as select * from table1 union all select * from table2 union all select * from table3 union all select * from ...
0
votes
2answers
84 views

Example of MS Access SQL IIF to T-SQL

Given the syntax rules shown below Access: IF(Condition, ReturnIfTrue, ReturnIfValue) T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END What would the below be formatted to T-SQL ...
2
votes
2answers
2k views

How to detect whether a key is present in a JSON object using TSQL?

This is an interesting one. Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d. Imagine this object is passed to a stored procedure and every key is ...
1
vote
1answer
33 views

Designing Calendar table - datatypes

I'm in the process of designing the Calendar table. I took some inspiration from Database design book by Louis Davidson. What I'm confused about are the data types. Example: MonthName varchar(10) ...
0
votes
1answer
357 views

SSIS data flow gets stuck reading table with no errors

When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
3
votes
2answers
215 views

SQL table with same name as catalog view

When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be ...
4
votes
1answer
346 views

Forcing execution plan with local join hints

I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between ...
4
votes
1answer
728 views

Why does JSON_QUERY within CASE statement fail to remove escape characters?

It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement. Example code below. declare @data ...
2
votes
2answers
207 views

Generic type functions in T-SQL

Consider the following function CREATE FUNCTION dbo.SafeDivide ( @a numeric, @b numeric ) RETURNS numeric AS BEGIN RETURN CASE WHEN @b = 0 THEN 0 ...
2
votes
1answer
322 views

What is background job queue in sql server? How to create a background job?

While exploring the SQL server I came up with following views: sys.dm_exec_background_job_queue; sys.dm_exec_background_job_queue_stats; Official site tells that It Returns a row for each query ...
2
votes
1answer
233 views

What types of locks are held on global temporary tables?

I am wondering if: the isolation level is Read Committed data is inserted or deleted or read are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table ...
0
votes
1answer
36 views

Disable Saving/Exporting Results of TSQL Query in SSMS?

I really don't want to go in to too much detail as to WHY I want to do this. Just know that it would be ideal for my use case. Is there a way to disable a specific database role from using the Copy, ...
2
votes
3answers
224 views

Not getting distinct results using case query

This is the query I am running. I am getting the correct results except they come in on a different line for each corpstorenum, so i am getting : Corpstorenum,BuffetYN, deliverybeeryn, dineinbeeryn,...
2
votes
2answers
877 views

How to replace multiple parts of a string with data from multiple rows?

Here's a fiddle to show what I'm after. Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in ...
2
votes
1answer
1k views

Debugging TSQL in SSMS [closed]

What techniques can I use to debug TSQL code now the debugger in SSMS 18 has been deprecated/removed?

1
3 4
5
6 7
52