Friday, January 14, 2005

Reclaim Unused Table Space ...

If we ever remove a variable length column (varchar) or TEXT column from one of our table, a certain amount of wasted space will be there in that table's physical representation. We can reclaim this space with the DBCC CLEANTABLE statement. Let us see how to use DBCC CLEANTABLE with a sample code snippet

Sample table structure

CREATE TABLE TableWithText
(
[FirstName] [char] (50),
[Age] [int] NULL,
[Resume] [text]
)
GO

Insert sample data into the table.

Insert into TableWithText Values ('Vadivel',28,'AAAAAAAAAAAA')
Insert into TableWithText Values ('Sailakshmi',27,'BBBBBBBBBBB')
Insert into TableWithText Values ('Vadi',58,'CCCCCCCCCCCCCCC')

To check the size of the table

sp_MStablespace TableWithText

Now let us drop the column ("Resume") with Text datatype

Alter table TableWithText drop column Resume

Try to check the size of the table again

sp_MStablespace TableWithText

There wouldn't be any change in the size of the table. Actually as we have removed a column of TEXT datatype we need to expect the size of the table to reduce down considerably. For that execute the below sql statement

DBCC CLEANTABLE ('pubs','TableWithText') -- parameters are database name and the table name

Drop the fixed length ("FirstName") column now

Alter table TableWithText Drop Column FirstName

Now if you check the size of the table there won't be any change at all. Thats because we reclaim the space only if we remove TEXT or variable length fields from our table.

DBCC Cleantable has an optional third parameter where we can specify the batch size. i.e., the number of rows which would be processed per transaction. If not specified, the statement processes the entire table in one transaction this might sometime throw timeout error.

DBCC CLEANTABLE ('pubs','TableWithText', 2)

Related Links:

1. Reclaiming a table space after dropping a column [With Clustered Index] - http://vadivel.blogspot.com/2007/10/reclaiming-table-space-after-dropping.html

2. Reclaiming the table space after dropping a column [Without Clustered Index] - http://vadivel.blogspot.com/2007/10/reclaiming-table-space-after-dropping_01.html

Thursday, January 13, 2005

Try/Catch block in Sql Server 2005

In this article let us look into a very basic example for using TRY..CATCH block in Yukon (code name of Sql Server 2005).

Things to know before proceeding futher are listed below. FYI, the definitions are extracted from the MSDN for the benift of those who doesn't have it:

Set xact_abort

1. When Set xact_abort is on, if a TSQL statement raises a run-time error, the entire transaction is terminated and rolled back.

2. When OFF, only the TSQL statement that raised the error is rolled back and the transaction continues processing.

3. Compile errors, such as syntax errors, are not affected by Set xact_abort.

IGNORE_DUP_KEY

Specifies the error response to duplicate key values in a multiple-row INSERT transaction on a unique clustered or unique nonclustered index. When on and a row violates the unique index, a warning message is issued and only the rows violating the UNIQUE index fail. When OFF and a row violates the unique index, an error message is issued and the entire INSERT transaction is rolled back. When processing an UPDATE statement, IGNORE_DUP_KEY has no effect. The default is OFF.

Errors in TSQL code can be processed using a try...CATCH construct similar to the exception handling features of the C++ and C# languages. A try...CATCH construct consists of two parts ; a try block and a CATCH block. When an error condition is detected in the TSQL statements contained in a try block, control is passed to a CATCH block where it can be processed.

A try block starts with the begin try statement and ends with the end try statement. one ormore TSQL statements can be specified between the begin try and end try statements.

A try block must be followed immediately by a CATCH block. A CATCH block starts with the begin CATCH statement and ends with the end CATCH statement. In transactionsact-SQL, each try block is associated with only one CATCH block.

When an error occurs within a try block, control is transferred to the associated CATCH block. The CATCH block handles the exception, and the program control is then transferred to the first TSQL statement that follows the end CATCH statement. If the end CATCH statement is the last statement in a stored procedure, trigger, or user-defined function, control is returned to the code that invoked the procedure, trigger, or function.

Sample table structure

Create table TestErrorHandling
(
EmployeeNumber int,
FirstName varchar(50),
Salary money NULL,
constraint [pkEmpNum] primary key clustered (EmployeeNumber)
with (ignore_dup_key = off)
) on [primary]
Go

Source code of the Stored procedure with TRY..CATCH block

Create proc NewEmployee
@EmpNum [int],
@FirstName [varchar](50),
@Salary [money]
as
Set xact_abort on

Begin try
begin transaction
insert into TestErrorHandling (EmployeeNumber,FirstName,Salary)
values (@EmpNum, @FirstName, @Salary)
commit transaction
End try
Begin Catch
Rollback transaction
Print 'Error :: Emp No provided already exists. Try with another number.'
End Catch
Go


Testing the stored proc
Exec NewEmployee 11,'Vadivel',100

Try running the above execute statement twice and you could see the error message written in the CATCH block being fired.

Monday, January 10, 2005

Enhancements to Top Keyword in Yukon

There are loads of new features in Sql Server 2005 code named Yukon. Out of which in this article let me explain briefly the enhancements made to TOP keyword in this version of the product.

Let us first create a sample table and populate some dummy records into it.

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

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

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

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

The below code snippet would work in both SQL Server 2000 and SQL Server 2005

/*
TSQL stops proceessing when they have been affected by the specified no of rows
*/

Set RowCount 2

