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
1 answer
33 views

INFORMATION_SCHEMA.COLUMNS - how to distinguish between table columns and view columns

My database has lots of tables and lots of views. For one of my tasks, I'm using this query. When I run following query it returns tons of columns - many of which belong to tables and many belong to ...
nam's user avatar
  • 291
0 votes
0 answers
62 views

Properly DELETE 500k+ Rows from Multiple Tables in a Single Script

There are several examples all over the Internet and StackOverflow on how to properly delete 500k+ rows from a single table and COMMIT those DELETES in Batches. I am unsure of how to incorporate these ...
Code Novice's user avatar
-1 votes
0 answers
54 views

sp_OAMethod is causing blocking with other normal Queries

I have been facing something new issue. SP_OAMethod is causing blocking with other normal queries. I have a procedure which includes select queries, and insert, update queries with begin transaction ...
dbajayy's user avatar
-3 votes
4 answers
120 views

When should I inspect the Error Log?

I've had a varied career, but I have never found relevant information in the SQL Server Error Log. Under what circumstances should I consider inspecting it?
J. Mini's user avatar
  • 81
3 votes
3 answers
146 views

How can I know how many tables my DELETE has changed?

Suppose that I have many tables linked by a chain of ON DELETE CASCADE foreign keys. To the best of my knowledge and experience, deleting from one table gives me no notice at all that I have in fact ...
J. Mini's user avatar
  • 81
0 votes
1 answer
76 views

EVENTDATA() function is giving only null values in sql server

I'm trying to capture logins time with a trigger using EVENTDATA() function. But It's giving Null values only for all types of events. Can anyone please help me on this? This is my Trigger CREATE ...
dbajayy's user avatar
0 votes
1 answer
85 views

Unique Index on NULLable field isn't creating a Unique Constraint so I can make it a FK on another table?

