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.

Querying Entity Model. Part 16 – WHERE IN With Concrete Values

In one of my recent posts Querying Entity Model. Part 5 – Implementing WHERE IN Analogue I described how Entity Framework implements Contains() method. In that case I had one LINQ query and one T-SQL query.

As you know LINQ could assemble multiple statements and Entity Framework would generate one T-SQL query. I mentioned it in my post Querying Entity Model. Part 14 – Dynamic Where Clause. But you can break this behaivor (accidentally or by intention) if you would save one of the intermediate query results to a .NET memory structure like List.

Let’s take a look at this example.

EF would generate one T-SQL query when you will get the results from query161b.

But if you cast first LINQ query to List (simply add .ToList() at the end), EF will generate two separate T-SQL queries.

The most important thing is EF had generated the second query with concrete values that were passed from the first list. If the first query extracts another set of values, EF would generate another T-SQL query. In result SQL Server needs to compile the second query and save its execution plan in procedure cache.

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.

Querying Entity Model. Part 15 – Union

Entity Framework allows to gather two result sets into one large with a Union method. Union would make a unique list of items. A simple LINQ query could look like the following:

Entity Framework generates a bit strange T-SQL code with SELECT DISTINCT and UNION ALL instead of an obvious UNION operator.

This query do the same.

It’s more clear and concise, but if you look at execution plans there will no difference.

Installing IIS On Windows 10 Using DISM

After installing Windows 10 there arise a need to install Internet Information Services for ASP.NET 4.6 development. When you use “Turn Windows feature on or off” in Control panel, Windows tries to download necessary files from Windows Update. In some cases it does not work. So I have to use offline method with DISM tool.
TechNet offers a good description in this article Enable or Disable Windows Features Using DISM. You need to work only Online. Offline means to prepare an image from which you will install OS later.

1. Open Command Prompt with Administrator rights.
2. You can view a list of all features installed on your Windows OS.

3. Install IIS-DefaultDocument feature with all related features by using /All switch.

It installed 15 extra features in addition.

4. Install IIS-ASPNET45 feature the same way.

5. Install any additional IIS feature one by one.