Multiple Choice Questions - Identity Column
1. When you make a column an identity column, SQL Server automatically assigns a . . . . . number to this column with every row you insert.
A) next
B) random
C) decimal
D) sequenced
2. The number that SQL Server starts counting from is called the . . . . . value and the amount that the value increases or decreases by with each row is called the . . . . . .
A) unique, sequence
B) identity, increase
C) seed, increment
D) Both A & C
3. You can create . . . . . IDENTITY column(s) per table.
A) one
B) two
C) three
D) unlimited
4.
CREATE TABLE SSS (ID int identity(1,1), Name char(20))
insert into SSS values('aaa');
SELECT SCOPE_IDENTITY() as new_rec_1
SELECT IDENT_CURRENT('SSS') as new_rec_2
insert into SSS values('bbb');
SELECT SCOPE_IDENTITY() as new_rec_3
SELECT IDENT_CURRENT('SSS') as new_rec_4
If above statements are run in the same sequence as written above, what will be the values of new_rec_1, new_rec_2, new_rec_3, new_rec_4A) 2, 2, 1, 1
B) 1, 1, 3, 3
C) 1, 1, 2, 2
D) 2, 2, 4, 4
5. Does identity guarantee uniqueness?
A) No
B) Yes
6. Which of the following statement is true:
A) It is mandatory to specify both seed and increment.
B) It is fine if neither seed nor increment are specified.
C) If neither of these are specified, the default is (1, 1).
D) Both A & C
E) All of above
7. Identity column works sequentially. Once you have set a seed and the increment, your values only increment or decrement. There is no automatic mechanism to go back and fill in the numbers for any rows you may have deleted. If you want to fill in the blank spaces, you need to use . . . . . . . . which allows you to turn off the identity process for inserts from the current connection.
A) SET IDENTITY_INSERT OFF
B) SET IDENTITY ON
C) SET IDENTITY OFF
D) SET IDENTITY_INSERT ON
8. An identity option can also be used in conjuction with a default constraint.
A) True
B) False
9. Which of the below statements are valid:
1. CREATE TABLE SSS3 (ID int identity(2,0), Name char(20))
2. CREATE TABLE SSS4 (ID int identity(0,2), Name char(20))
3. CREATE TABLE SSS5 (ID int identity(0,0), Name char(20))
4. CREATE TABLE SSS6 (ID int identity(2,2), Name char(20))
A) Only first is valid
B) Both 2nd & 3rd are valid
C) Both 2nd & 4th are valid
D) None of the above is valid
10.
CREATE TABLE SSS4 (ID int identity(0,2), Name char(20))
insert into SSS4 values('aaa');
insert into SSS4 values('bbb');
Select * from SSS4| ID | Name |
|---|---|
| 0 | aaa |
| 2 | bbb |
DBCC checkident(SSS4)
DBCC checkident(SSS4, reseed, 3)
insert into SSS4 values('ccc');
Select * from SSS4
What will be the result of above Select statement?
A)
| ID | Name |
|---|---|
| 0 | aaa |
| 2 | bbb |
| 3 | ccc |
B)
| ID | Name |
|---|---|
| 0 | aaa |
| 2 | bbb |
| 4 | ccc |
C)
| ID | Name |
|---|---|
| 0 | aaa |
| 2 | bbb |
| 1 | ccc |
D)
| ID | Name |
|---|---|
| 0 | aaa |
| 2 | bbb |
| 5 | ccc |
Answers