Table Sharding in SQL
Table sharding is a database design technique used to improve the scalability and performance of large-scale applications. It involves splitting a large table into smaller, more manageable pieces called "shards," which are distributed across multiple database instances or servers. Each shard contains a subset of the data, and together they form the complete dataset.

Why Use Table Sharding?

• Scalability: Sharding allows horizontal scaling by distributing data across multiple servers.
• Performance: Queries are faster because they operate on smaller datasets.
• Fault Tolerance: If one shard fails, only a portion of the data is affected.
• Cost Efficiency: Sharding enables the use of smaller, less expensive servers instead of a single, high-performance server.

Types of Table Sharding
Range-Based Sharding

• Data is divided based on a range of values in a specific column.
• Example: A table storing user data can be sharded by user ID ranges (e.g., Shard 1: User IDs 1–1000, Shard 2: User IDs 1001–2000).
• Pros: Simple to implement and query.
• Cons: Uneven data distribution if ranges are not carefully chosen.

Hash-Based Sharding

• A hash function is applied to a column (e.g., user ID) to determine which shard the data belongs to.
• Example: hash(user_id) % number_of_shards determines the shard.
• Pros: Ensures even data distribution.
• Cons: Harder to query across shards and to add/remove shards dynamically.

Geographic Sharding

• Data is divided based on geographic location.
• Example: Users in North America are stored in one shard, while users in Europe are stored in another.
• Pros: Useful for applications with geographically distributed users.
• Cons: Can lead to uneven distribution if one region has significantly more users.

Key-Based Sharding

• Similar to hash-based sharding, but uses a specific key (e.g., customer ID or order ID) to determine the shard.
• Pros: Flexible and allows for custom sharding logic.
• Cons: Requires careful planning to avoid hotspots.

Directory-Based Sharding

• A lookup table (directory) maps each record to its corresponding shard.
• Pros: Highly flexible and allows for dynamic shard allocation.
• Cons: Adds complexity and requires maintaining the directory.

