Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

Filter by
Sorted by
Tagged with
0 votes
0 answers
89 views

Exporting data to INSERT INTO script using T-SQL [duplicate]

I need to export a table into a .sql script containing INSERT statements using T-SQL. There are many public posts about using SSMS and clicking on things. I must create something that can be scripted. ...
lit's user avatar
  • 231
0 votes
1 answer
67 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 ...
J. Mini's user avatar
  • 45
2 votes
2 answers
603 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 ...
Marcus's user avatar
  • 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. ...
J. Mini's user avatar
  • 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. ...
J. Mini's user avatar
  • 45
-2 votes
1 answer
87 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 ...
J. Mini's user avatar
  • 45
1 vote
2 answers
55 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....
Axeltherabbit's user avatar
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 ...
David C's user avatar
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. ...
J. Mini's user avatar
  • 45
0 votes
1 answer
90 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 ...
J. Mini's user avatar
  • 45
0 votes
1 answer
46 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 ...
Gadonski's user avatar
  • 191
0 votes
2 answers
138 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 ...
SQL_NoExpert's user avatar
  • 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 ...
J. Mini's user avatar
  • 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? ...
Paradox's user avatar
  • 155
0 votes
0 answers
37 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 ...
Dera's user avatar
  • 13
1 vote
3 answers
327 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 ...
b_c's user avatar
  • 13
0 votes
1 answer
28 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?
zul nasir's user avatar
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, ...
Jaigus's user avatar
  • 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 ...
adam.g's user avatar
  • 295
2 votes
2 answers
992 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,...
camtech's user avatar
  • 133
0 votes
0 answers
41 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. ...
Ashif Shaikh's user avatar
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 ...
nivomi's user avatar
  • 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 ...
J. Mini's user avatar
  • 45
0 votes
1 answer
97 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 ...
Andrew Richards's user avatar
1 vote
0 answers
83 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 ...
theta-fish's user avatar
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 ...
adam.g's user avatar
  • 295
-4 votes
2 answers
133 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 ...
Francesco Mantovani's user avatar
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 ...
adam.g's user avatar
  • 295
2 votes
2 answers
555 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 ...
J. Mini's user avatar
  • 45
0 votes
2 answers
169 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 ...
JasonVL's user avatar
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 ...
Zoinky's user avatar
  • 359
1 vote
2 answers
82 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 ...
Prithvi's user avatar
  • 11
2 votes
1 answer
163 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 ...
user3748557's user avatar
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 ...
RaGe's user avatar
  • 13
2 votes
1 answer
82 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 ...
Caz1224's user avatar
  • 121
1 vote
1 answer
50 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 ...
Mark Freeman's user avatar
  • 2,151
0 votes
0 answers
95 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 ...
Kiquenet's user avatar
  • 145
0 votes
1 answer
70 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 ...
nam's user avatar
  • 289
-1 votes
1 answer
113 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)...
Davik2001's user avatar
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 ...
Look's user avatar
  • 13
2 votes
1 answer
378 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 & ...
Prosy Arceno's user avatar
1 vote
1 answer
218 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 & ...
Prosy Arceno's user avatar
0 votes
1 answer
124 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 ...
gregoryp's user avatar
  • 135
12 votes
1 answer
526 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 ...
J.D.'s user avatar
  • 35.2k
0 votes
2 answers
90 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 ...
Cristian Rusanu's user avatar
0 votes
1 answer
323 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, ...
user avatar
0 votes
0 answers
74 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 ...
user avatar
1 vote
1 answer
228 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 ...
D.R.'s user avatar
  • 343
0 votes
1 answer
427 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. ...
snorlaks's user avatar
0 votes
2 answers
738 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 ...
Circle Hsiao's user avatar

1
2 3 4 5
61