:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

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

Consult us to explore the Databases. Contact us