Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

203 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
5answers
2k views

SELECT query slow even with nonclustered Index and a full-text index

I am attempting to query between 0 and 65,000 rows from a table. The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server. Schema [Id] (PK) INT ...
4
votes
1answer
150 views

Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow

I've updated this post with some test data. I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
3
votes
2answers
1k views

Way to obtain full list of column dependencies for stored procedure?

I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
3
votes
2answers
4k views

Full text search using words stored in another table

Is there a way to do a full-text search using words stored in another table? This is my scenario: Table_A contains phrases entered by users in field "UserInput" (and other stuff). I have a full-...
3
votes
0answers
80 views

Workout change operation with temporal table records

I have just started using using temporal tables/system versioning, its very easy to setup and get going in terms of querying the data. The one thing missing for me is storing the change operation (...
3
votes
1answer
141 views

How to reverse only English words?

I've an nvarchar column with English and Hebrew letters in some fields. In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
2
votes
0answers
73 views

T-SQL : Slicing time periods by related time periods

Consider the following table that gives the validity dates for which an Item is associated to something called a Demand_Unit, and a certain Market : Item_ID Start_Date End_Date Demand_Unit Market X ...
2
votes
1answer
57 views

Advantage of querying data outside a filtered index

Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index? My playground: Table with billion rows and 100 columns Unique clustered ...
2
votes
1answer
195 views

Assign a student to a Classroom based on course and scheduled hour

Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc, we are talking 100s of students where we have ...
2
votes
0answers
71 views

Is there a setting in SQL Server to keep triggers from firing SELECT @@IDENTITY

This is a SQL Server 2014 Server and we have application code the does a Select @@Scope_Identity after an insert into a table. Recently other developers added triggers to this table and ever since, ...
2
votes
0answers
75 views

TVF Poor Performance with Linked Query

I have an inline TVF that take much longer to run than should be necessary. The issue looks to be the Linked Server reference. The Linked Server is SQL Server 2012. The server with the TVF is SQL ...
2
votes
0answers
426 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
0answers
228 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
2
votes
0answers
70 views

restore script variable date and time

Goodafternoon, i am looking for a variable for the date and time. I have several backups in one directory and want to restore the latest to a another server. Is there a variable i can use in my ...
2
votes
0answers
719 views

Using :Connect I keep getting msg 911 - database [dbname] does not exist

I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but ...
2
votes
0answers
467 views

What is the bare minimum access needed in the public role?

I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures. However, by default the user gets everything in the "public" role....
2
votes
0answers
564 views

Avoiding Functions() in the Where clause

Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query: (@p__linq__0 nvarchar(4000)) SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1] ...
2
votes
0answers
7k views

DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'

I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
2
votes
0answers
552 views

SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))

Are there any theoretical performance differences between these queries in the scenarios below. Scenarios are: MyTable has Value nvarchar(300) MyTable has Value nvarchar(max) Q1: declare @p ...
1
vote
0answers
65 views

Questions about execution plan

I'm reading "SQL Server Execution Plans, Third Edition, by Grant Fritchey". It includes the following query and the execution plan (queried against the Adventure Works database). SELECT soh....
1
vote
1answer
41 views

Is there any way to monitor if all columns in an article is published in replication

In replication, if we add a column in Publisher then it doesnt replicate to Subscriber unless it is done explicitly. I am looking for a way to track or monitor this. I am not able to find any system ...
1
vote
2answers
52 views

SQL SERVER Detailed Backup Report

I need to create a script that gives the report for the following information: Backup Job Name Databases that have been backed up in the last 24 hours Size of each DB backup Start time of backup End ...
1
vote
1answer
250 views

copy data from Previous non null rows and if first row is null copy next non null value

I need to copy data from rows with non-null rows and if first row is null copy the next non-null value. it is not a big table. There is no ID column and date column. The datatype of this column is ...
1
vote
2answers
109 views

BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1

I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,). Bulk insert called by sqlcmd did not fail but did not insert either. ...
1
vote
0answers
264 views

Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

DBAs, Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over? When I hit debug (Alt-F5), gives me this error, after making some ...
1
vote
1answer
103 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 ...
1
vote
1answer
134 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
vote
1answer
43 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 ...
1
vote
0answers
42 views

Removing files from a filegroup

