Few interview questions

Question

Few interview questions

Ahmad Osama

SSCertifiable

Points: 7676
More actions
October 7, 2008 at 3:18 am

#123865

Hi All,
Here are few interview questions..
1) Is it possible to have a NULL value in a foreign key field?
2) If I have 350 columns in a table, how can I index each n every column?
3) I have Insert,update and delete trigger on a table.If 3 different users fire an insert, update and delete statement in fraction of second, in what order triggers will be executed.?
4)If a table recieves 50,000 rows every 30 mins, then which index clustered/nonclustered will be appropriate for the given scenario?
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ahmad Osama (10/7/2008)
Hi All,
Here are few interview questions..
1) Is it possible to have a NULL value in a foreign key field?

Sure, providing the column is declared nullable.

2) If I have 350 columns in a table, how can I index each n every column?

You wouldn't. It's overkill and that many indexes will cause major degradation in insert/update/delete performance. Index the columns that are used in joins/filters and create fewer wider indexes rather than many narrow ones.

4)If a table recieves 50,000 rows every 30 mins, then which index clustered/nonclustered will be appropriate for the given scenario?

Not enough information. Appropriate indexes depend on the queries been executed against the table and the columns types in the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

1) Is it possible to have a NULL value in a foreign key field?
Sure, providing the column is declared nullable.

does Foreign key allows a column to be nullable.

2) If I have 350 columns in a table, how can I index each n every column?
You wouldn't. It's overkill and that many indexes will cause major degradation in insert/update/delete performance. Index the columns that are used in joins/filters and create fewer wider indexes rather than many narrow ones.

If we exclude the performance point ...is it possible to have these many indexes...

4)If a table recieves 50,000 rows every 30 mins, then which index clustered/nonclustered will be appropriate for the given scenario?
Not enough information. Appropriate indexes depend on the queries been executed against the table and the columns types in the table.

If we overlook these info...than based on the index architecture which will bw best in this scenario

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ahmad Osama (10/7/2008)
1) Is it possible to have a NULL value in a foreign key field?
Sure, providing the column is declared nullable.
does Foreign key allows a column to be nullable.

Yes, providing the column allows nulls.

2) If I have 350 columns in a table, how can I index each n every column?
You wouldn't. It's overkill and that many indexes will cause major degradation in insert/update/delete performance. Index the columns that are used in joins/filters and create fewer wider indexes rather than many narrow ones.
If we exclude the performance point ...is it possible to have these many indexes...

Not in SQL 2005.

4)If a table recieves 50,000 rows every 30 mins, then which index clustered/nonclustered will be appropriate for the given scenario?
Not enough information. Appropriate indexes depend on the queries been executed against the table and the columns types in the table.
If we overlook these info...than based on the index architecture which will bw best in this scenario

It is recommended that all tables have a clustered index. Whether or not you would want nonclusters depends on what queries are running and how that data is getting inserted. Where you would put the cluster depends on the data types of the columns, the queries been run and the type of data been inserted.

Did someone ask you this lot in an interview?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

Ahmad Osama (10/7/2008)
Hi All,
Here are few interview questions..
1) Is it possible to have a NULL value in a foreign key field?

Already been answered several times...

2) If I have 350 columns in a table, how can I index each n every column?

Already answered, but to emphasize... You can't... the limit on the number of indexes for a table is much less than that. (Teach a person to fish) Lookup "Maximum Capacity Specifications" in Books Online to find out more.

3) I have Insert,update and delete trigger on a table.If 3 different users fire an insert, update and delete statement in fraction of second, in what order triggers will be executed.?

To the best of my knowledge, it's not predictable unless you identify them as first or last... and you can only have 1 first and 1 last. All the others aren't really predictable. You can force the order by having a single trigger that will do all 3 tasks in the order you want.

Also note that each operation will be treated as if a transaction so if more than one user tries to update or delete the same row, who ever get's there first will fire the triggers first.

4)If a table recieves 50,000 rows every 30 mins, then which index clustered/nonclustered will be appropriate for the given scenario?

The answer is... whichever one that implies a temporal order for when the inserts occurred. In other words, either an IDENTITY column or some temporal column such as the date the row was inserted. Otherwise, the clustered index will suffer some pretty bad page splits (depending on the Fill Factor) on the data and the non-clustered indexes will also "split". When non-clustered indexes split within themselves, they do it a full extent (8 pages) at a time. They can become quite large and terribly fragmented very quickly if you don't watch your step.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jeff Moden (10/7/2008)
the limit on the number of indexes for a table is much less than that. (Teach a person to fish) Lookup "Maximum Capacity Specifications" in Books Online to find out more.

