SQL Index Tutorial | SQL Optimisation Part 1

SQL index is a distinct structure in the database that is built using the CREATE INDEX statement. Mostly , DBAs love & hate SQL indexes at the same time due to the tricky nature they posses. They can either speed up your databases or slow it down , depending on how correctly you have implemented them. People familiar with programming languages understand the behaviour of pointers very well. An index is the pointer in database. WHAT IS SQL INDEX As said , SQL indexes are a distinct data structure such as B+tree or balanced tree where reference to the original ...
Read More

Table vs View – All You Need To Know

Table vs View is a topic of debate among DBAs and beginners because the similarities they both share . We will discuss various aspects of both tables and views . Such as: Difference between Table vs View. Table vs View Performance. Advantages of Views in SQL In SQL Beginner's Guide we have developed basic understanding about views. This article will discuss advance aspects of the topic. In case you need some background reading Read : What is View in SQL ? Difference between Table vs View A table is where you store your data. The table actually occupies space on disk and ...
Read More

Lock Modes in SQL Server Part 2

Lock Modes in SQL Server ensures the concurrency and data integrity for multiple users. This is essential because we want all users to be able to use the database without any delays. Every thing to know about Levels of Locking in SQL Server read below link Read More : Locking in SQL Server Part 1  Lock Modes Following are the lock modes which that can engage with the resources : Exclusive (X) Shared (S) Update (U) Exclusive lock (X) This lock type, when imposed, will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive ...
Read More

Locking in SQL Server

Locking in SQL Server is mechanism to control and manage concurrency maintaining data integrity and validation. This allows multiple users to access  and use database concurrently. Consider the scenario and let me paint the complete picture here. When we talk about concurrency in DBMS , we are talking about DBMS allowing multiple users conducting multiple transactions and operations all at the same time. The operation can be read , update , delete or create. All of this , should execute on correct data. If Transaction T1 updates a row transaction T2 should be able to read the updated data. All ...
Read More

SQL Server Isolation Levels

SQL Server Isolation Levels helps in concurrency & transaction management by preventing concurrency problems . In an environment where we have multiple users modifying and accessing the same information , it is essential that they all can work at the same time without any delays. They all should be able to read correct data and modify data at will. Also see : SQL Server Concurrency & Transaction Management Commit & Rollback In transaction's ACID Properties we talked about , how a transaction has to change the state of the database and complete it's task and leave the system in a ...
Read More

SQL Server Concurrency & Transaction Management

SQL Server Concurrency & Transaction Management  is a process which allows multiple users to access data and perform multiple operations maintaining data integrity and smooth execution all at the same time. Microsoft SQL Server manages concurrency control by using locks to help protect data. The locks control how multiple users can access and change shared data at the same time without conflicting with each other. This a concept which needs to be in developer's mind all the time while writing the scripts to avoid any problems that may occur due to execution. Such as data loss , dirty reads and deadlocks ...
Read More

SQL Subquery | SQL Beginner Guide

SQL Subquery is another tool SQL Server has which can be used to fetch data from other tables to be used in main query according to the needs.  Inner query and nested query are other names of SQL Subquery. SQL Subquery can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc. The idea is that a subquery returns data for one column which can be further utilised in outer query. Too much to read , let's just start with examples and we will clear the points as ...
Read More

How Grouping Works in SQL

One of our subscribers reached us and asked How Grouping works in SQL ?  So today , this is what we are going to do . We will discuss in detail about grouping in SQL and how it actually works behind the scenes. Also , this is our first post in Questions category and we will be doing more as there are bundle of requests in the queue. So if you want to contribute and like us to write something for you , please do not hesitate. How Grouping works in SQL? Grouping summarises the whole data set into smaller chunks ...
Read More

MS SQL Query Execution Order Examples | SQL Beginner Guide

MS SQL Query Execution Order or SQL order of operations determines the sequence in which SQL clauses are executed by database engine . This helps in writing the error free and optimised queries .This SQL Query Processing is applied from a basic query to a thousands lines of queries , our database engine uses this execution order . See below the MS SQL Query Execution Order FROM ON OUTER WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY TOP Also see : SQL SELECT Query & Statements Recommended : MS SQL Group By Clause Also see : MS SQL Having Clause One ...
Read More

MS SQL Having Clause | SQL Beginner Guide

Filtering the Groups in MS SQL ( Transact SQL-TSQL)  requires MS SQL Having clause . We discussed in detail about sets & groups in our post on Group by clause.   In order to be able to practice the examples that we will discuss , i recommend you to use our sample database. It's a script you just have to copy paste in your MS SQL Server Management Studio & everything will be ready. Also see : How to Install MS SQL Server Management Studio  MS SQL Having Clause MS SQL Having clause is used to apply a filter on groups that are formed ...
Read More

MS SQL Group By Clause | SQL Beginner Guide

MS SQL Group By Clause in SQL is used with aggregate functions to group data based on columns. Before , we jump to details of the Group By clause let's try to understand about grouping. SQL is based on set-theory and whenever you're composing a query you treat a table as a whole & not row-by-row. Similarly , when we use aggregate functions we have to group the sets . This way you will have aggregated view of the set. What is the Difference Between a Group & Set? What is a SET in SQL A set is collection of ...
Read More

SQL Server Aggregate Functions | SQL Beginner Guide

SQL Server offers several built-in functions. SQL Aggregate functions are among the ones most widely used in reporting & data joining scenarios. Following are few SQL Aggregate functions we will discuss today COUNT ()                  returns the total number of values in a given column SUM ()                       returns the sum of the numeric values in a given column AVG  ()                       returns the average value of a given column MIN()          ...
Read More
Loading...

 

Advertisements