Tuesday 22 December 2009

SSMS handy short cut keys

In Microsoft SQL Server Management Studio, its handy to have some short cut keys to do common tasks.

One handy one I always use is to "select top 100 * from " as I like to highlight a table name and press CTRL+4 to get the top 100 rows instantly.

To set this up, go to Tools->Options->Keyboard. And type "select top 100 * from " without the quotes and making sure you add a space after the from. Very important to leave a space at the end.

Highlight a table and press Ctrl+4 and the top 100 rows will be shown.
SSMS will run your command and append the highlighted text. If your table is called USERS, it would run "select top 100 * from USERS". If you forgot to leave a space at the end, it would try and run "select top 100 * fromUSERS" which won't work.

Tuesday 1 December 2009

table-values functions. Make sure you write them as inline.

Here is a multi-line statement (slow):


CREATE FUNCTION dbo.fnSomeReport
(
@someParam INT,
@field_company_id INT = NULL
)
RETURNS @result TABLE
(
[field_company_name] VARCHAR(255),
[engineer] VARCHAR(100),
[sku] VARCHAR(30)
)
AS
BEGIN
INSERT INTO @result
(
[field_company_name],
[engineer],
[sku]
)
SELECT field_company_name, engineer, sku
FROM ...
WHERE ID > someParam
AND (@field_company_id IS NULL
OR
field_company_id = @field_company_id)

RETURN
END
GO



Now as an inline statement (fast)

CREATE FUNCTION dbo.fnSomeReport
(
@someParam INT,
@field_company_id INT = NULL
)
RETURNS TABLE AS
RETURN
(
SELECT field_company_name, engineer, sku
FROM ...
WHERE ID > someParam
AND (@field_company_id IS NULL
OR
field_company_id = @field_company_id)
)
GO


Right then whats going on and what the hell is all the fuss about anyway?
The query optimizer expands the function as if it was a macro, and generates the plan as if you had provided the expanded query. Thus, there is no performance cost for packaging a SELECT statement into a table-valued function. For this reason, when you want to reuse a stored procedure that consists of a single SELECT statement, rewriting it into an inline UDF is without doubt the best choice. (Or instead of rewriting it, move the SELECT into a UDF, and rewrite the existing procedure as a wrapper on the function, so that the client is unaffected.)

Why is the multi-line function bad?
A multi-statement function has a body that can have as many statements as you like. You need to declare a return table, and you insert the data to return into that table.

You use multi-statement functions as inline functions, but in difference to inline functions, they are not expanded in place, but instead it's like you would call a stored procedure in the middle of the query, and return the data in a temp table. This permits you to move more complex stored procedure into functions.

Compared to inline functions, multi-statement functions incur some overhead due to the return table. More important, though, is that if you use the function in a query, the optimizer will have no idea of what the function returns, and will make standard assumptions. Many times this is not a real issue, but particularly if the result is huge, it can be.

In most cases, you want to avoid the use of scalar UDFs in SELECT, WHERE, GROUP BY and ORDER BY clauses. These will essentially force the optimizer to execute the function once for each row in the query, and depending on the complexity of the function, this could bring your system to its knees.

A view does not accept parameters. To restrict the rows returned by a view, you use a WHERE clause in the query that is accessing the view, not in the view itself. Table-valued functions, are more like parameterized views; in fact, they were called parameterized views during development, according to Hal Berenson.

Inline table-valued functions - These are better than scalar functions. Some will say they are preferential over stored procedures because they can easily be used in JOINs and regular SELECTs; some will say they are preferential over views because they can be parameterized. In this way, they behave just like Access parameterized views, with the added benefit that a query plan can be cached.

Other links:

Tuesday 17 November 2009

PLINQO - Get it, use it, love it

I've stumbled across a LINQ to SQL extension called PLINQO freely downloadable at http://plinqo.com/ and includes source code.

PLINQO is a set of CodeSmith templates that generates code for you around your LINQ to SQL DBML file. It generates Manager classes and Query classes automatically (it does this by looking at your primary keys, indexes and foreign key constraints).

You must buy CodeSmith, which is the template engine, but PLINQO is free.

Its actually quite amazing and saves you tons of time and energy creating boiler plate code. So glad I found it, now I want you to investigate it too. Go check out the video tutorials to see what I mean. Awesome.

Thursday 12 November 2009

Virtual Machine Key Combinations with Hyper-V

Taken from here and kept for prosperity incase the information was ever lost. So all credit for the following information goes to Ben Armstrong.

Standard Windows Key combination Virtual Machine Connection Key Combination Explanation
CTRL + ALT + DEL CTRL + ALT + END Displays the Task Manager or Windows Security dialog box on Windows (or logs in).
ALT + TAB ALT + PAGE UP Switches between programs from left to right.
ALT + SHIFT + TAB ALT + PAGE DOWN Switches between programs from right to left.
ALT + ESC ALT + INSERT Cycles through the programs in the order they were started.
CTRL + ESC ALT + HOME Displays the Windows Start menu.
N/A CTRL + ALT + PAUSE Changes the Virtual Machine Connection window to / from full screen mode.
N/A CTRL + ALT + LEFT ARROW Releases mouse and keyboard focus from the Virtual Machine Connection window.

