Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
3,046
questions
-2
votes
1
answer
71
views
+50
Huge PREEMPTIVE_OS_GETPROCADDRESS Waits From sp_OAMethod. How to debug?
Some queries are hitting my CPU extremely hard. sp_WhoIsActive reports that sp_OAMethod is the cause (the sql_text column points to it) and that it has of huge waits of the ...
- 30k
0
votes
1
answer
56
views
If RAISERROR is as good as deprecated, how are you supposed to flush the PRINT buffer?
RAISERROR is as good as deprecated. The documentation says
New applications should use THROW instead of RAISERROR.
Yet, it is commonly used to flush the PRINT buffer.
Suppose that I'm writing a long ...
- 45
14
votes
5
answers
9k
views
How do I limit a SQL stored procedure to be run by one person at a time?
I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.
While this ...
2
votes
2
answers
585
views
Why are these columns specified in the include clause of an index rather than as key columns
For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
- 38.8k
0
votes
1
answer
485
views
How can I apply the same logic to multiple tables that have the same number of columns and data types?
I have multiple tables with identical number of fields (and data types).
Here are two examples (DDL to create these tables at the bottom):
Animal
AnimalGroup Name
----------- ---------...
CommunityBot
- 1
4
votes
5
answers
1k
views
What are the idiomatic approaches to copying a table (and all of its data) from one server to another?
I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. ...
- 45
-2
votes
0
answers
31
views
Can OLE Automation Cause Big CXPACKET Waits?
I had two things running on my server at the same time.
An SSIS package that triggers a hungry but heavily parallel stored procedure
A terrible loop that calls entirely too much OLE Automation (e.g. ...
- 45
1
vote
2
answers
53
views
How do I get the object that executed the query from a query handle?
The database is microsoft SQL server.
I've got a query handle (sql_handle) from sys.dm_exec_query_stats
Is there a way to get that the object the query is part of?
for instance I want to have dbname....
- 11.8k
0
votes
1
answer
45
views
Batch update in a large dataset
We have 3 tables:
Product (product)
Stock (product_Stock)
Price (product_price)
In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
- 256
1
vote
1
answer
101
views
What are the idiomatic approaches to cross-server upserts?
This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following:
You have two T-SQL servers, Production and Reporting.
...
- 45
1
vote
1
answer
304
views
Validating SQL Server Change Tracking Synchronization
I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
CommunityBot
- 1
0
votes
0
answers
76
views
Database restore takes 20 minutes longer when run through Agent job vs as T-SQL in a query window
When I run this statement in a query window as my own user account, the restore completes in 40 minutes. When I run it as a step in an agent job it runs in over 60 minutes. In the agent job, the step ...
- 1
11
votes
2
answers
33k
views
Select data divided in groups evenly distributed by value
I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...
- 301
3
votes
2
answers
832
views
Calculate median of values in T-SQL
I've got a piece of code (stored procedure) written in T-SQL that inserts into a table several data from a temporal table as shown here:
INSERT INTO [myschema].[CalculatedData](
Year,
Month,
...
- 1
0
votes
1
answer
89
views
Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?
In the past, there was a lot of paranoia around IN. In particular, I remember claims that it can be optimised differently to an equivalent WHERE EXISTS. Is this claim still true? Or is it now ...
- 82.9k
0
votes
1
answer
802
views
Importing CSV Flat File - how can we change defaults of all columns to n characters
I have to import a CSV file into a table that has 75 columns. The Flat File connection defaults all the columns to 50 character strings. Some of the fields are much smaller and some are 255. The only ...
CommunityBot
- 1
1
vote
3
answers
323
views
Maintain an ordinal column
I need to maintain a column that stores a sort order partitioned by a parent record foreign key. The column should be the contiguous counting numbers and should automatically renumber the records when ...
- 13
0
votes
2
answers
132
views
Convert varchar into datetime and use date diff on the where clause
I have a field ABCD stored as varchar(16) and the data stored as 20170509074744CD.
I am not versed with SQL Language. I will greatly appreciate any help converting the field ABCD into datetime and use ...
- 301
0
votes
1
answer
83
views
Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?
The documentation lists the following requirements for bulk imports with minimal logging
The recovery model of the database is set to simple or bulk-logged.
The target table is an empty or non-empty ...
- 176
5
votes
2
answers
28k
views
Applying Quotes Across Multiple Lines
In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk.
Is there a similar keyboard function that applies single quotes across ...
- 17.4k
6
votes
1
answer
397
views
Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow
I've updated this post with some test data.
I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
CommunityBot
- 1
1
vote
2
answers
803
views
BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1
I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. ...
CommunityBot
- 1
1
vote
2
answers
329
views
Update one table's row from the results of multiple rows of another table
I've seen posts on how to update multiple rows (specifically using case?), but my question is the inverse.
I have a table of phone_numbers
Person_ID
Phone_number
1
102-345-6789
1
102-445-6789
2
...
- 301
3
votes
1
answer
76
views
Is the WITH (NOLOCK) hint ignored on the non-target tables of an UPDATE or DELETE statement?
In SQL Server Databases, the WITH (NOLOCK) query hint is ignored on target tables in UPDATE and DELETE statements. However, does this also apply to tables included in a join for an update or delete? ...
- 155
0
votes
0
answers
1k
views
TSQL Materiaized/Indexed View Cluster index with subquery
I have two tables: Listings and Media and Media is a child of Listings. I am trying to create a indexed view that will include the first record from Media. I would also like to index my view so that ...
- 301
0
votes
1
answer
2k
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 ...
- 301
0
votes
1
answer
30
views
How can I get the accurate and optimized grouped data by passing expression in partition by statement?
I need to get the correction and a way to group it in a better and efficient way.
What I want is that I need to group the data on the bases of RenterUserId or MainDriverUserId
in following cases /...
- 301
1
vote
1
answer
2k
views
Comma separated value string and compare against other values
Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything.
Here is my query:
DECLARE @ATID AS VARCHAR(3) = 12
DECLARE @...
CommunityBot
- 1
0
votes
1
answer
294
views
How to use reference to another table in aggregate function
I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write:
SELECT [GroupId] = cr.[GroupId],
[...
CommunityBot
- 1
0
votes
0
answers
36
views
Analysis and Prediction using SQL
I have a transactional application on MSSQL database where people can send money to other people at anytime (More like Cashapp), i want to be able to predict a customer's frequent beneficiary on ...
- 13
0
votes
1
answer
334
views
Get Row Value by Column Name of Name at runtime
I currently have a stored procedure that obtains rows from an import table and joins another table which has a row for each column header of the import table.
I am able to query this but in order to ...
CommunityBot
- 1
8
votes
1
answer
1k
views
Inserting with implicit type conversion causes warning for cardinality estimates
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:
Type ...
4
votes
1
answer
31k
views
Send Multiple Attachments With Send DBMail
I thought that you just needed to separate two file names with a ; but my syntax fails with an error of
Msg 102, Level 15, State 1, Line 251
Incorrect syntax near '@filename2'.
This is my full ...
- 1
0
votes
1
answer
27
views
Do SQL Server have the utility same as Oracle Wrap Utility?
i have made a useful scripts in my environment and i want to wrap the script same as Oracle Wrap utility can do. is there any other way to the same in sql server?
- 17.4k
0
votes
2
answers
654
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 ...
CommunityBot
- 1
0
votes
1
answer
299
views
Exporting multiple TSQL result sets into one spreadsheet using Powershell
I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table ...
CommunityBot
- 1
0
votes
1
answer
40
views
How can I obtain a set of results such that each row's date is x numer of days after the previous one?
Lets say I have a table of purchases that looks like this:
Purchases
---------
Item_ID
Purchase_Date
Customer_ID
How would I be able to obtain the set of purchases, starting from the first purchase, ...
- 347
1
vote
2
answers
1k
views
TRY .. CATCH sometimes fails to catch PRIMARY KEY VIOLATION
I wrote code to upsert records to the database. The database is Azure DB, S4 tier.
Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many ...
- 1
0
votes
1
answer
52
views
What is the best option to remove duplicates in big heap?
Our Server is SQL Server 2016 Database with Full Recovery model.
We have table "A" - heap which has 280 millions of records and size 90 GB with 50 columns. This table contains around 5 ...
- 393
0
votes
1
answer
488
views
Query to list all the databases that have a specific user
I need to form a query that lists all the databases that include the words 'PRD' that have a single specific user name 'user_1'
So far I have this query:
select name as username,
create_date,
...
- 148
2
votes
2
answers
980
views
Conditional WHEREs if columns exist
I have several tables which typically have nullable "CreatedDate" and/or "ModifiedDate" datetime columns. I would like a query that can select from any single table where one, both,...
- 133
0
votes
0
answers
39
views
Cannot find the symmetric key 'e1', because it does not exist or you do not have permission. (SQL Server 2019)
It seems to be a weird error, I have tried everything from giving Control, Alter, and View Definition permission on the symmetric key but nothing seems to work out.
Tried everything from this article.
...
39
votes
8
answers
81k
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(...
- 82.9k
0
votes
0
answers
46
views
Convert a list-of-IDs column into a junction table [duplicate]
I have a table, Office, with a column that contains lists of ids representing many-to-many relationships:
TABLE Office {
OfficeId int IDENTITY(1,1) NOT NULL,
CubeIds NVARCHAR(MAX) NULL, --values ...
- 11
0
votes
1
answer
836
views
Getting data from Postgres through Linked server of SQL
I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query
select * from openquery(SALESFORCE,'select closeddate
...
CommunityBot
- 1
4
votes
2
answers
3k
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 ...
CommunityBot
- 1
2
votes
1
answer
2k
views
Is it worth time converting Access query to T-SQL query
Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions.
I had a database built in ...
- 2,201
0
votes
1
answer
668
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 ...
CommunityBot
- 1
0
votes
1
answer
49
views
For a SQL Server page, is there a distinction between being "in memory" and "in the buffer pool"?
The following excerpt is in Chapter 10 of T-SQL Querying by Itzik Ben-Gan. He is listing the benefits of In-Memory OLTP tables over traditional pages.
You can also dispense with the considerable ...
- 45
4
votes
1
answer
4k
views
Should I add set nocount for functions?
I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC.
I have a scalar-valued function that is called from an ...
CommunityBot
- 1