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 of rows, which return the result set of a table-valued function. Table-valued functions are of two types:

Inline Table-Valued Function

An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements.

Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:

CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO

You can use the following statement to execute the fx_Department_Gname function with a specified argument:

SELECT * FROM fx_Department_GName (‘Manufacturing’)

The preceding statement will return a result set having the group name ‘Manufacturing’.

Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:

CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.

Multistatement Table-Valued Function

A Multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN…END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. The temporary table is returned in the result set and is created based on the specification mentioned in the function.

Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:

CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END

The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:

SELECT * FROM PayRate (45)

Depending on the result set returned by a function can be categorized as deterministic or nondeterministic. Deterministic functions always return the same result whenever they are called with a specific set of input values. However, nondeterministic function may return different results each time they are called with a specific set of input values.

An example of a deterministic function is date add, which returns the same result for any given set of argument values for its three parameters. Get date is a nondeterministic function because it is always invoked without any argument, but the return value changes on every execution.

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 of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.

UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.

Creating UDFs

A UDF contains the following components:

• Function name with optional schema/owner name
• Input parameter name and data type
• Options applicable to the input parameter
• Return parameter data type and optional name
• Options applicable to the return parameter
• One or more T-SQL statements

To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
]
)
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,

• Schema_name is the name of the schema to which the UDF belongs.
• Function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
• @parameter_name is a parameter in the UDF. One or more parameters can be declared.
• [type_schema_name.] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs.
• [=default ] is a default value for the parameter.
• Return_data_type is the return value of a scalar user-defined function.

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 updated. Indexing a view helps in improving the query performance.

Another benefit of creating an indexed view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. If the query contains references to columns that are also present in the indexed view, and the query optimizer estimates that using the indexed view offer the lowest cost access mechanism, the query optimizer selects the indexed view.

When indexing a view, you need to first create a unique clustered index on a view. After you have defined a unique clustered index on a view, you can create additional non-clustered indexes. When a view is indexed, the rows of the view are stored in the database in the same format as a table.

Guidelines for Creating an Indexed View

• You should consider the following guidelines while creating an indexed view:
• A unique clustered index must be the first index to be created on a view.
• The view must not reference any other views, it can reference only base tables.
• All base tables referenced by the view must be in the same database and have the same owner as the view.
• The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

Creating an Indexed View by Using the CREATE INDEX Statement

You can create indexes on views by using the CREATE INDEX statement. For example, you can use the following statements for creating a unique clustered index on the vwEmployeeDepDate view:

CREATE UNIQUE CLUSTERED INDEX idx_vwEmployeeDepData
ON HumanResources.vwEmployeeDepData (EmployeeID, DepartmentID)
The vwEmployeeDepData view was not bound to the schema at the time of creation. Therefore, before executing the preceding statement, you need to bind the vwEmployeDepData view to the schema using the following statement:

ALTER VIEW HumanResources.vwEmployeeDepData WITH SCHEMABINDING
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID

The preceding statement alters the existing view, vwEmployeeDepData, and binds it with the schema of the underlying tables. You can then create a unique clustered index on the view.

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 inserting, updating, or deleting data through views:

• You cannot modify data in a view if the modification affects more than one underlying table. However, you can modify data in a view if the modification affects only one table at a time.
• You cannot change a column that is the result of a calculation, such as a computed column or an aggregate function.

For example, a view displaying the employee id, manger id, and rate of the employees has been defined using the following statement:

CREATE VIEW vwSal AS
SELECT i.EmployeeID, i.MangerID, j.Rate FROM HumanResources.Employee AS i
JOIN HumanResources.EmployeePayHistory AS j ON
i.EmployeeID = j.EmployeeID

After creating the view, if you try executing the following update statement, it generates an error. This is because the data is being modified in two tables through a single update statement.

UPDATE vwSal
SET ManagerID = 2, Rate = 12.45
WHERE EmployeeID = 1

Therefore, instead of a single UPDATE statement, you need to execute two UPDATE statement for each table.

The following statement would update the EmployeeID attribute in the Employee base table:

UPDATE vwSal
SET ManagerID = 2
WHERE EmployeeID = 1

The following statement would update the Rate attribute in the EmployeePayHistory table:

UPDATE vwSal
SET Rate = 12.45
WHERE EmployeeID = 1

Therefore, to modify the data in two or more underlying tables through a view, you need to execute separate UPDATE statements for each table.

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 Server allows you to create views to restrict user access to the data. Views also help in simplifying query execution when the query involves retrieving data from multiple tables by applying joins.

