How to Add Identity to Existing Column

This article is in continuous with the previous article on "Adding Identity to an existing table in SQL Server".

Back ground is I want to add IDENTITY column to the which is already exisitng with valid data.

Step 1: Add Column to the Table
ALTER TABLE DOMAIN ADD DOMAIN_ID_NEW INT IDENTITY (1, 1)

Step 2: Verification of the IDs
SELECT * FROM DOMAIN where DOMAIN_ID_NEW <> DOMAIN_ID

Step 3:
If the above query return 0 records then it means, there is no difference between the IDs which came from Old table and the IDs which are generated from Identity Column.

Step 4: Drop column in the table
ALTER TABLE DOMAIN DROP COLUMN DOMAIN_ID

Step 5: Rename the Column
sp_RENAME 'DOMAIN.DOMAIN_ID_NEW', 'DOMAIN_ID','COLUMN'

Step 6: Over


Related Articles:
Rename Table in SQL Server
Create Table with Identity in SQL Server
Identity in SQL Server - Alter Identity Column in SQL Server

60 Seconds with Suprotim Agarwal

60 seconds with Suprotim Agarwal:

Every accomplishment starts with the Decision and the Will to try

VJ caught up with Suprotim Agarwal, MVP in ASP/ASP.NET Architecture, to get his view on the Microsoft Most Valuable Professional Program(MVP).

VJ: Please tell us about your self.
Suprotim: I am an ASP.NET Architecture MVP and work as an Independent Architect Consultant providing consultancy on how to design and develop .NET centric database solutions. I hold a degree in Computer Management and have been working with .NET technologies ever since its 1.0 beta release, way back in 2000.

I am the founder and primary contributor to http://www.dotnetcurry.com/, http://www.devcurry.com/ and http://www.sqlservercurry.com/. I am also a Top Answerer in the ASP.NET forums.

When I am not exploring a technology, I spend my time watching movies, playing strategic video games and checking out the latest gadgets.

VJ: What motivated you to become an MVP
Suprotim: I have been very passionate about technology. Being the inquisitive type I am, I used to often try out new things in technology and thereby face challenges. Technical communities like MSDN and ASP.NET forums are where I used to find most of the solutions to the challenges I faced. Amongst the answerers, I used to observe people having MVP title’s providing excellent solutions. I felt grateful and obliged for the time these people took out to solve problems of users. This motivated me more than anything else to become one myself.

VJ: Please share with us your journey of becoming an MVP.
Suprotim: In the year 2006, I started giving shape to my motivation and made it a goal. I started actively participating in the MSDN communities and ASP.NET Communities. A year later, I was the Top Answerer at the forums and was made Moderator of MSDN Forums, which motivated me, further. I also started realizing that a lot of people had similar issues and they often felt an urge to use technology to its fullest. However most of them could not find time to do it. Thus http://www.dotnetcurry.com/ was born where I dedicated my time to write articles, tips and tricks about .NET and related technologies. Since I was also inclined towards SQL Server database, I started http://www.sqlservercurry.com/. Microsoft recognized my contributions and me an MVP. I feel proud that I am a Microsoft MVP and hope to continue being one till I retire J

“Every accomplishment starts with the Decision and the Will to try.”


VJ: Tell us about your MVP Summit experience
Suprotim: Rocking! I got to meet technical honchos, each brilliant in their own fields. I also met enthusiasts like you who had the motivation of contributing to the communities in their own way. There were technical sessions, interactions with the product team, feedbacks, group discussions, keynotes from executives and much more, including a Rocking Party! We MVP’s were pampered with good food, comfortable accommodation and free gifts. It was overall a brilliant experience that words alone cannot describe. You got to be an MVP to experience one!

VJ: Any message to technologists
Suprotim: There's a very simple principle that I follow in life and I will take this opportunity to share it with all the readers of dotnetvj.com.

If you dream about doing a lot of things, but haven't been able to achieve much, then you are just a dreamer. Add a date and time to your dreams and that becomes a Goal. And once a Goal is set, do ‘everything’ possible to reach it. During the process, if you start feeling that your goals cannot be reached, do not adjust your goals, adjust the steps taken to reach the Goal. Remember motivation is what gets you started but determination is what keeps you going. So Never, Never, Never give up!

Validate Email Address using Regular Expression Control in ASP.NET

In this article i would like to show you a simple code to validate User input to accept Email Address in Text Box control.

The traditional approach is to OnLostFocus event of Text Box we call a Java Script to read all the characters on by one to validate the entered the input valid email address or not. That is looking for @ symbol and using some blind rules.

Its really difficult to validate email address because now a days we see somany domains providing unique way to create email address. This is time consuming process to validate all such cases. Instead if you are familiar with Regular Expression the it becomes your life easier.So here i am going to give you simple example to validate email address in text box.

Lets look at the below code:

I have one Text Box with the ID txtEmailAddr and one RegularExpressionValidator where ControlToValidate is txtEmailAddr and ValidationExpression is "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" and If the format of the email doesn't match with regular expression then throw "Enter Valid Email Address" error message.

Very simple isn't it :)....

Similar Articles:

How to validate Numbers in Text Box

Validate Numbers Using Regular Expression in ASP.NET

In this article i would like to show you a simple code to validate User input to accept only Numbers in Text Box control.

Most traditional approach is to OnLostFocus event of Text Box we call a Java Script to read all the characters on by one to validate the entered the input is Number or not. This is time consuming process. Instead if you are familiar with Regular Expression the it becomes your life easier.

So here i am going to give you simple example to validate numbers in text box. Lets take simple case where in i want to validate Salary.

What you need to do is add a RegularExpressionValidator by setting Control to validate to the text box which we want to validate and ValidationExpression is ^[0-9] and ErrorMessage is "Enter Valid Salary".

Here "^" this symbol is called "Not" So the experession anything which is not in the range of 0-9 display the error message.

So No more For Loop or While loop to do this simple task.....

Similar Articles:

How to Validate Validate Email Address?

Adding Identity to an existing table in SQL Server

In this article, I would like to explain about how to add IDENTITY column to the existing table.

Let’s create a table and insert below records:

CREATE TABLE DOMAIN (DOMAIN_ID INT,DOMAIN_NAME VARCHAR(30),DOMAIN_DESC VARCHAR(100))

Insert below data:

Now the task is to add IDENTITY property to the DOMAIN_ID column.

Do you have any ideas or suggestions to which can guide me to complete this task !!!

Please check out How to Add Identity to Existing Column article for the solution.

Similar Articles:

How to Add Identity to Existing Column

Create Table with Identity in SQL Server

In this article I will show you how to create a table with Identity Column.

Let’s create and examine the STUDENT table.

CREATE TABLE STUDENT
( STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
SCHOOL_NAME VARCHAR(30)
)

If you look at the CREATE TABLE statement, just besides to the Datatype column i specified IDENTITY word with 1,1.