Some extra things to know about Virtual Machine Connection key combinations:

  • By default the standard Windows key combinations do not get sent to the virtual machine, unless you are in full screen mode. You can change this so that they are always sent to the virtual machine (if the Virtual Machine Connection has focus) by going to the Hyper-V Manager and selecting Hyper-V Server Settings... and then Keyboard and selecting the Use on the virtual machine option. I always enable this setting.
    • Note: CTRL + ALT + DEL will always go to the physical computer - so you need to use CTRL + ALT + END no matter what you select for a setting here.

  • You can change the release key combination (CTRL + ALT + LEFT ARROW) by going to the Hyper-V Manager and selecting Hyper-V Server Settings... and then Release Key and selecting one of the options from the drop down (I usually change my release key combination to be CTRL + ALT + SHIFT as I find it easier to type).

  • If you use the Virtual Machine Connection under an existing Remote Desktop Connection (not recommended - but I do it all the time) the Remote Desktop Connection will grab all of these key combinations before the Virtual Machine Connection gets to see them (even the release key combination). To deal with this you will need to change the Hyper-V Server setting to allow Windows key combinations to go to the virtual machine, change the release key combination to something other than CTRL + ALT + LEFT ARROW, and use the toolbar button or Action menu of the Virtual Machine Connection to send CTRL + ALT + DEL to the virtual machine.

Tuesday 29 September 2009

Execution of user code in the .NET Framework is disabled

I was testing our new SQL Server 2008 enterprise cluster today and managed to get the following SqlException running a C# application:
"Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
The statement has been terminated."


The solution is to run the following:
EXEC sp_configure 'show advanced options', '1' ;
go
RECONFIGURE ;
go
EXEC sp_configure 'clr enabled', '1'
go
RECONFIGURE ;
-- Turn advanced options back off
-- EXEC sp_configure 'show advanced options' , '0';
GO

Friday 25 September 2009

Web Deployment Tool 1.0 has shipped

After 2 years of development and lots of customer feedback, the Web Deployment Tool has shipped the RTW of version 1.0

Click here to be taken to the IIS Blog.

Click here to be taken to the download site.

Friday 24 July 2009

I love the Linq to REST capability

All without changes to your C# code at all. Awsome.

Just watched Scott Hanselman's ADO.NET Data Services talk from NDC09. Rather good, so go watch.
Given that it (ADO.NET Data Services) is agnostic of EntityFramework, Ling2Sql, NHibernate, or MyHomeGrownOrm, seems like a good basis for implementing simple CRUD style services.

Looking at it I can see the appeal and its a good way to go. You could easily plug in a mock objects with test data pretending to be your database for NUnit testing your code with ease.

Saturday 10 January 2009

Windows Server 2008 as a Workstation

Well, its well known that Windows Server 2008 is 11-19% faster than Vista, and its using the Vista kernel. There's a ton of articles and i'm not going to list them here.

But what I am going to show you is how to do it.

Quoting from Alex Zaharov-Reutt
Although using server versions of Windows as desktop/workstation versions over the years certainly isn’t new, the source of the latest drive to turn Windows Server 2008 into Windows Workstation Vista 2008 seems to have come from Microsoft employee Vijayshinva Karnure, in his blog “The Way I See It”.

Karnure works at Microsoft India, and in a blog entry called “Using Windows Server 2008 as a SUPER workstation OS”, and in a follow up “Cont’d” posting, he outlined what Server 2008 users need to do to enable all the Vista-esque workstation goodness.

Another website called “Convert your Windows Server 2008 to a Workstation!” offers a “Windows Server 2008 to Workstation Manual”.

Reasons why Server 2008 might be faster include the lack of DRM found in Vista, and the additional time (and incentive) Microsoft has had to get Server 2008 robust and fast, given the fact it is meant to be used in business environments which expect high-performance software and hardware.

Reveals some excellent must see/download links:
  1. It can look like this: server 2008 aero screenshots
  2. If your sitting on the fence and don't really know if you should use Windows XP, Windows Vista, or Windows Server 2008. Read this. However, if your PC is old and slow, stick with XP. XP will always be faster than vista and server 2008 because it does less. Read Tomshardware Windows XP vs Vista benchmark guide. Its close on all counts apart from Open GL applications such as Maya, 3DS Max, Ensign, Lightscape where Vista is 90% slower due do its lack of OpenGL support.
    There are some programs that showed deeply disappointing performance. Unreal Tournament 2004 and the professional graphics benchmarking suite SPECviewperf 9.03 suffered heavily from the lack of support for the OpenGL graphics library under Windows Vista. This is something we expected, and we clearly advise against replacing Windows XP with Windows Vista if you need to run professional graphics applications. Both ATI and Nvidia will offer OpenGL support in upcoming driver releases, but it remains to be seen if and how other graphics vendors or Microsoft may offer it.

    However, my glass is always half full and I'm forever optimistic. Can you honestly see large companies like Autodesk (owners of Maya and 3DS Max) sitting back idly while their applications run like a dog on Vista? I'm sure they're poking ATI and nVidia in the ribs as you read this.
  3. Windows Server 2008 Workstation Converter. This program automatically converts your server 2008 into a workstation. Totally excellent and a must have.

  4. If you don't want to use the above program, you can do it by hand by following this guide, or download the full PDF guide.
  5. Another guide by Vijayshinva Karnure is here: Page1, Page2.
Are there any drawback to using a Server OS?
Yes, in fact.
Most things will work just fine, however some setups detect Windows Server 2008 as a server OS and may not install. Diskeeper for example, or any other software vendor with a workstation and server version of their product.
The compatibility mode does not have a Vista option, only XP/Windows 2003 and other legacy OS.