Examples of Table Sharding
Example 1. Range-Based Sharding
-- Shard 1: User IDs 1–1000
CREATE TABLE users_shard1 (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

-- Shard 2: User IDs 1001–2000
CREATE TABLE users_shard2 (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

Example 2. Hash-Based Sharding
-- Shard 1: Hash(user_id) % 2 = 0
CREATE TABLE users_shard1 (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

-- Shard 2: Hash(user_id) % 2 = 1
CREATE TABLE users_shard2 (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

Example 3. Geographic Sharding
-- Shard 1: North America
CREATE TABLE users_na (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
region VARCHAR(50)
);

-- Shard 2: Europe
CREATE TABLE users_eu (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
region VARCHAR(50)
);

Best Practices for Table Sharding

Choose the Right Sharding Key
Select a column that ensures even data distribution and minimizes cross-shard queries.
Example: User ID or Order ID.

Plan for Growth

Design shards to accommodate future data growth.
Avoid hardcoding shard ranges to allow for dynamic scaling.

Minimize Cross-Shard Queries

• Cross-shard queries can be slow and complex. Design your application to minimize them.
• Example: Use denormalization or caching to reduce the need for joins across shards.

Monitor and Balance Shards

• Regularly monitor shard sizes and redistribute data if necessary to avoid hotspots.

Use Middleware or Sharding Libraries

• Middleware tools like ProxySQL or libraries like Hibernate Shards can simplify sharding logic.

Implement Backup and Recovery

• Ensure each shard is backed up independently and has a recovery plan.

Test for Performance

• Test your sharding strategy under realistic workloads to identify bottlenecks.

Document Sharding Logic

• Clearly document how data is distributed across shards to help developers and DBAs.

Challenges of Table Sharding

• Complexity: Sharding adds complexity to database design and application logic.
• Cross-Shard Transactions: Managing transactions across shards can be difficult.
• Rebalancing Data: Adding or removing shards requires redistributing data, which can be time-consuming.
• Query Optimization: Queries need to be optimized to avoid unnecessary cross-shard operations.

Conclusion
Table sharding is a powerful technique for scaling large databases, but it requires careful planning and implementation. By understanding the different types of sharding, following best practices, and addressing potential challenges, you can design a sharding strategy that meets your application's scalability and performance needs.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Database fragmentation is frequently the cause of inflated storage usage, slow query performance, and excessive I/O operations that you may have experienced as a developer or database administrator (DBA) using SQL Server. Database fragmentation, especially in SQL Server, has a direct impact on your application's speed and efficiency and, if left unchecked, can lead to lengthier query execution times. Understanding and fixing database fragmentation in SQL Server is the only topic covered in this book. We'll go over how fragmentation occurs, show how to spot it using real-world examples, and discuss how to maximize fractured data structures.

What is Fragmentation in SQL Server?
In SQL Server, fragmentation occurs when data in your database is not stored contiguously, resulting in disorganized pages and inefficient query executions. Fragmentation specifically affects:

1. Tables are stored as heaps or clustered tables.
2. Indexes (both clustered and non-clustered) that play a critical role in query optimizations.

SQL Server stores data in 8KB pages, and when these pages are not maintained in sequential order on disk, your database experiences external fragmentation. At the same time, internal fragmentation occurs when there is excessive free space within pages.

Why Does This Matter?

• Non-sequential data forces SQL Server to perform additional I/O operations, wasting CPU and memory cycles.
• Database read operations (e.g., scan or seek) become slower.
• Backups, restores, and database maintenance tasks require more time and resources.

Live Example 1. Detecting and Managing Index Fragmentation
Setup. Create and Populate a Fragmented Table
Let’s start by creating a fragmented table with a clustered index, performing multiple INSERT and DELETE operations, and then detecting the fragmentation.

Step 1. Create a Test Table with a Clustered Index
CREATE TABLE dbo.FragmentedTable (
    ID INT IDENTITY PRIMARY KEY,
    Data VARCHAR(1000)
);

-- Populate the table with some test data
INSERT INTO dbo.FragmentedTable (Data)
SELECT TOP (10000) REPLICATE('X', 1000)
FROM master.dbo.spt_values;

Here, we have a table with a clustered primary key on the ID column, and each row has a Data field filled with 1000 characters.

Step 2. Cause Fragmentation
Simulate fragmentation by deleting rows and inserting new ones, which will disrupt the contiguous storage of data.
-- Delete every 10th row to create gaps in the data pages
DELETE FROM dbo.FragmentedTable
WHERE ID % 10 = 0;

-- Insert additional rows to further cause fragmentation
INSERT INTO dbo.FragmentedTable (Data)
SELECT REPLICATE('Y', 1000)
FROM master.dbo.spt_values
WHERE type = 'P';  -- Arbitrary condition to get more values

At this stage, our table’s clustered index has become fragmented because the deleted rows left empty spaces, which new rows may not fill in sequentially.

Step 3. Check the Fragmentation Level
SQL Server provides the sys.dm_db_index_physical_stats dynamic management function to detect index fragmentation.
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc AS IndexType,
    ips.avg_fragmentation_in_percent AS FragmentationPercent,
    ips.page_count AS PageCount
FROM sys.dm_db_index_physical_stats(
    DB_ID(),    -- Current Database ID
    OBJECT_ID('dbo.FragmentedTable'), -- Target Table
    NULL,       -- All Indexes
    NULL,       -- All Partitions
    'DETAILED'  -- Detailed Mode
) ips
JOIN sys.indexes i
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

Output Example

In this case, you can see that the clustered index has 45.67% fragmentation, significantly impacting read and write efficiency.

Step 4. Rebuild the Fragmented Index
SQL Server provides two key options to resolve index fragmentation:

• Rebuild the index: This recreates the index from scratch.
• Reorganize the index: This performs an in-place defragmentation without locking the table.

Use the following query to rebuild the index:
-- Rebuild the index to address fragmentation
ALTER INDEX ALL ON dbo.FragmentedTable REBUILD;

Alternatively, you can reorganize the index (less resource-intensive but slower):

-- Reorganize the index for minor fragmentation
ALTER INDEX ALL ON dbo.FragmentedTable REORGANIZE;

Step 5. Verify the Results
Run the same query to check fragmentation again:

-- Check fragmentation after index maintenance
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent AS FragmentationPercent
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('dbo.FragmentedTable'),
    NULL,
    NULL,
    'DETAILED'
) ips
JOIN sys.indexes i
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id;

You should now observe that the FragmentationPercent has significantly decreased.

Key Takeaway

• Rebuilding or reorganizing indexes regularly based on fragmentation levels (e.g., rebuild for >30% and reorganize for 5–30%) ensures optimal query performance.
• Live Example 2. Handling Fragmentation in a Non-Indexed Heap

When tables do not have clustered indexes, fragmentation can still occur. Let’s run through the detection and resolution process for a heap.

Step 1. Create a Non-Indexed Table (Heap)
CREATE TABLE dbo.HeapTable (
    ID INT IDENTITY,
    Data VARCHAR(1000)
);

-- Insert initial data into the heap
INSERT INTO dbo.HeapTable (Data)
SELECT TOP (10000) REPLICATE('A', 1000)
FROM master.dbo.spt_values;

Step 2. Cause Fragmentation
-- Randomly delete rows from the heap to simulate fragmentation
DELETE FROM dbo.HeapTable
WHERE ID % 5 = 0;

-- Insert some more data into the table
INSERT INTO dbo.HeapTable (Data)
SELECT REPLICATE('B', 1000)
FROM master.dbo.spt_values
WHERE number < 200 AND type = 'P';

Step 3. Detect Heap Fragmentation
Without indexes, fragmentation in heaps is indicated by the Forwarded Records value, which highlights how often SQL Server has to jump between pages to retrieve data.
-- Check heap fragmentation levels
SELECT
    OBJECT_NAME(ps.object_id) AS TableName,
    ps.index_id,
    ps.forwarded_record_count AS ForwardedRecords,
    ps.avg_page_space_used_in_percent AS AvgPageSpaceUsed
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('dbo.HeapTable'),
    NULL,
    NULL,
    'DETAILED'
) ps;

Output Example
TableName   Index_ID    ForwardedRecords    AvgPageSpaceUsed
HeapTable   0 (Heap)    1200    68.45%

High forward record counts and low average page space utilization indicate significant fragmentation.

Step 4. Mitigate Heap Fragmentation
The most effective way to handle heap fragmentation is to:
Add a clustered index (preferably on a primary key) to organize the data.
Alternatively, use ALTER TABLE ... REBUILD to compact the heap.

-- Add a clustered index to eliminate fragmentation
CREATE CLUSTERED INDEX IX_HeapTable_ID ON dbo.HeapTable (ID);

-- Or compact the heap without adding an index
ALTER TABLE dbo.HeapTable REBUILD;

Best Practices for Fragmentation Management in SQL Server
Regular Maintenance: Set up a SQL Server Maintenance Plan to automatically rebuild or reorganize indexes regularly, based on predefined thresholds.

Use Fill Factor: Adjust the Fill Factor when creating indexes to leave free space for future inserts/updates, minimizing fragmentation.

CREATE INDEX IX_FillFactor ON dbo.MyTable (Column1)
WITH (FILLFACTOR = 80);

Monitor Auto-Growth Settings: Poorly configured database auto-growth settings contribute to fragmentation. Set optimal values for auto-growth increments to reduce frequent growth events.
Partition Large Tables: Partitioning tables can help reduce fragmentation by organizing large data sets into smaller chunks (e.g., by date ranges).

Conclusion
Fragmentation in SQL Server can have a severe impact on performance if not proactively managed. By regularly monitoring and addressing fragmented indexes and heaps, you can ensure efficient data access and storage utilization. Use tools like sys.dm_db_index_physical_stats, automate index maintenance, and apply best practices such as partitioning and fill factor adjustments to mitigate fragmentation in your SQL Server databases. Remember. A well-maintained database is the foundation of a high-performance application. Keep your data storage clean, fast, and efficient!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Today, we will delve into Database Console Commands (DBCC) in SQL Server. These are powerful commands, and having knowledge of them definitely helps us understand SQL Server databases. A collection of commands known as Database Console Commands (DBCC) in SQL Server aid developers and administrators in keeping an eye on, maintaining, and debugging SQL Server databases. Additionally, DBCC commands offer diagnostic and repair features to guarantee the functionality and well-being of your databases. Understanding DBCC commands is essential for every developer or DBA working with SQL Server in order to guarantee peak performance.

What Are DBCC Commands?
DBCC stands for Database Console Commands. These commands are essentially SQL Server utilities that help with:

• Maintenance: Tasks like shrinking databases or checking integrity.
• Monitoring: Performance diagnostics and examining internal behavior.
• Troubleshooting: Debugging deadlocks, corruption, or unexpected behavior.
• Validation: Checking database consistency and detecting possible issues early.

Common Categories of DBCC Commands
DBCC commands can generally be grouped into the following categories based on their purpose:

Maintenance Commands

• DBCC SHRINKDATABASE: Shrinks the size of the database files.
• DBCC SHRINKFILE: Shrinks a specific data file within a database.
• DBCC CLEANUP: Cleans up particular resources.

Validation Commands

• DBCC CHECKDB: Checks the integrity of all objects in a given database.
• DBCC CHECKTABLE: Checks the integrity of a specific table.
• DBCC CHECKFILEGROUP: Checks the integrity of a file group.

Status and Monitoring Commands

• DBCC SHOW_STATISTICS: Displays statistics data for a table.
• DBCC SQLPERF: Reports SQL Server performance metrics.

Troubleshooting Commands

• DBCC TRACEON: Enables a specific trace flag.
• DBCC TRACEStatus: Retrieves the status of trace flags.
• DBCC INPUTBUFFER: Retrieves the last SQL statement executed on a particular session.
• DBCC PAGE: Views internal database pages for advanced troubleshooting.
• DBCC DROPCLEANBUFFERS: Clears unused data from the server buffer pool.

Deadlock Diagnostic Commands
DBCC SQLPERF and DBCC TRACEON can be helpful in diagnosing and resolving deadlocks by enabling trace flags and visualizing system performance related to locking.

Importance of DBCC Commands
Proactive Monitoring
DBCC commands enable early identification of database issues (e.g., data corruption or performance degradation) before they escalate into larger problems. Commands like DBCC CHECKDB ensure the database remains consistent and functional.

Troubleshooting Deadlocks and Blocking
As developers and DBAs know, deadlocks can be a nightmare, affecting applications and causing production downtime. DBCC commands help visualize, debug, and resolve deadlock situations efficiently.

Database Optimization
Commands such as DBCC SHRINKDATABASE and DBCC CLEANUP help maintain a proper storage footprint, reducing wasted space and optimizing I/O performance.

Improving Debugging and Insight
Commands like DBCC INPUTBUFFER and DBCC PAGE give insights into what is happening inside SQL Server, helping with understanding and solving performance bottlenecks or improper SQL usage.

Deadlock Example and How DBCC Can Help

What Is a Deadlock in SQL Server?
A deadlock occurs when two or more processes block each other by holding locks on resources the other processes need. For example:

Process 1 locks Table A and then tries to access Table B, but Table B is locked by Process 2. Meanwhile, Process 2 tries to access Table A, which is locked by Process 1. Consequently, neither process can proceed, resulting in a deadlock.

Deadlock Scenario
-- Step 1: Create Two Tables
CREATE TABLE TableA (
ID INT NOT NULL PRIMARY KEY,
Data VARCHAR(50)
);

CREATE TABLE TableB (
ID INT NOT NULL PRIMARY KEY,
Info VARCHAR(50)
);

-- Step 2: Insert Sample Data
INSERT INTO TableA VALUES (1, 'A');
INSERT INTO TableB VALUES (1, 'B');

Stimulate the Deadlock by running two process in separate session
-- Step 3: Simulate a Deadlock
-- Process 1 locks TableA and tries to lock TableB

BEGIN TRANSACTION;

UPDATE TableA
SET Data = 'Updated A'
WHERE ID = 1;

WAITFOR DELAY '00:00:15'; -- Simulate time delay

UPDATE TableB
SET Info = 'Updated B'
WHERE ID = 1;

COMMIT TRANSACTION;

-- In another session, Process 2 locks TableB and tries to lock TableA

BEGIN TRANSACTION;

UPDATE TableB
SET Info = 'Another Update'
WHERE ID = 1;

WAITFOR DELAY '00:00:15'; -- Simulate time delay

UPDATE TableA
SET Data = 'Another Update'
WHERE ID = 1;

COMMIT TRANSACTION;

When both processes run simultaneously, SQL Server will detect the deadlock and terminate one of the processes with a deadlock error.

Using DBCC to Diagnose and Resolve Deadlocks
SQL Server offers various tools for resolving deadlocks. DBCC commands can play an essential role in diagnosing root causes.

Step 1. Enable Deadlock Trace Flags
Before simulating the deadlock, enable deadlock tracking using DBCC TRACEON:sql
DBCC TRACEON (1204, -1); -- Enables deadlock reporting to Error Log
DBCC TRACEON (1222, -1); -- Provides extended deadlock information

These trace flags provide detailed insights into why deadlocks occur and which resources are involved. The information will appear in the SQL Server Error Log.

Step 2. Simulate Deadlock Again
Run the above scenarios to create the deadlock. Deadlock trace flags will log information.

Step 3. Check the Deadlock Information in Error Logs
Run the following DBCC command to inspect SQL Server Error Logs for deadlock details:
DBCC ERRORLOG; -- Retrieves information from SQL Error Logs

You'll see details about the involved processes, tables, and locks. This output will help pinpoint the conflict and guide resolution steps.

Step 4. Resolving the Deadlock
Once identified, you can resolve or prevent the deadlock using one or more of the following approaches:

Adjusting Transaction Logic: Reorder queries or ensure processes obtain locks in the same order to avoid circular locking dependencies.
Using SET DEADLOCK_PRIORITY: Assign a lower priority to less critical transactions, so SQL Server terminates them instead of high-priority transactions during deadlocks:
SET DEADLOCK_PRIORITY LOW;

Index Optimization: Add efficient indexes to reduce locking contention on heavily accessed tables.

Using NOLOCK: Leverage query hints like NOLOCK, if appropriate, for reducing locking conflicts:
SELECT * FROM TableA WITH (NOLOCK);

Conclusion
DBCC commands are invaluable tools for database maintenance, monitoring, and troubleshooting. In scenarios like deadlocks, they help diagnose and uncover resource conflicts, allowing DBAs and developers to take corrective actions. Whether you're ensuring database integrity with DBCC CHECKDB or debugging internal locks and deadlocks with DBCC TRACEON, mastering DBCC will empower you to keep your SQL Server environments running smoothly and efficiently. By leveraging DBCC commands, you can proactively manage issues, reduce downtime, and optimize performance skills that every developer and DBA should have in their toolkit.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In a SQL Server database, this article shows how to replicate a table containing data between databases. This article uses a source database that has a table with data in it and creates a new database that has a copy of the source database's table with data in it. This article outlines a few easy ways to accomplish this. Let's now examine a real-world example of how to transfer a table containing data between databases in SQL Server. The SQL Server Management Studio is used to create the example on SQL Server.Creating a table in SQL Server

Now we create a table named employee using.
CREATE TABLE [dbo].[Employee]
(
    [EmpID] [int] NULL,
    [EmpName] [varchar](30) NULL,
    [EmpSalary] [int] NULL
)

The following is the sample data for the employee table.

Method 1. Copy Table using SELECT INTO
This command only copies a table's schema and its data. The Select into is used to copy a table with data from one database to another database's table. The Select into is also used to create a new table in another database. The general syntax to do that is:

Syntax
SELECT * INTO DestinationDB.dbo.tableName FROM SourceDB.dbo.SourceTable

Example
The employee table is defined in the master database. It is also called the source database. Now you want to copy the table with data from the master database to the model database. The following query defines it:
SELECT * INTO Model.dbo.[Employee] FROM Master.dbo.[Employee]

Now hit F5 to execute it.

Now press F8 to open the Object Explorer and select the model database to see the employee table.

Method 2. Generating Script in SQL Server
If you want to copy all objects, indexes, triggers, constraints, etc., do it using "Generate Scripts...". Suppose we have a database named Test. Now right-click on the Test database and select the "Generate Scripts..." option.

database Name -> "Tasks" -> "Generate Scripts...."

Now click on "Generate Scripts...". The Generate Scripts wizard will be opened.

Now click the "Next" Button and select tables and Stored Procedures.

Now click on the "Next" Button and provide the proper name with the file's path.

Now click the "Next" Button and review your source and target location.

Now click on the "Next" Button.

Now finally, click on the "Finish" button. The script file has been generated for the Test database. To see the generated script file, select the file's location on your computer.

Creating a Database in SQL Server
These are the following steps to create a new database:

• Press F8 to open the Object Browser in SQL Server Management Studio and expand it
• Database -> right-click-> select New database
• This would open the "New Database" window
• Now enter a database name to create a database
• Now click on the OK button to create the database. The new database will be shown in the Object Explorer

Currently, the database, named DatabaseTest, has been created.

Copy Database Schema and Data to Other Database
Now right-click on the script file, open it in Notepad, copy all the data, and paste it into the query window in SQL Server. It will look as in the following.

Now only change the database name test to DatabaseTest.

Now press F5 to execute the script and expand the databaseTest to see the schema and data.

Summary
This article taught us how to Copy Table With Data From One Database to Another in SQL Server.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

A: Overview
SQL statements that are created and run at runtime in response to input parameters are known as dynamic SQL. Coding flexibility is increased by using dynamic SQL statements, however programming and maintenance will become more difficult. Additionally, a success concept like SQL Injection will be introduced (see What Is SQL Injection).

In general, we would say, dynamic SQL Script is no good, due to, at least, two reasons:

1. Not readable, and not maintainable;
2. Security, possible the target of SQL injection

Therefore, using dynamic SQL Script must be in the situation: 'have to'.

In our previous SDD database data input, Create a System Data Dictionary Database, we use dynamic SQL. Let us discuss the reasonabilities. The content of this article:

• A - Introduction
• B - Security is not a concern for using Dynamic SQL in a Stored Procedure
• C - Requirement: Loop for Servers and Databases
  • C.1 - Get Servers (including linnked servers and remote servers) by SQL
  • C.2 - Get Databases by SQL
• D - Looping Databases by Static SQL is not possible
  • D.1 - All system database, such as sys.object, are all within one database, except servers and databases,
  • D.2 - The USE key word cannot be used within a Stored Procedure
  • D.3 - The database Identifier cannot be parameterized.
• E - Dynamic SQL Script is the only choice for Looping databases

B - Security is not a concern for using Dynamic SQL in a Stored Procedure
First, due to used in Stored Procedure, within firewall, and especially, there is no parameter input, so the security issue is almost zero. Let us see if this is a 'have to' situation.

C - Requirement: Loop for Servers and Databases
We need to create a database including all information about the whole databases in a Server, or at least one instance. We need to dynamically loop the databases existing in this server or instance.This is possible by

C.1 - Get Servers (including linnked servers and remote servers) by SQL
SELECT * FROM sys.servers

Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0. Such as

C.2 - Get Databases by SQL
SELECT * FROM sys.databases

Contains one row per database in the instance of SQL Server.

D - Looping Databases by Static SQL is not possible
Due to the following reasons:

• All system database, such as sys.object, are all within one database, except servers and databases,
• The USE key word cannot be used within a Stored Procedure
• The database Identifier cannot be parameterized.

We show these one by one.

D.1 - All system database, such as sys.object, are all within one database, except servers and databases,

In this page: sys.sysobjects , the definitiion says:

This ontains one row for each object that is created within a database

D.2 - The USE key word cannot be used within a Stored Procedure:
a USE database statement is not alloweed in a procedure, function or trigger:

if we try to use a USE statement in a stored procedure, we will see the error like above.

D.3 - we cannot use the dynamic database name in the schema identifier, such as

E - Dynamic SQL Script is the only choice for Looping databases
The following code sample is from the previous article: Create a System Data Dictionary Database

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Data can be queried and worked with as objects using ObjectContext and DbContext. Furthermore, the Unit of Work and Repository patterns can be combined to describe Dbcontext. DbContext and ObjectContext are conceptually equivalent.

ObjectContext
ObjectContext is a class that manages all the database operations, like database connection, and manages various entities of the Entity Model. We can say that ObjectContext is the primary class for accessing or working together with entities that are defined in the conceptual model.

ObjectContext is responsible for

• Database connection
• It provides built-in Add, Update and Delete functions
• Object Set of every entity
• Provide State of pending changes
• It holds the changes done in entities

ObjectContext also encapsulates a few things

• Connection to the Data Store or Database
• Metadata in the Entity Data Model (EDM)
• ObjectStateManager to track changes to the objects

DbContext
DbContext is conceptually similar to ObjectContext. DbContext is nothing but an ObjectContext wrapper, we can say it is a lightweight alternative to the ObjectContext. DbContext can be used for DataBase first, code first and model first development. DbContext mainly contains a set of APIs that are very easy to use. The API is exposed by ObjectContext. These APIs also allow us to use a Code First approach that ObjectContext does not allow.

ObjectContext VS DbContext

There are also many methods in common in both ObjectContext and DbContext. For example, ObjectContext has a direct method to execute a query against a database whereas the DbContext.Database class contains the SQLQuery method to obtain the same result.

Example
// using ObjectContext (EF4.0) using (
  Entities context = new Entities()
) {         IEnumerable < EmployeeDetails > empDetails   =  context.ExecuteStoreQuery < EmployeeDetails >     ("exec GetEmployeeData").ToList();
} // using DBContext (
  EF 4.1
  and above
) using (
  Entities context = new Entities()
) {         IEnumerable < EmployeeDetails > empDetails   =  context.Database.SqlQuery                                                                       < EmployeeDetails >("exec GetEmployeeData ", null).ToList();
}

