String_Split and String_Agg
Posted: November 25, 2019 Filed under: Uncategorized Leave a comment
create table test (col varchar(100))
insert into test values
('1-5, 7-10,12-14')
,('9-10')
--**** create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
,mycte as (
select col, d.value grp, e.value from test
cross apply string_split(col,',') d
cross apply string_split(d.value,'-') e
)
,mycte2 as (
select col, n
from mycte
cross apply (select n from nums ) d
where n>=parsename(replace(grp,'-','.'),2)
and n<=parsename(replace(grp,'-','.'),1)
group by col,n
)
--SQL Server 2017,2019
Select STRING_AGG (n,',') within group (order by n)
from mycte2
group by col
/*
SELECT --t1.Col,
Stuff(( SELECT ',' + Cast(t2.n as varchar(5))
FROM mycte2 t2
WHERE t2.Col = t1.Col
ORDER BY n
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS newCol
FROM mycte2 t1
GROUP BY t1.col;
*/
drop table test
Dynamic Case Pivot For Monthly Aggregated Data
Posted: November 15, 2019 Filed under: Uncategorized Leave a comment
Declare @FromDate date='2018-01-16'
Declare @ToDate date='2018-05-30'
create table #Main (ID varchar(100),Date_ date,Amount Decimal(18,2))
insert into #Main(ID,Date_,Amount) values ('1001','2018-01-01',1500.00)
insert into #Main(ID,Date_,Amount) values ('1001','2018-03-05',100.00)
insert into #Main(ID,Date_,Amount) values ('1001','2018-05-14',2300.00)
insert into #Main(ID,Date_,Amount) values ('1001','2018-07-11',1025.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-01-18',500.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-02-04',700.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-05-26',3000.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-11-11',355.00)
Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
--**** create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
,alldates as (
select n, dateadd(month,n-1,@FromDate) dt from Nums
WHERE dateadd(month,n-1,@FromDate)='''+Convert(varchar(10),@FromDate ,112)+''' and Date_<= '''+Convert(varchar(10),@ToDate ,112)
+''' ) t Group by grouping sets(ID,()) ';
--print @sql
EXEC(@SQL)
drop table #Main
Dynamic Pivot With Totals
Posted: November 15, 2019 Filed under: Uncategorized Leave a comment
CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');
INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);
INSERT INTO #Probale VALUES
(1,1,1,001,100,'01-06-2019',null)
, (2,3,1,001,200,'02-06-2019',null)
, (3,11,1,002,200,'03-06-2019',null)
, (4,10,1,002,200,'08-06-2019',null)
, (4,1,1,003,200,'08-06-2019',null)
, (4,3,1,003,200,'08-06-2019',null);
INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');
INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);
declare @fromdate date='2019-01-06'
declare @todate date='2019-08-06'
declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
Declare @sqlPivot as NVarchar(4000)
;with mycte as (
select a.CodeItem,a.Descriptionitem ,
row_number() Over(Partition by a.CodeItem,a.Descriptionitem Order by Entrydate) rn
,sum(case when b.CodeItem is null then 0 else 1 end )over(partition by a.CodeItem,Entrydate) cnt,
Entrydate
from #ItemMasterFile a left join #Probale b on a.CodeItem=b.CodeItem
and Entrydate>=@fromdate
and Entrydate=@fromdate and [Process_Date]=@fromdate and [Process_Date]<=@todate) t
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
set @sqlPivot=' Select
Case when t.[Dep_Name] is null then ''Total'' else t.[Dep_Name] end [Dep_Name]
,'+ @ColumnHeaders + ','+@ColumnHeaders2+' as [Total Qty]
FROM [Temp1] t
Group by GROUPING SETS ((t.[Dep_Name]),())
'
--print @ColumnHeaders2
EXEC sp_executesql @sqlPivot;
--clean up
drop table [dbo].[Temp1]
Uninstall SQL Server 2019 CTP with Command
Posted: November 15, 2019 Filed under: Uncategorized Leave a commentTried to uninstall SQL server 2019 CTP version from a server with GUI uninstall program but it was hanging there.
Came across a method Aaron Bertrand posted with built-in command called msiexec.
Use the powershell script from Aaron’s post:
$a = c:\temp\msiinv.exe -s | Select-String -Pattern "SQL Server" -Context 1,1; $a = $a -replace "Product code: ","msiexec /x """; $a = $a -replace ">", "rem"; $a = $a -replace "\t", ""; $a = $a -replace "}","}"""; $a | Out-File c:\temp\remove.bat -encoding ascii;
Generate a remove.bat file with all server components needs to be uninstalled.
Run the bat file from command line with alleviated rights (admin), and a few confirmation clicks and all SQL Server components selected are gone.
https://www.mssqltips.com/sqlservertip/4050/cleanly-uninstalling-stubborn-sql-server-components/
Script New Objects for User Databases Using Powershell
Posted: November 11, 2019 Filed under: Uncategorized 1 CommentRun into a question how to script newly created objects of user databases with a datetime filter,
The key part is the following:
$limit = (Get-Date).AddDays(-7) # past 7 days
#....
foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ( ([datetime]$_.createdate).Date -ge $limit ) })
The whole code section:
$date_ = (date -f yyyyMMdd)
$currentDate = Get-Date
$ServerName = "MC\SQLDEV2017Instance"
$path = "c:\temp\"+"$date_"
$limit = (Get-Date).AddDays(-7)
#foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ( ([datetime]$_.createdate).Date -gt $date_ ) } )
# foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ($_.createdate | Where-object {$_.Date -ge $date_ } )
#[datetime]::parseexact($date_ , 'yyyyMMdd', $null)
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup.
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')
$dbs=$serverInstance.Databases | Where-Object {!($_.Name -in ("master","model","msdb","ReportServer","ReportServerTempDB","tempdb"))} #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
$dbname = "$db".replace("[","").replace("]","")
$dbpath = "$path"+ "\"+"$dbname" + "\"
if ( !(Test-Path $dbpath))
{$null=new-item -type directory -name "$dbname"-path "$path"}
foreach ($Type in $IncludeTypes)
{
$objpath = "$dbpath" + "$Type" + "\"
if ( !(Test-Path $objpath))
{$null=new-item -type directory -name "$Type"-path "$dbpath"}
# foreach ($objs in $db.$Type | where {!($_.IsSystemObject)})
foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ( ([datetime]$_.createdate).Date -ge $limit ) })
{
If ($ExcludeSchemas -notcontains $objs.Schema )
{
$ObjName = "$objs".replace("[","").replace("]","")
$OutFile = "$objpath" + "$ObjName" + ".sql"
$objs.Script($so)+"GO" | out-File $OutFile
}
}
}
}
The original question:
First Object_id in SQL Server 2019
Posted: November 5, 2019 Filed under: Uncategorized Leave a commentWhen you create a new object in a new database in SQL Server 2019, the object_id of the first user object’s id is 581577110 in SQL Server 2019 and the next one is 597577167 with the increment of 16000057 (magic number, a prime number). When the new object_id if larger then 2,147,483,647 (int maximum), it will start from lower range (close to 0 ) and repeat this kind of cycle.
It has the same pattern as before (undocumented) and it will a huge capacity to hold all objects with unique numbers. I even tried to hit the limit on my computer but without success.
It will safe to assume it will work for most cases without issues to use database with many objects. When you drop a table and recreate it, it will have a different object_id.
But anyway, we nerve need the number in our code explicitly and use it only as reference to link objects together to get our query working for object information.
SQL Server 2019 Released (2019-11-04)
Posted: November 5, 2019 Filed under: Uncategorized Leave a commentWe are ready to set up new environments with SQL Server 2019 RTM version.
We have tried the CTP versions for sometime and it is time to work this new version of SQL Server.
SQL versions reference:
Microsoft SQL Server 2019 Versions
https://sqlserverbuilds.blogspot.com/2019/01/sql-server-2019-versions.html
Jingyang Li 