New answers tagged t-sql
1
This deficiency in the docs was raised as an issue in August 2019, with MS comment being
We've created an internal work item (1595947) to investigate and
potentially update the article.
We're going to close to this item and track with our internal work
item
Until such time as MS update the article (or accept a PR against the docs), here is a probably-...
0
If you don´t want to use a staging table (which would be my preference) you could look at defining the primary key with ignore_dup_key set. An excellent article discussing pros and cons with this approach below.
https://sqlperformance.com/2019/04/sql-performance/ignore_dup_key-slower-clustered-indexes
0
After reading again BULK INSERT documentation I think this may work. It is not elegant but for my scenario it could do it. It require me to know the number of records in the file. That is easy enough to get with:
wc -l 'C:\tmp\activity.csv'
Then with that number I would do:
-- Let's suppose I have one million records (Usually I have several million)
...
2
I think a good approch would be to have a staging table (without PK constraint).
You will load your data from your bulk command into this table and then, you should be able to script down the logic you want to implement into a store procedure that you can run after
Ex: Check for duplicate, log them to another table or send a mail, delete the duplicate base ...
1
ASE's (var)char max is 16384. (assuming ASE 15.x/16.x)
If the query can be broken into chunks, with each chunk no larger than 16K, then you could try concatenating them inside the execute() call.
From the documentation for the execute() command:
There are no restrictions to the number of characters supplied with
the literal string.
And from Example 6 (same ...
5
The difference between local time and UTC time can be reliably calculated by looking at the difference between GETDATE() and GETUTCDATE(), assuming the server operating system is configured in whatever the desired "local" time is (i.e. if local time for you is Central European Time, then the server must be configured to be in Central European Time)....
0
This is incomplete, may be someone can finish it if possible.
DECLARE @Buyer TABLE
(
BuyerId INT IDENTITY(1,1),
Name NVARCHAR(100),
Budget INT
);
INSERT @Buyer (Name, Budget) VALUES
('Anna', 100),
('Brett', 50),
('Conor', 20)
DECLARE @Item TABLE
(
ItemId INT IDENTITY(1,1),
Cost INT
);
INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(...
1
Introduction
I do not have the time for extensive testing, but can suggest from where
to start.
If you rewrite the query in a more symmetrical manner, to
emphasise that both entities are joined to a two-dimensional
cross-section of MyJoinTable:
SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1 ON E1.Id = JT.Entity1Id
JOIN Entity2 E2 ON E2.Id = JT....
1
Even though the asker didn't provide actual execution plans for comparison, I found the question interesting and dug up a little. I am pretty confident that the difference is due to implicit conversion introduced through the LIKE keyword. I made small script showing the effects of it:
CREATE TABLE T1 (A INT NOT NULL)
;WITH CTE AS ( SELECT X.A FROM (VALUES (...
0
In addition to BCM's answer:
The subject of the mail is an indicator if it comes from a job. Just compare it to an email that you know comes from a scheduled job.
Also, if it isn't Agent, then it it can be a module (stored procedure, trigger).
But it can also be SQL (sp_send_dbmail) submitted from a client. In that case, you would have to try to capture it ...
1
It is probably either coming from a job or within a stored procedure somewhere. If it's a stored procedure, then after you find it, use the job search from below to look for the job calling that sp.
---- Search Job Steps
SELECT
J.name AS JobName,
JS.step_name AS StepName,
JS.step_id AS StepNumber,
JS.command
FROM
msdb.dbo.sysjobs J
INNER JOIN msdb.dbo....
2
If you are using parameters, then it sounds like a classic case of parameter sniffing. This article gives a better explanation that I could do right now: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
Another thing to check is, in the execution plan, the estimated number of rows vs. the actual number ...
1
Nasty, but will do what you wanted:
DECLARE @Out TABLE (BuyerId int, ItemId int);
DECLARE @BuyerId int, @Budget int, @ItemId int, @Cost int;
WHILE EXISTS(SELECT TOP 1 Budget FROM @Buyer WHERE Budget >= (SELECT MIN(Cost) FROM @Item))
BEGIN
SELECT TOP 1 @BuyerId = BuyerId, @Budget = Budget
FROM @Buyer
WHERE Budget >= (SELECT MIN(Cost) FROM @...
-2
aka bin packing
There is a recent article on solving this type of problem here :-
https://www.sqlservercentral.com/articles/a-set-based-solution-for-the-bin-packing-problem
-1
I think @mustaccio is right, the problem is a "knapsack problem".
You maybe can attack the problem the other way around using GROUP BY CUBE.
select coalesce(a, 0) + coalesce(b, 0) + coalesce(c, 0) + coalesce(d, 0) + coalesce(e, 0) + coalesce(f, 0) + coalesce(g, 0) + coalesce(h, 0) + coalesce(i, 0)
from (values (50, 30, 20, 40, 10, 40, 30, 10, 5)) t(...
2
I believe the easiest way would be using LAG or LEAD functions. They allow you to accesses data from a previous or subsequent row in the same result set without the use of a self-join. But they're available starting with SQL Server 2012 (11.x).
So your solution would require the a self-join like this:
WITH CTE_MostRecentChange
AS
(
SELECT
...
0
The index can be SEEKed if you search on Col1 (first column).
It can possibly be scanned if you search for the other columns, but if it is depends on whether it covers the index, estimated selectivity and stuff like that. And even if it would be used, a scan is never as effective as a seek (rest being equal).
1
This will get all relations discoverable through FK constraints for a database:
USE <your database>
GO
SELECT
ChildSchema.name AS ChildSchema
,Child.name AS ChildTable
,fk.name
,ParentSchema.name AS ParentSchema
,Parent.name AS ParentTable
FROM
sys.foreign_keys FK
INNER JOIN
sys.tables Child
ON Child.object_id = FK.parent_object_id --...
0
So I found two ways to fix this, with @FlogDonkey sending me down the right path.
There is an option for Results to Text for the max characters displayed, i overwrite that from 256 to 1000 and it worked.
I also found that in Results to Grid you can set it to Retain CR/LF on copy or save.
I did, however, have to restart SSMS to get these changes to take ...
1
The reason for this is that the default Results to Text option's maximum number of characters displayed is set to 256 characters. That's why the rest of the text output is truncated.
Try results to Grid, and you should be a happy camper :-)
1
Use OPTION (RECOMPILE) on the INSERT
2
"How can I look at the overall cost?"
Above was in the comment and I think the question deserve some explicit attention. But I don't think David will like the "answer"...
What do you mean by cost? Something we can quantify, I take it. Which leads to the question of what we want to measure and what unit? Response time? I/O? CPU? Memory ...
4
Nikita has almost everything covered in her answer.
However the answer is also dependent on how many columns and what type of columns you may decide to add.
If you are adding columns with data types occupying less space may be the overall cost may not look that harmful. Please note I am just saying this because we do not know how many columns you are talking ...
4
The more columns you include to index the larger this index becomes. It affects all operations with this index (inserts, updates, selects). It takes more space in buffer pool and when you use index more data needs to be processed (including maintenance tasks like integrity checks and backups). Also when you update column included to this index SQL Server ...
5
How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?
The Filter operator is applying the bitmap built on the join columns at the hash join.
Of the three join predicates, only order_date has a data type that is supported for bitmap pushdown to the column store scan. If you look at the Predicate ...
0
INSERT INTO table1 (ID, Name, pin)
SELECT table2.ID, table2.Name, table2.pin
FROM table2
LEFT JOIN table1 ON t1.Name=t2.Name
WHERE t1.Name IS NULL
or
INSERT INTO table1 (ID, Name, pin)
SELECT ID, Name, pin
FROM table2
WHERE NOT EXISTS ( SELECT NULL
FROM table1
WHERE t1.Name=t2.Name )
If table1.ID value is an identity ...
Top 50 recent answers are included
