Transact SQL – STRING_AGG

Background

In our post on MySQL – Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function.

I really could not find a way around using it in MySQL.

I ran into some difficulties using it and as with problems one just googles for workarounds.

BTW, the MySQL post is here.

SQL Server

Introduction

It seems that in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

SQL Server – Identify Objects with dependencies outside of current database

Background

Needed a quick way to identify referenced objects that are not contained in my current database either to having being dropped, located in a different database, or located on a linked server.

 

DevioBlog

DevioBlog has a good concise query.

A write-up is available here.

And, here it is in it’s entirety.


select o.name, d.referenced_entity_name, *
from sys.sql_expression_dependencies  d

inner join sys.objects o 
       on d.referencing_id = o.object_id

where referenced_id is null


 

Code

 


/*

    sys.sql_expression_dependencies (Transact-SQL)
    v2008
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017

    ID of the referenced entity. 


    a) The value of this column is never NULL for schema-bound references. 

    b) The value of this column is always NULL for cross-server and cross-database references.

    c) NULL for references within the database if the ID cannot be determined. 

        Objects dropped

    
    d) For non-schema-bound references, the ID cannot be resolved in the following cases:

        The referenced entity does not exist in the database.

*/
select 
          
        [object]
        = quoteName
            (
                isNull
                (
                    tblSS.[name]
                    , ''
                )
            )

            + '.'

            + quoteName
            (
                isNull
                (
                    tblSO.name
                    , ''
                )
            )

        , [objectType]
            = tblSO.[type_desc]

        , [referencedDatabase]
            = tblSED.referenced_database_name

        , [referencedObject]
            = quoteName
                (
                    isNull
                        (
                            tblSED.referenced_schema_name
                            , ''
                        )
                )
                + '.'
                + quoteName
                    (
                        isNull(tblSED.referenced_entity_name, '')
                    )

        , [classofReferencedObject]
            = tblSED.referenced_class_desc

from sys.objects tblSO

inner join sys.schemas tblSS

        on tblSO.schema_id = tblSS.schema_id

inner join sys.sql_expression_dependencies  tblSED

        on tblSO.object_id = tblSED.referencing_id

where (

        (
            ( tblSED.[referenced_id] is null )
        )

    )

Summary

Thank goodness information about referenced objects are cataloged in the sys.sql_expression_dependencies system table.

If the referenced object is not in the contextual database, the referenced_id is logged as null.

Also as part of clean-up effort the referenced_id column is nulled out whenever an object is dropped.

 

SQL Server – Create Database from existing files

Background

Hard crash from running out of storage and now it is time to get a new LUN/Volume up.

 

Outline

  1. Get database filenames of database to be moved
    • sys.master_files ( Master Database )
    • sys.database_files ( Contextual Database )
  2. Take database offfline
  3. Re-attach database files
    • Create database for attach

 

Processing

Get database filenames of database to be moved

sys.master_files

SQL

set nocount on;
go

declare @database sysname
declare @dbid     int
declare @sqlFileName  nvarchar(max)
declare @sql  nvarchar(max)

declare @CHAR_TAB           char(1)
declare @CHAR_COMMA         char(1)
declare @CHAR_CRLF          char(2)
declare @CHAR_SINGLEQUOTES  char(1)

