Types
of SQL Keys
1. 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 subset of Super Keys.
2. 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 below diagram ID, RollNo and
EnrollNo are Candidate Keys since all these three fields can be work as Primary
Key.
3. Primary Key
Primary key is a set of one or more fields/columns
of a table that uniquely identify a record in database table. It can not accept
null, duplicate values. Only one Candidate Key can be Primary Key.
4. Alternate key
A Alternate key is a key that can be work as a
primary key. Basically it is a candidate key that currently is not primary key.
Example: In below diagram RollNo and
EnrollNo becomes Alternate Keys when we define ID as Primary Key.
5. Composite/Compound Key
Composite Key is a combination of more than one
fields/columns of a table. It can be a Candidate key, Primary key.
6. Unique Key
Uniquekey is a set of one or more fields/columns of
a table that uniquely identify a record in database table. It is like Primary
key but it can accept only one null value and it can not have duplicate values.
For more help refer the article Difference
between primary key and unique key.
7. Foreign Key
Foreign Key is a field in database table that is
Primary key in another table. It can accept multiple null, duplicate values.
For more help refer the article Difference
between primary key and foreign key.
Example : We can have a DeptID column in the
Employee table which is pointing to DeptID column in a department table where
it a primary key.
Pivot
Query:
select *
from
(
select c.rolename
as rolename ,b.username as username
from dbo.aspnet_UsersInRoles
as A
inner join
aspnet_users as b on
A.userid=b.userid
inner join
aspnet_roles as c on
c.roleid=a.roleid
) as
SourceTable
pivot
(
count(username)
for rolename in ([admin],[learner])
) as pivottable
Common
Table Expressions(CTE) in SQL SERVER 2008:
With
T(Address, Name, Age) --Column names for
Temporary table
AS
(
SELECT
A.Address, E.Name, E.Age from Address A
INNER
JOIN EMP E ON E.EID = A.EID
)
SELECT
* FROM T --SELECT or USE CTE temporary
Table
WHERE
T.Age > 50
ORDER
BY T.NAME
When to Use Common Table Expressions :
· Create a recursive query.
WITH ShowMessage(STATEMENT, LENGTH)
AS
(
SELECT STATEMENT = CAST('I Like ' AS VARCHAR(300)), LEN('I Like ')
UNION ALL
SELECT
CAST(STATEMENT + 'CodeProject! ' AS VARCHAR(300))
, LEN(STATEMENT) FROM ShowMessage
WHERE LENGTH < 300
)
SELECT STATEMENT, LENGTH FROM ShowMessage
- Substitute
for a view when the general use of a view is not required; that is, you do
not have to store the definition in metadata.
- Enable
grouping by a column that is derived from a scalar subselect, or a
function that is either not deterministic or has external access.
- Reference
the resulting table multiple times in the same statement.
No comments:
Post a Comment