SQL Server Tips

Passing Array of values to SP

I have been using dynamic queries when dealing with comma separated values for single parameter.

However now i learned that we can create a function (table valued user defined function) that splits the parameter value by the separator and returns a table.

dbo.sp_sample '12,13,14,15'
dbo.fn_split(@param1, ',')

Getting SP's Return Value

Sometimes we need to retrieve the SP's return values(scalar / set of rows) in some other SP.
for that we can use insert into #tblTemp exec dbo.sp_sample '12,13'

Table Variable Vs Temp Tables

we have been in confusion many times that which one to use table variable or temp table

Go for Table variable if data size is small
Go for Temp table if data size is large, bcoz we can create indexs on temp table that will help when we are dealing with large Daaataaaa (not typo)

Common Tips

Table

• Use Primary keys & Indexes (too much keys will affect the performance)
• Use Fill factor based on frequency of data insertion to the table
• Use Foreign keys and set appropriate null & not null
• Table name should explain the table purpose
• use appropriate data types, bit, tinyint, smallint, int, bigint...so on
• Use Varchar if data length is going to vary

SP

• Use set nocount on
• Use nolock in select queries
• Use proper Where conditions and inner joins
• Select appropriate columns
• Try to avoid NOT, <>, IS NULL, IN, UNION, DISTINCT if not necessary
• Try to avoid Cursors
• Avoid sp_ prefix, use usp_
• use query execution plans to tune up your queries
• SQL 2008 provides lot of inbuild reports that will help us identify long running running queries, most executed queries, connections and so on
• SQl 2008 intellisence is also another one good feature to like