Tag Archives: MS SQL Server

Table statistics in SQL Server 2005/2008/2000 like Table size, total row, index size etc.

We may often need to see total row in each table, data size, index size, used and unused space in table etc. Well, in this case SP_SpaceUsed stored procedure could be used but it shows you states of only one table, what if we wants this type of statistics for all use tables in entire database? In this case, undocumented stored procedure “SP_MsForEachTable” will come to our help. Let us see it practically.
–create SP which will display states of all tables
Create proc tableState
AS
–create temporary table
–which will store all states
CREATE TABLE #TableState (
       tableName sysname ,
       rowCounts INT,
       reservedSize VARCHAR(50),
       dataSize VARCHAR(50),
       indexSize VARCHAR(50),
       unusedSize VARCHAR(50))
SET NOCOUNT ON
–insert result of SP_SpaceUsed in temp table
–if you go for CURSOR or LOOP you don’t need to use
–sp_msforeachtable stored procedure
–but to avoid CURSOR,sp_msforeachtable could be good alternative
INSERT #TableState
  EXEC sp_msforeachtable ‘sp_spaceused ”?”’
–display data of temp table
SELECT * FROM #TableState order by tableName
–drop temp table explicitly!!
DROP TABLE #TableState
GO
–run above SP
exec tableState
 
 
Happy Programming!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Environment.CurrentDirectory in C#

Well, this blog is mainly focuses on SQL Server technology but sometime I would like to give tips on other Microsoft Technology as I did it in past as well.

I have seen many C# developer use full path to access some file or folder insider the root directory of project. They might be unaware with Environment.CurrentDirectory property.

Have a look at below small tips for that.

string var1;
var1 = Environment.CurrentDirectory;
var1 = var1.Substring(0, var1.Length – 9);

Note: default CurrentDirectory property point to Bin\Debug folder if you are running your application in Debug mode so I removed last nine character to get root path. You can make it more customize as per your need. Hope this will help.

Cheers!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Facts about IDENTITY in SQL Server 2005

IDENTITY is a property and it can be used with Table object in Microsoft SQL Server. Basically it uses to maintain unique auto increment number for table.

Syntax of IDENTITY is:

IDENTITY [(seed,increment)]

SEED is a numeric value with which you want to load your first row in the table.

INCREMENT is a numeric value to identify the gap between two values of Identity column.

I have used IDENTITY column in my many past article but let us see this practically here once again.

NOTE: You must not insert value in identity column, SQL Server automatically find the value for it and INSERT it.

–CREATE demo table

CREATE TABLE IdentityCheck

(

ID INT IDENTITY(2,2),

Name VARCHAR(10)

)

–insert record in table with Identity

INSERT INTO IdentityCheck VALUES(‘Ritesh’)

INSERT INTO IdentityCheck VALUES(‘Rajan’)

–check what have been inserted

SELECT * FROM IdentityCheck

In IdentityCheck table we have set seed as 2, it means first row in our table will get value 2 in ID column and we have set increment as 2 so it means that every new row will add 2 in the previous row by this fact you can assume that our second row will insert value 4 in ID column.

If you delete rows from table and then will start inserting rows in table again, it won’t start ID value from 2, in our case, it will start from 6 because if you delete records, it will not reset your IDENTITY but yes if you TRUNCATE the table, IDENTITY will reset.

–check whether identity reset by deleting records.

DELETE FROM IdentityCheck

–let us insert records again.

INSERT INTO IdentityCheck VALUES(‘Ritesh’)

INSERT INTO IdentityCheck VALUES(‘Rajan’)

–let us check what value we got this time in ID column

–those will be 6 and 8

SELECT * FROM IdentityCheck

–let us now truncate table which will reset identity

TRUNCATE TABLE IdentityCheck

–let us insert records again.

INSERT INTO IdentityCheck VALUES(‘Ritesh’)

INSERT INTO IdentityCheck VALUES(‘Rajan’)

–let us check what value we got this time in ID column

–those will be 2 and 4

SELECT * FROM IdentityCheck

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

UPDATE() function with Trigger in SQL Server 2005:

UPDATE() function in Trigger will is one of the very useful function related to trigger. UPDATE() function will help you to know that which column is going to update. Let us see how will it work?

–create table1 for demo

CREATE TABLE CustInfo

(

CustId INT Identity(1,1),

Name VARCHAR(10),

Country Varchar(10),

)

GO

–insert records in above table

INSERT INTO CustInfo

SELECT ‘RITSEH’,’India’ UNION ALL

SELECT ‘RAJAN’ ,’Hindustan’

GO

–CREATE trigger to check which field

–we are updating

CREATE TRIGGER UpdateCheck ON CustInfo

AFTER UPDATE

AS

IF UPDATE(Name)

BEGIN

PRINT ‘YOU HAVE UPDATED NAME FIELD’

END

ELSE

BEGIN

PRINT ‘YOU HAVE NOT UPDATED NAME FIELD’

END

–check this out

UPDATE CustInfo SET Name =‘R.Shah’ WHERE Name=‘RAJAN’

As we are updating Name field, we will see first message as condition will fall in IF part.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Correlated Subquery with CASE in SQL Server 2005

Correlated subquery is always a powerful tool in developer’s toolkit. It has been proved very efficient many time against CURSOR and give good performance as well. Let me show you one good example of correlated subquery with CASE expression in Microsoft SQL Server 2005.

–create table1 for demo

CREATE TABLE CustInfo

(

CustId INT Identity(1,1),

Name VARCHAR(10)

)

GO

–insert records in above table

INSERT INTO CustInfo

