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