DbContext also has some sets of methods that are not available with ObjectContext, like Dbset. Find, DbSet.Local etcetera.

Some of the ObjectContext methods are also renamed. For example, ObjectContext has methods like AddObject, DeleteObject, And Attach on ObjectSet<T>, in DbContext, they are named Add, Remove and Attach on DbSet<TEntity>.

Conclusion
Dbcontext can be defined as a lightweight version of the ObjectContext or we can say Dbcontext is a wrapper of ObjectContext and exposes only the common features that are really required in programming.

We can also get a reference to the ObjectContext from then DbContext to use those features that are only supported in ObjectContext.

The following code could help to get an ObjectContext Object from an existing DbContext Object.
public class EntityDBContext: DbContext,
IObjectContextAdapter {
ObjectContext IObjectContextAdapter.ObjectContext
{         get         {               var objectContext = (this as IObjectContextAdapter)               if(objectContext != null)
return (this as IObjectContextAdapter).ObjectContext;
              else                 return null;
        }    } }
Finally, DbContext is not a replacement for ObjectContext, but it is a simple alternative that builds on ObjectContext. Hope this will help to understand the differences between ObjectContext and DbContext.

"What are the differences between NoSQL and SQL databases?" is a question you've undoubtedly heard if you've taken part in a lot of interviews. You will gain a thorough understanding of these distinctions and when to pick one over the other from this essay.

