Query To Get No of Records in all the Tables of a Database

declare @table_collection table(table_id int identity(1,1), table_name varchar(200), count_sql nvarchar(500), rec_count int)
declare @table_count int

insert into @table_collection
select name, 'select @rc_count = count(1) from [' + name + ']',0 from sys.tables

select @table_count = count(1) from sys.tables
declare @rec_counter int = 1

declare @temp_id int
declare @temp_count int
declare @sql nvarchar(500)

while @rec_counter<=@table_count
begin
set @temp_count = 0
select @temp_id = table_id, @sql =count_sql from @table_collection where table_id = @rec_counter

EXEC sp_executesql @sql ,N'@rc_count int OUTPUT' ,@temp_count OUTPUT
update @table_collection set rec_count = @temp_count where table_id = @rec_counter

set @rec_counter = @rec_counter + 1
end

select table_name, rec_count from @table_collection


SQL TO GET ALL COLUMN AND DATA TYPE OF ANY SQL SERVER TABLE


select TABLE_NAME, COLUMN_NAME, DATA_TYPE
from information_schema.columns
where table_name like 'DepartmentTrainingMapping'
order by table_name, ordinal_position


Database Normalization

What does it mean by Normalization of a Database?

Definition: “Normalization is the process of removing anomoly from a Database.”

Removing Anomoly in a database is mainly concern with,

1. Eliminating redundant data (Storing the data in more than one table with proper relationships among the tables)
2. Ensuring data dependencies make sense (data is should be stored into related table). 

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Understanding Normalization in simple words.

You may consider the following example\scenario in order to understand the concept of normalization.

Suppose we have a normalized database (simple) with three tables 

• TableA  (with 5 Columns)
• TableB  (with 10 Columns)
• TableC  (with 15 Columns)

An un normalized database for the above given database will contain only one table with 30 Columns (5 + 10 + 15) and off course no relationship between the data. But in such case data would be repeated very frequently i.e. data would be redundant.

The Normal Forms

Normal Forms are the guidelines provided by the Database community to achieve an optimal database model.

First Normal Form (1NF): First normal form (1NF) sets the very basic rules for an organized database:

• 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 (the primary key).

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.

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.

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.

Note: These normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database. 

What is De-Normalization & its significance?

In order to understand the concept of De-Normalization we consider the example of Data Warehousing,

Data Warehousing is a concept which helps us to manage (store and retrieve) the data in an efficient manner. 

Going Back to De-Normalization….

Suppose you have Data Warehouse. You have come across a requirement to provide some set of data which needs to join 25 tables and all the tables contains huge data, huge means really very huge data. As we know that joins are little bit costly in terms of time, then you can imagine the scenario for joining 25 tables.

In this scenario rather than storing the data in a Normalized fashion, we can denormalize the database which will help us to achieve performance.

Understanding Database

What is a Database?
Definition: Database is a structured collection of records or data that is stored in a computer system i.e. Secondary Memory.
One small question…
Que : What’s the difference between Database & Data Structure?
Ans : Database is concerned with the Secondary Memory (Hard Disk) of the machine where as Data Structure is concerned with the Primary Memory (RAM) of the computer.

In simple words database is nothing but a software which is capable of storing the data and,

• Simple retrieval of data stored
• Simple way to insert, update and delete the record.
• Data should be stored in an efficient manner.

Above are the features of a simple database. Whereas an ideal database (RDBMS) should follow following characteristics/ rules in order to be a true Relational Database.

Codd’s rurle for Relational Database Management System

Rule 1 : The information Rule.

“All information in a relational data base is represented explicitly at the logical level and in exactly one way – by values in tables.”

Everything within the database exists in tables and is accessed via table access routines.

Rule 2 : Guaranteed access Rule.

“Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.”

 To access any data-item you specify which column within which table it exists, there is no reading of characters 10 to 20 of a 255 byte string.

 Rule 3 : Systematic treatment of null values.

 “Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.”

If data does not exist or does not apply then a value of NULL is applied, this is understood by the RDBMS as meaning non-applicable data.

 Rule 4 : Dynamic on-line catalog based on the relational model.

“The data base description is represented at the logical level in the same way as-ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.”

 The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.

 Rule 5 : Comprehensive data sub-language Rule.

“A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items

•  Data Definition
• View Definition
• Data Manipulation (Interactive and by program).
• Integrity Constraints
• Authorization.

Every RDBMS should provide a language to allow the user to query the contents of the RDBMS and also manipulate the contents of the RDBMS. 

Rule 6 : View updating Rule

 “All views that are theoretically updatable are also updatable by the system.”

Not only can the user modify data, but so can the RDBMS when the user is not logged-in.

 Rule 7 : High-level insert, update and delete.

 “The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.”

 The user should be able to modify several tables by modifying the view to which they act as base tables.

 Rule 8 : Physical data independence.

“Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.”

The user should not be aware of where or upon which media data-files are stored

 Rule 9 : Logical data independence.

 “Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables.”

 User programs and the user should not be aware of any changes to the structure of the tables (such as the addition of extra columns).

 Rule 10 : Integrity independence.

 “Integrity constraints specific to a particular relational data base must be definable in the relational data sub-language and storable in the catalog, not in the application programs.”

 If a column only accepts certain values, then it is the RDBMS which enforces these constraints and not the user program, this means that an invalid value can never be entered into this column, whilst if the constraints were enforced via programs there is always a chance that a buggy program might allow incorrect values into the system.

 Rule 11 : Distribution independence.

 “A relational DBMS has distribution independence.”

 The RDBMS may spread across more than one system and across several networks, however to the end-user the tables should appear no different to those that are local.

 Rule 12 : Non-subversion Rule.

 “If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time).”

 The RDBMS should prevent users from accessing the data without going through the Oracle data-read functions.

Note:

In Rule 5 Codd stated that an RDBMS required a Query Language, however Codd does not explicitly state that SQL should be the query tool, just that there should be a tool, and many of the initial products had their own tools, Oracle had UFI (User Friendly Interface), Ingres had QUEL (QUery Execution Language) and the never released DB1 had a language called sequel, the acronym SQL is often pronounced such as it was sequel that provided the core functionality to SQL.