SQL Server 2008 Data Types

Data Type               Description                                                           Storage Space

Char(n)                    N between 1 and 8,000 characters                           n bytes

Nchar(n)                  N between 1 and 4,000 Unicode characters                             (2 x n bytes) + 2 bytes overhead

Ntext                       Up to ((2 to the 30th power) - 1)

                               (1,073,741,823) Unicode characters

                                                                                                           2 bytes per character stored

Nvarchar(max)         Up to ((2 to the 30th power) - 1)

                               (1,073,741,823) Unicode characters

                                                                                                           2 x characters stored + 2 bytes

Text                         Up to ((2 to the 31st power) - 1)

                               (2,147,483,647) characters

                                                                                                           1 byte per character stored

Varchar(n)                               N between 1 and 8,000 characters                           1 byte per character stored + 2

                                                                                                           bytes overhead

Varchar(max)           Up to ((2 to the 31st power) - 1)

                               (2,147,483,647) characters

                                                                                                           1 byte per character stored + 2

bit                           0, 1, or Null                                                           1 byte for each 8 columns of this

tinyint                      Whole numbers from 0 to 255                                1 bytes

smallint                    Whole numbers from –32,768 to 32,767                   2 bytes

int                           Whole numbers from –2,147,483,648 to                   4 bytes

                               2,147,483,647

bigint                      Whole numbers from –9,223,372,036,854,775,808 to

                               9,223,372,036,854,775,807                                       8 bytes

numeric(p,s)

or

decimal(p,s)             Numbers from –1,038 +1 through 1,038 –1                             Up to 17 bytes

money                     –922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes

smallmoney             –214,748.3648 to 214,748.3647                                              4 bytes

float[(n)]                  −1.79E+308 to -2.23E-308,0, 2.23E-308 to 1.79E+308                              N< = 24 - 4 bytes

                                                                                                                          N> 24 - 8 bytes

real()                       −3.40E+38 to -1.18E-38,0, 1.18E-38 to 3.40E+38       4 bytes

Binary(n)                  N between 1 and 8,000 hex digits                            n bytes

Image                      Up to 231-1(2,147,483,647) hex digits                      1 byte per character

Varbinary(n)             N between 1 and 8,000 hex digits                            1 byte per character stored + 2 bytes overhead

Varbinary(max)         Up to 231-1(2,147,483,647) characters                     1 byte per character stored + 2

Date                        January 1, 1 to December 31, 9999                          3 bytes

Datetime                  January 1, 1753 to December 31, 9999                    8 bytes

Datetime2(n)            January 1, 1 to December 31, 9999                          6 to 8 bytes

Datetimeoffset(n)      January 1, 1 to December 31, 9999                          8 to 10 bytes

SmalldateTime         January 1, 1900 to June 6, 2079,                                              4 bytes

Time(n)                    Hours:minutes:seconds.9999999                                             3 to 5 bytes

                        N between 0 and 7 specifies fractional seconds              

Linked Servers

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

A linked server definition specifies the following objects:

• An OLE DB provider
• An OLE DB data source

An OLE DB provider is a DLL that manages and interacts with a specific data source. An OLE DB data source identifies the specific database that can be accessed through OLE DB. Although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for a variety of files and file formats. These include text files, spreadsheet data, and the results of full-text content searches.

The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL Server.

Codd's rules

Rule 1: The Information Rule

Every piece of data that we permanently store in a database is located in a table. In general, SQL Server fulfills this rule, because we cannot store any information in anything other than a table.

Rule 2: Guaranteed Access Rule

Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name. This rule stresses the importance of primary keys for locating data in the database. The table name locates the correct table, the column name finds the correct column, and the primary key value finds the row containing an individual data item of interest. We can also access data by any of the columns in the table, though we aren’t always guaranteed to receive a single row back.

Rule 3: Systematic Treatment of NULL Values