A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data. A view can derive its data from one or more tables, called the base tables or underlying tables. Depending on the volume of data, you can create a view with or without an index. As a database developer, it is important for you to learn to create and manage views.

Creating Views

A view is a database object that is used to view data from the tables in the database. A view has a structure similar to a table. It does not contain any data, but derives its data from the underlying tables.

Views ensure security of data by restricting access to:

• Specific rows of a table
• Specific columns of a table
• Specific rows and columns of a table
• Rows fetched by using joins
• Statistical summary of data in a given table
• Subsets of another view or a subset of views and tables

Apart from restricting access, views can also be used to create and save queries based on multiple tables. To view data from multiple tables, you can create a query that includes various joins. If you need to frequently execute this query, you can create a view that executes this query. You can access data from this view every time you need to execute the query.

You can create a view by using the CREATE VIEW statement. The syntax of the CREATE VIEW statement is:

CREATE VIEW view_name
[ (column_name [, column_name]…)]
[WITH ENCRYPTION [, SCHEMABINDING] ]
AS select_statement [WITH CHECK OPTION]

Where,

• View_name specifies the name of the view.
• Column_name specifies the name of the column(s) to be used in a view.
• WITH ENCRYPTION specifies that the text of the view will be encrypted in the syscomments view.
• SCHEMABINDING binds the view to the schema of the underlying table or tables.
• AS specifies the action to be performed by the view.
• Select_statement specifies the SELECT statement that defines a view. The view may use the data contained in other views and tables.
• WITH CHECK OPTION forces the data modification statements to meet the criteria given in the SELECT statement defining the view. The data is visible through the view after the modifications have been made permanent.

Guidelines for creating views

While creating views, you should consider the following guidelines:

• The name of a view must follow the rules for identifiers and must not be the same as that of the table on which it is based.
• A view can be created only if there is a SELECT permission on its base table.
• A view cannot derive its data from temporary tables.
• In a view, ORDER BY cannot be used in the SELECT statement.

For example, to provide access only to the employee ID, marital status, and department ID for all the employees you can create the following view:

CREATE VIEW HumanResources.vwEmployeeDepData
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID

The preceding code crates the vwEmployeeDepData view containing selected columns from the Employee and EmployeeDepartmentHistory tables.

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 is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,

• UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
• Index_name specifies the name of the index.
• Table_name specifies the name of the table that contains the attributes on which the index is to be created.
• Column specifies the column or columns on which the index will be created.
• Column_name specifies the name of the column or columns on which the index would be created.
• ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
• ON filegroup_name specifies the filegroup on which index is created.
• ON DEFAULT specifies that the specified index will be created on the default filegroup.
• PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
• FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
• SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
• IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
• STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
• DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
• ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.

Developer can create online indexes only in the SQL Server 2005 Enterprise Edition.

Non-clustered index in sql
Guidelines to create indexes

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 type to extract data. As compared to the FOR XML clause of the SELECT statement, the XQuery statements allow you to extract specific parts of the XML data.

Each XQuery statement consists of two parts, prolog and body. In the prolog section, you declare the namespaces. In addition, schemas can be imported in the prolog. The body parts specifies the XML nodes to be retrieved. The XQuery language includes the following statements:

• For: Used to iterate through a set of nodes at the same level as in an XML document.
• Let: Used to declare variables and assign values.
• Order by: Used to specify a sequence.
• Where: Used to specify criteria for the data to be extracted.
• Return: Used to specify the XML returned from a statement.

The XQuery statements also use the following functions provided by the XML data type:

Query: Used to extract XML from an XML data type. The XML to be retrieved is bicycle is manufactured at AdventureWorks, it passes through a series of work centre locations. Each work centre location produces a different cycle component. Therefore, the number of production steps varies between different work centres.

To analyse the production process, the management of AdventureWorks needs to retrieve a list of the location IDs of all the work centers, which have more than four steps. You need to generate the list displaying the location ids in the ascending order of the steps included in the work centres.

To perform this task, the database developer can create the following query:

