:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • 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 10 / 2009
  • 0
dbDigger, Null Values, T-SQL Tips and Tricks, User Defined Functions UDF

Use ISNULL to replace NULL value with alternate

It may be required to change NULL value of a column with a valid alternate value. This scenario is mostly there for report generation purpose. T-SQL provide a very handy function ISNULL() to replace NULL value with alternate value. Following is syntax for using ISNULL()
ISNULL(ColumntoCheckForNULL, AlternateValue)
ISNULL accepts two parameters. First parameter will always be the column for which alternate is required in case of its NULL value. Second parameter is the alternate value that is required to replace the NULL value. Second value may be any other column or other value. Alternate value may be implicitly converted to data type of first parameter. For example you may provide datetime as alternate value for character family data type but you can not provide character family data type as alternate of date time value.
For example if we are required to display City as alternate of NULL value of AddressLine2

 USE AdventureWorks  
 GO  
 SELECT AddressLine1, ISNULL(AddressLine2,city), PostalCode  
 FROM Person.address  

Similarly to display a string of characters as alternate of NULL AddressLine2

 USE AdventureWorks  
 GO  
 SELECT AddressLine1, ISNULL(AddressLine2,'No Address Line 2'), PostalCode  
 FROM Person.address  

This user defined function would be very helpful while working with string concatenations. If one of the strings being concatenated is NULL then end result of concatenation would be NULL. To avoid this situation use ISNULL where NULL value is expected.Just consider following two examples for use and effect of ISNULL in string concatenation.

 
-- String concatenation with NULL value  
 SELECT 'Value1 '+'Value2 '+NULL AS ConcatenatedString  
 GO  
 -- String concatenation by using ISNULL  
 SELECT 'Value1 '+'Value2 '+isnull(NULL, 'NULL-Replaced') AS ConcatenatedString  
 GO  
  • Mar 30 / 2009
  • 1
DBCC Commands, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks, Transaction log files

How to truncate the transactional log

In one of previous posts about Dealing the growth of Transactional Log Files i discussed briefly about function of transactional log files in SQL Server and also there were some prevention measures to prevent growth of transactional log file on large amount.
There may be situation that we have to get rid of grown file. In that case obvious work around is to truncate the transactional log. To truncate the transaction log for AdventureWorks database

ALTER DATABASE AdvetureWorks  
 SET recovery SIMPLE  
 GO  
 DBCC shrinkfile (LogFileNameofAdventureWorks)  
 GO  
 ALTER DATABASE AdventureWorks  
 SET recovery FULL  
 GO  

As in case of simple recovery mode SQL Server transaction log is not maintained for that database. So the transactional log will be dropped for database when recovery model is set to simple.
It is important to mention that on going transactions should be considered before changing recovery models.

  • Mar 26 / 2009
  • 2
dbDigger, Monitoring and Analysis, T-SQL Scripts, T-SQL Tips and Tricks

Get last modification date of a table in SQL Srever database

Some times i am required to get last modification date of some of my tables. This information is not there either in SSMS or through famous sp_help command. Before mentioning the example to get last modification date for a table it is better to mention that SQL Server 2000 does not keeps track of any such information. So you may get last modification date of a table in SQL Server 2005 and onwards. So to get last modification date of table in AdventureWorks

 
Use AdventureWorks  
 GO  
 SELECT Name, Modify_date  
 FROM sys.tables  
 WHERE name = 'ProductPhoto'  
 GO  

It is important to understand that modification date means modification in structure or properties of a table. Any operation on data of table does not have any link to modification date of that table that is being retrieved through this script. And to get this information for all of your tables in a database, just remove the where clause.

  • Mar 19 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Using integers in order by clause of T-SQL

Column names are commonly used in order by clause of any select statement in T-SQL. For example a simple select statement in AdventureWorks database using order by clause is as follows.

 
USE AdventureWorks  
 GO  
 SELECT name, groupName, modifieddate  
 FROM humanresources.department  
 ORDER BY Groupname  
 GO  

Result is obviously three columns ordered by [GroupName]. Let us use another more simple approach while using order by clause. Instead of using column name, we may bring order to our results by using integer in order by clause. For example ordering our result set through integer, we have following way.

 
USE AdventureWorks  
 GO  
 SELECT name, groupName, modifieddate  
 FROM humanresources.department  
 ORDER BY 2  
 GO  

Result of both of our scripts is in same order. SQL Server starts count of columns used in select list by 1 and increments by 1. In case of above scripts used, as we have three columns in select list, so we may use integer value from 1 to 3 for first, second and third column respectively. Value provided may not be less than 1. And also it may not be greater than number of columns used in select list. As in our case maximum value is 3.
This syntax of order by using integers may be used in all versions of SQL Server. It is simple than using full column names. Changing the order of columns in select list will also change the criteria being used in order by clause.

Short Comings:

A short coming is there in this approach. By using integer in order by clause you can not order by any column that is not in select list. While by using column names in order list you can order even by the column that is not in select list.

Consult us to explore the Databases. Contact us