SQL Server 2025 Express Edition Download, Install and Configure

 Problem

I heard there is a new version of SQL Server Express Edition with the 2025 release. What is new in this edition? Is it hard to install and configure SQL Server 2025 Express Edition?

Solution

In this tip, we will show how to download, install, and configure SQL Server 2025 Express Edition.

What is SQL Server 2025 Express Edition?

SQL Server 2025 Express Edition is the new SQL Server free version that can be used in production environments. In SQL Server, you have several SQL Server editions for Windows (there are Linux installers as well). The most feature rich version for production is Enterprise Edition. The Express Edition has limitations, but it is free. You cannot take advantage of all the features available in the Enterprise and Developer editions, but it is a cost-effective database the companies use for particular use cases.

Why would I use the SQL Server Express Edition?

SQL Server Express Edition is a great free edition with several features that you can use in production for small or distributed workloads. Also, you can start to learn about the database engine with this version, and after some time, upgrade to a more sophisticated edition such as Standard or Enterprise Edition.

How to download SQL Server Express Edition 2025?

First, download the SQL Server edition from the following URL: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Secondly, click the Download now option under the SQL Server 2025 Express edition title.

This option will download an installer named SQL2025-SSEI-Expr.exe.  This file is the installer we will use to install the SQL Server Express edition.

Step-by-Step Guide to install SQL Server Express Edition 2025

To install SQL Server Express edition, follow these steps.

First, run the SQL2025-SSEI-Expr.exe.

Second, select the installation option.

The basic configuration will install with the default features and is the faster option. The Custom installation is used to select the features to install. We will use this section to customize our installation. Use the Download Media option if you just want to download the software, but you want to install it later.

Third, you have to select the location to install SQL Server. Press the Browse button if you want to install in a different path than the default one. Once selected, press the install button.

After that, the installer will download the installation package.

SQL Server Installation Center

After downloading the SQL Server Express Edition 2025, a SQL Server Installation Center will pop up. First, click the New SQL Server standalone installation or add features option.

Read and accept the license terms and if you agree proceed with the installation.

Check for updates and press Next.

The installer will check whether your machine meets the requirements for installation. It will check the registries, the ports, and the Firewall configuration. In addition, it will verify if the .NET Framework 4.7.2 or newer is installed.

Installation Type

In addition, you will have the installation type.

You have 2 options here:

• To perform a new installation (or scenario).
• To add features to an existing instance – when you already have SQL Server Express 2025 installed and you want to install additional features not installed at the beginning.

Additionally, you can connect to Azure. In this tip, we will focus on an on-premises SQL Server Express install. If you want to learn more about Azure, refer to this link.

Feature Selection

At this stage, we have the features. Let me explain each one:

• The Database engine is the core of SQL Server to create the database and run queries, control transactions, and security, etc.
• Secondly, we have the SQL Server Replication. It is used to replicate data and database objects from one database to another database on a different server or instance.
• AI Services and Language Extensions are to run Python code or R in T-SQL.
• Full-text and Semantic Extractions are used to create special indexes to search and look for data in text columns or binary data in a database.
• Also, we have PolyBase. It is used to query external data as local tables. For example, you can connect to data in Teradata, MongoDB, or access Parquet, JSON files, and query as if they were local tables.
• Finally, we can install the LocalDB. This is a light database designed for developers to test features and functionality. Additionally, you can select the root directory of your instance.

Once done, you can add an instance name for your instance. Press Next after entering the instance name.

Server Configuration

In the Server Configuration window, you can verify the Accounts created for the service and check the Startup Type for the services. Here you have an explanation for each option:

• Automatic: Starts every time Windows boots and is available automatically.
• Manual: Stays stopped at startup and only starts if a user starts it manually.
• Disabled: It doesn’t start at all, even if a program or a user tries to start it, until it is manually enabled.

The Grant Perform Volume Maintenance Task Privilege is used to increase the performance of creating databases and expand the datafile expansion. In several cases, it can increase the database restoration time and the autogrowth.

However, some data can be exposed with this option. For more information about this topic, refer to this link.

Database Engine Configuration

Following this step, we have the Database Engine Configuration window. You can specify if you want to log in using Windows authentication or Mixed mode (SQL Server logins and Windows logins as well). For security reasons, Windows authentication is the recommended option. You can also create a system administrator (sa user) and set up the password. This option is not recommended either for security reasons.

For more information about Windows authentication and Mixed mode, and as a sa user, refer to these links:

• How to check SQL Server Authentication Mode using T SQL and SSMS
• When not to use the sa password in SQL Server applications

Also, you have the chance to add or remove Administrator users for the database.

After that, you can select the path for the following directories:

• First, the data root directory is the root of all the files (data files, log files, backups, configuration files and folders).
• Secondly, the system database directory is used to store the system databases, like the master, model, tempdb, and msdb.
• Thirdly, we have the user database directory. This is the path where the user data files are created by default.
• Also, we have the database log file directory. This is where the transaction log files are stored. The logs store the transaction information about the transactions in SQL Server, like insert, update, and delete operations.
• Finally, we have the backup directory. We store the backups in that directory.

TempDB Setup

The TempDB page is used to configure the Temporal database (tempdb).  It can be used for operations that require a large amount of memory, also to create temporary tables.

You can configure tempdb during the installation to increase the database performance. For more information about the tempdb performance

The memory page is used to allocate the maximum and minimum amount of memory to SQL Server. For more information about recommended settings

We also have a tab for User Instances, which allows users to run a separate instance of the database. The Filestream is used to store non-structured data like documents and images.

After that, press Next, and the installer will run the package to install the components and options selected.

If everything is OK, a Complete message will be displayed.

A restart message will be displayed.

At this point, SQL Server should now be installed and ready for use.

SQL Express 2025 Edition Limitations

SQL Server Express Edition only supports databases up to 50 GB, whereas Enterprise Edition supports databases up to 524 PB. Also, SQL Server Express Edition supports 1 socket or 4 cores.

The Maximum memory for the buffer pool per instance is 1410 MB. In addition, SQL Server Express Edition does not support Always On Failover Clusters or Availability Groups.

Beaver's functionality

 The errors you're encountering are due to differences in the SQL functions supported by Oracle and SQL Server. The LEFT and ISNULL functions are specific to SQL Server, and Oracle does not recognize them, which is why you're getting the ORA-00904: "LEFT": invalid identifier error.

Solutions:

Use Oracle-Compatible Functions:

For LEFT, use Oracle's SUBSTR function:

SELECT COLA, SUBSTR(COLB, 1, 10) FROM TABLEA;

For ISNULL, use Oracle's NVL function:

SELECT COLA, NVL(COLB, 'default_value') FROM TABLEA;

Transform Data in SQL Server:

If you can migrate the data without transformations, you can use the SQL Server-specific functions like LEFT and ISNULL after the data has been moved.

DBeaver Script Configuration:

If you want to write cross-platform scripts, you can use DBeaver's functionality to define SQL scripts for each database type, ensuring the correct functions are used for each.

If you need to run these functions as part of the migration process, you'll have to use Oracle-compatible syntax until the data is in SQL Server.

Removing duplicate rows from a large table with millions of records

 Removing duplicate rows from a large table with millions of records can be challenging, but it can be done efficiently using SQL. Here's a step-by-step guide to remove duplicates from a table in SQL Server:

1. Identify Duplicates:

First, you'll need to identify what constitutes a "duplicate." Typically, this means all columns (except for the primary key or a unique identifier) are the same.

2. Create a Backup:

Before making any changes, it's good practice to create a backup of your table.

 
SELECT * INTO YourTable_Backup
FROM YourTable;

3. Remove Duplicates Using a CTE (Common Table Expression):

The most common and efficient way to remove duplicates is to use a CTE combined with the ROW_NUMBER() function. Here's how you can do it:

 
WITH CTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Column1, Column2, Column3 ORDER BY (SELECT NULL)) AS RN
    FROM 
        YourTable
)
DELETE FROM CTE
WHERE RN > 1;

• Explanation:
  • The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.
  • The PARTITION BY clause divides the result set into partitions to which the ROW_NUMBER() function is applied.
  • The ORDER BY (SELECT NULL) clause simply ensures that the rows are arbitrarily ordered within each partition.
  • Rows with RN > 1 are considered duplicates and are deleted.

4. Verify the Results:

After running the deletion, it's essential to verify that duplicates have been removed and that the table integrity is maintained.

 
SELECT Column1, Column2, Column3, COUNT(*)
FROM YourTable
GROUP BY Column1, Column2, Column3
HAVING COUNT(*) > 1;

This query should return no results if all duplicates have been successfully removed.

5. Rebuild Indexes and Update Statistics:

After removing duplicates, it's a good idea to rebuild any indexes on the table and update the statistics.

 
ALTER INDEX ALL ON YourTable REBUILD;
UPDATE STATISTICS YourTable;

6. Considerations for Large Tables:

For very large tables (like your 260 million rows), you might need to remove duplicates in smaller batches to avoid long transaction times or transaction log growth. This can be done using a loop or by deleting rows in chunks.

Example:

 
WHILE EXISTS (SELECT 1 FROM CTE WHERE RN > 1)
BEGIN
    DELETE TOP (10000) FROM CTE WHERE RN > 1;
END

7. Monitor and Optimize:

Monitor the process and optimize as needed, particularly in a production environment where long-running queries could impact performance.

Conclusion:

By following these steps, you should be able to efficiently remove duplicate rows from your large table in SQL Server. The key is to use a method that minimizes the impact on your server and ensures the integrity of your data.

