TEXT to VARCAHR(MAX)

                                    TEXT to VARCAHR(MAX)



You may have migrated from SQL Server 2000 to SQL Server 2005/2008. There are several tables in your database having text data type columns. Text data type and image columns are deprecated, and replaced by varchar (max) and varbinary(max) . Apart from this, if the text data is stored outside the row, when this column is referenced in select, insert, update statements, it will consume extra reads slowing down query performance

You can check, whether text data is stored inside the row or on a separate data page by using the following query

select objectproperty(object_id('tablename'),'TableTextInRowLimit')

If the output is 0 then text in row limit is not set. i.e. The text column(s) are stored out side the data page and pointers in the data row, references the text data stored in text pages.

In SQL 2005 and above, by default lob data stored inside the data row. If we want to store lob data outside the row

EXEC sp_tableoption 'tablename', 'large value types out of row', 1;

Step by Step process to convert text to varchar(max)

We will take an example of an employee table and go through the step by step process to convert text data types to varchar (max) .

Script 1 :
use tempdb
go
if exists (select 1 from sys.tables where name = 'employee')
begin
drop table dbo.employee
end

create table dbo.employee
(
employee_id int identity(1,1) primary key ,
employee_name varchar(25) not null,
salary numeric(10,2) not null,
employee_info text
)
go

insert into employee ( employee_name,salary,employee_info)
values ('Rama',10000,'Good Character' )
insert into employee ( employee_name,salary,employee_info)
values ('Mohammad',40000,'Nice' )
insert into employee ( employee_name,salary,employee_info)
values ('James',50000,'Compassion' )
insert into employee ( employee_name,salary,employee_info)
values ('Saraswathi',50000,'Has high Knowledge' )
insert into employee ( employee_name,salary,employee_info)
values ('Krishna',20000,'Very Good' )
insert into employee ( employee_name,salary,employee_info)
values ('Fathima',32000.40,'Caring' )
insert into employee ( employee_name,salary,employee_info)
values ('Teresa',44000.50,'Professional' )
insert into employee ( employee_name,salary,employee_info)
values ('Buddha',90000.00,'Calm' )
go

Script 2 :

select objectproperty(object_id('employee'),'TableTextInRowLimit')
–- Returns 0 , which means text column rows are stored outside the data page
go

Now, let us create a function which will return page number in readable format
Script 3 :
create function dbo.ufn_convertpage_nums(@page_num binary (6))
returns varchar(20)
as
begin
return
( convert(varchar(2),(convert(int,substring(@page_num,6,1)) * power(2,8)) +
convert(int,substring(@page_num,5,1)))) + ':' +
convert( varchar(11),
( convert(int, substring(@page_num,4,1 )) * power(2,24)) +
( convert(int, substring(@page_num,3,1 )) * power(2,16)) +
( convert(int, substring(@page_num,2,1 )) * power(2,8)) +
( convert(int, substring(@page_num,1,1 )))

)
end

go



Script 4 : CAUTION: Don’t run this on production server. Run only on test server

use tempdb
go

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE ('All')

set statistics io on
go

set nocount on
select employee_id,employee_name, employee_info from dbo.employee
go

set statistics io off
go

Output is

-- Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 16, lob physical reads 0, lob read-ahead reads 0.
There are 16 lobs (large object) logical reads. Since there are 8 rows in the table
Script 5 :
select convert(varchar(25),object_name(p.object_id)) as tablename,
a.type_desc as page_type_desc,
total_pages as totpages,
used_pages,
data_pages ,
partition_number as pnum,rows ,
dbo.ufn_convertpage_nums(first_page) as first_page,
dbo.ufn_convertpage_nums(root_page) as root_page,
dbo.ufn_convertpage_nums(first_iam_page) as first_iam_page,
partition_id,
allocation_unit_id
from sys.partitions p
inner join sys.system_internals_allocation_units a
on p.partition_id = a.container_id
where p.object_id = object_id('dbo.employee')
go



tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id

employee IN_ROW_DATA 2 2 1 1 8 1:155 1:155 1:158 72057594038648800 72057594043236300

employee LOB_DATA 2 2 0 1 8 1:153 1:153 1:154 72057594038648800 72057594043301800



There are 2 LOB_DATA pages