set @CHAR_TAB = char(9)
set @CHAR_COMMA = ','
set @CHAR_CRLF = char(13) + char(10)
set @CHAR_SINGLEQUOTES = ''''

/*	
	Specify database name
*/
set @database = 'hrdb'

/*	
	Get database ID
*/
set @dbid = db_id(@database)

select 
        @sqlFileName = 
            isNull(@sqlFileName, '')
            + @CHAR_TAB
            + case
                when tblSMF.file_id = 1 then ' '
                else @CHAR_COMMA
              end   
            + '(FILENAME = '
            + @CHAR_SINGLEQUOTES
            + tblSMF.physical_name
            + @CHAR_SINGLEQUOTES
            + ')'
            + @CHAR_CRLF

from   sys.master_files tblSMF

where  tblSMF.database_id = @dbid

set @sql = 'CREATE DATABASE '
            + quoteName(@database)
            + @CHAR_CRLF
            + ' ON '
            + @CHAR_CRLF
            + @sqlFileName
            + ' FOR ATTACH '
            + @CHAR_CRLF

print @sql

Output



CREATE DATABASE [hrdb]
 ON 
	 (FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb.mdf') --1
	,(FILENAME = 'Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\hrdb_8.ldf') --2
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_0.ndf') --3
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_1.ndf') --4
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_2.ndf') --5
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_3.ndf') --6
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_4.ndf') --7
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_5.ndf') --8
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_6.ndf') --9
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_7.ndf') --10
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdbLOB_01.ndf') --11
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_index_01.ndf') --12
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_9.memopt') --65537
 FOR ATTACH 

sys.database_files

SQL


set nocount on;
go

use [hrdb]
go

declare @database sysname
declare @dbid     int
declare @sqlFileName  nvarchar(max)
declare @sql  nvarchar(max)

declare @CHAR_TAB           char(1)
declare @CHAR_COMMA         char(1)
declare @CHAR_CRLF          char(2)
declare @CHAR_SINGLEQUOTES  char(1)

set @CHAR_TAB = char(9)
set @CHAR_COMMA = ','
set @CHAR_CRLF = char(13) + char(10)
set @CHAR_SINGLEQUOTES = ''''

set @database = db_name()
set @dbid = db_id()

select 
        @sqlFileName = 
            isNull(@sqlFileName, '')
            + @CHAR_TAB
            + case
                when tblSDF.file_id = 1 then ' '
                else @CHAR_COMMA
              end   
            + '(FILENAME = '
            + @CHAR_SINGLEQUOTES
            + tblSDF.physical_name
            + @CHAR_SINGLEQUOTES
            + ')'
            + @CHAR_CRLF

from   sys.database_files tblSDF

set @sql = 'CREATE DATABASE '
            + quoteName(@database)
            + @CHAR_CRLF
            + ' ON '
            + @CHAR_CRLF
            + @sqlFileName
            --+ @CHAR_CRLF
            + ' FOR ATTACH '
            + @CHAR_CRLF

print @sql

Output