The general syntax of identity is
IDENTITY(Start_Num,Increment_Val).

When we insert the data, It starts with value 1 and increment it by 1 for every insert.

Its very easy. isn't it :)

Use the below SQL to Find all the IDENTITY columns with in a database:

Select table_name , column_name
From information_schema.columns
where
columnproperty (object_id (table_name), column_name, 'IsIdentity') = 1

In my next article I will get into more details….until then keep watching..DotNetVJ Chanel….

Oracle Sequence Vs IDENTITY in SQL Server

I am getting so many mails on what is the equivalent of Oracle Sequence in SQL Server?

There is nothing like equivalent of Oracle Sequence in SQL Server. At the same time IDENTITY can not be compared with Sequence in Oracle. All though there is a feature called IDENTITY to produce unique numbers.

Both are very unique and useful features in each of the databases.

Oracle Sequence Advantages:
1) Standalone object any one can use it for any column.
2) This is not tightly coupled with any column so one can easily drop this object.
3) One can easily insert the data into Sequence related column.
4) You can use the same sequence to populate multiple columns in the same table.
5) You need to explicitly use Sequence Name during inserts.


SQL Server IDENTITY Advantages:
1) IDENTITY is a property which is tightly coupled with column in a table.
2) This is tightly coupled with column so you can’t drop this property. The only way to drop IDENTITY property is to drop the column itself.
3) To insert the data into IDENTITY column you need to set a flag.
4) You can have only one column per table with the IDENTITY property.
5) You need to skip the IDENTITY column during inserts.

Keep watching for more information on IDENTITY in SQL Server.

REVERSE String in SQL Server

Today i would like to talk about very simple tip in SQL Server. If i ask you to write a logic to REVERSE a string then you probably will use While or For loop to do this.

Instead of writing complex logic, you can make use of REVERSE string function.

Its very simple to use. Just pass the string and it will reverses the string.

SELECT REVERSE('VIJAYA KADIYALA')

Output is:
--------------------
ALAYIDAK AYAJIV

If you want to perform the same task in Visual Basic (My favourite Language) then you need to wait for next day.

Compare Rows in SQL Server

In this post, i would like to give a simple tip on comparing rows in SQL Server.

Let's create "COMPARE" table and insert the following data.

Create Table Compare(name varchar(10),reading int,sq_no int identity(1,1))

insert into Compare (name , reading) select 'UNDRI',10
insert into Compare (name , reading) select 'UNDRI',11
insert into Compare (name , reading) select 'UNDRI',20
insert into Compare (name , reading) select 'UNDRI',20
insert into Compare (name , reading) select 'UNDRI',20
insert into Compare (name , reading) select 'UNDRI',22
insert into Compare (name , reading) select 'UNDRI',45
insert into Compare (name , reading) select 'UNDRI',20

This table has 3 column Name, reading and sq_no. Now the task is, i need to compare first row column value in reading with second row column value in reading, and Second row column value in reading with Third row column value in reading so on..

The simple rechnique with out modifying the table structure is:
Create a view with an additional column which has the unique row number to compare with.

CREATE VIEW v_compare as
SELECT o.*,(SELECT MAX(sq_no) FROM compare i WHERE I.SQ_NO <>

Now the next step is using the below query to find out the difference between the rows.

SELECT o.name,o.reading,(select o.reading-i.reading from compare i where i.sq_no = o.pre_sq_no) AS RESULT
FROM V_compare o

Disable Constraint in SQL Server

In my previous articles we did talk more about different constraints in SQL Server. But the topic or the point which I didn’t cover was “How to Disable Constraints”.

The only reason which I can think of interims of disabling constraint is during heavy data loads. By disabling constraint certainly improves the performance but taking a BIG risk at the data quality. I don’t recommend disabling the constraints, especially in OLTP systems.

As you know there are 5 constraints in SQL Server they are:
1) Primary Key
2) Foreign Key
3) Unique Key
4) Check
5) DEFAULT

We can’t disable Primary Key, Unique Key and DEFAULT constraints. So the only constraints which can be disabled are Foreign Key and CHECK Constraint.

The basic syntax is:

ALTER TABLE TABLE_NAME { NOCHECK CHECK } CONSTRAINT CONSTRAINT_NAME;

NOCHECK is used to disable and CHECK is used to enable the constraint.

Disable Foreign Key Constraint:
ALTER TABLE EMPLOYEE NOCHECK CONSTRAINT FK_EMPLOYEE_01

Enable Foreign Key Constraint:
ALTER TABLE EMPLOYEE CHECK CONSTRAINT FK_EMPLOYEE_01

Script to find Foreign Key Constraints State:
SELECT (CASE
WHEN IS_DISABLED = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
NAME AS CONSTRAINT_NAME,
OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME
FROM SYS.foreign_keys

Disable Check Constraint:
ALTER TABLE ACCOUNT NOCHECK CONSTRAINT cc_MinBal

Enable Check Constraint:
ALTER TABLE ACCOUNT CHECK CONSTRAINT cc_MinBal

Script to find Check Constraints State:
SELECT (CASE
WHEN IS_DISABLED = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
NAME AS CONSTRAINT_NAME,
OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME
FROM SYS.check_constraints

SQL Server Interview Question Answers -- Part 4

5) What is RULE?
A rule specifies the acceptable values that can be inserted into a column. This is similar to CHECK constraint.

11) What is the table name, that contains RULE Constraint definition?
Syscomments is the table which contains the RULE definition.
Use the below query to get the information.

select name as Rule_Name,
sc.TEXT as Rule_Definitionfrom sysobjects so inner join syscomments sc
on so.id = sc.id
where so.xtype='R'

15) Can you drop a Parent Table with out affecting its child tables?
No. First you need to drop all the Foreign Key relationships and then only you can drop Parent Table.

16) How to disable and Enable the constraints?
You need to use ALTER TABLE statement to disable constraint.
ex: ALTER TABLE ACCOUNT NOCHECK CONSTRAINT CHECK_IN_AMOUNT;
I will explain in-detail about disabling constraints in my next section.

17) What is the order of Constraints execution?
There is no predified order. All the constraints on the column gets executed.

18) what is difference between Primary Key, Unique Key and Alternative Key?
Primary Key ==> Is used to uniquely identify records and doesn't allow NULL values.
Unique Key ==> Is also used to uniquely identify records but does alow NULL Values per column combination.
Alternate Key ==> Is another type of Unique key, which is used to identify each record uniquely. This is not a constraint. This is just a terminology.


1) What is a Primary Key?
2) What is Unique Key?
3) What is the difference between Primary Key and Unique Key?
4) What is Check Constraint?
5) What is RULE?
6) What is the difference between RULE and Check Constraint?
7) How to create a relationship between two tables?
8) Can we create a Foreign Key with out Primary Key?
9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
10) What is the table name, that contains Check Constraint Definition?
11) What is the table name, that contains RULE Constraint definition?
12) Can we have RULE and Check Constraint on the same column?
13) Can we apply Integrity Constraints on Computed Columns?
14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
15) Can you drop a Parent Table with out affecting its child tables?
16) How to disable and Enable the constraints?
17) What is the order of Constraints execution?
-- Questions from Readers of the DotNetVJ
18) what is difference between Primary Key, Unique Key and Alternative Key?

