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

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

Web Parts are similar to that of an user controls. But what makes it different is "Customization". Yes webparts allow the users to customize the site by modifying (for ex: moving) controls around the page based on WebPartZones. "ASP.NET version 2.0 introduces new type of personalization called Page Personalization or Web Parts" Any ASP.NET server control can act as a Web Part but by creating a custom control derived from the WebPart class you gain access to advanced features. I tried out an very very simple web part sample and let me try and explain it here. I didn't even write a single line of code in code behind file for this sample. Mind you for more webparts we need to use code behind files :) Btw I used Visual Studio Beta for this testing purpose. Steps I followed 1. I expanded the "Tool box" and had a look at the "Webparts" section. As we have "Standard", "Html", "Webcontrols" etc., "Webparts" is ...

SQL Server has a less known datatype called “ TimeStamp ”. But I wasn't able to find any article about it on the web. So I thought I would try a sample myself and write about it. TimeStamp is actually used for record versioning in a table. When you Insert (or) Update a record in a table with a TimeStamp field, that field gets updated automatically. Lets create a sample table to understand the usage of TimeStamp field. Create table TimeStampExample ( RunningNumber int identity, LastName varchar(30), tStamp timestamp ) The above table “TimeStampExample” has been created with a TimeStamp field (tStamp). By the way its not mandatory to provide a field name for timestamp columns. The below table structure is perfectly valid only. Create table TimeStampExample ( RunningNumber int identity, LastName varchar(30), timestamp -- note we haven't mentioned a field name here ) When a record is inserted the value of tStamp gets automatically set by SQL Server. Lets see that in action. Insert...

One of the common problem the database developers face in their day to day life is record concurrency issues. Lets try to address that with the help of timestamp data type. Lets assume that Sarah and Ram are reading a same record. First Ram updates that record with some new data. Later if Sarah also updates the record (mind you she is viewing the old content only still) then it would overwrite the changes made by Ram. There are two ways of solving this issue they are: 1. Pessimistic Locking 2. Optimistic Locking Pessimistic Locking: First person who reads the record would put a lock on it so that nobody else can change it until he is done with it. Only when he releases the record the other user can make use of it. This method is not recommended because it might also takes hours or days together for the first person to release his lock due to various reasons. Optimistic Locking: The record would be locked only when a user wants to modify its content. SQL Server has a TSEqual (I presum...

As much as possible it is advisable to keep large text in a document on the file system and store a link to that within the database. Why do I say this? Because for storing large chunk of data we need to depend on the TEXT, NTEXT and IMAGE data types. So What? There are 2 disadvantages in it. They are: 1. These data types does not support commonly used string functions such as Len, Left, Right etc., 2. It occupies more / large space in the DB which internally means there might be a performance issues if you store such data in the database. Inspite of all these if you still want to use TEXT data type due to your business requirment then these functions might interest you! 1. PatIndex() 2. TextPtr() 3. ReadText() 4. TextValid() PatIndex() This function is useful with TEXT, CHAR and VARCHAR data types. This seeks for the first occurrence of a pattern within a string. If the pattern is found, it returns the character number where the first occurrence of the pattern begins. For better under...

sp_rename is the system stored procedure used to rename user created objects like “Stored procedure“, “Trigger“, “Table“ etc., This SP works pretty fine as long as you don't use it to rename a “Stored proc“, “Trigger“ or a “View“. Let me try and explain this with an example. Normally once a SP, Trigger or a View is created an entry is made into sysobjects as well as syscomments tables. For better understanding follow the following steps (examples uses Pubs database). Step 1: /* Description: Sample procedure to demonstrate sp_rename issue Author: M. Vadivel Date: August 12, 2004 */ Create Procedure FetchAuthorsDetails As Select * from Authors Step 2: [self explanatory] /* Description: Query to see the stored proc details in sysobjects Author: M. Vadivel Date: August 12, 2004 */ Select [name] from sysobjects where name like 'FetchAuthors%' Step 3: [self explanatory] /* Description: Query to see the stored proc details in syscomments Author: M. Vadivel Date: August 12, 2004 *...

• Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. • Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Truncate table can be rolled back if it happens within a Transaction . • Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete. • Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is. • In truncate table the counter used by an identity column for new rows is reset to the seed for the column. • If you want to retain the identity counter, use delete statement instead. • If you want to remove table definition and its data, use the drop tabl...

There are times when we would like to check whether the content in two tables are same or not. As of now there isn’t any built-in function in SQL Server to do the same (who knows they might come up with something in Yukon!). At present we need to manually compare the contents of tables to find out whether they are matching or not. Won’t it be nice to have a stored procedure which would do the job for us? Read on … Setting the environment First let us create a test table and populate it with some test data. Create table Student ( [name] varchar(50), [age] int ) Insert into Student ([name],age) Values ('Vadivel',27) Insert into Student ([name],age) Values ('Ash',30) Let us now create a copy of this table with a new name: Select * into StudentCopy from Student Now both the table ‘Student’ and ‘StudentCopy’ has the same structure and values. Solution!! The stored procedure helps in comparing two tables: Create Procedure usp_CompareTable ( @FirstTableName varchar(128), @Se...

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :...

I have also started blogging at http://vadivel.thinkingms.com . Check it out and do let me know your feedback!

In this post I have explained the way to use UDF (User Defined Functions) in constraints. For the purpose of discussion I have provided the structure of 2 (self explanatory) tables " MasterTable " and " ChildTable ". Sample data for MasterTable have also been provided below. --Table structure of MasterTable: Create Table MasterTable ( ItemID int Identity Primary Key, ItemName Varchar(50), Status bit ) --Sample data for the table MasterTable: Insert Into MasterTable (ItemName, Status) Values ('Rice',1) Insert Into MasterTable (ItemName, Status) Values ('Wheat',1) Insert Into MasterTable (ItemName, Status) Values ('Corn flakes',0) --Table structure of ChildTable: Create Table ChildTable ( ItemID int Foreign Key References MasterTable(ItemID), Quantity int ) Go /* Let us now create an user defined function to check whether the Item exist and its status is 1. According to this sample Status 1 means the record is enabled if it is 0 it is disabled....

Yesterday I created a GMail a/c (vmvadivel@gmail.com) for myself. Thought would inform you all about that. If you also have an active blogger a/c (www.blogger.com) you could see the invite for creating a Gmail account immediately after logging into the site (blogger.com). If at all you don't have an blogger account check the heading "Interested in an account" here . I guess that would help you to get a GMail id at some later date!! I was actually wondering if Google gives 1 GB of space to each user how big their servers needs to be?!??! Won't they run into storage problem within few months after they release it for public?? At that time I got to see the post from my good old friend Pandurang Nayak which had some interesting facts about GMail !!