Thursday, December 30, 2004

[Yukon] About large value data types

The maximum capacity for Varchar / Varbinary in SQL Server 7 and 2000 are 8,000 bytes. Similarly for nvarchar it is 4,000 bytes. For any content which is more than 8000 bytes we would go for "Text, NText, or Image" data types.

In SQL Server 2005 it has changed greatly with the introduction of the "Max" specifier. The Max specifier allows storage of up to 2^31 bytes of data. For Unicode content it allows storage of up to 2^30 bytes.

When you use the Varchar(Max) or NVarchar(Max) data type, the data is stored as character strings, whereas for Varbinary(Max) it is stored as bytes.

Basic example showing the usage of this new specifier.

Create table PatientDetails
(
PatientNumber int Identity,
FirstName varchar(max),
LastName varchar(max),
Memo varchar(max)
)

In earlier version of SQL Server we cannot use Text, ntext or image data types as variables / parameters in stored procedure or user defined funtions. But that is perfectly valid in Yukon.

Passing character data as parameter to a user defined proceedure and doing string concatenation. This would work perfectly fine in Sql Server 7 and later.

Create proc uspStrConcatenation
(
@strInputValue varchar(10)
)
as
begin
Select 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue)) as 'Concatenated Value'
end

exec uspStrConcatenation 'vel'

Passing Text data as parameter to a user defined proceedure.

This would fail in Sql Server 7 and 2000 as Text / ntext / image etc., data types cannot be used as variables in procedures / functions.

Create proc uspTextConcatenation
(
@strInputValue Text)
as
begin
Select 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue)) as 'Concatenated Value'
end

In SQL Server 2005 we can now pass any size string as parameter.

Create function fnStrConcatenation
(
@strInputValue varchar(max)
)
returns varchar(max)
as
Begin
return 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue))
End

Saturday, December 25, 2004

Creating reports using Pivot operator

In this article let me try and compare the way to create cross tab reports in SQL Server 2000 and SQL Server 2005.

Cross Tab Report: Representing columns as Rows and Rows as Columns is known as cross tab report or PivotTable.

Sample Table Structure and Data

Create table TestPivot
(
YearOfSales Int,
SalesQuarter Int,
Amount money
)
go

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 1, 100)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 2, 200)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 3, 300)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 4, 400)
go

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 1, 500)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 2, 600)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 3, 700)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 4, 800)
go

Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 1, 900)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 2, 1000)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 3, 1100)
Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 4, 1200)
go

The below query is to create Cross tab report OR PIVOT table in SQL 2000. As expected it would work in SQL 2005 also.

Select YearOfSales,
sum(case SalesQuarter when 1 then Amount else 0 end) as Q1,
sum(case SalesQuarter when 2 then Amount else 0 end) as Q2,
sum(case SalesQuarter when 3 then Amount else 0 end) as Q3,
sum(case SalesQuarter when 4 then Amount else 0 end) as Q4
from TestPivot
group by YearOfSalesgo

The below query is to create Cross tab report OR PIVOT table in SQL 2005

Select * from TestPivotPIVOT (sum(Amount) for SalesQuarter IN ([1], [2], [3], [4])) as Pt

Thursday, December 23, 2004

Database Compatibility ...

Database Compatibility

This article would explain a practical way of using the system stored procedure "sp_dbcmptlevel".

Sp_dbcmptlevel :: Set the database compatibility level.

--------------------------------------------------------------------------------
By default,
for SQL Server 6.5 the comatibility level is 65,
for SQL Server 7.0 the comatibility level is 70,
for SQL Server 2000 the comatibility level is 80
--------------------------------------------------------------------------------


One can check their database compatibility level by executing the sp_dbcmptlevel system stored procedure.

Let me explain the way I made use of this system stored procedure in my previous company. We were having SQL Server 2k in our development environment but for a project our requirement were to use only SQL Server 6.5 (hmm quite old isn't it? when did you last heard about SQL Server 6.5 :) ). Its for sure that we can't purchase 6.5 version for this project alone. At that time sp_dbcmptlevel came to our rescue. We just executed the below query so that our database (get it right, its database level and NOT server level change) compatibility would be set to SQL Server 6.5

-- Replace Pubs with your actual database name
EXEC sp_dbcmptlevel 'pubs', 65

This way if by mistake you use any keywords which are not in version 6.5 it would throw an error. Once the project is done or anytime during the course of the project you can reset the compatibility level back to 80.

For testing purpose

1. EXEC sp_dbcmptlevel 'pubs'
This would give you the current compatibilty information for pubs database

2. EXEC sp_dbcmptlevel 'pubs', 65
This would set the compatibility to SQL Server 6.5 for Pubs database alone.

3. Select top 5* from authors
After executing point 2 if we run the above query it would throw an error because TOP keyword is not available in SQL Server 6.5

4. EXEC sp_dbcmptlevel 'pubs', 80
This would reset the compatibility of pubs database to SQL Server 2000. Now execute the
query mentioned in point 3 to see the top 5 records displayed.

For further reading:

I suggest you have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_5c8c.asp for more details on this topic.

Thursday, December 02, 2004

Row_Number function in Sql Server 2005

In this article let us look into the way of displaying sequential numbers in Yukon (code name of Sql Server 2005).

Method 1 (Sql Server 2000)

Create table TestTable
(
EmployeeNumber int,
FirstName varchar(50),
Salary money NULL
)
Go

Insert into TestTable Values (100,'Vadivel',10000)
Insert into TestTable Values (200,'Vadi',20000)
Insert into TestTable Values (300,'vel',30000)
Go

Select Identity(int, 1,1) as 'Serial number', * INTO #TempTable from TestTable
Select * from #TempTabledrop table #TempTable

Method 2 (Sql Server 2005)

Row_Number() :: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Select EmployeeNumber, FirstName, Row_Number() Over (Order By EmployeeNumber) as 'Row Number' From TestTable