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.

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 @i INT
DECLARE @count INT
DECLARE @from_date date,
DECLARE @to_date date

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

WHILE(@i<=@count)
BEGIN
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
END

SELECT * FROM #temp_report

how to find table dependencies in sql server ?

Using syscomments

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

This returns the following showing both proc,function and trigger