Thursday, December 29, 2011

SQL Programming Guide

SQL Performance Tips

  • Don’t start with your stored procedure name with “sp_ “.
  • Don’t put SELECT * from in query. List the required columns.
  • Try to avoid Cursor as much as possible. We can apply WHILE loop and smart SELECT instead of Cursor in some cases.
  • Try to avoid creating Temporary table and use views, variant table instead of temp table.
  • Give Comments in SP and batch query for complicated code which makes easy to understand it.
  • Try to avoid wildcard characters at the beginning of a word and not equals operators while searching.
  • Use SET NOCOUNT ON at the beginning of your SQL batch queries.
  • Use ANSI-Standard Join which gives more readable and fast retrieval.
  • Don’t write any Query in front end application whole query should be in SP.
  • Try to avoid Dynamic query as much as possible in batch query, SP and Triggers.
  • Handle NULL values carefully use ISNULL and COALESCE functions to handle NULL.
  • Only use Unicode data type (NCHAR, NVARCHAR) when you are storing non English characters because it takes twice as much space as non-Unicode data types.
  • When you insert data in a table you should mention column names it will avoid an issue if the table is altered in future.
  • Use Normalize I/P and O/P of SP in front end like string manipulations, concatenations, row numbering, case conversions and type conversions because these operations are going to consume more CPU & RAM cycles on the database server.
  • Avoid unnecessary network roundtrips between front end application and DB.
  • Put status parameter for SP which defines status of success or failure.
  • Keep error handling properly in SQL batch queries and use rollback the transaction in case of an error by default, SQL Server will not rollback. Use @@ERROR, @@ROWCOUNT server property and try, catch to handle errors.

Tips to Avoid Deadloack

  • Make transactions as much as simple
  • Handle only required data while transaction
  • Should not get any I/P from user at middle of the transaction
  • Don’t not use high level locking methods unless it required.
  • Use table in same order in SP and all batch queries consistently.

Monday, December 12, 2011

How to List Of Month Year Between Date Range ?

How to show or list Month with Year Between Date Range in SQL

CREATE TABLE #temp_report(id INT IDENTITY(1,1), dates DATE,display_date VARCHAR(20))

DECLARE @from_date date,
DECLARE @to_date date

@from_date = '12/8/2000'
SELECT @to_date = '12/8/2012'
SELECT @count = DATEDIFF(mm, @from_date,@to_date )
SELECT @i =0

INSERT INTO #temp_report(dates,display_date)
SELECT DATEADD(MM,@i,@from_date),CONVERT(VARCHAR(3),DATEADD(MM,@i,@from_date),109)+''''+ SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MM,@i,@from_date),101),9,2)
SELECT @i = @i + 1


how to find table dependencies in sql server ?

Using syscomments

SELECT distinct
FROM syscomments sc
INNER JOIN sysobjects so ON =
WHERE charindex('table_name', text) > 0

This returns the following showing both proc,function and trigger