Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
3,047
questions
2
votes
2
answers
660
views
SQL Server 2016 UPDATE statement - are variable assignments always processed before column updates?
One of our application devs came to me with an interesting question. In the following code, you'll see that the update statement is assigning the current column values to two variables, but also ...
- 421
1
vote
1
answer
217
views
TSQL - How to get multiple values with different dates in one select?
What is the most efficient way to get values from 2 previous dates in one statement? (Freespace-2 and free space -3 would be 2 and 3 days prior)
SELECT
servername,
DriveName,
DriveLabel,
...
- 427
3
votes
1
answer
149
views
Distinct Sort Operator with no Order, Distinct, Grouping
I have two queries that are mostly the same bar a where expression. They are UNION ALL'd together for a complete result set. There is no grouping, nor ordering, nor distinct in the queries. However, ...
- 115
3
votes
1
answer
206
views
Why do conditionals apply to SET NOEXEC ON but not SET NOEXEC OFF?
Of the four select statements in the following script, I would expect the second and third statements to execute and the first and fourth not to execute. The first does not, but the fourth does. It ...
- 131
0
votes
2
answers
95
views
sys.indexes - type column
According to official documentation of sys.indexes, type = 2 -->Nonclustered rowstore (B-tree). But the line 4 of the following example taken from online here is showing type = 2 -->Nonclustered ...
- 289
0
votes
2
answers
59
views
exporting data from SQL query
I have a SQL CmdExec Job set up to run a query however the output data contains headers, job information and total numbers of returned rows as part of the output file and I am trying to figure out how ...
- 3
4
votes
2
answers
666
views
Are table-valued functions deterministic with regard to insertion order?
Is insertion order deterministic in a TABLE-valued function which instantiates the table and populates it with successive inserts, assuming no ORDER BY clause is used?
I needed to take a look at the ...
- 435
3
votes
1
answer
186
views
What is the difference between these 2 index setups?
What would be the difference between these 2 setups for an index on Field1 and Field2 on TableA?
Option 1:
create nonclustered index IX_Index1 on TableA (Field1)
create nonclustered index IX_Index2 on ...
- 153
1
vote
1
answer
89
views
Is there any benefit to creating a nonclustered index on 2 fields that are already present in the clustered index?
I have a table with 3 fields in the clustered index (they are the PKs of the table). One of those fields is no longer useful, so I'm wondering if querying is being slowed down by indexing on a field ...
- 153
8
votes
2
answers
4k
views
Getting an "Incorrect syntax near '(' " error message when using the standard create table using a select in SQL Server
Why am I getting the error in the Title of this post when I run the following query using SQL Server via Microsoft SQL Server Management Studio?
-- create the dash_airports table
CREATE TABLE ...
- 227
1
vote
0
answers
152
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
2
votes
2
answers
51
views
Backup syntax error with constructed string
This is doing my head in. What am I missing? Syntax errors with '+' and ;
-- Step 1: Perform a database backup
BACKUP DATABASE iMIS_Prod TO DISK =
'G:\SP_Backup_Daily\iMIS_Prod_' +
...
- 21
0
votes
2
answers
2k
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-...
- 435
9
votes
6
answers
3k
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
183
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,
...
- 109
1
vote
1
answer
47
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
281
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 ...
- 421
0
votes
1
answer
906
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 ...
- 155
3
votes
1
answer
72
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 ...
- 51
0
votes
1
answer
144
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
188
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 ...
- 155
0
votes
1
answer
321
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 ...
- 51
1
vote
1
answer
247
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 ...
- 358
1
vote
2
answers
218
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.
...
- 303
2
votes
1
answer
473
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 ...
- 123
1
vote
1
answer
56
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 ...
Ehsan Joodi
-1
votes
1
answer
67
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
920
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 ...
- 437
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
304
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 ...
- 295
2
votes
3
answers
274
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 ...
- 35
0
votes
2
answers
807
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
...
- 1
0
votes
1
answer
41
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
2
answers
341
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
...
- 111
3
votes
0
answers
480
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
433
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 ...
- 171
0
votes
2
answers
405
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
3k
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 ...
- 41
3
votes
1
answer
364
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 ...
- 31
1
vote
1
answer
349
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 ...
- 216
5
votes
2
answers
1k
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
2k
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, ...
- 746
2
votes
1
answer
527
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'
...
- 23
3
votes
1
answer
1k
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 ...
- 135
0
votes
1
answer
379
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/...
- 41
0
votes
1
answer
346
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
370
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.
- 795
2
votes
2
answers
217
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
1k
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,183
0
votes
0
answers
73
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
...
