Transact-SQL syntax not supported in Azure SQL Database

 

Comparing SQL Server and Azure SQL Database: T-SQL Compatibility and Key Differences

When working with Microsoft SQL Server and Azure SQL Database, developers will find that most Transact-SQL (T-SQL) features behave similarly across both platforms. Core functionalities such as data types, operators, and standard SQL functions—like string, arithmetic, logical, and cursor operations—are fully supported in both environments. However, there are certain differences worth understanding, especially when migrating applications or developing hybrid solutions.

---

Core T-SQL Features and Differences

Fully Supported T-SQL Features

For general application development, the majority of standard T-SQL features are identical in both environments. These include:

• Common data types and operators

• Arithmetic, logical, and string functions

• Cursors and control-of-flow constructs

However, differences emerge in system-level operations and administrative features.

---

Notable Differences in Standard T-SQL

While basic SQL usage remains consistent, some areas of T-SQL functionality differ between SQL Server and Azure SQL Database:

Key Differences

• Collation Rules: System object collation may vary.

• Authentication: Azure SQL does not support Windows authentication. Instead, it supports Azure Active Directory (AAD) authentication.

• Connection Endpoints: Some endpoint-related statements are unsupported.

• Cross-Database Queries: Direct multi-database queries using three- or four-part names are not supported, but elastic queries can be used for read-only access across databases.

• Ownership Chaining: Features like TRUSTWORTHY and cross-database ownership chaining are not available.

• Security Context: EXECUTE AS LOGIN is not supported. Use EXECUTE AS USER instead.

• Encryption: Supported with limitations—extensible key management is not available.

• Events and Notifications: Event notifications and query notifications are not supported.

• File Management: File placement, sizing, and management are handled by Azure and cannot be manually configured.

• Log Reader Features: Features depending on log readers (e.g., Change Data Capture) are not available.

• Debugging: T-SQL debugging is not supported.

• USE Statement: Changing the database context requires a new connection.

---

Advanced T-SQL Feature Differences

Some advanced features that rely on hardware access or deep server configuration are either limited or unavailable in Azure SQL Database:

• High Availability Configurations: Managed directly via Azure—traditional features like Always On or log shipping are unavailable.

• Server-Level Triggers: Logon and server-scoped triggers are not supported.

• Trace Flags: These are mostly deprecated or replaced by compatibility levels.

• CLR Integration: .NET CLR features are not supported.

• SQL Server Agent: Job scheduling must be handled using alternatives like Azure Automation or PowerShell.

• SQL Server Audit: Use Azure’s built-in auditing capabilities instead.

• SQL Trace: Unsupported—use Extended Events or auditing.

• Remote Procedure Transactions: Features like SET REMOTE_PROC_TRANSACTIONS are not available.

• Administrative Procedures: System stored procedures like sp_addmessage, sp_helpuser, or sp_migrate_user_to_contained have limited or no support.

• DMVs and Server Roles: Some server-level views and permissions are replaced with database-scoped alternatives.

• Configuration Settings: sp_configure options are replaced by ALTER DATABASE SCOPED CONFIGURATION.

• Semantic Search and FileTable: Not supported in Azure SQL Database.

• Linked Server Features: Statements like OPENQUERY, OPENROWSET, or four-part names are restricted.

• Resource Management: You must configure compute and storage through service tiers instead of hardware tuning.

---

T-SQL with Partial Support or Restrictions

While certain syntax is still available, it may be limited in scope or functionality:

• Database Creation and Alteration: Over 30 options are unavailable in Azure SQL for CREATE and ALTER DATABASE commands.

• FileTable Options: Not supported due to the absence of FILESTREAM.

• Login Management: Only a subset of options are supported for creating and altering logins in Azure.

---

Learn More

For a detailed and continuously updated list of supported features, check Microsoft’s documentation:
🔗 Transact-SQL in Azure SQL Database

How to find log file used space in SQL server

How to find the log file used space in SQL Server

Check the log size in percentage across all databases using the following SQL query:

DBCC SQLPERF(LOGSPACE)

The result of the query provides details about the log space used by different databases. Below is an example of how the output might look:

Database Name   Log Size (MB)   Log Space Used (%)   Status

-----------------------------------------------------------

master          1.992188         43.52941            0

tempdb          15.99219         2.393747            0

model           7.992188         18.23069            0

msdb            28.80469         7.770545            0

This output shows the log size and the percentage of space used for each database. The data is useful for monitoring and maintaining the health of your SQL Server databases.

Top Procedures By Total Logical Writes

 Top Procedures By Total Logical Writes

Understanding "logical writes" is crucial for database performance, as they directly impact both memory and disk I/O. Essentially, logical writes refer to how many pages are written to the buffer cache in memory. High logical writes can indicate a number of things, including inefficient queries that touch too many data pages, or issues with index usage. Monitoring these can help you pinpoint queries that are putting undue pressure on your system's resources.

SELECT TOP (25) p.name AS [SP Name]
 ,qs.total_logical_writes AS [TotalLogicalWrites]
 ,qs.total_logical_writes / qs.execution_count AS [AvgLogicalWrites]
 ,qs.execution_count
 ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,qs.total_elapsed_time
 ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
 ,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan 
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
 AND qs.total_logical_writes > 0
 AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_writes DESC
OPTION (RECOMPILE);

The SQL query above helps identify the top 25 stored procedures by their total logical writes within your current database. This can be a valuable tool for pinpointing potential performance bottlenecks and optimizing your SQL Server environment. Remember that while this query is powerful, it's always a good idea to analyze the query plan and other metrics to get a complete picture of your query's performance.

Sql Server code template & guideline

 

Best Practices for Writing SQL Code: Stored Procedures, Views, and Functions

When working with large development teams or frequent production releases, it's essential to follow a consistent coding structure in SQL Server. This is particularly important in environments with restricted permissions where operations like DROP or CREATE may be limited for security or governance reasons.