SELECT ‘RITSEH’ UNION ALL

SELECT ‘RAJAN’

GO

–create table2 for demo

CREATE TABLE OrderMasters

(

OrderMasterId INT IDENTITY(1,1),

CustId INT,

Quantity INT

)

–insert records in above table

INSERT INTO OrderMasters

SELECT 1,10 UNION ALL

SELECT 1,14 UNION ALL

SELECT 1,2

–co-related subquery with CASE

SELECT C.CustId,C.Name,

CASE(SELECT count(CustId) FROM OrderMasters O WHERE O.CustId=C.CustID)

WHEN 0 THEN ‘No Order’

ELSE ‘Has Order’

END as Status

FROM

CustInfo C

Above, correlated query will return “No Order” if it will not find any records in OrderMasters table and it will return “Has Order” if there is an entry in OrderMasters table.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Instead Of Trigger in SQL Server 2005

As name said “Instead Of” when you submit any transaction, “Instead of” trigger get fire first instead of your submitted DML processes further. You can define only one “Instead Of” Trigger for one table or one view.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create INSTEAD OF trigger on empData table,

–which will show message if Age > 100

–and roll back transaction

alter TRIGGER empAgeCheck ON empData

INSTEAD OF INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

After Trigger in Microsoft SQL Server 2005 (CREATE and ALTER)

After giving quiet a good details about Triggers in my previous article, I would like to move further and want to give introduction about After Trigger in SQL Server.

AFTER Trigger was the only trigger before Microsoft SQL Server 2000 and it is useful as well. Table can contain more than one AFTER trigger. You can use AFTER trigger for recording data audit trails, complex business rule and for complex data validation.

AFTER trigger fire after all transaction gets complete respected to DML command but before COMMIT.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create AFTER trigger on empData table,

–which will show message if Age > 100

–but won’t stop inserting records.

CREATE TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–if you want to abort the batch

–if it break the rule than ALTER your TRIGGER

–and add rollback in it

ALTER TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Triggers in Microsoft SQL Server

Triggers are nothing but the special stored procedure which used to fire automatically with table events like INSERT, UPDATE, and DELETE. Apart from traditional Instead of and after trigger, Microsoft SQL Server 2005 became rich with DDL Triggers. You can have a look at DDL Triggers at:

http://www.sqlhub.com/2009/03/ddl-trigger-in-sql-server-2005-for.html

The main reason now I am writing Trigger article is, one should have concept of transaction flow, T-SQL, Stored Procedure of SQL Server etc. I finished many articles for said topic and felt this is the right time to introduce Triggers in my blog.

If you are aware with ORACLE than you might know that ORACLE used to fire trigger per operation and per row but SQL Server fires up the trigger once for every data modification not for affected row only. This is the thing I like most about SQL Server’s trigger.

As per my first paragraph, SQL Server has two types of DML trigger available Instead of trigger and after trigger. Both are completely different than each other in every aspect.

Before we start writing the trigger practically, it would be interesting and useful to understand when and how trigger will fire? To understand this, let us see transaction flow of SQL Server.

— Identity insert always gets priority
— Nullability Constraint
— Data-type check
–DML command like INSERT, UPDATE, and DELETE stops execution and Instead Of trigger will fire
— Primary-Key constraints
— Check Constraints
— Foreign-Key constraints
— Hold DML finishes
— After Trigger
— COMMIT trans
— finally writes the data file.

In short, when you executes any DML statement, it will first check Identity, nullability and data type check than performs Instead Of trigger. Once SQL Server done with these, it will run After Trigger before committing the transaction.

I will come up with example of triggers in my next articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Multi Statement Table Valued Function in SQL Server 2005:

Multi Statement Table Valued User Define Function is very useful and handy in-order to retain complex code with the table variable. This function populates table variable inside and then returns record set like stored procedure.

–creating demo table

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating multi-statment inline table valued function

CREATE FUNCTION dbo.GetEmployeeData1(@dept VARCHAR(10))

RETURNS @empData TABLE

(

Name VARCHAR(10),

Dept VARCHAR(10),

Company VARCHAR(10)

)

AS

BEGIN

INSERT @empData(Name,Dept,Company)

SELECT Name,Dept,Company FROM emps WHERE dept=@dept

RETURN

END

GO

–use above function

SELECT * FROM dbo.GetEmployeeData1(‘MIS’)

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Cross Apply in inline table valued function in SQL Server 2005

CROSS APPLY and OUTER APPLY is new in Microsoft SQL Server 2005. CROSS APPLY works like JOIN and a.k.a. INNER APPLY. You can use it like co-related query.

–create one table for demo

use adventureworks

–department table

CREaTE TABLE deptInfo

(

DeptName VARCHAR(10),

Description VARCHAR(20)

)

–INSERT records

INSERT INTO DeptInfo

SELECT ‘MIS’,‘IT DEPT’ UNION ALL

SELECT ‘account’,‘finance department’ UNION ALL

SELECT ‘chemical’,‘chemical department’

GO

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating inline table valued function

CREATE FUNCTION dbo.GetEmployeeData(@dept VARCHAR(10))

RETURNS TABLE

AS

RETURN

(

SELECT * FROM emps WHERE dept=@dept

)

GO

–use cross apply in above function

SELECT d.Deptname,d.description,e.name,e.company FROM DeptInfo d

CROSS APPLY

dbo.getemployeedata(d.deptname) as e

WHERE d.deptname=‘MIS’

Note: You can refer another article on Cross Apply and Outer apply at:

http://www.sqlhub.com/2009/03/cross-apply-and-outer-apply-clause-in.html

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah