SQL

Seite 1

Database

It is a collection of information that is organised so we can easily accessed,managed and updated.

Database Management System

It is a system softwares helps the programmer to create,retrieve,update and manage data in database.

ACID Properties

This allows safe sharing of data

1. Atomicity - A transaction must be an atomic unit of work. In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are.

2. Consistency - When a transaction is completed all data must be in consistent state. If any failure occurs, returns all data to its state before the transaction was started.

3. Isolation - A transaction in process and not yet committed must remain isolated from any other transaction.

4. Durability - After transaction completed data must be saved permanently, Even in the event of a failure and system restart, the data is available in its correct state

Normalization

Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies.

1NF

No two Rows of data contain repeating group of information.

Each row should have a primary key that distinguishes it as unique.

Any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.

2NF

It should satisfy 1NF.

There must not be any partial dependency of any column on primary key.

3NF

It should satisfy 1NF and 2NF.

Every non-prime attribute of table must be dependent on primary key.

SQL Server

SSMS is a client tool helps to connect developer machine to SQL Database.

To connect to local server we can use . or (local) or ip address or server name.

When creating a DB using MS SQL two files are generated.

1. .MDF - Data File (Contains actual data)

2. .LDF - Transaction Log FIle( Used to recover the database)

To set database into single user mode use - Alter databasename Set SINGLE_USER With Rollback Immediate

Primary Key

It is used to identify each record in a table uniquely. A table only have one primary key and it does not allow null value.

Foreign Key

Foreign key of one table points to the primary key of another table. It prevents invalid data being entered into the foreign key column.

It is used to enforce database integrity.

[ ] - Words put in this when we want to identify it as names rather than keywords.

Default Constraint

The user given value will overwrite the default constain even if it is a null value.

Cascading Referential Integrity

This allows to define actions SQL taken when a user attempts to delete or update a value in a foreign key field. There are four actions

1. No Action - Default, Raise error.

2. Set NULL - All rows of foreign key set to NULL.

3. Set Default - All rows of foreign key set to default.

4. Cascade - All row containing foreign keys are also deleted or updated.

Check Constraint

It is used to limit a range of values that can be entered for a specific column. It returns boolean value. When we pass NULL to this field it returns 'Unknown' so it allows the value to be entered.

Identity Column

Column values are automatically generated when we inserting a value. Identity seed represents the starting digit.

To insert value into this field explicitly we have to turn ON identity insert using

SET IDENTITY_INSERT_tabelname ON

To reset the identity column we use

DBCC CHECKIDENT ('tablename',RESEED,starting digit)

Retrieve Last Generated Identity Column Value

1. SCOPE_IDENTITY - It returns last generated value in the same session and within the same scope.

2. @@IDENTITY - Same session and across any scope.

3. DENT_CURRENT('table name') -Specific table across any session and any scope.

Unique Key

It is used to enforce uniqueness of a column.

A table can have more than one unique key.

Unique key allows one null value.

Distinct

Values across the selected column is distinct.

Group By

It can be used only with aggregate functions.

Having

It can be used only in select statements. The filtering of results done after aggregations are performed.

We can use aggregate function with having.

Where

It can be used with select,delete and update statements. The filtering of results performed before aggregation.

Aggregate functions can not used in where statement generally.

Delete Vs Truncate

Delete can have criteria but truncate can not.

We can execute trigger with delete but truncate can not.

Delete logs the operation but truncate can not.

Delete is slow and truncate is fast.

Delete can be rolled back but truncate can not.

Increase SQL performance

Try to avoid writing a SQL query using multiple joins

Try to remove cursors from the query and use set-based query.

Create and use indexes.