The following guide outlines a standardized approach to writing SQL code — especially stored procedures, views, and functions — which enhances maintainability, consistency, and collaboration across teams.

---

📌 Sample SQL Procedure Template

Below is a template you can adapt for writing stored procedures:

/******************************************************************
* DATABASE        : OMAPINTERNET
* PROCEDURE NAME  : usp_SelectAllResults
* CREATED ON      : 04/03/2009
* AUTHOR          : Homer Simpson
* DESCRIPTION     : This procedure returns all results for a given NDC.
* WORK ORDER      : #13798
*******************************************************************
* MODIFICATIONS:
* DATE            : DEVELOPER         : CHANGES
* 08/01/2013      : Himanshu Patel    : Added Indicator column
******************************************************************/

---

✅ Recommended Coding Standards for SQL Procedures

To ensure clarity and best practices in SQL development, follow these guidelines:

• Use CamelCase for naming procedures and variables.
• Implement error handling using TRY...CATCH.
• Avoid cursors whenever possible; use set-based operations instead.
• Indent your code for better readability.
• Add blank lines between logical sections of your code.
• Use ANSI JOINs rather than old-style joins.
• Write clear and meaningful comments.
• Avoid using DROP or CREATE statements inside procedures.
• Declare all variables at the top of the procedure.
• Do not use column numbers in ORDER BY clauses — use column names instead.
• Avoid the use of the GOTO statement.
• Always use primary keys in WHERE clauses for UPDATE or DELETE statements.
• Add SET NOCOUNT ON at the beginning to reduce unnecessary result sets.
• Avoid SELECT *; explicitly define columns.
• Keep transactions short and commit as soon as possible.
• Use BEGIN TRANSACTION and COMMIT blocks for insert/update operations.
• Use sp_executesql instead of EXEC(@sql) for dynamic SQL.
• Avoid using WITH RECOMPILE unless absolutely necessary.
• Never use SQL reserved keywords as object names.
• Structure schemas so that each module has its own schema if the number of tables is large.
• Implement server-side paging to minimize network traffic.

---

🔠 Standard Variable Prefixes

To improve code readability and maintain consistency, use the following naming conventions for variable prefixes based on data type:

No	Data Type	Prefix	Notes
1	tinyint	@ti
2	smallint	@sm
3	int	@in
4	bigint	@bi
5	numeric	@nu
6	hierarchyid	@hi
7	bit	@bl
8	date	@Dt
9	time	@tm
10	datetimeoffset	@do
11	datetime	@Dt
12	datetime2	@Dt
13	money	@mo
14	smallmoney	@Sm
15	decimal	@De
16	float	@fl
17	char	@Ch
18	nchar	@nc
19	varchar	@Vc
20	nvarchar	@Nv
21	uniqueidentifier	@Ui
22	text	@Tx
23	ntext	@Nt
24	sql_variant	@Sv
25	varbinary	@Vb
26	image	@Im
27	xml	@Xm

Dedicated hosting provider name with Price calculator

 

Cloud Hosting Providers and Pricing Calculators

When selecting a cloud hosting service, cost estimation can vary greatly depending on several factors, including:

• Processing power (CPU)

• Storage type, size, and performance

• Memory allocation

• High availability or redundancy configurations

• Licensing for OS or software (e.g., Windows, Oracle)

• Usage patterns (duration, scalability needs, etc.)

Each cloud service provider offers a wide range of configurable services and pricing calculators to help estimate costs. Fortunately, most configurations can be modified even after deployment, offering great flexibility.

---

Google Cloud Platform (GCP)

Key Services:

• Compute Engine, App Engine, Kubernetes Engine, Cloud Run

• Cloud Storage, Networking, Cloud SQL, BigQuery, BigQuery ML

• Firestore, Datastore, Dataflow, Dataproc, Pub/Sub, Bigtable

• Cloud Functions, Cloud Endpoints, Stackdriver, Cloud DNS

• Vision API, Speech API, Translate API, Natural Language API

• Machine Learning, Cloud Spanner, KMS, CDN

• Healthcare API, IoT Core, Dialogflow, Cloud Composer

• Identity Platform, Data Loss Prevention, Memstore, and more

Pricing Calculator:
https://cloud.google.com/products/calculator/

---

Amazon Web Services (AWS)

Key Services:

• Amazon EC2, S3, Route 53, CloudFront

• RDS, DynamoDB, ElastiCache, Redshift, Glacier

• Elastic Load Balancing, CloudWatch, CloudSearch, Kinesis

• SES, SNS, SQS, SWF, Elastic Transcoder

• WorkSpaces, WorkDocs, Directory Service

• Elastic MapReduce, SimpleDB, VPC, Elastic File System

• Snowball, Direct Connect, and a wide range of other tools

Pricing Calculator:
https://calculator.s3.amazonaws.com/index.html

---

Microsoft Azure

Azure offers a comprehensive range of cloud solutions across compute, storage, networking, AI, analytics, databases, and more.

Service List:
https://azure.microsoft.com/en-us/services/

Pricing Calculator:
https://azure.microsoft.com/en-us/pricing/calculator/

---

IBM Cloud

IBM Cloud offers a flexible pricing structure depending on the type of virtual server or cloud service selected. They provide separate links for services, catalog, and pricing.

• Virtual Servers

• Service Catalog

• Pricing Overview

---

Rackspace

Rackspace provides managed cloud solutions and includes a simple pricing calculator for various configurations.

Pricing Calculator:
https://www.rackspace.com/calculator

---

Dimension Data

Dimension Data (now part of NTT Ltd.) also offers cloud pricing based on specific service selections.

Rate Card:
https://www.dimensiondata.com/en/legal/cloud-rate-card

---

Each provider has its own pricing structure and service model. When planning your cloud infrastructure, it's best to use the official calculators and adjust configurations according to your budget, performance requirements, and long-term scalability needs.

Important SQL Server Database Engine Interview Questions and Answers