60 Seconds with Pinal Dave

60 seconds with Pinal Dave:
MVP award played an important role in helping me chase my passion to make a difference.





VJ caught up with Pinal Dave, MVP in SQL Server, to get his view on the Microsoft Most Valuable Professional Program(MVP).


VJ: Please tell us something about yourself.
Pinal Dave: Well, to begin with, I am a Microsoft Most Valuable Professional (MVP) with over 6 years of experience as a Principal Database Architect, Developer, Administrator, and Project Manager. I specialize in SQL Server Programming. I am also the founder and a prominent author of SQLAuthority.com. Besides, my passion for writing and sharing my knowledge led me to become a writer and a blogger. I have authored over 900 SQL Server articles at SQLAuthority.com, and I am a regular columnist for several prominent IT websites and magazines.

VJ: What motivated you to become an MVP.
Pinal Dave: I have had ardor to make a difference in technology domain since I started my career. I needed a platform from where I can reach out to more and more people and make significant contribution to the technology realm. MVP award played an important role in helping me chase my passion to make a difference. MVPs are easily accepted in community and they are able to help in a better way owing to their MVP title. This was one of the reasons why I wanted to become an MVP. Today, as an MVP, I am actively involved in community service to teach, learn and transfuse technology to niche areas.

VJ: Please share with us your journey of becoming an MVP.
Pinal Dave: I have been writing for my blog http://blog.sqlauthority.com/ since November 2007. The whole blogging thing initially started as personal bookmarks. Actually, I was learning loads of new things about database and SQL Server. I needed to create some kind of journal or diary to store that valuable information. I finally decided to write what I learnt on my blog and that is how the journey started. Since then, I kept on writing down all my learning and observations on my blog. I have been an active member of Las Vegas SQL Server User Group and later on I became the President of Gandhinagar SQL Server User Group and the Leader of Ahmedabad SQL Server User Group. I have delivered numerous technical sessions in tech and User Group meetings. And it was during the course of my journey that Microsoft recognized my community service and awarded me with MVP.

VJ: Tell us about your MVP Summit experience
Pinal Dave: MVP Summit 2009 was one great event! There are several things about the event worth talking about but I think the best part was the opportunity to do networking with the smartest people on earth. Meeting product group and having one-to-one interaction with them is a rich experience. Imagine that you have been working for years with one technology and there are so many concerns and questions that need to be discussed with people who are actually making the technology. The summit was a great platform to meet SQL Server Product Team and intermingle with them. Additionally, I got the chance to meet some of the renowned MVP Fellows from across the world. It is a good coincidence that I had talked about the same subject in MVP Monthly Hour in March 2009. The topics that I covered there are
· Truly Global Experience – Grand Organization
· Meeting Product Group – Learn Inside Story
· Technical Sessions from Microsoft Experts
· Meeting Heroes of your Expertise
· Networking with Fellow MVPs and MVP Lead
· Career Advancement
· Sightseeing in Seattle, USA
· Microsoft Campus, Redmond
· I'm a PC – All MVPs are PC
· Steve Ballmer – an Energizer Bunny

VJ: Any message to the Technologists
Pinal Dave: For me, MVP is a milestone that comes along in the journey to help community. A true MVP works for sheer passion for technology. There is no real plan of action that one needs to follow to become an MVP. In fact, there are numerous ways through which one can become an MVP. But all of this leads to just one purpose - community service, as said earlier. Just keep up with the good work and soon you will be there.

I would like to share the following image that was taken on the very first day outside the Seattle Convention Center with Vijay (my dear friend), one of the top experts of .NET.

Best Practices on Answering Questions

Introduction

Just like any one, when we see any question in forums, we want to answer them. If we have the answer in our mind we write few lines about this over there. If we do not have the straight answer then we go to the Google and look for the answer and post the link in the answer section of question.

There are so many Answers already

If some one else already answers the question, but still you want to answer the question then you need to read all the answers before you post your own answer. This is very important. This way you will learn more about subject by reading other answers. If your answer matches to the existing then please do not duplicate the answer. When you are answering the question please do not refer to any external link unless the question is very generic.
Let’s go with example. Question “How to implement Replication in SQL Server”. For this kind of questions you can’t answer in few lines, you need to refer to the links. The question, which can be answered in few lines please put your own words to answer the question. Explain your answer with examples to understand in better way. Explaining the answers with real-world examples always improves your knowledge and others knowledge.

Think beyond

If you are just answering the question, then it is good. But this is not enough. You need to think beyond. Let’s go with example. Question is "How to Delete Duplicate Records in a Table". If you search for this in Google you will get different ways to delete the duplicate records. You post the link or your own query in the answer section. Do not think your answer is completed. Your answer is not yet completed.

This is where you need to think beyond. Does my solution/answer handle all the different cases? Let’s take some of the cases in deleting the duplicate records.

1) How many records you have in this table?
2) Who is data provider to this table?
3) Is this table available on 24x7?
4) What is % of the duplicate records?
5) Is this partitioned tabled?
6) Table has any Primary Key or Foreign keys with any other table?

Obviously you can't answer these questions on your own. The one who raised the question can only answer these. This way you are not only thinking beyond but also helping all the other people who are reading the question. This way the quality of your post is increasing.

Learn with your answer

If you are simply posting the same information by copying it from other sites then it is not great. Your main aim should be, learn with your answer. If you keep on doing this then at some point of time you don’t have to refer any other sites or links because you already have good knowledge on the subject.


Can I copy other articles into the answers section?

No. This is very easy for you to copy but this will create problems for the people who are maintaining the website. The Admin of the website gets into Copyright violation issues. Please do not copy the articles from other sites. Try to read and understand the article and paste the summary of that article in your own words and refer to the link.

What will I get by answering?

The biggest advantage is, this will improve your knowledge. If you are marking your presence in your project team it is not great. You need to mark your presence in the world. One day your pocket will be filled up with awards and rewards.

I don’t have time to answer the questions?

You don’t have to answer all the questions, but if you answer one “Unanswered question” or “Answer with good and real world example” will certainly improve your knowledge. Try to answer 1 question with real good example then one day you will be one of the top members in the forum. Do not treat this as burden. Just spend 10 minutes to learn something new or share your knowledge.

Best Practices On Posting Questions in Forums

Introduction
Just like any one, when we run into problems in .Net or SQL Server, we need help from other people. We look for the solution in Google. If we couldn’t find then we will get into good forums, where you have so many great people who can answer your question in very short span. There are some instances where great people can’t answer some of these questions with the information that is provided in Question and Description.