As a software developer, you may have already worked with various SQL databases such as T-SQL, PostgreSQL, MySQL, and others. What's the first thing you've likely noticed? The "rules" or the "schema," of course. SQL databases have highly structured data models. You create tables with defined columns and rows, strictly following a predetermined schema. Breaking this structure means violating the fundamental principles of SQL. Tables, columns, rows, and data types form the essential building blocks for organizing your data.

On the other hand, when you work with NoSQL databases (non-relational databases) like Azure Cosmos DB, Aurora, or MongoDB, you have the flexibility to frequently modify your data model. NoSQL databases don't enforce a rigid structure. They provide an "elastic" schema, allowing you to store data in various formats. Instead of sticking to the traditional table representation, you can use document-based, key-value-based, graph-based, or column-based models, among others.

For relational databases, developers usually apply normalization (such as first normal form, second normal form, etc.) to ensure a clear and balanced data structure. As a result, relational databases often rely heavily on multi-table JOINs, aggregations, and complex relationships to retrieve data. However, when datasets become large, it can be challenging and inefficient to manage and retrieve data quickly from relational databases.

Unfortunately, relational databases aren't inherently designed to handle massive volumes of data. They follow a "scale-up" approach, meaning they require more resources such as RAM, CPU, and GPU to handle increased data.