Here I am trying to provide some important SQL Server database engine interview questions and answers.

What is T-SQL?

T-SQL stands for Transact Structured Query Language. It is an extension of SQL functionality supported by Microsoft SQL Server.

How can T-SQL statements be written and submitted to the Database engine?

T-SQL statements can be written and submitted to the Database engine in the following ways:

• By using the SQLcmd Utility
• By using the SQL Server Management Studio
• By connecting from an application that you create

What is the difference between TRUNCATE and DELETE statements?

The difference between TRUNCATE and DELETE statements is as follows:

• TRUNCATE is used for the unconditional removal of data records from Tables. Truncate operations are not logged.
• DELETE is used for conditional removal of data records from Tables. These operations are logged.
• TRUNCATE is faster than DELETE.
• Truncate command resets the identity value.

What does the T-SQL command IDENT_CURRENT do?

The T-SQL command IDENT_CURRENT returns the last identity value produced for a specified table or view. The last identity value created can be for any session and any scope.

What does the T-SQL command IDENT_INCR do?

The T-SQL command IDENT_INCR returns the increment value mentioned during the formation of an identity column in a table or view that has an identity column.

Can we import data directly from T-SQL commands without using SQL Server Integration Services? If yes, what are the commands?

Yes, it is possible to import data directly from T-SQL commands without using SQL Server Integration Services. These commands include:

• BCP
• OpenRowSet
• Bulk Insert
• OPENQUERY
• OPENDATASOURCE
• Linked Servers

What is a sub-query?

A sub-query is a query that can be nested inside the main query, such as Select, Update, Insert, or Delete statements. Properties of sub-queries include:

• A subquery should not have an ORDER BY clause.
• A subquery should be placed in the right-hand side of the comparison operator of the main query.
• A subquery should be enclosed in parentheses because it needs to be executed first before the main query.
• More than one sub-query can be included.

What are dynamic queries in T-SQL?

Dynamic queries in T-SQL are those queries designed on the fly or at runtime using variables or using CTEs (Common Table Expressions) or other sources. We use EXECUTE function or SP_EXECUTESQL stored procedure to execute such queries.

What are ROLLUP and CUBE in T-SQL?

ROLLUP and CUBE are grouping sets used along with the GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.

What are the Join Types in T-SQL?

Join Types in T-SQL are as follows:

• Inner join
• Outer join
• Left outer join
• Right outer join
• Left outer join with Exclusions
• Right outer join with Exclusions
• Full outer join
• Full outer joins with Exclusions
• Cross join

What are the String functions available in T-SQL?

String functions available in T-SQL are:

• Left
• Right
• Ltrim
• Rtrim
• Substring
• Replace
• Stuff

What is OFFSET-FETCH filter in T-SQL?

In T-SQL, OFFSET-FETCH filter is designed similar to TOP but with an extra element. It helps to define how many rows you want to skip before specifying how many rows you want to filter.

What is Patindex?

The PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression or zero if the pattern is not found.

PATINDEX('%pattern%', expression)

How To Create a Temporary Table? How Do We Apply Nonclustered Index? What Is Nolock? When And Where Is Nolock Applied Normally?

Two ways of creating a temporary table with a non-clustered index applied to it. Also, an example shows how to apply "nolock." NOLOCK is normally applied while querying on production servers. This would make the records being queried sharable on the table. It allows other queries to query the same record in parallel. However, it might return junk data in certain cases because the select query might query the table while other insertions or updates are being performed on the table.

CREATE TABLE #tmpTable
(
  OfficeName varchar(50),
  officeid int,
  CustID int,
  AgentID int,
  mlsid varchar(4),
  RequestMoreDetails int null,
  Emails int null
)
CREATE NONCLUSTERED INDEX #IX_tmpTable ON #tmpTable(AgentID)

SELECT
  OfficeName,
  officeid,
  o.CustID,
  AgentID,
  o.mlsid,
  PrintBrochure_Views = null,
  RequestMoreDetails = null,
  Emails = null
INTO #table1 FROM Offices o
LEFT JOIN dbo.table2 t2 WITH (NOLOCK)
ON t2.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #table1(AgentID)

What Tools Do You Use For Performance Tuning?

Several tools are useful when performance tuning your SQL Server applications, including:

• Query Analyzer
• Profiler
• Index Wizard
• Performance Monitor

What Is SQL Profiler?

SQL Profiler is a tool that allows a system administrator to monitor events in SQL Server. It is mainly used to capture and save data about each event of a file or table for analysis.

What is a Recursive Stored Procedure?

SQL Server supports recursive stored procedures, which call themselves. A recursive stored procedure can be defined as a method of problem-solving wherein the solution is arrived at repetitively. It can nest up to 32 levels.

What are the Differences Between Local and Global Temporary Tables?

Local and global temporary tables differ as follows:

• Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.

CREATE TABLE #

• Global temporary tables are visible to all users and are deleted when the connection that created it is closed.

CREATE TABLE ##

What is a CHECK Constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. The CHECK constraint enforces data integrity.

Can SQL Servers Link to Other Servers?

SQL Server can be connected to any database that has an OLE-DB provider. For example, Oracle has an OLE-DB provider that can be used to connect with SQL Server.

How to check the writeable copy of the database in the availability group SQL server

How to Verify Writable Copies of Databases in an SQL Server Availability Group

This script is useful to check before executing any automation processes, ensuring you're writing to a valid, writable replica.

Checking the Writable Replica for a Specific Database

Before performing any database automation, it's critical to verify that you're operating on a writable replica. Below is a T-SQL script that can be used to check whether a database is on a writable replica. If the database is not writable, the script will exit gracefully:

if (dbo.fn_is_writeable_replica('DatabaseName') = 0)
BEGIN
    PRINT 'EXITING GRACEFULLY';
    THROW 51000, 'This is not a writable replica', 1;
END
    

List Writable Databases from All Availability Groups

