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
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
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 ...
- 47
-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
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
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 ...
- 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....
- 113
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
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
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 ...
- 45
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 ...
- 191
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 ...
- 1,039
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 ...
- 45
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
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
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
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?
- 11
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, ...
- 149
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 ...
- 295
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.
...
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
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
0
votes
1
answer
87
views
Update the second last row for each group
I am building a database in SQL Azure, with an MS Access front end.
I have a set of data in a table called PostHolders which looks like this:
ID
PersonID
ApppointmentID
ContractStartDate
...
- 115
1
vote
0
answers
81
views
Same update on different rows results in deadlocking
We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks.
Here is the ...
- 11
0
votes
1
answer
41
views
Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH
I would like to create transaction which will :
DISABLE change tracking IF exists.
TRUNCATE partition for table.
SWITCH PARTITION from different table to main table.
ENABLE change tracking IF was ...
- 295
-4
votes
2
answers
126
views
Calculate total_worker_time in minutes [closed]
I have an easy query which can show me the top 10 elapsed time by query_plan_hash and query_hash:
PRINT '-- top 10 elapsed time by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash
...
- 1,494
2
votes
1
answer
258
views
Can we exchange logical disjunction for UNION ALL?
I saw in that somebody exchange below code:
SELECT
PK1
, PK2
, PK3
, PK4
, C
, B
, SUM(NUMERIC_1)
, SUM(NUMERIC_2)
, MAX(NUMERIC_3)
, SUM(NUMERIC_4)
FROM ...
- 295
2
votes
2
answers
552
views
Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?
I'm currently reading a book from SQL Server 2014. It, like every other online source that I've found, tells you that PERCENTILE_CONT is a very slow way of calculating medians and does not show the ...
- 45
0
votes
2
answers
164
views
How to Import 21k Comma Delimited Text Files having a few columns with quote delimited comma separated values into SQL Server
I inherited a broken process, need to fix it and I'm hoping you, my fellow DBAs, may have a few good solutions.
There's a Windows server folder containing 21,663 CSV files that need to be imported ...
- 1
1
vote
1
answer
33
views
copy data between 2 databases with same tables, but different identities
I have tables Listings and Media which is a child table of Listings and linked based on ListingId in "source" database, and I have the same tables in "destination" dabase.
copy ...
- 359
1
vote
2
answers
81
views
TRN file Backup using LSN
I receive the TRN files on a daily basis from the client and I back it up on a regular basis, i have usually backed it up based on the timestamp, but they recommend using LSN. I download the files ...
- 11
2
votes
1
answer
159
views
Converting VARCHAR to XML with a size restriction
I have a query that needs to return text as valid XML.
I also need to restrict the length of the text to a specific number of characters.
I adapted a function that performs the conversion and outputs ...
- 23
1
vote
2
answers
44
views
TSQL count 2 different columns by using number table
I am trying to count 2 different date columns on a monthly interval by using a number table:
numbers table:
num
0
1
2
3
Originating Table (Example):
Gender
Subscription_Date
Cancel_Date
male
...
- 13
2
votes
1
answer
81
views
Performant Loop or Alternatives
My current data set looks like following
[Table 1]
Key
Group
Amount
Sequence
1
1
50
1
2
1
60
2
3
1
70
3
4
1
90
4
5
2
120
1
I have a second table that records a "Rolling rank" of each ...
- 121
1
vote
1
answer
48
views
What is the optimal way to handle literals that are referenced in NOT IN clauses of many queries within a Stored Procedure?
Back in the days of C (before C#) and in environments where memory was very limited or expensive (or both), there was a benefit to reducing repetitive use of a literal as it would reduce memory usage ...
- 2,151
0
votes
0
answers
88
views
PIVOT for "groups" - rows to columns
How-to convert rows (for group) into columns.
Here is the source table that have:
update: added Id and IdGestion columns
Id IdGestion PeticionId Field Value
48 17 16 Type ...
- 145
0
votes
1
answer
62
views
How to use sys.columns to get all columns of only user tables - in a database
I have hundreds of user tables and each tables has lots of columns. Sys.Columns is returning 16000 columns; whereas INFORMATION_SCHEMA.COLUMNS is returning about 15,000.
Question: Why the above two ...
- 289
-1
votes
1
answer
109
views
Azure DB - Converting Base64 Encoded into Binary Found in Databricks Python
I am going nuts on this. We have a base64 encoded value:
"+G1mQl3A30MKQsCBxMXwyMOc/AZEvpcRRI13mUOy3SNDEFjyQuVQ0UKHFsdCJ/HCQqEvuEE5Lb5AAg=="
We needed decoded to binary. In python (databricks)...
- 11
1
vote
1
answer
55
views
How to get more then one concatenated column from table rows
Below is a code i use now.
In real life @stab table is big (and it is normal table not variable) and there are many concatenated columns, not only 2.
I wonder is there any better solution than I use ...
- 13
2
votes
1
answer
364
views
Detect keys whose values are objects or arrays automatically in SQL JSON Query
I have these JSON:
Application 1:
{
"business_industry": "Agriculture",
"docs": [
{
"internal": false,
"type": "Asset & ...
- 135
1
vote
1
answer
195
views
Querying JSON key with values as JSON in SQL Server
I have these JSON:
Application 1:
{
"business_industry": "Agriculture",
"docs": [
{
"internal": false,
"type": "Asset & ...
- 135
0
votes
1
answer
118
views
ROW_NUMBER() with OUTER APPLY
I have a query that returns the lowest Price of a ProductVariation by using ROW_NUMBER() to enumerate the rows in groupings (partitions) of the ProductId, sorted by the Price ascending (MIN function ...
- 135
12
votes
1
answer
523
views
Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?
Is there any way to tell from within a trigger, when it gets fired, that it is being fired as the result of a DML action that occurred within another specific trigger?
Any chance any information about ...
- 35.1k
0
votes
2
answers
88
views
Do newer versions of SQL Server optimize queries using variables in stored procedures instead of 'magic numbers'?
Do newer versions of SQL Server (after SQL Server 2017) optimize the queries in the stored procedures that use variables instead of the so called "magic numbers"?
E.g. Instead of using
...
- 103
0
votes
1
answer
309
views
Disable ALL indexes on a table in SQL Server without Warning messages
Occasionally we need to perform bulk updates on certain tables and the indexes that help the database perform smoothly in normal use become a hindrance. So we disable all the indexes on such a table, ...
DinahMoeHumm
0
votes
0
answers
73
views
SQL Server 2019 sys.sp_cdc_cleanup_change_table procedure not working
Although CDC works and all transactions are logged, we cannot clear the respective CDC table using the builtin procedure sys.sp_cdc_cleanup_change_table which should remove all the transaction except ...
Shadman Saif Anonno
1
vote
1
answer
227
views
T-SQL schema changes that can be performed in O(1)
We want to minimize the downtime for our users during the upgrade process and want to ensure that our schema changes can all be performed in O(1) and are not O(n). Is there a list of documented schema ...
- 343
0
votes
1
answer
408
views
Extract date from string t-sql
I'm using SQL Server (T-SQL) 2016 and I would like to find the best vay to extract date from string. I dont know date starting index but I know that in given string there will be only one occurence.
...
- 1
0
votes
2
answers
688
views
Change login and user mapping in SQL Server
I have a login1 and two users user1 and user2. All three are created previously.
Originally, login1 was mapped with user1 and now I want to change to map with user2.
First, I tried
ALTER USER user2 ...
- 435
2
votes
2
answers
649
views
SQL Server 2016 UPDATE statement - are variable assignments always processed before column updates?
One of our application devs came to me with an interesting question. In the following code, you'll see that the update statement is assigning the current column values to two variables, but also ...
- 421