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