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

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 the purpose of ROLLUP function in SQL Server?

Roll Up is an SQL Server function that helps to perform aggregate operation on multiple levels in hierarchy.This on the other hand helps us to generate very handy reports.

Let us create a table with the below data

DECLARE @t TABLE([User Name] VARCHAR(50),[Access Date] DATE)



INSERT INTO @t VALUES

('Niladri','12/29/1996'),

('Arina','11/29/1986'),

('Niladri','12/29/2006'),

('Debasis','11/11/2005'),

('Arina','1/1/2013'),

('Rajlashmi','5/1/2013'),

('Rajlashmi','5/2/2013'),

('Rajlashmi','5/3/2013')



SELECT * FROM @t

Now , we need to generate a report as "How many users used till today and also the total sum of all".

For addressing the same let us fire the below query

SELECT 

		[User Name] = CASE WHEN GROUPING([User Name]) = 1 THEN 'Total:'

		ELSE [User Name] END

		,[Count] = COUNT ([User Name])

FROM @t

GROUP BY [User Name] WITH ROLLUP

As can be figure out that, we have count for the users at every individual levels as well as the total count.ROLLUP adds new row for each column used in GROUP BY clause.

What is a Super Key ?

Super Key is a key which is used to define uniqueness of a row with the collection of more than one column

Eg:- There is Customer Table

The Primary Key will be CustomerID field but this cannot be termed as a Super key
.
To be a Super key we can club 2 columns such as
CustomerID and LastName of the Customer.

If we see the above eg over here we have club 2 columns and uniquely identified a ROW

Database normalization is process of

NOTE: This is objective type question, Please click question title for correct answer.

What are some of the advantages of using SPARSE column?

* Storing a null in a sparse column takes up no space at all.

* To any external application the column will behave the same

* Sparse columns work really well with filtered indexes as you will only
want to create an index to deal with the non-empty attributes in the column.

* We can create a column set over the sparse columns that returns an xml
clip of all of the non-null data from columns covered by the set. The
column set behaves like a column itself. Note: you can only have one
column set per table.

* Change Data Capture and Transactional replication both work, but not the
column sets feature.

What are some of the disadvantages of using SPARSE column?

* If a sparse column has data in it, it will take 4 more bytes than a normal
column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique
identifier rises form 16 bytes to 20 bytes.

* Not all data type can be sparse: text, ntext, image, timestamp, user-defined
data type, geometry, or geography or varbinray (max) with the FILESTREAM
attribute cannot be sparse. (Changed17/5/2009 thanks Alex for spotting the
typo)

* computed columns can't be sparse (although sparse columns can take part in a
calculation in another computed column)

* We can't apply rules or have default values.

* Sparse columns cannot form part of a clustered index. If we need to
do that use a computed column based on the sparse column and create the
clustered index on that (which sort of defeats the object).

* Merge replication doesn't work.

* Data compression doesn't work.