SQL Server Interview Questions and Answers
<< Previous Question Next Question >>
 Question: 295 Page Views: 

Is it possible to create a Primary Key and Unique Key on Computed columns?



Posted By: Avi Date: 1 October 2010 11:44:07 AM
 Answer:

Yes, it is possible to create a Primary ke and unique key on computed columns. In case of Unique key its very simple and straightforward.For eg.:- Create table ss(id int NOT NULL, id2 as id +2 UNIQUE) The query will execute without any error. But in case of Primary Key its little bit tricky. If the following query is executed it will result into an error. viz. :- Create table ss(id int NOT NULL, id2 as id +2 Primary Key) . The error occured because the Primary Key can't have NULL value and SQL SErver have to guarantee that the computation will not lead to a null value or an overflow value or underflow value. So to use computed column as a Primary Key the one has to wrap it up with the ISNULL() function . The alternate Query will be as follows:- Create table ss(id int NOT NULL, id2 as ISNULL(id +2,0) Primary Key) So now if computation results in Null value the 0 will be inserted automatically.


Posted By: avibtech

Date: 1 October 2010 11:44:07 AM
Post a better Answer if you have
 
(Will show your Gravatar icon)  
  Country flag

Loading
Enter the text as shown in the image J3R6Ci
Related Questions
SQL Server : What is the difference between a live lock and a deadlock?

What is the difference between a live lock and a deadlock?

Live Lock:- In Live Lock the user is only able to read the transaction of the table and is unable to....
Category: SQL Server Date: 10/1/2010 11:23:07 AM
SQL Server : What do the Lock escalation do?

What do the Lock escalation do?

Lock escalation is the process which helps in converting the low level locks into higher level locks....
Category: SQL Server Date: 10/1/2010 11:22:07 AM
SQL Server : Why are locks used in SQL server?

Why are locks used in SQL server?

In SQL Server locks help the users from making conflicting changes into the database. When the table....
Category: SQL Server Date: 10/1/2010 11:21:07 AM
SQL Server : Can a stored procedure have optional parameters?

Can a stored procedure have optional parameters?

Yes, a stored procedure can have optional parameters. With the help of default value for the optiona....
Category: SQL Server Date: 10/1/2010 11:20:07 AM
SQL Server : Can you tell the important features of sub-queries?

Can you tell the important features of sub-queries?

Subqueries are very beneficial if used properly.They allow to return the desired result in a single ....
Category: SQL Server Date: 10/1/2010 11:19:07 AM
 
Signup for our weekly email newsletter
email newsletter
Questions Categories