CREATE DATABASE [hrdb]
 ON 
	 (FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb.mdf') --1
	,(FILENAME = 'Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\hrdb_8.ldf') --2
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_0.ndf') --3
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_1.ndf') --4
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_2.ndf') --5
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_3.ndf') --6
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_4.ndf') --7
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_5.ndf') --8
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_6.ndf') --9
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_7.ndf') --10
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdbLOB_01.ndf') --11
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_index_01.ndf') --12
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_9.memopt') --65537
 FOR ATTACH 

Take Database Offline

sp_detach_db

SQL


USE [master]
GO
ALTER DATABASE [hrdb] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'hrdb'
GO


SQL Server – Default Constraints & Explicit Values

Background

Constraints can be a powerful tool in enforcing data rules per SQL Server.

 

Constraints

Here are some of the rules:

  1. Not Null
    • Rule
      • Ensure that a column’s value can not be null
    • Boundary
      • Single Column
    • Validated
      • Validated during target’s table data insert and update
  2. Unique
    • Rule
      • Ensure that the values on one or more column has to be unique
      • No other Row can have same exact values
    • Boundary
      • One or more columns
    • Validated
      • Validated during target’s table data insert and update
  3. Primary Key
    • Rule
      • Ensure that the values on one or more column has to be unique
      • No other Row can have same exact values
    • Boundary
      • One or more columns
      • None of the Columns can be defined as null
      • Only a single primary key can be defined on a table
    • Validated
      • Validated during target’s table data insert and update
  4. Foreign Key
    • Rule
      • Ensure that co-related values exists in the referenced table
    • Boundary
      • One or more columns
    • Validated
      • Validated during target’s table data insert and update
      • Also, validated during referenced table update and deletes

 

 

Constraint – Not Null

Let us talk about the Not Null constraint.

Default Constraint

Definition

Column Definition

A default constraint can be defined during table declaration

SQL
declare @tblConstraintDefault TABLE
(
	  [id] int not null	identity(1,1)

	, [dob] datetime not null
		default '1900-01-01'

	, [dtInsert] datetime
               default getdate()
)

 

Table Constraint

Default Constraints can be defined post table creation

SQL

use [tempdb]
go

set xact_abort on
set nocount on
go

/*
	Drop Table if it exists
*/
if object_id('[tempdb]..#person') is not null
begin
	drop table #person
end
go

/*
	Create Table
*/
create table #person
(
	  [id] int not null	identity(1,1)

	, [dob] datetime not null
		--default '1900-01-01'

	, [dtInsert] datetime
)
go

/*
	add constaint post table declaration
*/
alter table #person
	add default
		'1900-01-01' 
		for [dob]


if object_id('[tempdb]..#person') is not null
begin

	drop table #person

end
go

 

Adding data

Adding data specify value

SQL

insert into @tblConstraintDefault
([dob])
select getdate()

 

Adding data default values

SQL

insert into @tblConstraintDefault
default values

 

Adding data – Explicitly specify null for dob

SQL
insert into @tblConstraintDefault
([dob])
select null
Error
Error – Textual
Msg 515, Level 16, State 2, Line 40
Cannot insert the value NULL into column 'dob', table '@tblConstraintDefault'; column does not allow nulls. INSERT fails.

Summary

Moral of the story.

If you are expecting default constraints to help or save you, make sure that you have not passed in column and value for not null column.

sp_help_revlogin

Background

Restored a database and after running sp_change_users_login, found that there were sql logins that are were still misaligned.

Knew that I needed to copy the logins over, as well.

 

SSIS

There are some SSIS tasks that helps.

 

Legacy

But , why go new school when old school holds.

 

sp_help_revlogin

  1. MSFT
    • How to transfer logins and passwords between instances of SQL Server
      Link

Revision

Here is a revised code

Outline

  1. Added argument
    • @skipSystemAccounts
      • Ability to skip system accounts such as
        • Policy Accounts
          • ##MS_PolicyEventProcessingLogin##
          • ##MS_PolicyTsqlExecutionLogin##
        • NT AUTHORITY\SYSTEM
        • NT SERVICE%
    • Issue suser_id to check for account prior to creation

Code


/*

    How to transfer logins and passwords between instances of SQL Server
    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

*/

USE [master]
GO

IF OBJECT_ID ('sp_hexadecimal') IS NULL
begin

  exec('create procedure [dbo].[sp_hexadecimal] as ')

end
GO

ALTER PROCEDURE [dbo].[sp_hexadecimal]
      @binvalue varbinary(256)
   ,  @hexvalue varchar (514) OUTPUT
AS
begin

    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    
    
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    
    WHILE (@i <= @length)
    BEGIN

      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue 
                =   @charvalue 
                  + SUBSTRING(@hexstring, @firstint+1, 1)
                  + SUBSTRING(@hexstring, @secondint+1, 1)
    
      SELECT @i = @i + 1
    
    END

    SELECT @hexvalue = @charvalue

end

GO
                
 
IF OBJECT_ID ('sp_help_revlogin') IS NULL
begin

  exec('create PROCEDURE [dbo].[sp_help_revlogin] as ')

end
GO

ALTER PROCEDURE [dbo].[sp_help_revlogin]
(
       @loginName           sysname = NULL 
     , @skipSystemAccounts  bit = 0
)
AS
begin

    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary  varbinary (256)
    DECLARE @PWD_string  varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr  varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname
 
    DECLARE @STRING_EXISTENCE_CHECK_PREFIX  varchar(120)
    DECLARE @STRING_BEGIN                   varchar(120)
    DECLARE @STRING_END                     varchar(120)

    declare @CHAR_CRLF                      varchar(30)

    declare @tsql                           nvarchar(4000)

    set @STRING_EXISTENCE_CHECK_PREFIX = 'if suser_id(''%s'') is null '  
    set @STRING_BEGIN = ' begin '
    set @STRING_END = ' end '
     
    set @CHAR_CRLF  = char(13) + char(10);
     
    DECLARE login_curs CURSOR FOR

          SELECT 
                  p.sid
                , p.name
                , p.type
                , p.is_disabled
                , p.default_database_name
                , l.hasaccess
                , l.denylogin 
        
        FROM sys.server_principals p 
    
        LEFT JOIN sys.syslogins l
            ON ( p.[name] = l.[name] ) 
    
        WHERE p.[type] IN ( 'S', 'G', 'U' ) 
    
        AND   p.[name] <> 'sa'
    
        AND   p.[name] = isNull(@loginName, p.[name])

        AND  (

                  (@skipSystemAccounts = 0)

                or
                (

                        ( @skipSystemAccounts = 1 )

                    --skip NT SERVICE
                    and (

                            ( 
                                p.[name] not in 
                                    ( 
                                          '##MS_PolicyEventProcessingLogin##'
                                        , '##MS_PolicyTsqlExecutionLogin##'
                                    )
                            )
                    
                            and
                            ( 
                                p.[name] not in 
                                    ( 
                                        'NT AUTHORITY\SYSTEM'
                                    )
                            )

                            and ( p.[name] not like 'NT SERVICE%')
                        )

                )

            )

        order by
                p.name

    OPEN login_curs

    FETCH NEXT FROM login_curs 
    INTO 
              @SID_varbinary
            , @name
            , @type
            , @is_disabled
            , @defaultdb
            , @hasaccess
            , @denylogin

    IF (@@fetch_status = -1)
    BEGIN

      PRINT 'No login(s) found.'
      CLOSE login_curs
      DEALLOCATE login_curs
      RETURN -1
    
    END
    
    SET @tmpstr = '/* sp_help_revlogin script '
    
    PRINT @tmpstr
    
    SET @tmpstr = '** Generated ' 
                    + CONVERT (varchar, GETDATE()) 
                    + ' on ' 
                    + @@SERVERNAME 
                    + ' */'
    
    PRINT @tmpstr
    
    PRINT ''
    
    WHILE (@@fetch_status <> -1)
    BEGIN
    
      IF (@@fetch_status <> -2)
      BEGIN
    
        PRINT ''
    
        SET @tmpstr = '-- Login: ' + @name
    
        PRINT @tmpstr
    
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group

          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    
        END
        ELSE 
        BEGIN -- SQL Server authentication
            -- obtain password and sid
            
            SET @PWD_varbinary = CAST
                                    ( 
                                        LOGINPROPERTY
                                        ( 
                                              @name
                                            , 'PasswordHash' 
                                        ) 
                                        AS varbinary (256) 
                                    )
            
            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
            
            EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
 
            -- obtain password policy state
            SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
            
            FROM sys.sql_logins 
            
            WHERE name = @name
            
            SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
            
            FROM   sys.sql_logins 
            
            WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN '
                             + QUOTENAME( @name )
                             + ' WITH PASSWORD = ' + @PWD_string 
                             + ' HASHED '
                             + ' , SID = ' + @SID_string + ' ' 
                             + ' , DEFAULT_DATABASE = [' + @defaultdb + ']'

            IF ( @is_policy_checked IS NOT NULL )
            BEGIN
            
              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
            
            END
            
            IF ( @is_expiration_checked IS NOT NULL )
            BEGIN
            
              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
            
            END
        
        END
        
        IF (@denylogin = 1)
        BEGIN -- login is denied access
        
          SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
        
        END
        ELSE IF (@hasaccess = 0)
        BEGIN -- login exists but does not have access
        
          SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
        
        END
        
        IF (@is_disabled = 1)
        BEGIN -- login is disabled
        
          SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
        
        END

        set @tsql = replace( @STRING_EXISTENCE_CHECK_PREFIX, '%s', @name)
                        + @CHAR_CRLF
                        + @STRING_BEGIN
                        + @CHAR_CRLF
                        + @tmpstr
                        + @CHAR_CRLF
                        + @STRING_END
        
        PRINT @tsql
      
      END

      FETCH NEXT FROM login_curs 
      INTO  
              @SID_varbinary
            , @name
            , @type
            , @is_disabled
            , @defaultdb
            , @hasaccess
            , @denylogin
      
      END

    CLOSE login_curs
    
    DEALLOCATE login_curs
    
    RETURN 0

end
GO

Source Control

github

DanielAdeniji/sp_help_revlogin
Link

SQL Server – Database Scoped Configurations

Background

Earlier touched on reviewing database compatibility level via sys.databases’ compatibility_level column.

That post is here.

Database Scoped Configuration

In v2016, there is also a new component known as “Database Scoped Configuration” that can be investigated and tuned to review and compare database performance.

Areas

Areas currently exposed includes:

  1. Clearing procedure cache
    • Legacy
      • DBCC FREEPROCCACHE
        • Works against entire SQL Instance or specific plans
  2. MAXDOP parameter for specific databases ( OLTP )
    • Legacy
      • SQL Instance
        • sp_configure ‘max degree of parallelism’
      • Query Hint
        • OPTION ( MAXDOP 1)
  3. Set the query optimizer cardinality estimation model independent of the database compatibility level
    • Legacy :- v2008 thru 2012
    • Current :- v2014
  4. Enable or disable parameter sniffing at the database level
    • Legacy
      • Trace Flag 4136
        • SQL Server 2008 R2 CU2, SQL Server 2008 SP1 CU7 and SQL Server 2005 SP3 CU9 introduce trace flag 4136 to disable the “parameter sniffing” process
          Link
  5. Enable or disable query optimization hotfixes at the database level
    • Legacy
      • Trace Flag
        • SQL Server query optimizer hotfix trace flag 4199 servicing model
          Link
  6. Enable or disable the identity cache at the database level.
    • Legacy
      • Trace Flag
        • Trace Flag 272 disables batching of identity values

 

Read

To read Database Scoped configuration for the current database, please issue


use [dbname]
go

select 
        [database] = db_name()
      , tblSDSC.*

from   sys.database_scoped_configurations tblSDSC

Set LEGACY_CARDINALITY_ESTIMATION

Syntax

Syntax


use [database]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  


Sample


use [hrdb]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  


References

  1. Docs / SQL / T-SQL / Statements
    • ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
      Link
  2. Docs / SQL / Relational databases / System catalog views /
    • sys.database_scoped_configurations (Transact-SQL)
      Link

SQL Server – Database Compatibility Level

Background

Upgrading quite a bit of SQL Server Instances.

One of the areas to keep an eye on is the compatibility level of individual databases within each Instance.

Code

Read Compatibility Level

Credit

Crediting Nick Kavadias ( Link ) for his response on Stack Overflow:

How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
Link

SQL



/*
    How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
    https://stackoverflow.com/questions/1501596/how-to-check-sql-server-database-compatibility-after-sp-dbcmptlevel-is-deprecate

*/
select 

              [database] 
                = tblSD.[name]

            , [compatibilityLevel]
                = tblSD.[compatibility_level]

            , [version] = 
                    CASE [compatibility_level]
                        WHEN 65  THEN 'SQL Server 6.5'
                        WHEN 70  THEN 'SQL Server 7.0'
                        WHEN 80  THEN 'SQL Server 2000'
                        WHEN 90  THEN 'SQL Server 2005'
                        WHEN 100 THEN 'SQL Server 2008/R2'
                        WHEN 110 THEN 'SQL Server 2012'
                        WHEN 120 THEN 'SQL Server 2014'
                        WHEN 130 THEN 'SQL Server 2016'
                        WHEN 140 THEN 'SQL Server 2017'
                    END

from sys.databases tblSD

order by 
        tblSD.[name]

Set Compatibility Level

Legacy

sp_dbcmptlevel

Syntax


exec master..sp_dbcmptlevel 
         @dbname = @name 
       , @new_cmptlevel = @version 

Sample


exec master..sp_dbcmptlevel 
         @dbname = 'hrdb' 
       , @new_cmptlevel = 130

Modern

Alter Database / Set Compatibility Level

Syntax


alter database [db-name]
	set COMPATIBILITY_LEVEL = [compatibilityLevel]

Sample


alter database [hrdb]
	set COMPATIBILITY_LEVEL = 130

References

  1. Alter Database
    • ALTER DATABASE (Transact-SQL) Compatibility Level
      • Docs / SQL / T-SQL / Statements
        Link
    • sp_dbcmptlevel (Transact-SQL)
      • Docs / SQL / Relational databases / System stored procedures
        Link