About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Monday, January 16, 2012

Batch Delete – “One spoon at a time” the smart DELETE

BATCH DELETE



Delete in SQL server is not as simple as it looks.
Yes it has simple line of statement.

delete BatchDeleteDemo_Tab where name='Aadarshini'

Then what is the issue, there are several problem like
  • transaction log
  • disk space
  • time consuming
  • locking
It depends how many rows are affected by the DELETE statement above, but generally, it is almost never a good idea to run a ‘straight forward’ deletes.

SQL Server is working in a transactional manner, the rows which are to be deleted have to be logged in the transaction log, and if there is not enough disk space for the log, then we have a real problem – the log is full, no other transactions can be run… you get the idea.

When you delete rows from a table, the rollback transaction information is always written fully to the log file, and it will grow until the transaction is committed or rolled back.

So what is the option left for us:

while (1=1)
begin
      delete top 3 BatchDeleteDemo_Tab where name='Aadarshini'
      If @@rowcount = 0
            BREAK
      ELSE
            Continue
End

The while loop will delete 3 record at a time and then check if there are more rows to delete, if there are not then the loop gets terminated

CONCLUSION: This method is better and more efficient for obvious reasons: the log does not grow by too much and the log space is reused; it is easy to terminate the delete quickly and only the transaction of the last batch will be reverted; the lock escalations do not have a huge impact on the entire system since it is for few rows at a time.  


Post Reference: Vikram Aristocratic Elfin Share

Tuesday, January 10, 2012

How to store ONLY Time in SQL SERVER?

Only Time in SQL SERVER


Many a time we just need to store time part of date only, but when we look after SQL Server there is no such datatype which allow us to store only time part of the datetime.

So the question of the matter is how to store only time in sql table object. Here is a small exmple of storing time efficiently, is to use an integer column.

create table demoToStoreOnlyTime_tab(
myTotalHour smallint)

Command(s) completed successfully.

Now while inserting, you multiply the number of hours by 100, and add the minutes

insert into demoToStoreOnlyTime_tab  values( 100*datepart(hour,getdate()) + datepart(minute,getdate()))
(1 row(s) affected)

Rest let the developer do there work for formating while displaying time on front end

select CAST(LEFT(myTotalHour, LEN(myTotalHour)-2) + ':'+ RIGHT(myTotalHour, 2) AS VARCHAR(5)) as 'MyHour'  from demoToStoreOnlyTime_tab

MyHour
------
11:16

(1 row(s) affected)

Happy Coding :)

Post Reference: Vikram Aristocratic Elfin Share

Monday, January 9, 2012

Function that will identify why your code fails. (USING TRY CATCH)


Function in TRY CATCH

Always try to put your code in the TRY block and you check for the errors in the CATCH block. SQL Server has a bunch of functions that will help you identify why your code failed, here is a list of the functions and what they return

ERROR_NUMBER()
returns the number of the error

ERROR_SEVERITY()
returns the severity of the error

ERROR_STATE()
returns the error state number

ERROR_PROCEDURE()
returns the name of the stored procedure or trigger where the error occurred, this will be NULL if you run an ad-hoc SQL statement

ERROR_LINE()
returns the line number inside the routine that caused the error

ERROR_MESSAGE()
returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

Let's run an example that generates a divide by zero error, in the catch we are just doing a simple select that calls the functions mentioned before to see what they return

begin try
      select 1/0 as 'DivisionResult'
end try
begin catch
      select
            error_number() as 'ErrorNumber',
            error_severity() as 'ErrorSeverity',
            error_state() as 'ErrorState',
            error_procedure() as 'ErrorProcedure',
            error_message() as 'ErrorMessage'
end catch

DivisionResult
--------------

(0 row(s) affected)

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure  ErrorMessage
----------- ------------- ----------- --------------- -------------------------------
8134        16            1           NULL            Divide by zero error encountered.

(1 row(s) affected)

As you can see we got all that information back, that was pretty nice. Let's take it to the next step
Create the following table to store all the error information in

create table LogErrors (ErrorTime datetime,
                  ErrorNumber int,
                  ErrorSeverity int,
                  ErrorState int,
                  ErrorProc varchar(max),
                  ErrorLine int,
                  ErrorMessage varchar(max))

Command(s) completed successfully.

Create this stored procedure that will insert into the table we just created
create procedure proc_InsertError
AS
begin
      insert LogErrors
      SELECT GETDATE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()
End

Command(s) completed successfully.

