SMP vs MPP

Symmetric Multi-Processing (SMP)

Symmetric Multi-Processing (SMP) is the primary parallel architecture employed in servers. An SMP architecture is a tightly coupled multiprocessor system, where processors share a single copy of the operating system (OS) and resources that often include a common bus, memory and an I/O system.

Think of this as a typical single server, multi-core with locally attached storage, running the Microsoft Windows OS.

Massively Parallel Processing (MPP)

 Massively Parallel Processing (MPP) is the coordinated processing of a single task by multiple processers, each working on a different part of the task. With each processor using its own operating system (OS) and memory. MPP processors communicate between each other using some form of messaging interface via an “interconnect”. The setup for MPP is more complicated than SMP and one approach to simplify this while providing equal amounts of resources between processors is the “Shared-Nothing” architecture. This approach is the one Parallel Data Warehouse is based upon.

The term shared nothing architecture was coined by Michael Stonebraker (1986) to describe a multiprocessor database management system in which neither memory nor disk storage is shared among the processors.

For a database which follows the shared-nothing architecture, each processor has its own set of disks. Data is “horizontally partitioned” across nodes, such that each node has a subset of the rows from each table in the database. Each node is then responsible for processing only the rows on its own disks. Such architectures are especially well suited to data warehouse workloads, where large fact tables can be distributed across the nodes.

In addition, every node maintains its own lock table and buffer pool, eliminating the need for complicated locking and software or hardware consistency mechanisms. Because shared nothing does not typically have a severe bus or resource contention it can be made to scale to hundreds or even thousands of machines.

Replace: SQL Server String Function

REPLACE is the string function in sql server which is used to replace a string with new string in main string for all the occurance.

let say RANDHEER is the main string and in this i want to replace E with I then if i use replace it will replace all E's with I in RANDHEER. The result will be something like RANDHIIR.

SYNTAX for REPLACE:

REPLACE( MAIN STRING , STRING TO BE REPLACED , NEW STRING WHICH WILL REPLACE)

Keywords:SQL SERVER STRING FUNTIONS

Charindex :SQL Server String functions

It the function returns the starting index of string or character in main string. It takes three parameters as input and returns the index which could be int or big int.

charindex( main string , string need to be searched , staring point)

The third parameter is optional which say’s if you can predict where the function start searching in main string you can specify the value else it will by default start from start J

If you are getting zero in output means the character string doesn’t exists in main script and value greater than 0 tells the location of string.

sql server string functions

sql server string functions

Below is the list inbuilt sql server string functions available in sql server.

• ASCII
• Char
• Charindex
• Concat
• Difference
• Left
• Len
• Lower
• Ltrim
• Nchar
• Patiindex
• Quotename
• Replace
• Replicate
• Reverse
• Right
• Rtrim
• Soundex
• Space
• Str
• Stuff
• Substring
• UnicodeUpper

Key word : Sql server string functions , string function in sql server, string functions

Using Foreign Keys or Check Constraints in SQL Server

A foreign key constraint is really nothing more than a check constraint with a list of allowed values. So the question becomes, when should you use a check constraint and when should you use a foreign key?

Check constraints should be used when you need to validate patterns, perform calculation to compare against or use comparison operators such as >, <, >=, and so on.

Foreign keys should be used when you need to validate the column against a list of acceptable values, even if the list contains only one or two value, you should still implement it as a foreign key.

For Check constraint, whenever you want to add a new value to the list, you have to modify the table structure by using an ALTER TABLE. But for foreign key, you simply insert the new value in to the table.

 Using a foreign key for list validation also leads to a maintainable design. When a database is initially designed, you might not know the list of acceptable values. Or the list might be completely valid at the time it was created, but five years later the list of valid value might have changed. Application developers can easily add a maintenance screen into an application to allow one or more designated users to modify the list of allowed values and the foreign key constraints prevents a value from being removed from the table if it has been used. You don’t need to go to DBA for these small DBA tasks.

Check Constraints in SQL Server

You use check constraints to limit the range of possible values in a column or to enforce specific patterns for data. Check constraints must evaluate to a Boolean True/False and cannot reference columns in another table.

You can create check constraints at two different levels

·         Column-level check constraints are applied only to the column and cannot reference data in another other column.

·         Table-level check constraints can reference any column within a table but cannot reference columns in other tables.