NULL values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational RDBMS for representing missing information in a systematic way, independent of data type. This rule requires that the RDBMS support a distinct NULL placeholder, regardless of data type.  NULLs are distinct from an empty character string or any other number, and they are always to be considered as unknown values.

Rule 4: Dynamic Online Catalog Based on the Relational Model

This rule requires that a relational database be self-describing. In other words, the database must contain certain system tables whose columns describe the structure of the database itself. This rule is becoming more of a reality in each new version of SQL Server, as with the implementation of the INFORMATION_SCHEMA system views.

Rule 5: Comprehensive Data Sublanguage Rule

This rule mandates the existence of a relational database language, such as SQL, to manipulate data. T-SQL fulfils this function for SQL Server and carries out all the data definition and manipulation tasks required to access data.

Rule 6: View Updating Rule

This rule can be technically fulfilled using INSTEAD OF triggers, but in what can be a less-than-straightforward manner. You need to take care when considering how to apply updates, especially if the view contains a GROUP BY clause and possibly aggregates.

Rule 7: High-Level Insert, Update, and Delete

This rule stresses the set-oriented nature of a relational database. It requires that rows be treated as sets in insert, delete, and update operations. The rule is designed to prohibit implementations that support only row-at-a-time, navigational modification of the database. The SQL language covers this via the INSERT, UPDATE, and DELETE statements.

Rule 8: Physical Data Independence

Applications must still work using the same syntax, even when changes are made to the way in which the database internally implements data storage and access methods. In fact, users of the data need only be able to get the basic definition of the data they need.

Adding indexes, changing the filegroup of an object, using partitioning:

Rule 9: Logical Data Independence

Rule 10: Integrity Independence

Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs. The database must support a minimum of the following two integrity constraints:

• Entity integrity: No component of a primary key is allowed to have a NULL value.

• Referential integrity: For each distinct non-NULL foreign key value in a relational database, there must exist a matching primary key value from the same domain.

Rule 11: Distribution Independence

This rule says that the database language must be able to manipulate data located on other computer systems.  In essence, we should be able to split the data on the RDBMS out onto multiple physical   systems without the user realizing it.

Rule 12: Non-Subversion Rule

If a relational system has or supports a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules or constraints expressed in the higher-level (multiple-records-at-a-time) relational language. This rule requires that alternate methods of accessing the data are not able to bypass integrity constraints. However, SQL Server 2008 violates this rule in two places:

• Bulk copy: By default, you can use the bulk copy routines to insert data into the table directly and around the database server validations.

• Disabling constraints and triggers: There’s syntax to disable constraints and triggers, thereby subverting this rule.

Disadvantages of traditional file approach

·         Data security

·         Data redundancy

·         Data isolation

·         Program /data dependence

·         Lack of flexibility

·         Concurrent access anomalies

RAD Model

The phases in the rapid application development (RAD) model are:

Business modeling: The information flow is identified between various business functions.
Data modeling: Information gathered from business modeling is used to define data objects that are needed for the business.
Process modeling: Data objects defined in data modeling are converted to achieve the business information flow to achieve some specific business objective. Description are identified and created for CRUD of data objects.
Application generation: Automated tools are used to convert process models into code and the actual system.
Testing and turnover: Test new components and all the interfaces.

Advantages of the RAD model:

• Reduced development time.
• Increases reusability of components
• Quick initial reviews occur
• Encourages customer feedback
• Integration from very beginning solves a lot of integration issues.

Disadvantages of RAD model:

• Depends on strong team and individual performances for identifying business requirements.
• Only system that can be modularized can be built using RAD
• Requires highly skilled developers/designers.
• High dependency on modeling skills
• Inapplicable to cheaper projects as cost of modeling and automated code generation is very high.

 When to use RAD model:

• RAD should be used when there is a need to create a system that can be modularized in 2-3 months of time.
• It should be used if there’s high availability of designers for modeling and the budget is high enough to afford their cost along with the cost of automated code generating tools.
• RAD SDLC model should be chosen only if resources with high business knowledge are available and there is a need to produce the system in a short span of time (2-3 months).