NoSQL databases, however, are designed for "scaling out." This means you can distribute and handle data across multiple servers without negatively impacting performance. Many people associate "NoSQL" with "Big Data," often using these terms interchangeably. Indeed, you can consider the term "NoSQL" a buzzword frequently associated with solving big data challenges.

Behind NoSQL Lies the 3V Principle

• Volume
• Velocity
• Variety

Let's examine each of these elements individually to understand their importance.

Volume refers to handling massive datasets, reaching terabytes, petabytes, and beyond. Thanks to the "scale-out" design, NoSQL databases comfortably manage vast amounts of data without issues. SQL databases, by comparison, often struggle with such extensive data sets due to limitations in hardware scaling and structured data constraints, making them less efficient for extremely large data scenarios.

Velocity is about throughput—handling massive amounts of simultaneous requests quickly and efficiently. NoSQL databases excel at processing high-velocity data streams, which is crucial for applications like social media feeds, real-time analytics, IoT applications, and more. SQL databases may experience bottlenecks due to their rigid schemas and transaction overhead, slowing down performance in high-throughput situations.

Variety emphasizes schema flexibility. You can utilize any of the schema forms mentioned previously or even choose a schema-less approach entirely. This schema flexibility means NoSQL databases can easily accommodate rapidly evolving data requirements, different data formats, and unstructured or semi-structured data like images, videos, and sensor data. Conversely, SQL databases are best suited for structured and consistent data that doesn't frequently change.

