All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
2answers
38 views

Locking and blocking lifetime on UNION queries on MS SQL Server

Can anyone shed any light on how locking works on UNION queries? Say I have the below query and the Archive tables take 1 min to run while the non-archive tables take 5s. Are any locks taken on the ...
0
votes
1answer
22 views

Importing CSV Flat File - how can we change defaults of all columns to n characters

I have to import a CSV file into a table that has 75 columns. The Flat File connection defaults all the columns to 50 character strings. Some of the fields are much smaller and some are 255. The only ...
1
vote
1answer
40 views

Recreating history from snapshoted data

i have a case where i need to compare snapshoted data(dbo.src) with destination table(dbo.dest) and find out which of the countries are new and which ones are not valid anymore. DATA: create table dbo....
0
votes
1answer
39 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 ...
0
votes
0answers
48 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
0answers
32 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 ...
0
votes
0answers
42 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....
0
votes
0answers
32 views

SQL Update with sum, inner join, and group condition

I'm trying to update with some condition: UPDATE a SET a.FLEX_ERROR = CASE WHEN ((a.NUM = 1 OR A.CODE = 'D') AND SUM(a.POINT) <> b.TOTAL_VALUE) ...
-2
votes
1answer
50 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: ...
6
votes
3answers
148 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 ...
3
votes
2answers
288 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 ...
-1
votes
1answer
88 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 ...
0
votes
2answers
49 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 ...
-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 ...
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
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 ...
5
votes
1answer
170 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
30 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
44 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 ...
4
votes
1answer
115 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
68 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
80 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
54 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
79 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
147 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 ...
8
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
104 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
130 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
46 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
97 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
70 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
68 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
62 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
63 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
48 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
219 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
40 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
27 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
218 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
85 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
27 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
63 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 ...

1
2 3 4 5
47