5/24/22

Finally, just minutes away the CTP2 of Microsoft SQL Server 2022 launched, get it now and try it

It is with my greatest satisfaction I proudly can introduce Microsoft SQL Server 2022, CTP2. Read more about the new version here, What's new in SQL Server 2022 - SQL Server | Microsoft Docs.

And download the Public Preview here, SQL Server 2022 Preview Trial (microsoft.com)

If you're not registered for msbuild and watching the news now, jump over here, https://mybuild.microsoft.com/en-US/home.

I'm busy watching all the announcements at the moment, more will come.


2/1/22

Really, is the backup running?

Yes, and now you launched all those backup statements, and you wonder when they are done...
This simple Query might help you a bit.

SELECT
 session_id,
 request_id,
 command,
 d.name as database_name,
 start_time,
 DATEDIFF(SECOND, start_time, GETDATE()) AS elapsed_time_in_seconds,
 percent_complete AS estimated_percent_complete,
 DATEADD(SECOND, ROUND(DATEDIFF(SECOND, start_time, GETDATE()) / percent_complete * 100.0, 0), start_time) AS estimated_finish_time
FROM sys.dm_exec_requests r INNER JOIN sys.databases d ON r.database_id = d.database_id WHERE command LIKE '%BACKUP%' OR command LIKE '%RESTORE%'

12/18/21

dbo.digits() - a sample In-line TVF using CTE

I found a question in one of the online forums where a user wanted to generate digits, like for an counter, but control how many digits and a range of allowed digits.

ex. I want 2 digits, and only the digits between 3 and 5 giving me a result like this.

digits
  33
  34
  35
  43
  44
  45
  53
  54
  55

I came up with this solution...

CREATE FUNCTION dbo.digits(
@len int,
@min int,
@max int
)
RETURNS TABLE
AS
RETURN
WITH
digits (digit) AS (
SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL
SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL
SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL
SELECT '9'
),
selection AS (
SELECT
digit
FROM digits
WHERE
CAST(digit AS int) BETWEEN @min AND @max
),
result AS (
SELECT
0 AS position,
CAST(digit as varchar(max)) digit
FROM selection
UNION ALL
SELECT
r.position + 1,
CONCAT(s.digit,r.digit)
FROM result r
CROSS JOIN selection s
WHERE r.position < @len
)
SELECT
digit AS digits
FROM result
WHERE position = @len - 1

You then execute the TVF with this line:
SELECT * FROM dbo.digits(2, 3, 5) ORDER BY 1

11/9/21

Skriptet

USE minaAdresser;
GO
CREATE SCHEMA Tabeller;
GO
CREATE SCHEMA Adresser;
GO
CREATE TABLE Tabeller.Namn
 (
  NamnID int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Tabeller_Namn
   PRIMARY KEY CLUSTERED,
  Namn nvarchar(255) NOT NULL
   CONSTRAINT uq_Tabeller_Namn_Namn
   UNIQUE
 )
CREATE TABLE Tabeller.Adresser_Adress
 (
  AdressID  int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Adresser_Adress
   PRIMARY KEY CLUSTERED,
  Förnamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Förnamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Efternamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Efternamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gata int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Gata
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gatunummer nchar(15) NOT NULL
   CONSTRAINT df_Adresser_Adress_Gatunummer
   DEFAULT (''),
  Postnummer char(6) NOT NULL
   CONSTRAINT ck_Adresser_Adress_Gatunummer
   CHECK (Postnummer LIKE '[1-9][0-9][0-9] [0-9][0-9]'),
  Ort int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Ort
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID)
 )
GO
CREATE VIEW Adresser.Adress
AS
SELECT
 a.AdressID,
 fn.Namn AS Förnamn,
 en.Namn AS Efternamn,
 g.Namn AS Gata,
 a.Gatunummer,
 a.Postnummer,
 o.Namn AS Ort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE VIEW Adresser.Utskrift
AS
SELECT
 a.AdressID,
 fn.Namn + ' ' + en.Namn    AS Mottagare,
 g.Namn + ' ' + a.Gatunummer   AS Postadress,
 a.Postnummer + ' ' + UPPER(o.Namn) AS Postort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE FUNCTION Adresser.hämtaNamn(@NamnID int)
RETURNS nvarchar(255)
AS
BEGIN
RETURN(SELECT Namn FROM Tabeller.Namn WHERE NamnID = @NamnID)
END
GO
CREATE FUNCTION Adresser.hämtaNamnID(@Namn nvarchar(255))
RETURNS int
AS
BEGIN
RETURN(SELECT NamnID FROM Tabeller.Namn WHERE Namn = @Namn)
END
GO
CREATE PROCEDURE Adresser.skapaNamn @Namn nvarchar(255), @NamnID int OUTPUT
AS
BEGIN
DECLARE @Retur int = 0
IF @Namn IS NULL
BEGIN
 SET @Namn = ''
 SET @Retur += 10
END
SET @NamnID = Adresser.hämtaNamnID(@Namn)
IF @NamnID IS NULL
BEGIN
 INSERT INTO Tabeller.Namn(Namn) VALUES(@Namn)
 SET @NamnID = SCOPE_IDENTITY()
 SET @Retur += 1
END
RETURN @Retur
END
GO
CREATE PROCEDURE Adresser.uppdateraAdress
 @Förnamn nvarchar(255),
 @Efternamn nvarchar(255),
 @Gata nvarchar(255),
 @Gatunummer nvarchar(255),
 @Postnummer nvarchar(255),
 @Ort nvarchar(255),
 @AdressID int OUTPUT,
 @updateAdressID int = -1
AS
BEGIN
DECLARE
 @Retur int = 0,
 @Exec int = 0,
 @FörnamnID int,
 @EfternamnID int,
 @GataID int,
 @OrtID int
