Get each table space and their rows count

Sometime we need to know how much table space are used to store the data and also wish to know the number of rows stored in it, this query help you to get all the detail.

SELECT
 SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id) AS name,
 reserved_page_count * 8 as space_used_kb,
 row_count
FROM sys.dm_db_partition_stats AS p
JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0
ORDER BY SCHEMA_NAME(o.schema_id) + ',' + OBJECT_NAME(p.object_id)

Generate list of all Month name

DECLARE @year INT
SET @year = 2016

;WITH months AS(
    SELECT 1 AS Mnth, DATENAME(MONTH, CAST(@year*100+1 AS VARCHAR) + '01')  AS monthname
    UNION ALL
    SELECT Mnth+1, DATENAME(MONTH, CAST(@year*100+(Mnth+1) AS VARCHAR) + '01') FROM months WHERE Mnth < 12
)
SELECT * FROM months;

SQL Create Table

SQL CREATE TABLE

The CREATE TABLE statement is used to create a table in a database to stored data.

Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE tablename
(
columnname1 datatype(size) [null | Not Null],
columnname2 datatype(size) [null | Not Null],
columnname3 datatype(size) [null | Not Null],
....
);

Parameters

tablename

The name of the table that you wish to create.

columnname1, columnname2

The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

CREATE TABLE employees

( employeeid INT NOT NULL,

  lastname VARCHAR(50) NOT NULL,

  firstname VARCHAR(50), 

  Address varchar(200)

  areaPin bigint

  );

1.     Column name employeeid is numeric column and can’t be null
2.        Lastname, firstName,Address is varchar column.
3.      AreaPin is numeric column.

SQL Basics for Beginner

SQL Basics

An instruction to a database to combine data from more than one table.

A SQL join combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.

DATABASE

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images.

RELATIONAL DATA

A database structure that is in relationship with other database objects. These links are what we use to do our SQL Joins, so they are important to understand. The name for these links in database terminology is "foreign keys." A foreign key is the way you link one table to another.

This join can be of any type like one-to-one, one-to-many, many-to-many etc.

TABLE

Databases store their data in a system of tables. As we do stored data in excel or access same we do in a tables. Tables is a collection of columns and rows where you store data. Typically, each row represents an additional thing that you care about, and each column represents an attribute that the thing can have. Table can have multiple type of columns like numeric, string, text, image, video etc.

We have employee table which is collection of columns 

(IdNum, Lname,Fname,JobCode,Salary,Phone).

TYPES OF SQL JOINS

SQL join is in a database is combining data from more than one table. There are different kinds of joins, which have different rules for joining.

INNER JOIN

An inner join produces a result set that is limited to the rows where there is a match in both table.  

LEFT OUTER JOIN

A left outer join, or left join, results in a set where all of the rows from the first, or left hand side, table are preserved. The rows from the second, or right hand side table only show up if they have a match with the rows from the first table.  Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set. 

RIGHT OUTER JOIN

A right outer join, or right join, is the same as a left join, except the roles are reversed.  All of the rows from the right hand side table show up in the result, but the rows from the table on the left are only there if they match the table on the right.  Empty spaces are null, just like with the the left join.  

FULL OUTER JOIN

All rows from both tables are returned in a full outer join. Similarly to the left and right joins, we call the empty spaces null.   

CROSS JOIN

The cross join returns a table with a potentially very large number of rows.  The row count of the result is equal to the number of rows in the first table times the number of rows in the second table. Each row is a combination of the rows of the first and second table.  

SELF JOIN

You can join a single table to itself.  We can use same table twice.

You can be perfect from diagram.

What is an Identity?

•         Identity (or AutoNumber) is a column that automatically generates numeric values.
•          A start and increment value can be set, but most DBA leave these at 1.
•          A GUID column also generates numbers; the value of this cannot be controlled.
•          Identity/GUID columns do not need to be indexed.
•          SELECT @@IDENTITY - returns the last IDENTITY value produced on a connection
•          SELECT IDENT_CURRENT ('tablename') - returns the last IDENTITY value produced in a table
•          SELECT SCOPE_IDENTITY() - returns the last IDENTITY value produced on a connection.

What is Normalization in SQL Server?

In relational database design, the process of organizing data to minimize redundancy is called normalization. It usually involves dividing a database into 2 or more tables and defining relationships between tables. Objective is to isolate data so that additions, deletions, and modifications can be made in just one table. 

Benefits:-

•          Eliminate data redundancy
•          Improve performance
•          Query optimization
•          Faster update due to less number of columns in one table
•          Index improvement

There are multiple forms of Normalizations in a database.

•          First Normal Form (1NF)
•          Second Normal Form (2NF)
•          Third Normal Form (3NF)
•          Fourth Normal Form (4NF -BCNF NF)

First normal form (1NF)

Eliminate duplicative columns from the same table.

·         Create separate tables for each group of related data and identify each row with a unique column or set of columns.

·         Remove repetitive groups

·         Create Primary Key

Name	State	Country	Phone1	Phone2	Phone3
John	101	1	488-511-3258	781-896-9897	425-983-9812
Bob	102	1	861-856-6987
Rob	201	2	587-963-8425	425-698-9684
PK			[ Phone Nos ]
?				?
ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684

 Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• ·         Meet all the requirements of the first normal form.
• ·         Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• ·         Create relationships between these new tables and their predecessors through the use of foreign keys.   
• Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• ·         Meet all the requirements of the second normal form.
• ·         Remove columns that are not dependent upon the primary key.

  Country can be derived from State also… so removing country

ID	Name	State	Country
1	John	101	1
2	Bob	102	1
3	Rob	201	2

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

•  Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.

If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it

 The 4NF also known as BCNF NF

TeacherID	StudentID	SubjectID	StudentName
101	1001	1	John
101	1002	2	Rob
201	1002	3	Bob
201	1001	2	Rob
TeacherID	StudentID	SubjectID	StudentName
101	1001	1	X
101	1002	2	X
201	1001	3	X
201	1002	2	X