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

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.

Extracting DACPAC From SQL Server Database With PowerShell

Working with SQL Server Data Tools I’ve faced with a demand to extract data-tier application many times. One of the appropriate solution is to use PowerShell script to extract the database to .dacpac file. Thanks to Gianluca Sartori, he gave a good script to extract DACPAC from all databases (see here).

Based on his script, I’ve did some modification to make it work with SQL Server 2014. I’ve found that I have 3 versions of sqlpackage.exe (110, 120 and 130). So the script looks for the latest one and run it.

Here is the final script:

To execute this script, run SQL Server PowerShell (sqlps) and type one of the following commands (they are identical, but use slight different syntax):

  • extract_dacpac.ps1 "(local)" Clients
  • extract_dacpac.ps1 -server "(local)" -database Clients

If everything is OK, you’ll see the progress of execution.
Extract DACPAC from database

PowerShell script can downloaded as zip file from this link extract_dacpac

Listing Columns, Keys and Indexes For Specific Table

Now I’m investigating Entity Framework and how it creates tables and other database objects. It’s surprising that if you have a reference (foreign key) in your model, EF creates an index in the referenced table during the migration process by default. So, if I have just 5 distinct referencing values with a roughly equal quantity of referenced records (in this case about 20% for each value), SQL Server will never use that index.
To put the creation of database objects under control, I need a simple script to see what objects are really created after migration. There are 3 queries in the script that list the following information:

  • columns, their data types and null/not null information
  • primary/foreign keys, and referencing table and columns
  • clustered/non-clustered indexes and columns

Finding References (Foreign Keys) To This Table

Occasionally I’ve faced with the task to change the value of the primary key, say, in Employee table in SQL Server database. Everything would be OK if this table is not referenced from the other tables. So when I change the value in primary key EmployeeId column, I would break the foreign key constraints.

When you create a table you can specify a REFERENCES … ON UPDATE CASCADE clause, so all changes in primary key would be propagated to the underlying tables (look here for CREATE TABLE syntax and here for referential integrity constraints). But this is not my case, I have the default NO ACTION option. I want to know all tables and columns that are referencing my primary key. This can be done by one of the three ways:

  1. stored procedure

    UPDATE 2016-02-01:
    If a table is not in a dbo schema, add a second parameter for owner.
  2. using system views from sys schema
  3. using INFORMATION_SCHEMA views

These methods work perfectly when you’ve set foreign keys. There may be a situation when you have a logical reference, but it’s not implemented as a constraint. You can try to search for a column name. Maybe you will be lucky if you have strong naming conventions and you include PK column name in FK column name, i.e. primary key is EmployeeId, so the foreign key will be BossEmployeeId or ManagerEmployeeId. If you don’t follow this rule, you might have simple name like ManagerId and it will be more harder to guess where the reference might be.

This query could help you to find tables by column name.

UPDATE 2016-04-13:
Modified version of the above query that include schema name.

Table Value Constructor in T-SQL aka SELECT FROM VALUES

SQL Server 2008 had introduced one interesting feature that helps to create “inline” tables. It’s a table value constructor. It is commonly used when you need to insert few values or few rows of values into the table. Something like this:

But there’s another case where you can use this kind of tables. For example, you need to update few records, you know their IDs and values to be put to table. Here you can write your update like the following code:

In this update command I join table Employee from database with “inline” table T that was constructed in-flight from 4 pairs of values. It’s much easier to write one command, especially if it’s rather complicated, than to repeat it again and again.

Update 2015-12-10
You can even create a temporary table with table value constructor like this.