To list all the writable databases in your Availability Groups, use the following query. This script retrieves databases that are marked as primary and not part of any distributed availability group:

-- List writable databases from all availability groups
SELECT 
    ars.role_desc, 
    ag_name = ag.[name], 
    adc.[database_name]
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars ON ars.group_id = ag.group_id
JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id
WHERE ars.is_local = 1
  AND ars.role_desc = 'PRIMARY'
  AND NOT EXISTS (
    SELECT 1
    FROM sys.availability_groups dag
    JOIN sys.availability_replicas fwd ON fwd.group_id = dag.group_id
    JOIN sys.availability_groups ag2 ON ag2.name = fwd.replica_server_name
    JOIN sys.availability_databases_cluster db ON db.group_id = ag2.group_id
    WHERE dag.is_distributed = 1
      AND db.[database_name] = adc.[database_name]
  )
    

This query will list all the writable databases in your SQL Server Availability Groups, ensuring that you're only interacting with the primary replica that is not part of any distributed groups.

How to add active directory linked server

 How to add Active Directory-linked server

This is an example of how to set up a linked server to Active Directory.

USE [master]
GO

/****** Object:  LinkedServer [ADSI] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI'
 ,@srvproduct = N'Active Directory Services 2.5'
 ,@provider = N'ADSDSOObject'
 ,@datasrc = N'adsdatasource'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'collation compatible'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'data access'
 ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'dist'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'pub'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'rpc'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'rpc out'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'sub'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'connect timeout'
 ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'collation name'
 ,@optvalue = NULL
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'lazy schema validation'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'query timeout'
 ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'use remote collation'
 ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'remote proc transaction promotion'
 ,@optvalue = N'true'
GO

Example query to retrieve user details from Active Directory.

SELECT *
FROM openquery(adsi, '
select employeeID
 ,sAMAccountName
 ,sAMAccountType
 ,distinguishedName
 ,displayName
 ,cn
 ,givenName
 ,middlename
 ,initials
 ,title
 ,department
 ,mail
 ,streetAddress
 ,c
 ,co
 ,st
 ,l
 ,postalCode
 ,homePhone
 ,mobile
 ,telephoneNumber
 ,company
 ,manager
 ,info
 ,homeDrive
 ,homeDirectory
 ,whenChanged
 ,whenCreated
 ,wWWHomePage
 ,internationalISDNNumber
 ,accountExpires
 ,comment
 ,assistant
 ,countrycode
 ,lastlogon
 ,legacyExchangeDN
 ,userPrincipalName
 ,logoncount
 ,physicalDeliveryOfficeName
 ,ou
 ,primaryGroupID
 ,scriptPath
 ,textEncodedORAddress
 ,facsimileTelephoneNumber
 ,modifyTimeStamp
 ,objectCategory
 ,uSNChanged
 ,uSNCreated
from ''LDAP://ad.MyAdServer.com''
where objectCategory = ''Person''
      and objectClass = ''user''
') AS tab

Refresh all views in a database

 

Refreshing SQL Server Views Automatically

When working with SQL Server, views can become outdated if the underlying tables or objects they depend on are modified. This is especially true for non-schema-bound views—those that don’t explicitly enforce dependency checks on referenced objects. Fortunately, SQL Server provides tools to refresh the metadata of these views, ensuring that they continue to function correctly.

Script to Refresh All Views in a Database

The following T-SQL script automates the process of refreshing all non-schema-bound views in a selected database. It loops through each eligible view and executes the sp_refreshview stored procedure.

DECLARE @ActualView VARCHAR(255);
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT DISTINCT s.[Name] + '.' + o.[Name] AS ViewName
FROM [sys].objects o
JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID 
WHERE o.[type] = 'V'
  AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
  AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1;

OPEN viewlist;
FETCH NEXT FROM viewlist INTO @ActualView;

WHILE @FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXECUTE sp_refreshview @ActualView;
    END TRY
    BEGIN CATCH
        PRINT 'View ' + @ActualView + ' cannot be refreshed.';
    END CATCH;

    FETCH NEXT FROM viewlist INTO @ActualView;
END;

CLOSE viewlist;
DEALLOCATE viewlist;

This script:

• Selects all user-defined, non-schema-bound views
• Iteratively refreshes each view using sp_refreshview
• Handles errors gracefully using TRY...CATCH

Refreshing a Specific View

If you want to refresh a single view manually, you can run:

EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

Refresh Views Dependent on a Specific Object

Sometimes, you may want to refresh only those views that depend on a particular table or object. Here’s how you can identify and generate sp_refreshview commands for them:

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V'
  AND sed.referenced_id = OBJECT_ID('Person.Person');

Replace 'Person.Person' with the name of the object that has changed. This query will list the commands needed to refresh only the affected views.

Learn More

For more details, see the official Microsoft documentation:
sp_refreshview (Transact-SQL) – Microsoft Docs

Possible Bad NC Indexes

 Possible Bad Non-Clustered Indexes

Identifying Unused Indexes in Your SQL Database

Are you looking to optimize your SQL database performance? One common area for improvement is identifying and addressing **unused indexes**. Indexes are crucial for speeding up data retrieval, but having too many, or indexes that aren't being utilized, can actually hinder performance by increasing write overhead.

Here's a handy SQL query that can help you pinpoint those underperforming or completely unused non-clustered indexes in your SQL Server database. This query focuses on indexes that have significantly more writes (updates) than reads (seeks, scans, lookups), suggesting they might not be pulling their weight.

SELECT
    SCHEMA_NAME(o.[schema_id]) AS [Schema Name],
    OBJECT_NAME(s.[object_id]) AS [Table Name],
    i.name AS [Index Name],
    i.index_id,
    i.is_disabled,
    i.is_hypothetical,
    i.has_filter,
    i.fill_factor,
    s.user_updates AS [Total Writes],
    s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
    s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]
FROM
    sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN
    sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
INNER JOIN
    sys.objects AS o WITH (NOLOCK) ON i.[object_id] = o.[object_id]
WHERE
    OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
    AND i.index_id > 1
    AND i.[type_desc] = N'NONCLUSTERED'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND i.is_unique = 0
ORDER BY
    [Difference] DESC,
    [Total Writes] DESC,
    [Total Reads] ASC
OPTION (RECOMPILE);
  

Understanding the Query:

• This query joins **`sys.dm_db_index_usage_stats`** (which tracks index usage), **`sys.indexes`** (for index metadata), and **`sys.objects`** (for table names).
• We filter for **user tables** (`OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1`) within the **current database** (`s.database_id = DB_ID()`).
• The core of the logic is `s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)`. This condition looks for indexes where the number of writes significantly exceeds the number of reads, indicating a potential candidate for removal or modification.
• We specifically target **non-clustered indexes** (`i.[type_desc] = N'NONCLUSTERED'`) and exclude primary keys, unique constraints, and unique indexes, as these often serve other purposes beyond just speeding up reads.
• The results are ordered to show indexes with the largest difference between writes and reads first, followed by those with higher total writes.

Running this query can give you valuable insights into your database's indexing strategy. Before making any changes, always thoroughly analyze the results and consider the specific needs of your application. Removing an index without proper evaluation could lead to performance regressions in other areas.

How to read the SQL log file using TSQL

SQL Server provides a hidden system stored procedure called sp_readerrorlog. This procedure enables you to directly view the SQL Server error log files from a query window and even lets you search for specific keywords within the logs.

The parameters for reading the error log files are as follows:

• Log File Value: 0 = current, 1 = Archive #1, 2 = Archive #2, and so on...
• Log File Type: 1 or NULL = error log, 2 = SQL Agent log
• Search String 1: The first keyword you want to search for.
• Search String 2: An optional second keyword to refine the search results.
• Start Time: The starting point for the search.
• End Time: The end time for the search.
• Sort Order: Use 'N'asc' for ascending order or 'N'desc' for descending order of results.

For example, to search for the word 'backup' in the current error log, you would execute the following SQL query:

EXEC master.dbo.xp_readerrorlog 0, 1, N'backup'

With this approach, you can easily retrieve specific log entries and monitor SQL Server activities in detail.

Top Procedures By Total Physical Reads

 Top Procedures By Total Physical Reads

This post provides a helpful SQL query to identify your most I/O-intensive stored procedures. By analyzing physical and logical reads, you can pinpoint performance bottlenecks and optimize your database.

SELECT TOP (25) p.name AS [SP Name]
 ,qs.total_physical_reads AS [TotalPhysicalReads]
 ,qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads]
 ,qs.execution_count
 ,qs.total_logical_reads
 ,qs.total_elapsed_time
 ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
 ,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan 
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
 AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC
 ,qs.total_logical_reads DESC
OPTION (RECOMPILE);

This query targets the top 25 stored procedures in your current database that have generated physical reads. It provides key metrics like **Total Physical Reads**, **Average Physical Reads per execution**, **Execution Count**, **Total Logical Reads**, and **Total Elapsed Time**. Additionally, it checks for **missing indexes** within the query plan, which can be a significant indicator of performance issues. You'll also see the **Last Execution Time** and **Plan Cached Time** for better context.
Feel free to uncomment the `qp.query_plan` line if you want to inspect the actual query plan for deeper analysis. This script is a great starting point for identifying stored procedures that might be causing high disk I/O and slowing down your database.

Generate Scripts from existing email configurations

 

Generate SQL Server Database Mail Configuration Scripts Using T-SQL

Setting up Database Mail in SQL Server is an important task, especially for sending alerts, job notifications, or custom messages from your SQL instance. While this can be configured through SQL Server Management Studio (SSMS), generating a repeatable T-SQL script allows you to automate and replicate your mail configuration across environments.

In this post, we'll walk through a T-SQL script that dynamically generates the configuration commands for existing Database Mail profiles, accounts, and server settings. This script pulls metadata from msdb and creates a reusable script block for recreating the configuration elsewhere.

---

Overview of the Script

The script does the following:

1. Enables required configuration settings (Database Mail XPs).

2. Generates script blocks to:

   • Create mail profiles if they don’t exist.

   • Create mail accounts with their respective settings.

   • Associate mail accounts with profiles.

3. Optionally includes commented-out commands to drop these configurations.

This is useful for backup purposes or for migrating Database Mail configurations to another server.

---

T-SQL Script for Mail Configuration Generation

Here’s the simplified logic behind the actual T-SQL that builds the configuration script:

USE msdb;
GO

DECLARE @TheResults VARCHAR(MAX), 
        @vbCrLf CHAR(2);
        
SET @vbCrLf = CHAR(13) + CHAR(10);
SET @TheResults = '
USE master
GO
sp_configure ''show advanced options'', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ''Database Mail XPs'', 1
GO
RECONFIGURE
GO
';

-- Append configuration for each mail profile/account
SELECT @TheResults = @TheResults + '
--------------------------------------------------------------------------------------------------
-- BEGIN Mail Settings: ' + p.name + '
--------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_profile_sp 
        @profile_name = ''' + p.name + ''',
        @description = ''' + ISNULL(p.description, '') + ''';
END;

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_account_sp 
        @account_name = ''' + a.name + ''',
        @email_address = ''' + a.email_address + ''',
        @display_name = ''' + a.display_name + ''',
        @replyto_address = ''' + a.replyto_address + ''',
        @description = ''' + a.description + ''',
        @mailserver_name = ''' + s.servername + ''',
        @mailserver_type = ''' + s.servertype + ''',
        @port = ''' + CONVERT(VARCHAR, s.port) + ''',
        @username = ''' + ISNULL(c.credential_identity, '') + ''',
        @password = ''NotTheRealPassword'',
        @use_default_credentials = ' + CAST(s.use_default_credentials AS VARCHAR) + ',
        @enable_ssl = ' + CAST(s.enable_ssl AS VARCHAR) + ';
END;

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysmail_profileaccount pa
    JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
    JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
    WHERE p.name = ''' + p.name + ''' AND a.name = ''' + a.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_profileaccount_sp 
        @profile_name = ''' + p.name + ''',
        @account_name = ''' + a.name + ''',
        @sequence_number = ' + CONVERT(VARCHAR, pa.sequence_number) + ';
END;

/* -- Uncomment to generate cleanup (drop) statements
IF EXISTS (...) -- Checks and deletes associations, accounts, and profiles
BEGIN
    EXEC msdb.dbo.sysmail_delete_profileaccount_sp ...
    EXEC msdb.dbo.sysmail_delete_account_sp ...
    EXEC msdb.dbo.sysmail_delete_profile_sp ...
END;
*/
'
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
LEFT JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT JOIN sys.credentials c ON s.credential_id = c.credential_id;

