Find Most executed queries

SELECT TOP 50
QueryState.execution_count
,OBJECT_NAME(objectid)
,query_text = SUBSTRING(
qt.text,
QueryState.statement_start_offset/2,
(CASE WHEN QueryState.statement_end_offset = -1
THEN len(convert(nvarchar(max), qt.text)) * 2
ELSE QueryState.statement_end_offset
END - QueryState.statement_start_offset)/2)
,qt.dbid
,dbname = db_name(qt.dbid)
,qt.objectid
FROM sys.dm_exec_query_stats QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) as qt
ORDER BY QueryState.execution_count DESC

Get all queries running against any specific table

/*

Get list of all queries hitting any specific tables

*/

SELECT DISTINCT TOP 100

ProcedureName = OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)

,SQLStatement = SUBSTRING(

sqlTxt.Text

,(QueryState.statement_start_offset/2)+1

,CASE QueryState.statement_end_offset

WHEN -1 THEN DATALENGTH(sqlTxt.text)

ELSE QueryState.statement_end_offset

END - (QueryState.statement_start_offset/2) + 1

)

,DiskReads = QueryState.total_physical_reads --- Disk reads

,MemoryReads = QueryState.total_logical_reads --–Logical Reads are memory reads

,ExecutionCount = QueryState.execution_count --Execution Count

,CPUTime = QueryState.total_worker_time  --CPU Time

,DiskWaitAndCPUTime = QueryState.total_elapsed_time

,MemoryWrites = QueryState.max_logical_writes

,DateCached = QueryState.creation_time

,DatabaseName = DB_Name(sqlTxt.dbid) --Database name

,LastExecutionTime = QueryState.last_execution_time

,cte.*

FROM sys.dm_exec_query_stats AS QueryState

CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS sqlTxt

CROSS APPLY sys.dm_sql_referenced_entities(

OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)

, 'OBJECT'

) cte

WHERE sqlTxt.dbid = db_id() --Get detail for current database

AND cte.referenced_schema_name + '.' + cte.referenced_entity_name = 'dbo.tblEmployee'

What will be the result of the query below? Explain your answer?

select case when null = null then 'True' else 'False' end as ResultSet;

Different types of ANSI standard joins

INNER JOIN (SIMPLE JOIN): This returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN): This returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN): This returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN. The results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

FULL JOIN (or FULL OUTER JOIN): This returns all rows for which there is a match in EITHER of the tables. Theoretically, a FULL JOIN associations the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

CROSS JOIN: This returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

What does UNION do or what result set UNION return ? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. This return unique records from all the table. 

The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION , since UNION requires the server to do the additional task of removal of any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

STUFF

Use case when we are working on TSQL or Query ,some time we came such a situation when we need to replace a set of string into another set of string, this can be anything like string, character, numeric or special character. We can handle such situation using STUFF function.

STUFF

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntax:-

STUFF (Character Expression, Start, Length, Replace with Expression)

Arguments: This function uses the following parameters.

Character Expression: Is an expression of character data. Character Expression can be a constant, variable, or column of either character or binary data.

Start: Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first Character Expression, a null string is returned. Start can be of type bigint.

Length: Is an integer that specifies the number of characters to delete. If length is longer than the first Character Expression, deletion occurs up to the last character in the last Character Expression. Length can be of type bigint.

Replace With Expression: Is an expression of character data. Replace with Expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of Character Expression beginning at start.

Return Types

Returns character data if character expression is one of the supported character data types. Returns binary data if character expression is one of the supported binary data types.

Remarks

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

An error is raised if the resulting value is larger than the maximum supported by the return type.

EXAMPLE

SELECT STUFF(‘abcdefgh’, 2, 3, 'xyzwsq'); 

GO

OUTPUT

-------------------

axyzwsqbcdefgh

(1 row(s) affected) 

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.

Covering Index

What happens when a Query execute against a table with a covering index and another query with the same index structure but without the included columns?

“The covering index gives you the knack to avoid the trip back to the table for satisfying the request, as all of the columns requested already included in non-clustered index. It means you are able to escape the logical or physical reads to trip back to read the table for that information.”

But we can’t ensure this is not enough avoidance of logical or physical reads. There is also the consideration of the necessary task between the columns in the non-clustered index and the columns that need to be looked-up in the table, you can say putting data together.

To explain this let's create two identical tables; same schema, same data:

Use TSQL2012

CREATE TABLE TSQL2012.dbo.EmplyeeDetails1

(

  pkid INT IDENTITY(1,1) NOT NULL,

  EmployeeId VARCHAR(20) NOT NULL,

  firstname VARCHAR(50) NOT NULL,

  lastname VARCHAR(50) NOT NULL,

  address1 VARCHAR(100) NULL,

  address2 VARCHAR(100) NULL,

  city VARCHAR(100) NULL,

  stateregion VARCHAR(100) NULL,

  Areapostal_code VARCHAR(10) NULL,

  Empcompanyid VARCHAR(20) NULL,

  notes VARCHAR(200) NULL,

  registration_notes VARCHAR(200) NULL

  );

    GO