After migration for previous versions of SQL server to SQL Server 2005/2008, text data type should be altered to varchar (max)

Script 6 :
alter table employee
alter column employee_info varchar(max)
go



Again, Run Script 4

Table 'employee'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 8, lob physical reads 1, lob read-ahead reads 0.
Lob reads reduced from 16 to 8


Running Script 5
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id

employee IN_ROW_DATA 2 2 1 1 8 1:155 1:155 1:158 72057594039238600 72057594044809200

employee LOB_DATA 2 2 0 1 8 1:153 1:153 1:154 72057594039238600 72057594044874700

employee ROW_OVERFLOW_DATA 0 0 0 1 8 0:0 0:0 0:0 72057594039238600 72057594044940200

You can see that a new page type description ROW_OVERFLOW_DATA is created. This page type is used when a row,does not have enough space to hold its maximum capacity of 8060 bytes

To make it more efficient, and reduce lob reads we need to update the table. We are updating the same column to push out of row data to inside the data row.

Script 7 :

update employee set
employee_info = employee_info
go



Note: If there are large number of rows , update in batches to reduce logging and performance improvement.

Again, Run Script 4

Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0,

lob physical reads 0, lob read-ahead reads 0.

Number of Lob reads are reduced to 0. This means data is moved from outside text pages to inside the data row

As an alterantive we can create the table with varchar(max) instead of text data type

Script 8 :

Note: In SQL Server 2005 and later, by default varchar(max) data is stored in the data row,
if the length of row does not exceed 8060. If length of row reaches this threshold,
data is pushed out to large object page (text page)

if exists (select 1 from sys.tables where name = 'employee')
begin
drop table dbo.employee
end

create table dbo.employee
(
employee_id int identity(1,1) primary key ,
employee_name varchar(25) not null,
salary numeric(10,2) not null,
employee_info varchar(max)
)
go



Run the insert script (Script 1 )

Run Script 4

Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Run Script 5

tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id

employee IN_ROW_DATA 2 2 1 1 8 1:153 1:153 1:154 72057594039304100 72057594045005800

employee LOB_DATA 0 0 0 1 8 0:0 0:0 0:0 72057594039304100 72057594045071300

After migration or if there are text data type columns , it is better to create a new table replacing text data types with varchar(max) data type and populating data from the old table

Conclusion

Recommended Method After Migration:


1. Create a new table (employee_new) replacing text data type with varchar(max)
2. Populate from old table (employee) to employee_new .
Note: If number of rows are very high , insert in batches to avoid bloating of log file and to improve performance
3. Drop old table (employee) . You may be required to drop constraints if any before dropping the table
4. Rename new table (employee_new) to old table (employee) . Put back the dropped constraints if any

Alternative Method

1. Alter table data type from text to varchar(max)
2. Update the same column
Note: If number of rows are very high ,update in batches to avoid bloating of log file and to improve performance

SQL Server 2000 to 2005, 2008 Migration Tips for Database Code from the field

Upgrade advisor is one of the tools which helps to detect compatibility issues of  the database code (SPs,Functions, View, Triggers) while Migrating from SQL Server 2000 to higher versions.

But with my experience this will not provide complete information .

If codebase is not in sync in various enviornments (Development,QA,UAT,Staging,Production), Database script need to be collected from these enviornments to the local system and compatibility issues can be checked.

I.
1. Install SQL Server 2008 with appropriate instance name in a test server.

2. Configure start up accounts for SQL Server, Agent, MSDTC (if required) and other services for SQL 2008

Note: If this start up account requires network access, proper share directory paths and access rights (read/write) need to be provided

3. Create an empty database in the new version of SQL server 2008/2005
Change database Compatibility to 100 (2008) /90 (2005)

4. Run Upgrade Advisor 2008/2005 on SQL 2000 version

5. Note both DTS errors and Database Engine analysis errors.

6. Correct errors reported in SQL 2000

7. Take entire database script in SQL 2000 with drop and create option selected for objects .

8. Run the database script on empty database ( Step 3 database)

9. Correct the code and Save the modified code in a file

10. Drop the empty Database ( step 3 ) in the new version of SQL Server

Following are the code issues encountered during migration

Note: Dynamic SQL Statements in the code are not checked in the following
steps. This may require manual checking or running the code .

