Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
3,046
questions
1
vote
1
answer
963
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 ...
- 19
0
votes
1
answer
2k
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
653
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
218
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
277
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
946
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
652
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
1
vote
1
answer
194
views
Where can I check a T-SQL function is available in a specific version?
Where can I find a list to make sure the functions I am using are available in older versions of Microsoft SQL Server?
We recently ran into issues with someone using CONCAT which won't work on some of ...
- 113
1
vote
2
answers
364
views
What is the disadvantage of not creating surrogate key in DW?
I want to create a data warehouse from 1 OLTP database. The tables in the OLTP db have got integer primary keys. So they are the business keys.
The tables are: Client, customer, products and sales. ...
- 3,430
0
votes
1
answer
203
views
Help with Replacing characters with text string chops of last character of string
I need assistance with fixing a replace statement that truncates the last character of the string. The original string (Question_Text) contains HTML characters and other erroneous characters that ...
3
votes
2
answers
847
views
Local Variables in Stored Procedures
I've read online that if you use local variables in stored procedures instead of the input variables then the stored procedure is optimized as if it was using the hint OPTIMIZE FOR UNKNOWN. How ...
- 53
0
votes
1
answer
2k
views
Generate random password using a T-SQL function
I have this code in PLSQL but I am unable to convert it to SQL Server.
I want to generate a random password that contains a mixture of upper and lowercase, numbers, and special characters in T-SQL ...
- 23
0
votes
2
answers
163
views
Query for when comparing to multiple child records
I cant seem to figure out what the tsql should be to do the following.
I would like to query Tweets table with all tweets that have category 1 and 2 (could be more than 2 categories passed)
So the ...
- 359
-1
votes
0
answers
34
views
Timestamp to indicate the latest event writing in a table [duplicate]
I want to know if there is any way to find out when the latest event was writing to a table in SQL Server.
Assuming the table does not have a created_at/updated_at columns and CDC and Change Capture ...
- 1
0
votes
1
answer
48
views
TSQL to Extract Time Value and Reformat while excluding non numbers eg 'Apr 2 2018 12:36PM' to '1236'
I have an nvarchar field which contains Date and Time data in the format 'Apr 2 2018 12:36PM'
I need to extract the value as '1236'.
While I have used combinations of substring and right I still need ...
9
votes
1
answer
1k
views
Strange conditional Syntax in TSQL Query: "<=+" What does it do?
I am working on diagnosing a problem with a legacy query in TSQL. One of the returned columns in the query is:
CASE WHEN PaidDate >= '2021-01-01' AND PaidDate <=+'2021-10-31' THEN WO ELSE 0 END ...
- 93
5
votes
1
answer
433
views
Large memory grant for joined tables
When I select columns from both joined tables a get a huge memory grant (529808). If I query each table columns separately it has a combined memory grant of just 8008.
Why does the combined list of ...
0
votes
1
answer
726
views
How can I group / display same date data in one row?
I have a table that gets inserted into every night that is a snapshot of the data. At any point in time, the data might change in the columns (AccountNo stays the same, RunKey is incremented by 1, and ...
- 15
0
votes
1
answer
60
views
Do Temporary Tables cause stored procedure Recompilations?
I have read certain articles online that creating temporary tables in stored procedures will cause stored procedure recompilations.
Could someone please explain what happens when a temporary table is ...
- 53
0
votes
1
answer
513
views
Populate new rank field with custom order
I'm trying to populate a new Rank field in my table. Here is an example of how it should look like.
+-----------+------------+------+
| CountryId | CreateDate | Rank |
+-----------+------------+------+...
- 267
4
votes
1
answer
212
views
Indexed view maintenance with foreign key not elided when inserting parent row
TL;DR;
Given an indexed view that has a JOIN between two tables that already have a foreign key relationship along with a WHERE predicate on the parent table.
When inserting into the parent table of ...
- 11.8k
1
vote
1
answer
84
views
Handle datetime checks during SQL Server day light savings
We have a logging table that has entry added every minute (log_date field in the table is
logged in UTC, the server local time is in PST) and we have a
condition that monitors and alerts if there is ...
- 532
1
vote
1
answer
967
views
Order of Execution of correlated Sub-queries in select and where clauses and Cross Apply
I'm new to SQL Server. I recently learned that the order of execution is:
From
Where
Group By
Having
Select
Order By
Where do correlated subqueries and cross apply fit in the order of execution?
I ...
- 149
0
votes
3
answers
612
views
How to select sys.views of another database from master databse?
I know I can get views metadata using sys.views system catalog view. But now I want to create a centralized function in my master database to be called from other databases.
For example, I want to ...
- 205
-1
votes
1
answer
69
views
Question about Key lookups
I have a table with 100 columns. The Primary key and clustered index are on the column Id - IX01_Id. I have a non-clustered index on Col2 - IX02_Col2.
I have 3 stored procedures that return the ...
- 53
0
votes
0
answers
147
views
Calculate Start and End of Fiscal Period Weeks
I have a calendar table established (thank you, Aaron Bertrand):
SET NOCOUNT ON
IF OBJECT_ID (N'dbo.Dim_Calendar', N'U') IS NOT NULL
DROP TABLE dbo.Dim_Calendar
SET DATEFIRST 7, LANGUAGE ...
- 55
2
votes
1
answer
460
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
...
- 23
2
votes
1
answer
332
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 ...
- 71
8
votes
3
answers
2k
views
Improper use of logical operators has led to poor query performance
I have a table with huge amount of data (Nearly 15 million) and below structure.
create table test
(a int,--> /* There is a normal index on this column */
b int,
<other columns>)
There is a ...
- 1,412
5
votes
2
answers
326
views
Index with IGNORE_DUP_KEY = ON filters out non-duplicate records
Below is a script which can be used to re-produce the issue I am facing.
Basically the question is this: why is it that the record with (ID1 = 6, ID2 = 7) is not populated into the final #tt2 table? ...
- 645
-1
votes
1
answer
60
views
Suggestions to create a table or to design a stored procedure to remove while loops [closed]
The company I am currently working for, has a scenario for which we have to use while loops or cursors in SQL. Which has a huge performance impact in our case, hence, I want to optimize it.
The ...
- 3
1
vote
1
answer
693
views
CREATE DATABASE permission denied in database 'master'. Cannot attach
In a T-SQL script, using a connection string like this:
Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Temp\ContextTest.mdf;Initial Catalog=ContextTest;Integrated Security=True
... I ...
...
- 133
1
vote
1
answer
505
views
How do I prevent a column being replicated in SQL server?
I'm on MSSQL 2016 using push-based transactional replication to an Azure instance. I have a few columns in our modeled data that I really do not want to replicate.
Is there a way to flag a column as &...
- 277
2
votes
1
answer
409
views
How to check which pages are part of an extent?
I have some data corruption and was not able to restore the data via backups. It was affecting mainly non-clustered indexes, so after drop-create of the indexes the issues were resolved.
Finally, I ...
- 3,770
0
votes
1
answer
370
views
How to query database integrity check duration time (how much time did it take to complete dbcc checkdb) [duplicate]
How to know how much time did it take to run database integrity check for all databases on a instance.
I simply need when it started and when its finished.
I am able to query when the last check db ...
- 657
1
vote
0
answers
74
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....
0
votes
0
answers
37
views
Question on ANTLR grammar rule in TSqlParser.g4 at https://github.com/antlr/grammars-v4/blob/master/sql/tsql
In the TSqlParser.g4 file
1.On line 3247, a rule predicate is defined as
predicate
expression comparison_operator expression
In turn comparision_operator is defined as
...
- 373
0
votes
0
answers
113
views
Unable to get FileStream Length with After Insert trigger
I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I'm populating ...
- 1
0
votes
0
answers
58
views
tsql do update queries that join other tables lock the joined tables also?
Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp ...
- 291
0
votes
1
answer
154
views
T-SQL : Grouping based on chronological switch periods
I have the below table extracted from a Type 2 Slowly Changing dimension. I want to group rows to only have distinct classifications and their respective 'switch' time periods in a chronological order....
- 23
0
votes
1
answer
67
views
SQL case problem [duplicate]
SELECT
CASE
WHEN OfferSum.DayOfWeek = 'Monday' THEN 'Mon'
WHEN OfferSum.DayOfWeek = 'Tuesday' THEN 'Tues'
WHEN OfferSum.DayOfWeek = 'Wednesday' THEN 'Wed'
WHEN ...
- 3
-1
votes
1
answer
611
views
Clustered Index and Sorting [duplicate]
select *
from [table_a];
A clustered index sorts the table based on whatever column(s) you choose.
That being said, if I have a clustered index on [column_a], [column_b], and [column_c] and run the ...
0
votes
3
answers
1k
views
Database Sizes error; Arithmetic overflow error converting expression to data type int
I am using the below query to pull back database sizes for our SQL instances.
This cannot pull data larger than 2TB back because of the maximum number the int data type can pull back. How do I go ...
- 193
0
votes
1
answer
1k
views
Update and Varbinary(Max)
I want to set the value of sequence from row 1 to row 2.
If I run:
Update [dbo].[TS_TaskSequence]
set [Sequence] = 'the value of sequence'
Where [TS_ID] = '16777234'
I get:
Implicit conversion ...
- 425
0
votes
1
answer
90
views
Is this horrendously low row estimate causing a slow insert?
reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt
I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, ...
- 115
0
votes
1
answer
326
views
How do you detect a change in grouping when using Partition By in SQL Server 2012+ and execute logic based upon the change in the same SQL statement?
If I have a SQL statement that contains something like,
ROW_NUMBER() OVER(PARTITION BY school_area ORDER BY school_area)
I get:
1 someschool1 schoolarea1
2 someschool2 schoolarea1
3 someschool3 ...
3
votes
3
answers
2k
views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query. I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
- 115
5
votes
2
answers
429
views
Why does switching two column values work by simply reassigning the values in T-SQL?
For example, the following query works just as intended in Microsoft SQL Server (T-SQL).
UPDATE Customer
SET ContactName = Customer.City, City = Customer.ContactName;
I would like to know why the ...
- 61
0
votes
1
answer
165
views
Number of executions of the same sub query by the DB engine
If I have this SQL query
SELECT
(select count(*) from tableA) as cnt,
t1.*
FROM
(
SELECT t.*
FROM
(
SELECT * FROM tableA
UNION ALL
SELECT * ...
- 206
0
votes
1
answer
116
views
SSIS debuging tool in SSMS 2016
I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...
- 15