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