Archive for March, 2010

MySQL “LIMIT” Equivalent in SQL Server 2005

First of all, there is no direct replacement of MySQL “LIMIT” in SQL Server. Let’s have a look at syntax for “LIMIT” in MySQL Server.

SELECT *
FROM [TABLE NAME]
ORDER BY [ORDER BY COLUMN NAME]
LIMIT 10 OFFSET 100

This query will select 101 to 110 th rows of data in MySQL. In SQL Server “TOP” can be used to select fixed number of rows from a Table or View.
Continue reading

Ajax Enabled Web User Control in ASP.NET

ASP.NET allows to create Web User Control and use it in Web Form like any other ASP.NET Web control. It increases code reusability eventually. Moreover, you can use Web User Control by just dragging and dropping it in your Web Form.


Continue reading

Retrieving Data from XML using T-SQL in SQL Server 2005

It’s quite simple to retrieve data from XML using T-SQL in SQL Server 2005. You can retrieve data using selection criteria as well.


Continue reading

XML Output From SELECT Command in SQL Server 2005

XML is widely accepted platform-independent format for data presentation. Here, I am going to show some simple SQL ways to generate XML output from Database table or views in SQL Server 2005.

Let’s start with creating a Customer table and inserting those rows of data.
Continue reading

.NET CLR Introduction with SQL Server 2005

SQL Server 2005 or later version allows hosting DOT NET CLR (Common Language Runtime) Object in Database. That is, you can write Stored Procedure, Triggers or Function with C# or VB and can host those in your SQL Server. Thus CLR extends SQL Server functionality allowing you to write Database Objects in any programming language of your choice.

First of all, CLR is not enabled in SQL Server by default. You have to enable it. This is straight forward using following SQL Commands.
Continue reading

Cross Tab Using CASE Expression, PIVOT, PIVOT with Dynamic Column Value in SQL SERVER 2005

Cross Tab Query or Report is one of the coolest features of Database Programming. Here, I am going to explain Cross Tab Query using CASE Expression and PIVOT. However, PIVOT is only available in SQL Server 2005 or later version.

Let’s create t_SaleReport table and insert some demo data in it.
Continue reading

CASE Expression in SQL Server 2005

SQL CASE Expression can be used effectively and efficiently with SELECT, UPDATE, DELETE Command or with WHERE, HAVING Clause. Here, I am going to show the use of CASE with SELECT Command.

CREATE TABLE t_Education
(
EducationID int IDENTITY(1,1) NOT NULL,
ApplicantID int NOT NULL,
ExamName nvarchar(20) NULL,
MajorName nvarchar(20) NULL,
ResultCode smallint NULL,
CGPA float NULL,
OutOfCGPA float NULL
)
Continue reading