Add Primary Key in SQL Server

One of the cirtical factor in database/table designing is to prevent duplicate records. There are several ways to do this. You can place Unique Index or Unique Constraint or add a primary key. Well there is only slight difference between these things.

I will go one by one to describe about these but for now lets concentrate on adding a primary key to a table.

There are two ways to add a primary key constraint:

1) While Creating the Table using CREATE TABLE command.
2) Adding Primary key after table creation using ALTER TABLE statement.

The simple thing is to create the table first and define the Primary key later. Again each one of us will have different opinion but to me this is simple step. Though you will end up in having multiple SQL statements but you will have more control on what you are doing.

The pre-requisite to define a Primary Key on a column is, that column should be NOT Nullable.
Check out Adding NOT NULL constraint article to meet the pre-requisite of Primary key.

To verify whether the column is NOT NULL or not, run the below query:

SELECT TABLE_NAME,COLUMN_NAME,IS_NULLABLE FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'DEPT'

If IS_NULLABLE is "NO" means column has Not Null Constraint.


Lets say I want to add Primary Key constraint on DEPTNO Column.

ALTER TABLE:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

We are trying to add a Primary Key Constraint on DEPT table and the column is DEPTNO.
The Name of the Constraint is PK_DEPT. Its very important to give a name to the constraint.
As the general standards Primary key constraint name should always start with PK_ and followed by the table Name Or you can have _PK.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO INT PRIMARY KEY, DNAME VARCHAR(30))

As you can see i didn't specify constraint name to Primary key. So Tell me is there any way to put a constraint name in the CREATE TABLE statement??

Add NOT NULL Constraint in SQL Server 2005

Today I will explain about the different ways to add a NOT NULL constraint on a column.

Advantages of NOT NULL constraint:
1) You can prevent users to enter null values into this column.
2) The pre-requisite to enforce Primary Key on a Column.

Now Lets get into the details:
There are two ways which i can think of
1) While creating the table using CREATE TABLE statement.
2) Modifiying the table definition using ALTER TABLE statement.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO INT NOT NULL, DNAME VARCHAR(30))

When you are creating a table as part the Column Definition you can specify the Constraint. This is very easy. But most of the cases we get into a position where we have a table in PROD or some where and want to define a Not Null constraint on the column. So the next section is about defining the constraint on existing table.

ALTER TABLE:

Created a table called Dept to store Department information.

CREATE TABLE DEPT(DEPTNO INT, DNAME VARCHAR(30));

ALTER TABLE DEPT ALTER COLUMN DEPTNO INT NOT NULL;

That's it. Now if you use any of following queries you can see the column is now not nullable.

SELECT * FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'DEPT'

or

sp_columns 'dept'

One question here, When you are adding NOT NULL constraint with ALTER TABLE you need to specify the column Data Type. Do you know why??? If you put different datatype then it will not accept!!!!

If you know the answer please post your comment.

XML Schema Definition Tool

XML Schema Definition Tool (Xsd.exe)

In many projects where Xml files are used, there can be a need to generate Xml schema
From an xml file and classes from schema file. We can use XML Schema Definition Tool (Xsd.exe) to generate Xml schema from XML file.

xsd Test.xml /outputdir:testOutputDir

where option "/outputdir:directory" specifies the directory for output files.

For Eg: xsd C:\Test.xml /outputdir:C:\Test which generates xsd schema file in C:\Test

To generate classes or dataset from an XSD schema file.

xsd file.xsd {/classes /dataset} [/element:element]
[/language:language] [/namespace:namespace]
[/outputdir:directory] [URI:uri]

Where
1) option "/classes" generates classes from an XSD schema file.
2) option "/dataset" generates dataset corresponding to XSD schema file.
3) option "/element" specifies the element in the schema for which code is generated.
4) option "/language" is used for specifying the language(C#, Visual Basic, Jscript, Visual J#)
5)option "/namespace" specifies the runtime namespace for the generated types. The default namespace is Schemas.
6) option "/outputdir:directory" specifies the directory for output files.
7) option "/URI:uri" specifies the URI for the elements in the schema to generate code for.

For Eg: xsd C:\Test\Test.xsd /classes /language:vb /outputdir:C:\Test which generates classes from an XSD schema file.

Identity in SQL Server - Alter Identity Column in SQL Server

Identity Column plays very major role in SQL Server.

Please check out some of my articles on Identity.

From my Own Blog:
-----------------
identity key columns in sql server
Identity in SQL Server
get your basics on identity columns

Full length and detailed articles from Jacob Sebastian:(SQL Server MVP)
------------------------------------------------------------
All you need to know about Identity in SQL Server

My MVP Summit 2009 Experience: DAY 3

Just like DAY 2, started with Non-Veg Break fast and headed to Microsoft to get more insight on technology.

Some more snaps:







Standing from Left to Right: Nikhil Kothari, Vijaya Kadiyala, Suprotim, Ravi (the one in Blue Jacket).

Once again i met the 3M(s) of C-SharpCorner.



With this last picture my Tour ends.



I am really looking FWD to be the part of MVP Summit again.

My MVP Summit 2009 Experience: DAY 2

DAY 2, Brand new day with lots of energy and lots of enthusiasm to visit Microsoft Campus and to attend various training sessions. Wow…I can’t believe it.
Had good non-vegetarian break fast. It may seem bit odd to you but I love Non-vegetarian food.

Boarded the bus to go to Microsoft Campus.

Here are some pictures, which I took when I was in bus: In Microsoft Campus





After getting down the bus I saw almost all country flags.



Some more pictures:









Unfortunately i can't say anything about what sessions i attended and what's in there!!!!