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...
Wednesday, November 5, 2014
Wednesday, October 29, 2014
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...
Sunday, June 29, 2014
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...
Wednesday, June 4, 2014
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...
Monday, May 26, 2014
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...
Sunday, May 18, 2014
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...
Tuesday, May 13, 2014
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 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...
Sunday, May 4, 2014
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...
Sunday, April 27, 2014
How to Rename and Dropping a Table in SQL
You can rename a table whenever required. The sp_rename stored procedure is used to remaname table. Sp_rename can be used to rename any database object, such as a table,view, stored procedure, or function. The syntax of the sp_re...
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...
How to Create Rule and User Defined Data Type in SQL
A rule enforces domain integrity for columns or user-defined data types. The rules is applied to the column of the user-defined data type before an INSERT or UPDATE statement is issued. In other words, a rule specifies a restrict...
Tuesday, December 10, 2013
Customizing and Concatenating Output in SQL Database: SQL Server
Sql Server Management Studio have some options to customizing the display like adding the literals in the sql query to change the column name in output. It also have an option to concatenate strings in records, selected by sql qu...