Calculate employee working time using sql

We want to calculate the login hours of the employee. Card swap details are store in the table.

Here I am creating a table

Use Test create table EmpLogTime (  Empid varchar(10),  CardNo varchar(10),  Name varchar(40),  checkin datetime,  checkout datetime )

  Inserting some value

insert into EmpLogTime values ('1','C001','Bagesh Kumar Singh','2017-1-2 08:02:05','2017-1-2 12:02:05'), ('1','C001','Bagesh kumar singh','2017-1-2 14:05:36','2017-1-2 18:01:33'), ('2','C002','Rajesh','2017-1-2 08:03:05','2017-1-2 12:07:45'), ('2','C002','Rajesh','2017-1-2 14:05:36','2017-1-2 18:11:33'), ('3','C003','Ankit','2017-1-2 08:07:05','2017-1-2 12:03:45'), ('3','C003','Ankit','2017-1-2 14:08:36','2017-1-2 18:01:33'), ('4','C004','Dinesh','2016-1-2 08:03:09','2016-1-2 12:06:33'), ('4','C004','Dinesh','2016-1-2 14:01:39','2016-1-2 18:12:36')

See the value

In a day we are doing the multiple swaps. We are considering first swap is as check in and last swap is as check out. Here I am calculating check in and checkout difference in minute for each entry and finally we are summing the all time.

See below script

;with hoursworked as ( SELECT Empid, CardNo,Name,DATEDIFF(minute, checkin, checkout) as minutes, FORMAT(checkin,'yyyy-MM-dd') [date] from EmpLogTime   ) select Empid,CardNo,Name,date, sum(minutes) [Total minutes per day]   from hoursworked group by Empid,CardNo,Name,date

See the output.

Suppose 8 hrs are mandatory for every employee. We can easily find out, who is working less hours.8 hrs means 480 Minutes. If the total time is greater than 480 minutes or equal to 480 minutes then employee completed the working hours else less working hours.

with hoursworked as ( SELECT Empid, CardNo,Name,DATEDIFF(minute, checkin, checkout) as minutes, FORMAT(checkin,'yyyy-MM-dd') [date] from EmpLogTime  ), WorkingHrs AS( select Empid,CardNo,Name,date, sum(minutes) [Total minutes per day] from hoursworked group by Empid,CardNo,Name,date ) select Empid,CardNo,Name,date,[Total minutes per day], case when ([Total minutes per day]-480)>=0 then 'Complete Hours' else 'Less Working Hours' end AS Status from WorkingHrs

IAM pages in sql server

When we are creating a table in sql server, it creates a whole hierarchy Object, partition and Allocation units. Unit store the data type.

For the entire allocation unit sql server create the separate IAM pages.

  

In short a heap/B tree structure can have minimum of one IAM page and maximum of (No. of partition X 3) IAM pages. If the tables grow further and pages allocated from different GAM interval, more IAM pages will be added. These IAM pages need to be linked together and this list is called IAM chain.

DBCC traceon(3604) DBCC ind('Test','Employee',-1)

IAM page ID is 8 in this example.

Bulk Change Map pages in sql server

BCM (Bulk Change Map) page also known as Minimally Logged Map (ML Map). SQL Server uses BCM pages to track extent modified by bulk logged operation since last backup log operation. BCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last log backup due to bulk logged operation, if the bit is 0, there is no change in the corresponding extent due to bulk logged operation after the last log backup. A BCM page can hold information of around 64000 extents. 

Boot page in sql server

Page type of Boot page is 13. Boot page will not be available in the secondary data file. We can see the content of this page using the DBCC Page command and some of the values stores in this page are self explanatory. If this page is corrupted for some reason, it is not possible to recover the database using DBCC Checkdb. The page restore also will not help in this situation. The only possible way to recover the database is restore from last good backup. The base metadata about the whole database is stored in this page. If this page is corrupt, checkdb cannot repair this page, and a restore of file number 1 will be needed to recover the database.

To get information about the database boot page, you can execute DBCC PAGE pointing to page 9 in file 1 or DBCC dbinfo [('dbname')], always activating the trace flag 3604 (DBCC TRACEON(3604)).

Sort pages in sql server

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Page type of sort page is 7.

When we performing the sort operation temporary pages are created. 

Text/Image pages in sql server

These pages are storages the LOB (large object) data, technically data which is more than 8 KB in size. It stores the data of data types (text, ntext, image, nvarchar (max), varchar (max), varbinary (max), and xml).Data that has Row-Overflow Data Exceeding 8 KB, if the data is more than the size of the page (8KB) the data column will be automatically allocated to the new page by sql server and will set IN_ROW_DATA allocation counter to 1.

Page free space page in sql server

SQL Server uses PFS pages to track the amount of space still available on each page. Page free space (PFS) contains a byte-map. Each page in a database file is associated with a single byte in one of the PFS pages. The byte contains information about how much space is still available on the page as well as a few status bits:

Ø  bits 0-2: how much free space is on the page

·         0x00 is empty

·         0x01 is 1 to 50% full

·         0x02 is 51 to 80% full

