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

Wednesday, November 20, 2013

Changing the default behavior of DML statements (INSERT/UPDATE/DELETE)


Generally we are using DML statements like Insert for inserting data into database table likewise Update for updating existing data in database table, but it is also possible to change the default behavior INSERT/UPDATE/DELETE (DML) statements for example while inserting data using INSERT statement, the new record may applies as an update to the table or even applies as a delete to the table.

You can implement the changing behavior with INSTEAD OF TRIGGER. Let’s do some practical to achieve the changing behavior of DML statements.

use testDemo
go
if object_id('ChangingDMLBehavior') is not null
      drop table ChangingDMLBehavior
go
     
We were checking the existence of 'ChangingDMLBehavior' table object.
Now lets create a table 'ChangingDMLBehavior'

create table ChangingDMLBehavior
(id int not null identity(1,1),
name varchar(10))
Command(s) completed successfully.

Table is now ready; it’s time to write an INSTEAD OF TRIGGER to stimulate the changing behavior of INSERT statement.

 create trigger trg_DMLBehavior on ChangingDMLBehavior
instead of insert
as
begin
      select * from inserted
      select * from deleted
      return
end
go
Command(s) completed successfully.

Now since Trigger is in place, now it’s time to try out INSERT statement on 'ChangingDMLBehavior' and check out the behavior of INSERT Statement.

 insert into ChangingDMLBehavior(name) values('Binny')

Now querying the table to find the inserted records, to our shock we found no records.

select * from ChangingDMLBehavior
id          name
----------- ----------

(0 row(s) affected)

This is because a record is inserted to table 'ChangingDMLBehavior' with INSERT command, but nothing happens to the table due to the instead of trigger. This is because the trigger does not tell SQL Server to perform any data manipulation operation.

Let’s do some modification of INSTEAD OF TRIGGER to allow INSERT OPTION on table.

alter trigger trg_DMLBehavior on ChangingDMLBehavior
instead of insert
as
begin
      set nocount on
      insert into ChangingDMLBehavior(name)
            select name from inserted
      return
end
go
Command(s) completed successfully.

Now we can fire INSERT statement on 'ChangingDMLBehavior' table and can expect insertion of record to the table. Lets insert one record into 'ChangingDMLBehavior'.

insert into ChangingDMLBehavior(name) values('Prachi')
(1 row(s) affected)

Querying the table we get

select * from ChangingDMLBehavior
id          name
----------- ----------
1           Prachi

(1 row(s) affected)

Instead of triggers are useful when you want some setting kind of table in database to hold the defined values.

When you are in love with your code you will stop dreaming code with closed eyes because reality better than your dreams

Post Reference: Vikram Aristocratic Elfin Share

PAGE Type in SQL Server


There are different types of pages in SQL Server. No matter what types of pages are, the layout of the page is the same. A data file consists of numbers of 8k-pages. 
  • A Page includes 8192 bytes. First 96 bytes are used for header. 
  • The rest of the space is for data. A variable length row offset array (or slot array) is located at the end of every page
  • Count of records (size of array) is saved in the header.
  • Records in a page are not sorted even though it is an index page. If the data needs to be sorted, the offset array will be is sorted by the key of the index.

There are about 14 types of pages in SQL Server data file.
  • Type 1 – Data page.
    • Data records in heap
    • Clustered index leaf-level
    • Location can be random
  • Type 2 – Index page
    • Non-clustered index
    • Non-leave-level clustered index
    • Location can be random
  • Type 3 – Text Mixed Page
    • Small LOB value(s), multiple types and rows.
    • Location can be random
  • Type 4 – Text Page
    • LOB value from a single column value
    • Location can be random
  • Type 7 – Sort Page
    • Temporary page for sort operation.
    • Usually tempdb, but can be in user database for online operations.
    • Location can be random
  • Type 8 – GAM Page
    • Global Allocation Map, track allocation of extents.
    • One bit for each extent, if the bit is 1, means the extent is free, otherwise means the extent is allocated (not necessary full).
    • The first GAM page in each file is page 2
  • Type 9 – SGAM Page
    • Shared Global Allocation Map, track allocation of shared extents
    • One bit for each extent, if the bit is 1, means the extent is allocated but has free space, otherwise means the extent is full
    • The first SGAM page in each file is page 3
  • Type 10 – IAM Page
    • Index Allocation Map. Extent allocation in a GAM interval for an index or heap table.
    • Location can be random.
  • Type 11 – PFS Page
    • Page Free Space. Byte map, keeps track of free space of pages
    • The first PFS is page 1 in each file.
  • Type 13 – Boot Page
    • Information about the page
    • Only page 9 in file 1.
  • Type 14 – Server Configuration Page (It may not be the official name)
    • Part of information returned from sp_configure.
    • It only exists in master database, file 1, page 10
  • Some Added Type in SQL Server 2008
    Type 15 – File Header Page
    • Information about the file.
    • It’s always page 0 every data page.
  • Type 16 – Differential Changed map
    • Extents in GAM interval have changed since last full or differential backup
    • The first Differential Changed Page is page 6 in each file
  • Type 17 – Bulk Change Map
    • Extents in GAM interval modified by bulk operations since last backup
    • The first Bulk Change Map page is page 7 in each file.
