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.

 

Transactions:-

 

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

Serializable

Snapshot (Yukon)

 

.

 

Types of Cursers: –

 

a. T-Sql Cursor:

                Static

                Keyset

                Dynamic

                Forward only

                Fast forward only

b. API Cursor:

                Static

c. Client:

                Static

                Keyset

 

Types of Store procedures:-

                System

                Extended

                User defined

                Temporary

                Remote

 

Constraints:

                Not NULL

Primary key

Check

                Unique

                Foreign key

 

Isolation Level:

                Uncommitted Read

                Committed Read

                Repeatable Read

                Serializable

                Snapshot (Yukon)

 

 

Locking:-

                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

 

Reference: http://www.microsoft.com/sql

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s