Let's explore more internal details between them.

• Transactions and ACID Compliance: SQL databases generally offer strong consistency and ACID (Atomicity, Consistency, Isolation, Durability) compliance. NoSQL databases often sacrifice strict ACID compliance for scalability and flexibility, adopting eventual consistency models.
• Complex Queries and Reporting: SQL databases excel in executing complex queries, and multi-table joins, and providing extensive reporting capabilities. NoSQL databases might require additional processing layers or specialized query mechanisms for complex analytical queries.
• Scaling Approaches: SQL databases typically scale vertically (adding more resources to a single server), while NoSQL databases scale horizontally (adding more servers), providing more flexibility and efficiency for handling large datasets.

Understanding these differences and key characteristics will help you choose the right database solution based on your specific requirements. The best measure for your application is your context. The application context defines which one is perfect for you.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Imagine you have a large database that stores customer orders, and you need to keep another system like a reporting dashboard — updated in real-time. Instead of repeatedly scanning the entire database for changes, which is inefficient, you can use Change Data Capture (CDC). CDC is a technique that tracks changes made to a database and ensures they are captured and passed on to other systems efficiently. It helps keep data in sync without causing a heavy load on the database.

Why Does CDC Matter?

• Reduces System Load: Instead of checking all records, CDC tracks only what has changed.
• Ensures Data Synchronization: Keeps different databases or applications in sync with real-time updates.
• Enhances Performance: Speeds up data processing and reduces unnecessary computations.
• Supports Real-Time Analytics: Enables event-driven architectures and live dashboards.

