Skip to main content

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
27 views

.DTSX package is there a way to wrap a workflow into a transaction

I have a dtsx package that imports data from a csv into a table, it truncates the table before it loads the data into the table. The problem is that sometimes for a reason or another the package fails,...
Axeltherabbit's user avatar
11 votes
1 answer
776 views

Is "ROW_NUMBER() OVER(ORDER BY xml.node)" well defined?

(It is more a question of documentation rather than behavior. It has been migrated from Stack overflow as was suggested there.) While researching the answer to another question that required ...
T N's user avatar
  • 221
1 vote
1 answer
69 views

Is it parameter sniffing?

Application uses Entity Framework to create/run query. SELECT [t].[sbt_assetid], [t].[sbt_messagetimestampmst], [t].[sbt_transid], [t].[CurrentRow], [t].[etl_timestampUTC] , [t].[sbt_battery], [t].[...
Sergio Pacheco's user avatar
0 votes
1 answer
55 views

Handling duplicated data in SQL

I have a tables which are created and populated with millions of rows on a daily basis. There is no ID field, and rows are identified by the unique combination of field values. There is no possibility ...
Luke's user avatar
  • 349
0 votes
2 answers
68 views

How to simplify this intersect query?

SELECT * from M left JOIN K on M.id = k.id left JOIN W on K.str = W.str where str = N'أخبار' INTERSECT SELECT * from M left JOIN K on m.id = k.id left JOIN W on K.str = W.str where str = N'فلسطين' ...
simpatico's user avatar
0 votes
1 answer
144 views

Alternate T-SQL Approach for Creating a PATINDEX Funtion to Find Multiple Occurrences of a Pattern

I'm working on a project to parse through numerous varchar max fields for multiple occurrences of specific patterns. For example, a cemetery plot was originally sold on 2/14/1928, then deeded on 7/4/...
JasonVL's user avatar
0 votes
0 answers
34 views

Use bitwise operator to confirm intA "includes" intB? (e.g. @schema_option in sp_addarticle) [duplicate]

I've generated scripts for our replication publications/subscriptions, and I'd like to confirm whether the sp_addarticle @schema_option param of 0x000000000803509F "contains/includes" the ...
Bobogator's user avatar
1 vote
1 answer
51 views

Number of Real Months Between Two Dates

Background We have service contracts that can start and end on any arbitrary dates. Typically however, contracts end "X years minus a day". For example, typical date ranges: Start Date = ...
HardCode's user avatar
  • 288
1 vote
1 answer
52 views

Scripting Replication

I'd like to know how to automate the creation of transactional replication. in a test environment, I often need to restore Prod data. The test data is replicated to another server for reporting (and ...
Trubs's user avatar
  • 754
1 vote
2 answers
954 views

Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?

I have been told that if your transaction consists of multiple statements, then the SNAPSHOT transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the ...
J. Mini's user avatar
  • 555
0 votes
0 answers
49 views

Azure SQL Database :: table size query takes forever to execute

I was working with the awesome Azure SQL Database Diagnostic Information Queries created by Glenn Berry and I realized that the query number 40 takes forever to execute: -- Get Schema names, Table ...
Francesco Mantovani's user avatar
0 votes
1 answer
119 views

Run TSQL alter db set trustworthy on multiple times

We have a few SQL Server databases that needs to have trustworthy option set to 'ON' to allow for external assemblies to load, but every time there's any security patch, the option sets to off and the ...
Affy's user avatar
  • 3
0 votes
0 answers
69 views

Schema not accessible implicitly on dev DB

I've got 2 databases, one for production and one for development. On production I can run select * from [mytable] -- and query [myschema].[mytable] without specifying the schema but on the ...
Axeltherabbit's user avatar
22 votes
2 answers
5k views

Why is COALESCE not a function?

The docs insist that ISNULL is a function, but COALESCE is not. Specifically, they say The ISNULL function and the COALESCE expression If I put on my Lisp hat, I can think of COALESCE as a macro and ...
J. Mini's user avatar
  • 555
0 votes
0 answers
64 views

What does dollar sign $ mean in a view definition? [duplicate]

I wanted to see the definition of system view sys.configurations. I ran query as follows: EXEC sp_helptext 'sys.configurations'; and I get the result: CREATE VIEW sys.configurations AS SELECT ...
user avatar
0 votes
1 answer
59 views

SQL Server identify different queries from a session

I wish to send an alert on long running queries for every 60 seconds the query accumulates. I have a query to serve this using SELECT some_columns FROM sys.dm_exec_requests er INNER JOIN sys....
beehive's user avatar
  • 137
0 votes
1 answer
33 views

Extract data from string column based on delimiter

I have a column with value like this. Declare @Table TABLE (ID int, Val varchar(100)) Insert into @Table Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx' I want to dynamically extract Jan as Month ...
user2370668's user avatar
4 votes
3 answers
1k views

Integer number in the 700000s as the days from year 1: how can this be cast in tsql to a date and back if the oldest datetime date is 1753-01-01?

I fell upon an integer format for dates for which I also know the date, but I do not know how to get to that date in TSQL and I also do not know how to get to the integer if I have the date: 700444 -&...
questionto42's user avatar
0 votes
0 answers
30 views

How can I attempt to row-compress a table type?

The documentation for row compression states that it has "No effect." on table types. This suggests that it's possible to attempt it, but ineffective. How can it be attempted? I find it ...
J. Mini's user avatar
  • 555
0 votes
0 answers
61 views

Catching errors in bulk insert

I am currently adding a new feature to my ASP.NET website, the goal is to upload a CSV file and have it processed by a SQL Server stored procedure. To perform the bulk insert, I am using this command ...
Helmi KAMEL's user avatar
-1 votes
1 answer
110 views

Create a category column or a table for each category?

Which is better? Table: Words Columns: ID, Word, ParentWordID, CatID Table: Categories Columns: CatID, Category Or: Table: Words Columns: ID, Word Table: Categories Columns: CatID, Categories ...
simpatico's user avatar
-1 votes
2 answers
49 views

implement MSSQL function inside a query

i have a query that eventually call the function [dbo].[HexToDec] in the select statement: INSERT INTO #KeyValuePairs (SourceRowId, [Key], [Value]) SELECT sv1.SourceRowId, sv1.Item AS [Key], ...
michael Diaz's user avatar
1 vote
1 answer
38 views

Excessive Execution Time

Context: I have written a t-sql query that works perfectly if I do not use a SELECT with a combining of 2 columns (txtSubID and txtSubmitByInitials). The problem is, I need to combine the 2 columns to ...
MIS_Gem's user avatar
  • 161
2 votes
1 answer
105 views

MSSQL Varchar field accept only int

On my test server (SQL 16.0.1110.1), have this table defined this way: CREATE TABLE [dbo].[mytable]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [IDOpSt] [bigint] NULL, [Order] [smallint] NULL, ...
MJSantos's user avatar
3 votes
2 answers
246 views

MS SQL Server: How to alias a linked server

This is regarding MS SQL Server. I have two DB servers for my application named Torch TorchProd and TorchTest are the db servers. There is also a data mining server DataMineProd and DataMineTest I ...
user avatar
0 votes
1 answer
173 views

OPENROWSET on sp_executesql with two dynamic parameters

Can someone give me a working example of using OPENROWSET on SQL Server with dynamic SQL (sp_executesql) executing a stored procedure with two parameters as variables, preferably date and integer? I ...
mk SQL's user avatar
  • 5
0 votes
1 answer
40 views

odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A collation conflict ... in the UNION ALL operator ... cannot be resolved. (#451)

I union all the distinct columns of the INFORMATION_SCHEMA.COLUMNS views of many databases which I query with a direct query: select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from xyz ...
questionto42's user avatar
0 votes
2 answers
54 views

Distinct combinations in a column over different partitions

Here is a simple table UserId RoleId 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 3 1 3 2 3 3 I am trying to find distinct combinations in RoleId column among different users. In this example I can ...
Yatish's user avatar
  • 3
0 votes
0 answers
112 views

T-SQL Temp Table Performance Issue

At my current contractor job, I have come across something I have never seen before in SQL performance. There is a report that takes most of the day, or the entire day to run. Can return about 5 ...
user avatar
0 votes
0 answers
42 views

TSQL query for metadata statistics like granularity, number of rows, indexes, keys, like a broadened MySQL INFORMATION_SCHEMA.STATISTICS, but for TSQL

The MySQL INFORMATION_SCHEMA.STATISTICS table has these columns: TABLE_CATALOG The name of the catalog to which the table containing the index belongs. This value is always def. TABLE_SCHEMA ...
questionto42's user avatar
-2 votes
1 answer
80 views

Is there any way to send SMS without OLE Automation? [closed]

We use OLE Automation methods such as SP_OAmethod, Sp_Oacreate, etc.. to send SMS to users from SQL Server. I wish to stop use OLE Automation. Is there any alternate way to send SMS?
dbajayy's user avatar
0 votes
1 answer
121 views

How can functions that make heavy use of table variables be optimised?

Suppose that you have a multi-statement table-valued function that makes heavy use of table variables. What methods exist for optimising them? I only know of the following and they all disappoint me. ...
J. Mini's user avatar
  • 555
4 votes
2 answers
3k views

Are views logically redundant?

Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any ...
J. Mini's user avatar
  • 555
0 votes
1 answer
121 views

Cannot disable Query store due to QUERY STORE ASYNC LOAD

I'm using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64). One of our databases seem to have a run away Query Store with over 90 gb stored. I want to disable QS, but cannot do so ...
siggemannen's user avatar
0 votes
1 answer
68 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
  • 301
0 votes
0 answers
109 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
-3 votes
5 answers
268 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
  • 555
3 votes
3 answers
227 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
  • 555
0 votes
1 answer
173 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
129 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
274 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
  • 13.7k
2 votes
1 answer
416 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
  • 555
2 votes
2 answers
186 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
  • 555
0 votes
1 answer
254 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
  • 555
0 votes
1 answer
85 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
  • 555
1 vote
2 answers
568 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
  • 555
0 votes
1 answer
46 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
  • 555
1 vote
1 answer
65 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
  • 69
0 votes
1 answer
119 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
  • 555
0 votes
2 answers
160 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

1
2 3 4 5
63