IF @Postnummer NOT LIKE '[1-9][0-9][0-9] [0-9][0-9]' SET @Retur = -3
ELSE IF @Gatunummer IS NULL SET @Retur = -2
ELSE
BEGIN
EXEC @Exec = Adresser.skapaNamn @Förnamn, @FörnamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Efternamn, @EfternamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 1000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Gata, @GataID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 100000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Ort, @OrtID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10000000
 SET @Exec = 0
END
END
IF @Retur >= 0
BEGIN
IF @updateAdressID = -1
BEGIN
SELECT @AdressID = AdressID FROM Tabeller.Adresser_Adress
WHERE
 Förnamn = @FörnamnID
AND Efternamn = @EfternamnID
AND Gata = @GataID
AND Gatunummer = @Gatunummer
AND Postnummer = @Postnummer
AND Ort = @OrtID
IF @AdressID IS NULL
BEGIN
INSERT INTO Tabeller.Adresser_Adress(Förnamn, Efternamn, Gata, Gatunummer, Postnummer, Ort)
VALUES(@FörnamnID, @EfternamnID, @GataID, @Gatunummer, @Postnummer, @OrtID)
SET @AdressID = SCOPE_IDENTITY()
SET @Retur += 1
END
END
ELSE
BEGIN
DECLARE @updated TABLE (AdressID int)
UPDATE Tabeller.Adresser_Adress
SET
 Förnamn = @FörnamnID,
 Efternamn = @EfternamnID,
 Gata = @GataID,
 Gatunummer = @Gatunummer,
 Postnummer = @Postnummer,
 Ort = @OrtID
OUTPUT inserted.AdressID INTO @updated
WHERE AdressID = @updateAdressID
SELECT @AdressID = AdressID FROM @updated WHERE AdressID = @updateAdressID
SET @Retur += 2
END
END
RETURN @Retur
END
GO
USE master

9/16/21

Ok, just a sample PIVOT

DECLARE @DatabaseObjects TABLE
  (
    DatabaseName sysname,
    ObjectType sysname,
    ObjectName sysname
  );
DECLARE
  @DatabaseName sysname,
  @SQLStatement varchar(max),
  @ExecuteStatement varchar(max),
  @Columns varchar(max);

SET NOCOUNT ON;
SET @SQLStatement = 'SELECT ''{DatabaseName}'' AS [DatabaseName], [type_desc] AS [ObjectType], [name] AS [ObjectName] FROM [{DatabaseName}].[sys].[objects];';
DECLARE
  DatabaseCrawler CURSOR
  FOR SELECT Name FROM sys.Databases ORDER BY Name ASC;
OPEN DatabaseCrawler;
FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @ExecuteStatement = REPLACE(@SQLStatement, '{DatabaseName}', @DatabaseName);
  INSERT INTO @DatabaseObjects
  EXEC (@ExecuteStatement);
  FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
END;
CLOSE DatabaseCrawler;
DEALLOCATE DatabaseCrawler;
SET NOCOUNT OFF;

SELECT * INTO #DatabaseObjects FROM @DatabaseObjects

SELECT @Columns = ISNULL(@Columns, '') + '[' + [Column] + '],' FROM (SELECT DISTINCT ObjectType AS [Column] FROM @DatabaseObjects) Columns

SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

SET @SQLStatement = 'SELECT DatabaseName, {Columns} FROM #DatabaseObjects PIVOT (Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt'

SET @ExecuteStatement = REPLACE(@SQLStatement, '{Columns}', @Columns);

EXEC (@ExecuteStatement)
DROP TABLE #DatabaseObjects

8/29/21

By request

DECLARE @start datetime, @numberofyears int
SET @start = '1900-01-01'
SET @numberofyears = 180
;WITH a1(col)
AS (
SELECT NULL
UNION ALL
SELECT NULL
), a2(col)
AS (
SELECT NULL FROM a1 x1 CROSS JOIN a1 x2
), a3(col)
AS (
SELECT NULL FROM a2 x1 CROSS JOIN a2 x2
), a4(col)
AS (
SELECT NULL FROM a3 x1 CROSS JOIN a3 x2
), a5(col)
AS (
SELECT NULL FROM a4 x1 CROSS JOIN a4 x2
), nums(num)
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM a5
), dates(datetime)
AS (
SELECT DATEADD(day, num, @start) FROM nums WHERE DATEADD(day, num, @start) < DATEADD(year, @numberofyears, @start)
)
SELECT YEAR(datetime) * 10000 + MONTH(datetime) * 100 + DAY(datetime) AS DATEKEY, datetime FROM dates

8/18/21

If you don't like your query plan!

In one of the cool channels us SQLMVPs communicate through the question was raised about why do the execution plan do what they do, and we started to elaborate about tweaks and twerks, and apply hinting in one way or the other. Sometimes you need to do these ninja moves, but usually it's because your statistics are skewed and/or your cached query plan is to old and the data has changed to much. This is usually fixed by updating the statistics or rebuild/reorganize your indexes and updating the statistics. It might be that you need to revamp your indexes too. Maybe wrong cluster key or you need a non-clustered index and include some columns. This if the query you're trying to run is a common query or not. Sometimes even a filtered index might be needed, or sometimes partitioning can be of help. Major changes should only be done if this is a common and re-occuring query.

I'd like to end this post with a lovely quote:

I encourage you to consider updating statistics rather than trying syntactic rewrites. Such things worked on rule-based optimizers but it is a goal of the SQL Server QP to avoid these kinds of gymnastics to the extent possible. Focus on the plans that come out and whether the optimizer has the right info to get you the plan shape you think is best more than the syntax. -Conor Cunningham