What is use of "SET NOCOUNT" in SQL?

Ø  When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.

Ø  The setting of SET NOCOUNT is set at execute or run time and not at parse time.

Ø  SET NOCOUNT ON improves stored procedure (SP) performance.

Ø  Syntax: SET NOCOUNT { ON | OFF }

Example of SET NOCOUNT ON:

Example of SET NOCOUNT OFF:

Why F12 key opens "Object Browser Window" instead actual Code Definition?

Problem:

Before some days i faced wired problem while working with my project. In my project solution i made different project for data access layer and used data access layer reference to another project so when i pressed F12 key or “Go To Definition” then i redirect to “Object Browser Window” instead actual code definition.

Solution:

After googling, i come to know that it was happening due to “mismatch framework version of different projects”. Firstly my entire project was in framework 3.5 but after some reason i need to update my one project version to 4.0 and i remained my data access layer as it is 3.5. So due to mismatch version it opens object browser window.

How to know framework version of windows application and how to change it?

I just needed to upgrade by framework version of my windows application project due to use of some advance functionality which is not supported by lower framework version. That’s why i am going to share it in blog that how to know framework version of application and upgrade it.

Follow following steps for vb application; it may vary some for c# application:

1) Right click on project that you want to know framework version.

2) Click on “Properties” or press “ALT+ENTER”.

3) Now click on “Compile” tab from tree view.

4) Click on “Advance Compile Options..” appear at bottom.

5) It opens Settings pop up and from that you can find out “Target Framework”. From that you can also change framework of your application and save settings. That’s it.

You can also refer following image for faster review:

How to get total no of Tables, Views, SP and Functions in SQL?

Use [Your_DB_Name]

/* Count Total Number Of Tables */

SELECT COUNT(*) AS 'Total Tables' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

   

/* Count Total Number Of Views */

SELECT COUNT(*)  AS 'Total Views'  FROM INFORMATION_SCHEMA.VIEWS

   

/* Count Total Number Of Stored Procedures */

SELECT COUNT(*)  AS 'Total SPs'  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

   

/* Count Total Number Of UDF(User Defined Functions) */

SELECT COUNT(*)  AS 'Total Functions' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

Output:

Difference between Truncate and Delete in SQL.

Another most frequently asked interview question.

Truncate	Delete
We can’t Rollback after performing Truncate. Example: BEGIN TRAN TRUNCATE TABLE tranTest SELECT * FROM tranTest ROLLBACK SELECT * FROM tranTest	We can Rollback after delete. Example: BEGIN TRAN DELETE FROM tranTest SELECT * FROM tranTest ROLLBACK SELECT * FROM tranTest
Truncate reset identity of table.	Truncate reset identity of table.
It locks the entire table.	It locks the table row.
Its DDL(Data Definition Language) command.	Its DML(Data Manipulation Language) command.
We  can’t use WHERE clause with it.	We can use WHERE to filter data to delete.
Trigger is not fired while truncate.	Trigger is fired.
Syntax : 1) TRUNCATE TABLE table_name	Syntax : 1) DELETE FROM table_name 2) DELETE FROM table_name WHERE  example_column_id IN (1,2,3)

Difference between Stored Procedure and Function in SQL.

Today i am going to explain difference between SP and UDF which is most common interview question.

Stored Procedure (SP)	Function (UDF – User Defined Function)
SP can return zero , single or multiple values.	Function can return one value which is mandatory.
We can use transaction in SP.	We can’t use transaction in UDF.
SP can have input/output parameter.	Only input parameter.
We can called function from SP.	We can’t call SP from function.
We can’t use SP in SELECT/ WHERE/ HAVING statement.	We can use UDF in SELECT/ WHERE/ HAVING statement.
We can use exception handling using Try-Catch block in SP.	We can’t use Try-Catch block in UDF.

Application.DoEvents() in windows application.

Windows maintains a queue to hold various events like click, resize, close, etc. While a control is responding to an event, all other events are held back in the queue. So if your application is taking unduly long to process a button-click, rest of the application would appear to freeze. Consequently it is possible that your application appears unresponsive while it is doing some heavy processing in response to an event. While you should ideally do heavy processing in an asynchronous manner to ensure that the UI doesn’t freeze, a quick and easy solution is to just call Application.DoEvents() periodically to allow pending events to be sent to your application.

For good windows application, end user doesn’t like when any form of application are freezing out while performing larger/heavyweight operation. User always wants application run smoothly and in responsive manner rather than freezing UI. But after googling i found that Application.DoEvents() is not a good practice to use in application more frequently so instead this events it’s better to use BackGround Worker Thread for performing long running task without freezing windows.

You can get better idea if you practically look it. Just copy following code and check application with and without putting Application.DoEvents().

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        For i As Integer = 0 To 1000

            System.Threading.Thread.Sleep(100)

            ListBox1.Items.Add(i.ToString())

            Application.DoEvents()

        Next

    End Sub