SQL Code/Procedure development suggestions

Most of you familiar writing stored procedures in SQL Server, but if you follow the development standards (in my experience)  which would help debugging the code (or) in case code modifications required it is easy to point out. 

Hope the following SQL coding rules help you while writing the SQL procedures & functions.

• Header part of the script should be updated reflecting the current changes to the script with the version details.  Suppose it is a new procedure you could mention as initial draft/release.  Later it can be added with all subsequent changes.

• Alignment of the script content should be proper.  Proper alignment shall help programmer to read the code easily.

• SNAPShot isolation helps reduce the locks, If the DB isolation is Snapshot and the SNAPSHOT isolation is supposed to be used in the scripts then verify that 'SET SNAPSHOT ISOLATION' is mentioned in the script. If in doubt confirm with the team which has sent the script for review and if 'SNAPSHOT ISOLATION' is mentioned, verify that SNAPSHOT ISOLATION is enabled at the database level too

• 'NOLOCK' can be used in the code when the diry reads are allowed which is less lock operation.

• 'SET NOCOUNT ON' statement should be mentioned in the beginning of each procedure. If the scripts require the counts and do not make use of SET NOCOUNT ON, make sure that it uses @@ROWCOUNT server variables instead, for fetching the counts.

• It is good to assign input parameter variables to the local variables then use it in procedure code.

• Fixed length columns in the table should use CHAR datatype all the times.

• TOP Statement might be required/might be present in the scripts. But verify if the TOP statement mentioned in the script is intentional/required.

• Any/All temp tables created in the procedure should be dropped at the end of the procedure

• SELECT statements using temp tables do not require NOLOCK as locks for the temp table maintained internally by SQL Server.

• If any other database references are present in the script, verify that the database is present, always use if exists clause to validate these cases.

• If LINKED Server references are present in the script, verify that the correct LINKED server are referenced and the LINKED Server is present in the appropriate environment.  Better to use If exists clause.

• Instead of multiple SET statements, a single SELECT statement can be used to assign value to variables that avoid confusion.

• Avoid using 'SELECT *' instead, use specific column name(s) in the SELECT statement.

•  Avoid using table variable if data stored in the table is too high.

• Avoid using CTE's if data stored in the corresponding CTE is too high.

• Avoid using co-related queries. See if the same can be resolved using subqueries instead.

• Avoid using CURSOR's if possible, try while instead.  If CURSOR is needed & used, check the CURSOR is CLOSED & DEALLOCATED before end of the procedure.

• Trigger should not be created on any table. If it is absolutely necessary see if an alternative approach can be used to eliminate the trigger.

• Check for Unused variable declarations in the procedures, this can be removed to have only required code.  Dead code / commented code should not be there in the procedure.

• Proper error handling should be done in the code, explaining the error handling in comments would help review the code.  

• Execution Plan of the procedure could help validating the logic and query optimization if needed.

• Validating the performance of the procedure can help figure out the performance bottlenecks.  Execute the procedure using parameters supplied in test environment atleast.

Class 8 - Functions in SQL Server

FUNCTIONS
What is function ?
A function in SQL Server can be defined primary as 2 types. User Defined Functions which is creating by user based on the requirement and System Defined Functions which was already created by Microsoft and provided for ready use.

These can be classified in other ways also :

The Transact-SQL programming language provides three types of functions:
• Rowset functions
Can be used like table references in an SQL statement.
• Aggregate functions
Operate on a collection of values but return a single, summarizing value.
• Scalar functions
Operate on a single value and then return a single value.

Deterministic and Nondeterministic Functions
All functions are deterministic or nondeterministic:
• Deterministic functions always return the same result any time they are called with a specific set of input values.
• Nondeterministic functions may return different results each time they are called with a specific set of input values.


USER DEFINED FUNCTIONS : When we come to User Defined Function again there are 3 types.
1) Scalar Function
2) Table Valued Function
3) Multi Statement Function

Scalar Functions
Scalare functions always returns single resultant value.
Syntax :
CREATE FUNCTION [ owner. ] fn_name ( [ { @parameter [ AS ] data_type }[ ,...n ] ] )
RETURNS data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Eg :
create function fn_studage (@id int) returns int
as
begin
declare @studage int
select @studage = datediff(year,dob,getdate()) from students where id = @id
return @studage
end

