Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,639
questions
2
votes
2answers
38 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
59 views
SQL AVG() Returning the wrong result
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 ...
-3
votes
0answers
33 views
Correct SQL query to compute customer support response time [closed]
Consider the following table:
TABLE TicketMessage
COLUMN TicketId INT
COLUMN Sender VARCHAR (either 'customer' or 'staff')
COLUMN SentAt DATETIME
The goal of the query is to compute the average ...
-1
votes
1answer
32 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
43 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....
2
votes
0answers
19 views
Validating SQL Server Change Tracking Synchronization
I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
0
votes
1answer
30 views
SQL Server :: how to move country code column to another table?
I have 2 tables:
tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG).
Like this:
CountryName TwoCharCountryCode ...
-1
votes
1answer
27 views
TSQL query searching for multiple keywords across multiple fields
I am looking for assistance in improving the search capability of my webstore. I have tried multiple methods without success. I sell diecast cars, and am missing out opportunities of products being ...
2
votes
3answers
49 views
Query to find sql server services currently running [duplicate]
Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?
0
votes
1answer
31 views
Joining Additional Table Column
I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to ...
2
votes
1answer
51 views
Populating bridge table with conditions [closed]
I have a bridge table trajectory_detail that I am supposed to populate with millions of records from the corresponding parent tables. The problem is that every value from parent table trajectory_id is ...
0
votes
3answers
46 views
Simultaneous SQL full backups of newly created databases
I would like to take a backup of newly created databases but within a minute if the user creates three databases the script can able to take only one database but for one new database which was ...
0
votes
1answer
13 views
OPENROWSET Bulk insert Text File NULL Columns
I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
0
votes
1answer
23 views
Query specific key fields from x-number of tables?
I'm building an insurance database and I need some help with best practice on fast quering data.
Let's say I have three insurance types ... Car, Home and Travel.
Common for all three types, I have the ...
1
vote
1answer
30 views
Execute SSIS (package) in SSMS using T-SQL and return a value
Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I ...
-1
votes
3answers
21 views
Set The Order Query Results Display
I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
-3
votes
0answers
48 views
I wish to create a table using SQL that provides counts at multiple levels that include zeroes
I wish to create a table using SQL that would look somewhat like the following:
County | Sex | Age Group | Ethnicity| Count
--------------------------------------------------
County1 | Male | ...
2
votes
2answers
75 views
Optimizing my query
Here's my query:
DECLARE @monthStartDate date;
DECLARE @monthEndDate date;
SET @monthStartDate = DATEFROMPARTS(2020,6,1);
SET @monthEndDate = EOMONTH(@monthStartDate);
SELECT @...
0
votes
0answers
37 views
Index usage for database: where are the sys tables located
I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
0
votes
0answers
9 views
No Data for RING_BUFFER_SCHEDULER_MONITOR
I am attempting to add a query for monitor recent CPU usage for our SQL servers into an SSRS dashboard. I have encountered numerous examples online recommending querying the sys.dm_os_ring_buffers DMV,...
0
votes
1answer
23 views
After Insert Trigger WIth MS SQL Server
I think I have all my bases covered but wanted to ask someone who knows better than I. This is my syntax.
UPDATE SI
SET autoInsert = 0
FROM inserted
WHERE SI.autoInsert = SI.autoInsert
OR SI....
-1
votes
1answer
45 views
How do you convert a cursor to CTE? T-SQL
In an effort to clarify I have added more information and removed the "simplified" code.
I have table with datetime punch information.
CREATE TABLE [dbo].[AT_Punches](
[PunchID] [int] ...
1
vote
1answer
40 views
Group by every N records but remove incomplete group that has records less than N
I need to query a table to fetch data in groups of some size say N=5 but if the size is less than 5 then the last group should be ignored
i.e every 5 records should be grouped
==> i.e first 5 ...
0
votes
1answer
38 views
“To many rows being processed at one. Reduce and try again.” error in SQL Server Management Studio
I get this error when trying to update some rows from SQL Server Management Studio v18.5:
Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch
Start Line 0] To many rows being processed ...
1
vote
1answer
21 views
Add Total Row To PIVOT Query
I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL ...
0
votes
1answer
66 views
Single user mode looses connection
So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single ...
1
vote
1answer
30 views
Combine Rows with indirect relation
I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone ...
0
votes
1answer
39 views
SP is locking due to Begin Transaction
Please read the following code
SET IMPLICIT_TRANSACTIONS ON
BEGIN TRANSACTION
DELETE FROM xxxx
SELECT * FROM xxxxxxxx
WHERE EXISTS ( SELECT * FROM xxxxx
WHERE CONCAT(xxxxxxx) = ...
3
votes
1answer
264 views
User-defined function returns first character only although RETURN VARCHAR(10) is defined?
Everyone,
I cannot figure out what is missing in the below user-defined function:
CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money)
RETURNS VARCHAR(10)
AS
BEGIN
IF (@Salary < 30000) ...
0
votes
0answers
19 views
BULK INSERT - why does FIRSTROW skip by FIELDTERMINATOR rather than ROWTERMINATOR?
In reading the answers for this question about the caution against using FIRSTROW as a means of skipping column header rows, it struck me that it seems really inefficient and non-intuitive that a ...
1
vote
1answer
35 views
GROUP BY multiple criteria with difference in row fields within a group
Everyone,
I am struggling to find a solution for the following task:
All users (1) that have a game (2) with their items count (3) and items price (4) are to be retrieved by a query
=> Display ...
0
votes
2answers
48 views
T-SQL: how to show the corresponding column's row to an aggregating function's result?
Everyone,
I have a question - how to show the (possibly ambiguous) column that corresponds to the value retrieved by an aggregating function?
Related to the dataset below, the task is to find the ...
0
votes
2answers
72 views
DBCC Shrinkfile not working on SQL 2016
Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank.
Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file ...
1
vote
1answer
31 views
Will this trigger always complete before returning results of Insert to client?
I have the following trigger for Auditing.
CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EntityState AS SMALLINT;
...
0
votes
1answer
34 views
Prematurely exiting a while loop in T-SQL and viewing the output
I let a while loop run over the weekend with a CURRENT_TIMESTAMP condition on it, problem is I accidentally set the condition to 2020-08-06 instead of 2020-06-08 (oops). Is there any way to manually ...
9
votes
1answer
367 views
Simplifying Function with Recursive CTE and/or Window Function
I'm trying to come up with a Recursive CTE and/or Window Function to create a function.
After days, I've boiled the function down to (pseudocode)
where I have N and B, and need to generate E:
En =...
0
votes
3answers
63 views
Can I simplify this MERGE statement w.r.t. WHEN MATCHED conditions?
I'm using SQL Server (SQL Server 2016 and Azure SQL) and I have this MERGE statement which uses a rather gnarly WHEN MATCHED condition to only update rows where values are actually different.
This is ...
1
vote
1answer
34 views
CASE in WHERE clause - retuns a single-row result instead of 4 rows?
Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
0
votes
2answers
36 views
Running queries/jobs via SMSS - SQL service and SQL agent service accounts (SQL Server)
If a command, for example the below command, is executed from SQL Server Management Studio while I am logged in the session with a windows authentication login, I understood that the access to the ...
0
votes
0answers
6 views
T-SQL, find projects' salaries (budget) from a created View
everyone.
I have played with a test database and wanted to calculate the budget (salaries) allocated to each ProjectID.
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[...
0
votes
0answers
14 views
Filestream or FileTable…what should be used to solve the problem
I've been assigned with a project that includes converting the columns in the existing tables to use FileTable or Filestream. Basically, the requirement is to move blob fields to filetable storage if ...
1
vote
1answer
69 views
Break down calendar table based on custom dates in SQL Server 2017
I have a table that contains the following fiscal year definitions:
FiscalYear | Period | PeriodStartDate | PeriodEndDate
==================================================================...
1
vote
2answers
31 views
SQL Server Express Edition feature and upgrade concerns
I want to install and learn T-SQL with SQL Server. The lightest edition that I can find is the Express Edition 2017.
Will the express edition support procedures, triggers, dynamic sql, PIVOT, ...
0
votes
1answer
26 views
MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?
I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data ...
0
votes
1answer
25 views
quoted identifiers on filtered indices
I´ve 2 questions. I am using a T-SQL Server 2014.
When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so?
I´ve some SPs which ...
1
vote
1answer
42 views
T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.
How should the query look like to achieve this, since the AVG function is an ...
0
votes
0answers
34 views
TSQL Materiaized/Indexed View Cluster index with subquery
I have two tables: Listings and Media and Media is a child of Listings. I am trying to create a indexed view that will include the first record from Media. I would also like to index my view so that ...
0
votes
1answer
34 views
T-SQL Merge not updating in explicit transaction
We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the ...
0
votes
1answer
21 views
Azure SQL: Should Manual Indexes be Applied with automatic tuning?
Should Manual Indexes on tables be applied in AzureSQL Cloud Database? We read that AzureSQL does automatic tuning, so are manual indexes even required? We read, there can be cases where AzureSQL ...
0
votes
1answer
46 views
Is it good practice to only delete through a trigger to enforce 'deleted by' auditing?
We've decided to design our database such that we actually delete rows when we want to delete some data, rather than just marking them as deleted, because the latter approach means that you always ...
