:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Apr 29 / 2009
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Interview Questions

Choosing between UNION and UNION ALL

When using the UNION statement, the fact should be considered that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like record sets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final record set.
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows.
On the other hand, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement.

Advantage of UNION ALL

The advantage of the UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being using. The better performance of UNION ALL in suitable cases can be verified through execution plans.

  • Apr 27 / 2009
  • 2
Constraints and Keys, DBA Interview questions, dbDigger, T-SQL Interview Questions

How to update associated foreign keys when primary key specification is changed

I had to change a auto generated primary key with some meaningful key value. After designing the meaningful key it was now time to consider the impacts on foreign keys related to that specific primary key. I planned to first script all related keys for re creation after the key change operation.
In test environment i just changed the primary key through SSMS to see the impact and surprisingly SSMS asked my permission to change the related foreign keys also. I was glad and task was successfully completed with some clicks only.
To go on with an example let us choose some keys in AdventureWorks
Check primary key to be changed

USE AdventureWorks
GO

— Check Specifications of primary key to be changed
sp_help [HumanResources.Department]
GO

Specification of Primary key to be changed

We can confirm that a primary key [DepartmentID] exists in the table. Data type of [DepartmentID] is smallint. Also we may confirm that this primary key is referenced as a foreign key in table [HumanResources.EmployeeDepartmentHistory].
Now i have to update the primary key [DepartmentID] in HumanResources.Department. For this purpose

  • right click on table
  • go to design
  • In design window change data type of [DepartmentID] from smallInt to Int
  • Just as you click out of modified data type cell, you will be facing following confirmation dialog

Confirmation about data type change

Dialog wants your permission to change data types of associated foreign keys. If you click yes data types of associated foreign keys will be changed and relations will be preserved with new data type. So just save the design changes and get your changes implemented. If you click no data type of primary key will not be changed.
Now confirm the foreign key data type change

USE AdventureWorks
GO

— Check data type change in foreign key
sp_help [HumanResources.EmployeeDepartmentHistory]

In the result we may confirm the change in data type of foreign key in [HumanResources.EmployeeDepartmentHistory].
So you do not have to plan any stretigies for synchronnizing the changes made in any foreign key. SQL Server Management Studio (SSMS) designer will work for you.

  • Apr 23 / 2009
  • 0
dbDigger, System Functions, T-SQL Interview Questions, T-SQL Scripts

Select random records from a table by using newID()

Normally SELECT TOP 1000 type approach is used while retrieving some sample data from a table with large number of records. But records retrieved through this approach are not from all over the table, so they can not be considered a random sample of data from that table. A simple approach to get sample data from all over the table will be to use newID() in following syntax

USE AdventureWorks
GO

SELECT TOP 100 FirstName, LastName, EmailAddress, Phone
FROM Person.Contact
ORDER BY NEWID()
GO

Above script will generate a random sample of data from Person.Contact table. You may also use where clause to further filter the records if required.
Also i have noted that using order by NewID() costs same as using order by for any other column. However the difference is there in case of clustered index. Order by any clustered index column is more economical than order by NewID().
Above was one of many uses of NewID(). We will look at other uses of NewID in some coming posts later.

  • Apr 15 / 2009
  • 0
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Tips and Tricks

Use EXCEPT to select values that does not exist in other table

EXCEPT operation is introduced in SQL Server 2005 and onwards. It is used to rows from left side query that are not present in right side query. So, It may be used to get rows that are present in one table but not in other.
Consider a case that we want to select rows containing name, designation, department from table name staff which are not present in table name Officers.

 
SELECT nameofStaff, designationofStaff, departmentofStaff  
 FROM staff  
 EXCEPT  
 SELECT nameofOfficer, designationofOfficer, departmentofOfficer  
 FROM Officers  

Above script will provide us distinct records that are there in Staff table but are not present in Officers table. Number of columns should be same on both sides of EXCEPT. And data types of columns should also match.
For more details please visit BOL

  • Apr 15 / 2009
  • 1
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Use INSTERSECT to select common values between two tables

INTERSECT operation is introduced in SQL Server 2005 and onwards. It is used to get common rows between both sides in distinct form. So, It may be used to get common rows between two tables.
Consider a case that we want to select common rows containing name, designation, department from two tables named staff, officers’
In this scenario we can use the INTERSECT operands in following way

SELECT nameofStaff, designationofStaff, departmentofStaff  
 FROM staff  
 INTERSECT  
 SELECT nameofOfficer, designationofOfficer, departmentofOfficer  
 FROM Officers  

Above script will provide us distinct rows that are common on given columns. Number of columns should be same on both sides of INTERSECT. And data types of columns should also match. For more details please visit BOL

  • Apr 09 / 2009
  • 0
Case Statement, dbDigger, Null Values, T-SQL Interview Questions

None of the result expressions in a CASE specification can be NULL.

The error message
Msg 8133, Level 16, State 1, Line 2
None of the result expressions in a CASE specification can be NULL.
is a run time error message and may be generated at all versions of SQL Srever. The said error message is generated when one result expression in a CASE expression is NULL. To remove the error message, analyze your case statement and modify it to make sure that no expression is NULL.

Consult us to explore the Databases. Contact us