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
Time Shifts , how to set it to the max value in the interval.
For example , now it’s 10:39, and the value of our select should be 15:00
S
I have an xml file/variable with multiple namespaces.
How do you parse it?
Here is the script:
and the output
S
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
Every sql server developer/dba should have some “utility” scripts, for looking at particular point.
One of this script is about index usage , that is looking to a table and get information about index, how they are used, statistics …
set nocount on
DECLARE
@objname nvarchar(128);
select OBJECT_NAME(i.object_id) as tblName
,i.name
,i.is_disabled as esteDezactivat
,STUFF(ca.IndexColumnsNames,1,1,'') as IndexColumns
,STUFF(ia.IncludedColumnsNames,1,1,'') as IncludedColumns
,STATS_DATE(ius.object_id, i.index_id) as Stat_Date
,ps.reserved_page_count as noPages
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,ius.last_user_seek
,ius.last_user_scan
--,i.*
,case when i.index_id =1 then 1 else 0 end as is_clustered
,i.is_unique
,i.is_primary_key
,i.is_unique_constraint
,i.is_disabled
,i.has_filter
,i.filter_definition
,ps.data_compression_desc
,'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + '.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ' DISABLE;' as DisableStmt
,'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + '.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ';' as dropStmt
from
sys.objects as o
left join sys.indexes as i
on o.object_id = i.object_id
left join sys.dm_db_index_usage_stats as ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
AND ius.database_id = DB_ID(DB_Name())
cross apply
(select ', '+ c.name +''
from sys.index_columns as ic
inner join sys.columns as c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.is_included_column = 0
order by ic.key_ordinal
for xml path('')
)ca(IndexColumnsNames)
outer apply
(select ', '+ c.name +''
from sys.index_columns as ic
inner join sys.columns as c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.object_id = i.object_id
and ic.index_id = i.index_id
and ic.is_included_column = 1
order by ic.index_column_id ASC
for xml path('')
)ia(IncludedColumnsNames)
left join (select
ps.object_id
,ps.index_id
, sum(ps.reserved_page_count) as reserved_page_count
,p.data_compression_desc as data_compression_desc
from sys.dm_db_partition_stats as ps
inner join sys.partitions as p
ON ps.partition_id = p.partition_id
AND ps.object_id = p.object_id
group by ps.object_id,ps.index_id,p.data_compression_desc
)ps
on ps.object_id = i.object_id
and ps.index_id = i.index_id
where
1=1
--ius.user_seeks = 0
--and ius.user_scans = 0
--and ius.user_lookups = 0
--and i.type > 1 -- nonclustered index
--and i.is_unique = 0 -- is not unique
--and i.is_primary_key = 0 -- not PK
--and i.is_unique_constraint = 0 --not Unique Constraint
/********and i.is_disabled = 0 -- is active*******/
--and i.has_filter = 0
--and database_id = DB_ID(DB_Name()) -- database actuala
and OBJECT_NAME(o.object_id) = @objname
order by OBJECT_NAME(ius.object_id),IndexColumns,IncludedColumns, QUOTENAME(i.name);
Thanks,
S
I was looking a certain post/question , especially for the answers . And there were like two answers from which I was enhance my knowledge.
And after more than 7 days, the post was gone: “This question was voluntarily removed by its author.”
Should StackExchange allow a post to be deleted by its author? (If it has answers and there are more than 2-3 days from creation/post date).
Some people have spent some time from their free time to answers that question……
I mean, ok you can delete it if no answers and/or are in an interval of 2-3 days from posting date
S
Different test cases:
1 supply , no consumption
2. 1 supply , 1 consumption equal
3. 1 supply , 1 consumption, (partial)
4 1 supply, 2,3 consumptions (partial)
5. 1 suoply , 2,3 consumption , no stock
6, 2,3 supplies , 1 consumption , 0 stock
7 2,3 supplies, 2,3 consumption, with stock
8 2,3 supplies, 2,3 items, 2,3 consumptions
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
Sql Server 