Rebuild Index Scripts

 USE DatabaseName

go

DECLARE @Database NVARCHAR(255)  

DECLARE @Table NVARCHAR(255)  

DECLARE @cmd NVARCHAR(1000)  

 

DECLARE DatabaseCursor CURSOR READ_ONLY FOR  

SELECT name FROM master.sys.databases  

--WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- databases to exclude

WHERE name IN ('VesselRoutes' ) -- use this to select specific databases and comment out line above

AND state = 0 -- database is online

AND is_in_standby = 0 -- database is not read only for log shipping

ORDER BY 1  

 

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

WHILE @@FETCH_STATUS = 0  

BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +  

   table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  

   EXEC (@cmd)  

   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  

   WHILE @@FETCH_STATUS = 0  

   BEGIN

      BEGIN TRY  

         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'

         --PRINT @cmd -- uncomment if you want to see commands

         EXEC (@cmd)

      END TRY

      BEGIN CATCH

         PRINT '---'

         PRINT @cmd

         PRINT ERROR_MESSAGE()

         PRINT '---'

      END CATCH

      FETCH NEXT FROM TableCursor INTO @Table  

   END  

   CLOSE TableCursor  

   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  

END  

CLOSE DatabaseCursor  

DEALLOCATE DatabaseCursor

 

SQL Server 2025 Platform Architecture

SQL Server 2025 (17.x) is a major leap forward, primarily because it shifts the database from a "storage engine" to an AI-ready data platform. It integrates features that previously required external services—like vector searches, machine learning, and advanced text processing—directly into the T-SQL engine.

Here is a breakdown of the most significant advancements in this release:

1. Built-in AI and Vector Search

The standout feature of 2025 is native AI integration. You no longer need to export data to specialized vector databases to build "intelligent" apps like recommendation engines or chatbots.

• Native VECTOR Data Type: Allows you to store embeddings (numerical representations of meaning) directly.

• DiskANN Indexing: A state-of-the-art vector indexing technology that enables lightning-fast "nearest neighbor" searches even on massive datasets.

• T-SQL Model Management: You can now register and call external AI models (like Azure OpenAI or Ollama) directly from a SQL query using sp_invoke_external_rest_endpoint.

2. Modern Developer Productivity

Microsoft has addressed several long-standing "wish list" items for developers, making T-SQL much more flexible.

• Native JSON Type: Unlike previous versions that stored JSON as strings, 2025 uses a native binary format. This makes parsing faster, storage smaller, and allows for direct indexing on JSON fields.

• Regular Expressions (RegEx): After decades of requests, functions like REGEXP_LIKE and REGEXP_REPLACE are finally built into T-SQL, eliminating the need for complex workarounds or CLR assemblies for string validation.

• Change Event Streaming (CES): You can now stream data changes directly to Azure Event Hubs in real-time, simplifying the creation of event-driven architectures.

3. "Zero-ETL" and Cloud Connectivity

The "choice of environment" you mentioned is realized through deeper integration with Microsoft Fabric and Azure Arc.

• Fabric Mirroring: This allows your on-premises SQL Server data to be mirrored into Microsoft Fabric's "OneLake" in near real-time. This provides a "Zero-ETL" experience, meaning you can run heavy analytics in the cloud without setting up complex data pipelines.

• Managed Identities (Entra ID): You can now use Azure Managed Identities for on-premises servers. This means your SQL Server can authenticate to Azure services (like Blob Storage for backups) without you ever having to manage or rotate passwords.

4. Performance and Scalability Boosts

SQL Server 2025 brings several "cloud-born" features from Azure SQL Database to the on-premises engine.

• Optimized Locking: Uses a new "Transaction ID" (TID) locking mechanism that significantly reduces memory consumption and blocking for concurrent transactions.

• Standard Edition Upgrades: Microsoft has increased the limits for the Standard Edition to 32 cores and 256 GB of RAM, acknowledging that modern hardware has outpaced the old 2022 limits.

• Zstandard (ZSTD) Compression: A new backup compression algorithm that offers better ratios and faster performance than the older default compression

Cached Query Plan

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE objtype = 'Adhoc'; 

Change Data Capture for Large table in SQL Server

If you are planning to transfer the data from one db server to another db server regular basis, You should plan to design Change Data Capture (CDC) which allow you to move changed data from source db to Destination server. I have implemented and tested completely with below sql scripts. You can also free to use this one.

-- STEP 1

