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 use Case and While statement in Batches: SQL

Database developer can use the CASE as well as While statement in situation where several conditions need to be evaluated. Using CASE statement The CASE statement evaluates a list of conditions and returns one of the possible re...

How to use Constructs in Batches for Conditional Execution: SQL

SQL Server allows you to use programming constructs in the batches for conditional execution of statements. For example, you need to retrieve data based on a condition. If the condition is not satisfied, a message should be displ...

How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns...

How to Modify XML Data using Functions in SQL Server

Similar to any other type of data, programmer might also need to modify the XML data. To modify data, you can use the modify function provided by the XML data type of the SQL Server. The modify function specifies an XQuery expres...

How to Retrieve XML Data Using XQuery

In addition to FOR XML, SQL Server allows programmer to extract data stored in variables or columns with the XML data type by using XQuery. XQuery is a language that uses a set of statements and functions provided by the XML data...

How to Retrieve XML Data from a Database Table: SQL

At times, you need to retrieve the relational data from a table into the XML format for reporting purposes or to share the data across different applications. This involves extracting data from a table in the form of well-formed ...

How to Store Typed XML Data in XML Columns: SQL

To store the typed XML data, programmer need to first register the schema associated with the data in the XML schema collection objects in the database. The XML schema collection is an object on the SQL Server that is used to sav...

How to Store XML Data in XML Columns: SQL

At times, Programmer need to store the XML data in its original state in a column of a database table. For example, you need to save the details of customers in the database. The details of individual customers are maintained by ...

How to Store Data from Rowset: SQL

Programmer can use the rowset created by openxml to store the data, in the same way that you would use any other rowset. You can insert the rowset data into permanent tables in a database. For example, you can insert the data rec...

How to Update Data in tables: SQL

Programmer need to modify the data in the database when the specifications of a customer, a client, a transaction, or any other data maintained by the organization undergo a change. For example, if a client changes his address o...

How to Delete Data from Table or Related Table, SQL

Programmer need to delete data from the database when it is no longer required. The smallest unit that can be deleted from a database is a row. You can delete a row from a table by using the DELETE DML statement. The syntax of th...

How to Manipulate XML data in Database Table, SQL

With a growth in clients accessing data through heterogeneous hardware and software platforms, a need across for a language that could be interpreted by any environment. This resulted in the evolution of a language called XML. SM...

How to Parse XML Document and Retrieve Rowset: SQL Server Syntax

SQL Server provides the sp_sml_preparedocument stored procedure to parse the XML document. This stored procedure reads the XML document and parses it with the MSXML parser. Parsing an XML document involves validating the XML data...

How to Insert Rows in Related Tables and Copy Data: SQL

Data pertaining to an entity can be stored in more than one table. Therefore, while adding information for a new entity, you need to insert new rows in all the related tables. In such a case, you need to first insert a row in the...

How to Insert Rows and Partial Data in Database Table: SQL

While inserting rows into a table, Database developer need to consider the following guidelines: The number of data values must be the same as the number of attributes in the table or column list. The order of inserting the inf...

How to Manipulate Data in Tables using DML Statements: SQL Syntax

After creating a database and the tables, the next step is to store data in the database. As a database developer, you will be required to modify or delete data. You can perform these data manipulations by using the Data Manipula...

Creating a Table by Using the Partition Scheme

After you create a partition function and a partition scheme, you need to create a table that will store the partition records. You can use the following statements for the same: Create Table EmpPayHistPart ( EmployeeID int, Rate...

How to Create Partition Function for Particular Column: SQL

A partition function specifies how a table should be partitioned. It specifies the range of values on a particular column. Based on which the table is partitioned. For example, in the scenario of Adventure Works, you can partitio...

How to Create Partitioned Table in SQL Server

When the volume of data in a table increases and it takes time to query the data, you can partition the tables and store different parts of the tables in multiple physical locations based on a range of values for a specific colum...