Renaming Database

Sometimes you need to rename a SQL Server database. Beside the database name itself, this name is usually a part of logical database file names, physical file names, maybe file groups.
To rename the database name you need:

  1. Bring database offline;
  2. Change paths to database files in system catalog;
  3. Rename physical file names using Windows Explorer;
  4. Bring database online;
  5. Change database name;
  6. Change logical file names (optionally).

Here is the script to do this. Be careful to put the correct logical database file names.

Drop Filestream In SQL Server

I have a table in SQL Server where one column is placed in Filestream filegroup. Now I need to delete this column and all files on disk.
Just recall the process to implement Filestream in SQL Server database:

  1. Add Filestream filegroup;
  2. Add file to a newly created Filestream filegroup;
  3. Create table where column is marked as FILESTREAM and you pointed FILESTREAM_ON to a Filestream group.

To drop Filestream you need to implement these steps in reverse order, but check Filestream columns, files and filegroups before.

Then do the following:

SQL Scalar Functions – MIN of Multiple Values

SQL Server built-in function MIN() is an aggregate function. It can find minimal value on a single column from many rows.
To find minimum of multiple columns you can write UDF or use a subquery.

Important! My examples works fine even with nullable values, i.e. they ignore null values and return a minimum only from concrete values. If all values are null, they return null.

1. Scalar function for two values

2. Scalar function for three values

It can be quite hard to write a function for more input values. You can use a subquery here.

Truncating DateTime In SQL Server

Sometimes we need to get only date from a datetime value, or truncate time to an hour or minute in SQL Server. There are some ways to do this:

  • casting;
  • manipulating with string conversion;
  • working on total number of years/months/days/hours in DateTime value.

Casting works fine for truncating to date.

You can convert datetime value to string (cast to nvarchar type), get a substring, and convert back to datetime. But it’s not efficient and can depend on string format (lond or short date format) or locale.

Another way is to calculate the number of years/months, etc. from a special date (DATEDIFF), then add this number of years/months to that special date (DATEADD).

Zero means the January 1, 1900.

Generating Date Sequence With SQL Query

The task is to create SQL query that will produce a date sequence starting from @startDate till @endDate including, i.e. 2017-01-01, 2017-01-02, 2017-01-03 and so on.

There is a couple of obvious solutions.

1. DATEADD() with integer sequence where the integer sequence is an increment

Please take care how many records are in sys.all_objects, it won’t generate a long sequence. If you need more, use a self cross join as shown in Generating Integer Sequence With SQL Query.

2. Recursive query

The last option is very important because SQL Server limits the recursion level to 100 by default.

Updating XML Data In SQL Server

Inspired by this excellent article Examples of using XQuery to update XML Data in SQL Server, I wrote a quick memo how to update XML data in SQL Server.

1. modify command can update only the first occurrence it finds. The command looks as following.

2. If you need to update all occurrences, make a loop to iterate all of them.

3. You can substitute a hard-coded path with a variable, but only for the last tag.

Source code for this example

Monitoring SQL Server Backups With DMV

I have a rather long-running backup that was started via Maintenance plan. So I have no output about the progress of a particular backup. In this case you can query DMV to get the information needed.

The output would be like this.


As you see, one backup is running while the others are pending.

Disabling/Enabling Constraints In SQL Server

It’s a short memo how to do this operations for one or many constraints, and for one or all tables in a database. It concerns both to ordinary check constraints and foreign keys.

1. One constraint

2. All constraints in one table

3. All constraints in all tables

For enabling constraint(s) change NOCHECK to WITH CHECK CHECK literally. “WITH CHECK” means that SQL Server should verify all existing rows for that constraint.

Generating Integer Sequence With SQL Query

Let’s imagine the situation where you had assigned numbers to accounting documents via identity mechanism and some numbers are skipped because these numbers were not committed. Now you want to find out which numbers are missing. This can be made by comparing numbers in database with an integer sequence (1, 2, 3 and so on) in a SQL query. So you need to generate this sequence by means of SQL Server.

There are many ways to do it, but I write a memo about two of them.
1. Source: Pro SQL Server Internals by Dmitri Korotkevich

He uses a series of CTE that generate 2^n integer values. I’ve modified his script a little to a more understandable form. You can generate from 2^17 (131 072) values and up to 2^32 (4 294 967 296) by placing N1…N5 in the N6 CTE.

2. Cross join rows from sys.all_objects
Source: Generate a set or sequence without loops – part 1 by Aaron Bertrand

This method could generate a few millions of rows (4, 5 or 7+ millions) because it depends on the total number of rows in sys.all_objects.

If you need to generate a smaller number of rows, just use SELECT TOP n statement to get n rows precisely.

List SQL Server Database File’s Sizes

Here’s a simple script for Microsoft SQL Server that list all databases, database file names and locations, and their sizes. In addition it shows recovery model and last backup LSN.