Why no one is responding to my question?
There could be number of reasons. The two most obvious reasons could be either the question or description are not very clear from a complete requirement point of view or the people in forum didn’t come across that situation or do not have expertise to answer the question or solve the problem. Obvious we can’t expect from any one to have hands on every technology that is there in the software field.

The Wrong way to post your question
Normally, a person will start posting like “Urgent” in the title and posts something like “How to validate Textbox data in ASP.NET. Please send me code. Its urgent”. When you hit submit, it will ask you to enter other mandatory fields in page.
With out spending a little to see where your question falls under, you just simply select any category to post your question. But your post goes into minutes, hours and even days with no response.

Another example in SQL Server section is, title contains SQL Query and the Description contains explain below query:
SELECT * FROM EMP WHERE EMP_ID = [:in_emp_id]

As you know each line of the code has meaning associated with its previous or next line. If you just post where you have the doubt then, we can’t answer your question.

The Correct way to post your questionLet’s take some time to put it in right way so that you will get good responses to your question. If we take examples in previous section then:
My Subject line is going to be “Text Box Validation in ASP.NET” and description or the problem statement would be “Hi Friends, I have one text box in my ASP.NET web application form, where I am expecting users to enter their telephone number. I would like to put to validation on this control. The validation that I want to place on this textbox is to accept only numbers from 0-9. The validation should be on client side using java script. So please give me the code/idea to achieve this”

When we are posting our question in the forum we should respect the person who is reading your question.


What am I supposed to do after getting the answer?
Once you are satisfied with the answer, please rate the answer that was able to give you the right solution or direction and please put a word “thank you”.

“Thank you” word really means a lot to the people who are trying to help you in solving the problem.

Even if you are able to find a solution on your own, please come back to the forum and say few lines about how you resolved the issue? This way you are going to help others as well. Please use the same thread.

What if I do not get the answer?
If you are not satisfied with the answer try to use the same thread to post your comments. If nobody replied to your question then repost the same question (but not on the same day) with more detailed information. This is the best way to solve any problem. If you open new thread you may get same answers once again.

SQL Server Interview Question Answers -- Part 3

10) What is the table name, that contains Check Constraint Definition?
INFORMATION_SCHEMA.CHECK_CONSTRAINTS, where CHECK_CLAUSE column stores the Definition of Check Constraint.

12) Can we have RULE and Check Constraint on the same column?
YES.

13) Can we apply Integrity Constraints on Computed Columns?
YES.

14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
SELECT
TC.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME,
TC.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME, KCU.COLUMN_NAME, ORDINAL_POSITION AS COLUMN_POSITION,
RC.UNIQUE_CONSTRAINT_NAME AS PARENT_PRIMARY_KEY_NAME, TC_PK.TABLE_NAME AS PARENT_TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC_PK
WHERE
TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND RC.UNIQUE_CONSTRAINT_NAME = TC_PK.CONSTRAINT_NAME

Please let me know, if you have simple definitions!!!!
Lets try to prepare list of possible interview questions on "Constraints".
I will answer all of these in next section.
1) What is a Primary Key?
2) What is Unique Key?
3) What is the difference between Primary Key and Unique Key?
4) What is Check Constraint?
5) What is RULE?
6) What is the difference between RULE and Check Constraint?
7) How to create a relationship between two tables?
8) Can we create a Foreign Key with out Primary Key?
9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
10) What is the table name, that contains Check Constraint Definition?
11) What is the table name, that contains RULE Constraint definition?
12) Can we have RULE and Check Constraint on the same column?
13) Can we apply Integrity Constraints on Computed Columns?
14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
15) Can you drop a Parent Table with out affecting its child tables?
16) How to disable and Enable the constraints?
17) What is the order of Constraints execution?
-- Questions from Readers of the DotNetVJ
18) what is difference between Primary Key, Unique Key and Alternative Key?

Let me know if you have any more questions on "Constraints"

SQL Server Interview Question Answers -- Part 2

4) What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.

6) What is the difference between RULE and Check Constraint?
The major difference between rule and Check is reusability. Check constraint is associated with columns in a Table. So these can't be re-used. Rules are defined with in a database and can be applied to any number of columns.

7) How to create a relationship between two tables?
Using Foreign Keys. Create a Foreign Key on child table referencing Unique Key or Primary key of Parent table.

8) Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is posible to create a Foreign key constraint.

9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
INFORMATION_SCHEMA.TABLE_CONSTRAINTS, where CONSTRAINT_TYPE column stores the information of Constraint Type.

Please let me know, if you have simple definitions!!!!
Lets try to prepare list of possible interview questions on "Constraints".
I will answer all of these in next section.
1) What is a Primary Key?
2) What is Unique Key?
3) What is the difference between Primary Key and Unique Key?
4) What is Check Constraint?
5) What is RULE?
6) What is the difference between RULE and Check Constraint?
7) How to create a relationship between two tables?
8) Can we create a Foreign Key with out Primary Key?
9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
10) What is the table name, that contains Check Constraint Definition?
11) What is the table name, that contains RULE Constraint definition?
12) Can we have RULE and Check Constraint on the same column?
13) Can we apply Integrity Constraints on Computed Columns?
14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
15) Can you drop a Parent Table with out affecting its child tables?
16) How to disable and Enable the constraints?
17) What is the order of Constraints execution?
-- Questions from Readers of the DotNetVJ
18) what is difference between Primary Key, Unique Key and Alternative Key?

Let me know if you have any more questions on "Constraints"

60 Seconds with Tony John

60 seconds with Tony John:
The MVP award made me feel that I am being recognized for what I did for the community.

VJ caught up with Tony John, MVP in Visual Developer - ASP/ASP.NET, to get his view on the Microsoft Most Valuable Professional Program(MVP).

VJ: Please tell us about yourself
Tony John:I am Tony John, founder of websites including dotnetspider.com, SilverlightClub.com, AspSpider.com, IndiaStudyChannel.com and few others. I have been working as a software engineer for last 12+ years.

I am from Kerala, but live in Houston Texas.

VJ: What motivated you to become an MVP
Tony John: I am a graduate in teaching before I did my Masters in Computers. I used to love teaching others. I started the community activities back in Chennai several years back with the aim of helping poor students and graduates learn programming. When the number of students increased for my free training, I switched to internet based training and that game birth to my first technology website dotnetspider.com

At that time, I wasn’t aware of the MVP recognition. However, after dotnetspider became popular, one of the active members of dotnetspider referred me for this program and I became an MVP.

VJ: Please share with us your journey of becoming an MVP.
Tony John: It was a great experience to be part of the elite group. The MVP award made me feel that I am being recognized for what I did for the community. It gave me good reputation among my colleagues and friends.

The biggest advantage I have as part of the MVP award is, it helps me a lot to promote my websites. As a Microsoft MVP, other webmasters recognize me and responds to my emails. It was easy for me to sign up for various partnership programs since the MVP label adds more trust to my name. I could make several companies sponsor prizes and awards in my websites using my MVP status.

