TSQL Masters

Nested Loop join,  Hash Join and Merge Join SQL server has three types of internal joins. Although there are different kinds of logical joins at the query level, SQL Server implement these all with three different physical join operators based on the scenarios. They are: Nested Loop join Hash Join Merge Join   1. Nested Loop Join The optimizer may choose the Nested Loops join when one of the joining tables is small, considered as the outer table and the other one is large, considered as the inner table which is indexed on the column that is in the join.One of the joining table is taken as the outer table and the other one as the inner table. For each row of the outer table, all the rows from the inner table are matched one by one. It cycles through the outer table, then search for matches in the inner table.  The optimizer considers three variants for a Nested Loops join: Naive nested loops join when the search scans the whole tab...

TRUNCATE  Vs  DELETE       Q1. What are the difference between TRUNCATE command and DELETE command? Q2. Can TRUNCATE be rolled back like the DELETE command? Q3. What are the advantages of TRUNCATE  command compare to DELETE?    Q1.What are the difference between TRUNCATE command and DELETE command ? TRUNCATE : 1.Remove all pages from the table. You can’t use WHERE clause with TRUNCATE . 2.Deallocate the data pages in the table. 3.Faster than DELETE. 4.Reset the identity column. TRUNCATE  is a DDL statement. So you need ALTER permissions for it. 5.Doesn’t fire trigger. 6.Acquire only page or table lock.. 7. TRUNCATE  cannot be used with indexed views. 8. Drop all object’s statistics and leave the table empty. Zero pages are left in the table. DELETE: 1.Remove all pages from file if used without a WHERE clause. You can use WHERE clause with DELETE. ...

SQL Server Pages and Extents Q. What is the basic storage unit in SQL Server database? Page is the basic unit of data storage in SQL Server. Disk space allocated to the data files (.mdf or .ndf)  are logically divided into pages. Log files do not contain pages, they contain log records. Q. What is the size of a data page? Size of a page in SQL Server is 8KB or 8192 Bytes (1024 bytes * 8KB) Out of 8192 Bytes, Header takes 96 Bytes Row offset takes 36 Bytes. So, 8192 Bytes - 96 Bytes- 36 Bytes = 8060 Bytes is available to store the actual data. Q. What happens if the data to be inserted exceeds maximum size of a data page, that is 8KB? (Row-Overflow Data Exceeding 8 KB) The maximum amount of data that a page can occupy is 8060 bytes. If the total size of a row exceeds 8060 bytes, SQL Server moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit and keeps a pointer to this new page in the original data page. Q. What are the diffe...

Data Modeling Tools for SQL Server Database What is Data Modeling? What is a data model? Data Modeling is the process of creating a data model for a software system. Data model usually consists of entities, attributes and their relationships, integrity rules and definitions of the objects. Data model helps us to visualize complex data structures How does a data modeling tool help: Most of the data modeling tools supports standard modeling methodologies( like generating ER Diagrams), automatic SQL generation, reverse engineering capabilities to document and update existing databases etc. Data modeling tools help to design databases also help to generate DB scripts. It can help to get a clear picture of your existing databases and how it look like. Data Modeling Tools for SQL Server Database Below is a list of some of the Data Modeling Tools. 1          CA ERwin Data Modeler 2  ...

What is a Relationship in a database management system? A relationship captures how two or more entities are related to one another. There will be relationship among entities. Example: A supervises relationship between an employee and a department. Nurse take care the patient Types of Relationships: The relationship between entities may be one-to-one, one-to-many or many-to-many. Example1: (One-to-one and One-to-many) If you take entities Student, Batch and Subject, the following are the 2 possible relationships One-to-one relationship between batch and subject. One batch is associated with only one subject. One-to-many relationship between batch and student entities. One batch may contain many students. Example2: Many-to-One:  Employees  -> report to ->Manager. Employees in a particular team/dept are reporting to the manager of that team/dept.

What is an Attribute in database management system? An Attribute is a property that describes an entity. Each entity is associated with a collection of attributes. Example of Attribute: Employee’s name, age,  address, salary and job etc are the attribute for the entity 'employee' Patients name, address, weight, height, age etc are the attribute for the entity 'Patient'. Student's  roll number, first name, middle name, last name, etc are the attribute for the entity 'Student'. How to implement: Attributes are represented as columns in a table. For example in a table named 'Patient', patient_idn, patient_first_name, patient_middle_name, patient_last_name, dob, patient_gender, martial_status, etc are the columns.

What is an Entity in database management system? An entity may be defined as a thing which is recognised as being capable of an independent existence and which can be uniquely identified. An entity is any object that is stored in the database. Example of entities: An entity is a person, object, or concept about which you want to store information. Some of the entities described in the tables are employees, departments, and projects. In a Hospital Management System, Patient is an entity. How to implement? In a relational database, entities are represented as tables.