Some Useful SQL Queries for Software Testers

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.