Laptop Battery [Note: This article assumes that the reader is familiar with the
T-SQL, joins and queries]
Get all the benefits of Microsoft SQL Server 7 with none of the headaches. in wizards, the Enterprise Manager, and the Query Analyzer. use stored procedures, save time by automating common tasks with Jobs and Alerts, import and export data to and from an SQL Server database, and keep your data secure and backed up with the tips, tricks, and techniques you'll discover inside Microsoft SQL Server 7 For Dummies. have SQL Server tools, too, including programs for automatically building multitier applications, creating SQL Server objects, friendly Visual Basic code.
Thinkpad I have taken utmost effort to make this article easy to
understand, but incase you are not clear with the concept, please
raise up your concern and Ill be more than happy to attend your
doubts. All the examples discussed in this article uses Employee
table. If you do not have this table, please use the following
script to create it.
Use Pubs Go Create table Employee ( Eid int, Name varchar(10), Salary money ) Go Insert into Employee values (1,'harry',3500) Insert into Employee values (2,'jack',2500) Insert into Employee values (3,'john',2500) Insert into Employee values (4,'xavier',5500) Insert into Employee values (5,'steven',7500) Insert into Employee values (6,'susana',2400) Go
According to the indictment, Jones would steal various IBM and Penguin computer servers from Verisign's warehouse in Virginia and sell them to Johnson. Johnson would then sell the servers to several individuals, who would sometimes place them for sale on eBay. As a result of this scheme, the indictment alleges that Jones and Johnson caused Verisign to lose more than $120, 000 worth of computer equipment. In the indictment, Jones and Johnson are charged in three counts with causing the interstate transportation of stolen property, namely IBM 330 and 335 servers, in violation of 18 U.S.C.
Microsoft A simple query that can find the employee with the maximum
salary, would be:
Select * from Employee where salary = (Select max(Salary) from Employee)
end application tuning (not specific to SQL Server), but then very quickly move to SQL Server specifics, starting with the high level stuff like the basic server resource counters for CPU, memory, and IO, then trying to find the slow queries by looking at SQL Wait types, and using Profiler to find long running, or blocked, or otherwise poorly performing queries. We then move on to demonstrate how to "fix" the performance of those poorly performing queries.
Laptop Computers How does this query work?
He runs the local SQL Server users group in Jacksonville (JSSUG) and was on the Board of Directors of the Professional Association for SQL Server (PASS).
Laptop Computer The SQL Engine evaluates the inner most query and then moves to
the next level (outer query). So, in the above example inner query
i.e. Select max(Salary) from Employee is evaluated first. This
query will return a value of 7500 (based on the sample data shown
as above). This value is substituted in the outer query and it is
evaluated as:
Select * from Employee where salary = (7500)
The name attribute provides an identifier to the SQL Server database. The attribute specifies the name of the connection. Because the connection string in the preceding example is called, you use this value for the attribute as well. pollTime The pollTime attribute specifies the time interval from one SQL Server poll to the next. The default is .5 seconds or 500 milliseconds (as shown in the example). This is not needed for SQL Server 2005 notification.
Desktop Computer Returns:
Eid Name Salary 5 steven 7500
Notebooks If the same syntax is applied to find out the 2nd or 3rd or 4th
level of salary, the query would become bit complex to understand.
See the example below:
Select * from Employee where salary = (Select max(Salary) from Employee where salary
The above query would go on and on, depending on the level of salary that is to be determined. As mentioned earlier, the SQL Engine evaluates the inner most query first and moves the next outer level. One wouldnt want to write such a big query just to find out this simple information.
The same result can be achieved with a simple syntax and easily understandable logic, by using a CORRELATED SUBQUERY. This article doesnt explain about correlated sub-query as it is out of scope of this article. (You may want to take a quick look on CORRELATED SUBQUERY .) As a "Rule of Thumb" keep these points in mind, when you use a correlated sub-query <ol><li>Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required</li> <li>Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query</li></ol>
Having said that, lets look at the query that captures the Nth maximum value: <pre>Select * From Employee E1 Where (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary> E1.Salary)
Lenovo (Where N is the level of Salary to be determined)
Hard Drive In the above example, the inner query uses a value of the outer
query in its filter condition meaning; the inner query cannot be
evaluated before evaluating the outer query. So each row in the
outer query is evaluated first and the inner query is run for that
row. Lets look into the background process of this query, by
substituting a value for N i.e. 4,(Idea is to find the 4th maximum
salary):
Select * From Employee E1 Where (4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary> E1.Salary)
Travelstar Since the outer querys value is referred in the inner query, the
operation is done row-by-row. Based on the sample data as shown
above, the process starts with the following record:
Employee E1 ---------------------------------- Eid Name Salary 1 harry 3500
Gateway The salary of this record is substituted in the inner query and
evaluated as:
Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary> 3500
Laptop Parts Above query returns 2 (as there are only 2 salaries greater than
3500). This value is substituted in the outer query and will be
evaluated as:
Select * From Employee E1 Where (4-1) = (2)
Software The "where" condition evaluates to FALSE and so, this record is
NOT fetched in the result.
Hard Drives Next the SQL Engine processes the 2nd record which is:
Employee E1 ---------------------------------- Eid Name Salary 2 jack 2500
Electronics Now the inner query is evaluated as:
Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary> 2500
Canon This query returns a value of 3 (as there are 3 salaries greater
than 2500). The value is substituted in the outer query and
evaluated as:
Select * From Employee E1 Where (4-1) = (3)
Desktop Pc The "where" condition evaluates to TRUE and so, this record IS
fetched in the result. This operation continues for all the
remaining records. Finally the result shows these 2 records:
Eid Name Salary 2 jack 2500 3 john 2500
Desktop Computers The above query works in the same manner in Oracle and Sybase as
well. Applying the same logic, to find out the first maximum salary
the query would be:
Select * From Employee E1 Where (1-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary> E1.Salary)
Think Pad If you are able to understand this functionality, you can
workout various other queries in the same manner. The bottom line
is, the query should be efficient and NOT resource hungry.
Repair Conclusion
Data Recovery This example is the simplest representation of Correlated
sub-query. In the real-time database manipulation, correlated
sub-queries will much more extensive. I would appreciate if you can
share your comments and suggestions on this article. You can email
me at harivhn@hotmail.com
[ Comment, Edit or Article Submission ]