When you know all about your code u start loving it.


Post Reference: Vikram Aristocratic Elfin Sharehttp://technet.microsoft.com/

Retrieving Database Page Metadata


You can use DBCC PAGE command to retrieve meta-data about a database page. Before executing DBCC PAGE you need to execute DBCC TRACEON (3604) in order to return the output to the screen.
It is useful to look into the database pages.

Syntax for using DBCC PAGE is

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ] )

The printopt parameter has the following meanings: (each option provides differing levels of information)
  • 0 - print just the page header
  • 1 - page header plus per-row hex dumps and a dump of the page slot
  • 2 - page header plus whole page hex dump
  • 3 - page header plus detailed per-row interpretation

Now let’s start with the Page Detail of a database table object.

CREATE TABLE EmployeeTable
(
emp_id INT IDENTITY(1,1),
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hired_date DATETIME NOT NULL,
active BIT NOT NULL DEFAULT 1,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (emp_id),
)
Go

insert into EmployeeTable (first_name,last_name,hired_date)
select 'Pragyan', 'Mohanty', '1999-03-15'
go
insert into EmployeeTable (first_name,last_name,hired_date)
select 'Swagatika', 'Patnaik', '2001-07-05'
go
insert into EmployeeTable (first_name,last_name,hired_date)
select 'Sagarika', 'Das', '2002-11-10'
go

Now to check the Page detail for this newly created table we need to find the file Id and the Page Number, which we can get by using DBCC IND command.

 DBCC IND ( {dbname}, {table_name},{index_id} )
Where index_id, we can get from querying sys.indexs, -1 shows all indexes

DBCC IND ( 'testDemo', 'EmployeeTable' , -1 )

PageFID PagePID IAMFID IAMPID ObjectID    IndexID iam_chain_type  PageType
------- ------- ------ ------ ----------- ------- --------------- --------
1       1725    NULL   NULL   290100074   1       In-row data     10      
1       1724    1      1725   290100074   1       In-row data     1       

(2 row(s) affected)

Note: Page Type 1 means Data Page and Type 10 means Index Allocation Map

So from here we have got File Id = 1 and Page Id = 1724

Now we can DBCC PAGE Command to see the detail of Page and Storage information.
Before we can run DBCC PAGE, it's required that trace flag 3604 be set to instruct the engine to send output to the console; otherwise you won't see anything!

DBCC TRACEON(3604)
DBCC PAGE ('testDemo', 1, 1724, 1);

You can use DBCC PAGE ('testDemo', 1, 1724, 1) WITH TABLERESULTS; to get your result in table format.

By executing the DBCC PAGE you will get the result of Page Header, Data and OFFSET Table / Array.

Reference: http://technet.microsoft.com/

Enjoy code like you gotta dance when there's nobody watching ;)


Post Reference: Vikram Aristocratic Elfin Share & http://technet.microsoft.com/

PAGE and EXTENTS in SQL Server


Page and Extents are logical division of data storage in SQL Server. The data file (.mdf, .ndf) are logically divided into Pages numbering from 0…n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.

PAGE: The page size is 8 KB. the page types used in the data files of a SQL Server database.
Page has three important sections:
  • Page header
  • Actual data i.e. Data row
  • Row pointers or Row offset 
There are different types of Pages in SQL Server database.
Data
Data rows with all data except textntext, and image data.
Index
Index entries.
Text/Image
Textntext, and image data.
Global Allocation Map, Secondary Global Allocation Map
Information about allocated extents.
Page Free Space
Information about free space available on pages.
Index Allocation Map
Information about extents used by a table or index.
Bulk Changed Map
Information about extents modified by bulk operations since the last BACKUP LOG statement.
Differential Changed Map
Information about extents that have changed since the last BACKUP DATABASE statement.

Data pages contain all the data in data rows except textntext, andimage data, which is stored in separate pages. Data rows are placed serially on the page starting immediately after the header. A row offset table starts at the end of the page. The row offset table contains one entry for each row on the page and each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

The maximum amount of data contained in a single row is 8060 bytes, not including textntext, and image data.

Data rows are put on the page serially, starting immediately after the header.

OFFSET TABLE: A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

EXTENTS: Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB.
To make its space allocation efficient, SQL Server 2000 does not allocate entire extents to tables with small amounts of data. SQL Server 2000 has two types of extents:
  • Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
  • Mixed extents are shared by up to eight objects.
NOTE: Log files do not contain pages; they contain a series of log records.

Reference: http://technet.microsoft.com/en-us/library/aa174529(v=sql.80).aspx

Never fail to spot a dreadful piece code; it will offer idea of superlative choice to implement the same.


Post Reference: Vikram Aristocratic Elfin Share