Partitioning a table in SQL Server that has a primary key referenced by foreign keys in other tables

 Partitioning a table in SQL Server that has a primary key referenced by foreign keys in other tables can indeed be challenging due to the constraints involved. Your approach of dropping and recreating constraints is a common solution, but here are a few considerations that might simplify the process or make it more efficient:

1. Use SWITCH Instead of Dropping and Recreating Constraints:

If you are partitioning for performance reasons, consider using the SWITCH statement. You can create a new partitioned table with the desired schema, and then use ALTER TABLE SWITCH to move data between tables. This avoids dropping foreign key constraints but requires more setup and might need the table to be empty when switching.

• Step-by-Step:
  1. Create a new partitioned table with the same schema as the original table.
  2. Use INSERT INTO ... SELECT to move data to the new table.
  3. Use ALTER TABLE SWITCH to switch the tables.
  4. Drop the old table and rename the new one.

2. Temporarily Disable Constraints:

SQL Server allows you to disable foreign key constraints temporarily, which might help in avoiding the need to drop them.

• Step-by-Step:

  1. Disable the foreign key constraints on the dependent tables.
  2. Drop the primary key constraint and clustered index.
  3. Partition the table and recreate the primary key constraint on the partitioned table.
  4. Re-enable the foreign key constraints.

• Example:

   
  ALTER TABLE [DependentTable] NOCHECK CONSTRAINT [FK_Name];
  -- Drop and recreate the primary key and clustered index
  ALTER TABLE [DependentTable] CHECK CONSTRAINT [FK_Name];
  

3. Using Schema Modification with Minimal Downtime:

If downtime is a concern, consider using techniques like online index creation and schema modification that might reduce the impact on the application.

• Online Index Creation: SQL Server Enterprise Edition supports creating and rebuilding indexes online, which might reduce the impact during partitioning.
• Schema Modifications: You could stage the new partitioned table while keeping the original table intact, then switch over with minimal downtime.

4. Consideration for SQL Server Version:

If you're using SQL Server 2016 or later, take advantage of improvements in partitioning and index creation features, like support for more efficient operations on partitioned tables.

5. Using a Maintenance Window:

Since the process involves significant changes, performing this operation during a maintenance window might be the best option, even if it means temporarily disabling or dropping constraints.

6. Documentation and Backup:

Document each step carefully and ensure you have a full backup before proceeding. This will help in case anything goes wrong during the process.

Conclusion:

Unfortunately, there isn’t a way to completely avoid the process of dropping and recreating constraints when partitioning a table that is heavily referenced by foreign keys. However, depending on your specific environment and requirements, the alternatives like SWITCH, temporarily disabling constraints, or using online operations might make the process smoother and less disruptive.

 

Check all indexes in all table in Database in MS SQL Server

 USE YourDatabaseName; -- Replace with your actual database name

-- Query to retrieve index information
SELECT 
    TableName = t.name,
    IndexName = ind.name,
    IndexType = CASE ind.index_id 
                    WHEN 0 THEN 'Heap (No Clustered Index)'
                    WHEN 1 THEN 'Clustered'
                    ELSE 'Nonclustered'
                END,
    ColumnName = col.name,
    ColumnPosition = ic.key_ordinal
FROM 
    sys.indexes ind
INNER JOIN 
    sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN 
    sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN 
    sys.tables t ON ind.object_id = t.object_id
WHERE 
    ind.type_desc <> 'Heap' -- Exclude heap tables (tables without clustered indexes)
ORDER BY 
    t.name, ind.name, ic.key_ordinal;

Explanation:

1. USE YourDatabaseName: Replace YourDatabaseName with the name of your SQL Server database to switch to that database context.

2. sys.indexes: This system view contains information about indexes in the database.

3. sys.index_columns: This view provides details about the columns that are part of each index.

4. sys.columns: This view gives information about columns in tables.

5. sys.tables: This view contains information about tables in the database.

6. SELECT: Retrieves the table name (t.name), index name (ind.name), index type (ind.index_id), column name (col.name), and column position (ic.key_ordinal).

7. CASE statement: Checks the ind.index_id to determine the type of index (clustered, nonclustered, or heap).

8. WHERE: Filters out heap tables (ind.type_desc <> 'Heap') because they don't have traditional indexes.

9. ORDER BY: Orders the results by table name (t.name), index name (ind.name), and column position (ic.key_ordinal).

Notes:

• Make sure to replace YourDatabaseName with the actual name of your database.
• This query provides comprehensive information about all indexes in the database except for heap tables.
• Running this query requires appropriate permissions to access the system views (sys.indexes, sys.index_columns, sys.columns, sys.tables).

By executing this query in SQL Server Management Studio (SSMS) or any other SQL query tool, you can obtain a detailed list of indexes and their configurations across all tables in your database.