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