Types Of Keys In Database

Database supports the following types of keys.

• Super Key
• Minimal Super Key
• Candidate Key
• Primary Key
• Unique Key
• Alternate Key
• Composite Key
• Foreign Key
• Natural Key
• Surrogate Key

Key In Sql:

Composite Key:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, 

but when it taken individually it does not guarantee uniqueness.

A primary key that is made by the combination of more than one attribute is known as a composite key.

CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 nvarchar(30),  

COL3 nvarchar(50),  

PRIMARY KEY (COL1, COL2)); (Composite Key)

Candidate Key:

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In the below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

AlterNate key:

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. 

So when we talk about alternate key, 

the column may not be primary key but still it is a unique key in the column.

Super Key:

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. 

Example: Primary key, Unique key, Alternate key are a subset of Super Keys

TUPLE

A tuple is a data structure that contains a sequence of elements of different data types. 

It can be used where you want to have a data structure to hold an object with properties, but you don't want to create a separate type for it.

Tuple<T1, T2, T3, T4, T5, T6, T7, TRest>

Tuple<int, string, string> person = 
                        new Tuple <int, string, string>(1, "Steve", "Jobs");

Example: Accessing Tuple Elements

var person = Tuple.Create(1, "Steve", "Jobs");
person.Item1; // returns 1
person.Item2; // returns "Steve"
person.Item3; // returns "Jobs"


var numbers = Tuple.Create("One", 2, 3, "Four", 5, "Six", 7, 8);
numbers.Item1; // returns "One"
numbers.Item2; // returns 2
numbers.Item3; // returns 3
numbers.Item4; // returns "Four"
numbers.Item5; // returns 5
numbers.Item6; // returns "Six"
numbers.Item7; // returns 7
numbers.Rest; // returns (8)
numbers.Rest.Item1; // returns 8





Usage of Tuple

Tuples can be used in the following scenarios:

When you want to return multiple values from a method without using ref or outparameters.
When you want to pass multiple values to a method through a single parameter.
When you want to hold a database record or some values temporarily without creating a separate class.


Tuple Limitations:

Tuple is a reference type and not a value type. It allocates on heap and could result in CPU intensive operations.
Tuple is limited to include 8 elements. You need to use nested tuples if you need to store more elements. However, this may result in ambiguity.
Tuple elements can be accessed using properties with a name pattern Item<elementNumber> which does not make sense.

IsNull and COALESCE

IsNull:

Return the specified value IF the expression is NULL, otherwise return the expression:

SELECT ISNULL(NULL, 'W3Schools.com');


In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.

Store_Name	Sales
Store A	300
Store B	NULL

SELECT SUM (ISNULL(Sales,100)) FROM Sales_Data;

returns the following result:

SUM (ISNULL(Sales,100))

400

COALESCE:

Return the first non-null value in a list:

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

Output:W3Schools.com

What is char, var, nchar , nvarchar

CHAR vs VARCHAR

CHAR:

• It is a fixed length data type
• Used to store non-Unicode characters
• Occupiers 1 byte of space for each character

1. DECLARE @string CHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

VARCHAR:

• It is a variable length data type
• Used to store non-Unicode characters
• Occupies 1 byte of space for each character

1. DECLARE @string VARCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

NCHAR:

• Is a fixed length data type
• Used to store Unicode characters (for example the languages Arabic, German and so on)
• Occupies 2 bytes of space for each character

1. DECLARE @string NCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

 

NVARCHAR:

• It is a variable-length data type
• Used to store Unicode characters
• Occupies 2 bytes of space for each character

1. DECLARE @string NVARCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

When to use what?

If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR. 

 

Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that. 

Identity in SQL

What is Identity in Sql:



An identity column of a table is a column whose value increases automatically. The value in an identity column is crated by the server. A user generally cannot insert a value into an identity column.


Example:

1. CREATE TABLE EMPLOYEE  
2. (  
3.    IID INT IDENTITY(1,1),  
4.    NAME [varchar](MAX) NOT NULL,  
5.    AGE INT NOT NULL  
6. )  

IDENTITY_INSERT ON allows a user to insert data into an Identity column and IDENTITY_INSERT OFF restricts a user from inserting data into an Identity column.

View in SQL

Views in SQL are kind of virtual tables. 
A view also has rows and columns as they are in a real table in the database. 

We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.


Create View:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

UPDATING VIEWS

There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then we will not be allowed to update the view.

1. The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.

Use of a View

Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

Getting Information about the Views

We can use the System Procedure Sp_Helptext to get the definition about the views. For example, we can use the sp_helptext command to get the information about the view Vw_EmployeeProj.

Hide   Copy Code

sp_helptext Vw_EmployeeProj

Renaming the View

We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:

Hide   Copy Code

SP_Rename 'Old Name', 'New name'

For example, if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:

Hide   Copy Code

sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'

Dropping a View

We can use the Drop command to drop a view. For example, to drop the view Vw_EmployeeInfo, we can use the following statement:

Hide   Copy Code

Drop view Vw_EmployeeInfo

1. Views can hide complexity

If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

2. Views can be used as a security mechanism

A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

3. Views can simplify supporting legacy code

If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.


These are just some of the many examples of how views can be useful.




Difference between stored procedure and View

A Stored Procedure:

• accepts parameters
• can NOT be used as building block in a larger query
• can contain several statements, loops, IF ELSE, etc.
• can perform modifications to one or several tables
• can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

A View:

• does NOT accept parameters
• can be used as building block in a larger query
• can contain only one single SELECT query
• can NOT perform modifications to any table

Difference between Join and Union

The join such as INNER JOIN or LEFT JOIN combines columns from two tables while the UNION combines rows from two queries.

In other words, join appends the result sets horizontally while union appends result set vertically.

The following picture illustrates the main difference between UNION and JOIN:

Conclusion:

Both being data combining operations are used in different situations. JOIN is used when we want to combine attributes of two relations having at least one attribute in common. UNION is used when we want to combine the tuples of the two relations that are present in the query.