Developers Part 1

Hi Friends,
Today I will discuss about some information which is helpful for Developer, I got this from book/BOL and shared by my friends(Sai… and others):

Data Types:-

                Bit                                           :                1 Bit                        : 0 or 1

                Int                                            :                4 Bytes                : -2^31 to 2^31-1

                BigInt                                      :                 8 Bytes                : -2^63 to 2^63-1

                SmallInt                 :                2 Bytes                : -32768 to 32767

                TinyInt                                    :                1 Bytes                : 0 to 255

                Decimal/Numeric                :                8 Bytes                : -10^38+1 to 10^38-1

                Money                                    :                8 Bytes

                Small Money                    :                4 Bytes

Datetime                              :                8 Bytes                : 1jan 1757 to 31 dec 9999

Smalldatatime                     :                4 Bytes                : 1jan 1990 to 6 june 2076

Char                                       :                1 Bytes                : max 8000

Varchar                  :                1 Bytes



Types of Pages:


1.       Data page: All type of data except text, ntext and images

2.       Index page: Stores rows of index information

3.       Text/Image page:

4.       Page free space page(PFS): stores info about free space available on pages.

5.       Global allocation map page(GAM): Stores info about allocated extents

                                                                    Extents allocated or not (Free)

6.       Shared Global allocation map page(GAM): Mixed extent have free available space (1)                                                                                          

                                                                                     0 : extent is not allocated

                                                                                      No free space for mixed extent

7.       Index allocation map page (IAM):

                                stores info about extents used by the table or index, can map 63903 extents for table (16 index or 900 width)

8.       Bulk changed map page: info about extents modified by bulk operations for BACKUP LOG statement.

9.       Differential changed map page: for BACKUP DTABASE.




A group of database operations combined into a logical unit of work that is either wholly committed or rollback. It Provide the ACID properties.

                Atomicity:- transaction either commits or aborts, if transaction commits all of its effects remains if it aborts all the effects are undone “ALL OR NOTHING”

                Consistency: – An application should maintain the consistency of a database.

                Isolation:- Concurrent transactions are isolated from the updates of other incomplete transactions.

                Durability:- Once transaction commits. Its effect will persist even if there are system failures.


ANSI sql isolation levels:-

Read Uncommitted

Read Committed

Repeatable Read


Snapshot (Yukon)




Types of Cursers: –


a. T-Sql Cursor:




                Forward only

                Fast forward only

b. API Cursor:


c. Client:




Types of Store procedures:-



                User defined





                Not NULL

Primary key



                Foreign key


Isolation Level:

                Uncommitted Read

                Committed Read

                Repeatable Read


                Snapshot (Yukon)




                A lock is an object used by internal system software to indicate that to user has a dependency on some resource.


Lock: A restriction on access to resource in a multi-user environment sql server automatically lock users out of a specific row, column, or file in order to maintain security or to prevent concurrent data modification problem.


Lock resources: Database/file/index/table/page/index key/index key range/extent/ row/application.


Physical Lock: row/page/table/DB

Lock mode:                Shared/update/exclusion/index



This entry was posted in Performance Tuning. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.