CROSS JOIN in SQL Server

Let's look at the very rarely used join, which is CROSS join. The simplest ways of implementing the CROSS JOIN is remove the joining conditions on these tables.

So, for an ultra-quick example, let's take our first example from the CROSS JOIN section earlier in the chapter. The ANSI syntax looked like this:

SELECT * FROM CARMODELS CM CROSS JOIN COLORS C;

To convert it to the old syntax, we just strip out the CROSS JOIN keywords and add a comma:

SELECT * FROM CARMODELS CM, COLORS C;

OUTER JOIN in SQL Server

Let's look at the most widely used join, which is OUTER join. I will not focus on what are outer joins but will give how to us ANSI joins to write OUTER join queries.

There are two types of outer joins:
1) Left Outer Join or Left Join: *= symbol is used to implement this kind of join.
2) Right Outer Join or Right Join: =* symbol is used to implement this kind of join.

SELECT E.EMPID, M.EMPID AS ManagerID
FROM HRDETAILS.EMP E LEFT OUTER JOIN HRDETAILS.EMP M
ON E.MGRID = M.EMPID;

The above query is based on ANSI joins. Now let's rewrite this query using a WHERE clause–based join syntax.
It's very simple just replace "LEFT OUTER JOIN" with "," and then where ever you have ON condition replace that with either WHERE or AND condition depending on the conditions and joins in your query.

SELECT E.EMPID, M.EMPID AS ManagerID
FROM HRDETAILS.EMP E ,
HRDETAILS.EMP M
WHERE E.MGRID *= M.EMPID;

There will not be any difference in the out put.

INNER JOIN in SQL Sever

Let's look at the very basic join, which is INNER or EQUI JOIN

SELECT E.*
FROM HRDETAILS.EMP E
INNER JOIN HRDETAILS.EMP M
ON E.MGRID = M.EMPID;

The above query is based on ANSI joins. Now let's rewrite this query using a WHERE clause–based join syntax.

It's very simple just replace "INNER JOIN" with "," and then where ever you have ON condition replace that with either WHERE or AND condition depending on the conditions and joins in your query.

SELECT E.*
FROM HRDETAILS.EMP E ,
HRDETAILS.EMP M
WHERE E.MGRID = M.EMPID;

There will not be any difference in the out put.

Clustered index Criteria

Scenarios to have Clustered indexes:

1) If the query has a WHERE clause with operators like BETWEEN, >, >=, <, and <=.
2) If the query is returns large result sets.
3) If the query uses JOIN clauses.
4) The query accesses values in a column sequentially. For example, a query on EmployeeIDs between 10000 and 900000
5) If the query uses an ORDER BY clause or a GROUP BY clause. The index might remove the need to sort the data because the data is already ordered.

Criteria to create Clustered index:

1) Contains unique values or, failing that, contain mostly distinct values.
2) Are defined as IDENTITY. Each value in the column is known to be unique.
3) Are often used to sort data returned by a query. Having data in a clustered index that is already sorted in a desired order can save the cost of sorts during the query.

Criteria to not create Clustered index:

1) There is a wide key for each data row.
2) Values in the key columns undergo frequent changes. Because data in a clustered index is sorted on key column values, a change in those values means that data rows have to be moved into their correctly sorted positions after any changes.

Clustered index Vs Non-Clustered index

Primarily in SQL server there are two types of indexes, clustered index and Non-Clustered index. Let me give you very basic difference between these types of indexes. These two indexes are different in terms how the data is stored. In Clustered index the leaf-nodes contains the data where as in Non-clustered index leaf-nodes contains the bookmarks to the actual data. In other words data is sorted and stored based on the Clustered Index. So that’s the reason you can have only one Clustered index per table.

find a column in SQL Server

There are coupe of queries in my mail box about How to find out a particular Column and its details from all the columns in SQL Server.

This is one of finding the information.

select sysobjects.name as 'TableName', *
from syscolumns, sysobjects
where syscolumns.name='columnname'
and sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')

SQL Server Errors

Error:
'Problem in Connecting Database. error " SQL Server does not allow remote connections." '

Solution:
Incorrect Installation was the culprit.
Re-Install the same and everythign will workfine.

SQL Server Errors

Error:
Cannot drop database “DataBaseName” because it is currently in use.

Solution:
Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
USE master
GO
DROP DATABASE (databaseName)
GO

RoWNum in MS Access

There were couple of questions in my mail-box that is how to produce RowNum in MS Access queries??

After spending couple of hours finally this can be achieved with Sub-queries...is this great?

SELECT o1.EMPID, o1.ENAME,
(SELECT COUNT(*) FROM Emp AS o2
WHERE o2.Sal <= o1.Sal) AS RowNum
FROM Emp AS o1
ORDER BY o1.Sal

Happy Coding....

SHUTDOWN

SHUTDOWN command in SQL Server is used to stop SQL Server Services.


SHUTDOWN [ WITH NOWAIT ]