I'm in MS SQL 2016. I'm a bit confused. I have a field that I have put a unique index on. The DocID is not a primary key, but it will be unique (and is NULLable): CREATE UNIQUE INDEX [...
Jeff.Clark's user avatar
5 votes
1 answer
251 views

Merge Delete on joined tables is allowed but has a bug

To start off, an updatable CTE, derived table or view may be the target of an UPDATE directly, even if it has multiple base tables, as long as all columns are from the same source table. But they ...
Charlieface's user avatar
  • 12.2k
2 votes
1 answer
178 views

What are the advantages and disadvantages of inspecting the Plan Cache rather than the Query Store?

The Plan Cache and the Query Store are not the same, even for the same query. When hunting for performance or monitoring information on a specific query or set of related queries, what are the ...
J. Mini's user avatar
  • 81
1 vote
1 answer
118 views

How to measure the cost of enabling Query Store?

Enabling Query Store can have performance benefits, particularly as of SQL Server 2022, but what about the costs? The documentation insists that the costs are small, but I want proof on my own system. ...
J. Mini's user avatar
  • 81
0 votes
1 answer
172 views

Why use a memory-optimized table variable instead of a temp table or normal table variable?

Table variables have a bad reputation, mostly due to cardinality estimates. However, this reputation was earned before the introduction of memory-optimized table variables. Microsoft covers some use-...
J. Mini's user avatar
  • 81
0 votes
1 answer
77 views

If memory-optimised tables always live in RAM, how do they survive a server reboot?

I clearly have a missing piece in my understanding of in-memory OLTP. Suppose that I have a memory-optimised table that is set up to persist both schema and data. Given that these tables always live ...
J. Mini's user avatar
  • 81
1 vote
2 answers
209 views

How can I tell if a full-text index is being used?

It's possible to tell how much a rowstore index is used, but what about full-text? I could find nothing in any GUI or DMV. Even dbatools doesn't support it. I've inherited a server that has a few full-...
J. Mini's user avatar
  • 81
0 votes
1 answer
39 views

Is there ever a reason to create a non-clustered index on a column that is already the clustered primary key?

Suppose that I have a table. Its primary key is clustered and consists of exactly one column. Is there ever a reason to also make a non-clustered index on that same column with no other columns in any ...
J. Mini's user avatar
  • 81
1 vote
1 answer
54 views

Job history cleanup query being blocked

I'm running the below query every hour to cleanup job history in the sysjobhistory table for all jobs that execute every 10 seconds. The query aims to delete 100k rows of job history that is older ...
Marcus's user avatar
  • 59
0 votes
1 answer
94 views

What is the idiomatic way to truncate a string down to 100 byte-pairs?

nvarchar(100) holds 100 byte-pairs, as documented here. LEFT([Your String], 100) truncates your string down to 100 characters as documented here. These are not the same. Foolishly, I have used LEFT([...
J. Mini's user avatar
  • 81
0 votes
2 answers
154 views

SELECTING TOP X based on last 30 days of activity

I have a table CREATE TABLE [dbo].[UserMoviesActions]( [Actionid] [int] IDENTITY(1,1) NOT NULL, [MovieId] [int] NOT NULL, [UserId] [int] NOT NULL, [CreatedDateUtc] [datetime2](7) NOT ...
Zoinky's user avatar
  • 359
0 votes
1 answer
88 views

Correct way to get a players position

I have a UserActivities table CREATE TABLE [dbo].[UserActivities]( [UserId] [int] NOT NULL, [Points] [int] NOT NULL, which could have 100k+ users, each user having 1k+ activities. I would ...
Zoinky's user avatar
  • 359
23 votes
1 answer
4k views

Is "+" slower than "CONCAT" for large strings?

I have always thought that CONCAT function is actually a wrapper over the + (String Concatenation) with some additional checks in order to make our life more easier. I have not found any internal ...
gotqn's user avatar
  • 4,016
0 votes
2 answers
182 views

Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be ...
J. Mini's user avatar
  • 81
3 votes
3 answers
593 views

Given that CREATE PROCEDURE must be the first statement of its batch, what is the use of Temporary Stored Procedures?

I was recently made aware of temporary stored procedures and became shocked that I've never seen them used before in my career. I was very disappointed when I remembered that CREATE PROCEDURE must be ...
J. Mini's user avatar
  • 81
0 votes
1 answer
45 views

SQL Availability group primary not visible from secondary via sys.dm_hadr_database_replica_states

Trying to create a single pane of glass for all our servers as to the Availability Group State and Health. I want to do this by writing an app and calling t-sql statements to gather data from each of ...
Keith Clark's user avatar
1 vote
2 answers
125 views

Understanding table locking in MS SQL TSQL Transactions

I wanted to make the old following query safe from data races when running them in parallel. The query checks if a row exists in in a table using specific criteria and if such row does not exist, it ...
The amateur programmer's user avatar
1 vote
1 answer
28 views

Hypervisor Host Information for SQL Servers (non-Cloud) Virtual Servers

Is there a way to find the backend storage information through tsql? I've been asked to find out which servers are in what host in our environment.
Julie's user avatar
  • 61
1 vote
2 answers
97 views

T-SQL round current time down to the last 15 minute period

I would like to select data from the last 15 minute period e.g: at 12.02 I want rows from 11:45 to 12:00 at 12.09 I still want rows from 11:45 to 12:00 at 12.18 I want rows from 12:00 to 12:15 The ...
beehive's user avatar
  • 137
1 vote
1 answer
116 views

Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?

Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates ...
J. Mini's user avatar
  • 81
0 votes
0 answers
16 views

The recovery model cannot be changed to SIMPLE when any files are subject to a RESTORE PAGE operation [duplicate]

One of my pages in database needed restore. so I did a restoration by this command: USE master RESTORE DATABASE myDB PAGE = '1:4171728'FROM DISK = 'D:\Backups\Database\myDB.bak 'WITH NORECOVERY" ...
mmrz1360's user avatar
1 vote
1 answer
136 views

How can run-time errors be distinguished from others?

Consider the following error message. Msg 207 Level 16 State 1 Line 7 Invalid column name 'FOO'. Because of how I trigged it, I happen to know that is was a parse/compile time error. Suppose that I ...
J. Mini's user avatar
  • 81
1 vote
2 answers
69 views

Explain plan will sort the result after join even the column included in index

I am using SQL Server 2022 Developer Trying get all AccessLog that classified to type 1. SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message] FROM [AccessLog] AS [t] LEFT JOIN [...
Uni's user avatar
  • 11
0 votes
0 answers
90 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
-1 votes
1 answer
131 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
  • 81
2 votes
2 answers
684 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
  • 59
7 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
  • 81
-2 votes
1 answer
150 views

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
  • 81
1 vote
2 answers
137 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
85 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
113 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
  • 81
0 votes
1 answer
96 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
  • 81
0 votes
1 answer
89 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
219 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,049
0 votes
1 answer
96 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
  • 81
3 votes
1 answer
89 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
43 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
  • 23
1 vote
3 answers
367 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
31 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
41 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
62 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
  • 335
2 votes
2 answers
1k 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
84 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

1
2 3 4 5
62