Following tables are created for examples below

create table #tablea ( col1 int, amount numeric(10,2))

insert into #tablea values (1,10.5)
insert into #tablea values (1,40.8)
insert into #tablea values (2,84.2)
insert into #tablea values (4,100.6)

create index index_ix_col1 on #tablea (col1)


create table #tableb (col1 int, stringdesc varchar(10))

insert into #tableb values ( 1,'abcd')
insert into #tableb values ( 4,'pqrsi')
insert into #tableb values ( 8,'xyzq')


---------------------------------------------------------------------------------

1. BACKUP LOG WITH NO_LOG,TRUNCATE_ONLY statements

Resolution:

From BOL


None. The transaction log is automatically truncated when the database
is using the simple recovery model.
If you must remove the log backup chain from a database,
switch to the simple recovery model.

Remove this statement


---------------------------------------------------------------------------------
2. sysperfinfo returns a bigint value for the cntr_value column.
Modify applications that use sysperfinfo to ensure that they can handle
the bigint values of the cntr_value column.

Resolution:

If cntr_values are captured in to tables ( #temp, table varibales, actual tables)
or stored in declared variables for performance analysis,
declaration should be changed to bigint

---------------------------------------------------------------------------------

3. With some exceptions,table hints are supported in the
FROM clause of a query only when the hints are specified using the WITH keyword.

Resolution:

In SQL 2000

select col1
from #tablea a (index=index_ix_col1)


In SQL 2005 and above add with clause for table hints

select col1
from #tablea a with (index=index_ix_col1)



---------------------------------------------------------------------------------

4.Column aliases in the ORDER BY clause cannot be prefixed by the table alias.

In SQL 2000

select
case when a.col1 = 1 then a.amount else 0 end as colamount
from #tablea a
order by a.colamount

Resolution:

In SQL 2005 and above remove table alias and use column alias

select
case when a.col1 = 1 then a.amount else 0 end as colamount
from #tablea a
order by colamount


---------------------------------------------------------------------------------
Info and Resolution

5.In SQL Server 2000, SQL Server Agent can write logs to the file system for jobs
that are owned by members of the sysadmin fixed server role.
If the job owner is not a member of the sysadmin role
and if the proxy account is enabled,
SQL Server Agent can write logs to the file system by using the
credentials of the proxy account.
After you upgrade, jobs that are owned by users
who are not members of the sysadmin fixed server role
can no longer write logs to the file system.
Instead, these users can select the option to write
their logs to a table in the msdb database.
Members of the sysadmin role can still write log files to the file system.

---------------------------------------------------------------------------------

6. Each GROUP BY expression must contain at least one column that
is not an outer reference

In SQL 2000

if exists
(select 1 from #tablea a
where not exists (select 1 from #tableb b
where b.stringdesc='abcd' and a.col1=b.col1
group by b.stringdesc,a.col1)
)

print 'ok'

Resolution:


In SQL 2005 and above -- a.col1 changed to b.col1 in group by

if exists
(select 1 from #tablea a
where not exists (select 1 from #tableb b
where b.stringdesc='abcd' and a.col1=b.col1
group by b.stringdesc,b.col1)
)

print 'ok'


---------------------------------------------------------------------------------

7. Cannot create procedure '....' with a group number of 2 because a procedure
with the same name and a group number of 1 does not currently exist in the database.
Must execute CREATE PROCEDURE '....';1 first.

Resolution:

This may happen if the main SP ( without verion no or with ;1 ) failed to create the Original SP. If sp;1 is not used in SQL 2000 , create a dummy SP with create procedure SP;1 and then create different versions (;2,;3 etc)
---------------------------------------------------------------------------------

8. ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Example:

select a.col1, a.amount as col2alias
from #tablea a
where a.col1 > 0

union all

select a.col1, a.amount
from #tablea a
where a.col1 < 0
order by a.col1 desc, a.amount

Note: col2 is aliased as col2alias which is part of the order by clause . Column aliases should be used in order by,  instead table alias.column name . Also avoid table alias in the order by clause.  Above statement will work in all versions of SQL Server 2000,2005,2008,better to change to the following

Resolution:


select a.col1, a.amount as col2alias
from #tablea a
where a.col1 > 0

union all

select a.col1, a.amount
from #tablea a
where a.col1 < 0
order by a.col1 desc, col2alias

 ---------------------------------------------------------------------------------

9. Setuser failed because of one of the following reasons: the database principal 'ownername' does not exist,its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

Resolution:

If there are objects (tables, stored procedures etc) created with ownerid in sql 2000,corresponding schema need to be created in sql 2005 and above

---------------------------------------------------------------------------------

10. The specified schema name "ownername" either does not exist or you do not have permission to use it.

Resolution:

In SQL 2005 and above object owner names are replaced with schema.
For object owners other than dbo, create corresponding schema and create objects in the schema .
Provide proper access to the users through schema.

---------------------------------------------------------------------------------

11.
In SQL Server 2005, syslockinfo and sp_lock may return unexpected values,
and may return multiple rows where previous versions of syslockinfo and sp_lock
returned at most two rows. Also, in order to access information from syslockinfo
or execute sp_lock in SQL Server 2005, the user must have VIEW SERVER STATE permission on the server.

Resolution:


If sp_lock is used for monitoring purpose in SP or job change it accordingly to accomdate new rows

---------------------------------------------------------------------------------
12. The query uses non-ANSI Outer join Operators ( "*=" or "=*" )

Resolution

Change query to left outer join ( =*) or right outer jon ( *=)

---------------------------------------------------------------------------------


drop #tablea
drop #tableb

---------------------------------------------------------------------------------

II.

SQL Server 2005 onwards password is case sensitive irrespective of collation settings . If connection strings from web servers are not in approriate case , logins will fail . Change connection strings accordingly

III.

If bcp/bulk insert uses format files in SQL 2000 , these are not compatible in SQL 2008 . Regenerate the format files before using bulk insert/bcp .

Find Members of an NT Group

-- Find Members of DOMAIN belonging to a user group 'UserGroup'

exec master..xp_logininfo 'DOMAIN\UserGroup','members'
exec master..xp_logininfo 'DOMAIN\Administrators','members'

user connections ,Logical Connections, Threads, Logins , Logouts

select *
from sys.dm_os_performance_counters a
where a.object_name = 'SQLServer:General Statistics'
and counter_name in ('user connections ','Logical Connections','Logins/sec','Logouts/sec')

--Number of threads used with MSSQL 2005

select count(*) as 'Number of threads'
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL';

Statements having High CPU usage on execution count

-- High CPU queries -- on execution count SELECT TOP 50 total_worker_time/execution_count AS avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats OUTER APPLY ( select dbid,objectid from ( select attribute,cast(value as int) as attvalue from sys.dm_exec_plan_attributes(plan_handle) where attribute in ('dbid','objectid') ) as depa PIVOT ( MAX(depa.attvalue) FOR depa.attribute IN ("dbid", "objectid") ) AS depapvt ) as depamain ORDER BY execution_count DESC;

Statements having High CPU Usage

select top 50 total_worker_time/execution_count as avg_cpu_cost, execution_count,db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname,
(selecct substriung(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1 then len(covert(nvarchar(max), text)) * 2 else statement_end_offset nd - statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
outer apply
( select dbid,objectid from
( select attribute,cast(value as int) as attvalue
from sys.dm_exec_plan_attributes(plan_handle)
where attribute in ('dbid','objectid')
) as depa
pivot ( max(depa.attvalue) for depa.attribute
in ("dbid", "objectid") ) as depapvt ) as depamain
where execution_count > 4
order by [avg_cpu_cost] desc;

Drop column after checking regular constraints

-- use database
go
-- SQL 2005 and above
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_lib_drop_column]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_lib_drop_column]
GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
 /* exec sp_lib_drop_column 'constratintcheckhead','accountid','l' exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
-- testing
create table constratintcheckhead ( accountid int not null primary key ,clientid int not null ) create table constraintcheckdetails ( accountid int not null ,notesid int not null primary key (accountid,notesid) )
alter table constratintcheckhead add constraint DF_accountid default 0 for accountid
alter table constratintcheckhead add constraint CK_accountid check (accountid >= 0 )
alter table constratintcheckhead add constraint UQ_account Unique (accountid,clientid ) alter table constraintcheckdetails add constraint FK_constraintcheckdetails_constratintcheckhead foreign key (accountid) references constratintcheckhead (accountid)
create index IX_constratintcheckhead_clientid on constratintcheckhead(clientid) with fillfactor = 90
create index IX_constratintcheckhead_clientid_accountid on constratintcheckhead(clientid,accountid) with fillfactor = 90
drop table constratintcheckhead
drop table constraintcheckdetails
Description :
Lists/Drops Constartints and indexes for a column and drops the column Following constraints are checked only Default,Check, Foreign Key, Primary Key,Unique Key Indexes : Clustered or Non Clustered This will not check column is replicated or part of full text etc Only SQL 2005 and above WARNING: Once constaints are dropped , column will be dropped Assumes there are not more than 10 columns in an index

Usage : exec sp_lib_drop_column 'constratintcheckhead','accountid','l'
exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
Input Parameters : @tablename , @columnname ,@flag char(1) @flag -- 'l' -- list constraints only , 'd' -- drop column , 'b' -- list constraints and drop column
Output Parameters : None
Return Value : None
Record Set : None
 */

create procedure dbo.sp_lib_drop_column @tablename varchar(150), @columnname varchar(80) ,@flag char(1) = 'l'
as
set nocount on

if @flag not in ('l','d','b')
return
declare @slno int ,@maxslno int,@vchsql varchar(4000),@ci_name varchar(150),@ci_type char(1)
create table #temp_constraints( tablename varchar(150),type_desc varchar(150),ci_name varchar(150),columnname varchar(80),ci_type char(1), parentablename varchar(150),parentcolumname varchar(80),slno int identity(1,1) primary key)
declare @numbers table ( n int primary key )
insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4) insert
into @numbers values (5)
insert into @numbers values (6)
insert into @numbers values (7)
insert into @numbers values (8)
insert into @numbers values (9)
insert into @numbers values (10)

 insert into #temp_constraints (tablename,type_desc,ci_name,columnname,ci_type,parentablename,parentcolumname )select object_name(parent_object_id) as tablename,type_desc,name as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.default_constraints