Inline Table-Valued Functions
Inline Table-Valued Functions return a resultset, as opposed to a single scalar value. A table valued function specifies the TABLE keyword in its RETURN clause. Its basic form is :
Syntax :
CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [ AS ] type } [ ,...n ] ])
RETURNS TABLE
[ AS ]
RETURN [ ( ] select-statement [ ) ]

An Inline Table-Valued Function created by this command:
CREATE FUNCTION datesales (@deadline as datetime)
RETURNS TABLE
AS
RETURN ( SELECT *
FROM sales
WHERE ord_date > @deadline)

and called by this sequence:
USE PUBS
GO
select * from datesales('09/13/1994')
will yield the following table:
stor_id ord_num ord_date qty payterms title_id
6380 6871 09/14/94 5 Net 60 BU1032
7067 D4482 09/14/94 10 Net 60 PS2091
7131 N914008 09/14/94 20 Net 30 PS2091
7131 N914014 09/14/94 25 Net 30 MC3021
8042 423LL922 09/14/94 15 ON invoice MC3021
8042 423LL930 09/14/94 10 ON invoice BU1032

Multi-statement Table-Valued Function
The final type of UDF is the Multi-statement Table-Valued Function. This UDS also returns a resultset, like the Inline variety UDF, but with a much more powerful result. The Multi-statement UFD can actually create a temporary table, specifying the fields, their type and characteristics. Now the scope of that temporary table is limited to the UDF in which it was defined, so when the UDF ends, the temporary table evaporates too. However, this can be a tremendously powerful technique to obtain or manipulate data.

The general form for the Multi-statement Table-Valued Function is:
CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [AS] type } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END
The following creates the datesales2 UDF:
CREATE FUNCTION datesales2 (@deadline datetime) RETURNS @table TABLE (stor_id varchar(6) null, ord_num varchar(8) null, ord_date datetime null, qty int, payterms varchar(20), title_id varchar(6))
AS
BEGIN
INSERT @table
SELECT *
FROM sales
WHERE ord_date > @deadline
RETURN
END
When the function is called by this sequence,
USE PUBS
GO
select * from datesales2('09/13/1994')
The following resultset is displayed and available. (It is the same set displayed in the Inline UDF, but created with the temporary table.)
stor_id ord_num ord_date qty payterms title_id
6380 6871 09/14/94 5 Net 60 BU1032
7067 D4482 09/14/94 10 Net 60 PS2091
7131 N914008 09/14/94 20 Net 30 PS2091
7131 N914014 09/14/94 25 Net 30 MC3021
8042 423LL922 09/14/94 15 ON invoice MC3021
8042 423LL930 09/14/94 10 ON invoice BU1032
Now these temporary tables by me combined, joined, with virtually any number created within the Multi-Tabled UDF, giving the user a very powerful tool for calculating and presenting data from the server in a way that truly leverages the effectiveness of writing code in the native language of SQL Server.

SYSTEM DEFINED FUNCTIONS :

FUNCTION EXPLANATION :

Function category Explanation
Configuration Functions : Returns information about the current configuration.
Cursor Functions : Returns information about cursors.
Date and Time Functions : Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.
Mathematical Functions : Performs a calculation based on input values provided as parameters to the function, and returns a numeric value.
Metadata Functions : Returns information about the database and database objects.
Security Functions : Returns information about users and roles.
String Functions : Performs an operation on a string (char or varchar) input value and returns a string or numeric value.
System Functions : Performs operations and returns information about values, objects, and settings in Microsoft® SQL Server™.
System Statistical Functions : Returns statistical information about the system.
Text and Image Functions : Performs an operation on a text or image input values or column, and returns information about the value.


Configuration Functions :
@@REMSERVERReturns the name of the remote Microsoft® SQL Server™ database server as it appears in the login record.
Syntax
@@REMSERVER
Example :
CREATE PROCEDURE check_server
AS
SELECT @@REMSERVER

@@LANGUAGE
@@SERVERNAME

@@SERVICENAME
Example
SELECT @@SERVICENAME
Here is the result set:
------------------------------
MSSQLServer

@@SPID
@@MAX_CONNECTIONS
The actual number of user connections allowed also depends on the version of SQL Server installed and the limitations of your application(s) and hardware.
To reconfigure SQL Server for fewer connections, use sp_configure.
Examples
SELECT @@MAX_CONNECTIONS

