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
vote
1answer
345 views

copy data from Previous non null rows and if first row is null copy next non null value

I need to copy data from rows with non-null rows and if first row is null copy the next non-null value. it is not a big table. There is no ID column and date column. The datatype of this column is ...
1
vote
1answer
37 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 ...
4
votes
2answers
2k views

T-SQL to Group time interval change by date range in sql server

The original table has the column Timestamp with Interval Interval: difference in minute between the current and previous Timestamp when sorted by Timestamp itself Timestamp Interval(...
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
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

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
votes
4answers
8k views

ADD PERIOD FOR SYSTEM_TIME on table failed

I have: table with existing data SQL Server 2016 SP1 SQL Server Management Studio 17.5 I am using the following statement to make a make my table a temporal one: ALTER TABLE [dbo].[...
0
votes
1answer
350 views

Security user audit

This has probably been asked a million times already and for this i apologise, but is there a way or any T-SQL I can run that will tell me, the server, the database, users login name, what permissions ...
0
votes
0answers
37 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 ...
0
votes
0answers
30 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) ...
3
votes
2answers
1k views

Way to obtain full list of column dependencies for stored procedure?

I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
3
votes
2answers
4k views

Full text search using words stored in another table

Is there a way to do a full-text search using words stored in another table? This is my scenario: Table_A contains phrases entered by users in field "UserInput" (and other stuff). I have a full-...
1
vote
2answers
340 views

Split databases into groups of roughly equal datasize

I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size. I want a table that contains every database and these databases are in 7 different ...
0
votes
2answers
216 views

Improving Speed of Cross Apply

I posted a question on how to create a relation between 2 tables based on a spatial field https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering The performance is ...
-2
votes
1answer
48 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
145 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
387 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
0
votes
1answer
101 views

OPENROWSET Bulk insert Text File NULL Columns

I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
1
vote
1answer
36 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 ...
21
votes
2answers
56k views

How to check for Non-Ascii Characters

What is the best way to check if a VARCHAR field has Non-Ascii Characters? CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255). I tried using PATINDEX and have run into the following issue. ...
0
votes
1answer
184 views

T-SQL Merge not updating in explicit transaction

We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the ...
1
vote
2answers
57 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. ...
0
votes
1answer
101 views

SSIS Package Duration is big before it begins executing

hello i have this question. From Visual Studio i have deployd package in Sql server and i am manualy executing Package. BUt i have noticed that package need 6 sec to do all things but Duration is 8 ...
1
vote
1answer
96 views

How to index of filter extended event files in Sql Server

I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot ...
1
vote
1answer
125 views

T SQL Cte Previous record calcuation

I'm using SQL Server 2016. I have the below table: SKU Shop Week ShopPrioirty Replen Open_Stk Open_Stk Calc 111 100 1 1 0 17 ...
0
votes
1answer
39 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 ...
-1
votes
1answer
86 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 ...
3
votes
2answers
287 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 ...
0
votes
2answers
310 views

How to avoid conditional join in order to write a tsql view

I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables Roles table RoleName --------- Admin Viewer User Set table Id Name --- ---- 1 Employee Data 2 Help ...
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 ...
2
votes
1answer
1k views

Is it possible for unique column to contain multiple null values SQL Server 2008 [duplicate]

I want to migrate data contained in an Oracle Table A to SQL Server. The table A contains a column CIN with unique constraint but mutliple null values. The problem is that SQL Server is rejecting the ...
1
vote
1answer
143 views

Execute SSIS (package) in SSMS using T-SQL and return a value

Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I ...
33
votes
8answers
57k views

Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least.. least(...
2
votes
1answer
202 views

Assign a student to a Classroom based on course and scheduled hour

Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc, we are talking 100s of students where we have ...
-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 ...
2
votes
1answer
197 views

Execute SQL conditionally based on database Compatibility Level

The Problem I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, ...
0
votes
1answer
407 views

Copy last modified files from network path into SQL server box and rename them using Agent job

I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job. This is what i am doing. Created a SQL server agent ...
0
votes
1answer
101 views

List all users and permission

I found this script and find it is very helpful to me. I am new here and not good in coding. Can someone explain to me the below script the **'{All Users}'means? represent of? Appreciate your answer ...
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 ...
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 ...
13
votes
12answers
8k views

How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

Given two numbers n and m, I want to generate a series of the form 1, 2, ..., (n-1), n, n, (n-1), ... 2, 1 and repeat it m times. For instance, for n = 3 and m = 4, I want a sequence of the ...
5
votes
1answer
168 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
103 views

Generate script for all stored procedures in Azure SQL database

I'm trying to create a SQL script that will create all the stored procedures that were exported from another database. Basically navigating in SSMS to the database > Generate Scripts > select ...
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 ...
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
189 views

Limit the number of results but show how much would be returned

I have a big problem in T-SQL with large amount of data where I need to limit number of returned rows to 200K but also to show the number of rows that would be returned. It is needed to be done under ...
3
votes
1answer
178 views

CPU vs Elapsed & Parallelism

I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel. After typing that, I was ...
0
votes
1answer
24 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 | +-----------+------------+------+...

1
2 3 4 5
58