Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,619
questions
1
vote
1answer
38 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
33 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
13 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
56 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
votes
0answers
22 views
Percentile SQL takes too long - need help optimizing
I have percentile query and I use its result for further calculations to generate response for frontend. However, it take about 23s for this query to finish:
SELECT distinct PERCENTILE_CONT(0.9) ...
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) = ...
0
votes
0answers
56 views
Stored procedure runs slow on SQL Server 2016 compared to SQL Server 2019
The stored procedure is running very slow on SQL Server 2016 as compared to 2019. It's doing some common validation from a temp table and inserting data into primary tables and mapping tables. The ...
3
votes
1answer
263 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
18 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
34 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
70 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
33 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
355 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
59 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
33 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
30 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
25 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
24 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
30 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
20 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 ...
0
votes
2answers
34 views
Permission to select sys tables in stored procedure
I have implemented the stored procedure to check if a specific [schema].[procedure] exists.
CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@...
0
votes
2answers
78 views
T-SQL Query or DB Engine Optimization? [closed]
This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second.
Any ...
1
vote
3answers
69 views
How to calculate end_date from start_date and duration in SQL Server Query
I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.
I am providing the start_date and duration as the input to calculate end-date.
While calculating the end_date the ...
0
votes
1answer
32 views
Query to insert records from one table into another table based on the column of the first table [duplicate]
I have a table with the structure below :
Table(customer_Num int , Channel_Id Varchar(50) , Branch_Code int)
and some example data for this table is :
Table
...
3
votes
1answer
57 views
Is it possible to see blocker's host name or machine name using sp_WhoIsActive?
I am using sp_WhoIsActive.
When blocking is occurred on a server I want to quickly identify who is the culprit.
Column login_name does not give me much info about blocker because there are hundreds ...
0
votes
1answer
54 views
Determine the source of user permissions in a database
I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment.
How can I track down what security ...
2
votes
2answers
86 views
Database cannot be opened. It is in the middle of a restore
I have executed these two commands:
USE master
GO
RESTORE DATABASE test
FROM Kingston
WITH NORECOVERY
GO
USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;
...
0
votes
3answers
51 views
Problem with IF statement [closed]
I am having an issue with this stored procedure. When the user selects "Yes", it should do what it says in the if statement for the "Yes" but it does not do anything, it does not insert anything into ...
1
vote
1answer
42 views
Does “Table Valued Function” force the plan to get into cache?
sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
0
votes
1answer
47 views
Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON
Bear with me - the example code has bit going on and I'll do my best to explain it, below.
with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
...
0
votes
1answer
61 views
Calculating the total row size of a table - SQL Server
I have a table 'Connections' with the following schema in SQL Server:
[ID] [int] IDENTITY(1,1) NOT NULL,
[User] [nvarchar](20) NOT NULL,
[UserID] [int] NULL,
[True] [bit] NOT NULL,
[Comment] [...
0
votes
1answer
26 views
Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query
I am splitting a column based on the colon using this code which works.
,CASE WHEN CHARINDEX (':', ref) > 0
THEN SUBSTRING(ref, 1, CHARINDEX(':', ref) -1)
ELSE NULL
END AS EndVal
The '...
2
votes
2answers
46 views
Select rows based on values from 1 column being the same?
An example of my database table
film id | scene id | value id
-----------------------------
0 | 0 | 7
0 | 1 | 1
0 | 1 | 0
1 | 2 | 8
...
4
votes
1answer
181 views
How to get list of users on all instance's databases? excluding the mirrors!
For an audit of our MSSQL databases, I have been asked to provide a list of all users for each database running on each instance.
There is an existing stack overflow question
which advised to use......
0
votes
2answers
29 views
Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL
Hope you're doing well
I have a table with the structure below :
Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date datetime)
Some Example Data :
...
3
votes
2answers
42 views
Find order that has deliveries in multiple weeks
I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it. Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the ...
0
votes
1answer
70 views
Validation logic for Mobile Number Column in SQL Server
I am creating a validation script and need help with mobile number validation logic with below details. Mobile number must be:
10 digits in length.
Cannot begin with a 1 or a 0 (For example:...
2
votes
1answer
66 views
Performance of query against column level dependencies much faster when using deprecated system view
Consider these two queries that get the names of triggers dependant on the JV_VoyageFlight column in table dbo.JobVoyage.
the first uses the 'old' mechanism that is deprecated (sys.sql_dependencies).
...
1
vote
1answer
36 views
Fetch data if exist in distinct of another column
I am trying to generate the query to fetch the data on the basis of company which have tax paid record in each year of :
Select *
From (
Select
CompanyName
, TaxPaidOn
, ...
0
votes
1answer
15 views
Have x number of rows returned for one record
I have a Dates table with every single date and a column called weekly, biweekly or monthly . I want to show my data as it would appear if it were scheduled
ex
id date weekly biweekly ...