Run these 3 queries, they will generate 3 inserts into the LogErrors table

begin try
      SELECT 1/0
end try
begin catch
    EXEC proc_InsertError
end catch;
       
begin try
    SELECT convert(int,'a')
end try
begin catch
    EXEC proc_InsertError
end catch;
       
begin try
    SELECT convert(tinyint,300)
end try
begin catch
      EXEC proc_InsertError
end catch;

If you check now what is in the table, you will see 3 rows

select * from LogErrors
ErrorTime  ErrorNumber  ErrorSeverity ErrorState  ErrorProc   ErrorLine ErrorMessage
----------- ----------- ------------- ----------- ----------  --------  -----------
2012-01-09  8134        16            1           NULL         2        Divide by zero error encountered.
2012-01-09  245         16            1           NULL         9        Conversion failed when converting the varchar value 'a' to data type int.
2012-01-09  220         16            2           NULL         16       Arithmetic overflow error for data type tinyint, value = 300.

(3 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share

How could you ensure that your VIEW is binded to your table in such a way that the tables cannot be deleted without deleting the VIEW. (SCHEMABINDING)


Finally I got some time to write this post; yes it is quite possible with the schemabing clause, creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema.


Imagine that you have created a view and you have altered the schema of underlying table (deleted one column).Next time when you run your view, it will fail. Here is when SCHEMABINDING comes into picture


lets do some practical on this.. we having a able named  DEMOFORSCHEMABINDING_TAB.


select * from demoforschemabinding_tab

id          name                 department           salary         pf_contribution

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

1           Aadita               IT                   35000.00       12000.00

2           Aakarsh              Sofware              45000.00       13000.00

3           Aaliya               Insurance            56000.00       13500.00

4           Falak                Account              35000.00       12000.00



(4 row(s) affected)



Now we are trying to create a view without SCHEMABINDING option



create view vw_without_schmeabinding_demoforschemabinding_tab

as (select * from demoforschemabinding_tab)



Command(s) completed successfully.



select * from vw_without_schmeabinding_demoforschemabinding_tab



id          name          department  salary   pf_contribution

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

1           Aadita         IT         35000.00  12000.00

2           Aakarsh        Sofware    45000.00  13000.00

3           Aaliya         Insurance  56000.00  13500.00

4           Falak          Account    35000.00  12000.00



(4 row(s) affected)




Now let’s try to drop a column say pf_contribution



alter table demoforschemabinding_tab

drop column pf_contribution



Command(s) completed successfully.



Let’s see the table data.



select * from demoforschemabinding_tab



id          name                 department           salary

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

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00



(4 row(s) affected)



Yes the column has been dropped. Now it is having only four columns.



Let find the same record from the view we have created in the above step.



select * from vw_without_schmeabinding_demoforschemabinding_tab



Msg 4502, Level 16, State 1, Line 1

View or function 'vw_without_schmeabinding_demoforschemabinding_tab' has more column names specified than columns defined.



The reason for this error message: The underlying schema (structure) of table has been changed on which the view was created.



So the question of the discussion is how to ensure that the schema (structure) of the table cannot be modified or dropped unless that view is dropped or changed. Otherwise, the Database Engine raises an error when executing ALTER statement on the table.



The answer to this question is using SCHEMABINDING. Let’s take an example to explain SCHEMABINDING.



select * from demoforschemabinding_tab

id          name                 department           salary

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

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00



(4 row(s) affected)



Now we will try to create a view on this table with SCHEMABINDING



create view vw_with_schmeabinding_demoforschemabinding

with schemabinding

as ( select id,name,department,salary from dbo.demoforschemabinding_tab)



Command(s) completed successfully.



Let’s run a select statement with the above created view



select * from vw_with_schmeabinding_demoforschemabinding

id          name                 department           salary

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

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00



(4 row(s) affected)



Now let’s try to modify the structure of the underlying table DEMOFORSCHEMABINDING_TAB



alter table demoforschemabinding_tab

drop column salary



Msg 5074, Level 16, State 1, Line 1

The object 'vw_with_schmeabinding_demoforschemabinding' is dependent on column 'salary'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN salary failed because one or more objects access this column.



The ALTER fails and gives the message 'vw_with_schmeabinding_demoforschemabinding' is depends on column salary so it cannot be modified or dropped. So this is the benefit of schema binding in view that is it will not allow to modify the underlying structure of table if any view is depends on the table schema.



Post Reference: Vikram Aristocratic Elfin Share