Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,859
questions
-1
votes
0answers
32 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 ...
-3
votes
0answers
42 views
group by and output based on count
Based on the following business determination that ONE 'exit' comprises (MIN time of entry and MAX time of exit), (meaning the cardholder has exited the facility). In reality there can be many '...
0
votes
1answer
37 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
1answer
979 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 ...
-3
votes
0answers
33 views
MSSQL How to save and manage the results of multiple SELECT QUERY? [closed]
I need to delete multiple users, they gave me a list in a spreadsheet to delete 153 users, I have to do a procedure or something like that, since it will be executed in a DB where the IDs (uuid) are ...
5
votes
1answer
165 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
1answer
26 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 ...
0
votes
1answer
40 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 ...
0
votes
1answer
21 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 |
+-----------+------------+------+...
4
votes
1answer
114 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 ...
1
vote
1answer
66 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 ...
1
vote
1answer
43 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 ...
0
votes
3answers
72 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 ...
-1
votes
1answer
58 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 ...
0
votes
0answers
49 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 ...
2
votes
0answers
75 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
...
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 ...
7
votes
3answers
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 ...
5
votes
2answers
100 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? ...
-1
votes
1answer
52 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 ...
1
vote
1answer
85 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 ...
...
1
vote
1answer
44 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 &...
2
votes
1answer
94 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 ...
0
votes
1answer
58 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 ...
1
vote
0answers
66 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
0answers
59 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 ...
0
votes
0answers
31 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 ...
0
votes
1answer
62 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....
0
votes
1answer
62 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 ...
-1
votes
1answer
45 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
3answers
147 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 ...
0
votes
1answer
34 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 ...
0
votes
1answer
38 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, ...
0
votes
1answer
25 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 ...
0
votes
0answers
25 views
How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?
Hope you are well
I'm been a bit stuck trying to develop the code to get the inheritance of extended properties.
I will explain the situation:
The thing is have a query to list all extended properties ...
3
votes
3answers
172 views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query that 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 ...
5
votes
2answers
414 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 ...
0
votes
1answer
80 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 * ...
0
votes
1answer
26 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 ...
0
votes
2answers
61 views
Difference between statement for declaring variable MSSQL
DECLARE @Variable AnyType
SELECT @Variable = something
OR
DECLARE @Variable AnyType = something
3
votes
1answer
38 views
How to group data but taking rows only in a specific order (example should be clearer ;) )
I have something i need to do and i don't know how to do it :)
I have the following data :
Art
Type
2215
Z
2215
C
2215
M
2216
C
2216
The result i want to have
Art
Type
2215
M
2216
C
Those ...
0
votes
1answer
32 views
Do we have information about duration time of query in actual execution plan?
I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
12
votes
1answer
967 views
Seemingly identical WHERE clauses returning different results
I have a table named [User]. Below is a simple query to find one user from the table:
SELECT [Username]
,[FirstName]
,[LastName]
,[EmailAddress]
,[IsStaff]
,[ExternalID]
...
2
votes
2answers
45 views
T-SQL Group and sum record set based on value in a given column
Take the following example:
SELECT * FROM [Orders] WHERE DATE = '2021-09-10'
Date FirstName LastName Adults Item Description
---------- --------- --------- ------ --------- -----...
0
votes
1answer
55 views
Select rows where result appears more than once
I have been looking all over StackExchange, testing various methods listed here, without any success.
I need to SELECT only users that have multiple sessions.
This is my beginning SELECT statement:
...
-4
votes
1answer
47 views
Updating column value across all SQL server versions [closed]
I have a simple table with 3 columns
Installed by - some login name
Installed date -
Date when bunch of scripts were run with any changes as part of that package
Version - version number when release ...
0
votes
1answer
36 views
What does T-SQL msdb.dbo.RestoreHistory Restore_Type NULL mean?
In msdb.dbo.RestoreHistory, the Restore_Type can be NULL.
See https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql?view=sql-server-ver15
restore_type / ...
2
votes
2answers
88 views
Keep full history of every values in each table
How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify ...
0
votes
0answers
42 views
Creating sql server custom database role that only allows reading some columns and nothing else
I want to create an sql server Custom Database Role which only allows reading some columns of some tables, and nothing else. this is my attempt.
CREATE ROLE CustomDatabaseRole01
GO
GRANT ...
5
votes
3answers
352 views
Hovering over an Operator in Execution Plans: How to make the tooltip not disappear when moving the mouse
I want to share my screen and show an execution plan but when I hover my mouse over an operator and what to point to something with my mouse in the tooltip then it disappears. Is there a way to make a ...
