CHECKSUM In SQL Server

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.

CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For this definition, null values of a specified type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.


The order of expressions affects the resultant value of CHECKSUM. The order of columns used with CHECKSUM(*) is the order of columns specified in the table or view definition. This includes computed columns.

@@ROWCount in SQL Server

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

· Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
· Preserve @@ROWCOUNT from the previous statement execution.
· Reset @@ROWCOUNT to 0 but do not return the value to the client.

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

EXECUTE statements preserve the previous @@ROWCOUNT.

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

TRIM Function in SQL Server

For trim the spaces SQL Server has no function which can do trimming from both end instead it has function LTRIM and RTRIM.

You can create your own function and use it in your code or query whenever required. It will be termed as UDF.

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

RETURN LTRIM(RTRIM(@string))

END

GO

This function will take input as string which need to be trimmed.

Function for comma seprated values from table

Generally we get the requirement that we need comma separated values on one column which we can combined with any other result set.


For example when we need to retrieve the all phone no of a customer or multiple line of address for the customer.

You can create a function in which you can pass a value on which you want all other values as comma separated. Once you create the function you can use in select statement to retrieve data in desired format.


These are the two ways to get string values in comma separated

Easy Ways for doing function
DECLARE @listStr VARCHAR(MAX)

SET @listStr = ''

SELECT @listStr = @listStr + NumberCols + ','

FROM NumberTable

SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)



DECLARE @listStr VARCHAR(MAX)


SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols


FROM NumberTable


SELECT @listStr

Ways to Improve Stored Procedures

There are multiple ways to improve the SP few of them without doing any code modification are

Do not prefix your Stored Procedure with sp_.

In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is looked into masters database first before it is looked into the database it is called in.

Call your Stored Procedure prefixed with dbo.SPName – fully qualified name.

    When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.