:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

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

  • Feb 19 / 2009
  • 0
dbDigger, System Functions, T-SQL Tips and Tricks

get Database ID and Database Name without any join to sys.databases

Some times it is required to get database ID to be used in a script. Or it is required to get database name from database ID. One approach is to make a join with sys.database. But i want to point out a very handy function for these tasks.

To get database ID from database name use following command

SELECT DB_ID(‘AdventureWorks’)
GO

And similarly to get database name from database ID use following command

SELECT DB_NAME(5)
GO

So these make the tasks easy.

  • Feb 07 / 2009
  • 0
dbDigger, SET Options, T-SQL Tips and Tricks

Return only meta data through Set FMTONLY

Recently i read an interesting question on SQLServerCentral.com, with main point that what function we can use to get just meta data instead of associated data from a T-SQL statement. It is interesting. After submitting my reply (which was accidentally correct), i read the provided BOL link and came to know that SET FMTONLY on or Off, we may getjust meta data instead of associated data through a T-SQL command. For example consider the impact of using this function

 
Use AdventureWorks  
 GO   
 SET FMTONLY ON  
 GO   
 SELECT * FROM production.Document  
 GO  
 SET FMTONLY off   
 GO  

SET FMTONLY ON
And instead of retreiving whole data, i just got name of columns from my script. You may use it on SQL Srever 2000, SQL Server 2005 and SQL Server 2008. You require membership of Public role to use this function.

  • Jan 29 / 2009
  • 0
dbDigger, Derived Tables, T-SQL Enhancements, T-SQL Tips and Tricks

Consider use of derived tables instead of temporary tables

Derived tables are SELECT statements that act as tables in the FROM clause. In various scenarios these can provide better performance than temporary tables. Consider following piece of script where i have used derived table

 
USE AdventureWorks  
 GO  
 SELECT DISTINCT s.PurchaseOrderNumber  
 FROM Sales.SalesOrderHeader s INNER JOIN  
 ( SELECT SalesOrderID  
 FROM Sales.SalesOrderDetail  
 ) d  
 ON s.SalesOrderID = d.SalesOrderID  

commands following the JOIN statement are working as derived table.

  • Jan 29 / 2009
  • 0
dbDigger, Having Clause, T-SQL Tips and Tricks

Using HAVING clause in T-SQL scripts

Having clause i used to filter the results produced by GROUP BY clause. WHERE clause filters the result before selection while HAVING clause is used to filter the results produced by GROUP BY clause. That is the reason that WHERE clause is always used before GROUP BY clause and HAVING clause is used after GROUP BY clause. Here is an example to use HAVING clause for filtration of GROUP BY results.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY OrderDate  
 Having Sum(TotalDue) <>  

ResultOfHaving
In result you may notice that HAVING clause filtered on result produced through GROUP BY clause and results qualified the condition in HAVING clause.

  • Jan 29 / 2009
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Tips and Tricks

Using GROUP BY and GROUP BY ALL in T-SQL scripts

As we know that GROUP BY clause is used to summarize the given columns in select statement. Example of using GROUP BY in AdventureWorks database is as follows. In this example we will calculate the total amount due of each date between July 01 2001 to July 31 2001.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY OrderDate  

As a result 31 rows are returned in following format.

ResultOfGroupBy

Now If there is a situation that we have to include all row values in result, even if these do not qualify the WHERE clause, then we have to use GROUP BY ALL. Running above script with GROUP BY ALL provides different result set.

 
USE AdventureWorks  
 GO  
 SELECT OrderDate,SUM(TotalDue) TotalDueByOrderDate  
 FROM Sales.SalesOrderHeader  
 WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'  
 GROUP BY ALL OrderDate  

ResultOfGroupByAll
Now we get 1124 rows. Even the rows that do not qualify the where clause have been returned but not included in calculation.

Consult us to explore the Databases. Contact us