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.

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