How to make connection with the database

If you are creating dynamic application then must be used database. Before open the connection must to create connection string with the engine or database. Various database engine available in the market, which are SQL, MYSQL, OR...

Data Definition Language (DDL) Triggers in SQL

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing. Database auditing helps in monitoring the DDL operat...

Execute Batches multiple times using Stored Procedures in SQL

Batches are temporary in nature. To execute a batch more than once, you need to recreate SQL statements and submit them to the server. This leads to an increase in the overhead, as the server needs to compile and create the execu...

Implementing Triggers and its Types in SQL

In a relational database, data in a table is related to other tables. Therefore, while manipulating data in one table, you need to verify and validate its effect on data in the related tables. In addition, you might need to manip...

Creating Table-Valued Functions in SQL

A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table-valued function return the output as a table data type. The table data is a special data type used to store a set o...

Creating Scalar functions in SQL

In earlier article we have discussed about user defined functions that have the limited scope in sql programming in compare to stored procedures. User defined function have two types i.e. scalar function and table-valued function...

How to Implement User Defined Function in SQL

Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set...

Calling a Procedure from another Procedure: SQL

At times, you might need to use the values returned by a procedure in another procedure. For this, you can execute or call one procedure from within another procedure. A procedure that calls or executes another procedure is know...

How to Return values from Stored Procedure: SQL

Similar to providing input values to the procedures at run time, you can also return values as output from the procedures. The values can be returned to the calling application through output parameters. To specify a parameter as...

How to Create Parameterized Stored Procedure: SQL

At times, you need to execute a procedure for different values of a variable that are provided at run time. For this, you can create a parameterized stored procedure. Parameters are used to pass values to the stored procedure dur...

Guidelines to Create and Execute Stored Procedure: SQL

To create stored procedure database developer have to keep some points in mind. The following points need to be considered before creating a stored procedure: You cannot combine the CREATE PROCEDURE statement with other SQL sta...

Handling Errors and Exceptions using Try-Catch: SQL

When you execute a query, it is parsed for syntactical errors before execution. If the syntax is correct, it is compiled and executed. Sometimes, due to factors, such as incorrect data, an error can occur during execution even if...

How to Perform Searching Data by Using a Full-Text Search

After the full-text index has been created on a table, you can query the table by using the full-text predicates. The full-text predicates are used to specify how the search string should be searched in the table. Predicates can ...

Populating the Full-Text Index in Search: SQL Server

After creating the full-text index, you need to populate it with the data in the columns enabled for full-text support. The SQL Server full-text search engine populates the full-text index through a process called population. Pop...

Configuring Full-Text Search in SQL Server

The full-text query feature in the SQL Server enables users to search for a wide range of text in the SQL tables. Consider an example. The sales management team of AdventureWorks, Inc. makes frequent searches on the ProductDescri...

Altering, Dropping and Renaming Views in SQL Server

In addition to creating view in sql server, database developer also need to manage them. Management of a view includes altering, dropping, or renaming described below. Altering views If you define a view with a SELECT * statemen...

How to create Indexes on Views: SQL Server

Similar to the tables, you can create indexes on views. By default, the views created on a table are no indexed. However, you can index the views when the volume of data in the underlying tables is large and not frequently update...

How to apply restrictions at time of Modifying Data using Views

Views do not maintain a separate copy of the data, but only display the data present in the base tables. Therefore, you can modify the base tables by modifying the data in the view, however, the following restrictions exist while...

Creating View and Guidelines in SQL Server

Database administrator might want to restrict access of data to different users. They might want some users to be able to access all the columns of a table whereas other users to be able to access only selected columns. The SQL S...

How to Optimize Indexes in SQL Server

SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. These modifications cause the information within the index to become scattered in the database. Fragmentati...