Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,913
questions
-2
votes
1
answer
64
views
How to convert data separated pipe to rows?
I work on sql server 2017 I have table name #countriesData data as below
my issue here How to convert countries column data separated pipe to rows .
meaning every rev and company and year will have ...
- 105
0
votes
0
answers
55
views
Resource consuming query
I have found below CPU consuming query with a query cost of 1540.
I have tried creating appropriate indexes which reduced the query cost to 1234 but still, the query is expensive.
Index Created :
...
- 1
-2
votes
0
answers
32
views
warning in execute plan [closed]
Who has an idea please for this type of warning as is corrected ?
0
votes
3
answers
73
views
Using Boolean Fields in a View to Identify Linked Tables
I am using SQL Server 2019. I am using SQL Server Management Studio on the client side.
Suppose I have the following data model:
-- A record of the overall test of a widget
CREATE TABLE [TestRecords]
...
- 157
-2
votes
1
answer
45
views
Row_number to not increment if values on three columns are the same from the row before
Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic:
...
- 617
2
votes
3
answers
48
views
Generate JSON with property names that include dot using SELECT FOR JSON
I need to generate the folowing JSON payload (shortened) from a table in SQL Server. Please note the dot in the property name. This is a special syntax called OData.
{
"Id" : "A1"...
- 23
-1
votes
0
answers
41
views
Table Statistics are out of date quickly
We have a table in our dev environment that has 2 million rows. Recently we dropped a few unused indexes from this table. A week after that, we noticed that the stored procedure that uses this table ...
0
votes
0
answers
11
views
Exporting multiple TSQL result sets into one spreadsheet using Powershell
I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table ...
- 75
0
votes
1
answer
27
views
Setting up reporting and alerting in SQL Server through TSQL and Powershell
I am trying to automate monitoring on SQL Server through PowerShell and TSQL, to get alerts through mail whenever some issue is there.
I have gone through many articles over the internet and tried the ...
0
votes
1
answer
47
views
SQL query to group results on matching record ID
I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
1
vote
0
answers
89
views
Any benefit creating temp tables with random prefixes in SQL Server to control latch waits on tempdb database
Recently I came across reading in one of the blogs that if a temp table name is prefixed with different/random names (in different procedures) that could benefit in reducing the concurrency issues / ...
- 478
-1
votes
2
answers
78
views
Must declare the scalar variable "@Name"
I need your help please since I am stuck here and not sure how to fix this.
Trying to run this code below and getting this message (Must declare the scalar variable "@Name").
How can I pass ...
- 57
3
votes
1
answer
549
views
Is there a way to Delete inserted data, or vice versa?
I'm trying to get Data inserted and then Deleted in the same query.
I'm trying to delete it in chunks to not cause LOG issues:
DECLARE @BatchSize INT
SET @BatchSize = 100000
WHILE @BatchSize <> ...
- 7,100
2
votes
1
answer
56
views
ownership chaining not working with sp_cursorfetch
ownership chaining is not working for me in some cases. I use Microsoft SQL Server 14.0.3370.1.
This is the setup for my tests:
create function fnTest() returns int
begin
return 3
end
go
create ...
- 121
0
votes
0
answers
23
views
Move two or more columns as starting columns in TSQL depending on values
AnimalId
Date
Remark
Number
PA
PCountA
PB
PCountB
PC
PCountC
PD
PCountD
AN001
2022-01-25
LR
22025002
coli
100
Strep ag
100
Strep s
100
Staph a
100
AN001
2022-01-25
12:10am
22025002
coli
300
Staph a
...
0
votes
1
answer
44
views
How can I set-up SQL Server alert for when table got corrupted?
Recently one of the database tables got corrupted in production's SQL server. I am trying to create an alert for this kind of issue. I don't see exact Error number or severity to choose from while ...
0
votes
0
answers
31
views
The configuration Option for a column is not unique error - sql server
I have a table with 5 columns. regionId is the primary key. One of the columns, regionKey, is a sql_variant type.
Create Table #Region
(
regionId int identity(1,1),
regionName varchar(100),
regionKey ...
0
votes
2
answers
51
views
How to manipulate the output of a GROUP BY CUBE query changing the NULL values generated for the groups?
I wrote a query to sum salaries grouped by Country and Gender. I don't want the rows to show NULL or empty values for the totals, instead I want it to show Total and Grand Total to better describe the ...
- 1
0
votes
1
answer
25
views
Inhibiting results to pane in SSMS (TSQL language) via script
I found that one could use a menu to "discard" results in SSMS (TSQL language). Please see: Original Question about inhibiting results and its answer.
However, I was wondering if there is a ...
- 125
1
vote
1
answer
47
views
Log Replay on top of SQL database files captured during VSS snapshot failed
I am having a SQL Server and want to perform hourly VSS based backups (will store the VHD separately in a disk) and perform log backups in every 10 min interval. And with the help of backed-up VHD and ...
2
votes
0
answers
75
views
Adding a new column to a table that has more than 1 billion rows
I have a table that has more than 1 billion rows. I have a requirement to add a new column to this table.
Alter Table mylargetable
Add newColumn varchar(50) NULL
I'm concerned that this might take a ...
-3
votes
1
answer
41
views
How to write select statment that get data based on partid from last date to current date with filling gapes?
I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) .
based on last date exist per partid
and on same time if there are any ...
- 155
0
votes
1
answer
27
views
Convert 7 columns in two rows to 14 columns in one row
I need a T-SQL script to change two rows to one row. The rows are grouped by Student ID and Unique Course Identifier. The second row columns in the groups for the Days, Start Time, End Time, Start ...
- 5
3
votes
3
answers
109
views
How to rename logical file names in recovery pending database that cannot be recovered?
Here's the situation:
Someone renamed database A to A_old in SSMS
They did not rename the physical nor logical file; physical file was renamed somehow to A_old.mdf
They then created a new database ...
- 141
2
votes
2
answers
100
views
Create unique index based on two columns (bi-directional)
I want to make a check constraint or a unique index to validate that I'm not creating duplicates within my table based on 2 columns, I do not want the same two IDs in these columns.
Thinking about ...
- 123
1
vote
1
answer
80
views
Temporarily disable SELECT for one table (during an update)
We have a dedicated data mart SQL Server with several really big tables that are updated one partition at a time via parametrized SSIS loop. Quite often when clients try to read such a table during an ...
- 11
0
votes
1
answer
44
views
Return SQL Agent Job status in SSRS when executing job from SSRS
I'm calling a SQL Agent job from SSRS and using a parameter to determine which job to run.
I can run the job(s) just fine. What I'm looking to do is return a message back to the user based on the job ...
- 309
3
votes
1
answer
243
views
Very slow update statement taking up to 11min to update 20 or 0 rows even with indexes
When I update 20 rows or no rows, it takes 11 minutes.
I mean 20 rows or no rows by different on m.MaximumReflowTemperatureID <> r.z2valueid between two tables.
Why is my update so slow, even ...
- 155
0
votes
1
answer
31
views
View to insert records into history table and then find changes with Except query
I want to insert records to a history table and show the changes from the previous day in a view. The script works, but when I try to create a view I get an error message that
incorrect syntax near ...
- 5
0
votes
1
answer
48
views
SQL Server query consolidating each database file data in to one row
I have this Dataset base of this query below
SELECT DB_NAME() AS dbname , size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', ...
- 617
0
votes
0
answers
59
views
Can't Run CHECKDB Against master db - SQL Server
Getting the below error when attempting to run CHECKDB against master.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, ...
- 41
0
votes
0
answers
35
views
Product Version for Analysis Services and Report Server using T SQL
How can I find the product version for both analysis services and report server using Transact-SQL?
I'm trying to put together a query that will get me the details of each SQL instance with their ...
- 121
3
votes
1
answer
666
views
SQL Server :: How many 8K pages are taken by this record?
I would like to see how many 8K pages are taken by a specific record.
If you run this query on AdventureWorks2019:
SELECT ProductID
,Name
,DATALENGTH(Name) AS SizeInBytes
,LEN(Name) AS ...
- 1,221
0
votes
4
answers
82
views
SQL Agent Job - Change all job steps to go to a specific step when the job fails
I have a SQL Agent Job that I need to change, so that any time any step fails the 'ON FAILURE' action goes to a specific step.
Does anyone have a script or can you show me how to write this so that I ...
0
votes
0
answers
42
views
T-SQL buffered output
I have a T-SQL script that I run as needed to delete old history records from a SQL 2019 database. There are associated records in other tables that need to go as well, so altogether I typically ...
6
votes
2
answers
162
views
Select all rows after first x rows containing NULLs
I have table src. In this table, each Product-Country combination might contain NULL in the Country column for the first x rows.
How can I SELECT rows for each Product-Country combination, only rows ...
- 75
9
votes
3
answers
754
views
What is the point of TRY CATCH block when XACT_ABORT is turned ON?
Code sample with XACT_ABORT_ON:
SET XACT_ABORT_ON;
BEGIN TRY
BEGIN TRANSACTION
//do multiple lines of sql here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK;
//...
- 2,237
0
votes
3
answers
56
views
Find the count of unique FK associated with two or more Col2 values
I’m trying to find a way to get a count of unique foreign keys that are associated with at least two different Col2 values across multiple rows. Example:
PK
Col2
FK
A
page1
abc
B
page2
abc
C
page1
...
- 1
-2
votes
2
answers
68
views
How to use IN with string comma separated
I have value in table1 like this:
Value
-------
JT, KF, LF
On select in other table how can I get a value like this
Select a.Field from table2
Inner Join Table1 on Table1.field = Table2.field
Where ...
1
vote
1
answer
40
views
Should I partition a database file for performance improvement if a file sits on RAID?
I read a lot about performance improvement if instead of a single large .mdf file you partition it by multiple (.ndf) files.
But I also found from documentation that the same improvement can be ...
- 637
1
vote
2
answers
62
views
Locking and blocking lifetime on UNION queries on MS SQL Server
Can anyone shed any light on how locking works on UNION queries? Say I have the below query and the Archive tables take 1 min to run while the non-archive tables take 5s. Are any locks taken on the ...
- 213
0
votes
1
answer
62
views
Importing CSV Flat File - how can we change defaults of all columns to n characters
I have to import a CSV file into a table that has 75 columns. The Flat File connection defaults all the columns to 50 character strings. Some of the fields are much smaller and some are 255. The only ...
- 101
1
vote
1
answer
47
views
Recreating history from snapshoted data
i have a case where i need to compare snapshoted data(dbo.src) with destination table(dbo.dest) and find out which of the countries are new and which ones are not valid anymore.
DATA:
create table dbo....
- 75
0
votes
1
answer
44
views
Query to find data where value of data changed to 0
I am trying to write a query to find the rows where value of particular column changes to 0 from some other value say 1 or above.
The value may change from 0 to other value again. But we need data for ...
- 9
0
votes
0
answers
69
views
How to retrieve min/max values and corresponding dates in a table
I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value.
Example Data:
CREATE ...
0
votes
0
answers
89
views
If I rename a table with dependencies, and then create an synonym of the old name pointing to the new table name will the dependencies still work?
In an attempt to remove some downtime to my users, I want to redirect them to a back up table while I'm repopulating the production table. The tables in question have been in place for years so my ...
- 9
0
votes
0
answers
45
views
Multiple Date rows into One single Date row(date range)
An employee was sick during different period of time, when there is not a single day difference between the multiple dates range, or the date is continuous then it should be shown in one row covering ...
- 1
0
votes
0
answers
48
views
SQL Filtering on multiple properties
I have a Database with a table Books:
Id
IsVisible
Slug
UserId
Name
CreationDate
Description
IsAdult
BirthDate
City
Siren
1
1
julien
56D1C3BE-7674-4CF0-6940-08D9D09EC5A8
Julien
2022-01-05 22:57:39....
- 101
-2
votes
1
answer
133
views
Count rows from multiple tables with similar names
I want to have 2 columns: Table Name and Row Count
When I run this, I get 12 table names in my database. How do I then count the rows in each one without having to union 12 count queries together:
...
7
votes
3
answers
205
views
Preventing stored procedures from writing to databases
I am a database developer mostly writing SSRS reports. I would like to create stored procedures and assure the DBAs that those stored procedures cannot write to the production databases, without the ...
- 83