Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

0
votes
1answer
38 views

MSSQL - Primary Key Clustered specifying multiple columns

I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this: ALTER TABLE [dbo].[Distribution_Batch] ADD CONSTRAINT [...
2
votes
4answers
90 views

Find first non-matching character between two strings

Posting this with solution provided. The use case could be framed in a number of ways: Return all characters that match at the start of two strings, until they do not match How many characters match ...
2
votes
1answer
81 views

Set in dynamic query value of variable declared outside dynamic query

I have a stored procedure which i simplified this way : DECLARE @variable1 INT DECLARE @SQL VARCHAR(MAX) SET @SQL = ' DECLARE @variable2 INT SET @variable2 = 1 SET '+CAST(@variable1 AS ...
0
votes
1answer
36 views

Add value to a column in a while loop in SQL Server

I have a stored procedure which looks like that : ALTER PROCEDURE psu_auto_segment_folder_create @Folder VARCHAR(MAX), /* for example : 'folder1/folder2/folder3' */ @ListId INT AS BEGIN SET ...
1
vote
1answer
32 views

Remove specific character from string

I have a variable which is a VARCHAR and will contain a list of folders (1 or more). It may looks like that : @Folder = '/folder1/folder2/' or @Folder = 'folder1/folder2/' or @Folder = '/Folder1/...
0
votes
1answer
128 views

SQL PRINT vs SQL EXEC

When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error: Is there a way of ...
3
votes
1answer
107 views

T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar

Recently I attempt to search for a particular pattern by converting XML data into varchar(max) although I'm aware it's not the best practice and found out it's not working as expected:- Setup ...
2
votes
1answer
62 views

Does using a read committed isolation level and a NOLOCK query hint impact performance?

Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP? I just noticed some changes that a dev ...
2
votes
2answers
65 views

Remove string after first and second specific character in a string

I have some text in a field like below; Confirmed Diagnosis ================ Stroke || Entered: 19-02-2019 01:36:39 || Notes: Suspected Diagnosis ================ Transient ischaemic attack || ...
0
votes
1answer
38 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 ...
3
votes
1answer
41 views

How to get tsql TOP PERCENT with OFFSET

select top 10 percent * from sys.databases order by database_id I want to get the same 10 percent result using offset but this query works wrong offset 0 rows fetch next (select cast((count(*) * 10/...
2
votes
2answers
61 views

What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice

Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have ...
2
votes
2answers
54 views

How to pivot a table for a set number of combinations? [duplicate]

Ok so I have a table of components and a colleague needs some data from this table in a different format, now I could do it with some python or java code but wondered if there is a simpler way in MS-...
1
vote
1answer
23 views

Conditional ORDER BY based on NULL values in the column

I have a table like this. table(student_name nvarchar(20) not NULL, grades int). When I write select * from table order by grades asc It shows this student_name | grades --------------------- ...
0
votes
0answers
26 views

Does Visual C# have all the features of TSQL in terms of data management and manipulation? [migrated]

As it is possible to query SQL Server both with Visual C# and TSQL, I want to know are they all equal in terms of operators and features available, or TSQL has more features which cannot be ...
0
votes
1answer
22 views

Filegroup: [primary] dbcc shrinkfile() need balanced extent counts per file when done

I'm working with a ~1 terabyte db, server 40+cores, memory 500G+, db files on SAN (no control over lun segments), the bottleneck now is file contention. Currently running 2014 but by final "release" ...
3
votes
4answers
78 views

Creating temporary table dynamically using condition

I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional ...
2
votes
1answer
336 views

Redundant indexes in SQL Server

I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce ...
0
votes
1answer
45 views

Query To Return Column Name And Date

I have a SQL Table that houses the date that a student was assigned and completes one of three exams. I am in need of a way to query the table and return the column name of the exam the student needs ...
1
vote
1answer
43 views

How to search id from one table in another table's nvarchar column

I have 2 tables table1 ( id int, name nvarchar) table2 ( pid int, parameters nvarchar) parameters in table2 contains table1.id in nvarchar format, like "some text 12345 some text", where 12345 is ...
1
vote
1answer
20 views

Query to find the subtract of each column from the following column

Hi everyone hope you are doing well I have a table with this structure Table 1: [dbo].[ValueTest] (Date , PorojectCode , Value) It has this data ('1397/04/01' , 'A' , 100) , ('1397/04/03' , ...
2
votes
1answer
38 views

Excessive memory grants from indexed foreign key cascade delete

Each day, stores can enter sales information into our OLTP application. The app calls a stored procedure in SQL to save this information. Based on the users's activity, the application sends a code ...
3
votes
1answer
78 views

reverse the characters of a string without changing the position of the numeric value in T-SQL

With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value. So for string: abc223de11 have edc223ba11
0
votes
0answers
31 views

Worked example of closure tables with from and thru dates

I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now. ...
4
votes
1answer
252 views

Slow While Loop, Query Improvment Assistance

I am working on creating a Datawarehouse. I have created a Time Dimension (Dim_Time), at 5-minute intervals. Hour Aggregations will have [Minutes] = NULL. For the purpose of this example: CREATE ...
0
votes
2answers
47 views

Mistake in a problem solution

I am trying to solve this problem from hackerrank this far I've written this solution select Start_Date, min(End_Date) from (select Start_Date from projects where Start_Date not in (select ...
2
votes
1answer
31 views

Unable to search between IPv6 range when converting string IP Address to VARBINARY(16)

I am attempting to search for IPGeolocation data, which is presented by the data vendor as a start and end range for both IPv4 and IPv6. However, when I convert the IP that is provided, I am getting ...
3
votes
0answers
50 views

Combine multiple stored procedures into one report?

This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is ...
4
votes
3answers
108 views

Create login with SID as string

I'm trying to create a login as follows: CREATE LOGIN [Test] WITH PASSWORD='Test123', SID=SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513') But no matter what it looks like SID only accepts ...
5
votes
2answers
163 views

Explain an OVER clause

I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character. If I am reading the ...
6
votes
1answer
171 views

Self-join example review in Books Online

Here in this example it states that performs a self-join, but it joins Sales.SalesPerson to Sales.SalesTerritory. I knew that self join means joining the same table to itself but here aren't Sales....
2
votes
1answer
27 views

Select duplicates by PrimaryKey

I have to select all duplicates by PrimaryKey Here is my DB ID UserID GroupID 1 1 1 2 1 1 3 1 2 4 2 2 If I pass ID=1 need to select row 1 and 2 Here is ...
1
vote
1answer
20 views

Insert multiple times XML data (SQL SERVER)

I have some difficulties to solve my problem. I have a stored procedure which take 2 input parameters. The first one is a varchar, but in reality it's going to be a list of SomeID separated by comma ...
0
votes
1answer
93 views

Compilation of T-SQL oddities and pitfalls

Like all programming languages, T-SQL has its oddities when compared to a generic programming language. Some of these have tripped us up. It would be nice if there were an already-compiled list so ...
0
votes
1answer
42 views

How to collapse into a single row from multiple rows

Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest ...
6
votes
2answers
297 views

Datetime conversion fails when filtered index is placed on partition column

I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem. The index's filter ...
0
votes
1answer
54 views

TSQL SELECT statement to list a column calculated based on the value of a field

I have a table named Table1, with three fields Project ID, EmployeeID, Starting Date. Need a select statement to list all columns of the table, and an additional column to show the number of project ...
0
votes
1answer
37 views

How To get value of column when it exist in content value of column by sql server 2016

I want To get value of column when it exist in content value of column by sql server 2016 For Example: Table1: Title1 ---- Jack Benz Volvoo Table2: Description Names ---- ...
0
votes
2answers
45 views

Select records with the same first column value and different second column value? SQL Server

Assume we have a SQL Server table as the following: ContentId | TagId -------------------- 1 2 1 3 2 1 2 2 2 3 1002 1 ...
3
votes
5answers
83 views

How to stop a job for the rest of the day after the relevant condition has been satisfied once?

I have a job that runs every 5 minutes to check an IF condition. If the condition is true, it will send an email, if not, it won’t do anything. My problem is that once the IF condition is true, it ...
0
votes
1answer
34 views

SQL Formatting: Add Space within query result

I need to insert a space into result set from a query, I tried using the space() function and right but I have confused myself. The Column result from the query is: 1234567891234567891 I need this:...
0
votes
2answers
81 views

foreach loop in sql scripts?

In powershell I use foreach to run a function through a list of targets. I was wondering that if there is any thing similar to foreach in sql, so I can run the same query through multiple DBs I chose ...
2
votes
1answer
40 views

How to run merge agent job step on tsql

I'm having a problem with the merge replication and I want to try and run the job as a script on ssms so I can see the errors (I've tried almost everything else) Original post Here is the script -...
1
vote
0answers
29 views

Repivoting rows of pivoted data with an dynamic amount of rows

I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
2
votes
1answer
87 views

Is it possible to step through TSQL that uses variables, in SSMS?

If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard. It seems by default ...
0
votes
3answers
66 views

Query where condition take from the list of value

/****** Script for SelectTopNRows command from SSMS ******/ SELECT DISTINCT ( SELECT ForeignCurrencyBal FROM [InsightWarehouse].[dbo].[v_Account] AS subA WHERE ...
-3
votes
1answer
81 views

What is the difference between the date queries and which is more efficient? [closed]

Where tt.transactiondate >= getdate () -1 Where tt.transaciondate >= dateadd (day, -1, getdate()) Where dateadd (day, 1, tt.transactiondate) >= get date() Where datediff (day, tt....
1
vote
2answers
65 views

Warning in Execution Plan

I have a T-SQL query with four inner joins, I executed the query in SSMS with Actual Execution Plan selected. When I review the generated plan I saw the warning icon on Sort operator. What is the ...
-1
votes
2answers
45 views

Is there any possible solution to insert correct records and prevent incorrect records from being inserted by insert statement in SQL server? [closed]

I know that each statement (i.e: Insert , Update , Delete ) in SQL-Server is treated as one single transaction and I know that one of the main characteristics of a transaction is it's 'Atomicity' so ...
4
votes
2answers
475 views

Is there any limit for IN results in SQL Server?

Is there any limit for the content that the IN filter can handle? For example: SELECT Name FROM People WHERE Job IN (All the values goes here) Microsoft docs for IN says: "Explicitly including ...