In 2008, the limit's increased significantly, because of the filtered indexes and sparse columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

GilaMonster (10/7/2008)
Jeff Moden (10/7/2008)
the limit on the number of indexes for a table is much less than that. (Teach a person to fish) Lookup "Maximum Capacity Specifications" in Books Online to find out more.
In 2008, the limit's increased significantly, because of the filtered indexes and sparse columns.

Heh... then the correct answer for question #2 in regards to SQL Server 2008 should be "I wouldn't do that because I don't want the DBA to chase me until my heels smoke." 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jeff Moden (10/7/2008)
Heh... then the correct answer for question #2 in regards to SQL Server 2008 should be "I wouldn't do that because I don't want the DBA to chase me until my heels smoke." 😀

Any person who wants to index every column of the table 'just because' deserves to be chased.

I can see cases where the old limit would be hit with systems with lots and lots of sparse columns in the tables. Apps like sharepoint that have hundreds of optional attributes that can be searched on. With filtered indexes on only the small portion of the table that has values for the particular attributes, it's possible to have a lot more indexes without having a increased impact on changes to the table.

It's one of those very small minority cases. Most of the time I start worrying at 10 indexes on a table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 8

The answer is... whichever one that implies a temporal order for when the inserts occurred. In other words, either an IDENTITY column or some temporal column such as the date the row was inserted. Otherwise, the clustered index will suffer some pretty bad page splits (depending on the Fill Factor) on the data and the non-clustered indexes will also "split". When non-clustered indexes split within themselves, they do it a full extent (8 pages) at a time. They can become quite large and terribly fragmented very quickly if you don't watch your step.

does this mean that nonclustered index will suffer the most.

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

Ahmad Osama (10/7/2008)
The answer is... whichever one that implies a temporal order for when the inserts occurred. In other words, either an IDENTITY column or some temporal column such as the date the row was inserted. Otherwise, the clustered index will suffer some pretty bad page splits (depending on the Fill Factor) on the data and the non-clustered indexes will also "split". When non-clustered indexes split within themselves, they do it a full extent (8 pages) at a time. They can become quite large and terribly fragmented very quickly if you don't watch your step.
does this mean that nonclustered index will suffer the most.

It depends on the distribution of the inserts... if the distribution is wide across the table, then nonclustered indexes can suffer quite badly. If the distribution is "close" for each batch of inserts, then the fact that a full extent is created on a "split" allows for a great many of those inserts to populate the same "split".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 10

It depends on the distribution of the inserts... if the distribution is wide across the table, then nonclustered indexes can suffer quite badly. If the distribution is "close" for each batch of inserts, then the fact that a full extent is created on a "split" allows for a great many of those inserts to populate the same "split".

Does "distribution of the insert" refers to the number of columns covered?

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 11

No... number of rows. For example, if you have a table of names, if the insert has names that start with every letter of the alphabet, that would be condsidered a "wide" distribution. If they all start with the letter "J", that would be a "narrow" distribution. Columns do come into play a bit because the more columns the table has, the fewer number of rows per page you have. That's also why it's not necessarily good to have a real wide table. For example, if you have a table with rows that are 5000 characters wide, 3060 bytes will be wasted on each page no matter what the Fill Factor is because each page holds a max of 8060 bytes... that's where the row limit of 8060 comes from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

free_mascot One Orange Chip Points: 27169 More actions · Answer 12

2) If I have 350 columns in a table, how can I index each n every column?

If performance is not a problem and by hook or crook you would like to create indexes on all the columns than you can think about "Composite Index".

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

free_mascot (10/8/2008)
2) If I have 350 columns in a table, how can I index each n every column?
If performance is not a problem and by hook or crook you would like to create indexes on all the columns than you can think about "Composite Index".

You can, but a composite index on 2 columns is not the same, performance-wise, as two separate indexes.

A composite index in A, B can only be used for seeks if the query filters on A or A and B

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ewan Hampson SSCrazy Points: 2780 More actions · Answer 14

Looking back at question 1

Is it possible to have a NULL value in a foreign key field?

Whilst "Is it possible" is answered by ALLOWED NULL, isn't there a design principle here, whether an FK column should always reference a record in the PK table, even if this requires you to create an artificial 'null equivalent' record?