8
sp_MSforeachdb is undocumented, unsupported, and has some serious shortcomings. You'll need to use something else. Either writing your own cursor over sys.databases, something like this:
declare c cursor local for
select name
from sys.databases d
where name not in ('master','model','tempdb')
and d.state=0
declare @db sysname
open c
...
6
This
@approval = Null
never evaluates to TRUE, it evaluates to UNKNOWN. See NULL and UNKNOWN.
Should be
@approval is Null
5
You should simply download and use SQL Server 2017 developer edition. Dev edition is same as enterprise edition so regarding feature you would not have any limitation and free to use for testing and learning. Do not use it in production
4
Yes, you simply have to correlate the blocking_session_id to the session_id of the blocker. In the following output, you see that session 68 is blocked by session 66, so you simply have to find the data for session 66.
To find block leaders most quickly, be sure to use the following parameters so that the session blocking the most other sessions will be at ...
3
This
USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;
Will not perform recovery if the STOPAT time is outside the range of times in the log backup. This allows you to specify the STOPAT for multiple log backups, and the database will only run recovery when you've applied the log backup containing the target time.
...
3
You can use RIGHT either inside your CASE statement or outside it, depending on whether you want to retain the NULL value when ':' isn't found in the string.
Inside CASE
SELECT
CASE
WHEN CHARINDEX (':', Ref) > 0 THEN RIGHT('0000' + (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1)), 4)
ELSE NULL
END
FROM Data
Outside CASE
SELECT RIGHT('0000' + ...
2
does this behaviour is shared with table value function or not?
the short answer is:
It is not, because if you check what execution plans you have in the cache these are the objects you can find there:
cacheobjtype nvarchar(34) Type of object in the cache.
The value can be one of the following:
Compiled Plan
Compiled Plan Stub
Parse Tree
Extended ...
1
The smallest SQL Server you can install for development purposes is SQL Server Express LocalDb
Microsoft SQL Server Express LocalDB is a feature of SQL Server
Express targeted to developers. It is available on SQL Server Express
with Advanced Services.
LocalDB installation copies a minimal set of files necessary to start
the SQL Server Database ...
1
Following is a partial solution that will return permissions assigned to objects in a given database to groups that a user is a member of either directly or indirectly. The script loops through the groups the user is a member of, and gets all of the groups those groups are members of recursively to build a list that is used for a WHERE IN predicate.
The ...
1
I believe that the underlying data in your table has additional spaces at the end. You can verify the actual data length with a query like:
SELECT LEN(StudentName) FROM dbo.Students WHERE StudentID = 1
If it's not 4, then it's storing more than just 'Mike' and SSMS is simply preserving your data.
If you don't want the extra spaces, you can remove it from ...
1
If you are looking for a different solution, you could always unpivot the data, aggregate and then join back on the source table. Here is an example.
Also, thank you so much for including sample table and data to work with, so much easier.
;WITH CTE_Unpivot AS
(
SELECT StudentID, MarkSubject, Mark
FROM dbo.Students AS S
UNPIVOT (Mark ...
1
I am not sure that using an IsDeleted column would create unnecessary headache. Locking down the table, and allowing non-administrative action via a view which filters on that column, while not displaying it, isn't very difficult. Add an Instead Of Delete trigger to the view, and you're all set.
Given that it's essentially boiler plate once you have the ...
1
I can't get that to cost nearly that much.
On my (very fast) desktop it's under 1ms of CPU time, and on Azure SQL Database it's only 4ms.
Perhaps your SQL Server is on a VM and is not getting full access to the host's CPUs.
EG
--use tempdb
go
drop table if exists tblTask
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTask]...
1
You're looking for the PIVOT clause. You can see a working example in this db<>fiddle.
The query is:
SELECT customer_num
,p.I AS I_Branch_Code
,p.Y AS Y_Branch_Code
,p.L AS L_Branch_Code
FROM
(
SELECT customer_num
,Channel_Id
,Branch_code
FROM [table]
)s
PIVOT
(
MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L])
) p
1
The issue seems to be that SQL is treating the pseudo field from the inner SELECT as text rather than JSON when it has been unioned. When the outer FOR JSON is applied to this pseudo field, it tries to escape the special characters in the text field. See this link for more info.
You can negate this using the JSON_QUERY function, essentially forcing SQL ...
1
for selecting values based on a specific column being the same you could have a look at NTILE
Distributes the rows in an ordered partition into a specified number
of groups. The groups are numbered, starting at one. For each row,
NTILE returns the number of the group to which the row belongs.
there are actually many options, including ...
1
Avoid Row_Number because first it process whole table then again you select row
where rn=1 for each group
DECLARE @Customer_Channel_Branch TABLE (
Customer_Num VARCHAR(10)
,Channel_Num INT
,Branch_Code INT
,Issue_date INT
)
INSERT INTO @Customer_Channel_Branch
VALUES (
'x'
,1
,1000
,1990
)
,(
'x'
,2
,...
1
Here is an alternative to the answer by Artashes Khachtryan which would support delivery dates a year apart. If that is not a concern, it provides no other benefit:
SELECT OrderNumber
FROM @DeliveryTable
GROUP BY OrderNumber
HAVING COUNT(DISTINCT DATEADD(D, -DATEPART(Weekday, DeliveryDate), DeliveryDate)) > 1
1
I'm putting this here since it's a bit long, but I don't think it should qualify as an answer. There are no answers here, just observations and advice.
Short version, there isn't anything that can be done to make the query go faster and achieve the same results. You need to change the process that is feeding data into the main table if you want batching ...
1
I wouldn't consider this a hack. Windowing functions, including ROW_NUMBER are fairly efficient and generally perform equal to or better than alternatives. On new instances of SQL Server, I assume that FIRST_VALUE would be more performant, but have not specifically confirmed this.
Note: I've seen LAST_VALUE produce incorrect results, and would recommend ...
Only top voted, non community-wiki answers of a minimum length are eligible
