:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Jan 07 / 2010
  • 0
dbDigger, Null Values, T-SQL Interview Questions

Using order by with nulls in T-SQL

You have a table with EmpName and DateOfLeaving columns.
The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company.
The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group.
Consider the Example:

Table: Employees

EmpName DateOfLeaving

Abc 10 Oct 1999

Bcd 11 Nov 1998

Ccd null

Dcd 10 Aug 2000

Eed null

The solution should be:

EmpName DateOfLeaving

Bcd 11 Nov 1998

Abc 10 Oct 1999

Dcd 10 Aug 2000

Ccd null

Eed null

Which query will produce this output?

Answer:

  • Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,’10/10/9999’),EmpName asc
  • Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc

Explanation:
In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
DBA Interview questions, dbDigger, SQL Server Agent scheduled Jobs, T-SQL Interview Questions

SQLAgent status through T-SQL

Question:

Using Transact-SQL, how can you find out whether SQLAgent is running?

Answer:

  • sysprocesses table for program_name like SQLAgent%
  • Use xp_servicecontrol querystate SQLSERVERAGENT

Explanation: Querying the sysprocesses table will return a row if sql agent is running, else it will not return any rows. The xp_servicecontrol procedure will return the status of the service (running/stopped) on 2005 / 2008 editions except express. On a SQL Server Express instance this command will return an error. So you need to check for errors as well as the status to determine whether the agent is running.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

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

union for different data types in T-SQL

What happens with this code?

select ‘B’

union

select 4

Answer: The varchar is converted in int (error results)

Explanation: This is an example of an implicit conversion in T-SQL. In this case, an error is returned as the varchar is converted to an int, or an attempt is made, which returns an error. The int is of higher precedence than a varchar, so that is the order of conversions.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

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

Call it ad-hoc or out of box thinking

Although is does not always work but, some times ad-hoc thinking makes the solution very simple. Consider the following scenario for which a very simplified solution is provided for. We may call it out of box thinking as well.

Question:
Is there a condition for the WHERE clause to display the details about the students born in the month numbers of 1[Jan], 3,4,5,7,8? The condition should not use both IN and OR operators.

Answer: Yes

Explanation: We can give the condition as follows:
where datename(m,dob) not like ‘%e%’
because these month(s) specified do not have the letter ‘e’, but all the other months have at least one ‘e’ in month name.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Find columns with identity property

Question:

In SQL Server 2005, how can you easily determine which columns have the identity property set?

Answer:

  • Query sys.identity_columns for the rows.
  • Query sys.columns.is_identity for a value of 1

Explanation: There is a table, sys.identity_columns that contains a row for each column in your database that has the identity property set. There is also a column called is_identity in the sys.columns view that contains a 1 if the column has the identity property set.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

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

Do not run until answer

Question:
What do you expect the result of the following query to be? No cheating, don’t run until you’ve answered!

 WITH DATA (Numbers) AS  
 (SELECT NULL UNION ALL  
 SELECT NULL UNION ALL  
 SELECT NULL UNION ALL  
 SELECT 1 UNION ALL  
 SELECT 2 UNION  
 SELECT 3)  
 SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA  
 WHERE Numbers IS NULL  

The final UNION negates the duplicates selected with the previous UNION ALL statements. The COUNT(ALL expression) evaluates the expression for each row in a group and returns the number of nonnull values. ALL Applies the aggregate function to all values. ALL is the default.For investigation, change the final UNION to UNION ALL, and the SELECT COUNT (ALL Numbers) to COUNT (*)


Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Consult us to explore the Databases. Contact us