Creating Sequence Number DDMMYY-00001 where DDMMYY is today’s date

The defined logic for generating sequence number field which is 12 digits is

E.g. 281113-00001

Where 281113 - Today’s date and

00001 – Unique no which will auto increment.

We require a table which will hold a single row with one filed today_date which will hold the current date and current_sequence_no which will hold the current sequence number.

create table Tab_VKM_SequenceManagement

(id int identity(1,1),

todays_date date,

current_sequence_no int)

insert into Tab_VKM_SequenceManagement(todays_date,current_sequence_no)

values(GETDATE(),1)

select * from Tab_VKM_SequenceManagement

Let’s create a procedure which will first check the date stored in Tab_VKM_SequenceManagement with the current date, if it is current date then it will update the table current_sequence count to +1 and generate the sequence number.

If the date in the table is not current date then it will update the date filed to current date and current_sequence to 1 and generate the required sequence number.

alter procedure  Proc_VKM_GetNextSequence

@return_sequence varchar(15) OUTPUT

as  

    declare @date date

    declare @sequence_no int

   

    declare @day varchar(4)

    declare @month varchar(4)

    declare @year varchar(4)

begin       

    select @date = todays_date, @sequence_no = current_sequence_no

    from Tab_VKM_SequenceManagement where id = 1     

  

    if @date = cast (getdate() as date)

    begin

           

            update Tab_VKM_SequenceManagement

            set current_sequence_no = current_sequence_no +1

            where id =1

           

            set @day =

            right('00' + cast( datepart(DD,@date) as varchar),2)

           

            set @month =

            right('00' + cast( datepart(MM,getdate()) as varchar),2)

           

            set @year =

            cast(Right(datepart(YY,getdate()),2) as varchar)

                       

            set @return_sequence =

            @day + @month + @year + '-' +

            right(('00000' + cast((@sequence_no +1) as varchar)),5)    

    end

    else if @date != cast (getdate() as date)

    begin  

            update Tab_VKM_SequenceManagement

           

            set todays_date = GETDATE(), current_sequence_no =1

           

            select @date = todays_date, @sequence_no = current_sequence_no

            from Tab_VKM_SequenceManagement where id = 1

           

            set @day =

            right('00' + cast( datepart(DD,@date) as varchar),2)

           

            set @month =

            right('00' + cast( datepart(MM,getdate()) as varchar),2)

           

            set @year =

            cast(Right(datepart(YY,getdate()),2) as varchar)

           

            set @return_sequence = @day + @month + @year + '-' +

            right(('00000' + cast((@sequence_no) as varchar)),5)

    end            

end

Script to call the procedure.

declare @next_sequence varchar(15)

exec Proc_VKM_GetNextSequence @next_sequence OUT

select @next_sequence

When the code shows the happiness, ego bored into it.

Post Reference: Vikram Aristocratic Elfin Share

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 Share, http://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 text, ntext, and image data.
Index	Index entries.
Text/Image	Text, ntext, 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 text, ntext, 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 text, ntext, 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

Pause your execution of block till a particular time using WAITFOR TIME

If you want your part of code to execute after a particular time then you can achieve this with the help of SQL Server WAITFOR TIME clause here you have mention the time along with the WAITFOR.

Let’s take a short example to see how we can achieve this. THRIST

select GETDATE() as 'Time'

WAITFOR TIME '05:40:00'

select GETDATE() as 'Delayed Time'

SELECT 'Hi its Morning'

Time

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

2013-10-10 05:39:56.440

(1 row(s) affected)

Delayed Time

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

2013-10-10 05:40:00.000

(1 row(s) affected)

Hi its Morning

Here we can see how we are able to achieve the delay of our batch using WaitFor Time.

Never miss a good chance to do good piece code.

Post Reference: Vikram Aristocratic Elfin Share

Pause your execution of block or delay execution using WAITFOR DELAY

There are situation when you want your piece of code to wait for some input after it proceed for next group of statement, if the scenario is like this you can achieve your wait using WAITFOR. So SQL Server has an option to wait for delay your batch until some short of input arrived.

Let’s take a short example to see how we can achieve this.

select GETDATE() as 'First Time'

waitfor delay '00:00:03'

select GETDATE() as '3 Second delay'

waitfor delay '00:00:02'

select GETDATE() as '2 Second delay'

waitfor delay '00:00:01'

select GETDATE() as '1 Second delay'

First Time

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

2013-10-10 05:24:39.007

(1 row(s) affected)

3 Second delay

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

2013-10-10 05:24:42.007

(1 row(s) affected)

2 Second delay

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

2013-10-10 05:24:44.007

(1 row(s) affected)

1 Second delay

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

2013-10-10 05:24:45.007

(1 row(s) affected)

Here we can see how we are able to achieve the delay of our batch using WaitFor Delay.

The way SQL developer says Database is poles apart. You database is safe as tongue in your mouth.

Post Reference: Vikram Aristocratic Elfin Share