@@VERSION

@@NESTLEVEL
Returns the nesting level of the current stored procedure execution (initially 0).

@@DATEFIRST
Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.
Syntax
@@DATEFIRST
Examples
SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'
Here is the result set. Counting from Friday, today (Saturday) is day 2.
1st Day Today
---------------- --------------
5 2



2. Cursor Functions
@@CURSOR_ROWS
@@CURSOR_STATUS
@@FETCH_STATUS

3. Date and Time Functions
Datepart Abbreviations
Year yy, yyyy
Quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

DATEADD
Eg : SELECT DATEADD(day, 21, pubdate) AS timeframe fROM titles

DATEDIFF
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles

DATENAMESELECT DATENAME(month, getdate()) AS 'Month Name' = February

DATEPARTSELECT DATEPART(month, GETDATE()) AS 'Month Number' = 2

DAY SELECT DAY('03/12/1998') AS 'Day Number'

MONTHSELECT MONTH('03/12/1998') AS 'MONTH Number'

YEARSELECT YEAR('03/12/1998') AS 'YEAR Number'

GETDATE()SELECT GETDATE()

4. Mathematical Functions
CEILING Returns the smallest integer greater than, or equal to, the given numeric expression.
Examples
This example shows positive numeric, negative, and zero values with the CEILING function.
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
GO
Here is the result set:
--------- --------- -------------------------
124.00 -123.00 0.00

FLOORReturns the largest integer less than or equal to the given numeric expression.
Examples
This example shows positive numeric, negative numeric, and currency values with the FLOOR function.
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
The result is the integer portion of the calculated value in the same data type as numeric_expression.
--------- --------- -----------
123 -124 123.0000

TRIGNOMETRY :
SIN
COS
TAN
COT

5. Metadata Functions
@@PROCIDReturns the stored procedure identifier (ID) of the current procedure
SELECT @@PROCID AS 'ProcID'

OBJECTPROPERTYIF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 2
print 'Authors is a table'

DB_IDReturns the database identification (ID) number.

DB_NAMEReturns the database name.
Ex :
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases

OBJECT_ID


6. Security Functions
USER
USER_ID
SUSER_NAME : EG :select SUSER_NAME(0X01)
SUSER_SID : EG : SELECT SUSER_SID('sa')

7. String Functions
LTRIMReturns a character expression after removing leading blanks.
select ltrim(' venkat')

RTRIM
Returns a character string after truncating all trailing blanks.

UPPERReturns a character expression with lowercase character data converted to uppercase.
select upper('venkat')

LOWERReturns a character expression with UPPER character data converted to lowercase.

LENReturns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

LEFTReturns the part of a character string starting at a specified number of characters from the left.
Ex : left(‘venkat’,3)

RIGHTReturns the part of a character string starting at a specified number of characters from the right.
Ex : Right(‘venkat’,3)

REPLACEReplaces all occurrences of the second given string expression in the first string expression with a third expression.
Examples
This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')


8. System Functions
CASE
CAST and CONVERTExplicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

CAST : SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'

CONVERT : SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'

ISNULLCOALESCE
CURRENT_USER
HOST_ID
HOST_NAME
Eg : CREATE TABLE Orders
(OrderID INT PRIMARY KEY,
Workstation NCHAR(30) NOT NULL DEFAULT HOST_NAME())

@@IDENTITY
ISNUMERIC
@@row_count
SCOPE_IDENTITY
SERVERPROPERTY
eg : SELECT SERVERPROPERTY('servername'),
SELECT SERVERPROPERTY('productlevel')

SYSTEM_USER

@@TRANCOUNT

USER_NAME

System Statistical Functions
@@CONNECTIONSReturns the number of connections, or attempted connections, since Microsoft® SQL Server™ was last started.

@@PACK_RECEIVEDReturns the number of input packets read from the network by Microsoft® SQL Server™ since last started.

@@CPU_BUSYReturns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since Microsoft® SQL Server™ was last started.

@@PACK_SENTReturns the number of output packets written to the network by Microsoft® SQL Server™ since last started.

@@TOTAL_ERRORSReturns the number of disk read/write errors encountered by Microsoft® SQL Server™ since last started.

9. Text and Image FunctionsPATINDEX
TEXTPTR
TEXTVALID