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

1
2 3 4 5
53