Microsoft SQL Server

The reference repository is sannae/tsql-queries.

About database administration

Resources, services and processes

• Name of the service and name of the instance:

  select @@servername     -- Hostname and instance name
  select @@servicename    -- Instance service name
  

• List all the processes with an open connection to the current instance:

SELECT hostname, COUNT(hostname) AS Processes
FROM sys.sysprocesses AS P
JOIN sys.sysdatabases AS D ON (D.dbid = P.dbid)
JOIN sys.sysusers AS U ON (P.uid = U.uid)
GROUP BY hostname
ORDER BY COUNT(hostname) DESC

• Show the currently allocated physical memory:

-- The following queries return information about currently allocated memory.
SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024)  AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory; 

-- The following query returns information about current SQL Server memory utilization.
SELECT
sqlserver_start_time,
(committed_kb/1024) AS Total_Server_Memory_MB,
(committed_target_kb/1024)  AS Target_Server_Memory_MB
FROM sys.dm_os_sys_info;

• It returns infos about the size of the database and the corresponding objects (tables, rows, etc.). On SQL Server:

  /* SQL Server */
  -- Returns database Name, Log Size, Row Size, Total Size for current db
  SELECT 
        [Database Name] = DB_NAME(database_id)
      , [Log Size (MB)] = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8./1024 AS DECIMAL(8,2))
      , [Row Size (MB)] = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8./1024 AS DECIMAL(8,2))
      , [Total Size (MB)] = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
  FROM sys.master_files WITH(NOWAIT)
  WHERE database_id = DB_ID() -- for current db 
  GROUP BY database_id
  

  And on MySQL:

  /* MySql */
  -- Returns the database sizes in MB
  SELECT 
    table_schema AS "Database", 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
  FROM information_schema.TABLES 
  GROUP BY table_schema;
  
  -- Returns the table of a specific DATABASE_NAME
  SELECT table_name AS "Table",
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
  FROM information_schema.TABLES
  WHERE table_schema = "database_name"
  ORDER BY (data_length + index_length) DESC;
  

Users and authentication

• It activates the Mixed Mode Authentication in the current SQL Server instance:

USE [master]
GO
/* [Note: 2 indicates mixed mode authentication. 1 is for windows only authentication] */
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

⚠️ Remember to restart the SQL Server engine service!

• It lists all SQL server users, specifying the corresponding roles:

SELECT spU.name, MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin
    ,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin
    ,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin
    ,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin
    ,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin
    ,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin
    ,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator
    ,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM sys.server_principals AS spR
JOIN sys.server_role_members AS srm ON spR.principal_id = srm.role_principal_id
JOIN sys.server_principals AS spU ON srm.member_principal_id = spU.principal_id
WHERE spR.[type] = 'R' 
GROUP BY spU.name

• It creates a new sysadmin login with the specified password:

USE DATABASE_NAME
-- Creates new login
CREATE LOGIN YOUR_USERNAME WITH PASSWORD = 'YOUR_PASSWORD';
GO
-- Assigns the sysadmin server role
ALTER SERVER ROLE sysadmin ADD MEMBER YOUR_USERNAME ;  
GO 

• Find any object from its description with:

USE [YOUR_DATABASE]
Select 
  [name] as ObjectName, 
  Type as ObjectType
From Sys.Objects
Where 1=1
    and [Name] like '%YOUR_OBJECT_DESCRIPTION%'

Object Types acronyms and names are listed in this MS Learn article.

Data and log files

• It restores a backup set from a bak file in the current SQL Server instance, also moving the corresponding files (i.e. basically restoring to a new location, you can find a good guide on this Microsoft Docs page).

  • Start by getting the logical names of the data and log files. The following RESTORE statement cannot be embedded into a SELECT, although you can use it to INSERT the values in a temporary table (following this answer on Stack Overflow)

    -- Get logical names
    RESTORE FILELISTONLY FROM DISK='C:\MY\PATH\TO\BAK\FILE.bak' WITH FILE=1
    

  • Then perform the restore with the option MOVE to replace the original data and log paths with new ones, if required:

    -- Restore database
    RESTORE DATABASE YOUR_DATABASE FROM DISK='C:\MY\PATH\TO\BAK\FILE.bak'
    WITH 
    MOVE YOUR_DATA_LOGICAL_NAME TO 'C:\MY\NEW\PATH\TO\MDF\FILE.mdf',
    MOVE YOUR_LOG_LOGICAL_NAME TO 'C:\MY\NEW\PATH\TO\LDF\FILE.ldf',
    RECOVERY, REPLACE, STATS = 10;
    

• Returns a list of all the logical and physical names for the files of every database in the current SQL Server instance. Source here.

SELECT 
  d.name DatabaseName, 
  f.name LogicalName,
  f.physical_name AS PhysicalName,
  f.type_desc TypeofFile
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
GO

• Migrating a SQL Server database to a lower version is not supported, in any version of SQL Server. You may want to consider generating scripts for the whole database schema and the data to be executed on the older-version instance.

Troubleshooting