VJ: Tell us about your MVP Summit experience
Tony John:The MVP summit was a great event. It gave me the opportunity to meet lot of people whom I have otherwise only heard about in the past. I could meet several fellow webmasters there. It gave me the opportunity to talk in person with the core people behind many Microsoft products.

VJ: Any message to technologists
Tony John:My advice to the new members is, focus on the work you are doing without worrying about receiving the award. You must demonstrate technical capabilities as well as willingness to help the community members. I am sure the dedicated work will be paid off and the MVP award will come on your way.

SQL Server Interview Question Answers -- Part 1

1) What is a Primary Key?

==> A Primary Key in a table uniquely identifies each and every row with out allowing Nulls. There are two types of Primary Keys:
1.1) Simple Primary Key ==> Defining primary key on a single column is called Simple Primary Key.
2.1) Composite Primary Key==> Defining Primary Key on more than one column is called Composite Primary Key.

2) What is Unique Key?
==> A Unique Key in a table uniquely identifies each and every row and allowing Nulls per column Combination.
There are two types of Unqiue Keys:
1.1) Simple Unique Key ==> Defining Unique key on a single column is called Simple Unique Key.
2.1) Composite Unique Key==> Defining Unique Key on more than one column is called Composite Unique Key.

3) What is the difference between Primary Key and Unique Key?
3.1)Primary Key by definition cannot be null, where as unique key can accept null values but if the unique key is defined on a column which is not null , then this unique key can also be used as an alternate primary key functionality to identify unique rows in a table.
3.2)By definition you can have only one primary key defined on a table where as you can have multiple unique keys defined on a table
3.3)Also by default Primary key is created as clustured index and unique key is created as non clustered index.


Please let me know, if you have simple definitions!!!!


Lets try to prepare list of possible interview questions on "Constraints".
I will answer all of these in next section.
1) What is a Primary Key?
2) What is Unique Key?
3) What is the difference between Primary Key and Unique Key?
4) What is Check Constraint?
5) What is RULE?
6) What is the difference between RULE and Check Constraint?
7) How to create a relationship between two tables?
8) Can we create a Foreign Key with out Primary Key?
9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
10) What is the table name, that contains Check Constraint Definition?
11) What is the table name, that contains RULE Constraint definition?
12) Can we have RULE and Check Constraint on the same column?
13) Can we apply Integrity Constraints on Computed Columns?
14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
15) Can you drop a Parent Table with out affecting its child tables?
16) How to disable and Enable the constraints?
17) What is the order of Constraints execution?

SQL Server Interview Questions

Lets try to prepare list of possible interview questions on "Constraints". I will answer all of these in next section.

1) What is a Primary Key?
2) What is Unique Key?
3) What is the difference between Primary Key and Unique Key?
4) What is Check Constraint?
5) What is RULE?
6) What is the difference between RULE and Check Constraint?
7) How to create a relationship between two tables?
8) Can we create a Foreign Key with out Primary Key?
9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
10) What is the table name, that contains Check Constraint Definition?
11) What is the table name, that contains RULE Constraint definition?
12) Can we have RULE and Check Constraint on the same column?
13) Can we apply Integrity Constraints on Computed Columns?
14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
15) Can you drop a Parent Table with out affecting its child tables?
16) How to disable and Enable the constraints?
17) What is the order of Constraints execution?


Do you think of anything else on "Constraints"?? If yes then Please post it in Comments section.

Constraints in SQL Server

There are total six types of constraints in SQL Server to implement different types of business rules and they are:
1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Chance to Become Most Valuable Professional (Microsoft MVP)

If you have been actively contributing to online or offline technical communities over the past year, here is your chance to get recognized. Nominate yourself for the Microsoft MVP Award - a program that recognizes exceptional technical community leaders from around the world who voluntarily share their high quality, real world expertise with others.

RULE in SQL Server

In this article I would like to explain about “Rules” which are part of Constraints family. Constraints are one of the key factors in designing a table. Constraints are mainly used to implement business rules. Let't take one real world example. When you are opening a bank account, there is a bank rule that says, you need to maintain a minimum balance of 1000 in your account. This is a business rule.

There are several places where we can implement this rule.
The way I see is, implementing this in Back-end gives more flexibility. For now I will explain about how to implement this business rule in back-end database.

To implement this kind of rule either we can use RULE or CHECK Constraint. This article will implement this business rule using RULE constraint.

There are total six types of constraints in SQL Server to implement different types of business rules and they are:
1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Other Relevant articles related to Constraints:
.. List all the Primary Keys in SQL Server Database
.. List all the Foreign Keys in a SQL Server
.. Foreign Key and Primary Key in SQL Server

In my previous articles we did talk about all the different constrains except Rule.

RULE Constraint:
RULE is an object with in a database. First you create a rule and then bind this rule to any no.of columns with in database. So this is more towards to the Object Oriented programming, where you are trying to re-use the code/logic.

Create and Bind:

Let’s Create the RULE for our business rule:

“Need to maintain a minimum balance of 1000 in your Check-in account.”

Let’s create an account table.

CREATE TABLE ACCOUNT(
ACCOUNT_ID int NULL,
ACCOUNT_LOCATION varchar(10) NULL,
CUSTOMER_ID int NULL,
CHECK_IN_AMT int NULL,
SAVINGS_AMT int NULL
)

Step 1: Let's create a RULE

CREATE RULE Amount_rule
AS
(@CHECK_IN_AMT >= 1000)

Step 2: Bind this rule to a column.

EXEC sp_bindrule 'Amount_rule', 'account.CHECK_IN_AMT'

Lets try to insert data into this table

INSERT INTO ACCOUNT VALUES(1001,'NY',1,1000,20);
INSERT INTO ACCOUNT VALUES(1002,'NY',2,1010,20);
INSERT INTO ACCOUNT VALUES(1003,'NJ',3,1000,20);
INSERT INTO ACCOUNT VALUES(1004,'CT',4,1020,20);
INSERT INTO ACCOUNT VALUES(1005,'FL',5,1200,20);

I am able to insert all the above rows with out any issues/errors. This is because CHECK_IN_AMT column is always equal to or greater than 1000.

Now lets try to insert below record

INSERT INTO ACCOUNT VALUES(1006,'FL',6,800,20);

Msg 513, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'testdemo', table 'dbo.ACCOUNT', column 'CHECK_IN_AMT'.
The statement has been terminated.


Now let’s try to reuse the same RULE on SAVINGS_AMT column.

EXEC sp_bindrule 'Amount_rule', 'account.SAVINGS_AMT'

Now lets try to insert below record
INSERT INTO ACCOUNT VALUES(1005,'FL',5,1200,20);

Msg 513, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'testdemo', table 'dbo.ACCOUNT', column 'SAVINGS_AMT'.
The statement has been terminated.

DROP and Un-Bind:

Now let’s remove the rule on SAVINGS_AMT column.

EXEC sp_unbindrule 'account.SAVINGS_AMT'

DROP RULE Amount_rule;

Key Point:
Rules and Check constraints are like a brother and sister. Check constraint is specific to a column with in a table. Rules are not specific to a table or a column.

Rules are a backward compatibility feature and check constraints are preferable.

60 seconds with Abhishek Kant

60 seconds with Abhishek Kant
MVPs are champions of community.

VJ caught up with Abhishek Kant, India MVP Lead, to get his view on the Microsoft Most Valuable Professional Program(MVP).

VJ: Please tell us about yourself.
Abhishek: I work with the Microsoft Technical communities in India to support and encourage them. I am employed by Microsoft and designated as Community Program Manager. I have been a techie part of my life after being trained as a banker in management school.
I blog about technology at: http://www.abhishekkant.net/
I am a gadget freak, enjoy programming in my spare time and love playing on my XBOX360 (gamertag: chints).

VJ: Please tell us about MVP Program and Its importance.
Abhishek: Microsoft community in India has a large presence in form of user groups, forums and blogs. MVP Award recognizes the top community contributors for Microsoft technologies. MVP is a globally recognized award and one that Steve Ballmer, CEO himself keeps a close contact with. MVPs are conscience of Microsoft and provide valuable neutral feedback on all our products. Their feedback (bugs and feature suggestions) is highly regarded by the development teams.
MVPs are champions of community and Microsoft relies on them to guide us in determining what community needs from us. To know about the great work Indian MVPs have been doing, visit:http://blogs.technet.com/southasiamvp

VJ: Please tell us about how to become an MVP.
Abhishek: You can’t take a certification to be an MVP! You have to earn it by working hard in the community and getting recognized as a leader there. The only way one can take the leadership position in the community is by being the most prolific contributor in the community.
You can self nominate yourself for the MVP award by visiting: www.microsoft.com/india/mvp
Make no mistake it is very difficult to become an MVP. We have only 125 MVPs in India and over 4000 world over.

VJ: Please tell us about benefits of becoming an MVP
Abhishek: MVPs enjoy recognition, technical and relationship benefits from Microsoft. Some important benefits are:
The most important benefit to the MVPs is access to all existing and upcoming (betas/ alphas) Microsoft products for free. Microsoft facilitates interactions between product teams and MVPs where they can participate in determining the future of the products.
Access to the wide network of MVPs worldwide provide for excellent networking and learning opportunities that is rare to find in professional world.

VJ: Please tell us about your MVP Summit experience
Abhishek: MVP Summit is a very different experience from regular conferences. Presence of MVPs from over 35 countries under one roof provides valuable learning and networking moments. Personal interactions with Microsoft product teams and candid exchange of ideas are unique features of MVP Summit that make it very valuable. The high point of every MVP Summit is the Steve Ballmer keynote. He is the best speaker I know of in the world.

VJ: Any message to the Technologists
Abhishek: Learning from books is something that we all learnt growing up. What I wish I knew in college was that a person learns more from participating in the community. Community challenges you with real world problems and encourages you to think of real world solutions. I wish I had participated in technical forums early on so that my knowledge would have grown exponentially.
If there is one thing that I would urge all technologists, whatever stage of career you are in at, to do is to start participating in community of interest to you. If you are a Microsoft technologist you definitely want to check out Microsoft Forums.
Communities are where you find the best and brightest of people engaged in world over. An interesting by product of community participation is the global recognition you would gain for yourself.

Difference between Primary and Unique Key Constraint

Do you know what is the difference between Primary Key and Unique Key Constraint?

If YES then You can display your name on my Blog.

Hurry-up post your reply by adding your comments on or before 08-APR-2009 (Last Day)

If you need any reference or help on this please check out my March and April Articles in Archive Section.

NOTE: Please do not copy the content from any other site.

I have got over 20 responses in my mail-box due to issues in posting the answers in the comment section and out of that selected "Aashish" as the winner of contest.

According to Aashish the Differences are:
1.Primary Key by definition cannot be null, where as unique key can accept null values but if the unique key is defined on a column which is not null , then this unique key can also be used as an alternate primary key functionality to identify unique rows in a table.
2.By definition you can have only one primary key defined on a table where as you can have multiple unique keys defined on a table
3.Also by default Primary key is created as clustured index and unique key is created as non clustered index.

This is the perfect answer to this question.

Unique Constraint in SQL Server

In this post I would like to summarize the different articles on Unique Constraint in SQL Server.
Constraints are one of the key factors in designing a table. When you are opening a bank account, bank says that you can not have more than one account in the same branch or location. This is a business rule and this can be implemented using Unique Constraints.

Unique Constraints can be defined at the Column level or Table Level.

Below are the list of articles related to Unique Constraints:
.. Unique Constraints Introduction and Defining at the Table Level
.. Defining Unique Constraints at the Column Level or on Single Column
.. How to find Unique Constraints definition and how to drop them

Unique Constraint in SQL Server -- Part 3

In this article I would like to show you on how to find the Unique constraints defined on a table.



In my previous articles I explained about Unique Constraint creation at the Table Level and also at the Column Level.

How to query Unique Key Constraints Definition?

The below script will give you Constraint definition along with the constraint name.

SELECT TC.TABLE_NAME,CC.CONSTRAINT_NAME,CC.COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.CONSTRAINT_TYPE = 'UNIQUE'
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
AND TC.TABLE_NAME = CC.TABLE_NAME

How to Drop Unique Constraints?

As always, to drop any object you need to find out the object name.

In this case ,I want to drop UC_ACCOUNT01 constraint defined on a ACCOUNT table.

ALTER TABLE ACCOUNT DROP CONSTRAINT UC_ACCOUNT01;

That’s it. It’s very simple.

Unique Constraint in SQL Server -- Part 2

In this article I will talk about Column Level unqiue Constraints in SQL Server. These are very useful if you want to have rules based on one single column within a table.

Please check my article on Creating Unique constraint at the table Level.


This can be created in two different ways i.e. CREATE TABLE/ALTER TABLE

Let’s go with an example.

Business rule which I want to set is Customer SSN should be unqiue.

Lets create CUSTOMER table and insert few records.

CREATE TABLE CUSTOMER
(CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(50),
CUSTOMER_LOCATION VARCHAR(10),SSN VARCHAR(12))

INSERT INTO CUSTOMER VALUES(1,'SAMY','NY','111-11-1111');
INSERT INTO CUSTOMER VALUES(2,'BECK','NJ','222-22-2222');
INSERT INTO CUSTOMER VALUES(3,'STAN','TX','333-33-3333');
INSERT INTO CUSTOMER VALUES(4,'NICK','FL','444-44-4444');

ALTER TABLE:

alter table CUSTOMER
add constraint UC_Customer01 unique(SSN);

CREATE TABLE:

CREATE TABLE CUSTOMER
(CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(50),
CUSTOMER_LOCATION VARCHAR(10),SSN VARCHAR(12),CONSTRAINT UC_Customer01 unique(SSN))

