SQL Server – How to use WHILE loop with Break and Continue keyword

  Most of developer know, how to use WHILE loop in sql server. You can control WHILE loop using CONTINUE and BREAK keyword. Whenever you need to stop the execution of while loop on certain condition, make use of BREAK keyword. It will exit from WHILE loop and control moved to next statement. CONTINUE keyword will skips ...

SQL Server- Get date and time from current system datetime with example

How to find out current date and time in sql server? Using GETDATE() function it is possible to retrieve system datetime. GETDATE() is system in sql server Syntax : SELECT GETDATE() Examples: SELECT GETDATE() as CurrentDate Example with sql server 2000 and 2005, you can use below gatedate() function to get hours and minutes from ...

Event Notification in sql server to track the change with example

Similar to DDL trigger, event notification also execute in response to transact sql ddl statements as well as sql trace events by sending information about these events to a service broker service. Event notification Event notification can be used to log and review changes or activity occurring on the database, and they respond to the event ...

Monitor sql server services using xp_servicecontrol

In Sql Server, you can use different tools to manage sql server services like sql server configuration manager or windows services. using xp_servicecontrol undocumented stored procedure, you can monitor, start and stop the sql server services. xp_servicecontrol All sql server services can be monitored by using xp_servicecontrol extended stored procedure using SSMS, it will give ...

Change SQL Server Agent Service Account start-up type to Automatic

SQL Server Agent SQL Server Agent helps to perform various administrative tasks such as responding to predefined events, assisting in the automation of routine tasks, and processing alerts. Steps to change the start-up type of SQL Server Agent. SQL Server Configuration manager will help you to change the start-up type of SQL Server Agent to ...

SQL Server – Transparent Data encryption – Steps to enable TDE with Example

In sql server 2008 r2, you can use Transparent Data Encryption (TDE) to encrypt an entire user database. TDE is used to encrypt the data and log files of the database in real time, as well as encrypting backup of the database. TDE can be used while still adhering to laws, regulations, and standards that ...

SQL Server – sp_procoption system stored procedure

Is it possible to run stored procedure whenever sql server starts? Answer is yes. SQL Server provides sp_procoption system procedure that will make you to run stored procedure automatically each time sql server service is started. sp_procoption Using sp_procoption system stored procedure help you to execute stored procedure when sql server service is started. This ...

Ranking function in sql server 2008 with example

Ranking Function You would like to identify and group the information available in the table based on condition, after grouping. You may wish to rank the people in each group based on their category. This result can be obtained by ranking grouped data Type of Ranking function in sql server 2008 Using Rank function, you ...

SQL Server – System function to access system table information

SYSTEM FUNCTION System function is basically use to access system tables of SQL Server, system functions starts with @@ sign which sometime refer to as global system variable.  It doesn’t follows variable functionality as it is more similar to function behavior. Some commonly used system functions in sql server are as follows DB_ID(name) and DB_NAME(ID) ...

SQL Server – User defined table valued function and Scalar function

Two types of functions supported by sql server, User defined function and Built-in or system defined function. User defined function You can create your custom function to perform any activity in sql server that known as User defined function. Scalar and table valued function is another type of user defined function. Scalar Functions In Scalar ...