2022 Developer Survey is open! Take survey.

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
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 ...
user avatar
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 : ...
user avatar
  • 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 ?
user avatar
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] ...
user avatar
  • 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: ...
user avatar
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"...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 / ...
user avatar
-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 ...
user avatar
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 <> ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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', ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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; //...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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: ...
user avatar
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 ...
user avatar

1
2 3 4 5
59