ALTER TABLE dbo.EmplyeeDetails1 ADD CONSTRAINT

 PK_EmplyeeDetails1 PRIMARY KEY CLUSTERED

  (

   pkid

  )

 WITH

  (

   PAD_INDEX = OFF

   , FILLFACTOR = 100

   , STATISTICS_NORECOMPUTE = OFF

   , IGNORE_DUP_KEY = OFF

   , ALLOW_ROW_LOCKS = ON

   , ALLOW_PAGE_LOCKS = ON

  ) ON [PRIMARY];

GO

CREATE TABLE TSQL2012.dbo.EmplyeeDetails2

 (

  pkid INT IDENTITY(1,1) NOT NULL,

  EmployeeId VARCHAR(20) NOT NULL,

  firstname VARCHAR(50) NOT NULL,

  lastname VARCHAR(50) NOT NULL,

  address1 VARCHAR(100) NULL,

  address2 VARCHAR(100) NULL,

  city VARCHAR(100) NULL,

  stateregion VARCHAR(100) NULL,

  Areapostal_code VARCHAR(10) NULL,

  Empcompanyid VARCHAR(20) NULL,

  notes VARCHAR(200) NULL,

  registration_notes VARCHAR(200) NULL

  );

 

  ALTER TABLE dbo.EmplyeeDetails2 ADD CONSTRAINT

 PK_EmplyeeDetails2 PRIMARY KEY CLUSTERED

  (

   pkid

  )

 WITH

  (

   PAD_INDEX = OFF

   , FILLFACTOR = 100

   , STATISTICS_NORECOMPUTE = OFF

   , IGNORE_DUP_KEY = OFF

   , ALLOW_ROW_LOCKS = ON

   , ALLOW_PAGE_LOCKS = ON

  ) ON [PRIMARY];

 

 

  GO

Data in both the table is identical, also added identical clustered index. Due to auto incremented identity column added fill factor =100%.

Now making some differences between tables.

EmplyeeDetails1

Adding non clustered index on firstname and lastname with an included column (EmployeeId)

CREATE NONCLUSTERED INDEX [IX_EmplyeeDetails1_lastname] ON [dbo].EmplyeeDetails1

(

 [lastname] ASC,

 [firstname] ASC

)

INCLUDE

 (

  EmployeeId

 )

WITH

 (

  PAD_INDEX = OFF

   , STATISTICS_NORECOMPUTE = OFF

   , SORT_IN_TEMPDB = OFF

   , DROP_EXISTING = OFF

   , ONLINE = OFF

   , ALLOW_ROW_LOCKS = ON

   , ALLOW_PAGE_LOCKS = ON

   , FILLFACTOR = 80

  )

GO

EmplyeeDetails2

Adding non clustered index on firstname and lastname but without the inclusion of column.

CREATE NONCLUSTERED INDEX [IX_EmplyeeDetails2_lastname] ON [dbo].EmplyeeDetails2

(

 [lastname] ASC,

 [firstname] ASC

)

WITH

 (

  PAD_INDEX = OFF

   , STATISTICS_NORECOMPUTE = OFF

   , SORT_IN_TEMPDB = OFF

   , DROP_EXISTING = OFF

   , ONLINE = OFF

   , ALLOW_ROW_LOCKS = ON

   , ALLOW_PAGE_LOCKS = ON

   , FILLFACTOR = 80

  )

GO

 We have two identical tables, each having 5000 records.  The only difference is in their single non-clustered indexes; one has an included column, the other does not.  They have identical primary keys and clustered indexes. 

If we execute the following query against the table with the included column in the clustered index we see the following execution plan:

SELECT employeeid, lastname, firstname

FROM EmplyeeDetails1

WHERE lastname = 'samsamada43032' AND firstname = 'samada43032';

go

SELECT employeeid, lastname, firstname

FROM EmplyeeDetails2

WHERE lastname = 'samsamada43032' AND firstname = 'samada43032';

The whole action occurs within the non-clustered index itself. We never have to travel back to the table for more data. If we execute the identical query against the table with the same index structure, minus the included column in the non-clustered index, we get different behavior.

Since the non-clustered index doesn't contain all the information we need to satisfy the query, it's necessary to hit the index for the base information, and then using the row pointer in the non-clustered index, pull in the employeeid from the table and run a Nested Loop Join in order to marry the two working sets of data together before returning them to the end user. We can see the difference in expense for reads when looking at the I/O statistics from the two queries:

Note that these are all logical reads since the pages were already in the buffer. If this was a situation where it was a much larger data set you were contending with on an active server with the pages still on disk, this could be a very expensive operation.