Different Types of Change Data Capture
There are multiple ways to implement CDC, and the right approach depends on your system’s needs. Let’s look at the common types:

1. Trigger-Based CDC
This method uses database triggers, which are special rules that get executed when data changes. Whenever a row is inserted, updated, or deleted, the trigger captures this change and logs it in an audit table.

When to Use:

• If your database does not support log-based CDC.
• When you need to maintain a detailed history of changes

Pros:

• Works even if your database doesn’t have built-in CDC features.
• Provides a complete history of data changes.

Cons:

• Can slow down database operations since triggers add extra processing.
• Increases database complexity with additional tables and logic.

2. Log-Based CDC
This approach reads the database transaction logs — the records of every change made to the database. Instead of modifying the database structure, it monitors changes at the system level.

When to Use:

• When you need real-time CDC with minimal performance impact.
• When dealing with high-volume transactional databases.

Pros:

• Has the least impact on database performance.
• Efficient for handling large data volumes.

Cons:
Requires access to database transaction logs, which not all databases allow.
More complex to set up and configure.

3. Timestamp-Based CDC
This method relies on a timestamp column (like “LastUpdated”) to identify changed records. When a query runs, it fetches all rows where the timestamp is newer than the last sync.

When to Use:

• If your tables already have a “Last Updated” timestamp column.
• When you need a simple CDC method without extra database configurations.

Pros:

• Easy to implement.
• No need for additional infrastructure.

Cons:

• Requires timestamps to be updated accurately, or changes might be missed.
• Not ideal for real-time processing, as it relies on scheduled queries.

4. Table Differencing (Snapshot-Based CDC)
In this approach, periodic snapshots of the entire table are compared to detect differences. Changes are identified by comparing the current state to a previous snapshot.

When to Use:

• When other CDC methods are not feasible.
• When working with small datasets where performance impact is minimal.

Pros:

• Works with any database, even those without CDC support.
• No need to modify the database structure.

Cons:

• Requires scanning the entire table, which can be slow.
• Not suitable for large datasets or real-time updates.

5. Hybrid CDC
A combination of multiple CDC methods to balance performance and accuracy. For example, log-based CDC might be used for real-time changes, while timestamp-based CDC acts as a fallback.

When to Use:

• When handling complex architectures with different data sources.
• When optimizing for both real-time and periodic data updates.

Pros:

• Offers flexibility to choose the best method per use case.
• Can improve reliability and accuracy.

Cons:
Requires a more complex setup and maintenance.

