Basic SQL Code Review

PROBLEM:

You want to make SQL code review to yourself or to one of your team member

SOLUTION:

Use the following list for a basic SQL code review. Remember - recommendations are good for 90% of cases. Use "Execution plan" to validate your queries

1. Always use indexes, even for small tables
2. Try to avoid "OR"s - may cause full table scans (UNION can replace OR)
3. Use UNION ALL instead of UNION if possible (does not sort)
4. Do not use SQL functions around indexed columns (will suppress the use of that index)
5. Try to avoid sorts (or use the same order by as the index)
6. Try to provide the WHERE clause with as many host variables that match the index
7. Avoid JOIN's with many tables
8. Use an OUTER JOIN instead of the NOT IN clause
9. Using col1 > 0 is better that col1 != 0
10. Use col1 = 40 not col1+10 = 50
11. Use EXISTS instead of DISTINCT and IN subquery
12. Use NOT EXISTS instead of NOT IN
13. Use UNION instead of OR
14. When 2 indexes exists on the same table and both rank the same (by the optimizer), the first index on the WHERE clause will be used
15. Try to use "index only" queries if possible. Consider adding columns to existing indexes
16. OR's are processed from left to right
17. AND's are processed from right to left
18. Put expensive OR's at the end part (right)
19. Put expensive AND's at beginning part (left)
20. Make proper use of correlated sub-queries
21. Break up complicated SQL statements. Do not attempt to solve all situations with single statement
22. Beware of NULLS! - IS NULL and IS NOT NULL will not use index
23. Use >= 0 to replace NULL expression 

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Disable all constraints, clean all database

PROBLEM:

You need to disable all constraints, clean all database

SOLUTION:

-- Disable all constraints

EXEC sp_MSForEachTable @command1="print 'NOCHECK CONSTRAINT ?'", @command2="ALTER TABLE ? NOCHECK CONSTRAINT all"

When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints

-- Delete data in all tables

EXEC sp_MSForEachTable @command1="print 'DELETE FROM ?'", @command2="DELETE FROM ?"

-- Enable all constraints

EXEC sp_MSForEachTable @command1="print 'CHECK CONSTRAINT ?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

-- If some of the tables have identity columns we may want to reseed them

EXEC sp_MSForEachTable @command1="print 'CHECKIDENT RESEED ?'", @command2="DBCC CHECKIDENT ( '?', RESEED, 0)"

Another option is to run the following code and save the results for both drop and recreate, then execute all the drops followed by your code and then recreate the constraints using the results from the second query

SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' DROP CONSTRAINT ' + '[' + f.name + ']'

FROM .sys.foreign_keys AS f

INNER JOIN .sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +' ADD CONSTRAINT ' + '[' + f.name +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)+')' as Scripts

FROM .sys.foreign_keys AS f

INNER JOIN .sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

To handle table constraints:

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable CHECK CONSTRAINT ALL

To handle single constraint:

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

.NET Code Review Checklist

PROBLEM:

How do you know your team's .NET code is well written?

SOLUTION:

Use the following checklist in your code reviews

Inline Documentation

1. Module Headers
2. Standard Routine Headers
3. Routine parameters, return values, assumptions
4. Constants, Module and Global variables
5. Routines primary path
6. Tricky code, special cases, exception handlers

Standards and guidelines

1. Project Name and description
2. Standard designs – message handling, clipboard, settings, etc
3. Namespaces:
4. CompanyName.TechnologyName[.Feature][.Design]
5. Class Naming – (Pascal Case, do not use _ or prefix)
6. Interface Naming – (I prefix, Pascal Case)
7. Attributes Naming – (use attribute suffix)
8. Interface Naming – (I prefix, Pascal Case)
9. Enum Naming – (no suffix, Pascal Case)
10. Static Field Naming – (Pascal Case)
11. Parameter Naming – (Camel Case)
12. Method Naming – (Pascal Case)
13. Property Naming – (Pascal Case)
14. Event Handler– (EventHandler suffix)

Change Control

1. Amendment history included
2. Marked changed code
3. Implementation of new/changed Interfaces

Requirements/Design

1. All specification features implemented
2. Verification of the requirements - Unit Test
3. Implementation of all interfaces defined
4. Design document updated, according design changes in the implementation

Performance/Optimization

1. File I/O optimized for efficiency
2. File opened only once to process (unless a random-access file)
3. DB - operations implemented for efficiency
4. DB – correct cursor and location specified for the type of use
5. DB – Use of indexed, record pointers, bookmarks
6. DB – Extra records (unused) in recordsets
7. Loops optimization
8. Recursive routines optimization

Robustness

1. Error Handling – avoid application crash
2. Error Handling – meaningful defined error messages
3. Error Handling – raise different error messages, according to the exceptions to provide appropriate automated response or indication to the user
4. Error Handling – use of event log (to be isolated and reproduced later)
5. Error Handling – catch blocks for all type of exceptions
6. Transaction – group related executions and protect against failures (Commit, Rollback)

Data Use and Control

1. Minimize use of global variables
2. Module variables use
3. Variables – declarations with the smallest scope possible
4. Variables – declared with a specific type (the smallest type appropriate for the data)
5. Variables – clear names to identify the use
6. Variables - Data comparisons of the same type
7. Variables (module and global) – initialized to default values
8. Magic numbers avoided using constants and macros
9. Use of Multiple-individual variables – group related data instead

Execution Control

1. Functions – return proper value
2. Recursive functions – boundary safeguards
3. Modularization use – remove deep nesting of control statements.
4. Comparisons – proper bracketed evaluations (ensure right order of priority)

Portability

1. External OS related components testing – ensure compliance with requirements
2. Platform specific code – clearly identified

Localization (if supported)