I am writing some T-SQL code to retrieve the file names that make up a file group so that I can remove the files and the related filegroup. The way that I coded it is below: set @cnt = (select ...
1
vote
1answer
52 views

Do I always need boilerplate code to prevent nested transactions?

Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one? It seems like a ...
1
vote
0answers
201 views

How to select all files of a SQL Server database from blob folder?

I have a SQL Server database. You can use the following script to create similar database: CREATE DATABASE [test] CONTAINMENT = NONE ON PRIMARY (NAME = N'test', FILENAME = N'C:\Program Files\...
1
vote
0answers
110 views

Capture trigger update() with inserted & Deleted changes only

I have price table that has over 100 columns. I'm in the process of fine tunning triggers and reducing amounts of records inserted, to audit changes when updates are applied. The audit table ...
1
vote
0answers
23 views

Read all Captured Extended Events for a Given Extended Event Session Name

I want to be able to read all XE files for a dynamic XE session name. My XE session stores files to disk. I have the query below DECLARE @XeName SYSNAME = 'MyXE' DECLARE @Path NVARCHAR(255) SELECT @...
1
vote
1answer
119 views

T SQL Cte Previous record calcuation

I'm using SQL Server 2016. I have the below table: SKU Shop Week ShopPrioirty Replen Open_Stk Open_Stk Calc 111 100 1 1 0 17 ...
1
vote
0answers
49 views

Determine if query comes after cascade update

We have a situation that in a trigger we are calling one procedure to update/insert values in another table. Now, the new requirement asked that we have to cascade update foreign key on that table (...
1
vote
0answers
243 views

Worked example of closure tables with from and thru dates

I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now. ...
1
vote
0answers
40 views

Repivoting rows of pivoted data with an dynamic amount of rows

I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
1
vote
0answers
184 views

sys.login_token doesn't include particular windows groups while they can be listed by sys.database_principals

When I query SELECT principal_id, name, [type] FROM sys.login_token I found some local windows groups were not there. However, when I check by SELECT principal_id, name, [type] FROM sys....
1
vote
0answers
2k views

Openrowset is throwing error for file access as Access Denied

I am getting Access Denied error .. Openrowset is throwing error for file access as Access Denied I tried running these 2 queries as well.. EXEC sp_configure 'show advanced options', 1 RECONFIGURE ...
1
vote
1answer
85 views

How to index of filter extended event files in Sql Server

I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot ...
1
vote
0answers
33 views

File Extensions are missing from new SSMS 2012

One of my colleagues SSMS recently got updated to 2012 version from SSMS 2008 R2. After that they are view only a few editors in SSMS for adding different File Extensions Tools ==> Options ==>Text ...
1
vote
0answers
75 views

Creating 'temporary' memory optimised tables with only read/write roles

I'm currently trying to get an application to use memory optimised tables instead of temporary tables, but have hit an issue. The issue is related to permissions, as users of the application are only ...
1
vote
0answers
660 views

Viewing directory of a mapped drive

I'm using the following query to view files/folders in a given directory. It works locally, but when trying to view a mapped drive I get... "The system cannot find the path specified." I have the ...
1
vote
0answers
118 views

How to list PDF Files names and path in a SQL Server Database

I need help on script to list PDF Files names and path in a SQL Server Database. DECLARE @full_path VARCHAR(500) SET @full_path = '\some\path\here' DECLARE @FindFile TABLE (FileNames nvarchar(...
1
vote
0answers
359 views

Encrypt through Java and Decrypt through SQL Server AES 256

Am trying to insert some value through JAVA Application with AES Algorithm and need to decrypt the data from the SQL Procedure and need to process a data. Which is the best practice to do so?
1
vote
1answer
69 views

Averaging 2 values based on an ID in SSRS

I am hoping I might be able to get some help with a problem I am having. I am currently trying to build a report in SSRS to replace an excel spreadsheet we use for data analysis so the report can be ...
1
vote
0answers
71 views

Poor performing Query -Tsql execution plan - estimated number of rows =1 Paste the Plan

The query and the execution plan are available at the link below. This is from vendor application that I support. I know that there has to be a better way to write this code but the vendor says that ...
1
vote
0answers
640 views

Inserting into an table with identity column from stored procedure works even though it shouldn't

We have this piece of (anonymyzed code) create table #SomeTmpTable ( [RowNumber] integer identity, [Value] integer, primary key (RowNumber) ) insert into #SomeTmpTable (...
1
vote
0answers
459 views

Encrypting and Decrypting by Certificate Not Working

I am trying to encrypt some data in SQL server using an existing key and certificate. I know this key and certificate work as I'm able to pull information from another table in the database but for ...
1
vote
0answers
976 views

The OR clause in a join condition takes more time to execute in SQL Server

The below query in SQL Server takes more time [approx 600ms] to execute. Based on the query execution plan. I figured out that the code indicated is the culprit. How can I try to optimize this query? ...

1
2 3 4 5