t-sql
You know the feeling, when you receive a stored procedure/query to make it better/faster
and after 20-30 minutes of searching/working you where able to run it under 1 sec ?
Today it happened to me too.
One stored procedure that had inside a function with lots a joins (15 aprx) …
some rewrites , some changes, some index and of course , the classic :
BR
S
I wanted to test the updlock hint , inside a transaction, for how long it’s keep.
(From what I remember, it’s kept till the end of the transaction.)
So, I decided to test it.
use StackOverflow2013
go
set nocount on
go
begin tran
select *
from dbo.Users with(updlock)
where id = 9567299;
select *
from dbo.Posts with(updlock)
where id = 17;
waitFor delay '00:00:10';
rollback tran
At some point, let’s add a RAISERROR(‘interior1 ‘,0,1) with NOWAIT;
use StackOverflow2013
go
set nocount on
go
begin tran
select *
from dbo.Users with(updlock)
where id = 9567299;
select *
from dbo.Posts with(updlock)
where id = 17;
RAISERROR('interior1 ',0,1) with NOWAIT;
waitFor delay '00:00:10';
rollback tran
Interesting point, is that with out RaisError, the results are display after the end of delay
But with raiserror with nowait, the result are display right away.
NOWAIT | Sends messages immediately to the client. |
S
the same , but with date/datetime value
Given a datetime value (a field that is of type datetime), to display only hour : minutes , with out seconds and date.
DECLARE
@dt_MyDate datetime = ‘20230825 06:05:45’;SELECT
FORMAT(@dt_MyDate, N’HH:mm’) as [HH:mm];
/* HH:mm in 24h format
hh:mm tt in AM PM format
*/SELECT
CONVERT(char(5), @dt_MyDate, 108) as [HH:mm];
S
Now , we have the years in a interval table, the raw table prepared in a source table.
Next we have to place a record in the right interval.
A record is in the X interval if:
So , the final select will look like this:
So.for this sample, the output will look like this:
S
How to …
See here one way to unpivot, thanks to Michael J Swart, let say, a generic way to unpivot.
And in comments, it’s a way that I like, with the help of a JSON.
It’s a combination between FOR JSON cu OPENJSON.
FOR JSON will give, let say, a string/array/combination between columns and values;
and OPENJSON will do the magic, will return rows having the desired output, having the unpivot result set.
FOR JSON AUTO , WITHOUT_ARRAY_WRAPPER , INCLUDE_NULL_VALUES
+ OPENJSON
S
AKA sababa
Usually, in our system, we use , for our primary key some kind of notation/coding for it, for example : xxxID.
So, given 3 liters + ‘ID’ , return the name of the table(s) , from each database existing in the instance.
For this, I have to search in each database, and I use some code from Aaron Bertrand , credit to him for this . link1 , link2.
CREATE
OR
ALTER PROCEDURE [dbo].[sp_PrimaryKeySearch] @pkColName nvarchar(128)
AS
BEGIN
DECLARE @nvc_SQLTEXT nvarchar(2000) =N'
IF EXISTS(SELECT *
FROM sys.key_constraints as kc
INNER JOIN sys.columns as c
ON c.object_id = kc.parent_object_id
WHERE kc.type =''PK'' AND c.name = '''+ @pkColName +''')
BEGIN
SELECT DB_NAME() as dbName, STRING_AGG(PKnUTable.name,'','') as lstTabele
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
WHERE PKnUKEY.type = ''PK''
AND PKnUKEYCol.name = '''+ @pkColName +'''
END
'; /*PRINT @nvc_SQLText;*/
EXEC sp_inEachDB @nvc_SQLTEXT
END
And add this code to the SSMS , to query shortcuts…and done.
S
There is this query in sql server 2019:
select
rezProcesat
from dbo.tblRezervari
where 1=1
and rezProcesat is null
and rezUserName= CAST(SUSER_SNAME() as varchar(256))
What will be the estimate for it?
Let’s take each predicate and find it estimate.
For rezProcesat is null, the estimate is 19140. For this, the is a filtered index on this predicate that have rows matching predicate=18204,unfiltered_rows=17593170
select rows,unfiltered_rows,rows*1.0/unfiltered_rows as Selectivity1 ,(rows*1.0/unfiltered_rows)*18498100 as EstimatePredicate1 from sys.dm_db_stats_properties_internal(object_id(‘dbo.tblFluxRezervari’),13);
| rows | unfiltered_rows | Selectivity1 | EstimatePredicate1 |
| 18204 | 17593170 | 0.00103471972362001 | 19140.34891950 |
For the second predicate : rezUserName= CAST(SUSER_SNAME() as varchar(256)), we have to look at the statistics information.
DBCC SHOW_STATISTICS (‘dbo.tblRezervari’,rezUserName) wITH DENSITY_VECTOR;
| All density | Average Length | Columns |
| 0.009708738 | 9.014213 | rezUserName |
select 18498100 * 0.009708738 as EstimatePredicate2
EstimatePredicate2 : 179593.206397800
And putting all together, we get:
EstimatePredicate1 = 19140; SelectivityPredicate1 = 0.00103472
EstimatePredicate2 = 179594; SelectivityPredicate2 = 0.009708738
select 18498100 * (0.00103472*0.009708738)
185.82868252393161600
S
Image that you have a folder with some sql scripts and you need to run them. And the result to be saved , to a csv file.
and if a script is marked somehow, in this case with "_1" like xxxx_1, this script should be executed for each database in that server…
<#
run scripts
to a server
to a database
from a folder
To DO
pentru anumite scripturi, care au , de exemplu, in numele lor, la final ceva , de ex: _1 $string.SubString($string.Length-2)
pt ele ar trebuie sa trecem prin toate bazele de date existente pe server
$string.SubString($string.Length-2)
#>
Add-PSSnapin SqlServerCmdletSnapin100 # here live Invoke-SqlCmd
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
clear
$server = ‘SQLSERVERDEV2019′
$db =’master’
$filename = ”
#unde sunt scripturile care treb rulate
$SourceLocation = ‘E:\Documents\SQL Server Management Studio\Projects\Work\scripts_DBA\scripts2′
#unde exportam rezultatele
$FolderPathExport =’E:\Documents\SQL Server Management Studio\Projects\Work\scripts_DBA\output’
$NewOutputFolder = $FolderPathExport + "\" + $Server.replace(‘\’,’_’) + "_" + $(get-date -f yyyy_MM_dd)
#daca folderul exista sau nu, atunci il cream
#si daca exista ii punem si ora_minutele_sec
IF(!(Test-Path -path $NewOutputFolder))
{
New-Item -ItemType Directory -Force -Path $NewOutputFolder
}
ELSE
{
$NewOutputFolder =$NewOutputFolder +’__’ + $(get-date -f hh_mm_ss)
New-Item -ItemType Directory -Force -Path $NewOutputFolder
}
<# vedem ce baze de date sunt pe server #>
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server
$dbs=$s.Databases
#####
$listOfFiles = Get-ChildItem $SourceLocation
$listOfFiles | forEach-object {
#setam numele fisierul care reprezinta sursa pt rulare script
$fileNameInput = $_.FullName
#setam numele fisierului pt export
$fileName =”
$fileName = $NewOutputFolder+ "\" + $_.BaseName.replace(‘ ‘,’_’)
IF( $_.BaseName.SubString($_.BaseName.Length-2) -eq’_1′)
{
#daca avem -1 in coada, atunci trecem prin toate bazele de date
$dbs | ForEach-Object {
$fileNameInterior = $fileName + "_" + $_.Name + "_" + $(get-date -f yyyy_MM_dd) + ".csv"
Invoke-sqlcmd -ServerInstance $server -Querytimeout 600 -Database $db -InputFile $fileNameInput| Export-Csv -Path $fileNameInterior -NoTypeInformation
}
}
ELSE
{
#buna , dar este inlocuita cu $fileName = $($FolderPathExport + "\" + $Server.replace(‘\’,’_’) ) + "_" + $_.BaseName.replace(‘ ‘,’_’) + "_" + $(get-date -f yyyy_MM_dd) + ".csv"
$fileName = $fileName + "_" + $(get-date -f yyyy_MM_dd) + ".csv"
#$fileName
Invoke-sqlcmd -ServerInstance $server -Querytimeout 600 -Database $db -InputFile $_.FullName| Export-Csv -Path $fileName -NoTypeInformation
}
}
Another solution/idea for islands and gaps
Sql Server 