Sql Server Interview Questions and Answers (1758) - Page 10

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

What is difference between RANK and DENSE_RANK.

The main difference between RANK and DENSE_RANK comes when they leave gap among records while ranking them.

For an example, if you have three records at position one. Both the functions will place all three records at same position of one but the difference comes is when they rank the next record. RANK() will rank the next record as fourth record while DENSE_RANK() will rank it as second.

What will you do if you need to create clustered index on more than one field? Is it possible?

The answer is yes and no.
You can create only one clustered index in table.
Workaround for such scenario can be :
Either you can create index on entire table or you can create clustered index on a view covering entire table.

Suppose, you have created table with following:

CREATE TABLE [dbo].[Table1](

	[field1] [int] IDENTITY(1,1) NOT NULL,

	[field2] [nchar](10) ,

	[field3] [nchar](10) ,

	[field4] [nchar](10)

) ON [PRIMARY]

Create index on entire table:

CREATE INDEX idx_coverTable ON Table1(field1, field2) INCLUDE (field3, field4)

Create clustered index on a view covering entire table:

CREATE VIEW v1111 with SCHEMABINDING

AS 

SELECT field1, field2, field3, field4 FROM dbo.Table1

GO

CREATE UNIQUE CLUSTERED INDEX idx_viewClustered ON dbo.v1111(field1, field2, field3, field4)

GO

Thanks,
Bhakti Shah

Can you control concatenation of string values with null values? How ?

We can control this by setting CONCAT_NULL_YIELDS_NULL on or off.
When it is set to ON, the concatenation will result null and when it is set to OFF, it will result the string.
Setting it ON,

SET CONCAT_NULL_YIELDS_NULL on;

print 'testing' + NULL; --results NULL and prints nothing

Setting it OFF,

SET CONCAT_NULL_YIELDS_NULL off;

print 'testing' + NULL; -- results testing and prints the same

print sign(0) -- prints 0

print sign(10) -- prints 1

print sign(-10) -- prints -1

Sometimes before using SIGN(), you need to confirm you are passing numeric value as argument.
You can do this using, ISNUMERIC()

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.