Conclusion
Choosing the right CDC method depends on factors like performance needs, database capabilities, and update frequency. Log-based CDC is preferred for real-time, high-volume systems, while timestamp-based CDC is a quick solution for simple use cases. Trigger-based CDC is useful when detailed change tracking is required, and table differencing can be a last resort when no other options are available. By selecting the right CDC approach, businesses can keep their data synchronized efficiently, enabling faster decision-making and better performance across applications.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL provides various numeric functions that help perform mathematical operations on numeric data. These functions are useful for calculations, rounding, and other numerical transformations.

Common Numeric Functions

• ABS(): Returns the absolute value of a number.
• CEILING(): Rounds a number up to the nearest integer.
• FLOOR(): Rounds a number down to the nearest integer.
• ROUND(): Rounds a number to a specified number of decimal places.
• POWER(): Returns the value of a number raised to a given power.
• SQRT(): Returns the square root of a number.
• EXP(): Returns the exponential value of a number.
• LOG(): Returns the natural logarithm of a number.
• LOG10(): Returns the base-10 logarithm of a number.
• RAND(): Returns a random float value between 0 and 1.
• SIGN(): Returns the sign of a number (-1, 0, or 1).
• PI(): Returns the value of PI (3.14159265358979).
• DEGREES(): Converts radians to degrees.
• RADIANS(): Converts degrees to radians.
• MOD(): Returns the remainder of a division.
• TRUNCATE(): Truncates a number to a specified decimal place.

Example Usage of Numeric Functions
1. Using ABS() Function
SELECT ABS(-15) AS AbsoluteValue;

2. Using CEILING() and FLOOR() Functions
SELECT CEILING(4.3) AS CeilValue, FLOOR(4.7) AS FloorValue;

Output

3. Using ROUND() and TRUNCATE() Functions
SELECT ROUND(123.456, 2) AS RoundedValue, TRUNCATE(123.456, 2) AS TruncatedValue;

Output

4. Using POWER() and SQRT() Functions
SELECT POWER(5, 3) AS PowerValue, SQRT(25) AS SquareRoot;

Output

5. Using MOD() Function
SELECT MOD(10, 3) AS ModResult;

6. Using PI(), DEGREES(), and RADIANS() Functions
SELECT
    PI() AS PiValue,
    DEGREES(PI()) AS DegreesValue,
    RADIANS(180) AS RadiansValue;

Output

When to Use Numeric Functions?

• Financial Calculations: Useful for interest rates, tax calculations, and rounding amounts.
• Data Analysis: Helps in statistical computations and mathematical transformations.
• Scientific Computing: Essential for performing complex mathematical calculations.
• Random Value Generation: Used for sampling, simulations, and random selections.

Advantages of Numeric Functions

• Simplifies mathematical computations in SQL.
• Enhances query efficiency by using built-in SQL functions.
• Provides precise and accurate results for calculations.

Numeric functions play a crucial role in SQL for performing various mathematical operations.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Depending on the input values, scalar functions in SQL return a single value. Instead of working with sets of rows, these functions work with individual values.

Common Scalar Functions
LEN(): Returns the length of a string.
UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.
ROUND(): Rounds a number to a specified decimal place.
GETDATE(): Returns the current date and time.

Example Usage of Scalar Functions

1. Using LEN() Function
SELECT LEN('Hello World') AS StringLength;

2. Using UPPER() and LOWER() Functions
SELECT UPPER('hello') AS UpperCase, LOWER('WORLD') AS LowerCase;

Output

3. Using ROUND() Function
SELECT ROUND(123.456, 2) AS RoundedValue

4. Using GETDATE() Function
SELECT GETDATE() AS CurrentDateTime;

5. Using ABS() Function
SELECT ABS(-25) AS AbsoluteValue;

6. Using SQRT() Function
SELECT SQRT(49) AS SquareRoot;

7. Using SUBSTRING() Function
SELECT SUBSTRING('SQL Functions', 5, 9) AS SubstringResult;

8. Using REPLACE() Function
SELECT REPLACE('Hello SQL', 'SQL', 'World') AS ReplacedString;

Advanced Use of Scalar Functions
1. Combining Scalar Functions
SELECT UPPER(LEFT('advanced scalar functions', 8)) AS Result;

2. Using Scalar Functions in Computations
SELECT ROUND(AVG(Salary), 2) AS AverageSalary FROM Employees;

3. Formatting Dates Using Scalar Functions
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

4. Custom Scalar Function Example
CREATE FUNCTION dbo.Getfullname(@FirstName NVARCHAR(50),
                                @LastName  NVARCHAR(50))
returns NVARCHAR(100)
AS
  BEGIN
      RETURN ( @FirstName + ' ' + @LastName )
  END;

Usage
SELECT dbo.GetFullName('John', 'Doe') AS FullName;

Advantages of Scalar Functions

• Helps in data formatting and transformation.
• Improves code readability and maintainability.
• Enhances query flexibility with built-in SQL functions.

Scalar functions are essential for manipulating individual values in SQL queries.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.