:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Apr 26 / 2011
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

T-SQL wild card characters for use with like operator

SQL Server provides four wild card characters to use with like operator. These are % , _ , [], [^]. These wild card characters provide very powerful capabilities to operate on string data. At morning i was required to operate on a column with strings containing digits. There were few values where alphabets were also in the value. I was required to separate the values which also contain  alphabets along with digits.
This can be done by using above mentioned wild card characters in T-SQL. Following is full example

 -- Create Table in Specified database  
 IF EXISTS (SELECT * FROM sys.objects  
 WHERE name = N'StringsTest' AND [type] = 'U')  
 DROP TABLE StringsTest  
 CREATE TABLE StringsTest (StringValue varchar(230))  
 GO  
 -- Populate the table  
 INSERT INTO StringsTest  
 SELECT '120-35-AQS' UNION ALL  
 SELECT '180-35-ASP' UNION ALL  
 SELECT '15335'    UNION ALL  
 SELECT '158-895'  UNION ALL  
 SELECT '158895'    UNION ALL  
 SELECT 'ASDBFC'    UNION ALL    
 SELECT 'ACVFRHB'  UNION ALL    
 SELECT 'ABC-158-35'    
 GO  
 -- To get values where alphabets are present  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%[A-Z]%'  
 -- To get values where digits are present  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%[^A-Z]%'  
 -- To get values where Q,R or S follows -A  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%-_[Q-S]%'  

In the same way you can build many more regular expressions as required in a specific scenario.

  • 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.

Consult us to explore the Databases. Contact us