:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

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

  • Jan 23 / 2009
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

T-SQL script to list all languages and their IDs in SQL Server 2005 and SQL Server 2008

A DBA may be required LanguageID to query in system catalog and resources. For example in SQL Server 2000, to get complete default list of SQL Server error number, severity level and error description you have to use following command

/*
Get default list of SQL Server error numbers
and descriptions in SQL Server 2000
*/

USE Master
SELECT * FROM SysMessages
GO

While to get same list in SQL Server 2005 or 2008, you have to modify the command as

/*
Get default list of SQL Server error numbers
and descriptions in SQL Server 2005 and SQL Server 2008
*/

USE Master
SELECT * FROM SysMessages
WHERE MsgLangId = 1033
GO

So we have to provide the language ID in some cases. To get list of languages along with ID use following script

/*
T-SQL Script to list all languages
and their IDs in SQL Server 2005 and SQL Server 2008
*/

USE Master
SELECT MsgLangId, Alias
FROM Sys.SysLanguages
GO

LanguageList
You may further choose use full parameters from Sys.Languages table.

Consult us to explore the Databases. Contact us