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
-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 ...

1
2 3 4 5
58