Tuesday, 25 April 2017

How Use sp_MSForEachTable SP for Tables Row Count

Easiest way to get row count of every table in a database. You can use sp_MSForEachTable
SP

CREATE TABLE #Tablescounts

(table_name varchar(255),row_count int)

 

EXEC sp_MSForEachTable @command1 = 'INSERT #Tablescounts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

 

SELECT * FROM #Tablescounts


SQL Pivot Tricky Query Example Asked in TCS

1). We have a table(#Temp1) contain RollNumber, Subject, Marks and want output as below, Write query for same.(pivot) asked in TCS
 

SELECT * FROM (SELECT * FROM(SELECT RN,[Subject],Marks from #Temp1)a

PIVOT(MAX(Marks) for [Subject] in ([Math],[Hindi],[English]))E) K

CROSS APPLY (SELECT MAX(Marks) MaxMarks FROM #temp1 WHERE RN = K.RN) S

 



2). Now write query which will show Subject as well like below .(pivot)
 
SELECT * FROM (

SELECT * FROM( SELECT RN,[Subject],Marks from #temp1)a

PIVOT (MAX(marks) for [Subject] in ([Math],[Hindi],[English]))E) K

CROSS APPLY (

SELECT CONCAT(Marks,('-'+[Subject])) MaxMarks_Subject FROM (

SELECT ROW_nUMBER() OVER(PArtition by rn order by Marks DESC) RowNum,Marks,Subject FROM #temp1 Where RN= K.RN)

J WHERE J.RowNum = 1

) S

Wednesday, 5 April 2017

Tuesday, 4 April 2017

Explain what is AutoScaling?

Scaling by adding additional instances is often referred to as scaling out. Windows Azure also supports scaling up by using larger role instances instead of more role instances.
By adding and removing role instances to your Windows Azure application while it is running, you can balance the performance of the application against its running costs.
An auto-scaling solution reduces the amount of manual work involved in dynamically scaling an application.

What is the difference between Web and Worker Roles in Windows Azure?

As you know the main difference between the two is that an instance of a web role runs IIS, while an instance of a worker role does not. Both are managed in the same way, however, and it’s common for an application to use both.For example, a web role instance might accept requests from users, then pass them to a worker role instance for processing.

Explain what is Azure Blob?

Azure Blob storage is a service for storing large amounts of unstructured object data, such as text or binary data, that can be accessed from anywhere in the world via HTTP or HTTPS. You can use Blob storage to expose data publicly to the world, or to store application data privately.

What is the difference between Windows Azure and Azure SQL database?

Windows Azure  is the cloud based premium service provided by Microsoft where we can able to host our application and make it available globally.

Windows Azure SQL Database is the another Azure service provided by Microsoft where the SQL DB engine is available in the Azure i mean the cloud. in a simple way, cloud DB based on SQL server engine.