1. Component integrations with the OS regional settings or any application settings
2. Code implemented to account for user localization issues
3. Presenting data – user specified date, currency formats, font, color selections, sort orders etc.
4. Application correct respond to region changes
5. Use of Resource file or language library – to enable easy deploy in multiple languages

Style and Layout

1. Consistent use of style elements
2. Code easy to read

Reusable

1. Common code identified for inclusion in a common library
2. Duplicated common code

Testable

1. Driver program for independent test of the component
2. Unit test plans used and applied – to identify critical test elements

Data Access

1. General configurations (connection string)
2. Proper use of Data objects
3. Error Handle – use try – catch – finally blocks (close connection object)

Assemblies

1. Naming Conventions (namespace)
2. Assembly location (local, GAC)
3. Multi File / Single File assembly
4. Versioning

ASP.NET

1. State Management (State object, Session / Application)
2. Configuration
3. Controls
4. Security
5. Caching
6. Error Message Handle
7. UI – CSS, Page Templates

Remoting Configurations

1. Activation Type (Client, Singlecall, Singleton)
2. Registration
3. Appropriate use - (synchronous, asynchronous)

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

8 Habits Of Highly Effective Manager

PROBLEM:

How do you know you are a good manager?

SOLUTION:
1. Be a good coach - Provide specific, constructive feedback, balancing negative and positive. Have regular one-on-ones, presenting solutions to problems tailored to the employee's strengths.
2. Empower your team and don't micro-manage - Balance giving freedom to your employees while still being available for advice. Make "stretch" assignments to help them tackle big problems.
3. Express interest in employees' success and well-being - Get to know your employees as people, with lives outside of work. Make new folks feel welcome, help ease the transition.
4. Be productive and results-oriented - Focus on what you want the team to achieve and how employees can help achieve it. Help the team prioritize work, and make decisions to remove roadblocks.
5. Be a good communicator and listen to your team - Communication is two-way: Both listen and share. Hold all-hands meetings and be specific about the team's goals. Encourage open dialogue and listen to the questions and concerns of your employees.
6. Help your employees with career development
7. Have a clear vision and strategy for the team - Even amid turmoil, keep the team focused on goals and strategy. Involve the team in setting and evolving the team's vision, goals, and progress.
8. Have key technical skills, so you can help advise the team - Roll up sleeves and work side-by-side with team, when needed. Understand the specific challenges of the work.

THREE PITFALLS
1. Have trouble making transition to team leader - Fantastic individual performers are often promoted to manager without the necessary skills to lead. People hired from outside often don't understand the specific ways of the company.
2. Lack a consistent approach to performance management and career development. Doesn't help employees understand what company wants. Doesn't coach employees on how they can develop and stretch. Not proactive: Waits for the employees to come to them.
3. Spend too little time on managing and communicating

Reference:http://www.businessinsider.com/8-habits-of-highly-effective-google-managers-2011-3#ixzz1HJQnYZms

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

9 Papers Software Architect Must Read

PROBLEM:

You are going to be a software architect, what should you read?

SOLUTION:
I could come up with quite a few more articles not to mention books that aren't in this list. However these are definitely some of the most influential papers I read

1. Who Needs an Architect? - Do we or don't we? By Martin Fowler (2003) 
2. The Byzantine Generals Problem - The problem with distributed consensus. By Leslie Lamport, Robert Shostak and Marshall Pease (1982) 
3. A Note on Distributed Computing - It is one of the foundation papers on distributed computing. By Samuel C. Kendall, Jim Waldo, Ann Wollrath and Geoff Wyant (1994) 
4. Big Ball of Mud- Brian Foote and Joseph Yoder (1999) -patterns or anti-patterns?
5. No Silver Bullet Essence and Accidents of Software Engineering - On the limitations of Technology and Technological innovations. By Frederick P. Brooks (1987) 
6. The Open Closed Principle - The first in a series of articles on Object Oriented Principles. By Robert C. Martin (1996) 
7. IEEE1471-2000 A recommended practice for architectural description of software intensive systems - It is a standard and not a paper but it is the best foundation for describing a software architecture I know. By various (2000) 
8. Harvest, Yield, and Scalable Tolerant Systems - That's where the CAP theorem was first defined. By Armando Fox, Eric A. Brewer (1999) 
9. An Introduction to Software Architecture - one of the foundation articles of software architecture field. By David Garlan and Mary Shaw (1993)

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

SCRUM in under 10 minutes

PROBLEM:
You have only 10 minutes to decide if you are going to use Scrum in your next project

SOLUTION:
http://www.youtube.com/watch?v=Q5k7a9YEoUI&hl=iw

If you have more than 10 minutes, you can start with this must read document "Scrum and XP - Beyond the trenches"

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

System Quality Description

PROBLEM:
You need a short list of system quality issues

SOLUTION:

1. Security - Ability of a system to protect itself and its resources from unauthorized use – including access, disclosure, modification, and destruction.
2. Availability - Ability of a service to remain operational, with minimal interruption or degradation, based on a service level. In the event of a failure of one or more of its parts, a service should remain operational. The level of tolerance for faults will vary, so one or more parts has a bound that is influenced by other business conditions.
3. Reliability - Ability of a system component to repeatedly produce the same results in accordance with its specifications.
4. Performance - Refers to a combined measurement of response time and throughput that meets established, service-level agreements. This may include network performance or latency between two network end points or, more importantly, how it effects a client application and end user.
5. Scalability - Ability of an architecture to accommodate greater intermittent or sustained demands for service without reducing performance or availability. The network provides scalability by being able to handle additional traffic, services, and service instances (hosts, switches, and so on).
6. Manageability - Ease with which a system can be managed, monitored, and maintained.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!