Questions tagged [t-sql]

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

1
vote
0answers
23 views

Is there a way stop the “.” (period) acting as a word breaker in FullText Indexes?

I know the period is a word breaker however I want to be able to search the fulltext catalog for a field containing the phrase "ASP.Net" or similar. Currently my search returns 'ASP' and 'Net' without ...
0
votes
1answer
29 views

Write update in more efficient way with joins

How can I write this update with joins in 'more sql' way? I have been told that this is weird coding: -- Updates recipient's account UPDATE table1 SET balance = balance + @amount WHERE ...
2
votes
1answer
28 views

How to read T-SQL time statistics?

I have a simple stored procedure as an example, which reads a table with 3 rows. CREATE PROCEDURE demo_procedure AS BEGIN SELECT * FROM my_table END When I run it with time ...
6
votes
3answers
166 views

Counting occurrence of words in table is slow

Consider these simplified tables: CREATE TABLE dbo.words ( id bigint NOT NULL IDENTITY (1, 1), word varchar(32) NOT NULL, hits int NULL ) CREATE TABLE dbo.items ( id bigint NOT NULL ...
0
votes
1answer
42 views

Stored Procedure error “Must declare the scalar variable”

I am trying to create a stored procedure with dynamic SQL inside of it. The proc receives 5 variables, 3 of which are names of columns and fields and another 2 are for OFFSET and FETCH NEXT part. I ...
1
vote
1answer
21 views

How to update two records with two different values with two 'where' conditions at once?

In T-SQL, how to have this: DECLARE @amount decimal(18,2) = 1000 UPDATE account SET balance = balance + @amount WHERE accountID= 1; UPDATE account SET balance = balance - @amount WHERE ...
1
vote
1answer
24 views

List user-defined schemata

If I create schemata using... CREATE SCHEMA [schema_name] AUTHORIZATION dbo ...is it possible to obtain a list of these user-defined schemata? I can see that there's an INFORMATION_SCHEMA.SCHEMATA ...
-4
votes
0answers
22 views

Numbering in report for TSQL [on hold]

I would like to ask on how I could get the rownumbers() with the following result MainID ClassId SubId 1 1 1 2 1 3 1 4 1 1 4 2 1 4 3 1 4 ...
11
votes
1answer
793 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
30 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
47 views

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

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
53 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
42 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
64 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
32 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
17 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,...
1
vote
1answer
61 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
79 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
49 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 ...
0
votes
1answer
38 views

Products in e-Commerce SQL DB

My application will have products belonging to 1 company. Each of those products will: Have at least 1 quantity Have 0 or more discounts Have 1 or more attributes Belong to 1 category, which can have ...
6
votes
1answer
87 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
34 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
906 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
59 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
66 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
61 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
40 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
81 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
54 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
25 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 ...
2
votes
3answers
104 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
51 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
1k 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
64 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
36 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
43 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 ...
4
votes
2answers
117 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
20 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;...
3
votes
4answers
262 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 ...
-1
votes
0answers
39 views

Transforming rows into columns using a specific condition

I have a table with the structure as attached in the Table Structure file.  Student Id     CourseNumber   ClassNumber InstructionID  InstructorDept Grade Pass/Fail But there are multiple students ...
3
votes
1answer
51 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
44 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
34 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
8 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
45 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
0answers
38 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
41 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
25 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
28 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
38 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 ...