Now lets try to insert one record with a new customer and existing SSN.

INSERT INTO CUSTOMER VALUES(5,'VINCE','FL','444-44-4444');

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UC_Customer01'. Cannot insert duplicate key in object 'dbo.CUSTOMER'.
The statement has been terminated.

It’s very simple isn’t it?How do you find list of Unique constraints in a database defined on a table? Is there any way I can drop these constraints?? To find the answers to these questions you need to check my next article.

Unique Constraint in SQL Server -- Part 1

In this article I would like to explain about “Unique Constraints”. Constraints are one of the key factors in designing a table. Before I explain what is the use of constraints lets take one real world example. When you are opening a bank account, there is a bank rule that says, you can not have more than one account with in same branch or location. This is a business rule.

To implement this kind of rule either we can use UNIQUE Constraint or Primary Key Constraint. This article will implement this business rule using UNIQUE KEY constraint.

The way I see is, implementing this in Back-end gives more flexibility. For now I will explain about how to implement this business rule in back-end.

There are total six types of constraints in SQL Server to implement different types of business rules and they are:

1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Other Relevant articles related to Constraints:
.. List all the Primary Keys in SQL Server Database
.. List all the Foreign Keys in a SQL Server
.. Foreign Key and Primary Key in SQL Server

Unqiue Constraint can be created in two different ways i.e. CREATE TABLE/ALTER TABLE and also at the two levels i.e. Table Level or Column Level. When the constraint is defined at the table level or on multiple columns it is also called as Composite constraint.

In my previous articles we did talk about Default,Check, Primary and Foreign Key Constraints. In this article we will talk about Unique Constraint.

Lets create ACCOUNT table and insert few records.

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,CHECK_IN_AMT INT,SAVINGS_AMT INT);

INSERT INTO ACCOUNT VALUES (10001,'NY',100010,1000,0);
INSERT INTO ACCOUNT VALUES (10002,'NJ',100020,800,0);
INSERT INTO ACCOUNT VALUES (10003,'LA',100030,0,800);

Table Level:

Business rule says “you can not have more than one account with in same branch or location”. This can be implemented by placing unique constraint on ACCOUNT_LOCATION and CUSTOMER_ID.

ALTER TABLE:

alter table ACCOUNT
add constraint UC_account01 unique(CUSTOMER_ID,ACCOUNT_LOCATION)

Let's try to insert below record.

INSERT INTO ACCOUNT VALUES(10004,'NJ',100020,1000,100);

SQL Server will throw below error
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UC_ACCOUNT01'. Cannot insert duplicate key in object 'dbo.ACCOUNT'.
The statement has been terminated.

CREATE TABLE:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,CHECK_IN_AMT INT,SAVINGS_AMT INT, CONSTRAINT UC_ACCOUNT01 UNIQUE(CUSTOMER_ID,ACCOUNT_LOCATION));

Couple of points on Unique Constraints:

1) UNIQUE constraints are to make sure that no duplicate values are entered in specific columns that do not participate in a primary key.
2) When you are placing an unique constraint it will automatically create Unique Index to maintain the integrity.
3) Unique Allows one Null value per column where as Primary key doesn’t allow null values.
4) There is no limit on having number of unqiue constraints on a table.
5) Unique Key constraint can be referenced in Foreign Key Column.

Check Constraint in SQL Server

In this post I would like to summarize all the different articles on Check Constraints in SQL Server.

Constraints are one of the key factors in designing a table. When you are opening a bank account, bank says that you need to maintain a minimum balance of 1000 in your account. This is a business rule and this can be implemented using Check Constraints. Check Constraints can be defined at the Column level or Table Level.

Below are the list of articles related to Check Constraints:
.. Check Constraints Introduction and Defining at the Column Level
.. Defining Check Constraints at the Table Level or on multiple Columns
.. How to find Check Constraints definition and how to drop them

Check Constraint in SQL Server -- Part 3

In this article I would like to show you on how to find the check constraints defined on a table.

In my previous articles I explained about Check Constraint creation at the Table Level and also at the Column Level.

How to query Check Constraints Definition?

The below script will give you Constraint definition along with the constraint name.

SELECT TC.TABLE_NAME,CC.CONSTRAINT_NAME,CC.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS CC,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.TABLE_NAME = 'ACCOUNT'
AND TC.CONSTRAINT_TYPE = 'CHECK'
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME



How to Drop Check Constraints?

As always to drop any object you need to find out the object name. In this case ,I want to drop cc_max_bal constraint defined on a table.

ALTER TABLE ACCOUNT DROP CONSTRAINT cc_max_bal;


That’s it. It’s very simple.

Are you looking for anything else in this!!!!

Check Constraint in SQL Server -- Part 2

In this article I will talk about Table Level Constraints in SQL Server. These are very useful if you want to have rules based on multiple columns with in a table.

Please check my article on creating constraint at the column Level.

This can be created in two different ways i.e. CREATE TABLE/ALTER TABLE

Let’s go with an example.

Business rule which I want to set is if a person opens check-in account and savings account then minimum balance should not be less than 800 by combining both.

CREATE TABLE:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,CHECK_IN_AMT INT,SAVINGS_AMT INT,CONSTRAINT cc_min_bal CHECK(CHECK_IN_AMT+SAVINGS_AMT>=800));

If you look at the syntax of the table creation I added CHECK constraint at the end of the all the columns. This is the way to create Table level constraints.

Lets try to insert the data:

INSERT INTO ACCOUNT VALUES (10001,'NY',100010,1000,0);
INSERT INTO ACCOUNT VALUES (10002,'NJ',100020,800,0);
INSERT INTO ACCOUNT VALUES (10003,'LA',100020,0,800);
INSERT INTO ACCOUNT VALUES (10004,'TX',100040,200,200);

I was able to insert first 3 records but last INSERT statement failed because check constraint is looking for minimum of 800 by combining both the account amounts.

Error while inserting the 4th insert statement is:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "cc_min_bal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT".
The statement has been terminated.

ALTER TABLE:

Now let’s define one more rule on the same table and on the same columns. The rule is Balance should not exceed more than 80000.

ALTER TABLE ACCOUNT
ADD CONSTRAINT cc_max_Bal
CHECK (CHECK_IN_AMT+SAVINGS_AMT<=80000);

Lets try to insert the data.
INSERT INTO ACCOUNT VALUES (10004,'TX',100040,200000,200);

Error while inserting this insert statement is:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "cc_max_Bal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT".
The statement has been terminated.

It’s very simple isn’t it?

How do you find list of check constraints in a database defined on a table? Is there any way I can drop these constraints?? To find the answers to these questions you need to check my next article.

Check Constraint in SQL Server -- Part 1

