SQL SERVER: ALTER column datatype

Though it appears simple, often we search google or  refer to msdn for simple SQL queries.

CREATE tableA(col1 Int,col2 Varchar(100),col3 Varchar(50))
GO

To alter the datatype for col3 in tableA, we need to do the below.

ALTER tableA
ALTER col3 Varbinary(MAX) NULL
GO

SQL SERVER: Problem with SELECT *

Its common practise to user SELECT * when we want to query data from a db table or underlying view or inserting data into another db table as below.

--Assumption: tableA , tableB exists
INSERT INTO tableB
SELECT * FROM tableA.
GO

The above statement works very well as long as both the db tables have the same structure.
But if the db structure for any of the above db changes, then the above query would fail.

Solution: Always use explicit column names in the SQL queries

INSERT INTO tableB(col1,col2,col3)
SELECT col1,col2,col3 FROM tableA
GO

Reference: Durga Palepu (http://blogwithdp.blogspot.com/)

Adding new column to SQL SERVER DB table

Here is the process to add new column to existing db table  in SQL SERVER.

Create sampleStudTable(Id int,Name Varchar(100))
GO

To add additional column to this db table;
ALTER TABLE sampleStudTable
ADD Discipline Varchar(100) NULL

Reference: http://blogwithdp.blogspot.com/

Happy Dussera

I wish you all Happy and Prosperous Dussera festival.

Regards,
DP

Community TechDays - Bangalore

Microsoft Community TechDays are back once again!!

This time the event happens in Bangalore and its absolutely free of cost!!

To get latest updates on Microsoft technologies, jump into the join the sessions.
You can attend the sessions in person or online from your home at your convinience.

For more details about the event and registration, please click link here.

SQL Server GO Keyword

Often we face problem when executing multiple SQL statements i.e. if any one among the SQL statements fail then all transactions may fail.
To overcome this problem in SQL Server, we can use GO keyword which we will seperate execution of one statement from the other SQL statement.

Syntax:
GO [Count]
Where Count Is a positive integer. The batch preceding GO executes the specified number of times.
Example:
Create Employee(Id int,Name Varchar(50))

INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp1')

INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp2)

Execution above statements fails as there is syntax error.

Using the GO keyword.
INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp1')
GO
INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp2)
GO
Now the 1st statement will be executed successfully.
To know more about the GO keyword click link here.

Reference: http://blogwithdp.blogspot.com/