stop a Profiler or SQL Trace using sp_trace_setstatus

http://weblogs.sqlteam.com/dang/archive/2007/12/16/Avoid-Causing-Problems-with-Profiler.aspx

 traces are ultimately created and managed using documented stored procedures (sp_trace_create, sp_trace_setevent, sp_trace_setfilter and sp_trace_setstatus).  However, instead of calling these stored procedures directly, SQL 2005 Profiler uses the SMO API which wraps these stored procedure calls and well as calls to an undocumented procedure to retrieve trace data.


http://www.eggheadcafe.com/software/aspnet/31538555/how-to-kill-a-profiler-trace.aspx

You can stop a Profiler or SQL Trace using sp_trace_setstatus.

Dan Guzman posted on Wednesday, January 23, 2008 10:19 PM

You can stop a Profiler or SQL Trace using sp_trace_setstatus. First, determine the desired trace id: SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2; Then, specify the trace id in the script below to stop and delete. DECLARE @TraceID int SET @TraceID = ? EXEC sp_trace_setstatus @TraceID, 0 EXEC sp_trace_setstatus @TraceID, 2 -- Hope this helps. Dan Guzman SQL Server MVP

http://mangalpardeshi.blogspot.com/2008/10/difference-between-rownumber-rank-and.html

Nice article on ROW_NUMBER, RANK , DENSE_RANK in SQL
http://mangalpardeshi.blogspot.com/2008/10/difference-between-rownumber-rank-and.html

Difference between ROW_NUMBER, RANK and DENSE_RANK

What is the Difference between ROW_NUMBER, RANK and DENSE_RANK? Which one to use?
This is very common question in the minds of SQL newbie's.
Lets take 1 simple example to understand the difference between 3.

First lets create some sample data :

-- create table CREATE TABLE Salaries ( Names VARCHAR(1), SalarY INT ) GO -- insert data INSERT INTO Salaries SELECT 'A',5000 UNION ALL SELECT 'B',5000 UNION ALL SELECT 'C',3000 UNION ALL SELECT 'D',4000 UNION ALL SELECT 'E',6000 UNION ALL SELECT 'F',10000 GO -- Test the data SELECT Names, Salary FROM Salaries

Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I'll write a query like this :

SELECT names         , salary         ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER         ,rank () OVER (ORDER BY salary DESC) as RANK         ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK FROM salaries

>>Output

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

Interesting Names in the result are employee A, B and D. 
Row_number assign different number to them.
Rank and Dense_rank both assign same rank to A and B.
But interesting thing is what RANK and DENSE_RANK assign to next row?
Rank assign 5 to the next row, while dense_rank assign 4.
The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value. So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

Mangal Pardeshi
SQL MVP

Finding Nth row using ROW_NUMBER()

Finding  Nth row using ROW_NUMBER()

Select * from (
select ROW_NUMBER() over(order by nTestNo desc)  Row , nTestNo from Tests.TestMaster
 ) T where T.Row = 3