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'

Missing index with create statements

Missing index :- Scripts will help you to get all the missing index and prepare script for new index.

/*
Description:- This Query will provide you detail of missing indexes on a table and also 
 prepare sql script for new index
*/
SELECT
DISTINCT
   @@SERVERNAME AS [ServerName]
   , DB_NAME() AS [DatabaseName]
   , SCHEMA_NAME([systemObject].[schema_id]) AS [SchemaName]
,[systemObject].Object_ID AS ObjectID
   , [systemObject].[name] AS [ObjectName]
   , CASE [systemObject].[type]
      WHEN 'U' THEN 'Table'
      WHEN 'V' THEN 'View'
      ELSE 'Unknown'
     END AS [ObjectType]
   , [MissingIndexDetail].[equality_columns] AS [EqualityColumns]
   , [MissingIndexDetail].[inequality_columns] AS [InequalityColumns]
   , [MissingIndexDetail].[included_columns] AS [IncludedColumns]
   , [MissingIndexGroupState].[user_seeks] AS [ExpectedIndexSeeksByUserQueries]
   , [MissingIndexGroupState].[user_scans] AS [ExpectedIndexScansByUserQueries]
   , [MissingIndexGroupState].[last_user_seek] AS [ExpectedLastIndexSeekByUserQueries]
   , [MissingIndexGroupState].[last_user_scan] AS [ExpectedLastIndexScanByUserQueries]
   , [MissingIndexGroupState].[avg_total_user_cost] AS [ExpectedAvgUserQueriesCostReduction]
   , [MissingIndexGroupState].[avg_user_impact] AS [ExpectedAvgUserQueriesBenefitPct],
   IndexName='[ix_' + tbl.name + N'_' 
                    + REPLACE(REPLACE(REPLACE(REPLACE(
                        ISNULL(equality_columns,N'')+ 
                        CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
                        + ISNULL(inequality_columns,''),',','')
                        ,'[',''),']',''),' ','_') 
                    + CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N']' ,

     IndexCreateScripts=N'CREATE INDEX [ix_' + tbl.name + N'_' 
                    + REPLACE(REPLACE(REPLACE(REPLACE(
                        ISNULL(equality_columns,N'')+ 
                        CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
                        + ISNULL(inequality_columns,''),',','')
                        ,'[',''),']',''),' ','_') 
                    + CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N'] ON ' 
                    + [statement] + N' (' + ISNULL(equality_columns,N'')
                    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
                    + CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE N'' END + 
                    ') ' + CASE WHEN included_columns IS NOT NULL THEN N' INCLUDE (' + included_columns + N')' ELSE N'' END
                    + N' WITH (' 
                        + N'FILLFACTOR=80, ONLINE=ON' 
                    + N')'
                    + N';'
FROM 
   [sys].[dm_db_missing_index_details] AS [MissingIndexDetail]
   CROSS APPLY sys.dm_db_missing_index_columns  ([MissingIndexDetail].index_handle)
   LEFT JOIN [sys].[dm_db_missing_index_groups] AS [MissingIndexGroup]
      ON [MissingIndexDetail].[index_handle] = [MissingIndexGroup].[index_handle]
   LEFT JOIN [sys].[dm_db_missing_index_group_stats] AS [MissingIndexGroupState]
      ON [MissingIndexGroup].[index_group_handle] = [MissingIndexGroupState].[group_handle]
   INNER JOIN [sys].[objects] AS [systemObject]
      ON [MissingIndexDetail].[object_id] = [systemObject].[object_id]
inner join sys.databases db on [MissingIndexDetail].database_id=db.database_id
inner join sys.tables tbl on [systemObject].object_id=tbl.object_id
WHERE
   [MissingIndexDetail].[database_id] = DB_ID()  -- Look in the Current Database
   AND [systemObject].[type] IN ('U','V')     -- Look in Tables & Views
   AND [systemObject].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
   --and( tbl.name like '%Accounting%' or tbl.name like '%treasury%') --Add table name for filter