-- Enable Database for CDC

USE [AdventureDB]

GO

EXEC sys.sp_cdc_enable_db

GO

use [AdventureDB]

GO

ALTER TABLE dbo.[Employee]

ENABLE CHANGE_TRACKING  WITH (TRACK_COLUMNS_UPDATED = ON)  

GO 

 

 --STEP2

USE [ChangeDB]

GO

CREATE TABLE [dbo].[CHG_Employee](

[EMP_ID] [varchar](12) NOT NULL,

[SYS_CHANGE_OPERATION] [char](1) NOT NULL,

[SYS_CHANGE_VERSION] [bigint] NOT NULL,

[Created_Date] [datetime] NOT NULL,

[Upload_Status] [char](1) NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CHG_Employee] ADD  DEFAULT (getdate()) FOR [Created_Date]

GO

ALTER TABLE [dbo].[CHG_Employee] ADD  DEFAULT ('N') FOR [Upload_Status]

GO

-- STEP3

USE [ChangeDB]

GO 

CREATE PROCEDURE [dbo].[usp_CT_CHG_Employee]

AS

/*Description : Change Tracking on [AdventureWorksDB] database to Employee table  */

BEGIN

Declare @last_synchronization_version bigint;

Declare @ChangNum bigint ;

SELECT @ChangNum = max(SYS_CHANGE_VERSION) FROM [ChangeDB].dbo.[CHG_Employee]

-- print @ChangNum

IF Exists (select 1  FROM  [ChangeDB].[dbo].[CHG_Employee] )  

   Begin

    -- PRINT '1 ONE'

INSERT INTO [ChangeDB].dbo.CHG_Employee  (EMP_ID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)

SELECT

CT.EMP_ID,

CT.SYS_CHANGE_OPERATION,

CT.SYS_CHANGE_VERSION

FROM

CHANGETABLE(CHANGES AdventureDB.dbo.Employee, @last_synchronization_version) AS CT 

WHERE CT.SYS_CHANGE_VERSION > @ChangNum

   End

ELSE

   Begin

    --  PRINT '0 ZERO'

INSERT INTO [ChangeDB].dbo.CHG_Employee (EMP_ID ,SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)

SELECT

CT.EMP_ID,

CT.SYS_CHANGE_OPERATION,

CT.SYS_CHANGE_VERSION 

FROM

CHANGETABLE(CHANGES [AdventureDB].dbo.Employee, @last_synchronization_version) AS CT 

End

END

GO

-- exec [dbo].[usp_CT_CHG_Employee]

Find Database Role with Objects and its Permissions

Finding a Database role with permission for Objects like (Table/View) to ensure the users got appropriates access permission within the database.

I have written this query to find database role (Users) with Object permissions.

USE [UserDatabase]

go

SELECT  

sp.[state_desc] ,

sp.[permission_name],

'ON'as Col1, 

ss.[name] AS [Schema_name], 

so.[name] as [Table_View_name], 

--so.[Type],

'TO' as Col2,

 dr.[Name]

FROM    sys.objects as so

INNER JOIN sys.schemas as ss ON so.schema_id = ss.schema_id

INNER JOIN sys.database_permissions AS sp ON sp.major_id    = so.object_id

INNER JOIN sys.database_principals  AS dr ON dr.principal_id = sp.grantee_principal_id

where  so.[Type] ='V' 

Order by  dr.[Name]

Announcing SQL Server 2025 (preview): The AI-ready enterprise database from ground to cloud

https://www.microsoft.com/en-us/sql-server/blog/2025/05/19/announcing-sql-server-2025-preview-the-ai-ready-enterprise-database-from-ground-to-cloud/

How to apply patches on SQL Database Mirror Servers

The following list of steps can be performed against Database Mirror servers to apply patches

1. Take backup of all dbs in Principal Database Server
2. Pause Mirroring on Principal Database Server for all mirrored dbs
3. The Principal server databases becomes (Principal,Suspended)
3. Stop all SQL services on Mirror Database Server (Including WMI service)
4. Apply CU patch on mirrored servers
5. Unpause Mirrors (wait for them to catch up)
6. Failover the databases (the mirror server becomes Principal)
7. Pause Mirroring
8. Hand-over to Application users to test and wait for their confirmation to proceed.
9. Stop all SQL services on Mirror Server (Including WMI service)
10. Apply CU patch on Principal Database servers
11. Unpause mirrors
12. Failback databases.