Vadivel's blog

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