--and [MissingIndexGroupState].[avg_user_impact]>=50; -- filter impact percentage 


go

How we can determine table column dependencies within a SQL database?

I came a situation during some analysis where i had to figure out all dependency of a column across database. This also required when we do some changes on table columns and wanted to know the impact of such changes. One way is that  we can use SYSCOMMENTS table.  It is a table which contains entries for each views, rules, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The column TEXT in the syscomments table contains the actual code for all these objects, and knowing it you can write a code to check the dependencies. I am explaining different approach.

/* Table Name for which we need to find the dependency of columns.*/

Declare @TableName varchar(max)='tblSupplierRequestOrder'

/* Column name for which we need to find the dependnecy in a database*/

Declare @ColumnName varchar(max)='OrderDate'

select  OBJECT_NAME(k.parent_object_id) as parentTableName

          , c1.name as parentColumn

          , OBJECT_NAME(k.referenced_object_id) as referencedTableName

          , c2.name as referencedColumn

    from    sys.foreign_keys k

            inner join sys.foreign_key_columns f

              on  f.parent_object_id = k.parent_object_id

            inner join sys.columns c1

              on  c1.column_id = f.parent_column_id

              and c1.object_id = k.parent_object_id

            inner join sys.columns c2

              on  c2.column_id = f.referenced_column_id

              and c2.object_id = k.referenced_object_id

    where   c2.name = @ColumnName

    and     OBJECT_NAME(k.referenced_object_id) =@TableName

Column and table constraints

Column and Table Constraints

Constraints can be column constraints or table constraints.

A column constraint is specified as part of a column definition and applies only to that column.

A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint.

For example, if a table has two or more columns in the primary key, you must use a table constraint to include both columns in the primary key. Consider a table that records events occurring in a system.

A number of different event can occur in different time or same time but assuming no same event occur in same time and same type.

This can be enforced in the table by including both the event_type and event_time columns in a two-column primary key, as shown in the following example.

CREATE TABLE System_process

   (event_typeID   int,

   event_time   datetime,

   event_site   char(50),

   event_desc   char(1024),

CONSTRAINT event_key PRIMARY KEY (event_typeID , event_time) )

What is Constraints in TSQL?

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.Constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Types of Constraints.

·         NOT NULL, This specify that column does not access NULL values.

·         CHECK constraints enforce domain integrity by limiting the values that can be put in a column. A check constraint can be Boolean values (True, false or unknown). We can add search condition which will be applied while we do insert or update operation on a column. This also enforce that inserted or updated values must be in range which is specified within check.

CREATE TABLE Employee

   (

   Employee_id     bigint PRIMARY KEY,

   Employee_name   varchar(max),

   Emp_address     varchar(max),

   Emp_basic_pay   money,

   CONSTRAINT chk_id CHECK (Employee_id BETWEEN 100 and 1000000 )

   )

·         UNIQUE constraints enforce the uniqueness of the values in a set of columns. This enforce no two rows in a table can have same value for the columns. Primary keys also enforce uniqueness, but primary keys do not allow NULL as one of the unique values.

·     PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key.

CREATE TABLE Employee

   (

   Employee_id     bigint PRIMARY KEY,

   Employee_name   varchar(max),

   Emp_address     varchar(max),

‘   Emp_basic_pay   money,

   CONSTRAINT chk_id CHECK (Employee_id BETWEEN 100 and 1000000 )

   )

•          FOREIGN KEY constraints identify and enforce the relationships between tables.

A foreign key in one table points to a candidate key in another table. (A column or combination    of columns that qualify as a primary key value is referred to as a candidate key.)

CREATE TABLE order_part

  (

order_nmbr      int,

part_nmbr      int

             FOREIGN KEY REFERENCES part_sample(part_nmbr)

            ON DELETE NO ACTION,

          qty_ordered      int

);

GO

You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value. The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point.

 The ON DELETE clause has the following options:

a.  NO ACTION specifies that the deletion fails with an error.

b.  CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also  deleted.

c.  SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL.

d.  SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value

The ON UPDATE clause defines the actions that are taken if you try to update a candidate key value to which existing foreign keys point. This clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options.

Combining statement with conditional operators

Operators “AND”, “OR”

Sometime we need to combine two or more TSQL statement based on some conditions, that we can achieve using Conditional operator, Boolean   “AND operator” and “OR operator”

Boolean Operator: AND (&), OR (||)

Conditional Operator:-  

1.       GREATER THEN EQUAL    (>=) 

                X>Y;

2.       LESS THEN EQUAL ( <=)

Y<X;

3.       EQUAL ( ==)

                X=Z;

4.       GREATER THEN ( >)

                X>Y;

5.       LESS THEN (<)

Y<Z;

6.       NOT EQUAL TO ( <>)

Y<>Z;

The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed.

For example:

SELECT COLUMN1, COUNT (COLUMN2) FROM TABLENAME WHERE "CONDITION1" AND 

"CONDITION2";

The OR operator can be used to join two or more conditions in the WHERE clause. However, either side of the OR operator can be true and the condition will be encountered - hence, the rows will be displayed. With the OR operator, either side can be true or both sides can be true.

For example:

SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY FROM EMPLOYEE WHERE BASIC_PAY >= 15000.00 AND TITLE = 'AVP';

This statement will select the EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY from the employee table where the BASIC pay is greater than or equal to 15000 and the title is equal to AVP. Both of these conditions must be true in order for the rows to be returned in the query. If either is false, then it will not be displayed.

Although they are not required, you can use parenthesis around your conditional expressions to make it easier to read:

For example:

SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY FROM EMPLOYEE WHERE (BASIC_PAY >= 15000.00) AND (TITLE = 'AVP');

Another statement get the result set basic_pay>15000 and title can be AVP or VP.

For example:

SELECT EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE, SALARY FROM EMPLOYEE WHERE (BASIC_PAY >= 15000.00) AND (TITLE = 'AVP' OR TITLE=’VP’);

Aggregate Functions

Aggregate Functions

MIN	Returns the smallest value in a given column
MAX	Returns the largest value 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
COUNT	Returns the total number of values in a given column
COUNT(*)	Returns the number of rows in a table

In database management system an aggregate function is a function where the values of multiple rows are grouped together as input and on certain criteria to form a single value. Aggregate functions are used to compute against a numeric column data from SELECT statement. This mostly summarize the results of a particular column of selected data. 

For example:

SELECT AVG (MARKS) FROM STUDENTS;

This statement will return a single result which contains the average value of everything returned in the Marks column from the Students table.

Another example:

SELECT AVG (SALARY) FROM EMPLOYEE WHERE TITLE=’AVP’;

This statement will return the average salary for all employee whose title is equal to AVP.

Example:

SELECT COUNT (*) FROM STUDENTS;

This statement returns the total number of students. However you can also add additional column or distinct values counts.

Example:-

SELECT COUNT (DISTINCT TITLE) FROM EMPLOYEE;

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

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

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.

SPACE

USE CASE: When we are working on TSQL or some text based scripting we may need some extra space in between two string, we can add space without space function as well but it is possible we may miss to put space in between string or text. If we have to maintain fixed length of space in between string every time we can use TSQL SPACE function.

SPACE

Returns a string of repeated spaces.

SYNTAX

SPACE ( integer_expression ) 

ARGUMENTS

integer_expression
Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

REMARKS

To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE.

EXAMPLE:-

/*

Considering employee table has three rows;

ID	FIRSTNAME	LASTNAME
1	PRIYA	KUMARI
2	AYUSH	KUMAR
3	RICHA	SINGH

*/

SELECT FirstName +’,’+SPACE(3)+LastName from employee;

PRIYA   KUMARI

AYUSH   KUMAR

RICHA   SINGH

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)