:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Oct 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Using parameter for rows with TOP in SELECT statement

T-SQL allows us to retrieve a specific number of rows by using TOP operator in SELECT statement. Following statement would retrieve top 10 rows containing name and number from PRODUCT table.

SELECT TOP 10 Name, ProductNumber  
 FROM [AdventureWorks].[Production].[Product]  
 GO  

Of course you may filter the rows by using WHERE clause and other ways as used in normal select statement. It is fine till here, until you are required to change the provided number (10 in above statement) with some dynamic parameter with TOP clause. Trying following code would generate error

 
DECLARE @rows int  
 SET @rows = 13  
 SELECT TOP @rows Name, ProductNumber  
 FROM Production.Product  
 GO  

Solution:

Solution to this problem is simple. Just enclose the parameter in SELECT statement with in a bracket. So our working code would be some thing like

 DECLARE @rows int  
 SET @rows = 13  
 SELECT TOP(@rows) Name, ProductNumber  
 FROM Production.Product   
 GO  

You may use this syntax in functions, procedures or scripts when required to use Using parameter for rows with TOP in SELECT statement. I have test this syntax on SQL Server 2005 and SQL Server 2008 instances and it worked well. May be it would not work on SQL Server 2000 (i read but not tested).

  • Aug 24 / 2010
  • 0
Data Types, dbDigger, T-SQL Interview Questions

Purpose of using Varchar(max)

varchar and nvarchar may store maximum bytes up to 8000 and 4000 respectively. In SQL Server 2005 and onwards we have another parameter max for varchar or nvarchar as field length. It can be used as varchar(max) or nvarchar(max).
Actually it is better version of text, ntext and image data types of SQL Server 2000. It enables the DBMS to store about 2 GB of data in a comparable data type. You can hold this value in a variable and also can apply comparison and manipulation functions on this huge amount of data.
There were a lot of limitations while using text, ntext and image data types. These values do not allow to use several of string functions. But also be aware of the fact that this tremendous power of using such large data as a string may eat your resources. So use this power with care.>
So you should be using varchar(n) or nvarchar(n) for bytes up to 8000 or 4000 respectively. And for larger text options consider using max as varchar(max) or nvarchar(max).

  • May 07 / 2010
  • 0
Data Modeling and Database Design, dbDigger, T-SQL Interview Questions

Is it possible to have NULL value in foreign key?

It is interesting question that either a foreign key column can have NULL value or not? The answer is Yes. A foreign key column may have NULL value. It simple means that you can record with NULL value in foreign key column is not associated with any record of primary key (being referenced) table.
It may easily be bound to have no NULL value in foreign key column. Simply alter it with NOT NULL constraint and it would prevent NULL value like any other column.

  • Apr 30 / 2010
  • 0
dbDigger, String Manipulations, T-SQL Interview Questions, T-SQL Tips and Tricks

T-SQL String concatenation

If you are going to google the terms like ‘T-SQL concatenate’ to view the syntax of assumed concatenate or concate built-in function, then there is no need to do it. T-SQL allows you to concatenate two or more strings by just using + sign among strings.
To experience, use following code line in SSMS

print 'string1 '+'concatenated to '+'string2'

It would provide following result

SSMS result for concat sign

  • Feb 01 / 2010
  • 0
Data Modeling and Database Design, dbDigger, Identity Columns, T-SQL Interview Questions, T-SQL Tips and Tricks

Insert value in identity column when identity column is only column in table

I came across a question that how to insert value in identity column when identity column is only column in the table. And to set identity insert on is not allowed. It is interesting scenario. So answer is to use DEFAULT VALUES. Consider following code for demo that how to insert value in identity column when identity column is only column in table.

USE AdventureWorks  
GO  
CREATE TABLE #TestInsert(onlyColum TINYINT IDENTITY (1,1))  
GO  

INSERT INTO #TestInsert DEFAULT VALUES 
GO  

SELECT * FROM #TestInsert  
GO 

And you would get inserted value from this temporary table.

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Finding Nth highest value in a column through T-SQL

Here is a situation, to find a specific highest value in a column. Most common example may be that of a salary column where one may need 7th highest salary.
To make short of this short problem i have selected pubs..Sales.qty column to work with.
Apply following query and you will get total 11 distinct values arranged

 
USE Pubs  
 GO  
 SELECT DISTINCT qty  
 FROM sales  
 ORDER by qty DESC  
 GO  

Now suppose we have to find 7th highest value in qty column. The simple query for this is

 SELECT min(qty)  
 FROM sales  
 WHERE qty IN  
 (SELECT distinct TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC)  
 GO  

So its done.
It is appropriate to mention here another query that i found on a blog.

 
SELECT TOP 1 qty  
 FROM ( SELECT DISTINCT TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC) ABC  
 ORDER BY qty ASC  
 GO  

So in above queries you may find nth highest value where n is always greater than 1.
That is all for finding nth highest value in a column.

Consult us to explore the Databases. Contact us