SELECT Instructons.query
(‘ declare namespace
ns=http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions:
for $work in /ns:root/ns:Location
where count(#work/ns:step) > 4
order by count ($work/ns:step)
return
count($work/ns:step)’) AS Result
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Value: Used to return a single value from an XML document. To extract a single value, you need to specify an XQuery expression that identifies a single node and a data type of the value to be retrieved.

For example, the management of AventureWorks, Ins. Wants a list containing the product model id, product name, machine hours, and labour hours. However, not all product have production instructions. As a database developer, you have stored this data in the XML format in the ProductModel table. You can create the following query to display the results:

SELECT ProductModelID, Name, Instructions.value (‘declare namespace ns=”http//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductManuInstructions”;
(/ns:root/ns:Location/@LaborHours) [1]’, ‘float’)AS
LaborHours,
Instructions.value(declare namespace
Ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions”;
(/ns:root/ns:Location/@MachineHours) [1]’, ‘float’) AS MachineHours
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Exist: Used to check the existence of a node in an XML data. The function returns I if the specified node exists else it returns 0. For example, the management of AdventureWorks, wants the details of all the customers in the city ‘NJ’. The details of all the customers are stored in an XML format in the CustDetails table. You can use the following query to display the results:

SELECT Cust_ID, Cust_Details.exist
(‘Customer[@City=’NJ”]’) AS ‘True’ FROM CustDetails

Retrieve XML data from DataSet

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 XML fragments. You can retrieve the XML data in the following ways:

• Using the FOR XML clause in the SELECT statement
• Using XQuery

Using the FOR XML Clause in the SELECT statement

SQL Server allows you to extract data from relational tables into an XML format by using the SELECT statement with the FOR XML clause. You can use the FOR XML clause to retrieve the XML data by using the following modes:

• RAW
• AUTO
• PATH
• EXPLICIT

Using the RAW Mode

The RAW mode is used to return an XML file with each row representing an XML element. The RAW mode transforms each row in the query result set into an XML element with the element name row. Each column value that is not NULL is mapped to an attribute with the same name as the column name.

The following statements display the details of employees with employee ID as 1 or 2:
SELECT EmployeeID, ContactID, LoginID, Title
FROM HumaneResources.Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML RAW

The preceding query displays the employee details in the following format:

<row EmployeeID=”1” ContactID=”1209” LoginID=”adventure-works/guy1” Title=”Production Technician – WC60” />
<row EmployeeID=”2” ContactID=”1030” LoginID=”adventure-works/kevin0” Title=”Marketing Assistant” />

Using the AUTO Mode

The AUTO mode is used to return query results as nested XML elements. Similar to the RAW mode, each column value that is not NULL is mapped to an attribute that is named after either the column name or the column alias. The element that these attributes belong to is named to the table that they belong to or the table alias that is used in the SELECT statement, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO

If the optional ELEMENTS directive is specified in the FOR XML clause, the columns listed in the SELECT clause are mapped to sub-elements, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO, ELEMENTS

Using the PATH Mode

The PATH mode is used to return specific values by indicating the column names for which you need to retrieve the data, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c
AND e.EmployeeID=1
FOR XML PATH

The preceding query displays the output in the following format:
<row EmpID=”1”>
<EmpName>
<First>Guy</First>
<Middle>R</Middle>
<Last>Gilbert</Last>
</Employee>
</row>

In the preceding result set, the EmployeeID column is mapped to the EmpID attribute with @ sign. The FirstName, MiddleName, and LastName columns are mapped as subelements of the EmpName element with the slash mark(/)

You can also use the optional ElementName argument with the PATH mode query to modify the name of the default row element, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID
AND e.EmployeeID=1
FOR XML PATH (‘Employee’)

Using the EXPLICIT Mode

The EXPLICIT mode is used to return an XML file that obtains the format as specified in the SELECT statement. Separate SELECT statement can be combined with the UNION ALL statement to generate each level/element in the resulting XML output. Each of these SELECT statements requires the first two tags to be called Tag and Parent. The Parent element is used to control the nesting of elements. It contains the tag number of the parent element of the current element. The top-level element in the document should have the Parent value set to 0 or NULL.

For example, the managers of AdventureWorks want to access the information regarding products through their mobile devices. These devices cannot directly connect to the SQL Server, but can read the data provided in the XML format. Therefore, you need to convert the details of the products from the Product table into the XML document. To perform this task, you need to create an XML document with <Product> as the parent tag. The <Product> tag will contain ProductID as an attribute and <ProductName> and <Color> as child elements.

To perform this task, the database developer can create the following query:

SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1!ProductName!element],
Color AS [Product!1!Color!elementxsinil]
FROM Production.Product
FOR XML EXPLICIT

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 save one or more XML schemas. You can create an XML schema collection object by using the following SQL statement:

CREATE XML SCHEMA COLLECTION <Name> as Expression
Where,

• Name specifies an identifier name with which the SQL Server will identify the schema collection.
• Expression specifies an XML value that contains one or more XML schema documents

