Cannot drop database because it is currently in use error

We might get following error when we try to drop a database in SQL Server:

"Cannot drop database 'MyDatabaseName' because it is currently in user".

To resolve this error, alter the database first before deleting the database like below..

USE [master]

GO

ALTER DATABASE [MyDatabaseName] set single_user with rollback immediate

DROP DATABASE [MyDatabaseName]

GO

SQL: Deleting duplicate records from table

Here is one way to delete duplicate records from a table:

Consider we have some duplicate records in TempTable which has a unique identifier column called "RowId".

delete from TempTable
where RowId not in
(
select min(ROWID)
from TempTable
group by column1, column2,...
)

For each duplicate set of duplicate records, the above query retains one row with minimum RowId and deletes all other duplicate records. If you want to retain row with maximum RowId and delete all other duplicate records, then just replace the min() method with max().

SQL Error: None of the result expressions in a CASE specification can be NULL

I got this error when my CASE statement in SQL returned NULL and did not have any ELSE section in the CASE statement: "None of the result expressions in a CASE specification can be NULL"

The CASE statement which resulted in error was like this:

SELECT 

CASE WHEN StudentId = 0 THEN null END

I wanted to return NULL when StudentId = 0; else return the StudentId.

The above query did not work without the ELSE part. After adding the ELSE section like below the query executed without error:

SELECT 

CASE WHEN StudentId = 0 THEN null ELSE StudentId END

SQL Script to Create a Database

Note: You will need to modify lines 2 and 4 to adjust the file paths for the data and log files to land the database on your files system wherever you want it. That should be the only modifications needed.

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

CREATE DATABASE [MyDatabase] ON PRIMARY
( NAME = N'MyDatabase_data', FILENAME = N'C:\SQL\Data\MyDatabase.mdf' , SIZE = 1048576KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MyDatabase_log', FILENAME = N'C:\SQL\Log\MyDatabase.ldf' , SIZE = 220160KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MyDatabase', @new_cmptlevel=90
GO

ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyDatabase] SET ARITHABORT OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyDatabase] SET READ_WRITE
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM
GO
USE [MyDatabase]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO