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.