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 update_xml.zip

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.

View Instance and Database Versions

You can get the following characteristics about your SQL Server:

  • product version (for example, 12.x for SQL Server 2014)
  • product level (RTM, Service Pack or CTP)
  • edition (standard, enterprise, developer, etc.)

Product Version Product Level Edition
-------------------- -------------------- ------------------------------
12.0.2495.0 RTM Developer Edition (64-bit)

(1 row(s) affected)

The full list of server properties can be seen in MSDN article SERVERPROPERTY (Transact-SQL)

For the database you can get the current internal version number and compatibility level. With some extra effort you can get starting database version number, i.e. the version at which the database was created originally.

Internal Version Number
------------------------------
782

(1 row(s) affected)

COMPATIBILITY_LEVEL
-------------------
120

(1 row(s) affected)

To see the starting internal database number you should use DBCC DBINFO command.

DBINFO STRUCTURE:

DBINFO @0x000000000E29D8E0

dbi_version = 782 dbi_createVersion = 661 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
...

Look at the first line starting with dbi_. The dbi_version is the current internal database version number (782 is SQL Server 2014), the dbi_createVersion is that of the moment of database creation (661 is 2008 R2). The list of these numbers can be found at this post.

List Row Count on All Tables in SQL Server Database

If you are using SQL Server 2008 or higher you can query the sys.partitions catalog view. The filed [rows] shows the number of records in each partition of the database table.

This query returns the schema and table names, and total number of rows. If you have only one partition for each table, you can wipe out SUM() and GROUP BY from the query.