SQL with Manoj

 
So few days back I got a ping from one of my reader, he was asked one question in a SQL Interview and he had a hard time to answer that:

How do you return every Nth row from Table or a result set?

He told he knew how to get top 2nd or top Nth record from a table, but was not able to come up with the logic for this problem.
 

I told him this can be done easily done by using Modulus “%” (percentage) operator.
 

–> Below is the simple logic by using Modulus logic to get consecutive 5th position in the below record-set. Below every row with “0” value (highlighted yellow) is the 5th consecutive position:

select BusinessEntityID % 5 AS [5thPosition], * 
from [Person].[Person]

–> And by simply moving the above Modulus logic to the WHERE clause will give to filtered rows:

select * 
from [Person].[Person]
where BusinessEntityID % 5 = 0

 
This is one of a typical Interview Question you might face while going for an opening of a SQL Developer. This looks very simple question but its also an important question to judge a person if he/she knows SQL basics and has really worked in SQL Server or not.

–> So, here are following ways you can query the SQL Server metadata and system tables to get list of all tables in a particular database, by using:

1. sys.tables

select * from sys.tables

2. sys.objects with filter of type = ‘U’

select * from sys.objects where type = 'U'

3. sys.sysobjects with filter of type = ‘U’

select * from sys.sysobjects where type = 'U'

4. INFORMATION_SCHEMA.TABLES

select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'TABLE'

5. sp_msforeachtable undocumented function

exec sp_msforeachtable 'print ''?'''

Out of all these the best option is to use the 4th one, i.e. INFORMATION_SCHEMA.TABLES views, as Microsoft itself suggest to use it instead of first three & the 5th option.