Vadivel's blog

Bill Gates turns 52 today :) Happy Birthday BillG.

I removed all of my VZ friends blog from my blogroll and have added onto my bloglines account.

Theory: :r -- parses additional T-SQL statements and sqlcmd commands from the file specified by into the statement cache. In this article we would see the usage of :r as well as handling spaces in SQLCMD. In SQL Mgmt Studio: Step 1: 01VariableInitialization.sql :setvar filepath "C:\Vadivel\SQL Related\Scripts\sqlcmd" :r $(filePath)\02TableCreation.sql Step 2: 02TableCreation.sql Create table tblTest ( Sno int identity, FName varchar(20) ) Go :r $(filePath)\03InsertScripts.sql Step 3: 03InsertScripts.sql Insert into tblTest (Fname) values ('alpha') Explanation of each file: 01VariableInitialization.sql -- In this file we create a scripting variable 'filePath' which will hold the path of the .sql files which we use for this demo. Then it executes 02TableCreation.sql. 02TableCreation.sql -- In this, we create tables of our choice. Also we make use of the scripting variable created in the previous file here to call another .sql file to insert records into th

This example demonstrates the way to create a variable and make use of it for multiple purpose. Actually in this example we would see how to create a variable and append strings into it. Lets create two Database with slight difference in the name. For example, DB1 and DB2. Then create a table in each DB and populate few records into it. ---Code snippet which needs to be run in Mgmt Studio starts here--- Use master go Create Database DB1 go Use DB1 go Create table t1 (a int) go insert into t1 values (1) insert into t1 values (2) insert into t1 values (3) go Create database DB2 go Use DB2 go Create table t2 (Num int) go insert into t2 values (4) insert into t2 values (5) insert into t2 values (6) go --Code snippet to be run in Mgmt studio ends here--- Now open command prompt and connect into the DB. Step 1: SQLCMD -U sa -P hot -S VADIVEL Step 2: Press Enter Now lets create a variable by name "dbname" and assign 'DB' as the string to it. Step 3: :setvar dbname DB Now l

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index. Create a table with non-clustered index in it: Create Table tblDemoTable_nonclustered ( [Sno] int primary key nonclustered, [Remarks] varchar(5000) not null ) Go Pump-in some data into the newly created table: Set nocount on Declare @intRecNum int Set @intRecNum = 1 While @intRecNum <= 15000 Begin Insert tblDemoTable_nonclustered (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109)) Set @intRecNum = @intRecNum + 1 End Check the fragmentation info before dropping the column: DBCC SHOWCONTIG ('dbo.tblDemoTable_nonclustered') GO Output: DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table... Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9 TABLE level scan performed. - Pages Scanned................................: 84 - Extent

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a clustered Index . Create a table with clustered index in it: Create Table tblDemoTable ( [Sno] int primary key clustered, [Remarks] char(5000) not null ) Go Pump-in some data into the newly created table: Set nocount on Declare @intRecNum int Set @intRecNum = 1 While @intRecNum <= 15000 Begin Insert tblDemoTable (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109)) Set @intRecNum = @intRecNum + 1 End If it's SQL 2000 or earlier: DBCC SHOWCONTIG ('dbo.tblDemoTable') -- Displays fragmentation information for the data and indexes of the specified table Go Output: DBCC SHOWCONTIG scanning 'tblDemoTable' table... Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9 TABLE level scan performed. - Pages Scanned................................: 80 - Extents Scanned.......