The readers of my articles on several forums asks questions regarding something or the other and I do answer them promptly. However I feel the answers should be shared among a larger group of people. I think this blog will make this possible.
Let us say you have a computer and you own it. When you installed the operating system you became the owner of the computer and hence its administrator. If others want to use the computer (the local account) than you as the administrator should create a new user and provide a password. You can also set the life time of that password. When so created each user has his own folder; has his own profile and shares a common folder (Public) with the other users which makes it possible to share files.
As the creator/installer of SQL Server has two options for authentication to log into the SQL Server (a Windows Service), either Windows Authentication or SQL Server authentication. Any Windows user (any of the users of the computer) can be set to log-in to the SQL Server if the computer administrator sets up the user to log-in.
This post shows the first part of creating a new user to a computer (desktop, laptop or a tablet).
Go to Control Panel | System and Security | Administrative Tools | Computer Management. You should be able to see the Local Users and Groups folder. Right click User and click on New User... as shown.
The New User window is displayed as shown.
Insert the name of the New User, herein UserX, the Full name as well as the Description can be provided if needed. Herein only a Description is provided.
You need to provide a password and confirm it. Even in Windows 8.1 password strength is not a problem. There are options to set how long a password can be active and whether it needs to expire. You as the administrator can also disable the account if it needs (employee leaves, tenders resignation). Herein the default choice has been changed and User Cannot change password and Password never expires has been chosen.
Click Create and it is done as shown in the Computer Management console. Now UserX can access the computer with the password you have created. You can now access his properties by right clicking UserX to bring up the properties.
Here Hodentek8(host) is the name of the computer; RegencyPark is the name of the instance and UserX is the name of the user.
Choose Windows Authentication using the following command and create the login using the following:
At this point you should have the computer user called UserX for your computer otherwise you will get this error.
------------- PS : A positional parameter cannot be found that accepts argument '$null'. At line:1 char:3 + PS <<<< SQLSERVER:\> $SqlUser.LoginType='WindowsUser' + CategoryInfo : InvalidArgument: (:) [Get-Process], ParameterBindingException + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.GetProcessCommand
Now create this login by this:
This completes the steps to create a New User Login which you can verify in SQL Server Management Studio as shown here:
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 evalauted 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):
You are probably intrigued why you are getting employee ID's other than 5 and 7 in
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
Open a Query pane in SSMS (from a database, for example) and right click to display the drop-down menu as shown.
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.
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.
The query you designed gets displayed in the Query Editor as shown.
Selecting the query and displaying it in designer will not work for more complicated queries.