60
You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails).
Transaction simulation suggested by the person who ...
14
There are some errors which are so severe that the CATCH block is never entered. From the documentation
Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.
...
12
Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor. Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the data structure you are traversing.
DECLARE st CURSOR LOCAL STATIC FOR . . .
11
i-one: The workaround that is suggested to you makes possible (at least) violating "A" of the ACID. For example, if SP is being executed by a remote client and connection breaks, then partial "commit"/"rollback" may happen, because of server can terminate session between two insertions/deletions (and abort SP execution before it reaches its end).
Does ...
9
You want WHILE @@FETCH_STATUS = 0 which means continue unless something isn't right.
Using <> -1 means it will continue even if the row fetched was missing, or it's not performing a fetch operation, making it infinite unless you get -1 as a return value, since there are 4 return values for @@FETCH_STATUS.
0 The FETCH statement was successful.
-1 ...
9
Like other execution plan warnings, this one is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.
As a purely practical matter, that is pretty much the end of it. The precise conditions that trigger this optimizer warning ...
9
Benjamin.
If I understood you correctly, you want something like this:
SELECT TR1.Title AS SourceTitle,
TR2.Title AS DestinationTitle
FROM [Result Map] AS RM
INNER JOIN Table_Result TR1 ON RM.Source_Id=TR1.Id
INNER JOIN Table_Result TR2 ON tr2.Id=RM.destination_id;
The query will return
SourceTitle DestinationTitle
ABC DEF
8
First, don't worry it's not just you. This trips up newcomers to SQL all the time. I see it so often. It's almost an inevitable part of SQL learning.
I'll try to do it in words. George K has already answered with the suitable code.
The trick is to not think of Table_Result as {a table from which you extract titles}.
This inevitably leads to you writing:
…...
8
As detailed in the documentation the correct way to specify that you are creating a log file is to use CREATE DATABASE ... LOG ON (...) - you are using ON (...).
Your second bit of code simply specifies to create an MDF file (data file) using .ldf as the file extension. To create a log file you'd need to replace ON with LOG ON as below.
Declare @DBname ...
6
count(column) does not count null values so you could use count() with a case statement that returns h.USERID or null.
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'Work Web' THEN h.USERID ELSE NULL END) UserCountWorkWeb
, COUNT(DISTINCT CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN h.USERID ELSE NULL END) UserCountOutlook
5
As per the documentation the OPENJSON command requires compatibility mode 130 - this is SQL Server 2016
In this case this means that the feature was introduced in SQL Server 2016, and can only be used in databases where the Compatibility model is set to 130 (SQL Server 2016) or higher.
Compatibility levels are tied to a specific SQL Server version and are ...
5
You should probably play with scripting options in Tools-Options-SQL Server Object Explorer-Scripting in SSMS menu.
There you can define what you would like to script and what not. Compression is there.
As for the other properties - have a look at this StackOverflow question - it will probably shed some light:
SORT_IN_TEMPDB is not a property of an index, ...
4
Depending on indexing and size of data, you may find performance is better if you use:
SELECT * FROM table WHERE (col = 0 OR col IS NULL)
Or
SELECT * FROM table WHERE col = 0
UNION ALL
SELECT * FROM table WHERE col IS NULL
4
Here's a timeline of the events you described, as well as my commentary on what was the likely cause of your bad plan.
You're alerted of a slow query that was previously fast
Bad times, I empathize!
You go crazy and run "the forbidden dbcc freeproccache" and it doesn't help
Usually, clearing the cache fixes problems like this if the problem is parameter ...
4
Bad idea code is just going to be more expensive to fix down the line.
If there are blocking problems using explicit transaction (rollback/commit), point your DBA to the internet for some great ideas to address the issues.
Here's a way to help alleviate the blocking: https://www.sqlservercentral.com/articles/using-indexes-to-reduce-blocking-in-concurrent-...
4
This is not a programming issue, rather it is an interpersonal/miscommunication issue. Most likely your "DBA" is worried about locks, not transactions.
The other answers already explain why you have to use transactions... I mean that's what RDBMS do, without properly used transactions there is no data integrity, so I'll focus on how to solve the real ...
3
The fake transaction strategy is dangerous because it allows concurrency issues that transactions specifically prevent. Consider that in the second example any of the data might be changed between statements.
The fake transaction deletes are not GUARANTEED to run or succeed. If the database server turns off during the fake transaction, some but not all of ...
3
What's probably happening is that another session is connecting to the database after you set it to SINGLE_USER. Always connect to a database before you set it to SINGLE_USER, or you can't guarantee that you will be the single user. But you can't drop a database you're connected to, so SINGLE_USER is just not the right state.
While setting the database ...
3
TLDR: Use proper isolation level.
As you correctly noticed the approach without transactions and with "manual" recovery can be very complex. The high complexity means normally much more time to implement it and much more time to fix errors (because complexity leads to more errors in the implementation). It means such approach can cost your customer much ...
3
It doesn’t execute Query_3, it doesn’t execute any of the following Inserts, including the one in the catch block.
A common problem with using a "log table" is that in error conditions your log entries aren't available, as they can't be written or they get rolled back.
You can return diagnostic messages to the client, and write them to the SQL log instead.
...
2
I have been lucky to have had the opportunity to work with many great people in my career and I used to think the same as you do now.
I've met DBA's who were better at writing T-SQL than me, and I've worked with DBA's that don't know a single line of T-SQL. While I personally find the latter frustrating, I try to keep an understanding of the task they are ...
2
In SQL Server 2016 or better, you can use STRING_SPLIT. I didn't dig in to all of your rules but here is a general approach. Note that the DECLARE @Tables are just there to mock up data. You'd remove those and change @Table1 and @Table2 in the final query to test against your real tables.
DECLARE @Table1 TABLE(folder varchar(32), OwnerNumber varchar(32));
...
2
This may not be the best performing way to do this but it should work for you.
Set up code: (Anyone else wanting to answer feel free to copy this part)
CREATE TABLE #Attrib (itemno int, attrname varchar(50), attrvalue varchar(50));
INSERT INTO #Attrib VALUES
(1, 'Color', 'Black'),
(1, 'Connector', '3.5mm'),
(1, 'Output', '5W'),
(2, 'Color'...
2
Here's an option that uses ROW_NUMBER to create a join based on the order of the ID column from each table. Naturally, this assumes an equal number of rows in each table.
--demo setup
Declare @T1 table (id int)
Declare @T2 table (id int)
insert into @T1(id) values(1),(2)
insert into @T2(id) values(4),(5)
--solution
select a.id, b.id from
(
select id, ...
2
When USE appears in a nested batch, it affects only statements in that batch. The database context is restored after the batch completes. EG
use tempdb
exec ('use master; select db_name()')
select db_name()
outputs
master
tempdb
2
Your problem might be related to issuing a USE inside of a cursor. Instead, I would suggest using the following approach, so you can get rid of the cursor.
EXEC sp_MSforeachdb
'USE ?
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
IF EXISTS(SELECT 1 FROM sys.indexes ind ...
2
You need to declare a table variable with the same shape as the TVF result, then use INSERT INTO not SELECT INTO, and remember to add brackets/parentheses to the end of the function name when invoking it, i.e. Foo() not just Foo:
DECLARE @Table TABLE (
[Id] [int] NULL,
[Number] [int] NOT NULL,
[Name] [nvarchar](255) NULL,
... dozens of ...
2
Deterministic encryption will produce the same value as long as the inputs are the same. In database terms it means that:
Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations).
Source
So ...
1
There are a number of "features" of merge statements that can lead to issues like this. For a relatively comprehensive list of issues take a look at the following link:
Use Caution with SQL Server's MERGE Statement
I've seen similar issues occurring myself, the solution was either to re-write the code to avoid MERGE altogether or in once case where it was ...
1
I'm not sure why you would do this, normally you have a link between tables, so it makes sense what you're joining together.
If you don't have an id on the columns, I guess your best bet would be to give them a rownumber, and use that as an Id.
-- Create table to hold first values
CREATE TABLE #randomfirsttable
(
number INT
)
-- Create ...
Only top voted, non community-wiki answers of a minimum length are eligible
