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 ...
