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:'

How to find out the last restore date for a database?

Credits to : http://www.sqlusa.com/bestpractices2005/dateoflastrestore/

to find the database restore dates and restore sample databases from database backup files:

-- Query msdb system database RestoreHistory and related tables
use msdb;
select
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
from RestoreHistory h
inner join BackupSet b
on h.backup_set_id = b.backup_set_id
inner join BackupFile f
on f.backup_set_id = b.backup_set_id
order by RestoreDate
go

email address regular expression

^(([^<>()[\]\\.,;:\s@\""]+(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$

regular expression for email address

Command line to install a windows service

Command line to install a windows service


cd c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
installutil /username=".\Annamalaisamy" /password="blahblah" E:\Projects\MyFirstService.exe
net start MyFirstService

Generate C# class code for table By Cade Bryant

The original source of the content is http://www.sqlservercentral.com/scripts/Development/61477/

GO
/****** Object: StoredProcedure [dbo].[usp_TableToClass] Script Date: 08/23/2010 10:46:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[usp_TableToClass]
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

HTTP methods

the following content were copied from
http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html
9.3 GET

The GET method means retrieve whatever information (in the form of an entity) is identified by the Request-URI. If the Request-URI refers to a data-producing process, it is the produced data which shall be returned as the entity in the response and not the source text of the process, unless that text happens to be the output of the process.

The semantics of the GET method change to a "conditional GET" if the request message includes an If-Modified-Since, If-Unmodified-Since, If-Match, If-None-Match, or If-Range header field. A conditional GET method requests that the entity be transferred only under the circumstances described by the conditional header field(s). The conditional GET method is intended to reduce unnecessary network usage by allowing cached entities to be refreshed without requiring multiple requests or transferring data already held by the client.

The semantics of the GET method change to a "partial GET" if the request message includes a Range header field. A partial GET requests that only part of the entity be transferred, as described in section 14.35. The partial GET method is intended to reduce unnecessary network usage by allowing partially-retrieved entities to be completed without transferring data already held by the client.

The response to a GET request is cacheable if and only if it meets the requirements for HTTP caching described in section 13.

See section 15.1.3 for security considerations when used for forms.

9.4 HEAD

The HEAD method is identical to GET except that the server MUST NOT return a message-body in the response. The metainformation contained in the HTTP headers in response to a HEAD request SHOULD be identical to the information sent in response to a GET request. This method can be used for obtaining metainformation about the entity implied by the request without transferring the entity-body itself. This method is often used for testing hypertext links for validity, accessibility, and recent modification.

The response to a HEAD request MAY be cacheable in the sense that the information contained in the response MAY be used to update a previously cached entity from that resource. If the new field values indicate that the cached entity differs from the current entity (as would be indicated by a change in Content-Length, Content-MD5, ETag or Last-Modified), then the cache MUST treat the cache entry as stale.

9.5 POST

The POST method is used to request that the origin server accept the entity enclosed in the request as a new subordinate of the resource identified by the Request-URI in the Request-Line. POST is designed to allow a uniform method to cover the following functions:

- Annotation of existing resources;
- Posting a message to a bulletin board, newsgroup, mailing list,
or similar group of articles;
- Providing a block of data, such as the result of submitting a
form, to a data-handling process;
- Extending a database through an append operation.
The actual function performed by the POST method is determined by the server and is usually dependent on the Request-URI. The posted entity is subordinate to that URI in the same way that a file is subordinate to a directory containing it, a news article is subordinate to a newsgroup to which it is posted, or a record is subordinate to a database.

The action performed by the POST method might not result in a resource that can be identified by a URI. In this case, either 200 (OK) or 204 (No Content) is the appropriate response status, depending on whether or not the response includes an entity that describes the result.

If a resource has been created on the origin server, the response SHOULD be 201 (Created) and contain an entity which describes the status of the request and refers to the new resource, and a Location header (see section 14.30).

Responses to this method are not cacheable, unless the response includes appropriate Cache-Control or Expires header fields. However, the 303 (See Other) response can be used to direct the user agent to retrieve a cacheable resource.

POST requests MUST obey the message transmission requirements set out in section 8.2.

See section 15.1.3 for security considerations.

9.6 PUT

The PUT method requests that the enclosed entity be stored under the supplied Request-URI. If the Request-URI refers to an already existing resource, the enclosed entity SHOULD be considered as a modified version of the one residing on the origin server. If the Request-URI does not point to an existing resource, and that URI is capable of being defined as a new resource by the requesting user agent, the origin server can create the resource with that URI. If a new resource is created, the origin server MUST inform the user agent via the 201 (Created) response. If an existing resource is modified, either the 200 (OK) or 204 (No Content) response codes SHOULD be sent to indicate successful completion of the request. If the resource could not be created or modified with the Request-URI, an appropriate error response SHOULD be given that reflects the nature of the problem. The recipient of the entity MUST NOT ignore any Content-* (e.g. Content-Range) headers that it does not understand or implement and MUST return a 501 (Not Implemented) response in such cases.

If the request passes through a cache and the Request-URI identifies one or more currently cached entities, those entries SHOULD be treated as stale. Responses to this method are not cacheable.

The fundamental difference between the POST and PUT requests is reflected in the different meaning of the Request-URI. The URI in a POST request identifies the resource that will handle the enclosed entity. That resource might be a data-accepting process, a gateway to some other protocol, or a separate entity that accepts annotations. In contrast, the URI in a PUT request identifies the entity enclosed with the request -- the user agent knows what URI is intended and the server MUST NOT attempt to apply the request to some other resource. If the server desires that the request be applied to a different URI,

it MUST send a 301 (Moved Permanently) response; the user agent MAY then make its own decision regarding whether or not to redirect the request.

A single resource MAY be identified by many different URIs. For example, an article might have a URI for identifying "the current version" which is separate from the URI identifying each particular version. In this case, a PUT request on a general URI might result in several other URIs being defined by the origin server.

HTTP/1.1 does not define how a PUT method affects the state of an origin server.

PUT requests MUST obey the message transmission requirements set out in section 8.2.

Unless otherwise specified for a particular entity-header, the entity-headers in the PUT request SHOULD be applied to the resource created or modified by the PUT.

9.7 DELETE

The DELETE method requests that the origin server delete the resource identified by the Request-URI. This method MAY be overridden by human intervention (or other means) on the origin server. The client cannot be guaranteed that the operation has been carried out, even if the status code returned from the origin server indicates that the action has been completed successfully. However, the server SHOULD NOT indicate success unless, at the time the response is given, it intends to delete the resource or move it to an inaccessible location.

A successful response SHOULD be 200 (OK) if the response includes an entity describing the status, 202 (Accepted) if the action has not yet been enacted, or 204 (No Content) if the action has been enacted but the response does not include an entity.

If the request passes through a cache and the Request-URI identifies one or more currently cached entities, those entries SHOULD be treated as stale. Responses to this method are not cacheable.

9.8 TRACE

The TRACE method is used to invoke a remote, application-layer loop- back of the request message. The final recipient of the request SHOULD reflect the message received back to the client as the entity-body of a 200 (OK) response. The final recipient is either the

origin server or the first proxy or gateway to receive a Max-Forwards value of zero (0) in the request (see section 14.31). A TRACE request MUST NOT include an entity.

TRACE allows the client to see what is being received at the other end of the request chain and use that data for testing or diagnostic information. The value of the Via header field (section 14.45) is of particular interest, since it acts as a trace of the request chain. Use of the Max-Forwards header field allows the client to limit the length of the request chain, which is useful for testing a chain of proxies forwarding messages in an infinite loop.

If the request is valid, the response SHOULD contain the entire request message in the entity-body, with a Content-Type of "message/http". Responses to this method MUST NOT be cached.

9.9 CONNECT

This specification reserves the method name CONNECT for use with a proxy that can dynamically switch to being a tunnel (e.g. SSL tunneling [44]).