Count number of tables in a SQL Server database

A simple query to find out number of tables in database is

USE YOURDBNAME

SELECT COUNT(*) from information_schema.tables 
WHERE table_type = 'base table' 

Enjoy...

SET NOCOUNT (Transact-SQL)

Whenever we write any procedure and execute it a message appears in message window that shows no of rows affected with the statement written in the procedure and we become very happy to see that our procedure is working. But do you know that this message creates an extra overhead on the network? Yes it does.

By removing this extra overhead from the network, we can actually improve the performance of our database and our application.

How should we do it?
When you create any procedure then first line of your procedure should be

SET NOCOUNT ON;

This one line of code turns off the message that SQL server sends back to front end after every T-SQL statement is executed. This is applied for all SELECT, INSERT, UPDATE and DELETE statements. As when stored procedures are executed there is no need to pass this information back to front end.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.If we still need to get the count of no of rows affected, we can still use @@ROWCOUNT option. Because The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Template used in SQL SERVER 2005

================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

================================================

Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Enjoy….

Weird thing with inner queries in SQL SERVER 2005

Few days ago, I found a very strange thing with SQL SERVER 2005 and I strongly feel that THIS IS A BUG in SQL SERVER which actually can create lots of data discrepancy.

To prove my point I have created a sample SQL Script. I want you to run the script in SQL SERVER.

Details of the Script

· Create new database.

CREATE DATABASE DB_TEST

· Use this Database.

USE DB_TEST

· Create a table named tblCategory

CREATE TABLE tblCategory
(
CategoryID INT PRIMARY KEY,
CategoryNAME VARCHAR(50)
)

· Create another table named tblProduct

CREATE TABLE tblProduct
(
ProductID INT PRIMARY KEY,
CategoryID INT FOREIGN KEY REFERENCES tblCategory(CategoryID),
ISCOMPLETED BIT
)

· Insert 5 rows in tblCategory

INSERT INTO tblCategory VALUES (1,'Category1')
INSERT INTO tblCategory VALUES (2,'Category2')
INSERT INTO tblCategory VALUES (3,'Category3')
INSERT INTO tblCategory VALUES (4,'Category4')
INSERT INTO tblCategory VALUES (5,'Category5')

· Insert 10 rows in tblProduct

INSERT INTO tblProduct VALUES (1,1,0)
INSERT INTO tblProduct VALUES (2,1,0)
INSERT INTO tblProduct VALUES (3,2,0)
INSERT INTO tblProduct VALUES (4,2,0)
INSERT INTO tblProduct VALUES (5,3,0)
INSERT INTO tblProduct VALUES (6,3,0)
INSERT INTO tblProduct VALUES (7,4,0)
INSERT INTO tblProduct VALUES (8,4,0)
INSERT INTO tblProduct VALUES (9,4,0)
INSERT INTO tblProduct VALUES (10,5,0)

· Select statement to confirm the data is inserted or not.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

· Here is a select query which is not correct. The query tells select ProductID from tblCategory table where CategoryID = 1. But the problem here is tblCategory is not having a column named ProductID. So this query throws an error and that's a correct behaviour.

SELECT ProductID FROM tblCategory WHERE CategoryID = 1

· Here is the magic. I have used the above select query which is not correct as a inner query with a update statement. What to do you think? What will happen? Update query should throw an error. Logically it should.I was thinking the same But just execute this update query and you will be shocked.

UPDATE tblProduct SET IsCompleted = 1 WHERE ProductID IN (SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

· Oops, 10 rows affected. Surpised. All the data in IsCompleted field is set to 1 but my inner query (SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong.

· This is just a sample query but When I executed such a similiar statement, 3364 rows in my table was updated.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

I think this is a bug but actaully it's not. Please read comments for more information on this..