Some Useful SQL Queries for Software Testers

Article
09/10/2009

Syed Aslam Basha here. I am a tester on the Information Security Tools team.

As a tester, apart from UI testing I test DB for integrity. Our boss is encouraging us to share tips that save us time on the team so here are a few of mine. In this blog post I want to highlight some SQL queries I use all the time.

Query to select usernames where the username is duplicate

    1: Select distinct t1.username from tooluser t1 where (Select Count(t2.username) from tooluser t2 Where t1.username= t2.username ) > 1

Query to delete a single row from a table which has duplicate data
- ```
    1: delete top 1 from tooluser where id=1 
```

Query to check Number of Partitions in the given fact table

    1: select * from sys.partitions where object_id=(select object_id from sys.tables where name='FactFWProxy') 
    2: select * from sys.partition_range_values;

Find out common records in tables A and B which are in different databases

    1: declare @A table(username varchar(50))
    2: declare @B table(username varchar(50)) 
    3:  
    4: insert into @A
    5: select distinct a.UserName from suatest.dbo.UR a 
    6: insert into @B
    7: select distinct b.UserAlias from sua.dbo.suauser b 
    8:  
    9: select a.username, b.username from @A a
   10: left join @B b on a.username = b.username

Find out version number of SQL
```
    1: select @@version
```

Convert datatime into mm/dd/yyyy format

    1: CONVERT(datetime,'2007-03-03',101)

Display definition of SP using command
```
    1: exec sp_helptext AddCategory
```
Truncating load test results tables

    1: truncate table LoadTestBrowsers
    2: truncate table LoadTestCase
    3: truncate table LoadTestMessage
    4: truncate table LoadTestNetworks
    5: truncate table LoadTestPageDetail
    6: truncate table LoadTestPageSummaryByNetwork
    7: truncate table LoadTestPageSummaryData
    8: truncate table LoadTestPerformanceCounter
    9: truncate table LoadTestPerformanceCounterCategory
   10: truncate table LoadTestPerformanceCounterInstance
   11: truncate table LoadTestPerformanceCounterSample
   12: truncate table LoadTestRun
   13: truncate table LoadTestRunInterval
   14: truncate table LoadTestScenario
   15: truncate table LoadTestSqlTrace
   16: truncate table LoadTestTestDetail
   17: truncate table LoadTestTestSummaryData
   18: truncate table LoadTestThresholdMessage
   19: truncate table LoadTestTransactionDetail
   20: truncate table LoadTestTransactionSummaryData
   21: truncate table WebLoadTestErrorDetail
   22: truncate table WebLoadTestRequestMap
   23: truncate table WebLoadTestTransaction
   24: truncate table LoadTestRunAgent

Modify the above script parameters appropriately to use it successfully!

- Syed

Some Useful SQL Queries for Software Testers

Additional resources