For example, the customer details are associated with the following schema:
<?XML version=”1.0” ?>
<xsd:schema targetNamespace=”http//schemas.adventure-
Works.com/Customers” xmlns=”http//shemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=” unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”?>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>

You can use the following statements to register the preceding schema, named as CustomerSchemaCollection, with the database:

CREATE XML SCHEMA COLLECTION CustomerSchemaCollection AS n’<?xml version= “1.0” ?>
<xsd:schema targetNamespace=”http://schemas.adventure-
Works.com/Customers” xmlns=”http://schemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=”unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”.>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”Name” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>’

You can view information about the registered schemas in a database by querying the sys.XML_schema_collection catalog view, as shown in the following statement:
SELECT * FROM sys.XML_schema_collections

After registering the XML schema, you can use the schemas to validate typed XML values while inserting records into the tables. You need to specify this while creating a table that will store the XML data. In the preceding example, if you need to validate the customer details with the CustomerSchemaCollection schema, you need to create the CustDetails table by using the following statement:

CREATE TABLE CustDetails
(
CustID int,
CustDetails XML
)

You can insert data into this table by using the following statement:
INSERT INTO CustDetails VALUES (2, ‘<?xml version=”1.0”?> <CustomerName=”Abrahim Jones” City=”Selina” />’)

While executing the preceding statement, the SQL Server will validate the values for the CustDetails column against the CustomerSchemaCollection schema.

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 a website. The website saves the details of each customer in an XML file. As a database developer, you need to save this data in the SQL Server. For this, you can create the following table to store the customer details:

CREATE TABLE CustDetails ( CUST_ID int, CUST_DETAILS XML )

You can save the following types of data in the columns with the XML data types:
Untyped XML data: is also a well-formed data, but is not associated with a schema. The SQL Server does not validate this data, but ensures that the data being saved with the XML data type is well-formed.

Typed XML data: is a well-formed data that is associated with a schema defining the elements and their attributes. It also specifies a namespace for the data. When you save the typed XML data in a table, the SQL Server validates the data against the schema and assigns the appropriate data type to the data based on the data types defined in the schema. This helps in saving the storage space.
As a database developer, you should know how to store both types of data on the SQL Server.

Staring Untyped XML Data

To store the untyped XML data, you can use columns or variables with the XML data type. For example, to store customer data in the CustDetails table, you can use the following INSERT statement:

INSERT INTO CustDetails VALUES (2, ‘<Customer Name=’Abrahim Jones” City= “Selina” />’)

In the preceding statement, the string value that contains an XML fragment is implicitly converted to XML. However, you can also convert a string value to XML by using the CONVERT or CAST functions. In this example, you can use the following statement to convert the data type of the string value to XML before inserting the record into the table.

INSERT INTO CustDetails VALUES (2, convert (XML, ‘<CustomerName=”Abrahim Jones” City=”Selina” />’) )

Similarly, you can also use the CAST function, as shown in the following statement:

INSERT INTO CustDetails VALUES (4, cast (‘<Customer Name=”Abrahim Jones” City=”Selina” />’ as XML) )

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 received by a supplier in the XML format into the SalesOrderHeader and SalesOrderDetail tables.

Clearing the Memory

After saving the data permanently in the database, you need to release the memory where you stored the rowset. For this, you can use the sp_xml_removedocument stored procedure.
Consider an example where customers shop online and the order given by the customers are transferred to the supplier in the form of an XML document. Following is the data available in the XML document:

DECLARE @Doc int
DECLARE @XMLDoc nvarchar (1000)
SET @XMLDoc = N’ <ROOT>
<Customer CustomerID=”JH01” ContactName=”John Henriot”>
<Order OrderID=”1001 CustomerID=”JH01”
<OrderDate=”2006-07-04T00:00:00”>
<OrderDetail ProductID=”11” Quantity=”12”/>
<OrderDetail ProductID=”22” Quantity=”10”/>
<Order>
</Customer>
<Customer CustormerID=”SG01” ContactName=”Steve Gonzlez”>
<Order OrderID=”1002” CustomerID=”SG01”
OrderDate=”2006-08-16T00:00:00”>
<OrderDetail ProductID=”32” Quantity=”3”/>
</Order>
</Customer>
</ROOT>’

To view this XML data in a rowset, you need to execute the following statements:

• Create an internal representation of the XML document by executing the following statement:
  EXEC sp_xml_preparedocumnt @Doc OUTPUT, @XMLDoc
