INSERT INTO random_data (value)
SELECT FLOOR(1 + (RAND() * 100))
FROM numbers_table
LIMIT 10;

• RAND() generates random numbers.
• Multiply and floor values to get integers in a range.

SELECT @@SERVERNAME, 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Local Machine Name],  
(cpu_count / hyperthread_ratio) AS [Physical CPUs],  
hyperthread_ratio AS [Hyperthread Ratio],  
cpu_count AS [Logical CPUs],  
softnuma_configuration AS [Soft-NUMA Configuration],  
softnuma_configuration_desc AS [Soft-NUMA Description],  
socket_count AS [Available Sockets],  
numa_node_count AS [Available NUMA Nodes]  
FROM  
sys.dm_os_sys_info;  

• ComputerNamePhysicalNetBIOS is the NetBIOS name of the machine where the SQL Server instance is running.
• cpu_count is the number of logical CPUs.
• hyperthread_ratio is the number of CPUs exposed on a single socket.
• softnuma_configuration is set to 0 (off, using hardware defaults), 1 (automated soft-NUMA), or 2 (manual soft-NUMA configuration via the registry).
• softnuma_configuration_desc is either OFF (the soft-NUMA feature is off), ON (SQL Server automatically determines the NUMA node sizes), or MANUAL (manual configuration).
• socket_count is the number of available processor sockets.
• numa_node_count is the number of available NUMA nodes.

How to know to which database you have access?. For this question there is a function HAS_DBACCESS returns 1 if the user has access to the database, otherwise, it will return as 0. If you see value NULL, that means the database name passed to the function is invalid.

SELECT name AS DatabaseName,
HAS_DBACCESS(name) AS HasDBAccess
FROM sys.databases WHERE HAS_DBACCESS(name) = 1

WITH RECURSIVE employee_tree AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, et.level + 1
    FROM employees e
    JOIN employee_tree et
    ON e.manager_id = et.employee_id
)
SELECT * FROM employee_tree;

• Use a recursive CTE to traverse the hierarchy.
• The base case includes top-level managers, and recursion builds levels.

DECLARE @StartDate datetime = MIN(DATEFROMPARTS(1753, 1, 1))
       ,@EndDate   datetime = MAX(DATEFROMPARTS(9999, 12, 30))
;

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE 
	    DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT theDate, 1 as theValue
  FROM theDates
OPTION (MAXRECURSION 0)
;

To enable Service Broker run

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER;

If the SET ENABLE_BROKER hangs and never completes

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

If you get an error when trying to enable Service Broker

The Service Broker in database "DatabaseName" cannot be enabled because there is already an enabled Service Broker with the same ID.

ALTER DATABASE statement failed.

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

To disable Service Broker

ALTER DATABASE [DatabaseName] SET DISABLE_BROKER;

To check if Service Broker is enabled on a SQL Server database

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DatabaseName';

Return YES if the condition is TRUE, or NO if the condition is FALSE.

SELECT IIF(5<10, '5 is lower than 10', '5 is highest');

Output:
5 is lower than 10

Review and modify your database indexes to ensure they align with query patterns. Remove unnecessary indexes and add missing ones to improve query performance.

-- Remove unnecessary indexes
DROP INDEX index_name ON your_table;

-- Add composite index
CREATE INDEX composite_index ON your_table(column1, column2);

Return the specified value IF the expression is NULL, otherwise return the expression.

SELECT 
    ISNULL(NULL,20) result;

Output:
20

SELECT 
    ISNULL('Hello', 'Hi') Result;

Output:
Hello

If expression is numeric returns 1, if not numeric returns 0.

SELECT ISNUMERIC(100);

Output:
1

SELECT ISNUMERIC('sql1');

Output:
0

SELECT ISNUMERIC(20*3);

Output:
1

sys.sysprocesses is a legacy system view that shows details about current processes (similar to sessions) on the SQL Server. Although it’s still supported, it is considered deprecated in favor of the more modern sys.dm_exec_sessions and sys.dm_exec_requests views.

Key Columns:

• spid: The session ID or process ID.
• status: The status of the process (e.g., running, sleeping).
• dbid: The ID of the database the process is connected to.
• blocked: The ID of the process that is blocking this one (if any).
• cmd: The command being executed by the process.
• cpu: The CPU time used by the process.
• physical_io: The number of physical I/O operations performed by the process.

Use Case:

Even though it's deprecated, some DBAs still use sys.sysprocesses for backward compatibility with older SQL Server versions. It provides a mix of session and request information, similar to sys.dm_exec_sessions and sys.dm_exec_requests.

Example Query:

SELECT spid, status, dbid, blocked, cmd, cpu, physical_io
FROM sys.sysprocesses;

sp_MSforeachtable @command1="select '?' as name, count(*) from ?"

• This sp_MSforeachtable is an undocumented stored procedure.
• Does not support on Azure SQL.