In this article I would like to explain about “Constraints”. Constraints are one of the key factor in designing a table. Before I explain what is the use of constraints lets take one real world example. When you are opening a bank account ,there is a bank rule that says, you need to maintain a minimum balance of 1000 in your account. This is a business rule. There are several places to implement this rule. We can implement this in Front-End application or in the Back-end code.

The way I see is, implementing this in Back-end gives more flexibility. For now I will explain about how to implement this business rule in back-end.

There are total six types of constraints in SQL Server to implement different types of business rules and they are:
1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Other Relevant articles related to Constraints:
.. List all the Primary Keys in SQL Server Database
.. List all the Foreign Keys in a SQL Server
.. Foreign Key and Primary Key in SQL Server

In my previous articles we did talk about Default, Primary and Foreign Key Constraints. In this article we will talk about Check Constraint.

Check Constraints:

We can use check constraints to limit the range of possible values in a column or to enforce a specific pattern for data. All these check constraints must evaluate to Boolean and it can’t reference columns in another table. Basically Check constraints are table specific.

We can create check constraints at two different levels:
1) Column Level
2) Table Level

As the name implies Column level can’t reference any other column with in the same table. The best example would be Minimum balance of an account. Table Level constraints can refer any column with in the table.

Column Level

Let’s create one ACCOUNT table and insert following records.

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),CUSTOMER_ID INT,ACCOUNT_BALANCE INT);

INSERT INTO ACCOUNT VALUES (10001,'NY',100010,50000);
INSERT INTO ACCOUNT VALUES (10002,'NJ',100020,60000);
INSERT INTO ACCOUNT VALUES (10003,'LA',100020,90000);
INSERT INTO ACCOUNT VALUES (10004,'TX',100040,15000);

We can create check constraint using CREATE/ALTER TABLE statement.

ALTER TABLE:

Lets add check constraint on ACCOUNT_BALANCE

ALTER TABLE ACCOUNT
ADD CONSTRAINT cc_MinBal
CHECK (ACCOUNT_BALANCE > 1000);

Now lets try to insert the data into this table with ACCOUNT_BALANCE = 900

INSERT INTO ACCOUNT VALUES (10005,'FL',100050,900);

When we run the above statement we will get below error.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "cc_MinBal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT", column 'ACCOUNT_BALANCE'.
The statement has been terminated.

Lets drop the Constraint

ALTER TABLE ACCOUNT DROP CONSTRAINT cc_MinBal;

Lets insert below record.

INSERT INTO ACCOUNT VALUES (10005,'FL',100050,900);


Now try to palce the check constraint on the column.

ALTER TABLE ACCOUNT
ADD CONSTRAINT cc_MinBal
CHECK (ACCOUNT_BALANCE > 1000);

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "cc_MinBal". The conflict occurred in database "testdemo", table "dbo.ACCOUNT", column 'ACCOUNT_BALANCE'.

So when you are placing a check constraint on a column it will validate the data first and then only puts the rule on that column.



CREATE TABLE:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,ACCOUNT_BALANCE INT CHECK(ACCOUNT_BALANCE > 1000) )


Check out next article on placing Check constraint at the table level.

Rename Table in SQL Server

In this article I would like to explain about how to rename a table using SQL Server Management Studio (SSMS).

If you look at my previous article on “Load text or csv file data into SQL Server” I created a table called “Cusomer” and loaded the data from Flat file. I am supposed to create “Customer” but ended up missing “t”. I guess "T" is on a vacation during that time :)

Let’s get back to the task. When you are renaming an object you need to make sure
that no-one is using this table. When I say no-one I mean it is not referenced in any other objects like views, stored procedures and etc.

Once you are sure about this then, you can execute the sp_Rename system stored procedure.

SP_RENAME 'CUSOMER','CUSTOMER'

You just have to pass old table and new table name. Once the operation is completed you will get the below message.

Caution: Changing any part of an object name could break scripts and stored procedures.

What if the new table name already exists?? Then you will get below error message.

I tried to rename the CUSTOMER to DEPT.

Msg 15335, Level 11, State 1, Procedure sp_rename, Line 402
Error: The new name 'DEPT' is already in use as a object name and would cause a duplicate that is not permitted.

What if the table that you are trying to rename is referenced in Views? How can you find this? Is there any simple query to do it??

For all these questions please check out my next article.

Drop DEFAULT Constraint in SQL Server

In my previous article, I explained about how to assign a DEFAULT value to a column.
In this section I will explain about how to drop/remove DEFAULT value from a column.

Let’s take a step back little bit and see, what is the syntax that was used to create DEFAULT value?

ALTER TABLE DEPTADD CATEGORY VARCHAR(3) NOT NULL DEFAULT ('IT')

If you look at the syntax, I didn’t mention the name of the constraint.

But the thumb rule in SQL language is you need to have a name to drop anything!!!!!

So to drop this constraint we need to find out the name of the default constraint that was given by SQL Server.

NOTE: If you don’t provide any name to constraints, SQL Server will automatically assign one for you.

Let’s find out the name of the constraint. All the default constraints are stored in SYS.DEFAULT_CONSTRAINTS table.

Below, I listed out all the important columns from SYS.DEFAULT_CONSTRAINTS table.

SELECT NAME,OBJECT_ID,PARENT_OBJECT_ID,TYPE,
TYPE_DESC,PARENT_COLUMN_ID,DEFINITION
FROM SYS.DEFAULT_CONSTRAINTS



In my database I have only one DEFAULT constraint that’s the reason I have only one record. If you have multiple rows then how would you identify the name of the constraint?? In that case you need to resolve the PARENT_OBJECT_ID, PARENT_COLUMN_ID columns.

NOTE: When ever you create an object in SQL Server, it will automatically assign one unique number and stores that information in OBJECT_ID.

EX: SELECT NAME,OBJECT_NAME(OBJECT_ID) FROM SYS.DEFAULT_CONSTRAINTS
If you want to find out the name of the object then pass OBJECT_ID to OBJECT_NAME function.

Let’s come back to our original task. So after resolving the names using IDs the query will be

DECLARE @DEF_CONS_NAME VARCHAR(100);
SELECT @DEF_CONS_NAME = NAME
FROM SYS.DEFAULT_CONSTRAINTS
WHERE OBJECT_NAME(PARENT_OBJECT_ID) = 'DEPT'
AND upper(COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID)) = 'CATEGORY'
SELECT @DEF_CONS_NAME




Now the next step is to construct the dynamic query to remove.

DECLARE @DEF_CONS_NAME VARCHAR(100);
SELECT @DEF_CONS_NAME = NAME
FROM SYS.DEFAULT_CONSTRAINTS
WHERE OBJECT_NAME(PARENT_OBJECT_ID) = 'DEPT'
AND upper(COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID)) = 'CATEGORY'
exec('alter table dbo.DEPT drop constraint ' + @DEF_CONS_NAME)

That’s it. It’s very simple isn’t it?

If you closely observe the above query I used OBJECT_NAME, COL_NAME, UPPER functions. I will explain about these in my next article.