• Execute the following query to store the data in a table by using the OPENXML function:
  INSERT INTO CustomerDetails
  SELECT *
  FROM openxml (@Doc, ‘/ROOT/Customer’, 1)
  WITH (CustomerID varchar (10), ContactName varchar (20) )

The data that will be displayed as shown in the following table.
CustomerID ContactName
JH01 John Henriot
SG01 Steve Gonzlez

• Remove the internal tree from the memory by executing the following statement:
  EXEC sp_xml_removedocument @Doc

You can also specify the column pattern to map the rowset columns and the XML attributes and elements. You can use the following OPENXML statement with the preceding statements to specify the column pattern:

SELECT *
FROM openxml (@Doc, ‘/ROOT/Customer/Order/OrderDetail’,1)
WITH (CustomerID varchar (10) ‘../../@CustomerID’,
ContactName vchar (20)’../../@ContactName’, OrderID int ‘../@OrderID’,
OrderDate datetime ‘ ../@OrderDate’, ProdID int ‘@ProductID’, Quality int)

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 or if the quantity of a product ordered is changed, the required changes need to be made to the respective rows in the tables. You can use the UPDATE statement to make the changes. Updating ensures that the latest and correct information is available at any point of time. One column of a row is the smallest unit of an update.

You can update data in a table by using the UPDATE DML statement. The syntax of the UPDATE statement is:

UPDATE table_name SET column_name = value [, column_name = value]
[FROM table_name]
[WHERE condition]
Where,

• Table_name specifies the name of the table you have to modify.
• Column_name specifies the columns you have to modify in the specified table.
• Value specifies the value(s) with which you have to update the column(s) of the table. Some valid values include an expression, a column name, and a variable name. The DEFAULT and NULL keywords can also be supplied.
• FROM table_name specifies the table(s) that is used in the UPDATE statement.
• Condition specifies the rows that you have to update.

Guidelines for Updating Data

You need to consider the following guidelines while updating data:

• An update can be done on only one table at a time.
• If an update violates integrity constraints, then the entire update is rolled back.

The following statement updates the AddressLine2 attribute of AddressID 104

UPDATE Address
SET AddressLine2 = ‘Plaza Palace’
WHERE AddressID = 104

Consider another example where you need to update the Title of an employee, Lynn Tsoflias to ‘Sales Executive’, in the Employee table. To perform this task, you need to refer to the contact table to obtain the Contact ID. You can update the details by using the following statement:

UPDATE HumanResources.Employee SET Title = ‘Sales Executive’
FROM HumanResources.Employee e, Person.Contact c
WHERE e.contactID = c.ContactID
AND c.FirstName = ‘Lynn’ and c.LastName = ‘Tsoflias’

When the preceding command is executed, the Title will be changed to ‘Sales Executive’.

Delete data from database and related table.

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 the DELETE statement is:

DELETE [FROM] table_name
[FROM table (s)]
[WHERE condition]
Where,

• Table_name specifies the name of the table from which you have to delete rows.
• Table_name specifies the name of the table(s) required to set the condition for deletion.
• Condition specifies the condition that identifies the row(s) to be deleted.

For example, the following statement deletes the address details of AddressID 104 from the Address table:

DELETE Address
WHERE AddressID = ‘104’

Deleting Data from Related Tables

While deleting records form related tables, you need to ensure that you first delete the records from the table that contain the foreign key and then from the table that contains the primary key.

Consider the example of the Adventure Works. The Employee table contains data of those employees who have retired from the company. This data is not required anymore. This increases the size of the database.

You are required to ensure that this old data is removed from the Employee table. You can delete this data by using the following SQL statement:

DELETE FROM HumanResources.Employee
WHERE BirthDate < dateadd (yy, -60, getdate ())

The database contains tables related to the Employee table. The related tables are HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, HumanResources.EmployeePayHistory, and HumanResources.JobCandidate. The EmployeeID attribute in these tables is a foreign key to the EmployeeID attribute of the Employee table. Therefore, the query results in an error. Therefore, you need to delete data from the related tables before executing the preceding DELETE statement.

Deleting All the Records from a Table

As a database developer, you might need to delete all the records from a table. You can do this by using the following DELETE statement:

DELETE table_name

You can also use the TRUNCATE DML statement. The syntax of the TRUNCATE statement is:
TRUNCATE TABLE table_name
Where,

• Table_name specifies the name of the table from which you have to delete rows. However, TRUNCATE TABLE is executed faster.

TRUNCATE TABLE does not support the WHERE clause. In addition, the TRUNCATE TABLE statement does no fire a trigger. When truncate is used, the deleted rows are not entered in the transaction log.