-- Split the generated text into rows for easier reading
;WITH Tally(N) AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects
),
ItemSplit AS (
    SELECT 
        N,
        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf, N + LEN(@vbCrLf),
                  CHARINDEX(@vbCrLf, @vbCrLf + @TheResults + @vbCrLf, N + LEN(@vbCrLf)) - N - LEN(@vbCrLf)) AS Item
    FROM Tally
    WHERE N < LEN(@vbCrLf + @TheResults)
      AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf, N, LEN(@vbCrLf)) = @vbCrLf
)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS LineNumber, Item
FROM ItemSplit;

---

Why Use This Script?

• Documentation: Quickly generate a readable backup of your email configuration.

• Migration: Move Database Mail settings to another SQL Server instance with minimal effort.

• Automation: Integrate this logic into CI/CD pipelines or setup scripts.

🔐 Note: The script replaces real passwords with 'NotTheRealPassword'. Always handle credentials securely when adapting this for production use.

SQL Server database backup history

 

How to View SQL Server Backup History

Monitoring database backups is essential for maintaining data integrity and recovery readiness. In this post, we’ll look at how to retrieve backup history in SQL Server using T-SQL queries. These scripts help you analyze when your databases were last backed up, the type of backups performed, and the devices used.

---

Viewing Recent Backup History

