Code First. Column Order Annotation

Working with Code First Entity Model, you can set the order of columns in database table. It’s very easy – just add an annotation before property declaration.

But there are some interesting things about this annotation.
1. Order numbers are relative, they are not the exact places of the fields inside the database table. For example, you can set 1 for colA, 10 for colB, 5 for colC, and you will get the following order: colA, colC, colB.
2. If you don’t set order numbers for all columns, Visual Studio will place ordered columns first (according to order numbers), then the rest columns (without order numbers).
3. It works only at the moment when Visual Studio creates a table. If you change the annotation later, migration does not generate code for table recreation.

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

Auto-generated Guid in Entity Framework Code First

When you create an entity in Code First model, you might need a Guid field. In addition you might want SQL Server should auto-generate values for that field. This can be done with an annotation as following:

After migration you will find that ClientGuid field has a default value newsequentialid().

Auto-generated Guid in Code First

The most curious thing is the default value in SQL Server database could be created only at the creation of field itself. If you create a field without an identity option first, then you try to add annotation, SQL Server does not add a default value.

In that case you can recreate database (but it’s not appropriate in many cases) or to add a class constructor to explicitly assign new Guid value as following:

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

SQL Join With First Matching Rows. Choosing the Best Approach

It’s a very old task for SQL developers to write a query that will join two tables and will pick only first matches from the second table to every row from the first table. In my case I need to combine client name from Clients table and his/her first phone number from Phones table.

After making some investigations I stopped at three different queries.

1. Join with SELECT TOP 1 subquery

2. Using CROSS APPLY operator

3. Subquery with SQL Server Window function (ROW_NUMBER)

If any client doesn’t have a phone number, you need to make some slight modifications to the code above: change JOIN to LEFT JOIN, and CROSS APPLY to OUTER APPLY. In this case you’ll have a client name with corresponding NULL instead a phone number.

I won’t estimate the simplicity of the code and ease of understanding. The code that looks shorter might not be the most effective. We need to compare query costs and choose the least one.

And now SQL Server will show its magic. The percentage of each query costs are 50%, 49% and 1% (just look at the screenshot below).

Join with first matching rows

So the most effective is the last query that uses a join with a ranking subquery (SQL Server creates a temporary table here). This query also operates with a minimum number of pages to retrieve the result. You can switch on the I/O statistics (run SET STATISTICS IO ON command) and look at Messages tab in SSMS. In my case I have the following output:
(70347 row(s) affected)
Table 'Phones'. Scan count 70713, logical reads 215349...
Table 'Clients'. Scan count 5, logical reads 833...
Table 'Worktable'. Scan count 0, logical reads 0...
Table 'Worktable'. Scan count 0, logical reads 0...

(70347 row(s) affected)
Table 'Phones'. Scan count 70708, logical reads 213139...
Table 'Clients'. Scan count 1, logical reads 761...

(70347 row(s) affected)
Table 'Phones'. Scan count 5, logical reads 2210...
Table 'Clients'. Scan count 5, logical reads 833...
Table 'Worktable'. Scan count 0, logical reads 0...

Tip: If you would use a LEFT JOIN in the last query, don’t place a “row_num = 1” condition in the WHERE clause, only after JOIN … ON. If you place it in WHERE clause, SQL Server will make an left outer join, and then filter rows (all NULL values will be rejected here). So you will get an equivalent of inner join.