/*
This script estimates compression savings for all objects, indexes, and partitions in the current database.

See http://blogs.msdn.com/b/dfurman/archive/2011/02/25/estimating-data-compression-savings-for-entire-database.aspx for details.
*/

DECLARE @CompressionSavingsEstimate table
(
SchemaName sysname NOT NULL,
ObjectName sysname NOT NULL,
IndexName sysname NOT NULL,
IndexType nvarchar(60) NOT NULL,
PartitionNum int NOT NULL,
CompressionType nvarchar(10) NOT NULL,
[size_with_current_compression_setting (KB)] bigint NOT NULL,
[size_with_requested_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_current_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_requested_compression_setting (KB)] bigint NOT NULL,
PRIMARY KEY (SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType)
);
DECLARE @ProcResult table
(
[object_name] sysname NOT NULL,
[schema_name] sysname NOT NULL,
[index_id] int NOT NULL,
[partition_number] int NOT NULL,
[size_with_current_compression_setting (KB)] bigint NOT NULL,
[size_with_requested_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_current_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_requested_compression_setting (KB)] bigint NOT NULL
);
DECLARE @SchemaName sysname;
DECLARE @ObjectName sysname;
DECLARE @IndexID int;
DECLARE @IndexName sysname;
DECLARE @IndexType nvarchar(60);
DECLARE @PartitionNum int;
DECLARE @CompTypeNum tinyint;
DECLARE @CompressionType nvarchar(60);

SET NOCOUNT ON;

DECLARE CompressedIndex INSENSITIVE CURSOR FOR
SELECT s.name AS SchemaName,
       o.name AS ObjectName,
       i.index_id AS IndexID,
       COALESCE(i.name, '<HEAP>') AS IndexName,
       i.type_desc AS IndexType,
       p.partition_number AS PartitionNum
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS i
ON o.object_id = i.object_id
INNER JOIN sys.partitions AS p
ON o.object_id = p.object_id
   AND
   i.index_id = p.index_id
WHERE o.type_desc IN ('USER_TABLE','VIEW');

OPEN CompressedIndex;

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CompressedIndex 
    INTO @SchemaName, @ObjectName, @IndexID, @IndexName, @IndexType, @PartitionNum;

    IF @@FETCH_STATUS <> 0
        BREAK;

    SELECT @CompTypeNum = 0;
    WHILE @CompTypeNum <= 2
    BEGIN
        SELECT @CompressionType = CASE @CompTypeNum 
                                  WHEN 0 THEN 'NONE' 
                                  WHEN 1 THEN 'ROW'
                                  WHEN 2 THEN 'PAGE' 
                                  END;

        DELETE FROM @ProcResult;

        RAISERROR('Estimating compression savings using "%s" compression for object "%s.%s", index "%s", partition %d...', 10, 1, @CompressionType, @SchemaName, @ObjectName, @IndexName, @PartitionNum);

        INSERT INTO @ProcResult
        EXEC sp_estimate_data_compression_savings @schema_name = @SchemaName, 
                                                  @object_name = @ObjectName, 
                                                  @index_id = @IndexID, 
                                                  @partition_number = @PartitionNum, 
                                                  @data_compression = @CompressionType;

        INSERT INTO @CompressionSavingsEstimate
        (
        SchemaName,
        ObjectName,
        IndexName,
        IndexType,
        PartitionNum,
        CompressionType,
        [size_with_current_compression_setting (KB)],
        [size_with_requested_compression_setting (KB)],
        [sample_size_with_current_compression_setting (KB)],
        [sample_size_with_requested_compression_setting (KB)]
        )
        SELECT [schema_name],
               [object_name],
               @IndexName,
               @IndexType,
               [partition_number],
               @CompressionType,
               [size_with_current_compression_setting (KB)],
               [size_with_requested_compression_setting (KB)],
               [sample_size_with_current_compression_setting (KB)],
               [sample_size_with_requested_compression_setting (KB)]
        FROM @ProcResult;

        SELECT @CompTypeNum += 1;
    END;
END;

CLOSE CompressedIndex;
DEALLOCATE CompressedIndex;

SELECT SchemaName,
       ObjectName,
       IndexName,
       IndexType,
       PartitionNum,
       CompressionType,
       AVG([size_with_current_compression_setting (KB)]) AS [size_with_current_compression_setting (KB)],
       AVG([size_with_requested_compression_setting (KB)]) AS [size_with_requested_compression_setting (KB)],
       AVG([sample_size_with_current_compression_setting (KB)]) AS [sample_size_with_current_compression_setting (KB)],
       AVG([sample_size_with_requested_compression_setting (KB)]) AS [sample_size_with_requested_compression_setting (KB)]
FROM @CompressionSavingsEstimate
GROUP BY GROUPING SETS (
                       (CompressionType),
                       (SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType)
                       )
ORDER BY SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType DESC;

SET NOCOUNT OFF;

Generate dummy data and insert it into table dbo.dummydata

-- // create table to store data

drop table if exists dbo.dummydata
GO
create table dbo.dummydata
(
    Id int,
    randomdata uniqueidentifier
)

declare @cnt int = 100

while @cnt > 0 

begin 
-- // Generate 50000 random rows

    declare @datetime datetime = getdate()
    ;with randowvalues
        as(
           select 1 id, NEWID() randomnumber
            union  all
            select id + 2, NEWID() randomnumber
            from randowvalues
            where 
              id < 100000-1
          )

        insert into dbo.dummydata(Id, randomdata)
        select *
        from randowvalues
        OPTION(MAXRECURSION 0)

    set @cnt = @cnt-1

    declare @msg varchar(255)
    set     @msg = concat('duration batch: ',datediff(ms, @datetime, getdate()), ' (ms)')

    RAISERROR(@msg, 0, 1) WITH NOWAIT

end

-- create table to store the results
drop table if exists dbo.DatabaseUsages
create table dbo.DatabaseUsages
(
    DbName nvarchar(256) not null,
    LoginName nvarchar(256) not null,
    LastLoginTime datetime not null
)

-- capture the data
MERGE dbo.DatabaseUsages AS tgt
USING 
(
    SELECT 
      db_name(database_id) as [DbName]
    , original_login_name as [LoginName] 
    , MAX(login_time) AS [LastLoginTime]
    FROM sys.dm_exec_sessions as src
    where db_name(database_id) is not null
    GROUP BY db_name(database_id), original_login_name, original_login_name
) as src ON (tgt.DbName = src.DbName AND tgt.LoginName = src.LoginName)
WHEN MATCHED THEN 
    UPDATE SET tgt.LastLoginTime = src.LastLoginTime

WHEN NOT MATCHED THEN
    INSERT VALUES (DbName, LoginName, LastLoginTime);
GO

SELECT
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan,
    qsq.query_id,
    qsq.query_hash
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%%'
    and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
GO
`

The maximum values for an integer in SQL Server are:

-2147483648 through 2147483647

And the byte size is 4 bytes.

Other maximum values:

BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes)
SmallInt: -32768 through 32767 (2 bytes)
TinyInt: 0 through 255 (1 byte)

We expiranced every 5 minute a login failed error in our SQL 2016 development enviroment. I found out that the CEIP services was causing this errors.
I decided to turn this service out.

For more information about this topic see stack overlfow https://stackoverflow.com/questions/43548794/how-to-turn-off-telemetry-for-sql-2016

Get first and last day of a week and set monday as te first day of the week

SET DATEFIRST 1

declare @Week int  set @Week = 40
declare @Year int  set @Year = 2021

select dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4 -
         datepart(dw, dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4) + 1

select dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4 -
         datepart(dw, dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4) + 6