I consider myself as a quite experienced user of Nhibernate, that is why I decided to check what is going on on the other side of barricade and started playing with Entity Framework. I followed the steps of some tutorial and I stuck at the very beginning of it. I was about to generate model classes using “Entity Data Model Wizard” but it turned out that the wizard could not find my SQL server (despite the fact that server itself was up and running).
I remember having similar issue a while back, but at that time I wasn’t able to solve it (I ended up creating connections string manually). This time I wanted to give this issue a closer look. After a bit of digging I found the problem – the SQL server wasn’t broadcasting information about itself. In order to fix it and make the server visible to other computers and applications it is necessary to start SQL Server Browser service. To do that go to Start -> Run and type Services.msc
Then in newly opened window find SQL Server Browser service
Double click it and set up startup type to Automatic. You can also click Start button to make sure that service starts immediately
From now on SQL servers should be visible in “Entity Data Model Wizard”
SQL
T-SQL – syntactic sugar for updating current row in cursor
I am not a big fan of cursors, however for time to time I have to write one. In my case cursor usually is responsible for some complicated updates. Until yesterday I’ve been writing my update statements within the cursor the standard way
|
1 2 3 4 5 |
-- cursor code omitted for brevity -- some complicated update UPDATE Person.Person set FirstName = 'some logic goes here' where Person.BusinessEntityID = @Id where @Id variable is set by cursor. -- cursor code omitted for brevity |
There is nothing wrong with that, however we can do the same thing using specialized syntax – current of keyword(s)
|
1 |
UPDATE Person.Person set FirstName = 'some logic goes here' where current of personCursor |
The entire cursor statement then looks that way
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE personCursor CURSOR FOR SELECT top 1000 * FROM Person.Person OPEN personCursor FETCH NEXT FROM personCursor WHILE @@FETCH_STATUS = 0 BEGIN -- some complicated update goes here UPDATE Person.Person set FirstName = 'some logic goes here' where current of personCursor FETCH NEXT FROM personCursor END CLOSE personCursor DEALLOCATE personCursor |
T-SQL – executing query multiple times
For time to time during my everyday work I have to do some SQL query optimization. The simplest way of measuring whether my changes improved the performance or not, is simply measure the execution time of given query (I know that this is not the best way of doing that, but let’s leave aside execution plans for now). However measuring time of single executin is not very meaningfull. Thats why I like execute query multiple times by using this simple trick
|
1 |
GO 100 |
and then calculate an average time of execution by dividing the total time by execution count. As You can see, all You have to do is to specify execution count after the GO statment. Despite the fact that Management Studio indicates that there is an error in the SQL, this is perfectly valid code. Of course You should hide execution results in order to measure only the time of execution, not the time of rendering data. To do that, right click on query window and choose “Query Options…”
then select “Results” node and check “Discard results after execution”
