SQL Server – Page 50

SQL Server SHOW TABLES Equivalent

February 22, 2022 by Ian

Every now and then I find myself typing SHOW TABLES in SQL Server, expecting to get a list of tables.

That would make perfect sense if I was using MySQL or MariaDB. But SQL Server/T-SQL doesn’t have a SHOW TABLES statement like MySQL or MariaDB, so it never works. And I keep forgetting. But fortunately, SQL Server does have alternatives.

Here are five options for getting a list of tables in SQL Server. These can be used whenever you’re trying to find that elusive SHOW TABLES statement in SQL Server.

Fix Msg 512 “Subquery returned more than 1 value” in SQL Server

September 17, 2024February 21, 2022 by Ian

If you get error Msg 512 that reads “Subquery returned more than 1 value…” in SQL Server, it’s because you’re using a subquery that returns more than one value in a scenario where this is not allowed.

5 Ways to Select Rows with the Minimum Value for their Group in SQL

July 21, 2023February 16, 2022 by Ian

Here are five options for using SQL to return only those rows that have the minimum value within their group.

These examples work in most major RDBMSs, including MySQL, MariaDB, Oracle, PostgreSQL, SQLite, and SQL Server.

3 Ways to Select the Row with the Minimum Value in SQL

February 15, 2022 by Ian

Here are three examples of using SQL to find and select the row with the minimum value in a given column.

The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.

7 Ways to Find Duplicate Rows in SQL Server while Ignoring any Primary Key

July 23, 2023February 11, 2022 by Ian

Here are seven options for finding duplicate rows in SQL Server, when those rows have a primary key or other unique identifier column.

In other words, the table contains two or more rows that share exactly the same values across all columns except for its unique identifier column.

4 Functions to Format a Number to 2 Decimal Places in SQL Server

February 9, 2022 by Ian

When using T-SQL with SQL Server, we can format numbers using various methods, depending on our desired format.

Below are four functions that can be used to format a number to two decimal places in SQL Server.

4 Ways to Check for Duplicate Rows in SQL Server

July 23, 2023February 8, 2022 by Ian

Here are four methods you can use to find duplicate rows in SQL Server.

By “duplicate rows” I mean two or more rows that share exactly the same values across all columns.

5 Ways to Select Rows with the Maximum Value for their Group in SQL

July 21, 2023February 4, 2022 by Ian

Here are five options for using SQL to return only those rows that have the maximum value within their group.

These examples work in most major RDBMSs, including MySQL, MariaDB, Oracle, PostgreSQL, SQLite, and SQL Server.

3 Ways to Select the Row with the Maximum Value in SQL

February 2, 2022February 2, 2022 by Ian

Here are three examples that use SQL to find and select the row with the maximum value in a given column.

The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.

4 Ways to Check if a Table Exists Before Dropping it in SQL Server (T-SQL)

January 31, 2022 by Ian

Dropping a table in SQL easy. You simply use DROP TABLE myTable where myTable is the name of the table you want to drop. You can use this method to drop a table in SQL Server via T-SQL script.

But you’ll get an error if the table doesn’t actually exist. That is, unless you check for the existence of the table first.

Below are four ways of using T-SQL to check whether the table exists before dropping it.