SQL Server system function (T-SQL) and brief description.

System Functions (T-SQL)

@@ERROR : Returns the error number for the last Transact-SQL statement executed.
@@IDENTITY : Returns the last-inserted identity value.
@@ROWCOUNT : Returns the number of rows affected by the last statement.
@@TRANSCOUNT : Returns the number of active transactions for the current connection.
APP_NAME : Returns the application name for the current session if set by the application.
CASE : Evaluation a list of condition and returns one of multiple possible result expression.
CAST : (expression as data_type) / CONVERT : Convert an expression of one data type to another.
COALESCE (expression [ ,...n]) : Returns the first non null expression among its arguments.
CURRENT_TIMESTAMP : Returns the current date and time. ANSI SQL equivalent to GETDATE.
CURRENT_USER : Returns the name of the current user. Equivalent to USER_NAME().
DATALENGTH (Expression) : Returns the number of bytes used to represent any expression.
FORMATMESSAGE (msg_number , [param_value [,...n]]) : Constructs a message from an existing message

in sys.messages and returns the formatted message for further processing. 
GETANSINULL : Returns the default nullability for the database for this session.
HOST_ID : Returns the workstation identification number.
HOST_NAME : Returns the workstation name.
IDENT_INCR : Returns the increment value (returned as numeric  (@@MAXPRECISION,0)) specified during

the creation of an identity column in a table or view that has an identity column.
IDENT_SEED : Returns the seed value (returned as numeric (@@MAXPRECISION,)) that was specified when

 an identity column in a table or a view that has an identity column was created.
IDENTITY : to insert an identity column into a new table.
ISDATE (expression) : Determines whether an input expression is a valid date.
ISNULL (expression , replacement_value) : Replaces NULL with the specified value.
ISNUMERIC (expression) : Determines whether an expression is a valid numeric type.
NEWID : Creates a unique value of type unique identifier.
NULLIF (expression , expression) : Returns the null value if the two specified expression are equal.
PARSENAME (object_name , object_piece) : Returns the specified part of an object name. Parts of an object

that can retrieved are the object name, owner name, database name and server name.
PERMISSIONS ([objectid [,'column']]) : returns a value containing a bitmap that indicate the statement,

object or permissions of the current user.
SESSION_USER : Returns the user name of the current context in the current database.
STATS_DATE : Returns the date that the statistics for the specified index were last updated.
SYSTEM_USER : Allow a system-supplied value for the current login to be inserted into a table

 when no default value is specified.
USER_NAME ([ID]) : returns a database user name from a specified identification number.

Transactions in SQL Server

Transactions

A transaction groups a set of task into a single execution unit. Every transaction begins with a specific task and ends when all the tasks in the group successfully complete. If the execution of any of the tasks is fails, the transaction is fails. Therefore, we can say a transaction has only two results: success or failure. Any incomplete steps result in the failure of the total transaction.

Properties of Transactions

Every Transaction has the following four standard properties and in short form generally called ACID:

1. Atomicity: Ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is will be failed and previous operations are rolled back to their previous state.
2. Consistency: Ensures that the database properly changes its state based on a successfully committed transaction.
3. Isolation: Enables transactions to work independently.
4. Durability: Ensures that the effect of a committed transaction persist in case of any failure.

Transaction Control

The following commands are used to control transactions:

• Begin Transaction
• Rollback Transaction
• Commit Transaction

Example

      BEGIN TRAN

      BEGIN TRY

            --INSERT INTO SQL statement

            --INSERT INTO SQL statement

            COMMIT TRAN

      END TRY    

      BEGIN CATCH

            ROLLBACK TRAN --For Error

            SELECT ERROR_NUMBER() AS ErrorNumber,

            ERROR_SEVERITY() AS ErrorSeverity,

            ERROR_STATE() AS ErrorState,

            ERROR_PROCEDURE() AS ErrorProcedure,

            ERROR_LINE() AS ErrorLine,

            ERROR_MESSAGE() AS ErrorMessage;

      END CATCH

Stored Procedure

Stored Procedure

Stored Procedure is one of the powerful parts of SQL Server. Basically it is a group of T-SQL statements which is complied and stored inside the SQL Server.

As a result we get some advantages like:
1. Secure due to encryption.
2. We can use repeatedly after once compile.
3. Reduces data pass over a network.
4. Secured raw data because they are accessible by only stored procedure.

Basis Syntax of Stored Procedure

CREATE PROCEDURE Object Name
Input parameter DataType
Output parameter DataType Output
AS
BEGIN
--Variable Declaration 
  @parameter DataType
--SQL Statements
……………………..
END

In the stored procedure we can use SELECT, INSERT, DELETE and UPDATE these four basic query statements. For example, we can use the following query from application.

SELECT EmpID,EmpName,Cell 
FROM Employee
WHERE DeptID = 1

So, every time this query will be compiled. But if we make a stored procedure then it will compile one time and can be used as many times as required. So, stored procedure is more efficient than general query.

Simple Stored Procedure

CREATE PROCEDURE sp_Employee 
@DID int 
AS
BEGIN
 SELECT EmpID,EmpName,Cell 
 FROM Employee
 WHERE DeptID = @DID
END

In above Stored Procedure @DID is a parameter.

Execute the Stored Procedure

EXEC sp_Employee 1
Or
EXECUTE sp_Employee 1

Random Number in SQL

How to generate unique random number in SQL?

Random Number in SQL

SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function.  It returns a random float value between 0 and 1.  We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

SELECT RAND() AS [RandomNumber]

The output of the RAND() function will always be a value between 0 and 1.  If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places.  By casting to integer we can get rid of the decimal places.

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]

If we pass seed parameter value then the RAND() function will always return same value. 

SELECT RAND(1) AS [RandomNumber]

The NEWID() Way

Here's a different way to generate a random number without using the RAND() function. We can use NEWID system function to generate a random numeric value.

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS[RandomNumber]

The NEWID function returns a unique value of unique identifier data type. To convert it into integer first we have to convert it into VARBINARY then integer. The resulting integer value can be positive and negative. It we want only a positive value then we need to use the absolute value mathematical function ABS. 

Method 1 : Generate Random Numbers (Int) between Range

-- Variables for the random number

DECLARE @Random INT;

DECLARE @Upper INT;

DECLARE @Lower INT

-- Random number between 1 and 999

SET @Lower = 1     -- The lowest random number

SET @Upper = 999 -- The highest random number

SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower),0)

SELECT @Random