For example, the following statement deletes all the records from the Address table:
TRANCATE TABLE Address

Manipulate XML Data and Parsing with XML document.

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 with the structure defined in the DTD or schema.

The parsed document is an internal tree representation of various nodes in the XML document, such as elements, attributes, text, and comments. Sp_xml_preparedocument returns a handle or pointer that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing

Sp_xml_removedocument.

Retrieving a Rowset from the Tree

After verifying the accuracy of the structure and completeness of data, you need to extract the data from the available XML data. For this, you can use the openxml function to generate an in-memory rowset from the parsed data. The syntax of the openxml function is:

Openxml ( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Where,

• Idoc specifies the document handle of the internal tree representation of an XML document.
• Rowpattern specifies the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.
• Flags indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. Flags is an optional parameter and can have the following values:
  0 – to use the default mapping (attributes)
  1 – to retrieve attribute values
  2 – to retrieve element values
  3 – to retrieve both attribute and element values
• Schemadeclaration specifies the rowset schema declaration for the columns to be returned by using a combination of column names, data types, and patterns.
• TableName specifies the table name that can be given, instead of SchemaDeclaration, if a table with desired schema already exists and no column patterns are required.

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 table that contains the primary key. Next, you can insert a row in the related table containing the foreign key.

For example, in the AdventureWorks database, the employee details are stored in the Person.Contact, HumanResources.Employee, HumanResources.EmployeeDepartmentHistory, and HumanResources.EmployeePayHistory tables.

To save the details for a new employee, you need to insert data in all these tables. The following statement insert data of a new employee into the database:

Inserting records in the Person.Contact table.

INSERT INTO Person.Contact VALUES (0, ‘Mr.’, ‘Steven’, NULL,’Fleming’,
NULL, ‘stevenfleming@adventure-works.com’, 1, ‘951-667-2401’,’B4802B37F8F077A6C1F2C3F50F6CD6C5379E9C79’, ‘3SA+edf=’, NULL, DEFAULT, DEFAULT)

INSERT INTO HumanResources.Employee VALUES (‘45879632’, 19978,’adventure-works/steven’, 185, ‘Tool Designer’, ‘ 1967-06-03 00:00:00.000’,
‘M’, ‘M’, ‘2006-08-01 00:00:00.000’, 1, 0, 0, 1, DEFAULT, DEFAULT)

INSERT INTO HumanResources.EmployeeDepartmentHistory VALUES (291, 2, 1, ‘2006-08-01 00:00:00.000’, NULL, DEFAULT)

INSERT INTO HumanResources.EmployeePayHistory VALUES (291,’2006-08-01 00:00:00.000’, 23.0769, 2, DEFAULT)

Copying Data from an Existing Table into a New Table

While inserting data in table, you might need to copy rows from an existing table to another table. You can do this by using the SELECT statement.

For example, in the AdventureWorks database, data for the employees with a remuneration rate of 35 or above is to be copied into a new table called Preferredemployee from the EmployeePayHistory table.
The following statements copy the values from the EmployeePayHistory table into the PreferredEmployee table:

SELECT * INTO PreferredEmployee
FROM HumanResources.EmployeePayHistory
WHERE Rate >= 35

The preceding statement will create a table named PreferredEmployee. The table will have the same structure as HumanResources.EmployeePayHistory.

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 information must be the same as the order in which attributes are listed for insertion.
• The values clause need not contain the column with the IDENTITY property.
• The data types of the information must match the data types of the columns of the table.

Consider an example of the Address table that is used to store addresses of the employees. The following table describes the structure of the Address table.

AddressID int NOT NULL
AddressLine1 nvarchar(60) NOT NULL
AddressLine2 nvarchar(60) NULL
StateProvinceID int NOT NULL
PostalCode nvarchar(15) NOT NULL

To insert a row into the Address table with all the column values, you can use any one of the following statements:
INSERT into Address
VALUES (104, ’24, Herbon Aptsi, ‘Arthor Lane’, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLIne1, AddressLine2, StateProvinceID, PostalCode)
VALUES (104, ’24, Herbon Apts’, ‘Arthor Lane’, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLine1, AddressLine2, PostalCode, StateProvinceID)
VALUES (104, ’24, Herbon Apts’, ‘Arthor Lane’, ‘60070’, 56)
Or
INSERT into Address
VALUES (104, ’24, Herbon Apts’, NULL, 56 ‘607009’)

Inserting Partial Data

Depending on the constraints applied to the columns of the tables, you can insert partial data into the database tables. This means that while performing an insert operation, you can insert data for selective columns in a table. It is not necessary that you have to insert values for all columns in the table. The SQL Server allows you to insert partial data for a column that allows NULL or has a default constraint assigned to it. The INSERT clause lists the columns for which data is to be inserted, except those columns that allow NULL or have a default constraint. The VALUES clause provides values for the specified columns.

In the previous example of Address table, the AddressLine2 column allows you to enter a NULL value in a row. Therefore, you can use the following statements to insert partial data into the table:

INSERT into Address
VALUES (104, ’24, Herbon Apts’, NULL, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLine1, PostalCode, StateProvinceID)
VALUES (104, ’24, Herbon Apts’, ‘607009’, 56)

Manipulate data in tables

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_rename stored procedure is:
Sp_rename old_name, new_name
Where,

• Oldname is the current name of the object.
• Newname is the new name of the object.

The following SQL query renames the EmployeeLeave table:
Sp_rename [HumanResources.EmployeeLeave] ,
[HumanResources.EmployeeVacation]

You can also rename a table by right-clicking the Table folder under the Database folder in the Object Employer window and selecting the rename option from the shortcut menu. After a table is created, you may need to see the details of the table. Details of the table include the column names and the constraints. For this purpose, you can use the sp_help command.

Dropping a Table

At times, when a table is not required, you need to delete a table. A table can be deleted along with all the associated database objects, such as its index, triggers, constraints, and permissions. You can delete a table by using the DROP TABLE statement. The syntax of
DROP TABLE [ database_name . [ schema_name ]. ] table_name
Where,

• Database_name specifies the name of the database where th table is created.
• Schema_name specifies the name of the schema to which the table belongs.
• Table_name specifies the name of the table that needs to be dropped.

When a table is deleted, any other database object referenced by the table needs to be deleted explicitly. This should be done before deleting the table. This is because while deleting a table, if violations occur in the rule of referential integrity then an error occurs that restricts you from deleting the table. Therefore, if your table is referenced then you must delete the referenced table or the referenced constraint and then delete the table.

For example in the HumanResources schema, the Employee table contains EmployeeID as its primary key. The EmployeeLeave table under the same schema contains EmployeeID as its foreign key and is referenced with the EmployeeID column of the Employee table. Therefore, when you want to delete the Employee table, you first need to delete the EmployeeLeave table.

You can also delete a table by right-clicking the Tables folder under the Database folder in the Object Explorer window and selecting the Delete option from the shortcut menu.

Create table using partition scheme

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 column. This helps in managing the data and improving the query performance.

Consider the example of a manufacturing organization. The details of inventory movements are stored in the InventoryIssue table. The table contains a large volume of data and the queries take a lot of time to execute thereby slowing the report generation process.

To improve the query performance, you can partition the table to divide the data based on a condition and store different parts of the data in different locations. The condition can be based on the date of transaction and you can save the data pertaining to five years at a location. After partitioning the table, data can be retrieved directly from a particular partition by mentioning the partition number in the query.

In the preceding example, the partitioned table were created after the database had already been designed. You can also create partitioned tables while designing the database and creating tables. You can plan to create a partitioned table when you know that the data to be stored in the table will be large. For example, if you are creating a database for a banking application and you know that the transaction details will be voluminous, you can create the transaction table s with partitions.

Partitioning a table is only allowed in Enterprise Edition of SQL Server. To create a partitioned table, perform the following tasks:

• Create a partition function.
• Create a partition scheme.
• Create a table by using the partition scheme.

For example, the AdventureWorks database stores the data of all the employees for the last 11 years. This data includes the personal details of the employees and their payment rates. Whenever there is a change in the payment rate of an employee, it is recorded in a separate record. However, this result in generation of large volume of data. This adversely affects the query performance.

To improve the query performance, the database developer needs to partition the table storing the changes in wage rate.

Create Rule and User-defined DataType

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 restriction on the values of a column or a user-defined data type. Rules are used to implement business-related restrictions or limitations. A rule can be created by using the CREATE RULE statement.

The syntax of the CREATE RULE statement is:
CREATE RULE rule_name AS conditional_expression
Where,

• Rule_name specifies the name of the new rule that must conform to rules for identifiers.
• Conditional_expression specifies the condition(s) that defines the rule. It can be any expression that is valid in a WHERE clause and can include elements, such as arithmetic operators, relational operators, IN, LIKE, and BETWEEN.

The variable specified in the conditional expression must be prefixed with the @ symbol. The expression refers to the value that is being specified with the INSERT or UPDATE statement.

In the preceding example of the EmployeeLeave table, you applied a rule to accept only three values: ‘CL’, ‘SL’, and ‘PL’. You can perform the same task by creating a rule. You can use the following statement to create the rule:
CREATE RULE rulType
AS @LeaveType IN (‘CL’, ‘SL’, ‘PL’)

After you create the rule, you need to activate the rule by using a stored procedure, sp_bindrule.

The syntax of sp_bindrule is:
Sp_bindrule <’rule’>, <’object_name’>, [<’futureonly_flag’>]
Where,

• Rule specifies the name of the rule that you want to bind.
• Object_name specifies the object on which you want to bind the rule.
• Futureonly_flag applies only when you want to bind the rule to a user-defined data type.

Using a user defined data type

User-defined data types are custom data types defined by the users with a custom name. User-defined data types allow modifying the composite data type used in the database. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values.

You can create user-defined data types by using the CREATE TYPE statement. The syntax of the CREATE TYPE statement is:
CREATE TYPE [schema_name. ] type_name {FROM base_type [ ( precision [, scale ] ) ] [ NULL | NOT NULL ] } [;]
Where,

• Schema_name specifies the name of the schema to which the alias data type or the user-defined data type belongs.
• Type_name specifies the name of the alias data type or the user-defined data type.
• Base_type specifies the SQL Server supplied data type on which the alias data type is based.
• Precision specifies the decimal or numeric point. Decimal and numeric are non-negative integers that indicate the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.
• Scale specifies the decimal or numeric scale.
• NULL | NOT NULL specifies whether the data type can hold a null value. If not specified, NULL is the default value.

The following SQL query creates a user-defined data type for descriptive columns:
CREATE TYPE DSCRP
FROM varchar (100) NOT NULL;

In the preceding example, a user-defined data type DSCRP is created to store the varchar data type and the size limit is specified as 100. Further, it also specifies NOT NULL. Therefore, you can use this data for the columns that hold description, address, and reason.

For example, you can use the DSCRP data type to store the data of the LeaveReason column of the EmployeeLeave table, as shown in the following statement:

CREATE TABLE HumanResources.EmployeeLeave
(
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES
HumanResources.Employee (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY KEY (EmployeeID, LeaveStartDate),
LeaveEndDate datetime NOT NULL,
LeaveReason DSCRP,
LeaveType char(2) CONSTRAINT chkLeave CHECK (LeaveType IN (‘CL’, ‘SL’, ‘PL’)) CONSTRAINT chkDefLeave DEFAULT ‘PL’)

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 query in sql server. In this article we will do these tasks with some examples in sql.

Customizing the Display

Sometimes, you might be required to change the way the data is displayed on the output screen. For example, if the names of columns are not descriptive, you might need to change the column headings by creating user-defined headings.

Consider the following example that displays the Department ID and Department Names from the Department table of the Adventure Works database. The report should contain column headings different from those given in the table, as specified in the following format.

Department Number  Department Name

You can write the query in the following ways:

• SELECT ‘Department Number’ – DepartmentID, ‘Department Name’ FROM HumanResources.Department
• SELECT DepartmentID ‘Department Number’, Name ‘Department Name’ FROM HumanResources.Department
• SELECT DepartmentID AS ‘Department Number’, Name AS ‘Department Name’ FROM HumanResources.Department

Similarly, you might be required to make results more explanatory. In such case, you can add more text to the values displayed by the columns by using literals. Literals are string values enclosed in single quotes and added to the SELECT statement. The literal value is printed in a separate column as they are written in the SELECT list. Therefore, literals are used for display purpose.

The following SQL query retrieves the department-Id and their name from the Department table and change the column header as specified in the query.

SELECT DepartmentID 'Department Number', Name 'Department Name'
FROM Department

The SQL Server will display the output of the query, as shown in the following figure

Add caption

Concatenating the Text Values in the Output

As a database developer, you will be required to address requirements from various users, who might want to view results in different ways. You might be required to display the values of multiple columns in a single column and also to improve readability by adding a description with the column value. In this case, you can use the Concatenation operator. The concatenation operator is used to concatenate string expressions. They are represented by the + sign.

The following SQL query concatenates the data of the Name and GroupName columns of the Department table into a single column. Text values, such as “department comes under” and “group”, are concatenated to increase the readability of the output:

SELECT Name + ' department comes under ' + groupName + ' group' AS Department
FROM Department

When you execute the query, it will format the output according to above query. The SQL Server will display the output of the query, as shown in the following figure.

Retrieving specific Records
Retrieve Selected Rows and Calculate Column values