The following script displays detailed information about all database backups from the past 30 days for a specified database:

-- Retrieve backup history for the past 30 days
SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS ServerName,
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.expiration_date,
    CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Transaction Log' 
    END AS BackupType,
    bs.backup_size,
    bf.logical_device_name,
    bf.physical_device_name,
    bs.name AS BackupSetName,
    bs.description
FROM 
    msdb.dbo.backupmediafamily AS bf
    INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
WHERE 
    bs.backup_start_date >= DATEADD(DAY, -30, GETDATE()) 
    AND bs.database_name = 'myDatabase'
ORDER BY 
    bs.database_name, 
    bs.backup_finish_date;

This query pulls data from the msdb system database and provides insight into the backup type, size, device used, and more.

---

Finding the Most Recent Backup per Database

To identify the latest full backup for each database on the server, use the query below. This script returns the most recent full backup details for all databases.

-- Get the latest full backup information for each database
SELECT 
    A.Server,
    A.last_db_backup_date,
    B.backup_start_date,
    B.expiration_date,
    B.backup_size,
    B.logical_device_name,
    B.physical_device_name,
    B.backupset_name,
    B.description
FROM (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        MAX(bs.backup_finish_date) AS last_db_backup_date
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
    GROUP BY 
        bs.database_name
) AS A
LEFT JOIN (
    SELECT 
        CONVERT(CHAR(100), SERVERPROPERTY('ServerName')) AS Server,
        bs.database_name,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.expiration_date,
        bs.backup_size,
        bf.logical_device_name,
        bf.physical_device_name,
        bs.name AS backupset_name,
        bs.description
    FROM 
        msdb.dbo.backupmediafamily AS bf
        INNER JOIN msdb.dbo.backupset AS bs ON bf.media_set_id = bs.media_set_id
    WHERE 
        bs.type = 'D'
) AS B 
ON 
    A.Server = B.Server 
    AND A.database_name = B.database_name 
    AND A.last_db_backup_date = B.backup_finish_date
ORDER BY 
    A.database_name;

---

These queries are helpful for DBAs who want to quickly validate the backup status of their databases or audit recent backup operations.

For more details on the backup-related dynamic management views, refer to the Microsoft Documentation on backupset.

how to list Query Statistics

 

Understanding dm_exec_query_stats in SQL Server

The dm_exec_query_stats view in SQL Server provides aggregated performance statistics for cached query plans. Each row in this view corresponds to a single query statement within a cached plan. The data stored here is tied to the lifetime of the plan, meaning that when a plan is removed from the cache, the associated rows in this view are also deleted.

---

Query to Retrieve Data Based on Execution Count

This SQL query displays results based on the number of times each query has been executed.

SELECT TOP 1000 
    SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, 
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.TEXT)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2) + 1),
    qs.execution_count,
    qs.total_logical_reads,
    qs.last_logical_reads,
    qs.total_logical_writes,
    qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time / 1000000 AS total_elapsed_time_in_S,
    qs.last_elapsed_time / 1000000 AS last_elapsed_time_in_S,
    qs.last_execution_time,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY execution_count DESC;

---

Example: Top Five Queries by Average CPU Time

The following query returns the top five queries based on average CPU time. The queries are grouped by their query hash, which ensures that logically equivalent queries are aggregated based on their total resource consumption.

SELECT TOP 5 
    query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1, 
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
        - QS.statement_start_offset) / 2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

---

Example: Query to Retrieve Row Count Aggregates

This query retrieves aggregate row count information, including total rows, minimum rows, maximum rows, and last rows for queries.

