Tricky SQL Questions

Introduction

This tip presents few SQL Tricky Questions and Answers which will be useful for your development as well as Interviews.

Using the Code

1) How to insert into a table with just one IDENTITY column?

Answer

INSERT INTO tbl_ID DEFAULT VALUES;

2) How to concatenate multiple rows into a single text string without using loop?

Suppose we have a table with the following data:

ClientID    ClientName
3           Sowrabh Malhotra
4           Saji Mon
6           Sajith Kumar
7           Vipin Job
8           Monoj Kumar

We need to concatenate the ClientName column like the following:

Sowrabh Malhotra, Saji Mon, Sajith Kumar, Vipin Job, Monoj Kumar

Answer

Method 1

SELECT ClientName + ', ' 
From ClientMaster 
For XML PATH('')

Method 2

DECLARE @ClientNames VARCHAR(MAX);
SET @ClientNames = '';

SELECT @ClientNames = @ClientNames + IsNull(ClientName + ', ', '')
FROM ClientMaster 
Select @ClientNames

3) How to use ORDER BY in VIEWS

Create View vClientMaster
As
Select TOP 100 PERCENT ClientID, ClientName FROM ClientMaster Order BY ClientID  DESC 

But in the above example, the SQL server will not consider the [TOP 100 PERCENT] statement when executing the query. So we will not get the result in the order described in the view. But if we specify any number less than 100 PERCENT, SQL server will sort the result.

Note: It is not advisable to use ORDER BY in VIEWS. Use order by outside the view like the following:

Select ClientID, ClientName FROM vClientMaster Order BY ClientID  DESC 

4) How to create a UNIQUE Key on a Column which is having multiple NULL values?

In SQL Server, Unique Key Constraint will allow only one NULL value. But there are situations that are more than one null value in the column, but still have to maintain Uniqueness by ignoring all null values.

Answer

Method 1

Only works on SQL Server 2008 and above

Using Filter Index. Filtered index is used to Index a portion of rows in a table. While creating an index, we can specify conditional statements. The below SQL Query will create a Unique Index on the rows having non null values:

CREATE UNIQUE INDEX IX_ClientMaster_ClientCode ON ClientMaster(ClientCode)
WHERE ClientCode IS NOT NULL

Method 2

Create a view having the unique fields and create a Unique Clustered Index on it:

Create View vClientMaster_forIndex
With SchemaBinding
As
Select ClientCode From dbo.ClientMaster Where ClientCode IS NOT NULL;
Go

CREATE Unique Clustered Index UK_vClientMaster_ForIndex
on vClientMaster_forIndex(ClientCode)

Method 3

Create a Computed Column like the following and create a UNIQUE KEY on that:

[CMP_ClientCode]  AS (case when [ClientCode] IS NULL _
then CONVERT([varchar](10),[ClientID],0) else [ClientCode] end)

4) What is the default size of SQL Server Database

Answer

The default size of user database is based on system database model. When a user creates a new database, SQL server will take the copy of system model database and add user specified settings on it and create new database.

References

• blog.sqlauthority.com