Vadivel's blog

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 Va...

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=...

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</ProductNam...

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 use...