Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2
votes
1answer
23 views
Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?
I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
0
votes
3answers
32 views
Sum of decimal values that come in a string
Is there an easy way to get the sum of all decimal values in a string column.
Example:
"ABC $3.25, DEF $2.50, HIJ2 $0.25"
"ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50"
I want to add only the values ...
2
votes
1answer
30 views
Object id error in sql server
I want to know the object referenced as an associatedObjectId in a deadlock graph, so I ran the below query:
SELECT OBJECT_NAME(1234560112364076)
I get the following error:
Arithmetic overflow ...
0
votes
1answer
8 views
How to update JSON using logic equivalent to WHERE clause
My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
0
votes
0answers
17 views
I am a newbie and I need a query that will query a certain table/column from all the database [on hold]
My table name is File
Column name is Location..I need the records that is in the column
2
votes
1answer
30 views
How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?
How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly.
...
0
votes
0answers
57 views
How to unit test a stored procedure that has a temp table using tSQLt framework [on hold]
I have stored procedure that creates a temp table #line(can't share the SP because of company privacy). I am using tSQLt framework. I am not sure how to address this situation. Please help
tSQLt ...
0
votes
0answers
37 views
Alter view after table drop
I want to alter the view for the drop tables.
For an example:
View:
create view View1 as
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from ...
0
votes
2answers
34 views
Example of MS Access SQL IIF to T-SQL
Given the syntax rules shown below
Access: IF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END
What would the below be formatted to T-SQL ...
2
votes
2answers
19 views
How to detect whether a key is present in a JSON object using TSQL?
This is an interesting one.
Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d.
Imagine this object is passed to a stored procedure and every key is ...
1
vote
1answer
26 views
Designing Calendar table - datatypes
I'm in the process of designing the Calendar table. I took some inspiration from Database design book by Louis Davidson.
What I'm confused about are the data types.
Example:
MonthName varchar(10) ...
0
votes
0answers
16 views
SSIS data flow gets stuck reading table with no errors
When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
3
votes
2answers
169 views
SQL table with same name as catalog view
When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be ...
4
votes
1answer
276 views
Forcing execution plan with local join hints
I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between ...
4
votes
1answer
222 views
Why does JSON_QUERY within CASE statement fail to remove escape characters?
It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement.
Example code below.
declare @data ...
2
votes
2answers
46 views
Generic type functions in T-SQL
Consider the following function
CREATE FUNCTION dbo.SafeDivide
(
@a numeric,
@b numeric
)
RETURNS numeric
AS
BEGIN
RETURN CASE
WHEN @b = 0
THEN 0
...
2
votes
1answer
37 views
What is background job queue in sql server? How to create a background job?
While exploring the SQL server I came up with following views:
sys.dm_exec_background_job_queue;
sys.dm_exec_background_job_queue_stats;
Official site tells that
It Returns a row for each query ...
2
votes
1answer
49 views
What types of locks are held on global temporary tables?
I am wondering if:
the isolation level is Read Committed
data is inserted or deleted or read
are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table ...
0
votes
1answer
26 views
Disable Saving/Exporting Results of TSQL Query in SSMS?
I really don't want to go in to too much detail as to WHY I want to do this. Just know that it would be ideal for my use case.
Is there a way to disable a specific database role from using the Copy, ...
2
votes
3answers
50 views
Not getting distinct results using case query
This is the query I am running. I am getting the correct results except they come in on a different line for each corpstorenum,
so i am getting :
Corpstorenum,BuffetYN, deliverybeeryn, dineinbeeryn,...
2
votes
2answers
131 views
How to replace multiple parts of a string with data from multiple rows?
Here's a fiddle to show what I'm after.
Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in ...
2
votes
1answer
73 views
Debugging TSQL in SSMS [closed]
What techniques can I use to debug TSQL code now the debugger in SSMS 18 has been deprecated/removed?
3
votes
3answers
208 views
Query to categorize rows based on a “time” column without using a CASE expression
There is a ProductTT table as you can see below:
[dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int)
...which contains the following rows:
1 XX 0030
2 UY 0354
3 YY 0517
4 ZZ ...
10
votes
1answer
205 views
function to receive a character input and return date format (with incorrect input)
I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect ...
3
votes
1answer
101 views
Large string concatenation in query
I am trying to concatenates strings in T-SQL like
'This is a test ' + CHAR(13) + CHAR(10) + ' string'
When the string is long (I do not know the limit), I get an error:
Some part of your SQL ...
6
votes
2answers
221 views
How to find if a column is referenced in a computed column?
I'm trying to mass retype columns. That means first dropping and recreating any constraints they are part of.
I found columns referenced by these constraints
Foreign Keys,
Primary Keys,
Indexes,
...
6
votes
4answers
1k views
Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table
I need to write a stored procedure to receive 4 numbers and insert them into a table. This is what I've developed so far:
Declare
@1 Int = 10,
@2 Int = 20,
@3 Int = 30,
@4 Int = 40
Create ...
2
votes
1answer
194 views
Indexes with the same object_id but different names
When I run
SELECT
count(object_id) AS count,
object_id,
min(name) as name1,
max(name) AS name2
FROM
sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count
I will get a list ...
1
vote
2answers
71 views
Query takes 10+ minutes to execute
I'm supposed to find out why this query takes more than 10 minutes to execute. Things is SQL is not my strong suit. I know it has to do with all the group by in part... But what approach am I supposed ...
7
votes
1answer
296 views
Why doesn't SQL Optimizer use my constraint?
I want to create a table with a NOT NULL bool column.
I use TINYINT with CHECK constraint BETWEEN 0 and 1. The constraint is new and thus trusted
Now I would expect that SQL optimizer now knows that ...
1
vote
1answer
21 views
update json value to null
As I understand it, if you select json_value or json_query specifying a key or object that does not exist in your json document, then in strict mode, you will receive an error. This allows you, for ...
2
votes
1answer
44 views
Equivalence of correlated DATEADD intervals
Is there any difference between the following statements?
SELECT DATEADD(MONTH, @SomeInteger * 12, @SomeDate)
SELECT DATEADD(YEAR, @SomeInteger, @SomeDate)
In other words, are there any cases in ...
2
votes
3answers
94 views
Is there a way to retrieve the logical filename from a backup file?
I would like to write an automation script that restores a SQL Server DB from its backup file. However, doing this is not a straightforward procedure in SQL because the main query needs additional ...
-1
votes
1answer
42 views
How to print a query from many rows in one line? [closed]
In SQL Server 2017, I have a many-to-many table:
=========================
id nameID phoneID
=========================
1 1 1
2 2 2
3 1 3
...
-4
votes
1answer
49 views
T-SQL IF/ELSE problem [closed]
IF (@MCODE_VALIDATION = 0)
INSERT INTO [table].dbo.list VALUES('TEST','TEST','TEST');
GOTO GENERATE_CHILD;
ELSE 'Incorrect syntax near the keyword 'ELSE'.
PRINT 'NOT OK'
GOTO ...
0
votes
2answers
50 views
How to use OR operator in CASE THEN statement
I have a situation like this
select col1, col2
from table1
where col1 = case when (booleanexp1) then a OR col1 = b
when (booleanexp1) then c OR col1 = d end
I mean I need to get ...
0
votes
1answer
32 views
how to write this query accordingly
i need this query to run against 20 instances from main server that have linked server to those 20 instances.i want to change the [@server] to the right instance via cursor and make the SELECT ...
1
vote
2answers
47 views
SQL Server cursor and foreachdb loop
I want to create a cursor (without creating a temp table) to get all orphan users in my SQL Server instances. Here is what I've come up with:
Declare @Orphan_users table (username sysname)
DECLARE @...
5
votes
1answer
317 views
Must numeric JSON keys be quoted?
I've noted that key-value values may be unquoted when numeric in TSQL JSON strings, but it seems the key component must always be quoted.
select 1, isjson(''), 'empty string' union
select 2, isjson('{...
0
votes
3answers
52 views
exec master..xp_cmdshell
Can anyone help me resolve the syntax in the following script? Here @Name and @DBname are declared above.
exec master..xp_cmdshell 'echo 'alter login QUOTENAME(select @Name) with default_database = ...
2
votes
1answer
50 views
t-sql to create tables using a declared variable
I am attempting to create multiple tables in multiple databases using declared variables for the db_name, table_name , column_name and column_type. Is this feasible ? I have to create the same tables ...
0
votes
0answers
21 views
Find closed sub directory with full text search
I have a simple table with registered directory names inside a full text indexed varchar column.
For example:
'c:\Dir1\'
'c:\Dir1\Sub1\'
'c:\Dir1\Sub2\'
'c:\Dir2\'
'c:\Dir3\'
I now need to find the ...
2
votes
2answers
60 views
GROUP BY a number of DAY in SQL Server
I have this query
SELECT dbo.f_DateWithoutHour (0,DATEADD (DAY, 3, TheDate)) AS Date
FROM (
SELECT TheDate
FROM xxxxxxx.dbo.dim_date (NOLOCK)
WHERE TheDate >= '...
0
votes
2answers
36 views
How to get list of databases that are mapped to a login?
I was trying to list all databases that are mapped to a login in sql server using T-SQL without creating a temp or any type of table. Any help will be appreciated.
2
votes
1answer
85 views
SQL Server expensive nested loops join and lazy table spool
I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:
select distinct d.documentpath as path, d.documentname as name, d....
8
votes
3answers
191 views
How can I get running totals of recent rows faster?
I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
0
votes
1answer
46 views
TSQL Agent jobs owned by users
I'm new to Brent Ozar's sp_blitz and am trying to do some cleanup in my new job. I get tons of security findings about agent jobs owned by users. Most of these hits are system-type accounts (...
9
votes
3answers
1k views
How to get the last not-null value in an ordered column of a huge table?
I have the following input:
id | value
----+-------
1 | 136
2 | NULL
3 | 650
4 | NULL
5 | NULL
6 | NULL
7 | 954
8 | NULL
9 | 104
10 | NULL
I expect the following ...
3
votes
1answer
218 views
SQL Server 2016 - excessive memory grant warning on poor performing query
I have a relatively large database of 550GB on a SQL Server 2016 EE instance which has a max memory limit of 112GB of the total 128GB RAM available to the OS. The database is at the latest ...
0
votes
1answer
58 views
How to change the default database for login in T-SQL
I was trying to change the default database for logins in the sql server instance to the database they are mapped to. Currently, the default database of all logins in master database.
Alter login ...
