Vadivel's blog

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

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

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

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