Wednesday, July 27, 2005

Rank function in SQL Server 2005 ...

This article is to explain the functionality of Rank() function in SQL Server 2005. In our schooling days if the over all score of two guys are the same in exams then they would be ranked as 1 each and the next guy would get 3rd rank and not 2nd rank. To achieve same kind of result in SQL Server 2005 we can make use of this Rank() function.

Extract from MSDN:

If two or more rows tie for a rank, each of the tied rows receives the same rank. For example, if the two top sales people have the same SalesYTD value, then they are both ranked one. The sales person with the next highest SalesYTD is ranked number three, since there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

Table Structure for the sample table:

Create table DemoRankingFunction
(
Sno int identity,
FirstName varchar(25),
Marks int
)

Sample records for the demo purpose:

Insert into DemoRankingFunction Values ('Vadivel',99)
Insert into DemoRankingFunction Values ('Sai',99)
Insert into DemoRankingFunction Values ('Gowri',90)

Insert into DemoRankingFunction Values ('Rajini',86)
Insert into DemoRankingFunction Values ('Kamal',80)
Insert into DemoRankingFunction Values ('Sneha',80)
Insert into DemoRankingFunction Values ('Simran',76)

Let us write a query which would help us rank the students according to the marks scored.

Select
Sno,
FirstName,
Marks,
Rank() Over (Order by Marks desc)
From
DemoRankingFunction

Result:

Sno FirstName Marks Result
----- ------------ ------- --------
1 Vadivel 99 1
2 Sai 99 1
3 Gowri 90 3
4 Rajini 86 4
5 Kamal 80 5
6 Sneha 80 5
7 Simran 76 7

Clean up:

Delete table DemoRankingFunction

Technorati tags: , , ,

Monday, July 25, 2005

Creating XML Schema in SQL Server 2005

In this article let us see a very basic example of XML schema and its usage. In order to validate an XML file create a XSD for it. XSD allows you to define a full structure for a XML document that must be followed to be validated. If we enter a different structure it would result in an error. Let us create a sample table structure, schema and test it by inserting couple of records into it.

Create a XML Schema

Create xml schema collection TestTableSchema
as
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified" attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="TestTable">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="FirstName" type="xsd:string"/>
<xsd:element name="Age" type="xsd:integer"/>
<xsd:element name="Sex" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>';

This above schema defines the TestTable XML. It defines it to contain three elements:

1. FirstName,
2. Age and
3. Sex with corresponding datatypes. Let us attach the above scheme to a field while creating our test table.

Attach the XML Schema

Create table TestTable
(
Sno int identity primary key,
OtherDetails XML(TestTableSchema) not null
)

Test our Code

The below sample record would insert properly after the validation.

Insert into TestTable
Values (
'<TestTable>
<FirstName>Vadivel</FirstName>
<Age>29</Age>
<Sex>M</Sex>
</TestTable>')


The below sample code snippet would fail because we have introduced a new element which was not there in the XSD "salary".

Insert into TestTable
Values (
'<TestTable>
<FirstName>VadivelDeception Point</FirstName>
<Age>29</Age>
<Sex>M</Sex>
<Salary>1000</Salary>
</TestTable>')


It would throw an error message as folows:

Msg 6923, Level 16, State 1, Line 1
XML Validation: Unexpected element(s) at the end of content model: Salary


Technorati tags: , , ,

XML data type and Quoted Identifier

The XML data type has 5 methods namely,

1. exist
2. value
3. modify
4. query and
5. nodes

Extract from MSDN:

1. query() : Use this method to query over an XML instance.
2. value() : Use this method to retrieve a value of SQL type from an XML instance.
3. exist() : Use this method to determine if a query returns a nonempty result.
4. modify() : Use this method to specify XML DML statements to perform updates.
5. nodes() : Use this method to shred XML into multiple rows to propagate parts of XML documents into rowsets.

Code Snippet -- Part I

Declare @xmlString XML
Set @xmlString =
'
<Customer>
<Order id="102">
<ProductName>Rice</ProductName>
</Order>
</Customer>
'

Select testTable.testColumn.query('.') as Records
From @xmlString.nodes('/Customer/Order') testTable(testColumn)


On executing the above code snippet it would display the result as follows:
<Order id="102"><ProductName>Rice</ProductName></Order>

Now, let us excute the below line of code
Set Quoted_Identifier Off

After that if we try and execute the Code Snippet -- Part I ... it would fail. It would throw the below shown error message:

Msg 1934, Level 16, State 1, Line 10
SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns.


Thus the bottom line is all the XML data type methods requires Quoted_Identifier to be ON.

Technorati tags: , , ,

Sunday, July 03, 2005

Simple SP written using CLR in SQL Server 2005

1. Open a C# -- Database project
2. Create a new file into it.
3. Add the below code into it ...

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public partial class StoredProcedures
{
[SqlProcedure]

public static void SimpleSP()
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

//current month no
objPipe.Send(System.DateTime.Now.Month.ToString());

//no of days
objPipe.Send(System.DateTime.DaysInMonth(2005,7).ToString());

}
};

Just build it and deploy this code. Building and deploying is as simple as just right clicking on the project name and choosing the appropriate option.

Open SQL Server Management Studio

1. Create a new query
2. Try and execute the SP written via VS.NET

use TestDatabase
Exec SimpleSP

It would most probabaly throw an error like this:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

By default execution of the code in .NET framework is disable ... so just run the below two lines to enable it.
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Now try to execute the SP it would work fine and display the numeric value of the current month. Also it would display the number of days in the month of "July".