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
0 votes
0 answers
31 views

Tool to Run Sql from a table - with some bells and whistles [closed]

I’m looking for Sql Server tsql code that will let me run dynamic sql from a table. I need to generate and then run a large number of sql statements, which will extract data, clean it, and then apply ...
  • 109
0 votes
2 answers
46 views

Convert hex string representation to hex number in SQL Server T-SQL

I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-...
  • 371
0 votes
0 answers
47 views

Getting Error Msg 8152, Level 16 only in SQL 2016 but works fine in versions prior to SQL2016

I have SQL server 2016 SP2 version installed with DB compatibility set to 130. Running code to perform an insert into table variable results in: Msg 8152, Level 16, State 31, Line 8 (String or binary ...
9 votes
6 answers
2k views

Is this SQL Procedure "injection proof"?

Most all answers and examples of SQL injection are showing some form of dynamic SQL or interpreting parameters as SQL. I haven't been able to find an example of the "correct" way. Microsoft ...
  • 261
0 votes
1 answer
51 views

Join on a date column?

I'm not sure if I'm thinking about this in the right way, but I want to join on 2 date columns (which are months really), here is my query: with EmployeeGoals as ( select Employees.id, ...
1 vote
1 answer
41 views

Finding where a row changes for one column based on a list of matching words

I work in healthcare and one of the tasks I've been given is to find out whether a column changes under certain criteria. I should only output the claims that do so. In more detail, the data is ...
1 vote
1 answer
53 views

Is there a best practice for resolving dependencies of SQL objects for build during development?

I have a greenfield project involving a SQL database that is under development. There is no production data involved yet. There is a rather long list of specifications which I expect can be ...
  • 321
0 votes
1 answer
138 views

Does a select within a where clause affect performance?

I know that we should avoid using functions in the where clause of a query wherever possible since the function will run for every row to return the proper results. However, does the same principle ...
  • 121
3 votes
1 answer
42 views

Check if date ranges connect or have only specific rows in between

I store the employees' vacations in an SQL table as 2 columns of dates (StartDate : EndDate) along with the vacation type. I can't figure out the best approach to check for some rules, every time an ...
0 votes
1 answer
39 views

What is the transaction isolation level for SP_EXECUTE_REMOTE

The system I am administering is deadlocking very frequently on a specific stored procedure. The application is made up of an Azure SQL Database (ASD) that is connected to a web application. The ASD ...
  • 191
1 vote
0 answers
70 views

What is the object involved in an Object Lock in SQL Server Profiler's Deadlock graph? [closed]

In searching documentation online, I have found that there can be row, page, or table locks. However, I ran a SQL Server Profiler trace and noticed both a Page Lock and an Object lock. I can easily ...
  • 121
0 votes
1 answer
64 views

anyone compared join performance between openjson and user defined table type?

I use fair amount user defined table type in my code and it is relatively slow to join with other table when I put fairly big recordsets in (e.g. 100k). I know openjson can also be used to accept list ...
1 vote
1 answer
138 views

SQL Server procedure, to find and replace data inside big table

I need to write a store procedure to update link reference in my DB. Link can be contained in few nvarchar field that contains JSONs (that could contains some urls). To do so i update the tables in ...
  • 351
0 votes
2 answers
53 views

DataGrip showing prints during a transaction

I have a long running update query, for this reason I used the old well known trick of dividing the query in smaller chunks of 1000 to be print the status of the query and calm the anxiety. ...
  • 293
2 votes
1 answer
54 views

Need to group pair of similar rows together

I have a situation where I need to group pairs of similar rows together such that they are grouped by [date] column and SUM(amount) is 0. Here's a sample code. declare @data table ( id int ...
1 vote
1 answer
53 views

SQL Server Log Backup

In Microsoft SQL Server I should take log backup twice to free up LDF space. Why did this happen? Can anyone help me? In fact, I use full model recovery and have a full backup. I want to say that I am ...
user avatar
-1 votes
1 answer
46 views

Remove Databases from HA Group using TSQL - SQL Server

I am trying to automate following steps using TSQL (It is currently working fine using Manual -GUI interface of SQLServer), working on SQLServer 2022 Create a table, with columns to store DBName, ...
0 votes
1 answer
113 views

Convert an Int column to BigInt in a stored procedure in SQL Server 2012

We have a stored procedure that uses SUM on an Int column in the SELECT statement. We have a few cases where for one of the parameter values, the SUM(integer column) exceeds the Int range and ...
0 votes
1 answer
37 views

Zero time when previous row = Parked

I need to Calculate the true time of a picking event , the below pulls the scan audits of a picking job , the total time is 18minutes but the true time is 2.09minutes . so i need to 0 the time of the ...
  • 1
0 votes
1 answer
49 views

What permission have to set for SQL user for configuration of change tracking in Database?

I am building application which have to have option to configure (disable/enable) change tracking on tables on specific database. I am connecting to database via SQL user. Which database permissions ...
  • 261
2 votes
3 answers
167 views

How to improve performance of this Scalar Function for search with ranking by word

I have a function that searches and ranks to be used on inventory and part numbers. We have tried Full Text Search and Azure Cognitive Search and its been a long journey. I need to match and rank on ...
0 votes
2 answers
174 views

SQL Begin Statement - Error (Incorrect syntax near the keyword 'BEGIN'.)

I’m trying to adapt this code to send and email to everyone from my specific table but I keep getting this error (Incorrect syntax near the keyword 'BEGIN'.) CREATE PROC test.send_email_group BEGIN ...
0 votes
1 answer
38 views

Use table from another SQL 2016 instance

I have two SQL 2016 Enterprize instances, payroll and PP (Production planning); currently, I have an employee(Demographic) table in both instance databases, and now data is loaded in both instances' ...
1 vote
1 answer
67 views

Update one table's row from the results of multiple rows of another table

I've seen posts on how to update multiple rows (specifically using case?), but my question is the inverse. I have a table of phone_numbers Person_ID Phone_number 1 102-345-6789 1 102-445-6789 2 ...
3 votes
0 answers
211 views

What is the cause of persistent SQL Server deadlocks with a chosen victim, where the victim is never stopped, and no transactions are rolled back?

We are experiencing an issue with Microsoft SQL Server where every few days of running our application against the database, it goes into a state where it keeps deadlocking. It logs a deadlock about ...
  • 31
5 votes
3 answers
146 views

Is there a way to sort by an alphanumeric column? [duplicate]

I have a table units unitname unit 1 unit 2b unit 2c unit 2a unit 10 unit 3a unit 3c unit 11 I want to sort the same as unitname unit 1 unit 2a unit 2b unit 2c unit 3a unit 3c unit ...
0 votes
2 answers
97 views

Making a query to sum up all records of a subgroup excluding a specific instance

I'm struggling to make a query that looks at a series of finance records for an individual and gets the amount owing on one record and also (separately) the amount owing on any previous records Here ...
  • 450
3 votes
4 answers
617 views

Get data that was present yesterday but is not present today

I have a table like this: Id Name Value Date 1 John 1 12/01/2022 2 Jane 2 12/01/2022 3 John 3 12/02/2022 4 Max 4 11/30/2022 Assuming that today is 12/02/2022, I want only Jane in the result ...
3 votes
1 answer
155 views

Invalid Object Name after Creating a Table using a while loop

I created this loop to create and populate a temporary table for each int representing a day. The tables are created and properly executed but i get Invalid Object Name everytime i try to print a ...
1 vote
1 answer
64 views

Reference for check correct culture in sql server for Format function

Clients use custom culture to view the website for themselves. This culture setting is saved per user in the database, and this setting is accessed using the format field in the select query as ...
5 votes
2 answers
592 views

sp_executesql Not Working with Parameters

I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I've been tearing my hair out the last few hours over this. I would really appreciate any ...
  • 178
4 votes
1 answer
488 views

Assigning values to multiple @variables using SELECT

I've encountered some T-SQL code which seems to rely on behaviour I found unexpected. When assigning values to multiple @variables in one SELECT statement, when one @variable is dependent on another, ...
0 votes
1 answer
174 views

I want to find the max number of characters of all varchar and nvarchar columns in DB

I want to find the max number of characters of all varchar columns in DB. I have a query like this: SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' ...
3 votes
1 answer
366 views

MIN function returning multiple rows after adding column to SELECT

I'm trying to get the minimum value of Price together with product Description in the table ProductVariation. When creating my query this way: SELECT productId, MIN([price]) AS price FROM ...
  • 133
0 votes
1 answer
76 views

JOIN Temp Table and table on other linked server poor perfromance

I have a query on my FirstServer like this one: DROP TABLE IF EXISTS #TEMP; CREATE TABLE #TEMP ( ItemId NVARCHAR(100), [Date] DATE ); INSERT INTO #TEMP(ItemId,[Date]) ( SELECT 'test', '11/...
0 votes
1 answer
67 views

Sql server Intellisense is not working

My sql server management studio intellisense is not working, below is my SSMS version and screen shot that I have tried, my SQL server is not installed locally but I am accessing the sql server ...
-2 votes
1 answer
145 views

Binary data type in SQL Server and joins [closed]

Will joining on a binary data type be slower than e.g. char? E.g binary(10) vs char(10). Storage is 10 bytes for both.
  • 743
2 votes
2 answers
85 views

Change Tracking SYS_CHANGE_COLUMNS on Update operation

I would like to get an understanding of why on an update operation in the change tracking table, the SYS_CHANGE_COLUMNS can be NULL. I would expect the SYS_CHANGE_COLUMNS will be populated. In the ...
  • 31
8 votes
2 answers
771 views

SQL Modulo Function gives the wrong value?

In Javascript, Excel, Python, and a scientific calculator, the modulo operation -34086.1576962834 % 360.0 <-- python =MOD(-34086.1576962834, 360.0) <-- Excel function gives a value of 113....
  • 2,178
0 votes
0 answers
166 views

TSQL (SQL Server) Table-valued function whose return-form depends on parameters

I have a table who has an arbitrary number of bit columns which can change over time. Key Bit1 Bit2 Bit3 'something1' 1 0 0 'something2' 1 1 0 'something3' 0 0 1 What I am trying to do is set up ...
0 votes
0 answers
67 views

Create logins for all active directories groups named ("Domain Users"), related to current Windows Authenticated user in SQL Server

I need to get all active directories locations, related to current machine, by SQL script to create logins for them. Is there a way to perform that? For now I wrote such procedure: USE [master] GO ...
5 votes
2 answers
450 views

Case-Sensitive Collation on a select query in SQL Server

Our SQL Server's collation is set to SQL_Latin1_General_CP1_CI_AS I have a requirement where we have the same values with different cases but SQL Server treats them the same since our collation is set ...
0 votes
0 answers
186 views

How can i combine two select columns value and values should be unique and optimized?

We have Microsoft SQL Server 2017 (RTM) - Microsoft Corporation Web Edition (64-bit) and Compatibility Level is: 140. I want to combine (Not through concatenation) the values of two columns (ar....
  • 13
0 votes
0 answers
18 views

How can I get the accurate and optimized grouped data by passing expression in partition by statement?

I need to get the correction and a way to group it in a better and efficient way. What I want is that I need to group the data on the bases of RenterUserId or MainDriverUserId in following cases /...
  • 13
0 votes
1 answer
38 views

Get current current object and previous one in one row

Lets say I have a table with columns: Name City Date Value John NewYork 2022-01-02 10 John NewYork 2022-01-03 20 Mark NewYork 2022-01-01 30 Mark NewYork 2022-01-03 40 now main query qould be: ...
1 vote
1 answer
78 views

How can I refine my query in a best way with optimum results (for large database)?

How can I refine my query in a best way with optimum results (for large database tables) ? See the tables structure and the query below CREATE TABLE [dbo].[Franchise]( [Id] [int] IDENTITY(1,1)...
  • 13
0 votes
2 answers
76 views

How to rtrim the slash character (/)?

rtrim('txt/','/') What if we cannot use this version of the rtrim function.. How else to remove the trailing '/' from the end of a string (if present)?
  • 101
4 votes
0 answers
108 views

T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed

BACKGROUND I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10. I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
  • 197
0 votes
1 answer
124 views

How to get definition of partition function programmatically?

I have tables partitioned by date with various frequency space (multi-years, years, quarters, months etc.) sys.partition_functions gives name and function_id. Is there a way to query the actual ...
4 votes
1 answer
401 views

'SUSER_SNAME' cannot be invoked with parameters in this version of SQL Server

I was reading this blog post which contains a query that can allow you to find when a DROP was fired. I changed LIKE 'DROPOBJ%' to NOT LIKE 'DROPOBJ%' so I can return more rows: SELECT [Current LSN] ...

1
2 3 4 5
60