A great and simple project that saves a lot of time for SQL Server Development: ScriptDB. Unfortunately have not been updated for 4 years already. And out of the box does not work with SQL Server 2008 and SQL Server 2012.

I have took the liberty to compile the project (thanks to the open-source!) against the newer libraries and now it works for SQL Server 2008 and SQL Server 2012.

ScriptDB for SQL Server 2008 and 2012

SQL Query to do multiple sums

Sometimes you need to do a report with totals column and with totals that depend on some condition, like
Product, Total Sales this year, total sales this month.
Use SUM(Case…) condition, like here:

SELECT  SUM(CASE WHEN order_date >= '01/01/09' THEN quantity ELSE 0 END) AS items_sold_since_date,
        SUM(quantity) AS items_sold_total,
FROM    Sales
GROUP BY product_ID

Credit goes to Joel Coehoorn

Another way to do Sum:

  COUNT(distinct case when Status.Complete = 1 then Status.ID else NULL end) as [Count Of Complete Status],

Alternative to cursor in SQL Server

You can use temp table in the same manner as you use SQL Server cursor

create table #temp (
    value1 int,
    value2 int

    @SomeValue1 int,
    @SomeValue2 int

insert into #temp (
from realTable
--where some condition

select top 1 
    @SomeValue1 = value1, 
    @SomeValue2 = value2
from #temp

-- while we have rows in the table, do work with them.
while @SomeValue1 is not null 

    -- Do something with value1 and value2

    -- delete the row we have just worked with
    delete from #temp where value1=@SomeValue1 and value2=@SomeValue2

    -- don't forget this bit!! otherwise you'd be stuck in the infinite loop, 
    -- as variable is not reset to null on empty table
    set @SomeValue1 = null  

    -- get the next row  in the table
    select top 1 @SomeValue1=value1, @SomeValue2 = value2
    from #temp


-- here we are done, can drop the temp table
drop table #temp

Probably you would benefit from adding @ID uniqueidentifier to temp table, in case you have repeating data, and delete rows from temp table based by the ID value

EF 4.3 Database Migration Commands Cheatsheet

Code-Based Migrations


Add-Migration Рscaffold next migration based on changes made to the model.
Update-Database – update database model to the latest migration model
Update-Database -Verbose – update database model and output SQL generated
Update-Database -TargetMigration: “someMigration” – update database up (or down) to a “revision”
Update-Database -Script -SourceMigration:$InitialDatabase -TargetMigration:”AddPostAbstract” Generate SQL script of the migrations
SQL(“update sometable set somefield=’hi'”) – can insert custom SQL into migration class.

Automatic Migrations

Original here:

Enable-Migrations -EnableAutomaticMigrations -Force – turn on automatic migrations

Basically does the same as code-based migrations, but before doing Database-update it automatically calls Add-Migration and gives it a name.

When you use Data Definition Language (DDL) to modify a table, and then you try to save the table in Microsoft SQL Server 2008, you may receive the following message:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To change the Prevent saving changes that require the table re-creation option, follow these steps:

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options.
  3. In the navigation pane of the Options window, click Designers.
  4. Select or clear the Prevent saving changes that require the table re-creation check box,  and then click OK.


SQL Server connection validation

I run into a problem where I tried to connect to a fresh installation of SQL Server 2008 and could not do it for no apparent reason. But it turns out that SQL Server 2008 is shipped with TSP/IP disabled and nobody can connect to it via network, even from localhost.
Here is the solution:

You can check in registry this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\Enabled
It must be set to 1 for SQL Server to be listening for network connection.

Run this on master database to see error log on the server:
exec Go Xp_readerrorlog
You should look for
SQL server listening on X.X.X.X: Y
This will be an indicator if network connections are allowed in the Server