where object_name(parent_object_id) = @tablename
and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name(parent_object_id) as tablename,type_desc,name as ci_name,col_name(parent_object_id,parent_column_id) as columnname, 'N' as ci_type ,'' as parentablename,'' as parentcolumname
from sys.check_constraints
where object_name(parent_object_id) = @tablename and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name( parent_object_id) as tablename, 'FOREIGN KEY' as type_desc, object_name(constraint_object_id) as ci_name,col_name(parent_object_id,parent_column_id) as columnname,'N' as ci_type,object_name( referenced_object_id) as parentablename, col_name(referenced_object_id,referenced_column_id) as parentcolumname
from sys.foreign_key_columns
where object_name( referenced_object_id) = @tablename
and col_name(referenced_object_id,referenced_column_id) = @columnname
union all
select object_name(i.object_id) as tablename, case when o.type = 'PK' then 'PRIMARY KEY' when o.type = 'UQ' then 'UNIQUE'else 'INDEX ' end as type_desc, i.name as ci_name,t.index_column_name as columnname, case when o.type in ('PK','UQ') then 'N' else 'I' end as ci_type,'' as parentablename, '' as parentcolumname
from sys.indexes i
left outer join sys.objects o
on o.parent_object_id = i.object_id
and i.name = o.name
cross apply
( select index_col(object_name(i.object_id),index_id,n) as index_column_name from @numbers where index_col(object_name(i.object_id),index_id,n) = @columnname ) t where object_name(i.object_id) = @tablename
----- if @flag in ('l','b')
select * from #temp_constraints order by slno
 if @flag = 'l'
return
set @slno = 0
select @maxslno = max(slno) from #temp_constraints
while @slno < @maxslno
begin 
    set @slno = @slno + 1
    select @tablename = tablename, @ci_name = ci_name, @ci_type = ci_type
    from   #temp_constraints where slno = @slno
   if @ci_type = 'N' set @vchsql = 'alter table ' + @tablename + ' drop constraint '   + @ci_name else if @ci_type = 'I' set @vchsql = 'drop index ' + @tablename + '.' + @ci_name
-- print @vchsql
 exec (@vchsql)
end
drop table #temp_constraints
return