SELECT 
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1, 
    (CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
        ELSE qs.statement_end_offset END - 
        qs.statement_start_offset) / 2) AS query_text,
    qt.dbid, 
    dbname = DB_NAME(qt.dbid), 
    qt.objectid,
    qs.total_rows, 
    qs.last_rows, 
    qs.min_rows, 
    qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%SELECT%'
ORDER BY qs.execution_count DESC;

---

For more detailed information, you can refer to the official documentation: Sys.dm_exec_query_stats Documentation.

Database side pagination

Pagination is a technique used to break up large datasets into smaller chunks, making it easier to display them on screen. This method is often referred to as paging. It is commonly used in applications, and one of the best examples can be seen in Google Search.

Method 1

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

SELECT [SalesOrderID]
 ,[SalesOrderDetailID]
 ,[CarrierTrackingNumber]
 ,[OrderQty]
 ,[ProductID]
 ,[SpecialOfferID]
 ,[TotalCount] = COUNT(*) OVER ()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY;
GO

Method 2

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

WITH Main_CTE
AS (
 SELECT [SalesOrderID]
  ,[SalesOrderDetailID]
  ,[CarrierTrackingNumber]
  ,[OrderQty]
  ,[ProductID]
  ,[SpecialOfferID]
 FROM [dbo].[SalesOrderDetail]
 )
 ,Count_CTE
AS (
 SELECT COUNT(*) AS [TotalCount]
 FROM Main_CTE
 )
SELECT *
FROM Main_CTE
 ,Count_CTE
ORDER BY Main_CTE.SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY

Method 3

DECLARE @PageSize INT = 10
 ,@PageNum INT = 1;

SELECT [SalesOrderID]
 ,[SalesOrderDetailID]
 ,[CarrierTrackingNumber]
 ,[OrderQty]
 ,[ProductID]
 ,[SpecialOfferID]
 ,[TotalCount]
FROM [dbo].[SalesOrderDetail]
CROSS APPLY (
 SELECT COUNT(*) TotalCount
 FROM [dbo].[SalesOrderDetail]
 ) [Count]
ORDER BY SalesOrderID OFFSET(@PageNum - 1) * @PageSize ROWS

FETCH NEXT @PageSize ROWS ONLY
GO

Find missing index warnings from cached plans

This query could take some time on a busy instance, so be cautious when executing it on production environments.

The following SQL query is designed to retrieve the top 25 cached plans that are missing index recommendations. It may take some time to execute on heavily loaded instances:

SELECT TOP (25) OBJECT_NAME(objectid) AS [ObjectName]
    ,cp.objtype
    ,cp.usecounts
    ,cp.size_in_bytes
    ,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
    AND dbid = DB_ID()
ORDER BY cp.usecounts DESC
OPTION (RECOMPILE);

This query checks the cached execution plans for missing index recommendations in the current database. It can help identify potentially useful indexes that could improve performance. The query returns:

• ObjectName: The name of the object (such as a table) associated with the cached plan.
• objtype: The type of object (e.g., table, index, etc.) that the cached plan pertains to.
• usecounts: The number of times the cached plan has been executed.
• size_in_bytes: The size of the cached plan in bytes.
• query_plan: The XML query plan that details the execution of the query.

Note: The query uses the WITH (NOLOCK) hint to avoid blocking and allow for faster execution, though it may return uncommitted data. The OPTION (RECOMPILE) ensures that the query is recompiled each time it is executed, which is important for reflecting the most up-to-date plan.

Buffers used by current database objects

 Buffers used by current database objects

This SQL query can take some time to execute, especially on a busy database instance. It's designed to provide insights into the buffer usage of your SQL Server, helping you identify which objects are consuming the most memory.

