A Year Value Recall and Looking Frontward to well again Next Year

A Year Value Recall and Looking Frontward to well again Next Year.

2011 was a fantastic year, I evidence achieving many personal high points, which I surly look back to inspire myself for the next 2012 to become more better.

Mumbai Opportunity:

With god grace I get a chance to move to one of the IT city of India. Here I want to mention this city as GREAT BOMBAY. With my experience of living in Mumbai for some time, Mumbai teaches a lot about life.  Probably no other city in India teaches you as that of Mumbai. Mumbai buzzes with activity all through the 24 hours. The life line of Mumbai, the local trains is a great attraction. When you see the Mumbai trains you will believe that efficiency is still there in India. After reaching the office in the toilet everyone enquires the other which one (local) did the other make it i.e. 8:05 or 8:08 while polishing their attire to commence the work.

When you are in this city you will feel that certain sayings like "NOTHING IS IMPOSSIBLE", " SKY IS THE LIMIT", are really true. 2012 is the time to explore more on opportunity this city gives.

Blogging

I again started blogging as year 2011 draw closer to the very last week; I wish that I will continue this habit. This year my target will be 200 articles on SQL server. I am also aim to start few more blog on Entrepreneurship, Economic India and ASP.NET.

Friends

I feel myself very blessed on this front whether from preceding company or in Mumbai I got first-rate friends, I wish I will continue to uphold our friendship no matter for whatsoever.    

Get Fit,at least Exercise if not GYM

This has been an area which I ignored completely this year and end up putting five more Kilos and tummy clearly visible. Less I pen it on this the better. I am going to seriously concentrate on this. This medical keywords really scares… diabetes, blood pressure, heart attack… I don’t want to explore on these keywords.

  

Post Reference: Vikram Aristocratic Elfin Share

BATCH UPDATE IN SQL SERVER. A much relif for those who are working with Production server

BATCH UPDATE  IN SQL SERVER. A much relif for those who are working with Production server

During my deputation to Onsite, once I experienced a situation where a table from the PRODUCTION/LIVE DB needs an updation for a particular column value.

I remember a situation where where a junior SQL devloper execute update statement on that table which contain data in GBs. As a result of that update statement every thing get stopped.

Those of us, who work in the Production / Live DB, know the pain, of firing an update query on a huge table. This is very problematic as this will lead to the following

• The entire table will be locked by your application , so other users /  application will not be able to perform the DDL on this table
•  The TempDB will grow huge
•  A update lock on a table will stop other users , for making changes in the table.

So what is option to resolve this issue?

I create a solution where I am updateing a table with Active cloumn TRUE where ever it gets ‘FALSE’ value in the column.

For demostration purpose our table contain 10 record with the Active column value ‘FALSE’ and we are updating 2 record at a time. So the batch size of updation in our example is 2. U can set your own value depending upon your application useage.

select * from user_tab where active = 'False'

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           0

2           Orpita                                             0

3           Aalap                                              0

4           Trishona                                           0

5           Shobna                                             0

6           Eekalabya                                          0

7           Fani                                               0

8           Kishalaya                                          0

9           Mrinmoy                                            0

10          Satyajit                                           0

(10 row(s) affected)

And we are updating the FALSE value of Active column to TRUE. But here we are updating two record at a time.

declare @cnt int

declare @total_record int

declare @row_processed int

declare @row_count int

select @cnt = 1

select @row_processed = 0

select @total_record=count(*) from parent_tab

while ( @cnt > 0)

begin

      set rowcount 2

      update user_tab

      set active = 'True'

      where active = 'False'

      select @row_count = @@rowcount

     

      if(@row_count = 0)

      begin

            select @cnt = 0

      end        

      else

      begin

            select @row_processed = @row_processed + @row_count

            print 'The total number of rows effected :'+convert(varchar,@row_processed)

      end

end

print 'Update complete'

set rowcount 0

//OUTPUT OF SCRIPT

(2 row(s) affected)

The total number of rows effected :2

(2 row(s) affected)

The total number of rows effected :4

(2 row(s) affected)

The total number of rows effected :6

(2 row(s) affected)

The total number of rows effected :8

(2 row(s) affected)

The total number of rows effected :10

(0 row(s) affected)

Update complete

After execution of this script the result of the table is as follows

select * from user_tab where active = 'False'

user_id     user_name                                          active

----------- -------------------------------------------------- ------

(0 row(s) affected)

So we can see there is no record with ACTIVE column value ‘FALSE’. Let’s check for ACTIVE = ‘TRUE’

select * from user_tab where active = 'True'

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Fani                                               1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

(10 row(s) affected)

So all records gets updated with ACTIVE status sets to ‘TRUE’.

If you find any problem in understanding this code, revert me at viki.keshari@gmail.com

Is it possible to create a STORED PROCEDURE or a FUNCTION or TRIGGER with the same name as TABLE Name?

Answer is NO; let’s analyze answer by taking a temporary table ‘PARENT_TAB’ having the following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Procedure: Now we try to create a PROCEDURE with the same name as table name ie. ‘parent_tab’

create procedure parent_tab

as

begin

      select * from parent_tab

end

Msg 2714, Level 16, State 3, Procedure parent_tab, Line 4

There is already an object named 'parent_tab' in the database.

Urr.. Got error

Function:  Let’s try our luck with function with the same name as table name ie. ‘parent_tab’

create function parent_tab()

returns int

AS

BEGIN

      return (0)

end

Msg 2714, Level 16, State 3, Procedure parent_tab, Line 5

There is already an object named 'parent_tab' in the database.

Again Fail!!

Trigger: Let’s try for trigger

create trigger parent_tab ON parent_tab

instead of delete

AS

    PRINT 'Sorry - you cannot delete this data'

GO

Msg 2714, Level 16, State 2, Procedure parent_tab, Line 4

There is already an object named 'parent_tab' in the database.

Constraint: Will we be allowed to make constraint with the same name as table name, lets see

alter table parent_tab

add constraint parent_tab primary key(first_id)

Msg 2714, Level 16, State 4, Line 1

There is already an object named 'parent_tab' in the database.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

So we fail in all our attempt.

CONCLUSION: We cannot make procedure or function or trigger or constraint with the name as table name.

REASON: Since SQL Server treat all these as objects of database and this object are uniquely defined by the object name and object id that is the reason why we cannot have procedure or function etc with the same name as table name.

Post Reference: Vikram Aristocratic Elfin Share

Different ways to find TOP N records.

For our explanation we have taken a table ‘parent_tab ‘ having the following seven records data.

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Various ways to achieve this : Our objective is to find first 4 records from the above table

1. Using ROWCOUNT

set rowcount 4

select name from parent_tab

set rowcount 0

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

2. Using ROW_NUMBER

select name from

(

select row_number() over (order by first_id) as rno, name from parent_tab

) as tab where rno <= 4

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

3. Using Top N

select top 4 name from parent_tab

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

If you know any other way to achieve this, please light up your suggestion to me.

Post Reference: Vikram Aristocratic Elfin Share