Some Useful SQL Queries for Software Testers

Article
09/10/2009

Syed Aslam Basha here from the Information Security Tools team.

In this blog post I want to highlight some useful SQL queries:

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 Aslam Basha ( syedab@microsoft.com )

Microsoft Information Security Tools (IST) Team

Test Lead

---------------------------------------------------------

Please leave a comment if the blog post has helped you.

Some Useful SQL Queries for Software Testers

Additional resources