Useful SQL Functions

NEWID ( )



DECLARE @myid uniqueidentifier
Example

SET @myid =NEWID()

PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid) 



OBJECT_NAME(@@PROCID)



PROCID: Returns the object identifier (ID) of the current
Transact-SQL module. A Transact-SQL module can be a stored procedure,
user-defined function, or trigger. @@PROCID cannot be specified in CLR modules
or the in-process data access provider.

Return Type : int






@@TRANCOUNT

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.




ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for
ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT.

COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by

RAISERROR



 Itis used to return

messages back to applications using the same format as a system error or 

warning message generated by the SQL Server Database Engine.

RAISERROR can return either:




A user-defined error message that has been created using the
sp_addmessage system  stored procedure.
These are messages with a message number greater than 50000 that  can be viewed in the sys.messages catalog
view.

A message string specified in the RAISERROR statement.




RAISERROR can also:

Assign a specific error number, severity, and state.

Request that the error be logged in the Database Engine
error log and the Microsoft Windows application log.

Substitute argument values into the message text, much like
the C language printf_s function.

Both RAISERROR and PRINT can be used to return informational
or warning messages to an application. The message text returned by RAISERROR
can be built using string substitution functionality similar to the printf_s
function of the C standard library, whereas PRINT can only return a character
string or character expression. A RAISERROR severity of 11 to 19 executed in
the TRY block of a TRY…CATCH construct causes control to transfer to the
associated CATCH block. Specify a severity of 10 or lower to return messages
using RAISERROR without invoking a CATCH block. PRINT does not transfer 

control to a CATCH block.

When RAISERROR is used with the msg_id of a user-defined
message in sys.messages, msg_id is returned as the SQL Server error number, or
native error code. When RAISERROR is used with a msg_str instead of a msg_id,
the SQL Server error number and native error number returned is 50000.

When you use RAISERROR to return a user-defined error
message, use a different state number in each RAISERROR that references that
error. This can help in diagnosing the errors when they are raised.

Use RAISERROR to:

Help in troubleshooting Transact-SQL code.

Check the values of data.

Return messages that contain variable text.

Cause execution to jump from a TRY block to the associated
CATCH block.

Return error information from the CATCH block to the calling
batch or application.

Example

RAISERROR ('your error message',16,2);  --Here 16 is the Level and 2 is the 

state 




DB_ID() 

Returns the database identification (ID) number

Example 1 

SELECT DB_ID() AS my_database_id 

Example 2 

SELECT DB_ID(N'AcharyaDatabase') AS my_database_id 

DB_NAME()




Returns the Current database name

SELECT DB_NAME() AS my_db_Name; 

@@SERVERNAME

Returns the name of the local server 

SELECT @@SERVERNAME AS 'Server Name' 

@@SERVICENAME

Returns the Servicename

SELECT @@SERVICENAME AS 'Service Name'

USER

SELECT USER AS 'User Name'

@@SPID

SELECT @@SPID AS 'ID'

SYSTEM_USER

SELECT SYSTEM_USER AS 'Login Name'



 @@TEXTSIZE

SELECT @@TEXTSIZE AS 'Text Size'

@@TIMETICKS

Returns the number of microseconds per tick.

SELECT @@TIMETICKS AS 'Time Ticks'




@@TOTAL_READ,@@TOTAL_WRITE

Returns the number of disk reads, not cache reads, by SQL
Server since SQL Server was last started

SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes'




@@VERSION

SELECT @@VERSION AS 'SQL Server Version'

AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM

Select Sum(salary) from wages

Select AVG(salary) from wages

Select COUNT(salary) from wages

Select MAX(salary) from wages

Select MIN(salary) from wages

Select STDEV(salary) from wages

Select STDEVP(salary) from wages

Select VAR(salary) from wages

Select VARP(salary) from wages