WITH NOWAIT clause is an Optional parameter. Shuts down SQL Server without performing checkpoints in every database. SQL Server exits after attempting to terminate all user processes. When the server restarts, a rollback operation occurs for uncompleted transactions.

If you dont use this clause then shutdown happens in the following fashion.

1. Disabling logins (except for members of the sysadmin and serveradmin fixed server roles).
2. Waiting for currently running Transact-SQL statements or stored procedures to finish.
3. Inserting a checkpoint in every database.

GO in SQL Server

Today, I was looking at couple of forums and my mailbox and encountered the question "What is GO"? in SQL Server? is it a command?

I was able to reply to this question in all the forums but its really important for me to put this in blog so that my blog readers can get the information.

It is basicaclly signals the end of a batch of Transact-SQL statements. Its not a T-SQL statement and its a command recognized by the osql and isql utilities and SQL Query Analyzer. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

USE pubs
GO
SELECT COUNT(*)
FROM authors
GO

This example creates two batches. The first batch contains only a USE pubs statement to set the database context and the next one is to retun the data.

My New Rank in SQL Sever Central

As you know i am one of the active member in Dotnetspider.com where i posted/answered 3200+ answers in last couple of years and wrote no.of articles and posted no.of code snippets. Now Its time to move on and set new goals to my self as a result started posting information on SQL Server Central, where it is mainly for SQL Server DB related questions.

As always hard-work always pays,

xp_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

Executing this xp_cmdshell statement returns a directory listing of the current directory.


xp_cmdshell 'dir *.exe'

xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.

When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.

By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

IIS 7.0

Windows Server 2008 featuring Internet Information Services 7.0 is a powerful Web application and services platform that delivers rich Web-based experiences. It offers improved administration and diagnostic tools to help achieve lower infrastructure costs on a variety of popular development platforms. With improved reliability and scalability, IT professionals and developers can manage the most demanding Web serving environments, from a single Web server to a large Web farm.

Download IIS @
http://www.microsoft.com/windowsserver2008/en/us/trial-software.aspx

special characters in SQL Server

There are several characters that have special meaning within a SQL query, for example the percent sign (%) in a LIKE query is a wildcard that essentially means "any number of characters can go here." Likewise, the underscore (_) is a wildcard that says "any single character can go here." So what if you are actually looking for a value that contains a literal percent sign? You will end up with bizarre results if you try the following:

SELECT columns FROM table WHERE
column LIKE '%%%'

Instead, you can try one of the following solutions:

SELECT columns FROM table WHERE
column LIKE '%[%]%'

-- or

SELECT columns FROM table WHERE
column LIKE '%\%%' ESCAPE '\'

The first query 'delimits' the special character with square brackets, telling the engine to treat it as a normal literal character instead of a character with special meaning. The second query uses a custom escape character -- you can use any character you like, just be careful that you aren't also expecting to use it as part of the literal string.

Method Overloading

In complex applications written in C#, we may need many methods which do essentially similar functions but are just different enough to be considered unique. For example, we may have to calculate a person's tax liability and would need to implement a method for doing this calculation in our application program. However, there are many different rules when it comes to tax calculations and they vary throughout the world. While there may be many rules, one basic equation stays the same: Your net income equals your gross income minus a computed tax amount. It is the method of computing your tax that varies.

We would probably have to implement different methods for each type of tax calculation. And, we could give each method a unique name such as TaxCalc1, TaxCalc2, TaxCalc3, etc. But wouldn't it be nice to just name the method TaxCalc and pass different arguments to it based on the computation desired?




For instance, let's say you live in a region within your country where you are taxed on your personal income, the value of your home, and any income you generate by doing business. On sales you generate through business activity, you must pay a gross receipts tax on all sales. If you own your home, you must pay a property tax on the imputed value of it. Then lastly, you must pay a tax on all income you generate through a job with another employer.

Check out the below link
http://www.codersource.net/csharp_method_overloading_ed.html

OOPS Concepts Part 1

To day i woule like to take a step back and give some information on OOPS concepts.

What Is an Object?
An object is a software bundle of related state and behavior. Software objects are often used to model the real-world objects that you find in everyday life. This lesson explains how state and behavior are represented within an object, introduces the concept of data encapsulation, and explains the benefits of designing your software in this manner.

What Is a Class?
A class is a blueprint or prototype from which objects are created. This section defines a class that models the state and behavior of a real-world object. It intentionally focuses on the basics, showing how even a simple class can cleanly model state and behavior.

What Is Inheritance?
Inheritance provides a powerful and natural mechanism for organizing and structuring your software. This section explains how classes inherit state and behavior from their superclasses, and explains how to derive one class from another using the simple syntax provided by the Java programming language.

What Is an Interface?
An interface is a contract between a class and the outside world. When a class implements an interface, it promises to provide the behavior published by that interface. This section defines a simple interface and explains the necessary changes for any class that implements it.

Keep watching.....