Sunday, March 22, 2015

How do you explain this correlated subquery?

The syntax of subqueries is as shown here.

Outer query(some operator) (Inner query)

While non-correlated subqueries are quite easy to understand correlated sub queries
are not quite obvious.

In the case of non-correlated subqueries, the inner query is evaluated first. The value or values returned from inner queries are used in the outer queries based on some operation.

In the case of correlated subquery the outer query is run and for each value returned by the outer query the inner query is evaluated and when the match is found the outer query stops. Usually the inner query just returns either some value or no value and the logical operation is usually Boolean (exists, not exists, any, all, etc.).

The correlated subquery you are asking about is the following posed to the Northwind database:

Use Northwind
Go

Select o.EmployeeID, ShipName, o.OrderID ​
from Orders o​
Where  exists​
(Select i.ShipCity​
from Orders i​
Where i.ShipCity=o.ShipCity and i.EmployeeID in (5,7))

Go

This returns the following result set (here are sme 6 rows out of 674 rows):

CorrelatedSubquery
You are probably intrigued why you are getting employee ID's other than 5 and 7 in
the result.

The reason for this is the inner query result when processed with the logic (exists) returns true or false. If it is true the Orders table returns the columns requested. Inner query result set are not part of the rows returned.

Try the same query replacing 'exists' with 'not exists'. You will find that the subquery returns 156 rows.

The total of all rows returned by the query,

Select o.EmployeeID, o.ShipName, o.OrderID ​
from Orders o​


is 830 which is (156+674).

Saturday, March 21, 2015

How do you design a Query using Query Editor in SQL Server Management Studio?

From scratch:
Open a Query pane in SSMS (from a database, for example) and right click to display the drop-down menu as shown.

DesignQueryinEditor

Choose the Design Query in Editor... option and the Query Designer window gets displayed with a Add Tables window. You can add as many or as few tables as you want and design your query. Holding control key down, select multiple tables and click ADD to add all the selected tables. If you suspect that the data has changed recently, you can click Refresh to use latest data.

DesignQueryinEditor2

Display a query you created in the designer

You can create a query in the query pane. Highlight the query, right click the highlighted portion and select Design Query in Editor... as shown.


QueryEditorUp
The query you designed gets displayed in the Query Editor as shown.

SimpleSelect
 Selecting the query and displaying it in designer will not work for more complicated queries.


Wednesday, March 18, 2015

I do not see the available databases toolbar item in SSMS, Why?

Missing Toolbar item? NO

The SQL Server Management Studio(SSMS) is highly context sensitive.

For example, unless a Query Window is open (by creating a new query or by clicking first in the query window) the menu item Query will not be displayed.


SQLQueryeditor00

When you open a query window (by using New Query  from database or SQL Server
instance node) then you will see the Query menu item.

SQLQueryeditor01

In a similar manner depending on how whether View | Toolbars | Sql Editor
is chosen or not you will not see the 'Available databases' item as shown in the
next image.


SQLQueryeditor01

Saturday, March 14, 2015

How do you install Windows PowerShell 4.0 on Windows 7?

Windows PowerShell 3.0 is installed on Windows 7 by default but not Windows PowerShell 4.0.
You can check for version and verify if you have Windows PowerShell 4.0 as detailed in the following post:
http://hodentekhelp.blogspot.com/2014/08/if-you-have-multiple-versions-of-power.html

Review this image:

So, how do you install Windows PowerShell 4.0 on Windows 7?

First of all lets take a look at what are the new features that we can use in Windows PowerShell 4.0
Here are the Windows PowerShell 4.0 new features:

 •Support for workflow and remote script debugging
•Improved workflow authoring experience to make it more consistent with script authoring
•Added PipelineVariable as a common parameter
•Better support for downloading updatable help by using Save-Help and Update-Help in offline scenarios
•Updated version from 3.0 to 4.0
•Several bug fixes and performance improvements

Read more here:
http://www.microsoft.com/en-us/download/details.aspx?id=40855

In order to get Windows PowerShell 4.0 you need to install Windows Management Framework 4.0. This can be installed on Windows 7 SP1.
 I am not focussing on Windows Servers for this post.
It is not available for Windows 8.0 but installs by default when you upgrade to Windows 8.1
 
You can download WMF 4.0 from the previous link.
The download  has multiple files as shown here:

WMF40files.png

Installing WMF 40 confers the following functionality:
•Windows PowerShell 4.0
•Windows PowerShell ISE
•Windows PowerShell Web Services (Management OData IIS Extension)
•Windows Remote Management (WinRM)
•Windows Management Instrumentation (WMI)
•The Server Manager WMI provider
•A new feature for 4.0, Windows PowerShell Desired State Configuration (DSC)

You have a much more power full interface now.