• Isolate the database (i.e. put it in "single user mode") from any connection in order to perform maintenance tasks:

  USE YOUR_DATABASE
  GO
  ALTER DATABASE YOUR_DATABASE
  SET SINGLE_USER
  WITH ROLLBACK IMMEDIATE
  GO
  

  Then do all your operations, and finally:

  -- Set the database back in to multiple user mode
  USE YOUR_DATABASE
  GO
  ALTER DATABASE YOUR_DATABASE 
  SET MULTI_USER
  GO
  

• Retrieves the SQL Server Error Log: it returns the error log path for the current SQL Server instance (guide here):

  USE master
  GO
  EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
  GO
  

  If the connection to SQL Server is not available, you may find the error log with the following options:
  1. SQL Server Configuration Manager > SQL Server Services > SQL Server (INSTANCE_NAME) > Properties > Startup Parameters > add -e
  2. Open regedit > Go to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(version)\MSSQLServer\Parameters

• Check out any condition with NULL by using IS NULL instead of = (and similarly for IS NOT NULL and <>)! This is especially true with SQL Server:

  /* NULL cheat sheet */
  DECLARE @MyNullVariable nvarchar(1)
  DECLARE @MyNonNullVariable nvarchar(1)
  SET @MyNullVariable = NULL
  SET @MyNonNullVariable = '1'
  
  IF (@MyNullVariable = NULL) PRINT 'True' ELSE PRINT 'False' -- Returns FALSE
  IF (@MyNullVariable IS NULL) PRINT 'True' ELSE PRINT 'False' -- Returns TRUE
  IF (@MyNullVariable <> NULL) PRINT 'True' ELSE PRINT 'False' -- Returns FALSE
  IF (@MyNonNullVariable IS NOT NULL) PRINT 'True' ELSE PRINT 'False' -- Returns TRUE
  IF (@MyNonNullVariable <> NULL) PRINT 'True' ELSE PRINT 'False' -- Returns FALSE
  

• ⚠️ Error SQL71564: Error validating element [YOUR_USER]: The element [YOUR_USER] has been orphaned from its login and cannot be deployed - means that the user specified in [YOUR_USER] is orphaned, i.e. does not have a corresponding login object, and this can occur even if there actually is a login whose GUID is matching the user's GUID.

So first of all, list the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'YOUR_USER'

Here's a GitHub Gist to fix all the orphaned users.

• ⚠️ Error SQL71654: Error validating element [YOUR_ELEMENT]: the element [YOUR_ELEMENT] cannot be deployed as the script body is encrypted - in this case the database element [YOUR_ELEMENT] has been encrypted with TDE - i.e. WITH ENCRYPTION. Find the element and check if you can retrieve the encryption, or delete it.

Browsing data and tables

• It searches a table in the specified DATABASE_NAME by looking for PATTERN in table name

USE DATABASE_NAME SELECT * FROM information_schema.tables WHERE Table_name LIKE '%PATTERN%'

• It lists the general properties (rows, total occupied space, total free space, etc.) of all the tables in a specified DATABASE_NAME

USE DATABASE_NAME
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY t.Name

• Create a +temporary table* with the result of two other SELECTs on other tables:

  -- Total n.1
  DECLARE @Total1 NVARCHAR(100)
  SET @Total1 = (SELECT COUNT(*) AS [Total1] FROM Table1)
  -- Total n.2
  DECLARE @Total2 NVARCHAR(100)
  SET @Total2 = (SELECT COUNT(*) AS [Total2] FROM Table2)
  -- Summary table
  DECLARE @Totals TABLE (TableNumber NVARCHAR(100), Total INT)
  INSERT INTO @Totals VALUES
      ('Table1',@Total1),
      ('Table2',@Total2)
  SELECT * FROM @Totals
  

• In case you keep seeing the Invalid object name error in the query editor, even if SSMS properly shows the objects you're browsing, try refreshing the IntelliSense cache.

Triggers and automation

• To create an INSERT trigger, follow this template:

  USE DATABASENAME
  GO
  
  -- Start transaction
  BEGIN TRAN
  GO
  
  -- Create trigger
  CREATE TRIGGER [dbo].[T_TableName_TriggerName] ON TableName
  AFTER INSERT
  AS
  BEGIN
      /* Trigger body */
  END
  GO
  
  -- Disable trigger after creation
  DISABLE TRIGGER [dbo].[T_TableName_TriggerName] ON TableName
  
  -- Commit transaction
  COMMIT
  GO
  

  The trigger can then be enabled manually on SSMS, or by using

  ENABLE TRIGGER [dbo].[T_TableName_TriggerName] ON TableName
  

• In the trigger body, the records inserted at each transaction are accessible through the virtual table INSERTED. Here's an example on how to reach for the values just inserted, i.e. the ones activating the trigger:

      /* This is the trigger body */
      /* The following example copies some values of the record inserted in TableName directly into DestinationTable */
  
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
  
      -- Select the record from TableName and add it to DestinationTable
    INSERT INTO DestinationTable
      (field1, field2, ...)
      SELECT
          TableName.Field1, TableName.Field2, ...
      FROM INSERTED
      -- Some other conditions like JOIN or WHERE
  

• The same applies for a DELETE trigger (CREATE TRIGGER [dbo].[T_TableName_TriggerName] ON TableName AFTER DELETE): the deleted record are accessible by the trigger from the DELETED table