Vadivel's blog

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

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

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

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

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') ) 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 belo...