The most basic constraints compares the data in a column to a specified value---- for example CHECK  salary <= 50000. You can create any number of  check constraints separated by AND, OR, or NOT to create more complex conditions.

 You can also use check constraints to enforce patterns within data. Using check constraints this way, you might enforce the pattern that an employee ID is required to start with an uppercase letter, followed by three digits and then six additional letters. Another example is to require an         e-mail address to contain , in order, any number of character or digits, an @symbol , a number of characters or digits, a period (.), and then either three characters or two characters with a period (.) plus two more characters.

 The wildcard characters for pattern matching are underscore (_), which designates one value that can be a character, number or special character; and a percent symbol (%), which designates any number of a characters, numbers, or special characters. For example,

a table –level check constraints to validate an e-mail address might look like this :

CONSTRAINT chkEmail CHECK (Email like ‘%@% [a-z] [a-z]’ or Email like ‘%@%. [a-z] [a-z].[a-z] [a-z]’

A column-level check constraints for the Employee ID looks like this:

CHECK (EmployeeID like ‘[(A-Z] [0-9] [0-9] [0-9] [A-Z] [A-Z] [A-Z] [A-Z] [A-Z] [A-Z] ‘)

Collation in SQL Server

SQL Server uses the collation setting to determine how non-Unicode character data is stored and how to sort and compare Unicode and non-Unicode.

To understand SQL Server collations, you need to start with the Windows OS. When you install a Windows OS. You install a version for the language you want to use, such as English, Greek or Russian. These various language versions require different character and different code pages to support the character sets and associated keyboard layout. A Windows locale is also set, based on the version of the Windows OS that you have installed. This Windows locale determines the setting for numbers currencies, times and dates on the server.   

Let the Microsoft SQL Server Installation Wizard determine the default Windows collation based on the Window locale of the Windows OS unless one of the following conditions exists:

·         The primary language supported by SQL Server  instance you are installing is different from the Windows locale of the computer on which you are installing SQL Server

·          The SQL Server instance you are installing will participate in a replication scheme with SQL Server instances supporting a different language.






Keywords: SQL Server 2005,SQL Server 2008 , SQL Server Collation,Collation in SQL Server

Recovery Models in SQL Server

A recovery model is a database configuration option that controls how transactions are logged, whether the transaction log is backed up, and what restore options are available for the database. The recovery model you choose for your database has both data- recovery implication and performance implications, based on the logging the recovery model performs or doesn’t perform.

Types of Recovery Models

SQL Server provides three recovery models for data base: Full, Simple and Bulk logged. These models determine how SQL Server works with the transaction log and selects the operation that it logs and whether it truncates the log. Truncating the transaction log is the process of removing committed transactions and leaving log space to new transaction. The following is a definition of each recovery model

• In the Full recovery model, the database engine logs all operations onto the transaction log, and the database engine never truncates the log. The Full recovery model lets you restore a database to the point of.
• In the Simple recovery model, the database engine minimally logs most operations and truncates the transaction log after each checkpoint. In the simple recovery model, you cannot back up or restore the transaction log. Furthermore, you cannot restore individual data page.
• In the Bulk-Logged recovery model, the database engine minimally logs bulk operations such as SELECT INTO and BULK LINSERT. In this recovery model, if a log backup contains any bulk operation, you can restore the database to the end of the log backup, not to a point in time. The Bulk-Logged recovery model is intended to be used only during large bulk operation.

RAID System in SQL Server

RAID systems are arrays of disk drives that provide fault tolerance, more storage capacity, and better performance for the disk subsystem, depending on the configuration. Although RAID hardware systems are not part of the SQL Server configuration, they directly affect SQL Server performance. There are a variety of RAID levels each of which uses a different algorithm for fault tolerance. The most common RAID levels used with SQL Server are 0, 1, 5 and 10.

• RAID 0 is also known as disk striping because it creates a disk file system called a stripe set. RAID 0 gives the best performance for read and writes operation because it spreads these operations across all the disks in the set. However, RAID 0 does not provides fault tolerance; if one disk fails, you lose access all the date on the stripe set.
• RAID 1 also known as disk mirroring provides a redundant copy of the selected disk. RAID 1 improves read performance but can degrade the performance of write operations.
• RAID 5 most popular RAID level, stripes the data across the disks of the RAID set as does RAID 0 , but it also adds parity information to provide fault tolerance. Parity information is distributed among all the disks. RAID 5 provides better performance than RAID1. However, when a disk fails, read performance decreases.
• RAID 10 or RAID 1+0 , includes both striping without parity ad mirroring RAID 10 offer better availability and performance than RAID 5, especially for write-intensive applications.
• The RAID configuration that is best for your database files depends on several factors, including performance and recover ability needs. RAID 10 is the recommended RAID system for transaction log, data and index files. If you have budget restrictions, keep transaction log files in a RAID 10 system, and store data and index files in a RAID 5 system.

Authentication Modes in SQL Server

SQL Server supports two types of authentication modes: Windows authentication and Mixed Modes.

The default authentication mode for SQL Server is Windows authentication, which mandates that the only users who can connect to the SQL Server instance are users who previously authenticated to the Window OS.

The alternative authentication, Mixed Mode, means that SQL Server support users who authenticate using either of two authentication methods. The first method is to rely on the Window OS to authenticate users. The second method is for SQL Server to authenticate users directly based on the submission of a user name and password to SQL Server by the client application that is attempting to gain access.

For most SQL Server environments, you should use Windows authentication mode because it provides the highest level of security. However, legacy applications often do not user accounts, so they must forward a user name and password to connect to SQL Server. In this case, Mixed Mode authentication lets the legacy application use SQL Server login to access the instance or if the database environment includes clients on OSs that cannot   authenticate with the Windows OS such as Macintosh or UNIX clients. DBA using Mixed Mode should be aware that using this authentication mode requires the creation of a strong password for the SA accounts. This account is highly privileged of SQL Server and its password critical factor when deciding to use Mixed Mode.

Named , Default and Multiple Instances of SQL Server

When you install SQL Server, the Microsoft SQL Server Installation Wizard gives you the option of defining the installation as the default instance or as a named instance.

 A named instance simply means that you define a mane for the instance during the installation. You will then have to access that instance by name.

Default instances acquire the name of the server you install them on. Thus you can have only one default instance at a time, but you can have mane name instance.

 When you start the Microsoft SQL Server Installation Wizard by running the setup.exe application, it detects whether a default instance already exists on the computer. If the wizard does not detect a default instance, it gives you the choice of installing a default instance or a named instance, clear the Default check box and type in the named instance.

Using multiple instances of SQL Server increase administration overhead and causes duplication of components. Additional instances of the SQL Server and SQL Server Agent services require additional computer resources: memory and processing capacity

In the following scenarios, multiple instances of SQL Server can be used:

• While testing multiple version of SQL Server on the one computer.
• While testing service packs and development databases and applications.
• While different customers require their own system and user databases along with full administrative control of their SQL instance.
• While the desktop engine is embedded in applications because each application can install its own instance independent of instances installed by other applications.

Materialized view or Indexed view

A materialized view or indexed view is a db object that stores the results of a query physically. it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots. A snapshot can be redefined as a materialized view.
In dbms a view is a virtual table representing the result of a database query. Whenever a query or an update addresses an ordinary view's virtual table, the DBMS converts these into queries or updates against the underlying base tables. A materialized view takes a different approach in which the query result is cached as a concrete table that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of some data being potentially out-of-date. It is most useful in data warehousing scenarios, where frequent queries of the actual base tables can be extremely expensive.

As the materialized view is like a real table, anything that can be implemented on a db table can be implemented on it.  Creating indexes on any column, resulting improvements in query performance time.

Database Views

A view consists of a query accessible as a virtual table in a relational database. Unlike normal tables (base tables) in a relational database, a view is not form part of the physical schema. Changing the data in a table alters the data shown in subsequent results of the view. 

Advantages of views over tables:

• Views can represent a subset of the data contained in a table
• Views can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
• Views take very little space to store because the database contains only the definition of a view, not a copy of all the data it presents
• Views can limit the degree of exposure of a table or tables to the outer world

Just as functions (in programming) can provide abstraction, so database users can create abstraction by using views. In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views.

Just as rows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered - by definition - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. However, sorted data can be obtained from a view, in the same way as any other table - as part of a query statement.

Database Lock

A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to update to the database. Any user can only modify those database records (that is, items in the database) to which they have applied a lock that gives them exclusive access to the record until the lock is released. There are two types for locking data techniques in a database:

1.      Pessimistic locking

2.      Optimistic locking  

In pessimistic locking a record or page is locked immediately when the lock is requested, while in an optimistic lock the record or page is only locked when the changes made to that record are updated. The latter situation is only appropriate when there is less chance of someone needing to access the record while it is locked; otherwise it cannot be certain that the update will succeed because the attempt to update the record will fail if another user updates the record first. With pessimistic locking it is guaranteed that the record will be updated. Locking can be controlled by isolation level.

Normalization Denormalization

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller tables and relationships between them. The main goal is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Informally, a relational database table is often described as "normalized" if it is in the Third Normal Form. Most 3NF tables are free of insertion, update, and deletion anomalies.
Databases intended for online transaction processing (OLTP) are typically more normalized than databases intended for online analytical processing (OLAP).
OLTP applications are characterized by a high volume of small transactions such as updating a sales record at a supermarket checkout counter. The expectation is that each transaction will leave the database in a consistent state. By contrast, databases intended for OLAP operations are primarily "read only" databases. OLAP applications tend to extract historical data that has accumulated over a long period of time.
For such databases, redundant or "denormalized" data may facilitate business intelligence applications. Specifically, dimensional tables in a star schema often contain denormalized data. The denormalized or redundant data must be carefully controlled during extract, transform, load (ETL) processing, and users should not be permitted to see the data until it is in a consistent state. The normalized alternative to the star schema is the snowflake schema. In many cases, the need for denormalization has waned as computers and RDBMS software have become more powerful, but since data volumes have generally increased along with hardware and software performance, OLAP databases often still use denormalized schemas.
Denormalization is also used to improve performance on smaller computers as in computerized cash-registers and mobile devices, since these may use the data for look-up only (e.g. price lookups). Denormalization may also be used when no RDBMS exists for a platform (such as Palm), or no changes are to be made to the data and a swift response is crucial

Data Modeling

Data modeling is a process used to define data requirements needed to support the business within the scope of corresponding information systems inside the organizations.
There are three different types of data models produced while progressing from requirements to the actual database to be used for the information system. The data requirements are initially recorded as a conceptual data model which is essentially a set of technology independent specifications about the data and is used to discuss initial requirements with the business stakeholders.
The conceptual model is then translated into a logical data model, which documents structures of the data that can be implemented in databases. Implementation of one conceptual data model may require multiple logical data models. The last step in data modeling is transforming the logical data model to a physical data model that organizes the data into tables, and accounts for access, performance and storage details. Data modeling defines not just data elements, but also their structures and the relationships between them.
Data modeling techniques and methodologies are used to model data in a standard, consistent, predictable manner in order to manage it as a resource. The use of data modeling standards is strongly recommended for all projects requiring a standard means of defining and analyzing data within an organization, e.g., using data modeling.

Primary Foreign and Unique Keys

In a relational database , a "Primary Key" is a key that uniquely defines the characteristics of each row .The primary key has to consist of characteristics that cannot be duplicated by any other row. The primary key may consist of a single attribute or a multiple attributes in combination. For example, a birthday could be shared by many people and so would not be a prime candidate for the Primary Key, but a social security number or Driver's License number would be ideal since it correlates to one single data value. Another unique characteristic of a Primary Key as it pertains to a relational database, is that a Primary Key must also serve as a Foreign Key on a related table[. For example:

Author TABLE Schema:

AuthorTable(AUTHOR_ID,AuthorName,CountryBorn,YearBorn)
Book TABLE Schema:
Book TABLE(ISBN,Author_ID,Title,Publisher,Price)
Here we can see that AUTHOR_ID serves as the Primary Key in AuthorTable but also serves as the Foreign Key on the BookTable. The Foreign Key serves as the link and therefore the connection between the two "related" tables in this sample database.
In a relational database, a unique key index can uniquely identify each row of data values in a database table. A unique key index comprises a single columns or a set of columns in a single database table. No two distinct rows or data records in a database table can have the same data value (or combination of data values) in those unique key index columns if NULL values are not used. Depending on its design, a database table may have many unique key indexes but at most one primary key index.
A unique key constraint does not imply the NOT NULL constraint in practice. Because NULL is not an actual value (it represents the lack of a value), when two rows are compared, and both rows have NULL in a column, the column values are not considered to be equal. Thus, in order for a unique key to uniquely identify each row in a table, NULL values must not be used. According to the SQL standard and Relational Model theory, a unique key (unique constraint) should accept NULL in several rows/tuples — however not all RDBMS implement this feature correctly.
A unique key should uniquely identify all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys are Social Security numbers (associated with a specific person) or ISBNs (associated with a specific book). Telephone books and dictionaries cannot use names, words, or Dewey Decimal system numbers as candidate keys because they do not uniquely identify telephone numbers or words.
A table can have at most one primary key, but more than one unique key. A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not. Thus, the values in unique key columns may or may not be NULL, and in fact such a column may contain at most one NULL field. Another difference is that primary keys must be defined using another syntax

Triggers in SQL Server 2005

Triggers are the database objects which fire on some event on table or view to perform some specific task.

Triggers are used of three types in terms of language statements and these are DDL, DML and LOGON triggers.

DDL – Data definition languages such as Alter table, Create table etc.

DML – DML triggers are fire on insert, update or delete operation on table.

LOGON – LOGON Triggers fires on the login n the application i.e. fires on the basis of session id.

Generally we care about DML triggers i.e. triggers fires after the insert update or delete operations on a table.

Further triggers can be classified in two types.

1.      Instead of Triggers.

 As per my understanding from different eBooks online Instead of trigger fires instead of the sql statement which is firing the trigger. I.e. the sql statement which is performing some event on table will be skipped and instead of trigger defined on table will do the operation and the sql statement will not be able to do the change. Instead of trigger can be used for views.

2.      After Trigger.

After trigger performs the operation on the table after the execution of sql statement which is performing some even. I.e. is a sql statement is doing update then after the updating the table the after trigger will fire. After trigger cannot be used for views.

There are two magic tables in sql server which are used in triggers to get the update data and earlier data.

Deleted: contains the columns which were deleted or modified in the table in last action. Using this data in triggers logic the task can be performed.

Inserted: It contains the latest data which is inserted or updated in the table.

General Syntax for creating the trigger.

--CREATE A SAMPLE TABLE PERSION

GO

/****** OBJECT:  TRIGGER [DBO].[T1]    SCRIPT DATE: 01/23/2011 12:06:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- AUTHOR:        <AUTHOR,,NAME>

-- CREATE DATE: <CREATE DATE,,>

-- DESCRIPTION:   <DESCRIPTION,,>

-- =============================================

ALTER TRIGGER [DBO].[T1]

   ON [DBO].[PERSON]

   AFTER UPDATE

AS

BEGIN

      -- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM

      -- INTERFERING WITH SELECT STATEMENTS.

      SET NOCOUNT ON;

      DECLARE @VAR VARCHAR(100)

SELECT @VAR= NAME FROM DELETED

UPDATE PERSON

SET ADDRESS=@VAR

    -- INSERT STATEMENTS FOR TRIGGER HERE

END

SQL Server User defined functions

As per the definition given by the various books and sites UDF are the database objects which user can create for computation or to get some specific result set on the basis of input parameters.

Just like stored procedure udf can also have the bunch of logical SQL statement to get some specific data but in udf you cannot use DDL or DML statements for physical database tables. Some of these operations can be performed for table variable.

Udf can be used in from class and join can be performed on those which give the feeling of view with parameter. I.e. for view parameters can’t be passed and but for udf it can be.

UDF’s are of three types

1.      Scalar value udf:

Scalar value udf returns single value i.e. they performs the calculation or logic and returns the single value. These functions are generally used in select statement and execute for each row in select statement.

2.      Inline table :

Inline table functions are the functions which returns result set. In these function the return clause contains the select statement with variable and the columns name are as per the column selected in select statement.

3.      Multi statement table

Multi statement functions returns table variable. Return variable is declared as table variable and then the result set need to be inserted in that table and function returns the table with columns as declared in returned variable.

You can provide 1024 parameters in udf J

Generally developers use UDF in there select statement to get some specific result in query. So for each row the UDF is called in select statement and if the number of rows is very large then calling a db object in a sql query can result in performance issue of query.

Anyways functions provide the concepts of modular programming. Functions are very effective in many places.

SQL Server Stored procedure

Stored procedure is the batch of SQL statements which used to perform some specific business task.

In Stored procedures the tasks are written as a bunch of SQL Statements which performs some specific task and combined perform business logic.

Different SQL statement can be come under one execution plan which enhances the performance. The logic written in the stored procedure reside on server itself so the interaction between the client and server is minimal.

Stored procedures provide database abstraction i.e. keeps the table hidden from the user.

Error handling can be done in stored procedure. You can use DDL and DML statement in stored procedure.

Through stored the network traffic can be reduced.

Syntax for SP

1.      Create statement Create procedure A

2.      Declare parameters

3.      Begin

4.      Write the logic for achieving business logic.

5.      End.

SP is executed by exec command

Exec proc 1.

SQL Server uses various system stored procedure to handle different system operation which encapsulate the system table to be directly in use.

Stored procedure can have output parameters, output variable need to be declaring before executing the SP so the output value can be hold.