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

Coding standards from http://amazedsaint.blogspot.com/2010/11/top-6-coding-standards-guideline.html

Some time back, I collated a list of 7 Must Read, Free EBooks for .NET Developers, and a lot of people found it useful. So, I thought about putting together a list of Coding Standard guidelines/checklists for .NET /C# developers as well.

As you may already know, it is easy to come up with a document - the key is in implementing these standards in your organization, through methods like internal trainings, Peer Reviews, Check in policies, Automated code review tools etc. You can have a look at FxCop and/or StyleCop for automating the review process to some extent, and can customize the rules based on your requirements.

Anyway, here is a list of some good Coding Standard Documents. They are useful not just from a review perspective - going through these documents can definitely help you and me to iron out few hidden glitches we might have in the programming portion of our brain.

So, here we go, the listing is not in any specific order.

1 – IDesign C# Coding Standards

IDesign C# coding standards is a pretty decent and compact (27 pages) Coding Standards Document. It covers a Naming conventions, Best practices and Framework specific guidelines. Example:

The document even has guidelines for project settings, build configuration, versioning etc. Good work by IDesign guys. You can download the document here

• Download IDesign C# Coding Standards

2 – Encodo C# Handbook

Encodo C# handbook is bit more recent, and has 72 pages of guidelines on Structure, Formatting, Naming. It also has a ‘Patterns and Best Practices’ section, which is a must read for any .NET/C# developer.

You can download the Handbook here.

• Encodo C# Handbook Download

3 – Microsoft Framework Design Guidelines

MSDN has a section on guidelines for Designing class libraries, which covers a set of best practices related to Type Design, Member Design etc. You can find it here.

• Read the Design Guidelines for Developing Class Libraries

4 – Denni’s C# Coding Standards document

Dennis created an initial version of C# coding standards, which was published as Philips Health Care C# coding standards document (~70 pages). The document categorizes the guidelines to categories like Naming, Exception Handling, Control Flow etc.

• Update: Dennis kindly pointed that the Initial Version I linked here earlier has now been superseded by the Coding Guidelines for C# 3.0 and C# 4.0. Paul Jansen of Tiobe will update his site soon regarding the new version - But in meantime, download the guidelines and some companion documents here:http://csharpguidelines.codeplex.com/

5 – Microsoft’s All-In-One Code Framework Coding Guideline

Microsoft’s All In One Code framework has a Coding Style Guideline document. The Microsoft All-In-One Code Framework is a free, centralized code sample library provided by the Microsoft Community team. It has typical code samples for all Microsoft development technologies, and a code style guideline document with that. Thanks to Kevin for pointing out this guideline document with All In One Code Framework (See the comments)

• Download All In One Code Framework Guideline Document

6 – Brad’s Quick Post on Microsoft Internal Coding Guidelines

Brad had a post on Microsoft Internal coding standards (I’m not sure whether he still follow that in Google, if at all he uses C# there). It is a short post, and is mainly on Styling and Naming conventions.

• Read Microsoft Internal Coding Guidelines Post

7 – Mike’s C# Coding Style Guide

Mike Kruger (Sharpdevelop) had published a 13 page C# Coding Style guide. Again, the focus is on Casing, Naming conventions, Declaration style etc. A short and simple Style Guide.

• Download C# Coding Style Guide

So, if you are still confused about which document to choose - my recommendation is here for you - Based on your landscape, organizational climate, project and domain, go through these documents and pick the relevant recommendations – to formulate your very own 10 page ‘.NET/C# Coding standards/guidelines’ for your team.

Also, if you think I missed any prominent guideline document, list down the same in the comments section, and I’ll include that in the main post if it is relevant – My initial post was about 6 documents, but I expanded/modified the list later based on some feedback I received. Happy Coding.

Also, don’t miss these related posts.

• 7 Freely Available Ebooks For .NET developers
• 5 Back to Basics C# Articles - Fluent Interfaces, Expr Trees etc
• 3 Gems from Mono to spice up your .NET Apps
• Top 5 Common Mistakes .NET Developers Must Avoid
• 6 Cool VS2010 Tips you may find interesting
• 4 .NET 4.0 Libraries you *should* know about

Forcing the current identity value to a new value

Forcing the current identity value to a new value

The following example forces the current identity value in the BusinessEntityID column in the Employee table to a value of 300. Because the table has existing rows, the next row inserted will use 301 as the value, that is, the current identity value plus 1, the current increment value defined for the column.

SQL
USE AdventureWorks2008R2;
GO
DBCC CHECKIDENT ("HumanResources.Employee", RESEED, 300);
GO

Select top 1 from each group

Select * from (
select ROW_NUMBER() OVER(PARTITION BY Field1 , Field2 ORDER BY Field3 DESC) AS RowNumber
Field1 , Field2 , Field3
from table1
where
) a where RowNumber = 1

SHRINK DATABASE

USE MyDATA
GO
DBCC SHRINKFILE('MyDATA_log', 1)
BACKUP LOG MyDATA WITH TRUNCATE_ONLY
DBCC SHRINKFILE('MyDATA_log', 1)
GO

On sql server 2008, To remove database log 
https://www.brentozar.com/archive/2009/08/backup-log-with-truncate-only-in-sql-server-2008/

 BACKUP LOG martinrobson TO DISK='NUL:'