SELECT SCHEMA_NAME(o.Schema_ID) AS [Schema Name],
       OBJECT_NAME(p.[object_id]) AS [Object Name],
       p.index_id,
       CAST(COUNT(*) / 128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
       COUNT(*) AS [BufferCount],
       p.[Rows] AS [Row Count],
       p.data_compression_desc AS [Compression Type]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id
INNER JOIN sys.objects AS o WITH (NOLOCK) ON p.object_id = o.object_id
WHERE b.database_id = CONVERT(INT, DB_ID())
  AND p.[object_id] > 100
  AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
  AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
  AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
GROUP BY o.Schema_ID,
         p.[object_id],
         p.index_id,
         p.data_compression_desc,
         p.[Rows]
ORDER BY [BufferCount] DESC
OPTION (RECOMPILE);

Understanding the Query's Output:

• Schema Name: The schema to which the object belongs.
• Object Name: The name of the database object (table, index, etc.).
• index_id: The ID of the index on the object.
• Buffer size(MB): The estimated memory consumed by the object in megabytes within the buffer cache.
• BufferCount: The number of 8KB pages allocated to the object in the buffer cache.
• Row Count: The total number of rows in the object.
• Compression Type: Indicates if the data is compressed and, if so, the type of compression used.

This query can be a valuable tool for performance tuning and memory management in your SQL Server environment. By identifying objects with high buffer consumption, you can investigate potential optimizations like indexing strategies, data compression, or query rewrites.

Leverage Standard RegEx in SSMS for Powerful Searching

SQL Server Management Studio (SSMS) allows you to perform advanced search and replace operations using Regular Expressions (RegEx). These can be particularly helpful for cleaning up code, refactoring queries, or removing unnecessary characters. Below are some useful RegEx examples that you can apply to your SSMS queries.

---

1. Removing Blank Lines

If you’ve imported code from a source like Firefox, you might encounter unwanted blank lines. To remove these, you can search for:

\n @\n

And replace it with:

\n

---

2. Finding Un-commented 'INT' Instances

To find all instances of INT that aren't commented out, use:

^~(:b<--).:bINT>

This RegEx checks for lines that aren't preceded by a comment (--), making it easier to find INT instances in active code.

---

3. Selecting Up to 100 Characters in a Line

To select the first 100 characters of a line (and all characters if fewer than 100), use:

^(.^100)|(.*)

This will capture up to 100 characters at the start of the line and will fall back to matching the entire line if the line is shorter.

---

4. Inserting a String at a Specific Position

To insert a string at a specific column position (e.g., after the first 100 characters), use the following:

Find:

^{.^100}

Then, replace it with:

\1'I've inserted this'

This will insert the specified text after the first 100 characters.

---

5. Deleting Columns After a Specific Position

To delete 10 characters after column 100, use:

^{(.^100)}{.^10}

Replace with:

\1

This RegEx will preserve the first 100 characters while removing the next 10.

---

6. Finding Quoted or Delimited Strings

To find strings enclosed in either quotes or delimiters, use:

("[^"]*")|([[^[]])

This captures quoted strings as well as strings enclosed by square brackets.

---

7. Replacing Quoted Strings with Delimiters

To replace all quoted strings with delimited strings, use the following find and replace pattern:

Find:

("[^"]*")

Replace with:

[\1]

---

8. Removing Inline Comments

If you want to remove inline comments that take up the entire line, use:

^:b<--.>$

Replace with nothing. This RegEx will remove lines that only contain comments.

---

9. Finding Valid Object Names with Delimiters

To find valid object names within delimiters, use:

[{[A-Za-z_#@][A-Za-z0-9#$@_]*}]

And replace with:

\1

This will remove unnecessary delimiters while ensuring the object name is valid.

---

10. Finding 'Tibbling' Prefixes (tbl, vw, fn, usp)

To find object names that begin with prefixes like tbl, vw, fn, or usp, use:

<(tbl|vw|fn|usp)[A-Za-z0-9#$@_]*>

This RegEx helps identify objects using certain naming conventions.

---

11. Removing Tibbling Prefixes

To remove these prefixes (e.g., tbl or usp) from the object names, use:

Find:

<(tbl|vw|fn|usp){[A-Za-z0-9#$@_]*}>

And replace with:

\1

This will keep the object name while removing the prefix.

---

12. Matching Words with 3 or More Characters

If you need to match words that are at least 3 characters long, use:

<:c^3:c*>

This is a workaround for the lack of proper range quantifiers in SSMS.

---

13. Finding Multi-line Comments

To find multi-line comments that start with /* and end with */, use:

/*(:Wh|[^:Wh])*/

This RegEx works around the limitation of stopping at the end of a line.

---

14. Finding Title-Cased Words

To find words that start with a capital letter followed by lowercase letters, use:

<:Lu:Ll[a-zA-Z0-9_$]*>

This helps find properly capitalized words in your code.

---

15. Removing Headers in Code (e.g., SMO Comments)

To remove headers added by SMO (e.g., comments about object names and script dates), use:

Find:

/[*]^6.<[*]^*6/

Replace with nothing.

---

16. Commenting and Uncommenting Lines

• To comment out lines, select the lines and use:

  Find:

  ^

  Replace with:

  -- (or your comment character)

• To uncomment lines, use:

  Find:

  ^:b<--{.}*$

  Replace with:

  \1

---

17. Finding Two Words Separated by Up to Three Words

To find two words (e.g., FROM and AS) separated by up to three words, use:

\bFROM(?:\W+\w+){1,3}?\\W+AS\b

This RegEx allows for more flexibility in matching phrases between two keywords.

---

18. Finding the First Object Referenced by a FROM Clause

To find the first object referenced by a FROM clause, use:

^~(:b<--.){(:Wh|[^:Wh])#}&< (ON|CROSS|RIGHT|INNER|OUTER|LEFT|JOIN|WHERE|ORDER|GROUP|AS)

This matches keywords after FROM, such as INNER JOIN or WHERE.

---

19. Finding Integers or Floating-point Numbers

To find either integers or floating-point numbers, use:

~(. )<([+-]@[0-9]@.[0-9]E[+-][0-9])|([+-]@[0-9].[0-9]*)|([+-]@[0-9]+)

This captures both types of numeric values in your code.

---

Metacharacters Outside Square Brackets

Character	SSMS Equivalent	Meaning
\	\\	General escape character
^	^	Assert start of string or line
$	$	Assert end of string or line
.	.	Match any character except newline
[	[	Start character class
]	]	End character class
`	`	`
(	(	Start sub-pattern
)	)	End sub-pattern

How to work with Registered Servers in SQL Server Management Studio

Using Registered Servers in SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) offers a powerful feature called Registered Servers, which allows users to easily manage and connect to multiple SQL Server instances. This feature is especially useful for database administrators who frequently work with several servers and want to streamline query execution across them.

How to Access Registered Servers

To open the Registered Servers window in SSMS, follow one of the methods below:

• Navigate to View > Registered Servers from the main menu.
• Use the keyboard shortcut Ctrl + Alt + G.

Registered Servers window in SSMS

Creating a New Server Group

You can organize your servers into custom groups for better manageability. To create a new server group:

• Right-click on Local Server Groups within the Registered Servers window.
• Choose New Server Group and provide a meaningful name.

Creating a new server group in SSMS

Registering a New Server

To register a new server:

1. Right-click on the desired server group and select New Server Registration.
2. Enter the Server Name, choose the Authentication Type, and provide credentials if needed.
3. You also have the option to save the login information.

Registering a new server with credentials

Additional Connection Properties

While registering a server, you can configure additional properties such as:

• Default database
• Connection color for easy visual identification

Setting default database and connection color

Context Menu Options

By right-clicking on a registered server or group, you’ll find several useful options like:

• Connecting to the server
• Editing server registration
• Deleting or exporting registrations

Context menu options in the Registered Servers window

Executing Queries Across Multiple Servers

One of the key benefits of using Registered Servers is the ability to run the same query across multiple servers simultaneously. This is ideal for monitoring, health checks, or executing standard configurations across environments.

---

Further Reading:
To explore more about Registered Servers, visit the official Microsoft documentation:
Register Servers - SSMS | Microsoft Docs