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