·         0x03 is 81 to 95% full

·         0x04 is 96 to 100% full

Ø  bit 3 (0x08): is there one or more ghost records on the page?

Ø  bit 4 (0x10): is the page an IAM page?

Ø  bit 5 (0x20): is the page a mixed-page?

Ø  bit 6 (0x40): is the page allocated?

Ø  bit 7 is unused

A PFS interval is 8088 pages, or about 64MB. A PFS page doesn't have a bitmap – it has a byte-map, with one byte for each page in the PFS interval.

  

PFS(1:1)= 0X44 ALLOCATED 100_PCT_FULL

Means 0x04 is 96 to 100% full (page is full 90-100%)

Global allocation map (GAM) and Shares Global Allocation Map (SGAM) pages in sql server

SQL Server uses a structure called the Global Allocation Map (GAM) to keep track of all free extents in a database file. The GAM is consists of several GAM pages. GAM pages records what extents have been allocated for any use. GAM has bit for every extent. If the bit is 1, the corresponding extent is free, if the bit is 0, the corresponding extent is in use as uniform or mixed extent. A GAM page can hold information of around 64000 extents. That is, a GAM page can hold information of (64000X8X8)/1024 = 4000 MB approximately. In other ward we can say that, a data file of size 7 GB will have two GAM pages.

SGAM pages record what extents are currently being used as mixed extent and also have at least one unused page. SGAM has bit for every extent. If the bit is 1, the corresponding extent is used as a mixed extent and has at least one page free to allocate. If the bit is 0, the extent is either not used as a mixed extent or it is mixed extent and with all its pages being used. A SGAM page can hold information of 64000 extents. That is, a SGAM page can hold information of (64000X8X8)/1024 = 4000 MB. . In other ward we can say that, a data file of size 7 GB will have two GAM pages.

Server configuration page in sql server

Use the Database Configuration page to edit system settings of a Master Data Services database. System settings affect all web applications and web services associated with the selected Master Data Services database. You must select or create a Master Data Services database before system settings are enabled and available for configuration.

Page type of Server configuration page is 14.

Data page in sql server

SQL servers store the data records in data pages. A data page consists of three sections. Page Header, actual data and row offset array. Data rows with all data, except text, next, image, nvarchar (max), varchar (max), varbinary (max), and xml data. The actual data in your table is stored in Pages, except BLOB data. If a column contains BLOB data then a 16 byte pointer is used to reference the BLOB page. The Page is the smallest unit of data storage in Microsoft SQL Server. A page contains the data in the rows. A row can only reside in one page. Each Page can contain 8KB of information, due to this; the maximum size of a Row is 8KB. A group of 8 adjacent pages is called an extent. A heap is a collection of data pages.

Let’s see how the Data pages

Here I am creating a table and inserting some records.

create table employee ( EmpID int,   Name varchar(100),   EAdd varchar(1500),   Mobile varchar(10) )

Now I am inserting a record.

  

Let’s see where the data store in the pages. For seeing this we need to use of DBCC command.

First we see the index allocation page using below script.

DBCC IND('Test',employee,-1) --Test – database name --employee –table name -- -1 it means see the all pages stored in IAM(Index allocation Map) page.

   

Now I am seeing the page vales using below script

DBCC TRACEON(3604) DBCC Page('Test',4,9,1)

Executing this command

 

Basically page is divided on 3 parts

Ø  Page header (It is 96 bytes and stores the Meta data about page like page number, page type, the amount of free space, allocation unit ID.)

Ø  Data rows (It store the actual data)

Ø  Row offset (It is 36 bytes, stores the information about the data rows and address to next pages)

I this given example we can see that after inserting one record 8055 byte space is free

I means for one record take

For one record take 39 bytes. Let’s see I am inserting some more records

Now there are 16 records in a table. New see the free space on the page

  

See in the chart

  

 For one record it takes 39 byes. Now think suppose we insert 208 records then what happen.

  

8094 byte store in 1 page and other 330 byte store in 2^nd page.

See two pages created.

  

188 records are store on the page 1 and remaining 28 records are store in 2^nd page.

SQL Server buffer pool

An SQL server buffer pool is nothing but a place in system memory that is use for caching tables and index data page for read or modify from the disk. It is also known as sql server buffer cache. The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval.

 Hard disks are slow, memory is fast. Whenever data is written to or read from a SQL Server database, it will be copied into memory by the buffer manager. The buffer pool will use as much memory as is allocated to it in order to hold as many pages of data as possible. When the buffer cache fills up, older and less used data will be purged in order to make room for newer data.

Below sql script is use to find the buffer pages

SELECT                 COUNT(*) AS buffer_cache_pages,                 COUNT(*) * 8 / 1024 AS buffer_cache_used_MB FROM sys.dm_os_buffer_descriptors;

 
 

Delete top N records from a table

With the help of below sql script we can delete top n record from a table.

Delete top (1) FROM [Test].[dbo].[Employee]

 

One record is deleted successfully.

Using CTE

with  temp as   (    select top 5 * from [test].[dbo].[employee]    order by 1 asc   )   delete from temp