-- First two records salesquarter would be set as 999
Begin Tran
Update TestTopOptionYukon SET SalesQuarter = 999
If @@RowCount = 0
Begin
Print 'No Record found'
Set RowCount 0
End
Commit Tran

To list all the records again we need to set the RowCount as 0.

Set RowCount 0
Select * from TestTopOptionYukon

The below code snippet would work only in SQL Server 2005.

As you could see Set Rowcount statement is not used at all. Instead we have made use of TOP keyword.

Begin Tran
Update TOP (2) TestTopOptionYukon SET SalesQuarter = 999
If @@RowCount = 0
Begin
Print 'No Record found'
Set RowCount 0
End
Commit Tran

Passing parameters to the TOP keyword

One of the feature which was missing in Sql Server 2000 was passing parameters to the TOP keyword. That is now possible in Yukon.

Declare @topValue Int
Set @topValue = 2
Select Top (@topValue) * from TestTopOptionYukon

Even if you give decimal Values it takes only the integer part and ignores the decimal part. The below query would list the Top 2 records.

Declare @topValue Int
Set @topValue = 2.9
Select Top (@topValue) * from TestTopOptionYukon

Listing Top n percentage of records from a table

Declare @topValue Int
Set @topValue = 25
Select TOP (@topValue) Percent * from TestTopOptionYukon

Out of all the coolest enhancement is we could now pass a query as a parameter to TOP keyword.

The below code snippet helps us in finding out the top half of the exisitng records.

--It would list 50% of the records i.e, in our case top 6 records
Select TOP (Select cast((Select Count(*)/2 from TestTopOptionYukon) as int)) * from TestTopOptionYukon

Monday, January 03, 2005

Encrypt all Stored Proceedure ...

There are situations where we would have written lots of stored procedure for a project. If at all we want to encrypt all the SPs at one shot, as of now we don't have any build-in function or tool to do that. The below SP does that with the help of "Cursor".

This SP is intelligent (!!) enough to encrypt all Stored procs within the current database except itself.

There is no "easy" way to decrypt the encrypted procedures. So it is always advisable to generate scripts of ALL SPs before executing the below piece of code. Also this code snippet would only work for those SPs whose content are within 8000 characters.

Create procedure uspEncryptAllSP
As
Declare @procName varchar(255),
@procContent varchar(8000),
@sqlQuery varchar(8000)

Set nocount on

Declare curEncrypt cursor for
Select
so.name,
sc.text
From
Sysobjects so,
Syscomments sc
Where
sc.id=so.id and
so.type ='p' and
so.category=0 and
so.name <> 'uspEncryptAllSP' and
sc.encrypted=0

Open curEncrypt
Fetch next from curEncrypt into @procName, @procContent

While @@Fetch_Status = 0
Begin

Print 'Encrypting the Stored Procedure: ' + @procName
Execute ('drop procedure ' + @procName)

Set @sqlQuery = Replace(@procContent, 'Create procedure ' + @procName, 'Create procedure ' + @procName + ' With Encryption')
Execute (@sqlQuery)

Fetch next from curEncrypt into @procName, @procContent

End
Close curEncrypt
Deallocate curEncrypt

Print 'Mission Completed !!!'
Go

We can still improve this code by putting the main logic within transaction. So that if at due to some reasons the procedure is deleted but could not be recreated with encryption we could roll back.

XML Data type in Yukon

In Sql Server 2005, XML is a native data type with the help of it we can store XML documents itself within a SQL database. In this article let us see in details about this XML data type.

For the purpose of our understanding let us create a sample table using the code snippet provided below.

Create table DemoXMLDataType
(
EmpNumber int identity,
EmployeeDetails xml not null,
constraint Chk_EmployeeDetails check (EmployeeDetails.value('(/Employee/@DOJ)[1]', 'datetime') <= Getdate())
)
Go

As you could see EmployeeDetails is a field where XML document as such would be saved. We have added a Check constraint to check one of the attribute of the XML document and allow only those records whose DOJ (date of joining) is less than or equal to current date.

Here, [1] == means first attribute.

-- The below two Insert statements would execute successfully without any issues

Insert into DemoXMLDataType Select 'Vadivel'
Go

Insert into DemoXMLDataType Select 'Sai'
Go

-- The below statement would fail as the DOJ is greater than current date

Insert into DemoXMLDataType Select 'Vel'
Go

The above insert statement would throw an error like the one below::

Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with CHECK constraint 'Chk_EmployeeDetails'. The conflict occurred in database 'master', table 'DemoXMLDataType', column 'EmployeeDetails'. The statement has been terminated.

If we run a query to display all records in the table the EmployeeDetails field would be a hyperlink column. On clicking that hyperlink it would open the corresponding XML document in a seperate XML editor.

Select * from DemoXMLDataType
Go

[Extract from MSDN] The xml data type provides the following methods:

query() : Use this method to query over an XML instance.
value() : Use this method to retrieve a value of SQL type from an XML instance.
exist() : Use this method to determine if a query returns a nonempty result.
modify() : Use this method to specify XML DML statements to perform updates.
nodes() : Use this method to shred XML into multiple rows to propagate parts of XML documents into rowsets.

To display the Employee Number and the date of Joining use the below code snippet

Select EmpNumber, EmployeeDetails.value('(/Employee/@DOJ)[1]', 'datetime') as 'Date of Birth' from DemoXMLDataType
Go

Using the contains() XQuery function to search for a specific character string

Select * from DemoXMLDataType where EmployeeDetails.exist('//FirstName[contains(., "Vadivel")]') = 1
Go

Clean up resources
Drop table DemoXMLDataType